T-SQL: A Week Of Continuous Integration Day 2

This is the second 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


Making Use of a Build Server

So we've seen in the last post how we can start scripting databases but we could still do so much more. Like many others, our dev team run continuous integration on a build server with TeamCity at the hub of things and there was genuine excitement at the prospect at pulling our database development in line with the rest of the code base. With my database scripts in SVN, we could make regular, automated "builds" of a database which would validate our schema while also giving us a basis to add a testing framework in the future. For me, this would be another real step forward and would solve a couple of issues. For example, imagine a database with hundreds of stored procedures referencing a particular column in a table. A drop or rename of this column would render all the stored procedures invalid and having a regular build of the schema would quickly identify any issues. It would also encourage deprecated objects to be cleared up - good practice when keeping a lean database for maintenance and performance. All i needed was to make use of the existing build server and provision an instance of SQL Server to act as the target for deploying the database.

So i'm sold. TeamCity can pull out the scripts from SVN and then using software such as FinalBuilder, I was able to loop through the directories and execute the sql scripts. Fine...in theory.

However, when you come to wanting to build the database from these scripts, there is a problem in that when using a simple powershell script (with Invoke-SQLcmd) to build the db in that dependencies are not honoured. SQL requires that objects are deployed in a certain order (functions need tables to be created first, tables need to be created before its relations etc) and the only way to create a db from a scripts directory is to manually build (and maintain) a deploy script - kind of against the idea of Continuous Integration. So while the Powershell scripting works nicely for my production environment, i'd need a different approach to get continuous integration working.

But at this stage, the plan had developed further and we now had the following components:

1) Database Objects Scripted
2) Scripts stored in Source Control
3) A database server provisioned for continuous integration
4) A build server which could execute the steps.

T-SQL: A Week Of Continuous Integration Day 1

This is the first post of a 5 part series outlining my journey to getting SQL Server databases under source control and into a continuous integration environment. Hopefully, others will find this useful.

Powershell & Scripting objects

This all stemmed from 2 things. Firstly, my stated yearly goal to get my hands dirty with powershell and secondly a desire to take regular scripts of my production databases for recovery purposes (should my backups let me down). The idea being that I could re-create the database from a set of scripts stored on a central repository on any given server. So, I wrote a powershell script to query the production database model and output these scripts to the filesystem, scheduled it through the SQL Agent and we were off and running.

The benefit of using Powershell to do this was that you could essentially customise your own folder structure and also use it across your entire SQL Server estate. You could easily schedule the scripts to run a central server, loop through a list of sql servers and output all the databases to a central filesystem so you have all your scripts in one place. Oh, and its free. FREE!! And we all like free stuff - a pretty compelling case to implement such a thing.

But this got me thinking. Our production database is obviously crucial to us and is fully backed up with various levels of redundancy and avenues for restore should a catastrophic failure occur. The main concern for me was our development database which one could argue is at the highest risk and possibly has the most valuable code. In this instance, there was a shared development database (more on shared vs dedicated later) which we could apply the same principle to. Even better, we would be able to extend the functionality to hook into our chosen source control provider (SVN) and make regular check-ins. Ok, so the job would still be scheduled and therefore we'd be exposed to potential dataloss but we'd be in a much better place than before.

Shared vs Dedicated development model

I've mentioned above and in a previous post about the different database development models. There are two common approaches for database development, shared and dedicated. Dedicated means each developer works off of a local copy of the database much more akin to traditional development. Changes can be made and tested on a local instance before checking back into your source control repository. Shared means there is a single central database which all developers connect to and make changes to. I've used this approach when dealing with large integrated systems dependent on other technologies/services such as a website where its not really practical to have the entire system on your local machine. Essentially, the shared database is "the truth" and as a result, including the schema in source control can be overlooked.

So at this stage, we have the looked at the following:

1) Database scripted to the filesystem
2) Scripts stored in source control

Obviously, this is great but there is much more that can be done and i'll cover more in my next post.

Powershell: change start location of shell

I'm starting to build up a repository of powershell scripts and I have been getting a little frustrated that every time I open up the PS shell, it defaults to my user directory:


I then have to "cd" to the location of my scripts to start working. Back a couple years ago, I remember going through some steps to personalise my PS shell (although obviously that knowledge didn't stick!) and so I thought i'd find out how to change the default dir to be my PS scripts folder.

This article suggested that I just needed to edit the Microsoft.Powershell_profile.ps1 file in the WindowsPowerShell directory in My Documents. Easy.

Off i went to that directory, only to discover it wasn't there - not hidden or protected it just didn't exist. Thinking that the location may be different on my setup, I ran the $Profile command in PS and it confirmed that it should be in C:\Documents and Settings\RichardB\My Documents\WindowsPowerShell



Then it dawned on me. Perhaps I just need to create the directory and file myself. So I navigated to My Documents and created the WindowsPowerShell folder and a Microsoft.Powershell_profile.ps1 file. The final step was to edit the file to change the behaviour I was after:

Set-Location C:\PS



A quick restart of Powershell and there we have it. A bit of time saved when using Powershell.