This example describes how to set up a database deployment pipeline using TFS and Redgate tools. 

You'll use the DLM Automation TFS MSBuild scripts to build, test, sync and publish a NuGet package to your release management tool.

Make sure you have the following: 

• Access to Team Foundation Server (TFS) 2010, 2012, 2013 or 2015. This tutorial series uses TFS 2012.

• Visual Studio  2010, 2012, 2013 or 2015. This tutorial series uses Visual Studio 2012.
  For details of which TFS and Visual Studio versions can work together, see Compatibility between Team Foundation clients and Team Foundation Server (MSDN article). 

   

  If you want to publish test results in MSTest format, you'll need to have Visual Studio Premium, Ultimate or Test Pro installed on the machine running your build agent.

• SQL Server Management Studio (SSMS) 2008 (RTM, SP1, SP2, R2, R2 SP1, and R2 SP2), 2012, or 2014 (CTP2)
• Access to a SQL Server 2012 or 2014 instance with common language runtime (CLR) integration enabled. For more information, see Enabling CLR integration (MSDN article). This tutorial uses SQL Server 2012.
• Redgate SQL Source Control installed on the same machine as SSMS. See SQL Source Control
• The WidgetShop demo files. Download the WidgetDevelopmentDatabaseCIDemo.zip file, and extract the contents.
• .NET 4.5 installed on your local machine. This program is installed automatically with Visual Studio 2012 and 2013. 
  If you're using Visual Studio 2010, you'll need to install it manually. See Microsoft .NET Framework 4.5 (Microsoft Download Center)
• DLM Automation, installed on the machine running the build agent. See Installing (DLM Automation documentation). 
• Redgate's DLM Automation TFS MSBuild scripts copied to the machine running the build agent. See Setting up the TFS MSBuild scripts.

2. Create a team project in TFS

1. In Visual Studio, click Connect to Team Foundation Server:
   
   
   
2. Select or add your TFS server and then click Connect. 
   For details of how to connect to an instance of TFS for the first time, Connect to Team Projects in TFS (MSDN article)

3. From the File menu, select New > Team Project. The Specify Team Project Settings page is displayed.

4. Enter the project name WidgetShop:
   
5. Click Next.
6. On the Select a Process Template page, leave the default process template selected and click Next.
7. On the Specify Source Control Settings page, select Create an empty source control folder and click Next.
8. On the Confirm Team Project Settings page, click Finish to start creating the project. 
   Once creation is complete, the Team Project Created page is displayed. 
9. Click Close.

CREATE DATABASE WidgetCI
GO
USE WidgetCI
GO
ALTER DATABASE WidgetCI SET TRUSTWORTHY ON

6. Set up the build in Visual Studio

1. In Team Explorer, click  Home and then click Builds.
2. Click New Build Definition:
   
3. On the General tab:
   1. in the Build definition name text box, type WidgetDevelopment.

   2. under Queue processing, select Disabled. We'll enable this once configuration is complete.

   
4. Click Trigger and select Continuous Integration:
   
5. Click Workspace. Make sure the $/WidgetShop source control folder is displayed. If it isn't:
   1. Click in the Source Control Folder field, and then browse to and select the WidgetShop folder.
   2. Click OK.
   

6. Click Build Defaults. Under Staging location, select Copy build output to the following drop folder and enter the shared location that you want to use as your drop folder.
   
   

7. Click Process. You need to configure a number of build process parameters on this page.

   1. Under 1. Required > Items to Build > Projects to Build, click the browse button. Click Add and select DLMAutomation.proj, then click OK on each of the two dialogs. 
      If you can't see the project file, select MSBuild Project files (*.*proj) from the Items of type drop-down list.

   2. Under 2. Basic > Build Number Format, replace the default format with 1.0$(Rev:.r)
      This means your builds will be numbered 1.0.1, 1.0.2, 1.0.3, and so on. The second '.' is added automatically before the final digit, because it's defined in (Rev:.r). 
   3. Under 3. Advanced > Agent Settings, click the  expand arrow. Click Name Filter and select the TFS server name from the drop-down list, for example, dptc12 - Agent1.
      

   4. Scroll down to the 3. Advanced properties list and click MSBuild Platform. Select X86 from the drop-down list.

   The Process page should look similar to this: 
   
