CODEWITHSUNDEEP
sqlsynchronization
Dunny
Dunny

Reputation: 21

How to Sync tables between two databases in both directions - SQL Server 2008 R2

We are running SQL Server 2008 R2. Our operational software runs against this database and we are going to run two separate instances of the operational software accessing two separate databases. Both databases are on the same SQL Server. There are a list of 26 tables, out of 200+ in the database, that will need to be synced across the two separate databases. The tables can be accessed from either database and insert/update/deletes must be synced across both databases. The sync needs to happen near real-time, no less than once per minute.

I know I can hard code triggers, using MERGE to update the tables, but that means writing 52 (26 x 2) separate triggers that have all of the detail hard coded.

I've looked into Replication, but that seems to be more geared toward one master table feeding a child table, not two way updates.

I've looked into Change Tracking, but not all of the tables have PK's defined. This is the vendor's database, not mine. I know all the tables should have PK's. I don't know that I can define PKs on the tables.

My other thought was to replace one of the tables with a synonym so that both systems are actually updating the same table. My concern with that solution is the possibility of "breaking" the operational system.

I've just started researching Microsoft Sync Framework and whether that is an option.

Any thoughts, suggestions, direction would be greatly appreciated.

Thanks, Dave

Upvotes: 2

Views: 8774

Answers (1)

criticalfix
criticalfix

Reputation: 2870

This is a Replication scenario. Transactional Replication can handle updates from the Subscriber back to the Publisher, but in this case you want Merge Replication, which is better at merging conflicts when a Subscriber updates back to the Publisher.

You'll need PKs. If you have autoincrement IDENTITY fields, those are going to be a problem. There are strategies for partitioning id ranges, such as setting different Identity Seed values for versions of the table in each database.

If you can't make any changes to the database, this approach probably can't meet the requirements. Replication is the right way to do this sort of thing, but it does require some power to change the structure.

Upvotes: 2

Related Questions