Other data transmit/copy options in SQL SERVER.

Question

There is a business need that updated data is needed at Say at A,B, and C locations wherein A is Head office and B and C are warehouse locations and except Master(s) records; transaction(s) records will be updated at all the 3 locations by data users; apart from replication; which technology/option(s) from SQL Server ecosystem can be used; time lag duration between syncs can be approx. 15 to 20 minutes old data and acceptable to stake holders; last update of a record to be updated/synced at all servers; however no data loss or inconsistency is expected after last update of data. All 3 locations are say approximately 600 miles apart.

May suggest in increasing order of infrastructure cost. Thanks in advance.

Answer 1

There are a number of ways to skin this cat, both with regards to the table design and how to perform the actual sync between the sites. My first reaction is to use Service Broker, but you could also roll your own with help of Change Tracking or Change Data Capture. By rolling your own, I mean that you write your own program.

When it comes to Service Broker, I recommend [this book written by Roger Wolter] (https://www.amazon.com/Rational-Server-Service-Broker-Guides/dp/1932577270/ref=sr_1_1?ie=UTF8&qid=1420025225&sr=8-1&keywords=Roger+Wolter+service+broker) who was the Program Manager when the feature was developed in SQL 2005. This is a very sweet book in 220 pages, so you can spend a day or two on reading to get the idea if this is something for you.

Answer 2

Hi @DM ,

Welcome to Microsoft Q&A!
I overlooked this post, you can also refer to it: https://stackoverflow.com/questions/59028206/how-can-i-synchronize-three-database-from-different-locations
Then i read this article: https://www.jumpmind.com/blog/combined/data-reads/about-products/mssql-vs-symmetricds
But I haven't used it, so maybe it's worth a try.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.