8. Press Ctrl+S to save the build definition.
   

9. Under Default Template near the top of the page, click Show details. The build process XAML file and version control path are displayed:  

   

10. You need to configure the XAML file so that TFS passes the build number and binaries directory as variables. To do this, complete the instructions in Passing TFS Build properties to MSBuild using TFS 2010 or later.

The build definition is now set up.

7. Configure the properties files

Properties files contain commands that configure your TFS build steps. The properties files are:

• DLMAutomation.global.properties
• DLMAutomation.build.properties 
• DLMAutomation.test.properties
• DLMAutomation.sync.properties
• DLMAutomation.publish.properties

In this example, we're going to configure the global and build properties files. 

Configure the global properties file

Global commands are required by every TFS build step. You can use most of the default option settings.

1. In Visual Studio, go to Source Control Explorer and open $\WidgetShop\dlmAutomation-steps\DLMAutomation.global.properties.

2. Set WidgetPackage as the package ID. For example,<packageId>WidgetPackage</packageId>
   This is the name of the package you're going to create.

3. At the licenseSerialKey option, enter your DLM Automation license key if you have one. For example, <licenseSerialKey>123-456-789012-ABCD</licenseSerialKey>
   If the license has been activated on this machine already, the key is displayed. If you don't have a serial number, leave this option blank and a 28 day free trial will start automatically.

4. Press Ctrl+S to save the file.

Configure the build properties file

Build commands create and validate the SQL creation script used to generate the NuGet package. For more information, see Build step command options.

If you're using Redgate's DLM Dashboard (version 1.4.4.313 or later), you can send information to it about the packages DLM Automation builds. Once the packages are deployed, they're displayed on the dashboard as recognized schema. For more information, see DLM Automation integration (DLM Dashboard documentation).

1. Open $\WidgetShop\dlmAutomation-steps\DLMAutomation.build.properties.
2. At the scriptsFolder option enter the path to the database scripts folder in source control relative to the project file. For example:
   <scriptsFolder>.\WidgetShop\Database\ScriptsFolder</scriptsFolder> 
3. If you're using DLM Dashboard:

   1. at the dlmDashboardHost option enter the name or IP address of the machine hosting DLM Dashboard. For example:
      <dlmDashboardHost>localhost</dlmDashboard>

   2. at the dlmDashboardPort option, enter the port number. Ignore this if you're running the DLM Dashboard on default port 19528.

4. If you want to include SQL Doc database documentation in the NuGet package, at the includeDocs option, change the value from False to True so that it looks like this:
   <includeDocs>True</includeDocs>

   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 DLM Automation integration (DLM Dashboard documentation). For help understanding SQL Doc documentation, see What's in the documentation? 

5. Press Ctrl+S to save the file.

In Source Control Explorer, select the edited property files, right-click and select Check in Pending Changes. 

Configure the test properties file 

Test commands generate test data using SQL Data Generator and run tSQLt tests (such as static analysis, unit or integration tests) against a temporary version of your database. You'll use the WidgetTestData.sqlgen file extracted from the downloaded WidgetDevelopmentDatabaseCIDemo.zip file. For more information, see Test step command line options.

The database schema for WidgetShopDevelopment includes four basic SQL Cop tests that will check for:

• procedures named SP_
• procedures using dynamic SQL without sp_executesql
• procedures with @@Identity
• procedures with SET ROWCOUNT

To configure the file:

