Why am I doing this?

At work, I have been working on getting our applications into the build pipeline (in TeamCity). I have been making good progress on the application side but a lot of our database migrations were still kind of manual. I would inevitably end up with a failing build because I forgot to open up SQLCompare, compare the target to the source database, create a migration script, and run the script. It is not a huge effort to do this because SQLCompare makes this easy, and it's a lot better than manually writing scripts, but surely I could automate this, right? Plus I was already using SQL Source Control, which means that I can deploy database changes from source control rather than comparing two actual databases (I love this). This also means I can branch my databases and have different builds pointing at different branches just like my application code.

Why use SQL Compare and SQL Source Control?

Why not just write migration scripts by hand? We can certainly do this and I have done it before. For .NET applications I like using DBUp to help automate things. But tools like SQL Compare can save a lot of time and help reduce errors. SQL Compare can create migration scripts for you, and with SQL Source Control I can use SQL Compare to synchronize databases based on the state in source control (Git). This means there is a single source of the schema, I can branch the database, I can fit this into my build pipeline, and it fits more naturally into my workflow.

What do I need?

• TeamCity
• SQL Toolbelt Essentials (this comes with SQL Source Control)
• SQL Compare (Toolbelt Essentials comes with SQL Compare too, but you can buy it separately).

Get your database into source control

If you don't have your database under source control, checkout this StackOverflow question. Let's just say it's a really good idea, and Red Gate helps us do that with SQL Source Control.

The build steps

I wanted to be able to push changes to my database's remote git repository without triggering the database build until after the application was ready to run with those changes. Basically I wanted to avoid breaking the test environment. I also wanted the application build to depend on the database build such that changes to the application would trigger synchronizing the database first.

This is how I wanted it to go.

1. Push changes to the database schema
2. Push changes the to application
3. The application build starts
   1. The database changes are pulled down from source control
   2. The target database schema is migrated according to the difference between source and the target database
   3. The application changes are pulled down
   4. The application is compiled
   5. The tests are run
4. The build passes
5. The application is deployed to the target
6. ????
7. Profit!

TeamCity build configurations

We can do this with TeamCity, taking advantage of something called Snapshot Dependencies..

Here is what I did

I have a Web API 2.0 based application and a SQL Server database it runs on. I setup a project for the web API with a build config for the web API and a separate config for the database. The most important bits are that:

• The application build has a snapshot dependency on the database configuration.
• The database configuration does not have a trigger. If I wanted to sync the database with source whenever there were changes, I would add a VCS trigger. But if I sync the database the application might break if its corresponding changes have not deployed, yet.

Application configuration

I won't get into the details of the build steps, but I will say that I am using FAKE to build using MSBuild, which also runs my tests.

The important bit is the snapshot dependency.

Which sets up a build chain where you can see the order in which all-the-things will happen.

Database configuration

This is where SQL Compare comes in. This was trickier than it looked.

First I had to install SQL Compare. OK simple enough, except that I had to install it under the same account that runs the TeamCity agents or the licensing wouldn't work. So first I had to change the service account for the TeamCity Build Agent, and give that user access to the TeamCity folders. I then logged in to the TeamCity server using that account, and installed SQL Compare (you can use the 14-day trial of the Toolbelt Essentials). Once I had that installed I ran the SQL Compare program and activated my license (while still logged in as the TeamCity agent service account). If you are using the 14-day trial you will have the option to activate that and it should work just fine.

SQL Compare can run as an executable to which you can pass arguments. This makes it really easy to incorporate in build steps. First I figured out how to use the SQL Compare command line arguments. SQL Compare gives us options to compare and synchronize databases using scripts created by SQL Source Control. You can follow the guide here.

I did this as a batch script so that I could pass it arguments, like the user and password, from TeamCity.

Here is what my batch script looks like:

echo on
set user=%1
set password=%2
set workingdir=%3
set server=%4
set database=%5
"C:\Program Files (x86)\Red Gate\SQL Compare 12\sqlcompare" ^
/verbose ^
/scr1:"%workingdir%" ^
/S2:%server% ^
/U2:%user% ^
/P2:%password% ^
/db2:"%database%" ^
/Include:Identical ^
/sync

OK so what is going on here?

I have five variables

• user -- this is the user that will run the migration scripts. I gave mine db_owner, but you might need to give it sys admin if it needs to create user accounts.
• password -- make it long and complex and store it securely, kids!
• workingdir -- this is where TeamCity will store all of the scripts after it pulls them down from the repository. This is a parameter that TeamCity gives you access to. We will see that later.
• server -- the target database server
• database -- the target database
• scr1: source of the database scripts
• S2: the target server
• U2: a user with appropriate privileges on the target server database
• P2: stealthispassword
• db2: the target database
• Include:Identical -- use this if you don't want to get error messages when the databases are identical
• sync: synchronize the structure of the target database with the source

In my database's source folder I added a folder called build and put my batch script in it. I then added it to source control so I can make TeamCity run this script from source. This way I don't have to depend on a file stored on the server ... somewhere. Plus this is the same way I do our other builds.

The build step

This is pretty straight forward.

• I am using the Command Line type build step.
• I called it "sync database with source"
• I left the working directory as is because we want to be in the root of the repository and that is where it is.

Command executable

This is where we can use the TeamCity parameter reference to the working directory (%system.teamcity.build.workingDir%). TeamCity makes it easy to use these parameters. There is a little icon next to the textboxes that looks like a list. Click that and you can select from a huge number of parameters.

%system.teamcity.build.workingDir%\build\synchronize_with_source.cmd

Command parameters

These are the arguments I will pass to my batch script. Most of these I setup as parameters in my build configuration.

%env.SQLCompareUser% 
%env.SQLCompareUserPassword% 
%system.teamcity.build.workingDir% 
%env.DatabaseServer% 
%env.Database%

(Notice how I don't have to store my sensitive information in source control, kids!)

And that is it.

Failure Conditions

Using a command line like this, TeamCity might not detect that a build step failed and so will report it as Success. If you see this happening, go to the Failure Conditions page and check "an error message is logged by the build runner" and see if that helps.

Thoughts and Improvements

This is a really easy way to get your databases synchronized but does cost a considerable amount of money. I think the time savings alone will be worth it, though.

This is great and all but there are bigger and better tools from Red Gate. They have a DLM Automation suite that looks pretty awesome, but I am happy with what I have for now.

Note: I do not work for Red Gate or Jet Brains or anything, I just like their products.

Please note that you do not have to get buy a SQL Toolbelt in order to get SQL Compare. You can buy it as a standalone, which is good because you don't need all of that other stuff of you'll only be using it in your build.

If you have any questions or need any help, please tweet me or you can leave a comment if the thingy is working.