SQL Compare in 10 minutes

I'm one of the authors of an open source eCommerce application, Suteki Shop, based on Microsoft's MVC Framework. We've been very lucky to have been chosen as a sample application by Microsoft, so if you're interested in the framework, there's a good chance you will hear about us. You can download the source code here, and there's a working version of the software here that also serves as a public site for the project.

Here's one of our customers, Jump the Gun:

The software is under constant development; there have been 43 changes in April alone. Unfortunately I haven't been very good at keeping the public site up to date. Last weekend I decided it was time to bite the bullet and deploy the latest version of the trunk. Building and deploying the assemblies, views and attendant files is easy; I just built the software on my workstation and copied them onto the server.

The database was a different problem though. In the interests of keeping things simple I maintain a script of the current development version of the database schema in the source repository. This is so that anyone who wants to try out Suteki Shop only has to fire up Visual Studio and run the script into an instance of SQL Server. The problem was that the script had moved on since the live version was deployed, but I didn't want to lose all my data by running in the script and dropping and re-creating all the tables. I needed a tool that could compare the script against the database and simply write alter table statements to upgrade the database to the latest schema.

I've been aware of Redgate software and their tool, SQL Compare, for some time, but never had an opportunity to try it out. I had a quick look on their website and it did indeed claim to do what I wanted. The rest of this article is a description of the next ten minutes. I was very impressed by how beautifully easy it was.

Downloading and installing the trial version of SQL Compare was very straightforward. Everything went smoothly.

When you launch SQL Compare you get the New Project screen where you can enter a source and target for a comparison. It's pretty flexible; you can choose a database, a backup, a snapshot or a scripts folder for either. I browsed to the folder holding my database creation script for the source, and my old database as the target.

I clicked compare now and got a screen saying that I had some errors parsing my scripts.

What?

On closer inspection it had picked up some non-schema statements (use master, create database, etc) in the database creation script, and the insert statements from static data insertion script that lives in the same folder. I guessed that it would just ignore these if I clicked 'Continue Without Resolving Errors', so I that's what I did.

It very quickly executed the comparison and displayed the 'Comparison Successfully Completed' screen below.

I clicked OK and was presented with this complex screen which shows exactly how my script differs from my database. In fact there was only one real change in the Basket table where I'd previously added a foreign key for Country. The diff is very clear and shows quite neatly what is going to happen when the changes are applied.

I then floundered for a minute or so. I expected to see a big red 'Apply Changes' button or something similar. It took me a while to figure out that I needed to click 'Synchronization Wizard'. This gave me the option of backing up my target database, doing another comparison after the changes had been applied and saving the synchronisation script.

I didn't need any to do any of them, so I left them unchecked and clicked 'Next'.

You then get to see all the objects that are affected by the synchronisation. It looks like if you have any further dependencies you have a chance to inspect them at this stage. I didn't so just clicked 'Next' again.

The next page shows the details of what is going to happen. Here is the summary tab. It looked correct, so I just clicked 'Synchronize Now'.

Success! It all worked very nicely.

OK, so it was hardly a huge challenge, but for a ten minute out-of-the-box experience you can't get much better than this. I would heartily recommend checkout out SQL Compare if you have this kind of requirement.