This tutorial takes you through each stage of setting up a database deployment pipeline using TFS and Redgate tools. 

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

1. Install software and download demo files 

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 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 Build plugin installed on the machine running the build agent. See Setting up the TFS Build plugin.
  

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. Create a DLM Automation project file

Create a DLM Automation project file in the WidgetShop database scripts folder. This folder was created by SQL Source Control.

1. In Visual Studio, from the DLM Automation menu, select Set up DLM Automation:
   

2. Browse to WidgetShop > WidgetShop > Database > ScriptsFolder. The folder name is displayed in the DLM Automation project name text box: 
   

3. Rename the project CIsteps and click OK. 
   You've created the DLM Automation project, but it's not checked in yet. You'll do that once you've edited the continuous integration steps.

4. On the CIsteps.sqlciproj page, at the Package name text box, replace CIsteps with WidgetShopLatest. 
   This is the name of the NuGet package DLM Automation is going to create. It must be unique and can't contain spaces.

   When you're thinking of a package name to use in your own environment, bear in mind that it's going to be deployed to other databases. A generic name that describes what you're deploying is better than one that's specific to the build step itself, or the database. For example, if your database is called Development, this wouldn't make sense as a package name deployed to your production database.

5. At the Version text box, use the default version number 1.0.0. 
   
   TFS manages minor increments to package versions by adding a fourth digit during the TFS build process. In this example, the package version will be 1.0.0.1. 

DLM Automation supports four steps that perform different continuous integration tasks; build, test, sync and publish. When you created the CIsteps.sqlciproj file, a build step was created automatically and added to the DLM Automation steps table:

Each project can only have one build step. This step will validate the creation script and generate the NuGet package. As part of this validation, DLM Automation creates a temporary version of the database using LocalDB, which is dropped once the build is complete.

If you want to change any of the default settings, you can edit the build step:

1. In the DLM Automation steps sections, from the Build step row, click Edit.

2. If you're using Redgate's DLM Dashboard (version 1.4.4.313 or later), DLM Automation can send schema information to it every time the build step runs. Under the DLM Dashboard integration section:

   1. Enable the Send schema information to DLM Dashboard check box.
   2. Enter the name or IP address of the machine hosting DLM Dashboard.
   3. Enter the DLM Dashboard port number. The default port is 19528.
   Once you deploy changes (for example, by running the sync step), DLM Dashboard:
   • recognizes the deployed schema from the information DLM Automation sent during the build step
   • adds the schema to its list of recognized schemas, with the name <packageId-packageVersion>, for example, WidgetShop1.0
   • labels the schema with the DLM Automation icon 
   • labels the schema as an update , not drift 
   For more information about how DLM Automation works with DLM Dashboard, see DLM Automation integration.

3. DLM Automation can include SQL Doc database documentation in the NuGet package that it builds. To select this option, under Database documentation, enable the Include database documentation check box.

   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?

4. As part of the validation process, DLM Automation creates a temporary version of the database using LocalDB, which is dropped once the build is complete. If you want to run full-text queries against your tables, you'll need to use a different temporary database because LocalDB doesn't support Full-Text Search. For more information on LocalDB, see SQL Server 2014 Express Local DB (MSDN article). To specify a temporary database:

   1. Click the Advanced tab.

   2. Select SQL Server.

   3. Enter the details of the temporary server in the fields that appear.

5. When you've finished editing the build step click OK.
6. Click Save.

At this stage you could check in changes and configure the TFS build definition, then make a change to the local copy of the database and trigger a build.

Add a test step

Add a test step so that every time there's a new build of the WidgetDevelopment database, DLM Automation will create a temporary version of the database using LocalDB and run tSQLt tests (such as static analysis, unit or integration tests) against it. 

1. In the DLM Automation steps section, from the Add a step drop-down list, select Test:
   
   The Add test step dialog is displayed.
2. In the tSQL tests section, leave Run every test selected.
   If you only wanted to run tests in a single class, or a single test within a class, you'd select Run a specific test or class and enter [testclass].[testname].
3. Select the Generate test data check box.
4. Select Use a SQL Data Generator project file and browse to WidgetShop > WidgetShop > Database > WidgetTestData.sqlgen. This was the file created for you and provided in the WidgetDevelopmentDatabaseCIDemo.zip file.
   The location must be relative to the CIsteps.sqlciproj file.
5. Leave Stop the TFS build process if this step fails selected. 
   This makes sure additional stops are only run if the test step is completed without errors.
   The dialog should look similar to this:
   
6. Click Add. The test step is added to the DLM Automation steps table:
   
7. Press Ctrl+S to save changes.

If you've already configured the TFS build definition, you can now:

1. Check in changes to the DLM Automation project file.
2. Update your local copy of the database and check in changes to trigger a build.
3. Click Open Drop Folder. The NuGet package, test reports file and logs are displayed.
   

Add a sync step

Add a sync step that will update an existing database with the contents of the WidgetDevelopment database after the build and test steps have been completed successfully. 

1. In the DLM Automation steps section, from the Add a step drop-down list, select Sync.
2. Under Database server, at the Server text box, browse to the SQL Server instance where the WidgetCI database is located. 
   WidgetCI was the database created when you completed the instructions in Creating databases.
3. Leave SQL Server authentication selected and enter your username and password. 
4. Click Test connection. This should verify that DLM Automation can access the server on which the sync database exists.
5. From the Database drop-down list, select WidgetCI.  
   The dialog should look similar to this:
   
6. Click Add. The sync step is added to the Additional steps table.
7. Press Ctrl+S to save changes.
   

If you've already configured the TFS build definition, you can now:

1. Check in changes to the DLM Automation project file.
2. Update your local copy of the database and check in changes to trigger a build.
3. In SSMS, open the WidgetShopCI database to verify that it's been synchronized.

Add a publish step

Add a publish step that will publish the package to the NuGet feed of a release management tool such as Octopus Deploy. 

1. In the DLM Automation steps section, from the Add a step drop-down list, select Publish.

2. At the URL text box, enter the fully-qualified URL for your NuGet feed. 

3. If you're using a private NuGet feed, enter the API key.  

4. Click Add. The publish step is added to the Additional steps table:
   

5. Press Ctrl+S to save changes.
   

If you've already configured the TFS build definition, you can now:

8. Check in changes

Once you've finished editing the DLM Automation project file, check it into version control:

9. Configure the TFS build definition

Include the DLM Automation project file in the TFS build definition in Visual Studio. 

1. In Team Explorer, click Home and then click Builds.

2. Click New Build Definition:
   

    

3. Click General and in the Build definition name text box, type WidgetDevelopment.
   
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. Under 1. Required > Items to Build > Projects to Build, browse to and select the CIsteps.sqlciproj file. 

   You'll need to filter the Browse dialog to show Items of type: MSBuild Project files (*.*proj). 

   The page should look similar to this:
   

8. Press Ctrl+S to save the build definition.

You can now update your local copy of the database and check in changes to trigger a build. 

Verify that the NuGet feed in your release management tool contains the latest package. 

10. Next steps: using release management tools

Redgate provides SQL Release, part of the DLM Automation Suite, which works with your release management tool to deploy the package containing the changes you've made to your production servers.

For more information, see SQL Release.

If you're using Octopus Deploy as your release management tool, you can use the SQL Release step templates to help you set up your database projects. See Octopus Deploy step templates reference.