This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.
Compare and Synchronize SQL Server Databases – 100 times faster
Managing the design and creation of database objects such as tables, stored procedures, and views is a fact of life for developers who use a SQL Server database in their applications. Because applications grow and change continually, additions and changes to database structures (or schemas) are a constant part of the development process.
Developers generally create the requisite database objects using Microsoft’s SQL Enterprise Manager. Enterprise Manager offers a powerful, rapid way to create database objects because it automates the details of the work, allowing the developer to focus on application functionality. However, it also hides the SQL commands required to create the new objects from the developer, which can cause problems when the development team needs to migrate structures between the development, test, staging, and live databases.
The few developers who use a SQL editor to hand code every database object change can use the resulting scripts to make any future database migrations – if (and it is a very big if) in these shared common scripts they save every change in the correct order. When using this process, even one omitted statement can cause chaos.
I believe that using an automated migration tool for database structures is the best way to migrate database structures. For SQL Server databases, SQL Compare from Red Gate Software is the leading such tool.
What SQL Compare does
SQL Compare automates database structure migrations; it runs the comparison, shows you the differences, and generates the script, which can be run from SQL Compare, to synchronize the chosen databases.
The screenshots and explanation below will give you a brief introduction to the tool. Some images have been cropped for the purpose of this explanation. The best way to find out how well it will work for you is to download a free, fully functional trial from the Red Gate website.
Setting up a comparison
Figure 1. Setting up your comparison
The first thing you need to do is specify which two databases you would like to compare. SQL Compare can use either Windows or SQL Server authentication, and you can compare databases stored in different locations if necessary.
In this case we are going to compare two local databases – WidgetStaging with WidgetProduction. To run the comparison, press “Compare”.
Summary of Results
Figure 2. Summary of comparison results
Once SQL Compare has compared the two databases, the results will be displayed.
The top half of the screen (shown in Figure 2) gives you an overview of where there are differences. The black "=" symbol indicates there are no differences; the double-headed blue and red arrow indicates the object is different in the two databases, and the blue arrow indicates missing objects. If there were a red arrow, it would indicate additional objects.
The bottom half of the screen (Figure 3, below) shows you the creation SQL and points out where there are specific differences in the databases. Again, there are arrows to show you what is missing or different, and the SQL is color coded for ease of comprehension. If you don't want to view a particular object attribute, clicking the "-" will collapse it.
If you click on a particular database object in the top half of the screen, in this case the table WidgetPrices, you will be able to view the specific differences within that single object in the lower half of your screen (shown below).
Figure 3. Viewing differences within a single database object
In Figure 3, you may have noticed that there are two tabs that I haven’t shown you. The “Sync SQL 1” and “Sync SQL 2” tabs contain the code to synchronize this single object, WidgetPrices. In most cases, you won’t want to do this, as it is a lot of work to synchronize one object at a time. It is easier to synchronize all the different objects at once, which we will look at next. But, in some cases, you may just want to synch a single object, so this option is available to you.
Synchronization Wizard
Figure 4. Choosing the direction for synchronization using SQL Compare’s Synchronization Wizard
Now that SQL Compare has pinpointed the differences in the two chosen databases we can generate the synchronization script that would make the two databases the same.
Clicking the “Synchronize” button will launch the “Synchronization Wizard”.
The first thing we need to do is choose which direction we want to make changes. In this case we want to make the changes to WidgetProduction to make it the same as WidgetStaging.
By default SQL Compare will look at dependencies in the database objects. If you would prefer SQL Compare to ignore dependencies, you can uncheck the box at the bottom left of the Wizard screen.
Script for synchronizing your SQL Server databases
Figure 5. Viewing the SQL Script to synchronize two databases
Once you have chosen the direction for synchronization, SQL Compare will generate the synchronization script. The “SQL Script” tab shows you the script that has been generated.
If you need to keep records of changes to your databases, you could also choose to save the script for use at a later date.
Figure 6. A summary of what the SQL Script will do
Clicking the “Summary” tab of this Wizard screen tells you exactly what the script is going to do.
Figure 7. Warnings about any potential problems with synchronization
The “Warnings” tab will point out anything that you need to be made aware of before you run the script. In this case it is warning us that the table WidgetReferences will need to be rebuilt which will make the synchronization run slightly more slowly. If the problem identified could cause a synchronization to fail, SQL Compare will suggest a solution.
Choosing your synchronization option
Figure 8. Choose what to do with the synchronization script
Now that SQL Compare has generated the synchronization script, you need to decide whether you would like to synchronize the databases from within SQL Compare or run the synchronization from within the SQL Query Analyzer.
If you decide to synchronize the databases, as we have done here, you can choose whether or not you would like to re-compare the databases after the synchronization. In this case, we are going to re-run the comparison to ensure it was a success.
Re-running a comparison to ensure a successful synchronization
Figure 9. Successful synchronization – databases are now identical
As we asked SQL Compare to re-compare the databases after the synchronization, SQL Compare automatically re-runs the comparison and displays the results. We can now be sure that the synchronization was successful as all objects are the same in both databases, as indicated by the “=” symbols.
A vital tool for developers using SQL Server databases
The process for managing database structural migrations between development, testing and live databases is best done using SQL Compare. If you are a developer who develops using SQL Server databases then SQL Compare will save you a lot of time.
If you need to compare and synchronize the data within your SQL Server databases, SQL Compare’s companion product, SQL Data Compare, will do this time-consuming task for you too.
SQL Compare is described as “an important part of any serious software shops tool chest”, and has won awards for being the most time saving tool for SQL Server. Many positive reviews and testimonials have been written about SQL Compare, and its companion products.
A major new version of this already award-winning tool was released on October 22nd. Read all about it at http://www.red-gate.com/sql/version30x_sql_compare.htm.
The best way to see how well this product will work for you is to download a free, fully functional trial from Red Gate’s website.
