SQL Change Automation 3

This tutorial takes you through each stage of setting up a database pipeline using TeamCity and the SQL Change Automation TeamCity plugin. You'll use the plugin to build, test and sync a SQL Change Automation project.

1. Prerequisites

Make sure you have the following:

An existing TeamCity server (version 7 or later).
An existing TeamCity Build agent.
An existing SQL Change Automation project in source control.
An existing SQL Change Automation Test Project in source control.
An existing TeamCity project. See Creating and Editing Projects.

2. Install the SQL Change Automation plugin

Download the SQL Change Automation TeamCity plugin
Copy the zip file to the TeamCity data directory plugin folder.

For more information, see Installing additional TeamCity plugins.

3. Add a build step

You'll add the build step using one of Redgate's build runners for TeamCity. For more information on build runners, see Configuring Build Steps.

From the Build Configuration Settings menu, select Build Steps.
Click the Add build step button.
From the Runner type drop-down, select Redgate SQL Change Automation Build.
Under Database project, select Build a SQL Change Automation project. Enter the path to your SQL Change Automation project relative to your checkout directory.
Under Build artifact, enter a name for the NuGet Package ID. This is the name of the NuGet package you'll create. The name must be unique and can't contain spaces.
(Optional) Under Temporary database server, select SQL Server if you have features in your database that are not supported by LocalDB.
By default SQL Change Automation uses LocalDB to recreate and validate a temporary version of your database. The temporary database is dropped once the build is complete.
(Optional) SQL Change Automation can include SQL Doc database documentation in the NuGet package that it builds. To select this option, under Database Documentation, click Include database documentation.

Including database documentation will increase the size of the NuGet package and the time it takes to build, particularly for large databases.
The documentation is stored in db > docs > main.html in the NuGet package. If you're using DLM Dashboard version 1.6.3 or later and have set up DLM Dashboard integration, you can also view the documentation directly from DLM Dashboard. See SQL Change Automation integration (DLM Dashboard documentation) for more details. For help understanding SQL Doc documentation, see What's in the documentation?
(Optional) Click Show advanced options. Under SQL Change Automation Version, select Specific and provide the specific version of SQL Change Automation that will be used. By default, the latest version is used.
Click Save.
(Optional) If you want TeamCity to include the nuget package in its Artifacts you should edit the Artifacts path in the Build general settings to capture nuget files and click Save.

4. Add a Test step

The test step allows TeamCity to run tSQLt tests included in your database. For more info read Use tSQLt with SQL Change Automation projects.

From the Build Configuration Settings menu, select Build Steps.
Click the Add build step button.
From the Runner type drop-down, select Redgate SQL Change Automation Test.
Under Test source, select Test a SQL Change Automation project using tSQLt tests. Enter the path to your SQL Change Automation test project relative to your checkout directory.
Under Temporary database, select SQL LocalDB. If you need a custom SQL Server, select SQL Server and provide additional connection details.
For the Run tests option, select Run every test.
(Optional) Select the checkbox Populate the database with data before testing if you intend to automatically populate the database with test data.
You can optionally provide a SQL Data Generator project (.sqlgen) file.
Click Save

5. Run a build

Open your TeamCIty project and click the Run button.
See Configuring VCS Triggers to learn how to automatically start a new build each time TeamCity detects new changes in the configured VCS roots.
To display the build status, click Projects.
To show a summary of the log messages printed by the build, move your cursor over the Success drop-down button, and then click Build log.
To view the NuGet package, click Artifacts.
The build artifact is the name TeamCity gives to the output of a build step. In this example, the NuGet package is the artifact, and it's stored on TeamCity's server until we're ready to deploy it.

6. Add a Sync step

From the Build Configuration Settings menu, select Build Steps.
Click the Add build step button.
From the Runner type drop-down, select Redgate SQL Change Automation Sync.
Under Build Artifact, inser the NuGet Package ID. This must be the same ID used to configure the Build step.
Under Target database, enter the name of the target server and database you want to update and the SQL Server credentials if needed.
Click Save.

7. Publish the package to a NuGet feed

Now that you've finished testing, you can publish the package to a NuGet feed. You can then use Octopus Deploy with SQL Change Automation to manage deployments.

You can use TeamCity as a NuGet server to save you having to set up your own repository. For more details, see Using TeamCity as a NuGet Server.

Once you enabled TeamCity triggering a build will publish the package to the NuGet feed automatically.

If you're using a different release management tool, check the help documentation provided by that tool vendor.