You want to deploy your SQL Server database changes with the push of a button and you prepared your Build & Deploy environments for that. The next step is to create a build configuration on your Continuous Integration server in a way that it automatically creates database builds when changes are committed to your source control system. In this post I’ll explain how I did that with our TeamCity CI Server.
In my last post I’ve shown a high level overview of the involved environments, how they relate to each other and explained the components of the Build & Deploy environments a bit more in depth. Now it’s time to create a build configuration with our CI Server, but first I’d like to extend the overview of our Database Deployment Pipeline with our Source Control System:
Zooming in on the Source Control and Build Environment parts, it looks like this:
In this overview the build configuration on our CI Server is watching a database source control repository. What we want is that when a change occurs in the database repository, for example when a database change is committed to source control, the CI Server detects it. Then we want it to automatically create a database build via one of its agents and store it in the Package Management’s repository (see the golden arrow for the flow in the above overview). Our deployment tool (Octopus Deploy) will then be able to pull the build from there. We do not worry about deploying the database changes via this build in this post yet.
NB: If you want to follow along, I’m assuming you already have your databases source controlled. If not, you can set up a database source control repository in your company’s source control system. You can learn how to do that in this previous post. In a later post I’m going to show you how to get your databases systematically in source control with your Database Deployment Pipelines already set up.
Creating a TeamCity Build Configuration
When you’re logged in to your TeamCity Administration page, you can start by creating a project for your databases first. That’s not mandatory by the way. Maybe you or your company want to have build configurations from your applications and databases in one project, that’s fine too. I would recommend talking to your devs first before deciding on this. They probably have much more experience with TeamCity build configurations and they probably have a way of doing things a certain way. Better yet, if you are using this guide, show it to them and ask to create a project and build configurations together! Anyways, I’ve created a separate project in TeamCity called Databases:
If you have read a previous post from me about setting up a Proof of Concept, you’ll recognize that I’m using the screenshots for creating a TeamCity project and the screenshots for creating a build configuration. That’s because I’m lazy :-). If you are unfamiliar with the TeamCity terminology I use in this post, you can find explanations of these terminologies in that same post.
Next I created a new build configuration:
Pay attention to the build number format. Redgate tools work only when builds are in a certain format (see: package version syntax). Again, it is advisable to decide on the build number format together with your developers.
The next page is about configuring the VCS settings (VCS stands for Version Control System and is the same as Source Control). This is where you configure the build configuration to watch the Database Source Control Repository.
Here you enter the location of your Database Source Control Repository (URL). As you can see, we are using Subversion (SVN) as our VCS/Source Control System.
The next page is for configuring the first build step in this configuration:
Since you’ve already installed Redgate’s DLM Automation Suite, you can now select the Red Gate SQL CI Build Runner type for the first step you configure. Give the step a meaningful name and a meaningful Package ID. It is advisable to select SQL LocalDB as your Temporary database server if you just started. It means that when this build step is executed, a temporary scratch database for some automated tests will be created on this instance. This instance lives on every build server where you installed Redgate’s DLM Automation Suite. If your database is referencing one or more other databases, it is not possible for the Build Runner to create a scratch databases if the temporary database server is LocalDB (those other databases don’t exist on your LocalDB instances). In that case you have to select a SQL Server instance, preferably one dedicated for this purpose.
Next you have to create an extra build step for publishing the NuGet package:
Finally you want this configuration to be triggered to automatically create a new build every time a change in the database repository occurs. This can be done by configuring a build trigger (5):
That’s it. It only takes you a few minutes to create a TeamCity Build Configuration for your databases. You’re probably eager to see the magic of automated database builds happen, so go ahead and make a change to your database repository (preferably by committing a change to it from your development environment)! How to set up the Deployment Environment and how to deploy this build with the push of a button, will be part of the next post.