To start, I love Red Gate Software’s Tools. I have loved them since the first time I started using SQL Backup and SQL Compare when I first started Geekswithblogs.net. I think they create quality products and have been a “Friend of Red Gate” for years. So know that this review is coming from a “fan boy” of sorts.
What is SQL Compare?
Well the name sort of says it all, this product compares SQL databases. But not just databases, but backups, snapshots, and source control using their product SQL Source Control. Once you configure your project to select the type of source you would like to run through the system, SQL Compare will load the differences into a view and allow you to see what is different. You can then push the changes into the different sources, allowing them to be aligned. I don’t even like to think about what this would be like with hand rolled SQL most developers resort to.
For this review, I am going to use the SQL Server Sample Database, Adventure Works, available on CodePlex.
Comparing Live Databases
SQL Compare has had this feature for years so I won’t spend much time explaining it, even though it is my most used by far. This feature really works well when you have a test or demo system that is running a version of a database and a development version you are using on your machine. You can compare the differences, select the ones you want to synchronize and generate a script that the SQL Compare application will execute or you can run yourself in SQL Server Management Studio.
This can also be a poor mans version of Source Control if you want to keep tracking of changes between builds, but if the funds are available, I would highly suggest adding SQL Source Control to your suite of tools.
Comparing a Live Database to Source Control
For this sample we have changed the EmailAddress field length in Production.ProductReview table from 50 to 100. Immediately SQL Source Control shows us that this table has changed by the icon, but we are going to use SQL Compare to help us this round.
We first open up SQL Compare and set our source database to the AdventureWorks database on the local machine and then set the Target to the Source Control schema folder we have assigned in Team Foundation Server and SQL Source Control. Below is the project configuration
The output of the comparison shows that we have one difference between the Source Control version and the Database.
My standard response would be to open SQL Management Studio and check in the changes, but I want to see what it will do if I run the SQL Compare Synchronization Wizard under source control.
So far so good, the Wizard can make the changes to the scripts themselves, however it doesn’t check out the files first so you would need to go into source control (our case, Team Foundation Server) and check out the files then check in the changes. In fact it will let you know it is changing read-only files first before committing the changes. This should alert you that you will need to do a little extra leg work to get the changes back into source control.
Instead, open SQL Management Studio and Commit the Pending Changes from the SQL Source Control tool so you can ensure TFS gets the check out request.
Comparing a Live Database to a Previous Backup
Using the Red Gate SQL Backup utility, I created a backup of the AdventureWorks database at this point. Then I went back in and modified the HumanResources.Employee table to extend the length of the Title field.
SQL Backup is another amazing tool if you are used to creating backup with SQL Management Studio. This makes the process simple for creating backups ad hoc or on a schedule. Another day, another review though.
Next I opened up SQL Compare and change the project to compare against the recently created SQL Backup file with the Live Database.
And after executing the project, I see the one change to the Employee table. Simply awesome!
But lets go one step further. What if I use a backup from SQL Management Studio and compare it to a backup from SQL Compare? What will the outcome be? With the existing changes, I used SQL Server Management Studio to generate a traditional backup then changed the SQL Compare project to use the backup instead of the Live Database.
And the after the comparison, it worked as expected. I can find so many times where this just makes life simpler and allows me to get back to solving the problems and not messing with the tools.
Buy this product! I don’t know what else to say. If you have a need for a tool for comparing SQL Server databases, backups, snapshots, or Source Control Files, this will save you loads of time and do a great job at it. We didn’t even cover the Synchronization Wizard, but I hope just by showing you the power of the compare engine, you can imagine how nice it will be to have this tool automatically generate the changes into a SQL script it can run or open for you in Management Studio.