This tutorial takes you through each stage of setting up a database-build pipeline using Visual Studio Team Services (formerly known as Visual Studio Online) and the VSTS DLM Automation Build extension. You'll use the extension to build, test and sync a SQL Server database.

1. Prerequisites

Make sure you have the following:

• Access to Visual Studio Team Services (VSTS).
• SQL Server Management Studio (SSMS) 2008 or later.
• 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.

Build agent location

The VSTS DLM Automation Build extension will work with a hosted build agent or a build agent running on your own environment. There are different issues to consider depending on which you choose:

• The sync step of this build task synchronizes a SQL Server database to the contents of a package. This SQL Server must be accessible from the build agent. For a hosted build agent, this will typically be an Azure database. For a build agent running on your own environment, you can use a SQL Server instance visible from the build agent. This will typically be your local SQL Server.
• The hosted build-extension cannot generate SQL Data Generator data for use by the test step. To overcome this, use a build agent running on your own environment and install DLM Automation on that machine.

2. Create a project in Visual Studio

1. In VSTS , go to your Default Collection.
2. Under Recent projects & teams, click New.
   
3. Name the new project WidgetShop. Use any Process template, such as Agile. For Version Control, use Team Foundation Version Control. (Git can also be used but this is not covered in this tutorial.)
4. Click Create.

3. Create example databases

For this tutorial, you'll create two databases:

• WidgetDevelopment: you'll use this for development
• WidgetCI: this is a blank database that, once you've configured the sync step, will be automatically updated every time a change is made to WidgetDevelopment

To create WidgetDevelopment:

1. Open SQL Server Management Studio (SSMS).
2. From the File menu, select Open > File.

3. Browse to the WidgetDevelopment.sql file (extracted from the WidgetDevelopmentDatabaseCIDemo folder) and click Open.

   Using Azure SQL Databases

   If you want to put your WidgetDevelopment and WidgetCI databases in Azure, you'll need to make a few edits to the WidgetDevelopment creation script:

   1. Delete all content before the first CREATE TABLE statement.
   2. Find the CREATE ASSEMBLY [tSQLtCLR] statement, and change WITH PERMISSION_SET = EXTERNAL_ACCESS to WITH PERMISSION _SET = SAFE. 

   You'll also have to create the WidgetDevelopment and WidgetCI databases yourself in the portal. Basic-tier databases (eg 100MB) are fine.

4. Click Execute to create the database.

To create WidgetCI:

1. In SSMS, click New Query.

2. Execute the following SQL query:

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

4. Link the development database to version control

Use SQL Source Control to link the WidgetDevelopment database to TFS version control. For this tutorial, you'll use the dedicated developer model, so each developer has their own copy of the database. For more information, see Database development models.

1. In SSMS Object Explorer, right-click the WidgetDevelopment database and select Link database to source control.
2. On the Link to source control page under Source control system, select Team Foundation Server (TFS).
3. In Server URL, enter the server URL of your VSTS's collection, for example: https://johnsmith.visualstudio.com/defaultcollection.  
4. To the right of Database folder, click Browse and select the WidgetShop database. Click Create Folder… and create a folder called dbScripts.
5. Under Development model, select Dedicated database.
6. Click Link.
   
7. When the link to source control has been confirmed, click OK.
8. Click the Commit changes tab.
9. Make sure the check box at the top of the list is selected so that every object in the list will be checked in.
10. Enter a comment to describe the changes; for example, Initial check-in of database state. 
11. Click the Commit button. After a few seconds, a message confirms the changes are committed.
    
12. Go to the DLM Automation Build VSTS Marketplace page.
13. Click Install.
14. Choose the account you want to install it to, and click Confirm.
15. Click Close.

5. Create a build definition

You can now create a VSTS build definition for our database build.

1. Go to the Widgetshop project in VSTS.
2. In the top menu, click BUILD.
3. Click the green plus icon to create a new definition. Don't try to create a XAML definition.
4. Select an Empty build definition and click Next.
5. In Repository type, select Team Foundation Version Control.
6. In default agent queue, select either hosted or local build-agents.
7. Click Create.

6. Build a database package

You can now build a database package from the schema in Team Foundation Source Control.

1. Click Add build step…
2. Go to the Redgate DLM Automation Build task and click Add.
    
3. Click Close.
4. In Operation, select Build a database package.
5. In Database Folder, select Database scripts folder is a sub-folder of the VCS root.
6. In Subfolder Path, click the ellipsis (…). Select the $/WidgetShop/dbScripts directory, which is where you committed the database schema.
7. In Output Package Name, enter WidgetShop.

You also need to Publish Build Artifacts, so you can see the created package:

1. Click Add build step.
2. Go to the Publish Build Artifacts task and click Add.
3. In Path to Publish, enter */WidgetShop.
4. In Artifact Name, enter WidgetShop.
5. In Artifact Type select Server.
   When the build runs, you need the build directory to be clean so that files from earlier builds, such as test results, don't create confusion.
6. Go to the Repository tab.
7. Set the value of Clean to false.

We're now ready to save and run our build.

1. Click Save. Enter WidgetShop as the name of the build definition. Click OK.
    
2. Click Queue build…
3. Select the Queue you want to use.
4. Click OK.

