Continuous integration is the business of making sure that everything in your software project builds and gets tested automatically. Each code commit kicks off a build, and the build has a suite of unit and integration tests run against it. This means you get quick validation of your changes, and notification of problems.
Martin Fowler wrote the definitive article on continuous integration back in 2000, so it is not a new idea. In fact, it is standard practice for a lot of development teams working on application code. But for the database layer, it is a little harder.
Along with benefits like change tracking and change sharing, source control offers you a single location for the "one true version" of your code. It is the location you deploy from in continuous integration. But database code is not like application code. There is not a set of files you can copy, and you do not compile it. So the first problem is that there is nothing to put in source control.
Then there is the fact that SQL is declarative, and DDL statements modify the current state of a database. So every time you make a change, you must account for referential integrity, and ensure data is preserved.
In practice, this means creating lots of migration scripts.
An ideal solution would allow you to get your database into source control alongside application code, and automate creation of change scripts. This would include both the database schema and any necessary static data and be deployed from there by the build system.
This is now possible with Red Gate's SQL Developer Bundle and Perforce.
The first thing to do is to get the database into source control. The Developer bundle does not include a source control system. Instead, the SQL Source Control add-in for SSMS connects with your existing source control system. So to get set up, you link the database to your source control system:
You enter details of your source control repository, link the database, and then commit the objects:
You can also choose to source control your static data. To do this, context-click the database in the Object Explorer, and click Link/Unlink Static Data…
When the time comes to deploy, you need to get the database out of source control again. To support this, SQL Compare and SQL Data Compare have command-line interfaces you can use on a build server, for example with MS Build, NAnt, or TeamCity. So you can write a simple script to get a copy of the latest database version from source control and deploy it to a testing server, creating detailed reports of the migration.
These tools provide the source control and deployment automation that let you bring database code in line with the application development process.
If you want to try this out for yourself, have a look at the SQL Developer Bundle and watch the 30-Minute ON DEMAND Webinar on Smart Database Version Control with Perforce and Red Gate.
Good luck and let us know how it works.
Michael Francis is the Brand Manager for SQL Source Control at Red Gate Software. Michael's number one aim is to make SQL developers and DBAs aware that they can easily connect their source control system (Perforce and all others) to SQL Server, ending the days of non-source controlled database development. In his spare time Michael enjoys spending time with his family and climbing mountains.