How do I include my database in the continuous integration process?
Please excuse me, today is my first day trying to set up a CI environment using TeamCity. I am developing an ASP.NET/Sql Server Application and so far so good. MSBuild is my provider.
I would like to know what the options are when it comes to automatically ensuring that changes to my local database are uploaded to the test server as part of the integration process. Now I'm not too sure if 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 research shows that RedGate SQL products are good for this. They are not cheap. They have a good document on using their products in a continuous integration process: http://downloads.red-gate.com/HelpPDF/ContinuousIntegrationForDatabasesUsingRedGateSQLTools.pdf
source to share
First of all, I think your database should be included in your source code control strategy. The basic principles for achieving this have been beautifully summarized in K. Scott Allen . You can also check out Evolutionary Database Design by Martin Fowler . And for more practical information check out the answers at How should you create your database from a source control? ...
As I wrote in my answer , I support two sets of scripts: one set that can create a database from scratch, and one that can upgrade a given database from one version to the latest. As part of the CI strategy:
- The database on the CI server must be rebuilt from scratch using these scripts. Then you can check that your scripts are in working order.
- In an ideal world, I should be able to upgrade from version N-1 to version N of the database and compare the resulting schema with the schema of the previous database. I am still working on it ...
- Test data must be entered into the database to allow unit testing of your application (you can use bcp for this).
source to share