How can I include my database in the continuous integration process?

Question

Please excuse me, today is my first day trying to setup a CI environment using TeamCity. I'm developing an ASP.NET/Sql Server app and so far so good. MSBuild is my provider.

I'd like to know what the options are when it comes to automatically making sure changes to my local database are uploaded to the test server as part of the integration process. Now I'm not too sure I want to go in the direction of DBGhost to automatically sync schema changes, I'd be happy to just replace the database on the test server with a copy of my local database.

What are my options here?

edit: further investigation shows the RedGate SQL products are good for this. They arent cheap though. They have a nice whitepaper on using their products within a continuous integration process: http://downloads.red-gate.com/HelpPDF/ContinuousIntegrationForDatabasesUsingRedGateSQLTools.pdf

Answer 1

First of all, I think your database should be included in your source control strategy. Basic principles to achieve this have been beautifully summed up in K. Scott Allen series. You can also check Evolutionary Database Design by Martin Fowler. And for more practical information, check the answers to How should you build your database from source control?.

As I wrote in my answer, I maintain two sets of scripts : one set that can build the database from scratch, and one that can upgrade a given database from one version to the last. As part of a CI strategy :

• The database on the CI server should be rebuilt from scratch using these scripts. You can then check that your scripts are in working condition.
• In a perfect world, I should be able to perform an upgrade from version N-1 to version N of the database, and compare the obtained schema to the schema of the previous database. I am still working on it...
• Test data should be injected in the database, allowing for unit testing of your application to be performed (you could use bcp for this).