Introduction
In my previous article, we have seen how to implement a source control system for Microsoft SQL Server databases.
The next step in database project maintenance after implementing a source control system is to have a continuous integration system in place. You might be pondering what is the need for continuous integration when you have a version control system already.
A source control/version control system helps in managing the change sets to the source code of the project and maintaining them in a central location accessible to all the developers in your team. Whereas, a continuous integration system helps in building the project after each change made to the source code. This process of building the project after each change helps in identifying code errors at the earliest and reduces the time involved in identifying which exact change has resulted in the error. It therefore improves the quality of releases.
There are many popular open source and premium continuous integration tools like Buddy, Jenkins, TeamCity, GitLab CI, etc., but as part of this course, let us look at how to setup a continuous integration system with JetBrains TeamCity.
Prerequisites
In order to begin with this step, you should be ready with a source control system for your database project. In case you do not have it yet, please look at the previous article that I wrote.
If you are already done with it, well and good. We can jump to the next step.
2. SQL Server Database Continuous Integration using Github and TeamCity
a. Setting Up TeamCity
In order to use TeamCity for continuous integration of your database project, you need to have a running TeamCity server. Ideally, you would have a server already in place for your web/mobile application projects and you may use the same server for the database projects as well. If you do not have one yet, you have to setup a server.
You can either use the cloud version of TeamCity by signing in from JetBrains website or if you want to setup TeamCity on your own server, you have to download and install TeamCity Server and Build Agent from JetBrains’ website.
The download, installation and setup of TeamCity Server and Build Agent is easy. A detailed explanation of the process is currently out of scope of this tutorial but there are several step-by-step tutorials available on the internet. In fact, there is comprehensive documentation available on the official website itself. Here is the link. You can use the default installation settings and default ports.
Follow the instructions written on the website and you should end up with this screen on your browser:
You should see that the server is up and running and an agent is connected. Click on ‘Create Project’ button to add your database project to TeamCity.
b. Creating a New Project in TeamCity
On the next page, we can see that there are options to create a project manually or we can connect an existing remote repository and create a project out of it. As per our objective, we will need to select the option to create a project from remote repository as we have our database project on Github.
Paste the link to your github repository in the ‘Repository URL’ textbox and provide your github credentials if they are required to access the repository. Make sure that the credentials are correct and click on ‘Proceed’ button. This will verify the connection to your remote repository and on the next screen, you can specify the name for your project and build configuration.
Click on ‘Proceed’ button to move to the next screen.
c. Configuring Build Steps
TeamCity will scan the repository will list the build steps that automatically detected during the scan based on the project type. Since we are maintaining the project in a Visual Studio solution and as we are going to make changes only in the solution, select the checkbox beside ‘Visual Studio (sln)’ and click on ‘Use Selected’ button.
This will create the build step. We need to configure the target properties and to do that, click on ‘Edit’ link on the newly created build step.
The runner type and solution name are automatically populated as they are imported from the remote repository. We need to select the version of Visual Studio that the database project is being built from. Click on the combo-box for selecting the version of Visual Studio and select the version of Visual Studio from the dropdown. In my case, I have selected Visual Studio 2019 as I have my database project being developed in Visual Studio 2019. You might have to select Visual Studio 2017 or Visual Studio 2015 based on the version of Visual Studio that you are using.
Once you select the version of your Visual Studio, click on ‘Save’ button and the changes to the build step will be saved.
We can also specify the ‘Configuration’ and ‘Command line parameters’ on this screen but we will come back to that later.
Click on the ‘Projects’ tab on navigation bar at the top of the page and you should see that a project is created for your database along with a build configuration with the names you specified while creating the project.
d. Running Build Configuration
Since it is a newly created project, there are no builds to be displayed yet. So, go ahead and click the ‘Run’ button to run a build from the repository. As soon as you click the button, a line item will be added to the builds with a status as ‘Running’.
The build will take a few seconds to minutes depending on the size of your project and the status will be updated to Success in case of a successful build or Failed if there are any errors in code. In this case, as there are no errors in the repository that we linked, the build is a Success.
e. Creating a Publish Profile
We must now create a publish profile for our database project to be used with TeamCity. This profile will contain the target database settings, version, name of script and target connection string. This can be done from Visual Studio and here is how to do it.
Right click on the database project and click on ‘Publish’. On the window that pops up, we can specify the target database connection by clicking on the Edit button and choosing from the list of available connections.
Once the connection string is defined, we would need to save the profile as an XML file in the project. Click on ‘Save Profile As…’ button and choose a name for the profile. Since the current scenario is for a development server, we name it as dev.publish.xml. Saving it will add a new file to the database project which is visible under the Solution Explorer.
f. Configuring Publish Profile in TeamCity
Next, back in TeamCity, we must specify that this publish profile needs to be used to publish the database. Open TeamCity Web dashboard and go to your project-> Build and click on Edit in the build step.
On the Configuration page, input this command into the Configuration textbox and click on Save button.
/p:SqlPublishProfilePath=”<name of publish profile>”
Replace the name of publish profile with the name that you used in the previous step. In this case, the command looks like:
/p:SqlPublishProfilePath=”dev.publish.xml”
With this, our TeamCity, Github and Visual Studio connection setup is complete.
g. Testing Continuous Integration
From the very definition of what Continuous Integration is, any minor changes pushed into our Github repository should trigger a new build in our TeamCity server. Let us see what happens when we make a small change and push it to Github and merge with the master branch.
For this test, let us add a new stored procedure in our database project and push it to master branch of our Github repository.
Commit this change and push it to the master branch of our Github repository.
After the push is completed, wait for a few seconds and look at the dashboard of TeamCity server. A build is automatically triggered; and its status displayed saying that the build is pending.
It will automatically carry out the build process and the status updated to Success/Failed depending on the outcome of the build. In this case, the build is a success.
If you click on the ‘Success’ link and switch to the ‘Changes’ tab, it will show the number of changes made since the last build.
It shows the complete log fetched from our repository after connecting it to TeamCity.
With that, it is evident that our continuous integration is setup successfully. We built the project without going through the build procedure of Visual Studio. You can try doing that a few times to see for yourself.
h. Erroneous Builds
In the previous section, we saw that the project is built successfully upon pushing any changes to our remote repository. But what if the code checked in has an error? What happens in that case? Let us induce an error in our code and see what happens.
Let us add an incorrect statement in the newly created stored procedure and push code to our repository.
Commit and push this to the master branch.
Wait for few seconds to allow TeamCity to build the project and it will display that there is a compilation error.
Click on the error to see full details about the error, the changes that caused the error, the details of where the commit came from.
This makes it easy to identify where the root cause of the build error is located. We can also set to receive email notifications whenever an error occurs in TeamCity. That again is easy to do and currently out of scope of this course.
Points of Interest
With this, the continuous integration system setup and testing is complete for our database project.
But in every project, you will have to replicate the project’s build to additional environments such as the Test environment, Staging/Pre-production environment and finally the Production environment. Although you can achieve this by using separate publish profiles for each server and use the publish configuration from TeamCity to publish databases into different environments, it is not the most ideal and automated way to do it. That is where Continuous Delivery/Continuous Deployment comes into the picture.
Next: SQL Server Database Continuous Deployment using TeamCity and Octopus
History
- 20th October, 2019: Initial draft of this article
