Keep Your SQL Servers Synchronized with SQL Compare and SQL Data Compare

By : Joél Contreras Dec 20, 2003

Printer friendly

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.