Continuous Deployments for SQL Server – Part 1

Okay so your using a SQL Server database at work but you haven’t yet put the database into Source Control, here are some reasons as to why you want to do this:-

Source control your database
Trust me your going to want to do this if you haven’t already, it’s a good practice, it may save your skin some day, yes you can take backups which again is a good practice, here is an excellent article on why you want to do this:- Why put your database into source control?.

Okay now that we wish to put our database into source control – we still use Subversion at work so I downloaded Subversion and installed it locally as well as TortoiseSVN.
I have 2 databases called STOCK_DB_DESIGN and STOCK_DB_DESIGN_ORIGINAL within SQL Sever, the former had a few schema changes, imagine this is Development(STOCK_DB_DESIGN) and Staging(STOCK_DB_DESIGN_ORIGINAL).

I created a local repository within Subversion and downloaded RedGate’s Database Lifecycle Management Products.

This tool comes with SQL Compare and SQL Data compare as well as a host of other awesome SQL Server tools which you can find out about at the above link.

For this post I’ll be using Redgate’s SQL Source Control which adds a tab to SQL Server Management Studio and looks something like this:-

So lets go ahead and add a database to our source control using this tool, Select the option above, and then Next

I selected Subversion because at work we still use it, but you can use Git also and others, click Next

I then give it a repository URL, I already created a repository within Subversion for my database and added the URL like above, click Next and that’s it, our database is now linked to source control. Now to get the database scripts into Subversion we need to right click on the Database in question within SQL Server Management studio like so and select Commit Changes to source control:-

This will then add all of the database objects to SQL Source Control, once that’ completed, go into windows explorer and I use TortoiseSVN I can right click within my local SVN repository folder and then select Checkout or get latest version and this will result in the following folders being populated:-

And when I go into say the tables folder I’ll see the following:-

And that’s all there is to source controlling your SQL Server database. If I make change to an object say a table I’ll see whats changed locally in SQL Management Studio and I’ll know I still need to commit the change as seen below:-

In Part 2 I’ll show you how to compare schemas on both databases to work out what changes are required, and how to script them using the tool.