My first real production experience with SQL Server came when I upsized a popular event management system written in Access 97. Since Access Data Projects (ADPs) had not been released yet, we had to use Enterprise Manager and SQL Query Analyzer to maintain the database schema. With the advent of ADPs, SQL Server objects may now be maintained from the Access environment but there remains no convenient way to log individual changes to tables, views, stored procs and other database objects.
Visual Source Safe is a good way to track changes but the nature of Access (all objects being housed in a single file) makes using it a tedious chore. We had abandoned VSS years before and were reduced to managing versions of our SQL Server database with a folder full of SQL script text files.
I know, I know, we should have at least used Source Safe for our SQL scripts but since it wasn't in place, and since there were only 2 developers, we forged ahead without any source control in place. That was a mistake, of course, and soon we had multiple versions of production and development databases lying around with no easy way to identify which one contained what modifications.
Around that time, I was trolling the SQL Server newsgroups for help with some T-SQL issues and I saw a post with a link to a utility for comparing SQL Server databases. I went to http://www.red-gate.com and downloaded a free beta copy of SQL Compare from Red Gate Software. I could not believe how simple it was to use and how thoroughly it solved my database maintenance problems. That was five years ago and I am still a fan. The only thing that has changed is the product: it has gotten better.
What is SQL Compare?
SQL Compare is like WinDiff for databases and it is very simple to use. Select the two databases you wish to compare (which, by the way, do not have to be on the same server) and click the Compare button. The comparison process is very fast in the latest version and when it is finished, you are presented with a screen like that shown in figure 1.
Do not look too closely at the image or you will begin to wonder about my object naming conventions. Rest assured this is not my style. I inherited this database from a new client and have been asked to support it, fixing bugs and adding enhancements off site. For example, the lower half of the SQL Compare window (see figure 1) shows how the tblRBIBidsPending table in the left database contains fields that do not exist in the right database. That is because I added them to my development copy.
The upper half of the window displays a list of all objects compared, as well as their synchronization status. The Status column indicates if the objects in the two databases are identical, if one is missing from the other, or if the object exists in both but differs in content. The interface allows you to filter for one or more of these conditions, which makes the list more manageable. You may also filter for specific types of database objects, such as tables, views, stored procedures and the like.
All of this is great for identifying differences, but the real power lies in the synchronization process exposed by SQL Compare. Remember I said that the changes shown above were applied to a local development copy of the database. These changes now need to be applied to production. How can SQL Compare assist with this process?
Notice in Figure 1 that the lower window includes two additional tabs: one provides the SQL script to synchronize changes from the left database to the right and the other tab shows the script that propagates the changes the other way. From these tabs, you can copy and paste the change script into a text file.
Alternatively, if you are on the network and attached to the target database, you can simply click the Synchronize button and a wizard will walk you through the process of migrating changes. The wizard even allows you to load the change script into Microsoft SQL Query Analyzer where you can examine the script, tweak it, save it to disk and run it yourself.
The SQL Compare interface also allows you to filter out certain types of differences, such as white space and comments, which may not be of interest to you when it comes to comparing objects. In fact, 22 different settings in the compare options dialog allow you to control the change script generated.
Another new feature that makes the latest version a valuable resource is the ability to save snapshots of analyzed databases. Once a snapshot has been created, it may be used in comparisons, though it cannot be modified with a change script. These snapshots can be saved as a history of the evolution of your databases. These snapshots can be stored in your development environment and uses as a baseline to production. Since they are simple schema scripts, they take up very little room and they are a great way to save a historical record of database changes.
Why invest in SQL Compare?
The thing I like least about writing product reviews is the part where I describe the product, as in the section above. Truth is, the help file usually does a far better job of explaining all its features than I ever could. What I like most about product reviews is describing why the product is valuable and identifying the target audience, those who will derive the greatest benefit from it.
To appreciate the value of this product, you need to know that it is a moderately priced utility, selling for $195 for a single user license. SQL Compare is also sold with multiple license packs, support contracts and bundled with other great Red Gate software, like SQL Data Compare. (Check out their web site for complete pricing information.) I say moderately priced because, on the one hand, two hundred bucks represents a lot of pizza and designer coffee but on the other hand, it will pay for itself in only 2 to 4 hours of billable contractor time. It is a good deal for the money.
Ok, so that is what it costs, but what is its value? What will it bring to your business that you do not already have? Consider this:
- A tool for creating a version history of your databases, saved as snapshots.
- The ability to compare your production databases against frozen snapshots to see if anyone has "tweaked" the production code.
- A utility to quickly compile a script for documenting and migrating batch changes from development to production.
To be fair, some of this functionality already exists in other tools you may be using, such as Visual Source Safe, Query Analyzer and SQL Enterprise manager. With VSS, you can document changes, save script files and compare them in a text editor. Enterprise manager allows you to generate a script snapshot of your database and Query Analyzer is where you tweak and execute those scripts. So, what are the benefits of SQL Compare?
- Speed! It's fast. The code behind this tool was recently rewritten for the latest version and it is as fast as lightning.
- Ease of use! The above-mentioned tools do what SQL Compare does, but not in one place and certainly not in one fluid motion, as does this product.
- Customization! Some of SQL Compare's customization is available in those other tools, and some is not. Try, for example, to ignore white space and comments when comparing files in VSS. It is just not there and it is one of the most important features to me as a developer. I do not want to be chasing down lines of code that differ by a space.
Yes, it is a great tool, but is it right for you? It may or may not be, depending on your development environment. Consider these scenarios:
- If your databases remain relatively static, then you do not need SQL Compare.
- If your IT department has strict change control and religiously uses a source control program, then you may not need it, though you could benefit from it.
- If your development team codes furiously in a RAD environment then, as far as I'm concerned, you can't live without it.
Clearly, my bias is showing. Red Gate's SQL Compare is a product I have used for years and I love the latest version. Above, I mentioned a sister product called SQL Data Compare. It works in much the same way but instead of comparing schemas, it compares table content. That will be the subject of a future product review. Trial copies of these utilities are available at the Red Gate web site and I encourage you to download and try them out.