Data Synchronization Using SQL Data Compare
Okay, with the database objects synchronized, we now turn to SQL Data Compare to do the job of ensuring that the data in Pubs is copied to Pubs Copy. For the first screen, SQL Data Compare displays exactly the same “Comparison Settings” screen as displayed in figure 1 of SQL Compare. Once these parameters have been set, the following screen in figure 7.0 is displayed. This screen allows you to select the tables to use in the synchronization process. In addition to the tables, options to compare keys and/or columns are made available in this screen.
The next screen allows you to choose the records to use for comparison as listed below. For this review, I chose to compare the identical records, different records, mission records, as well as additional records.
Figure 9.0 below is a screen shot of the final data comparison screen. As you can see, this output screen is divided into a top and bottom panel. The top panel contains the comparison tables selected in figure 7.0 and the records comparison criteria chosen in figure 8.0.
Another important item to note in figure 9.0, are the record criteria tabs (i.e. Identical records, Different records, Missing records, or Additional records) at the bottom of the screen. Pressing any of these tabs will show all of the records for a given table that meet the specified criteria (i.e. Identical records, Different records, Missing records, or Additional records).
In figure 9.0 above, the “Identical records” tab is selected and, since Pubs Copy does not contain any records, the second panel (which displays record status) is blank. Pressing on the “Different records” and “Additional records” tabs also yield the same blank panel. However, selecting the “Missing records” tab, all of the records in the selected table (i.e. Authors) are displayed on the second panel as noted in figure 10.0. Notice the message displayed on the heading of bottom panel which indicates that “[dbo.Authors] – 23 records that are only present in (local).Pubs.” Selecting each table yields the same synchronization status message (obviously with record counts specific to the selected table).
To synchronize the data from Pubs to Pubs Copy, I simply click on the Synchronize command at the top of the screen. The next sets of screens are synonymous to the screens displayed by SQL Compare as shown in figures 3.0, 4.0, and 5.0. To save space and not clutter this article with more images, I’ve chosen to exclude the screen shots from this section of SQL Data Compare (again refer to figures 3.0, 4.0, and 5.0). The end result of our data synchronization process using SQL Data Compare is depicted on figure 11.0 below.
Now notice the message displayed on the heading of bottom panel which shows that “[dbo.Authors] – 23 records that are the same in both tables.” This indicates that this particular table in synchronized. Selecting each table on the top panel will yield the same synchronized status. Thus, we have completely synchronized the Pubs and Pubs Copy databases.
Summary
The arduous tasks of maintaining synchronized database across multiple environments has been made a whole lot easier by Red-Gate Software’s SQL Compare and SQL Data Compare tools. When compared to using SQL Enterprise Manager or manually coding these scripts, deciding to use these products is truly a “no-brainer” decision. But don’t just take my word for it; download a 14 day full feature evaluation copy of these products at Red-Gate Software’s web site.
About the Author
For nearly 19 years, Joél Contreras has been employed in the Information Technology area of a major financial pension firm. During this tenure he has played significant roles in possibly every area of the company’s technological organization including Database Management System, Network Infrastructure, Project Management, Disaster Recovery, IT Customer Relationship Management, and Systems Programming. Although, he has been in management for over fourteen years, he has never lost the passion for “playing” with new technology. Joél has a BA in Computer Information Systems and a Master of Business Administration. He is planning to pursue a PHD in Computer Science. Joél can be reached at mailto:jc@loanhost4life.com
]]>