In this lab we will create a Continuous Integration Process in State-Based Development. We will begin making a variety of changes to our database and committing them to version control.

DevOps MPP Course Source


Lab Tasks:

  • Prepare your local environment
  • Implement a CI process.

Estimated Lab Time:

  • approx. 30 minutes

Task 1: Prepare our local environment

We will set up a local environment for CI builds.

  1. Ensure that you have set up a local VSTS build agent. This was noted in an earlier lab, but use the instructions from Deploy an agent on Windows. Be sure your agent is configured to connect to your VSTS account.

  2. Start your agent. We recommend that you use a nonservice-based agent and start this from a command line as shown.

An Administrator Command Prompt window displays.

  1. Verify that your agent is running, by connecting to your VSTS account in a browser, clicking the Settings icon, then clicking Agent queues. Your agent should have a green bar to the left.

On the WideWorldImporters tab, on the Agent queues tab, at the bottom, a green bar displays.

  1. Open SQL Server Management Studio and connect to your instance. Enter this code in a query window and execute it. This will create an empty database that we will use for the output of the CI process.

Task 2: Implement a CI process

We will implement a CI process for our state-based database development project

This lab explains how to implement a build process for a state-based development when using SSDT.

  1. In your VSTS account, click Build & Release in the toolbar, and then click the Builds tab. You should see an empty Build Definitions screen.

    On the Build and Release tab, on the Builds tab, an empty Build Definitions screen displays.

  2. Click New Definition. You should receive a set of templates from which you can choose as a basis for your build. Click empty process under the Select a template heading.

    Under Select a template is a link option to start with an empty process.

  3. The empty template should look similar to this. You can change the name if you would like.

    The Empty build definition template displays.

  4. Click the Get Sources task on the left. We are still working with the master branch so that default value is correct.

    Under Get Sources, under From, This Project is selected. The Repository is set to WideWorldImporters-SSDT, Branch is master, and Clean is false.

  5. On the left, click Add Task. This will bring up a list of tasks on the right. Scroll down to the Visual Studio Build task and click Add. This task will appear on the left side.

    In Add tasks, on the Build tab, an Add button displays.

  6. Click the Build Solution ***.sln task. The properties will appear on the right.

    Set the the following properties:

    • Display name: Build WWI SSDT Database
    • Solution: WideWorldImporters-SSDT.sln
    • Visual Studio version: Visual Studio 2017
    • MSBuild Arguments: /p:TargetServer=”.\SQL2016” /p:ShadowServer=”(localDB)\Projectsv13” /p:TargetDatabase=”WWI_SSDT_Integration” /p:GenerateSqlPackage=True /p:ReportStyle=Simple /p:Configuration=$(BuildConfiguration)

    Note that the target server should be a local SQL Server 2016 installation. Make sure the correct instance name is entered.

    In Visual Studio Build, under MSBuild Arguments, the Target Server displays.

  7. Click Add Task again. Click Utility to select the utility tasks group, scroll down to the Copy Files task, and click Add.

    Under Add Tasks, on the Utility tab, Copy Files is selected.

  8. Click the Copy Files to task to get the properties to the right. Configure the properties as follows:

    • Display name: Copy Files from Build
    • Source Folder: WideWorldImporters-SSDT\bin\$(BuildConfiguration)
    • Contents: **
    • Target Folder: WideWorldImporters-SSDT\bin\$(BuildConfiguration)

    Copy Files displays with the fields set to the previously mentioned settings.

  9. Click Add Task again. Click Utility again, scroll down to add the Publish Build Artifacts task and click Add.

  10. Click the Publish Artifact task to get the properties to the right. Configure the properties as follows:

    • Display name: Publish Build Artifacts
    • Path to Publish: WideWorldImporters-SSDT\bin\$(BuildConfiguration)
    • Artifact Name: Database_Package
    • Artifact Type: Server

    Publish Build Artifacts displays with the fields set to the previously mentioned settings.

  11. Click the Variables tab at the top of the build definition. This is the place where we can set variables for our build. In the last two tasks, we used the BuildConfiguration variable, which is used in the build as $(BuildConfiguration). Click the Add button on the right and then enter:

    • Name: BuildConfiguration
    • Value: Release

    On the Variables tab, in the left pane, Process variables is selected.

  12. Click the Triggers tab on the build menu. We want to enable Continuous Integration, so click the selector to make this Enabled. Notice we can limit this by branches.

    On the Triggers tab, the Continuous Integration selector is set to Enabled.

  13. Click the Options tab on the build menu. On the right side, from the Default agent queue drop-down list, click Default. This will enable our agent to build on our local machine and update our local instance.

    On the options tab, on the right side, Hosted is selected from the Default agent queue drop-down list.

  14. In the upper right, click the drop down beside Save & queue, then click Save.

    Save is selected from the Save and Queue drop-down menu.

  15. Enter a comment in the Save build definition window, then click Save. Our builds are versioned just like our code.

    Under Save Build Definition, Select folder is \, and the Comment is Initial Build Definition.

  16. Click Queue in the upper right to start a build.

    Screenshot of the Queue button.

  17. A confirmation appears, allowing you to change options. Just click Queue at the bottom.

    Under Queue build for WideWorldImporters-SSDT-CI, Agent queue is set to Default, and Branch is set to master. Under Variables, system.debug displays.

  18. The build will begin running. Click to select the Builds tab, then click the WideWorldImports-SSDT-CI build.

    A callout points to the Builds tab. A second callout points to the WideWorldImports-SSDT-CI link.

  19. You should see details of a successful build.

    On the Build and Release tab, under Builds, the Build Definitions summary displays with 100 percent success rate for one build.


In this lab you completed the following tasks:

  • Prepared your local environment
  • Implemented a CI process.