2

Currently we have one SQL Server 2008 R2 database server in location X. All application users are in location X. All instances of the application can add data to database table and which may be requested by other users at the same time.

Now few of our users moved to location Y and they are facing lag in accessing data from location X database.

I'd like to create a replica of database X and put in location Y and keep them in sync. Updates in location Y would be available to location X database too and vis-versa. What is the best way to keep both databases in sync and reduce the access lag?

I am new to database management. Please feel free to suggest some articles or reading.

  • Need more info. Are the locations on the same LAN/WAN etc? What are the requirements i.e.: how much lag can there be between the databases? If we are talking minutes, you might get away with log shipping. Seconds? Transactional Replication/Mirroring. Depends on the requirements. – Peter Feb 23 '16 at 22:47
2

As you plan to update location Y, you should consider merge replication but it isn't simply a case of turning it on and you may need to make some changes to your DB or application.

You do have other options.

  • Reduce database chatter. Minimise the number of calls to the database to reduce the impact of latency. If you're talking between London and Hong Kong, you'll pick up 250ms of latency with each packet (round trip). Four packet would accumulate 1 second of latency, so batch your queries up and use stored procedures to reduce the overall size of your queries.
  • Split reads and writes. The reads could talk to the local DB (Y) and the writes sent back to the master (X). This could enable you to use AlwaysOn Availability Groups, Log Shipping or other forms of replication like Snapshot and Transactional.
  • WAN optimization. Most firewalls have a WAN optimiser option (usually a bolt on). This would compress the traffic, reducing the latency of larger payloads. Or look at Windows Server BranchCache.

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.