Typically, the build will take a few minutes on a hosted agent, or about 20 seconds on a local agent. The first time it runs on your local agent, it will need to download the DLM Automation build task. This is about 10MB, so may take a few minutes on a slow connection.

Once it's complete, you can investigate the build output.

1. Click on Build <number> to see a summary of the build.
    
2. Click Artifacts to see the outputted artifacts.
3. Click Explore. There is one artifact – the NuGet package built from the database scripts.
    
4. Click Build WidgetShop to see the log output of the DLM Automation Build step. If there's a build error, this is a good place to start investigating.
    

7. Add a test step

You can now add a DLM Automation test step. On every 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. 
Redgate SQL Data Generator can be used to add test data to the temporary database. This is covered in step 9: Add Data Generator to the test step.
The database schema for WidgetDevelopment already includes four basic SQL Cop tests that will run against the temporary database and check for:

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

There's also a unit test in the schema that checks for email addresses in the Contacts column. Once the tests are complete, DLM Automation generates test reports for review and the temporary database is dropped.
To add the test step:

1. Go to the WidgetShop build definition.
2. Click a Redgate DLM Automation Build step.
3. In Operation, select Test a database package.
4. In Package ID, enter WidgetShop.
5. Leave the remaining fields as default.

You should also choose to publish the test results so you can see the created package:

1. Click Add build step…
2. In the Test category, select the Publish Test Results task, and click Add.
3. Leave Test Result Format as Junit.
4. In Test Results Files, enter */WidgetShop.junit.xml.
   By default, if there is a test failure, subsequent steps, including the Publish Test Results step, won't run, and we wouldn't see the failing tests. To fix this, you can force the Publish Test Results step to always run:
5. Select the Always run checkbox.
   We also need to make sure the steps are in the right order.
6. Drag the steps so that they're in the order: Build Widgetshop, Test WidgetShop, Publish Test Results and Publish Artifact.
    

You're now ready to save and run the build:

1. Click Save, and save the build.
2. Click Queue Build, queue a build, and wait for it to complete.
3. Go to the build summary. On the right, you'll see the test results. Click on the link to see a breakdown of the tests.
4. Back at the build summary, click Artifacts.
5. Click Explore.
   Notice that there are now three artifacts: the package, a trx file, and a junit.xml file. The trx and junit.xml are Visual Studio Test and JUnit test results files respectively. Trx is used by older versions of Visual Studio.

If you start to see multiple test results, then you need to set your build definition to clean the build folder before starting. To do this, go to your Build Definition, and in Repository set Clean to true.

8. Add a sync step

You can now add a sync step that will update an existing database with the contents of WidgetDevelopment after the build and test steps have completed successfully.

1. Go to the WidgetShop build definition.
2. Click a Redgate DLM Automation Build step.
3. In Operation, select Test a database package.
4. In Package ID, enter WidgetShop.
5. Enter the details of your server:
   1. In Server, enter the name of your server. This can be an Azure SQL Database server, or a SQL Server.
   2. In Database, enter WidgetCI.
   3. Select either Windows Authentication or SQL Server Authentication. If you select Windows Authentication, it will connect as the account that is running the build agent.
6. Leave the remaining fields as default.
   You also need to make sure the steps are in the right order.
7. Drag the Sync WidgetShop step to the end.
    
   You're now ready to save and run the build.

1. Save the build definition.
2. Queue a build, and wait for it to complete.
3. In SSMS, open the WidgetCI database. Notice that the schema has been synchronized to the checked-in schema.

9. Add Data Generator to the test step

DLM Automation uses Redgate SQL Data Generator technology to generate test data for the test step. Due to space limitations, this feature is not available for hosted agents. To use it with an on-premises agent, you must install DLM Automation on the agent.
SQL Data Generator can operate according to two modes:

• Automatically generate data based on the schema. It will make educated guesses based on the name and the datatype of the column, and generate 1000 rows per table.
• Use a sqlgen file. In SQL Data Generator, create a project for the WidgetShop database. Set it up as you see fit, then save as a sqlgen file.

1. We've created an example WidgetTestData.sqlgen file for you. Find it in the zip file, under WidgetShop\Database directory.
   You can then commit this file to Team Foundation Source Control. In this tutorial, you'll do this in VSTS, but you can also use Visual Studio.
2. Go to the WidgetShop project.
3. Click CODE in the top menu.
4. Right-click the $/WidgetShop node, and select Add file(s).
    
5. Click Upload existing files.
6. Click Browse.
7. Navigate to where you unzipped theWidgetDevelopmentDatabaseCIDemo.zip. Go to the \WidgetShop\Database subdirectory, and select WidgetTestData.sqlgen.
8. Enter a suitable check-in comment and click OK.
    

You can now edit the DLM Automation Test step to use this file.

1. Open the WidgetShop build definition.
2. Go to the Test WidgetShop step.
3. Check Use Redgate SQL Data Generator.
4. Uncheck Automatically generate data.
5. Click the ellipsis next to SQL Data Generator file. This open a file-selection box.
6. Select WidgetTestData.sqlgen and click OK.
7. Save and Queue a build.
   A build, using the SQL Data Generator file, will now run.
8. Once the build is complete, look at the build log of the Test WidgetShop step. Notice that rows have been generated and inserted into the test tables.