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!