1. Open $\WidgetShop\dlmAutomation-steps\DLMAutomation.test.properties.
2. Set the disabled option to False. For example, <disabled>False</disabled>
3. Set the generateTestData option to True. For example, <generateTestData>True</generateTestData>

4. At the dataGeneratorProjectFile option, enter the path to the SQL Data Generator file in source control relative to the project file. For example, <dataGeneratorProjectFile>.\WidgetShop\Database\WidgetTestData.sqlgen</dataGeneratorProjectFile>
   This will populate the temporary database with test data from the file. For more information about SQL Data Generator, see SQL Data Generator 3 documentation. 

5. Press Ctrl+S to save the file.

Test results are output in JUnit XML format and saved in the drop folder you specified when setting up your build definition.

In Source Control Explorer, select the edited property files, right-click and select Check in Pending Changes.

 Configure the sync properties file

Properties files contain commands that configure your TFS build steps. You've already configured the global and build properties files earlier (see Set up the build in Visual Studio).

Sync commands update another database (in this example, the WidgetShopCI database) with the latest version in source control. For more information, see Using the sync command.

Configure the publish properties file

Properties files contain commands that configure your TFS build steps. You've already configured the global and build properties files in the earlier (see Set up the build in Visual Studio).

Trigger a build

Make a change to the WidgetDevelopment database in SSMS, and use SQL Source Control to commit the change. 

A build will start automatically: 

Once the test step is complete, click Open Drop Folder. The NuGet package, test reports file and logs are displayed:

Once the publish step is complete, the package is available on your NuGet feed.

Release management

 Now you can deploy your database from the NuGet package to Octopus Deploy using SQL Release.

The easiest way to do this is using DLM Automation step templates. 

1. Copy the DLM Automation step templates to your Octopus Deploy library

Once you've copied the DLM Automation step templates to your Octopus Deploy library, they're available whenever you add a process step in an Octopus Deploy project:

1. Go to the DLM Automation step templates on the Octopus Deploy library website.
2. In this example, we'll use the "Redgate - Create Database Release" and "Redgate - Deploy from Database Release" step templates.
   Click on the "Redgate - Create Database Release" template.
3. Click Copy to clipboard:
   

You're now ready to paste the script from your clipboard into your Octopus Deploy library:

1. In Octopus Deploy, at the top of the page, click Library.
2. On the Step templates tab, click Import.
3. In the Import window, paste the copied template into the empty field.
4. Click Import.
5. Click Save.
6. Repeat steps 1 to 5 to copy the "Redgate - Deploy from Database Release" step template in the same way.

2. Create an Octopus Deploy project

1. In Octopus Deploy, click Projects and All.
2. Click Add project.
3. In the Name field, enter Widget Deployment.
4. Click Save.

You'll now add a series of deployment process steps to your Octopus Deploy project.

3. Add the "Download and extract database package" step

This step picks up the NuGet package of the database schema you're going to deploy.

1. Set up your NuGet package feed by doing one of the following:
   • register your existing external NuGet package feed with Octopus. For more details, see Adding external package feeds.
   • configure your build server to push packages to the Octopus built-in repository. For more details, see Using the built-in repository.
2. In the Widget Deployment project, on the Process tab, click Add step and select Deploy a NuGet package.
3. In the Step name field, enter Download and extract database package. 
4. In the Machine roles field, enter db-server and press Enter.
   This must match the role you assigned to the Tentacle.
5. In the NuGet feed field, select either the name of the external feed you registered when you set up your NuGet feed, or the Octopus Server (built-in) repository.
6. In the NuGet package ID field, enter the name of the package without the version number. For example, if the package was called WidgetShopLatest.0.1.nupkg, you'd only enter Widget.
   When the package is generated, NuGet package manager automatically adds a number. If we included it here, Octopus would only deploy the package that matched that name and version number. By removing the number, we're telling Octopus to always look for the latest package with that name.
7. In the Environments field, select Production.
   If you leave this blank, the step will be accessible to all environments. 
