Performance in replicating table between servers

Posted on

Question :

I’m using SQL Server 2014 and I have two databases, in different region of the world. One in Brazil and the another in China. The databases are exactly the same, except by the ID column, with they have different ranges.

These two databases, they need to communicate to each other the records that have been inserted, deleted and updated. For that, I created a column named UpdatedDate in each table, and after each operation this column is updated with the GetUtcDate(), this way, I can know with record I need to update.

Each minute, I run a task in each server that compared both databases.

The problem is, some tables have many records (Around 500k), and when the server is updating those tables, the application gets terribly slow.

The databases are totally separated and have no VPN connection, because of that that I’m using SQL Server tasks.

Here it’s a example of the task that runs the update:

Update
    LocalTable
Set
    LocalTable.RecordA = RemoteTable.RecordA,
    LocalTable.RecordB = RemoteTable.RecordB,
    LocalTable.UpdatedDate = RemoteTable.UpdatedDate
From
    MyTable LocalTable
    Inner Join [RemoteServer].MyDatabase.dbo.MyTable RemoteTable On LocalTable.ID = RemoteTable.ID
Where
    LocalTable.UpdatedDate < RemoteTable.UpdatedDate;

It’s there a way to improve the performance? I’ve thought in limiting the RemoteTableUpdatedDate to get only the changes from the last three days.

I’ve also though in creating an index with the UpdatedDate and the ID, including the rest columns… Bu I don’t have very much expertise in SQL Server.

Thanks for the help!

Answer :

if you want the records that have been changed you could use the OUTPUT Clause (Transact-SQL)

you can also see an example of this here

Regarding your update statement, you definitely need an index on the ID column in both tables. I would even consider creating it as clustered, but I don’t know your tables structure and your application.

you could for example, use the output clause to save the changed records into an specific table(s) and replicate that/those table(s).

Leave a Reply

Your email address will not be published.