I have read access to a remote SQL Server that contains table 'dbo.myTable'. The problem I am facing is that it is slow to read the data remotely, especially for small frequent calls.

I also have a local SQL server. What is the most simple way to have a local copy of 'dbo.myTable', that is synchronised automatically with the main one? (I don't need second precision, maybe a few minutes lag is enough)

The data will only be modified on the remote one, as locally I will only read.

Thank you

  • It might be simpler just to resolve the existing performance issue, unless you are absolutely certain that it can't be resolved (i.e. unreliable network). There are many methods to replicate. How many records in the table, how often is it changed, mainly inserts or updates? Is there any 'record last updated' field in the source table? Can you change anything in the source system? – Nick.McDermaid Jan 23 '17 at 4:15
  • What is slow is to access the remote server through Internet. There is no way around it...The table is mainly changed via inserts. Updates are rare. yes I can ask to do some changes in the source database, if needed. – RockScience Jan 23 '17 at 4:31
  • 'Simple' depends on your technology preference. Database mirroring is simple for a DBA. An SSIS or T-SQL solution is simple for me. A custom console app is simple for a developer. The simplest replication is just truncating the local table and loading all data into that (with some smarts so the data stays available). Given that you mention bad connectivity I would first test using BCP to extract all of the data to a local file repeatedly and see how long it takes and how reliable it is. This tells you whether it is practical to just extract all the data or whether you need some kind of CDC – Nick.McDermaid Jan 23 '17 at 4:50
  • Here's an example bcp command line to try: bcp database.dbo.table out C:\myfilee.dat -S SQLServerName -Uusername -Ppassword -N. This is a good way to test basic network connectivity without getting confused by other things like SSIS or SQL Statements. If this takes a long time or if it fails (repeat it a dozen times and see) then you need to consider something like change data capture, where only the data required is sent, not all of it. – Nick.McDermaid Jan 23 '17 at 5:08

You may want to consider transactional replication, which would allow you to have an initial snapshot of the table copied from the source system to the target system. After this initial snapshot, you can then configure transactional replication to push the data changes to the target system.

The requirement for transactional replication is that the table has a primary key, however, it is a feature that I have used for the purpose you described on many occasions.

Bear in mind that replication would need to be activated on the source system and requires some administration to ensure it runs correctly. However, for a single table syncronization setup as described, it should cover everything you need.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.