You’ve convinced Management and your colleagues your company should implement Continuous Integration for databases, and now you are ready to actually set this up for your production databases. Where to start? How do you tackle this? In this series I’ll explain how I did it.
Our set of environments
We have two DTAP (Development, Test, Acceptance and Production) streets, one for our backend databases and one for our manufacturing database. They are in two different locations and serve different purposes. For both, shared development was done on databases running in our development environment (a SQL Server instance we call D-DTAP) and change and rollback scripts were manually crafted by developers. These changes were promoted to our test environment (a SQL Server instance we call T-DTAP) for testing and then were run in our (user) acceptance environment (you guessed it – A-DTAP) by our Release Manager before getting deployed to production.
Each DTAP street is used by one team who have different approaches and processes in developing their software and their databases. I started implementing Continuous Integration for databases for one of the teams.
Our Source Control System
We use SVN and GitHub as source control systems for our applications and for our database change scripts, but we decided to use just SVN as the source control system for our databases. For now. Since we can also source control static data, which can be sensitive, we’d rather have that data not in a public cloud. And, as we already source controlled our change scripts in SVN, we figured the transition to source controlling our databases using Redgate’s SQL Source Control would go more smoothly.
Our Build Server a.k.a. CI Server
Our Build Server, which from now on I am going to call our CI Server, is a TeamCity server with 8 build agents running on their own servers. The reason I am now going to name this a CI Server instead of a Build Server is that it is going to do much more besides just building in the future (for example, automated testing.)
Our Deployment Server
We use Octopus Deploy as our deployment tool for our (automated) deployments. With this tool we can deploy releases to the different environments with the push of a button. We already used this tool for deploying releases of our applications, but now we are also able to use the same method for databases with Redgate’s DLM Automation Suite.
Where to start
My developer colleagues and I agreed to first source control the database schemas and set up Continuous Integration for them to make the transition go more smoothly. Source controlling static data would be done at a later stage, as well as providing sample data, taking care of environment-specific data and how to handle the same database in different geographical locations. We also agreed to start with the smallest and least complex databases to get us started and build up experience as quickly as possible.
Now the stage is set, it has to be prepared to make Continuous Integration for databases possible. In the next post I’ll show you how I did that.
- Build on your existing environment (if you have one)
- Start simple, only source control the database schema the first time (if you haven’t already source controlled your databases)
- Start simple, pick the least complex database to source control and to setup for Continuous Integration