Monday, October 8, 2012

Bidirectional synchronization using xSQL Data Compare

xSQL Data Compare is designed to compare the data in two SQL Server databases and generate the change script to make database A the same as database B or vice versa - in other words it is designed to go in only one direction at a time. However, while this one directional synchronization is very helpful in various scenarios it does not address the need of the users who are in charge of managing distributed databases and that for one reason or another are not able to implement a SQL replication solution. While not an ideal solution, with a bit of effort those users can use xSQL Data Compare to address this issue.
So, let's say you have a copy of database DB1 on Server S1 and a copy of it on server S2. Both servers/databases are active so they are both being modified simultaneously. Now, let’s say that you are in charge of synchronizing those two copies of the database every night. Before you can synchronize let's say table T1 on S1.DB1 with table T1 on S2.DB1 you will need to make a couple of structural changes:
  1. First let's assume that table T1 has a TimeStamp column that will be used to decide which record overrides which.
  2. Now, add column S2_TimeStamp on table S1.T1 and add column S1_TimeStamp on table S2.T1. You will need to make sure that the new timestamp columns are automatically updates anytime the row is modified. 
  3. Create a view for S1.T1 and a view for S2.T1 naming the timestamp columns in such a way that the name of S1.T1.TimeStamp matches S2.T1.S1_TimeStamp and S2.T1.TimeStamp matches S1.T1.S2_TimeStamp. The view on S1 will contain the following columns: T1.ID, T1.TimeStamp as TimeStamp1, T1.S2_TimeStamp as TimeStamp2 whereas the view on S2 will contain: T1.ID, T1.TimeStamp as TimeStamp2, T1.S1_TimeStamp as TimeStamp1. This will ensure that the columns of S1.T1 are automatically mapped with columns of S2.T1.
You will only need to do the above once for each table you wish to do a bidirectional data synchronization for. At this point everything is ready to set up the comparison and synchronization. Here are the steps:
  1. First, run a compare of the two views you created above and then:
    1. Exclude wT1.TimeStamp2 column and generate the script that makes S2.wT1 the same as S1.wT1. Execute the script – this will update the S2.T1 table with the timestamp from S1 without touching the S2 timestamp. 
    2. Now include wT1.TimeStamp2 and exclude wT1.TimeStamp1 and generate the script that makes S1.wT1 the same as S2.wT1. Execute the script – this will update the S1.T1 table with the timestamp from S1 without touching the S1 timestamp.
  2. Now you are ready for the final step – compare table S1.T1 with S2.T1 adding a WHERE clause for each side to exclude the rows where the timestamps are the same. You will need to repeat this process twice: first, you only include rows where S1.T1.TimeStamp > S1.T1.S2_TimeStamp – you will need to "push" those rows from S1 to S2; then, you include only the rows where S2.T1.TimeStamp > S2.T1.S1_TimeStamp – you will need to push those rows from S2 to S1.
 You can use the SQL Data Compare command line utility to automate all those comparison steps.
 
xSQL Data Compare is free for SQL Express and also for other editions of SQL Server with some limitations (check the details here: http://www.xsql.com/products/sql_server_data_compare/editions.aspx).

0 comments:

Post a Comment