T-SQL: A Week Of Continuous Integration Day 3

This is the third post of a 5 part series outlining my journey to getting SQL Server databases under source control and into a continuous integration environment.

The other posts are:
Day 1 - Powershell & Scripting Objects
Day 2 - Making Use of a Build Server

Database Developer Toolkits

Although the path to Continuous Integration had taking a knock back with the limitations of my powershell solution, i was determined to succeed. Initially, i'd wanted to cobble something together on a shoestring harnessing existing technologies to make the solution portable and cheap. However as time went by, the importance and benefits of CI became clearer and clearer and I felt it was time to look into other products (both 3rd party and MS) to see if there was a solution out there for us. After all, this can't be anything new and there's no need to reinvent the wheel.

There were 2 main issues that I wanted any solution to address:

1) keeing our source control up to date easily
2) deploying database solutions to a Continuous Integration server

Based upon this, I quickly found 2 potential avenues to explore.

1) Visual Studio Database Edition
2) Red-Gate Developer Toolbelt

Visual Studio Database Edition 2008

I was fortunate in that I was already in possession of a licensed copy of Visual Studio and indeed, a few of the guys had used the database projects (dbproj) for SQL development. It also has diff capabilities for both schema and data which would allow you to generate a deployment script. Fundamentally though, I believe that database development should be done in SQL Server Management Studio (SSMS) and i've outlined a few of the differences I see between the two as a database development IDE.

SQL Server Management Studio (SSMS)

more natual for a Database Developer

Can develop "online" against a database

immediate feedback from intellisense

easy to view dependencies in sql

edit multiple databases in one session

edit multiple objects in one connection

no native support for source control

Free

Visual Studio 2008 Database Edition

version dependencies - tied to a particular version

have to develop against scripts and apply them

solutions allow grouping of all dev project types (c#, ssrs, ssas, ssis, dbproj etc)

builds fail if there are dependencies on other dbs

doesn't support shared development model

can manually deploy to different servers

integrates into source control (with plugins for some vendors)

Visual Studio Database Edition is a good product in its own right and really does check a lot of the boxes for me with development. Being able to bundle all your projects as one solution is really nice and the diff capability is something that I believe MS really should incorperate into SSMS. However, from a continuous integration perspective, the use of VS just doesn't work for me. Besides the cost and heavyweight install requirements for running it on a build server (i don't think there is a simple SDK which works), the major show stopper for me is the disconnecting nature of development with SQL objects. Working with individual scripts and then applying the changes manually just isn't a viable solution for me. A database professionals natural habitat is SSMS and unless MS make database projects available through this IDE then it will the case for using Visual Studio Database Edition will always struggle to stack up.

Red-Gate SQL Developer Toolbelt

The red-gate solution is based upon their SQL Developer Toolbelt and it solves both the issues highlighted above. The first componenent is the SQL Source Control product which is essentially a plug-in to SQL Server Management Studio and allows you to manage your source control from the same environment you are developing in. You can essentially make changes to objects from within SSMS and you can then right click the object and commit them. Simple but effective, it even has colours to suggest when a change needs to be sync'd to the repository while it supports both the shared and dedicated development models. With my belief that development should happen in SSMS, this is a huge tick in the box as you don't have to jump through any hoops to keep your database in sync with source control.

SQL Source Control scripts out the database to file (including data!) in an intuitive folder structure which leads nicely into generating a build script to deploy to a continuous integration server. Red-Gate offer a "Compare" tool which allows "diff"ing of databases as well as generating change scripts and deploying a database from source control. Importantly, all this functionality is available from the command prompt and could therefore be scheduled on a buildserver. The ability to easily generate a build script and execute was the key ingrediant missing from the Powershell solution and essentially leaves us with a solution which answers all the questions (and a few more besides!) and is an intuitive end-to-end development process which fits nicely into my way of working.

We are now in a position to:
1) Database Objects Scripted and maintained in realtime
2) Scripts stored in Source Control
3) A database server provisioned for continuous integration
4) A build server which could execute the steps.
5) Software which can deploy the database from source control.