Example: Setting up a database build in TeamCity - TeamCity plugin

This example demonstrates setting up a simple automated TeamCity build to deploy database changes.

In the example, we will set up a build to deploy the latest revision of the source controlled GizmoDev development database to the GizmoTest testing database.

The example has six steps:

1. Installing the Red Gate TeamCity plugin
2. Creating a new TeamCity project
3. Attaching a VCS root
4. Adding and configuring the Red Gate build runner
5. Adding a VCS trigger
6. Making a database change

1. Installing the Red Gate TeamCity plugin

In this example, we will use the Red Gate build runner plugin for TeamCity.

To install the plugin:

1. On a computer with TeamCity installed, download the plugin.

   This downloads the openMe.zip file which contains RedGateSQLServer.zip.

2. Move the RedGateSQLServer.zip file to the plugins folder of the TeamCity data directory. If TeamCity was installed to its default location, the plugins folder is located at:

   C:\Users\All Users\JetBrains\TeamCity\plugins.

   Note: the location of the plugins folder may be different if TeamCity was not installed in its default location. For more information, see: TeamCity Data Directory (TeamCity documentation).

3. In the Windows Start menu search box, type services.msc, and then press Enter:

   

4. In the list of services, right-click TeamCity Web Server, and then click Restart:

   

   You can now use the Red Gate build runner in TeamCity.

2. Creating a new TeamCity project

1. In TeamCity Administration, under Projects, click Create project:

   

2. On the Create New Project page, enter a name for the project.

   In this example, we will call the project GizmoTest update:

   

3. Click Create.
4. Under Build Configurations, click Create build configuration:

   

5. On the Create Build Configuration page, enter a name for the build.

   In this example, we will name the build Integration build:

   

6. Click VCS settings.

3. Attaching a VCS root

Once you have created a project, you need to specify the location (VCS root) of your development database in source control.

In this example, we are using SQL Source Control to link our GizmoDev development database to a Subversion (SVN) server.

To attach a VCS root:

1. Under Version Control Settings, click Create and attach new VCS root:

   

2. In the Type of VCS box, select the source control system you are using with your development database.

   In this example, we are using Subversion.

3. Under SVN Connection Settings, in the URL box, we specify the location we want TeamCity to monitor for changes.

   In SQL Source Control, the location of a database in source control is displayed on the Setup tab:

   

   You can copy the location by right-clicking the path, and then clicking Copy:

   

   In this example, our location is https://lt-tomc.red-gate.com/svn/Databases/GizmoDev/

   

   If authentication is required for your source control server, you must specify a user name and password.

4. Click Test connection.

   TeamCity tests the connection to the source control location.

5. If the connection is successful, click Save.

   The VCS root is created.

4. Adding and configuring the Red Gate build runner

1. Click Add Build Step.
2. In the Runner type box, select Red Gate (SQL Server):

   

   If your database is located in a subfolder of your VCS root, you must specify a path in the Database folder box. For example, if your VCS root is http://subversion.example.com/ and your database folder location is http://subversion.example.com/AdventureWorks, enter AdventureWorks.

   In this example, our VCS root is our database location, so we do not need to specify a database folder.

3. Under Continuous Integration setup, specify connection details for your SQL Server.
4. Select Update a database on every build, and specify the name of the database you want the build to update.

   In this example, we want to update the GizmoTest database:

   

   Note that the database you specify must be an existing database on the server; the build runner does not create the database for you.

5. Click Save.

5. Adding a VCS trigger

If we want TeamCity to trigger a build when changes are detected in source control, we need to add a VCS trigger:

1. On the Build Configuration page, under Configuration Steps, click Build Triggering:

   

2. On the Build Triggering page, click Add new trigger:

   

3. From the Add new trigger box, select VCS Trigger:

   

4. Click Save.

   TeamCity will now run a build when a check-in to your VCS root is detected.

6. Making a database change

Now that the build is set up in TeamCity, we can make a database change to trigger a build.

In this example, we will add a new Image column to our Gizmos table:

We then commit the change using SQL Source Control:

Once the change is commited to source control, TeamCity picks up the change and runs a build. You can view the status of the build by clicking Projects in TeamCity:

For a summary of the log messages printed by the build, or for troubleshooting a failing build, move your cursor over , and then click Build log: