Introduction
SQL Compare is a tool that allows you to see the differences between two databases. It only focuses on the structure and not the data. SQL Compare can be purchased as a stand alone tool or as part of a bundle that includes a tool that focuses on the data. SQL Compare will show you the difference between similar objects by color coding SQL code. You get to see the code for each database side by side one object at a time.
Environment
I loaded SQL Compare on a PC with a Pentium III processor and running Windows 2000 Professional. To use SQL Compare you must install the .Net Framework and MDAC 2.6 or higher. You can find links to these as well as SQL Compare at
http://www.red-gate.com/downloads.htm. I used SQL Server 2000 databases to make my comparisons. I created two databases. One called dbFirst and one called dbSecond. I created various objects in each one, some the same and some not. I created tables, primary keys, indexes, triggers, defaults, user defined functions, stored procedures, views, check constraints, user defined data types, roles, and users that I added to each database.
Installation
SQL Compare is simple to install. I installed SQL Compare version 3.0.0.130/ I double clicked on bundle.exe, clicked next a few times, and accept their license agreement. Two minutes after I began the install was done. It uninstalls even faster; under 1 minute. To get SQL Compare you also end up with a few other products Red Gate makes. The download is a free and gives you a full function version to use for 14 days.
Using SQL Compare
Every time you launch SQL Compare you see a trial pop up box. Simply click next to start using SQL Compare. In a matter of a few minutes I compared two databases, figured out what the color coded arrows meant and synchronized the two. All this without reading any documentation.
The screen after the trial box is labeled Comparison Settings. It looks like this:
It allows you to enter the server, database, and login info for both databases you wish to compare. When ready simply click compare. SQL Compare then compares all database objects. Once finished you will be able to view a screen similar to the following:
SQL Compare caught all the differences I threw at it except for a difference of lower case and upper case letters for a column I called ZIP in one table and zIP in another table. I sent an e-mail to Redgate at about 9:32PM on 2 Dec 03. I figured I'd get a response in a few days to a week. They replied at 10:24AM on 3 Dec 03. The person sending me the e-mail wrote "...there is an option in SQL Compare called 'treat items as case-sensitive'." So if you need a case sensitive comparison, SQL Compare can handle it.
In the upper window of the screen shot above you will notice you can deselect any object you don't want to synchronize. When you click on the synchronize button you get a pop up screen labeled Synchronization Wizard. It gives you two options of which you can only select one. Make changes to dbSecond to make it the same as dbFirst or make changes to dbFirst to make it the same as dbSecond. After making your decision it shows you the script it will use to make changes along with a summary and warnings when needed. Then you decide if you want SQL Compare to run the script or to dump it into Query Analyzer for you to run it. Click finish and you're done. If you send the script to Query Analyzer then you must refresh the results in SQL Compare after running the script. If SQL Compare runs the script you can instruct it to automatically refresh its comparison after it is done. This is useful if you only change one or two differences and not all of them.
SQL Compare does not move data when synchronizing tables. It preserves the data that resides in the destination tables. I got a warning while synchronizing tables telling me that one of the columns I was moving did not allow null and did not have a default value (it was an identity column) and because of this the change could fail. Since the column was an identity column everything went fine. I also got a warning while synchronizing users telling me that if the user didn't exist in the destination database that it would be created with a blank password.
You can easily switch on and off which objects to view by selecting filters and the objects you want to see or not see.
Conclusions
I was surprised at how easy SQL Compare is to use. I only went to the help to get information about SQL Compare to put into this article. Its functionality is self-explanatory. It does its comparisons quickly. I recommend SQL Compare for anyone who needs to make object comparisons and/or synchronizations.
Ratings
I will rate each of the following using a scale from 1 to 5. 5 being the best and 1 being the worst.
| Ease of Use | 5 | Everything is self-explanatory. |
| Feature Set | 5 | Allows you to compare all or some SQL Server objects depending on your needs. |
| Value | 5 | Very reasonably priced for the power you get. |
| Technical Support | 5 | I was surprised by their swift response to my e-mail. |
| Lack of Bugs | 5 | I encountered no problems nor bugs. |
| Documentation | 4 | I didn't look very closely at the documentation, but it is easy to read and use. |
| Performance | 5 | It performed wonderfully and did everything I expected. |
| Installation | 5 | The install was very smooth and error free. |
| Learning Curve | 5 | I was able to use it in a couple minutes and never needed to Refer to the documentation to understand what it was doing. |
| Overall | 5 | A very good product. |
Product Information
Web Site: http://www.red-gate.com
Developer: Red Gate Software Ltd
Pricing: $195 (Single user license for SQL Compare only)
$350 (Single user license for a bundle of tools that includes SQL Compare)