CODEWITHSUNDEEP
sql-serversynchronization
chris_techno25
chris_techno25

Reputation: 2487

Sync 2 SQL Server databases both ways

I am trying to sync 2 SQL server databases from different locations. This is the scenario. Say servers A and B have 10 sacks. They're initially in sync. They are not always connected but as soon as they are, I'd like them to make changes to each other. If 2 sacks were taken from server A at 10 am, and 4 sacks were taken from server B at 10:10 am, as soon as they're connected, both servers should have 4 sacks left. I need changes to be committed chronologically regardless of when they actually do sync. So what happens when they connect, 2 sacks is deducted from 10 and they sync, so that means 8 sacks are left at servers A and B. At 10:10 am, 4 sacks are again deducted from the 8 left, which makes the final count to just 4. Is this setup possible? I tried Merge Replication but it doesn't sync the way I want it to produce the results I want. Going back to my example using Merge Replication, if sync time happens every 15 minutes, when 2 sacks are taken at 10 am from server A, changes are still not made to the other server since next sync time is at 10:15 am, which also means server B still has 10 sacks and server A has 8. At 10:15 am, only the 4 sacks taken from server B is taken into consideration and the final count at 10:15 am is 6 for both servers. The first change at 10:00 am is ignored. How can I achieve my desired setup? Thank you.

Upvotes: 1

Views: 77

Answers (1)

Brian
Brian

Reputation: 1248

I would put Triggers on the table that tracks the stacks on each database, and every time you make a change you write a record to a Synch table with the timestamp, the change, the server where the change occurred, and a flag to indicate whether that record has been reconciled. Then, when the databases reconnect, you select all the unreconciled records and apply them as needed. So, in your scenario, you'd have two records:

Server A:
---------
10:00|-2|A|0

Server B:
---------
10:10|-4|B|0

When the servers reconnect, Server A applies any changes that don't belong to it, and Server B does likewise.

Upvotes: 1

Related Questions