In this lab we will create a Continuous Integration Process in Migration-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
  • Prepare your VSTS Account
  • 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. I recommend you use a nonservice-based agent and start this from a command line.

    ![The following command lines display: E:\DevOpsAgent > run. The results are: Scanning for tool capabilities. Connecting to the server. Listening for Jobs.](..\assets\ciwithmigrationbaseddev-jan2018\Lab2.4_Image1.jpg)

  3. Verify your agent is running, but connecting to your VSTS account, clicking the gear icon and then selecting Agent Pools. Your agent should have a green bar to the left.

    Under Agents, a green bar is next to the PLATO Enabled checkbox, whose State is Online, and Current Status is Idle.

  4. 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.

     CREATE DATABASE WWI_Integration;

Task 2: Prepare your VSTS Account

We will add a new task to your VSTS account.

  1. In the upper right corner of your VSTS account, there is a bag icon. Hover over it and you will see two options. Click Browse Marketplace.

    On the toolbar, a shopping bag icon is selected, and from its drop-down menu, Browse Marketplace is selected.

  2. This will open up the main marketplace for VSTS/TFS extensions.

    The VSTS Marketplace displays.

  3. Search for Redgate in the edit box and you should get a number of results. Click Redgate ReadyRoll.

    Redgate is typed in the search box, and below, four results display.

  4. This will open up the extension details. Click Install.

    The Install page displays for Redgate ReadyRoll, and the Install button is selected.

  5. This will bring up the account choice and license confirmation. Choose the appropriate account and click Continue, then, when the checking is complete, click Confirm.

    Under Select Account,dkranch is selected. Under Confirm, Build displays for the permissions the extension will be granted.

  6. You should receive a confirmation that the extension is installed in your account. Click Close.

    The Redgate ReadyRoll confirmation page displays with a message that says you are good to go.

Task 3: Implement a CI process

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

This lab explains how to implement a build process for a migration-based development using the ReadyRoll engine. One thing to be aware of is that the visual look of VSTS can change at times, so the images may be slightly out of date at times. However, the process remains the same.

  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 page, on the Builds tab, a New definition button displays.

  2. Click the New Definition button to get started. 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, Build templates display.

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

    Under Process, the Get sources empty template displays.

  4. Click the Get Sources task on the left. We see on the right that this build will pull from the master branch. For this lab, that is what we want.

    On the Get sources page, This project is selected. The Repository drop-down is set to WideWorldImporters-RR, 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.

    Under Add Tasks, the Build tab is selected. Below this, a list of tasks display, and Visual Studio Build is selected.

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

    Set the the following properties:

    • Display name: Build WWI ReadyRoll Database
    • Solution: WideWorldImporters-RR.sln
    • Visual Studio version: Visual Studio 2017
    • MSBuild Arguments: /p:TargetServer=”.\SQL2016” /p:ShadowServer=”(localDB)\Projectsv13” /p:TargetDatabase=”WWI_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.

    The Visual Studio build task properties display with fields set to the previously defined settings.

  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, the Utility tab is selected. Below this, 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-RR\bin$(BuildConfiguration)
    • Contents: **
    • Target Folder: WideWorldImporters-RR\bin$(BuildConfiguration)

    Copy Files properties display with fields set to the previously defined 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-RR\bin$(BuildConfiguration)
    • Artifact Name: Database_Package
    • Artifact Type: Server

    Under Add Tasks, the Utility tab is selected. Below this, Publish Build Artifacts is selected.

  11. Click the Variables tab at the top of the build definition. This is 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

    The Variable tab page displays.

  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, under Continuous Integration, the option to Disable this trigger is 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, in the right pane under Agents, Hosted is selected in the Default agent queue drop-down list.

  14. In the upper right, there is a Save & queue selection. Click the arrow next to this, then click Save.

    From the Save and queue drop-down menu, Save is selected.

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

    In the Save build definition section, the Comment field has the comment, “Initial Build Definition.”

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

    On the menu bar, Queue is selected.

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

    In the Queue build for WideWorldImporters-RR-CI, the Agent queue is set to Default, and the Branch field is set to master. At the bottom, the Variables tab is selected, and system.debug displays as false.

  18. The build will begin running. First, you will see it waiting for an agent, then a console will appear with information.

    The Build running console displays.

  19. After the build succeeds, you will see a green bar across the top and a number of menu items below the graph.

    A green bar at the top displays "Build Succeeded." On the Summary tab, build summary details display.

  20. Click the Artifacts link and you will see our Database_Package artifact. This contains the scripts to execute against our downstream databases.

    Under the green Build Succeeded bar, the Artifacts tab is selected. Under Name, Database_Package displays.

  21. Click the Database Script item to see the script that would be run against the WWI_Integration database.

    Under the green Build Succeeded bar, the Database script tab is selected, under which the script displays.


In this lab you completed the following tasks:

  • Prepared your local environment
  • Prepared your VSTS Account
  • Implemented a CI process