Almost every development team uses Source Control Management for application development. There are several excellent Version Control Systems (VCS) which make the task a breeze. Likewise, Continuous Integration is also increasing in importance. By contrast, the adoption of both practices with database development has been slow.

Of course there are special difficulties that account for this. One problem is that database objects don’t exist as simple files that can be versioned; you can't just pick them up and place them in a target location when you want to deploy them. You’ve got to consider the very nature of databases: You’ve got real live data to deal with, and the ramifications of screwing up a deployment are pretty severe.

Some special tooling is in order and fortunately the planets are starting to align in such a fashion that some of my favourite products work very nicely together to serve just the purpose we’re after. Last year I wrote about Red Gate’s SQL Source Control as a very excellent way of versioning databases and followed up later in the year about automating deployments with TeamCity.

Let’s take these tools – plus a few more from the Red Gate product suite – and , at last, make one-click database deployment a reality.

Get your database under source control

First and foremost, none of this is going to make any sense whatsoever unless you get your database under source control. As I wrote a while back in The unnecessary evil of shared database development,

Databases are an essential component of many of the applications we build and so it is crazy talk to argue that they don't need to be in a VCS. Without the DB in source control we end up with a fragmented, partially complete picture of what an application is. We lose the ability to say “Here – this is the state of code over time”, as only part of the picture exists. We also lose all the productivity advantages; not just of being able to rollback when required, but of being able to integrate with the work of the rest of the team. Sometimes we can get around the integration problem by using a shared development databases but, well, go back and read the article I've just mentioned for everything that’s wrong with that.

For us, VCS is the “source of truth” for automated deployments from a continuous integration environment. It’s where application life begins and it’s where TeamCity is going to turn to when it publishes both the web application and the database.

Pretty much everything in the database needs to be in the VCS. This includes database objects such as stored procedures, views, triggers and of course, tables. Because SQL Source Control neatly files each object type away into its own folder, all we need to do is take a quick look in the Subversion repository for the project and we’ll see just what sort of stuff is going to go in there:

In order to publish the changes, we need a way to
* pull the scripts for each database object from source control
* create a script that will make the changes to the target database
* execute the script to make the required changes against the target database.

Depending on the changes, we could be dropping objects, altering permissions, changing data lengths or potentially any other conceivable database change. The main thing is that VCS is our source of truth and we need to ensure the target environment is changed to match this exactly.

This is where SQL Compare comes into play. I’ve long been a user of this on the desktop as a means of automating releases and ensuring environments were absolutely identical. But there are two features the current version has which make it perfect for our purposes here; unattended command line execution and syncing from a VCS source, the latter of which necessitates the Professional version.

Full article...