Managing multiple versions of the same database across different environments (i.e. Development, Test, Production, and Disaster Recovery site) is perhaps one of the most important and least favorable Database Administrative duties. Whether it’s synchronizing database objects or actual data, creating utilities to perform this obligatory task is somewhat of a dreaded chore. I mean, think of it, when was the last time you looked forward to creating one of these utility jobs? My point exactly!
In some organizations (typically smaller IT shops, including one person shops), developers are allowed to create the utilities required to do the job. In other organizations (typically the larger IT shops), these responsibilities are delegated to a DBA area and are part of a formal process of database synchronization from one environment to another. Regardless of which camp you fall into, the challenges and approaches to maintaining a synchronized database between Development, Test, Production, and Disaster Recovery site normally follow this process:
1. Create the scripts in a Development environment.
2. Test the scripts in the Development environment.
3. Make the required script changes to accommodate Acceptance Test environment configuration.
4. Move the scripts to the Acceptance Test environment as part of an acceptance test migration process.
5. Test the scripts in the Acceptance Test environment.
6. Make the required script changes to accommodate the Production environment configuration.
7. Move the scripts to the Production environment as part of a production migration process.
8. Move the scripts to the Disaster Recovery environment as part of a Business Continuity process.
In SQL, the complexity of managing this “routine” process grows exponentially with the number of databases, database objects, developers, and DBAs. Obviously, with the increased complexity, the risk level of potentially not having your databases synchronized across the intended environments rises. To mitigate the risk, a Quality Assurance (QA) step can be inserted into the process. The sum of all of this is more time spent in performing DBA duties that, for the most part, should be routine. So you may ask, okay why not automate this whole process? To which I reply, my point exactly!!!
Automated synchronization of SQL objects and data is exactly what SQL Compare 3.0 and SQL Data Compare from Red-Gate Software offers as a solution to organizations executing versions of Microsoft SQL version 7.0 or higher. I decided to review these two products in tandem because, as I see it, the functions they perform are complementary (i.e. one synchronizes the database objects, while the other takes care of synchronizing the data.)
Let the Testing Begin
Before we go into the details of the test results, let’s briefly review my test environment configuration, as listed below:
OS – Windows Server 2003 Standard Edition.
DBMS – MS SQL 2000.
Database – Pubs (demo database available with all versions of SQL).
Using SQL Server Enterprise, I created a new database and called it Pubs Copy. I purposely wanted to begin with a blank database to see how the two products handled synchronization of a blank database. Figure 1.0 shows the SQL Compare 3.0 Comparison Settings dialog box. This is the first screen displayed after SQL Compare starts. As demonstrated in figure 1.0, the databases to be used for comparison and the host servers where they reside are selected in this screen. Security access to the servers and database is gained via standard Windows authentication or SQL Server authentication processes.
Figure 1.0
Upon pressing the Compare button, SQL Compare collects all object information regarding the two databases. This is followed by an informational dialog box status indicating the results of the comparison step. Once this dialog box is by passed, the following comparison results screen is displayed:
Figure 2.0
As you can discern, the screen is divided into three panels. First and foremost, the top panel displays command options, the databases being compared (color coded), as well as a grid with four columns that can be sorted by pressing on the column headers. The color coding of the databases are good visual indicators and provide guidance of the databases being compared. The grid columns list the following headers:
1. Type – Lists the object type (i.e. table, stored procedure, etc) for the databases
2. Name – Lists the object name.
3. Status – Synchronization status indicator flag. The meaning of each indicator is as follows.
4. Synchronize – Checking this checkbox selects the objects to be synchronized in the direction as noted by the synchronization Status (#3 above) indicator.
Selecting one of the rows in the top panel automatically changes the script displayed on the bottom left hand panel. This “script” panel displays the Creation SQL Scripts, and the object synchronization scripts generated by SQL Compare (again, refer to figure 2.0). The bottom right hand panel contains the second database being compared. Since I decided to begin with a blank database object, this panel is blank at this time in the synchronization process. Pressing the “Synchronization” command on the top panel initiates the synchronization process.
However, before the process begins, the following directional dialog screen is displayed:
Figure 3.0
As noted above in figure 3.0, radio buttons are available to select the direction (i.e. source and target databases) in which to perform the synchronization process. This is followed by the Synchronization script screen wizard as shown below:
Figure 4.0
This is the actual SQL script file that will be executed during the synchronization process. As noted on this screen, there is a “Save script” option. Since these are standard SQL script commands, the script can be saved for execution at a later time from SQL Query Analyzer. Or, as I decided, the scripts can be executed directly from SQL Compare. Pressing the Next button initiates the script execution and results in the following dialog box.
Figure 5.0
After pressing the Finish button, a warning’ dialog box provides you with an opportunity to cancel the synchronization process. Proceeding past the warning will result in the database object synchronized as the following screen shows:
Figure 6.0
As can be clearly seen, the top panel “Status” indicator has now changed to “=” to reflect that the two objects are synchronized. Also, notice that the bottom right hand panel (which was once empty) now contains the scripts that were synchronized.
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.
Figure 7.0
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 8.0
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.
Figure 9.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).
Figure 10.0
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.
Figure 11.0
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.
Click here to sign up for FREE B2B / Tech newsletters from Murdok!
For nearly 19 years, Jol 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 companys 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. Jol has a BA in Computer Information Systems and a Master of Business Administration. He is planning to pursue a PHD in Computer Science. Jol can be reached at jc@loanhost4life.com
