Continuous Intregration with a Migration-Based Methodology
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
- This lab is used in course DevOps200.6x: DevOps for Databses - Module 2.
- Completion of the lab Changes Using a Migration-Based Methodology
- 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.
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.
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)
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.
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.
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.
This will open up the main marketplace for VSTS/TFS extensions.
Search for Redgate in the edit box and you should get a number of results. Click Redgate ReadyRoll.
This will open up the extension details. Click Install.
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.
You should receive a confirmation that the extension is installed in your account. Click Close.
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.
In your VSTS account, click Build & Release in the toolbar and then click the Builds tab. You should see an empty Build Definitions screen.
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.
The empty template should look similar to this. You can change the name if you would like.
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 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.
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.
Click Add Task again. Click Utility to select the utility tasks group, scroll down to the Copy Files task, and click Add.
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)
Click Add Task again. Click Utility again, scroll down to add the Publish Build Artifacts task and click Add.
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
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
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.
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.
In the upper right, there is a Save & queue selection. Click the arrow next to this, then click Save.
Enter a comment in the Save build definition window, then click Save. Our builds are versioned just like our code.
Click Queue in the upper right to start a build.
A confirmation appears, allowing you to change options. Just click Queue at the bottom.
The build will begin running. First, you will see it waiting for an agent, then a console will appear with information.
After the build succeeds, you will see a green bar across the top and a number of menu items below the graph.
Click the Artifacts link and you will see our Database_Package artifact. This contains the scripts to execute against our downstream databases.
Click the Database Script item to see the script that would be run against the WWI_Integration database.
In this lab you completed the following tasks:
- Prepared your local environment
- Prepared your VSTS Account
- Implemented a CI process