Team City is a Java based continuous integration and deployment server shipped as commercial software but free to use for up to 20 build configurations. It polls the source control repository for any changes providing build reports and statistics to the team.
Prerequisites for the set up
Download the latest available version of Team City from the official website download section.
In this article, SQL Server Management Studio will be used. Click here for download links and install instructions. In case you don’t have a SQL Server instance to connect to, download and install the free SQL Server 2014 Express from this link, which includes the above mentioned SQL Server Management Studio, and installs SQL Server on your local machine.
Please note that the necessary pre-requisite to implement the continuous integration (CI) in your database development process is to have a database under source control. For the purpose of this article, we will use the latest version of Visual SVN Server (Standard edition which is free) to manage a Subversion repository used in the CI process.
You will also need a web browser to set up and manage the Team City projects.
Installing the Team City continuous integration (CI) server
Start the Team City installation by double-clicking the Team City executable. Click the Next button to advance to the next step:
Read the license agreement and click the I Agree button to proceed to the next step:
Choose the location on your hard drive where the Team City will be installed. By default, it will be installed on the C drive, but you can redirect this to Program Files if you like. When the location is et, click the Next button to advance forward:
In the next step, the installation offers to install a build agent component (that can run as a Windows service) and the CI server component with the corresponding web service:
If the appropriate services are included in the installation, they can be managed in the same way as all other Windows services, using the Services management:
Clicking the Next button will initiate the installation process. Wait until the installation is finished:
After the selected components are installed, the next step would be to define a port which will be used to access the continuous integration (CI) server. By default, port 80 will be specified:
In case the specified port is busy, and you click the Next button, a warning message pops up with the information that the port should be changed:
Specify whether the local account or a system one will run the Team City server service. We’ll go with the user account for the purpose of this article:
Specify a domain and a user account credentials that will run the service:
Leave both checkboxes selected, so both services will be started, build agent and the Team City server:
The last step is to finish the installation, and close the installation wizard. You can leave the Open TeamCity Web UI after Setup is completed option checked:
By clicking the Finish button, with the above mentioned option checked, web UI of the Team City server will be opened in a web browser.
Some notes on Team City installation:
If the default port is assigned to the CI server, you can access its web UI via the following address: http://localhost/. Otherwise, the port must be specified
User account specified for the Team City service must have the following:
- Log on as service right (how to set this)
- Write permission for the Team City data directory
- Write permission for the Team City home directory (the one where Team City is installed locally)
- Read/Write permissions for the source control repository integrated with the Team City service
Starting up a web UI for the first time
If the option from the previous image remains unchecked, Team City web UI can be started manually from any web browser installed on a local machine.
To initiate the Team City web UI, follow the steps below:
Specify the following address: http://localhost:<port>/ where the <port> must be the same port that you have specified during the Team City installation process in any web browser (if the default port is used, it does not have to be specified):
Team City will need to create a folder where intermediary data will be stored. By default, the folder will be created in C:\ProgramData\JetBrains\TeamCity. However, this can be changed directly through the web Interface, before moving forward to the next step.
Clicking the Proceed button will create the folder on a specified location and open the next page, presenting the Database connection setup step. From the database type drop down list, we will select the MS SQL Server type.
If you are missing the MS SQL driver, download it from this link, and follow the instructions on how to set it up
Go back to the Team City web UI and click the Refresh JDBC drivers button:
The process should recognize the driver and show the following information:
Below the information about the loaded drivers, on the same page, host machine, SQL Server Instance and a dedicated database for the Team City service need to be specified. Please note that an empty database that will be used by the Team City service must be created within SQL Server Management Studio, before it is specified in the Database name field.
In this case, an empty database called TeamCity is created on a local instance of SQL Server.
The Database host [port] and the Database instance name fields are left blank since the service is running on a local machine and on a local instance of SQL Server:
Clicking the Proceed button requires some time for the environment to set up:
Once the components are set, a license agreement appears. Select the Accept license agreement checkbox, and click the Continue button:
Create administrator account which will have access to the projects configuration and management
Once this is done, the admin panel will be shown, where you can add additional information.
Setting up a project
The goal of the continuous integration (CI) in Team City is to integrate the source control repository used in the development stage with the CI server that will execute the specified steps (build, test, sync with another environment)
After the initial setup, the next thing would be to create a Team City project. Each project contains the following:
- Integration with the source control repository where developers are pushing changes, while working on a database in the development stage
- Steps that will be executed (build, test, sync, publish)
- Trigger that will be fired based on the specified settings (in this case, we want a trigger to fire on each committed change)
To create the project, click the Create project button under the Projects tab:
Specify a name of the project and click the Save button:
Next, you will need to integrate the previously created project with the repository where a development database is linked. To do so, switch to the VCS Roots tab, and click the Create VCS root button:
Specify the source control system, in our case Subversion:
Build configuration under the continuous integration (CI) project
In order to configure the CI project, a build configuration must be created. This can be done from the project settings, under the General settings tab, by clicking the Create build configuration button:
After specifying a name for the build configuration, assign previously created VCS (version control settings), by selecting it from the Attach existing VCS root drop down list, under the Version Control Settings tab, and clicking the Attach button:
From the Build Steps tab, specify the type of runner, that will perform the build process:
What is a runner?
Build runner is a part of Team City that allows integration with a specific build tool. Team City offers several built-in runners, but variety of 3rd party tools have Team City integration shipped as a plug-in. Find more about runners on this link.
For each of the steps we need to define (build, test, sync with another environment), the appropriate runner should be specified along with the corresponding settings. For example, one step should be to build a database using the “latest state“ from the repository, another step would be to specify unit tests to be executed against the built database, and another step to synchronize built database with another environment (e.g. QA) once all unit tests are passed.
Each of the above mentioned steps can be executed manually, by clicking the Run button:
However, to automate the process (since it should be initiated on each commit), there are several types of build triggers.
Build trigger represents a rule which initiates a new build on certain events. More about triggers can be found on this link.
For the purpose of this article, and for the purpose of implementing the continuous integration (CI) process in the database development we’ll add a VCS trigger by selecting it from the drop down list, under the Triggers tab:
A VCS trigger is a mechanism used when the CI process is monitoring changes on the specified source control repository, set to be fired on each committed change.
Only one VCS trigger is available for a single project. This means that if you have already set a VCS trigger and assigned it to a process, it won’t be available in the trigger list anymore. However, you can still specify other available type.
Build step
After specifying the trigger, the build step will be initiated on each committed change to the specified source control repository. This means that the build process will use the latest state of each database object on the repository to build a database (this includes the committed change that fires the trigger).
Please note that a new database will be built on each committed change to the repository. This means that a runner (plug-in) previously mentioned, that is selected to build a database, should be able to pull the latest state from the repository and build a database without breaking the referential integrity and taking care of the database object dependencies. Also, if there is any static data committed to the repository it should be properly inserted into a built database. The goal is to have a database where unit tests can be run against, and that can be synchronized in later step with the QA.
On other side, if, for any reason database is not built successfully, an error should appear informing the user about the failure, and not moving to the next step.
The runner builds a database on a previously specified server (e.g. test server). This server needs to be specified within the runner settings selected for building a database.
Test step
Once a database is built successfully, the process will move to the next step where unit tests can be executed against the database.
This is also handled by a runner (specified in a new step – test step). In this step, the user should be able to provide a set of tests that will run against the database, eventually to populate a database with a random test data, and to provide a mechanism to show test results or to send them to the development team.
Sync step
After unit tests are passed, a database can be synchronized with another environment (QA).
Again, a runner that is selected should be able to synchronize schema and data of the tested database with a database in another environment (QA).
The core use case presented in this article covers the basics of the continuous integration (CI) and that means to build a new database whenever a new change is detected on the source control repository, run specified unit tests against the database, and synchronize tested database to QA environment.
This allows continuous integration of SQL database changes with the CI server using the specified mechanism (runners or plug-ins) in each step, that are configurable and can be set according to the user needs, to run in a specified order.
He uses his spare time to play guitar, ride a bike and hang out with his friends. During winter, he likes skiing the most, but all other snow activities, too.
He is also author of various SQL Shack articles about SSIS packages and knowledgebase articles about ApexSQL Doc.
View all posts by Marko Radakovic