The first step of continuous integration is to build your software from your source code. The idea is that each time the source code changes you should build/compile your code from scratch to ensure that you can.

Why bother?

This is the lowest bar of all your tests, there is no point running your unit, UI or load tests if the thing won’t compile. There is no point creating a deployment artifact if the code won’t deploy. Hence, this is the first test that you will run, and ideally you will do this automatically every time anyone in your team commits to source control.

Databases are no different.

But you don’t “compile” a database? I say you do (ish). Compilation is the process of taking your source code and turning it into the bits and bytes of your working piece of software. If you keep your database source code in a source control system, probably as a set of .sql files, each time you modify them you should build your database. If you cannot build your database, from scratch, from your source code something is wrong. You have a broken build.

Building your database every time you commit to source control will provide fast feedback if you ever break the SQL syntax or break some types of dependencies. This is the sort of stuff that merge tools often manage to mess up. (Are your commas at the start or end of the line?) Also, annoyingly SQL Server Management Studio lets you break dependencies.

A problem

So what if your database depends on another database?

How do you build a database from scratch if it has a dependency on another database/linked server? I’ve been asked this question three times in the last fortnight so I thought that this time, rather than responding by email I’d write a blog post so that everyone can benefit.

How to solve it

Scenario:

DB1 depends on DB2.

DB2 depends on DB3.

DB3 depends on DB1.

I want to build one of these databases from my source code.

Yup – I have a circular dependency. The solution I’m going to suggest should support that.

You need to create a persistent environment that will contain a copy of each of the databases in your web of dependencies. Normally this will be done on a dedicated SQL instance. These databases need to be in line with source control as they will require the objects which other databases are dependent on.

Creating this environment may be a combination of manual steps and using SQL Compare. It’s likely to be challenging as you’ll need to add objects in an appropriate order. If you have a really complicated web of circular dependencies my heart goes out to you… Take solace in the fact this is a step you only need to go through once. And take a lesson from this exercise that your architecture may have room for improvement. And wouldn’t the whole process have been easier if your architecture was documented and your developers stuck to the intended design?

An obvious question is who is going to fund another SQL instance? Hopefully the well documented benefits of CI and automated testing will be enough to justify the cost. Have you ever shipped a regression by mistake that you could have written an automated test for? How much did your last failed deploy cost you?

Once you have this integration environment up and running and it is in line with source control – fantastic. Revoke everyone’s write access to it. (And I mean everyone.) The only thing making changes to this environment should be your build server.

Your build process should do the following as soon as the source code for any of the databases in the environment changes:

1. Delete all the objects on the corresponding database in the integration environment.
2. Re-build the database from source control, from scratch.

If using Redgate SQLCI you can achieve this using the BUILD command with the /temporaryDatabaseServer and /temporaryDatabaseName switches.

Effectively what you are doing is creating your entire dependency web in an isolated/disposable environment and you are punching holes in it in order that you can run your builds for each individual database inside that hole.

Further extensions

Once you achieve this, why stop at automating your builds – you have just created a platform for automated testing. You can use something like tSQLt or Redgate SQL Test with Redgate SQL CI TEST commands to automate unit tests. If you have to manually run a set of regression tests imagine how much time you would save and how much more confidence you would have if these were automated and auditable?

Limitations

This approach, where you create your entire environment to handle dependencies, has the downside that you cannot run concurrent builds. If you try to build two databases that depend upon each other at the same time you’ll have problems. The solutions to this problem are:

1. Build more than one integration environment.
2. Have your build process create the entire integration environment from scratch for every build.
3. Sort out your architecture.

You might also want to bear in mind that linked servers require the transaction isolation level to be set to READ COMMITTED. You can do that using the SQL Compare command line switch /til:<transaction isolation level>. You can call this from SQL CI using the /additionalCompareArgs switch.

If you have a better way to solve this problem please leave a comment.