8. Click Save.

4. Add the "Create database release" step

This step creates the database deployment resources, including the Update.sql script.

1. On the project Process tab, click Add step and Redgate - Create Database Release.
2. In the Machine roles field, enter db-server and press Enter.
   This must match the role you assigned to the Tentacle.
3. In the Export path field, enter the path the database deployment resources will be exported to.
   This path will later be used in the "Deploy from Database Release" step. It must be accessible to all tentacles used in database deployment steps.
4. In the Database package step field, select Download and extract database package.
5. In the Target SQL Server instance field, enter the fully qualified SQL Server instance for the database you're deploying to.
6. In the Target database name field, enter the name of the database you're deploying to.
7. In the Username (optional) and Password (optional) fields, enter the SQL Server username and password used to connect to the target database.
   If you leave these blank, Windows authentication will be used to connect to the target database.
8. In the Environments field, select Production.
   If you leave this blank, the step will be accessible to all environments. 
9. Click Save.

5.  Add the "Review database deployment resources" step

This step pauses deployment to let you review the database deployment resources, including the Changes.html report, before allowing deployment to go ahead.

1. On the project Process tab, click Add step and select Manual intervention required.
2. In the Step name field, enter Review database deployment resources.

3. In the Instructions field, copy and paste this text:

   Please review the schema and static data changes, warnings and SQL change script in 'Changes.html'.

4. In the Environments field, select Production.
   If you leave this blank, the step will be accessible to all environments. 

5. Click Save.

6. Add the "Deploy from database release" step

This step uses the database deployment resources to deploy the database changes.

1. On the project Process tab, click Add step and select Redgate - Deploy from Database Release.
2. In the Machine roles field, enter db-server and press Enter.
   This must match the role you assigned to the Tentacle.
3. In the Export path field, enter the path the database deployment resources will be exported to.
   This must match the export path you entered in 4. Add the 'Create database release' step. 
4. In the Database package step field, select Download and extract database package.
5. In the Target SQL Server instance field, enter the fully qualified SQL Server instance for the database you're deploying to.
6. In the Target database name field, enter the name of the database you're deploying to.
7. In the Username (optional) and Password (optional) fields, enter the SQL Server username and password used to connect to the target database.
   If you leave these blank, Windows authentication will be used to connect to the target database.
8. In the Environments field, select Production.
   If you leave this blank, the step will be accessible to all environments. 
9. Click Save.

You've finished setting up the project steps. The Process tab should look like this:
 

7. Create a release

Now all the steps are set up, you can run your deployment process to create a release:

1. Create a blank database called WidgetProduction:
   1. Open SQL Server Management Studio (SSMS).
   2. Click New Query.

   3. Execute the following SQL query to create a database:

      CREATE DATABASE WidgetProduction
      GO
      USE WidgetProduction
      GO

2. In the Widget Deployment project, on the Process tab, click Create release.
   This page lets you add an optional release note. 
3. Click Save. 
4. Click Deploy to Production (or if there's more than one environment, click Deploy and select Production).
5. Click Deploy Now.
   As the deployment process runs, Octopus Deploy shows the task progress list. The deployment pauses so you can review the database deployment resources:
   
6. Click Changes.html to download the Change report.
   Use the report to review the update script, warnings, and details of what'll be added, removed or modified if you go ahead with deployment. 
7. In Octopus Deploy, click assign to me and, in Notes, enter a comment to say you've reviewed the database deployment resources.
8. If you're happy with the report, click Proceed.
   When the deployment is complete, the Task progress page looks like this:
   

You've now completed the deployment of the database package.

What now?

Other DLM Automation step templates are available on the Octopus Deploy Library website:

• "Redgate - Deploy from Database" deploys the schema of a source database to a target database without a review step.
• "Redgate - Deploy from Package" deploys a NuGet package containing a database schema to a target database without a review step.

For more information, see Octopus Deploy step templates reference.