Overview

This tutorial walks you through the process of creating a Redgate Change Control project and a related Azure DevOps project with a Git repo. You'll create a local database environment using Microsoft's Northwind sample database and add the code to your Redgate Change Control project, then push it to Azure DevOps. Next, you'll create classic Build and Release pipelines in Azure DevOps for your self-hosted Azure DevOps Agent. 

Prerequisites

To follow along with this tutorial, you will need to have the following. Your Redgate Sales team can help set you up with this information:

• A Flyway Teams license key
• A license key for Redgate Deploy
• A license key and special edition of the SQL Compare command line. Unzip this to the location 

You will also need access to...

• An Azure DevOps organization where you may create an Azure DevOps project. You may do this in either Azure DevOps Server (formerly known as Team Foundation Server) or Azure DevOps Services
• A self-hosted Agent for your Azure DevOps pipeline
• An installation of SQL Server Developer Edition where you have sysadmin access. In this tutorial we assume that this instance is on the same Windows installation where you are working and where you have the Azure DevOps Agent installed, but this is not required: the instance can be installed anywhere as long as TCP/IP connections are allowed from where you are running Redgate Change Control as well as from where the Azure DevOps Agent is installed.
• A service account to use for automated deployments which can utilize Windows Authentication to access the target

Initial setup: Create a Redgate Change Control project, an Azure DevOps project, and a SQL Server database environment

1.  Open Redgate Change Control. When prompted, click to install latest version on exit. Exit and complete installation. 

2. Open a browser and navigate to your Azure DevOps Services organization

2.1 Create a new project named Northwind

Click on Repos and copy the URL under Clone to your computer

2.2 Open a command prompt and clone down the repo using the URL you copied 

3. Open SQL Server Management Studio and connect to the SQL Server instance you will use for testing. 

3.1 Use the Northwind Script to create two databases:  Northwind_Dev, Northwind_Test

To do this:

• First find and replace 'northwnd' with 'Northwind' (this fixes some oddly named filenames)
• Then find and replace 'Northwind' with the name of the database you are creating, such as 'Northwind_Dev', and run the script 
• Repeat using find and replace to change the name of the databases

3.1 Create two empty databases, one for the shadow database and another for building using the following code:

4. Reopen Redgate Change Control and create a new project

• Project location: C:\Northwind
• Project name: Northwind
• Database Engine: SQL Server

5. On the Schema Model tab, link the development database, and import the schema.

Note: In this example we are using a local SQL Server instance, so we may use LOCALHOST. If you are using one installed elsewhere, customize the JDBC url accordingly.

• Authentication type: Windows
• JDBC URL: jdbc:sqlserver://localhost;databaseName=Northwind_Dev
• Schemas: dbo

Refresh the schema model, select all objects, save to project

Commit changes

• Commit message: initial commit of schema model
• Push (optional)

6. On the Generate Migrations tab, set up the shadow database and baseline

Note: In this example we are using a local SQL Server instance, so we may use LOCALHOST. If you are using one installed elsewhere, customize the JDBC url accordingly.

• Click 'Setup shadow database'
• Authentication type: Windows Authentication
• JDBC URL: jdbc:sqlserver://localhost;databaseName=Northwind_redgate_Shadow

Test connection and authenticate.

After a couple of seconds a message should pop up saying, "You haven't created a baseline yet." Click Create baseline.

Note: In this example we are using a local SQL Server instance, so we may use LOCALHOST. If you are using one installed elsewhere, customize the JDBC url accordingly.

• Click 'Connect to database'
• Authentication type: Windows Authentication
• JDBC URL: jdbc:sqlserver://localhost;databaseName=Northwind_Dev

Test connection and authenticate. Click Baseline.

You should see a message that RCC has successfully completed the baseline and added a versioned migration script.

7. Go to the version control tab and commit and push the change.

Set up an Azure DevOps classic build pipeline for your self-hosted agent

1. Add PowerShell scripts to the repository to be executed by your pipelines

• Open VSCode or File Explorer, depending on your preference
• Open folder C:\Northwind\Northwind\ 
• Create a new folder named _Automation
• Add a file to this folder named RedgateDeployFunctions.ps1 (the full path will be c:\Northwind\Northwind\_Automation\RedgateDeployFunctions.ps1)
• Save the three files from this gist into this directory

Commit and push the change from preferred tool. (You may do this in Redgate Change Control itself on the Version Control tab if you prefer.

1.2 Install SQL Compare command line on the server where your self-hosted agent runs

Unzip the version of SQL Compare from the Redgate team to the location: "C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\" on the server where your build agent will be running. Keep the SC folder in place. 

This should result in "C:\Program Files (x86)\Red Gate\SQL Change Automation PowerShell\SC\sqlcompare.exe" being a valid path. 

1.3 Install Flyway Teams on the server where your self-hosted agent runs

Download the Flyway command line from https://flywaydb.org/documentation/usage/commandline/#download-and-installation

Extract the archive to the directory of your choice on the self-hosted agent. We used "C:\flyway".

Add the directory you created to the path on the self-hosted agent.

2. Set up secrets in Azure DevOps Library

• Expand Pipelines, then click Library
• Click + Variable Group
• Name the variable group RedgateDeploy
• Click +Add and add variable
  • Name: FLYWAY_LICENSE_KEY
  • Value: YourLicenseKeyValue
  • Click the padlock to make it a secret variable
• Click +Add and add variable
  • Name: SQLCOMPARE_LICENSE_KEY
  • Value:  YourLicenseKeyValue
  • Click the padlock to make it a secret variable
• Click Save at the top of the pane to save changes to the variable group

2. Create pipeline

Open your browser of choice

• Navigate to your Azure DevOps Project and click Pipelines → Builds . (Note: if you are using a more recent version of Azure DevOps, this will only be 'Pipelines')
• Click New pipeline or Create pipeline (depending on your version of Azure DevOps)
• If you are using a newer version of Azure DevOps, click Use the classic editor to create a pipeline without YAML.
• Select a source: Azure Repos Git
  • Team project: Northwind
  • Repository: Northwind
  • Default branch for manual and scheduled builds: master
  • Clean: set to true
  • Clean options: all build directories
• Click Continue
• Choose a template: under Configuration as code select Empty Job
• Pipeline settings:
  • Name: Northwind-CI
  • Agent pool: Default
  • Tasks:
    • Click + under Agent Job 1 to add a task
      • Add a PowerShell task
        • Script Path: Northwind/_Automation/BuildCmd.ps1
        • Click + under Environment variables to add a variable:
          • Name: FLYWAY_LICENSE_KEY
          • Value: $(FLYWAY_LICENSE_KEY)
        • Click + under Environment variables to add a variable:
          • Name: SQLCOMPARE_LICENSE_KEY
          • Value: $(SQLCOMPARE_LICENSE_KEY)
        • Click + under Environment variables to add a variable:
          
          • Name: AGENT_BUILD_DIRECTORY
          • Value:  $(Agent.BuildDirectory)
        • Click + under Environment variables to add a variable:
          
          • Name: BUILDDB_HOST
          • Value:  LOCALHOST
        • Click + under Environment variables to add a variable:
          
          • Name: BUILDDB_PORT
          • Value:  1433
        • Click + under Environment variables to add a variable:
          
          • Name: BUILDDB_NAME
          • Value:  NorthwindBuild
        • Click + under Environment variables to add a variable:
          
          • Name: ARTIFACT_ALIAS
          • Value:  $(Release.PrimaryArtifactSourceAlias)
    • Click + under Agent Job 1 to add second task
      • Add a Publish Build Artifacts task
        • DisplayName: Publish Artifact: baselinesnapshot
        • Path to publish: $(Agent.BuildDirectory)\BuildBaseline.snp
        • Artifact name: baselinesnapshot
        • Artifact publish location - leave to the default (Azure Pipelines/TFS)
        • Control options - set Continue on error to true
    • Clone the Publish Build Artifacts task under Agent job 1
      • Display name: Publish Artifact: build snapshot
      • Path to publish: $(Agent.BuildDirectory)/BuildResult.snp
      • Artifact name: buildsnapshot
      •  Leave other options as cloned
    • Clone the Publish Build Artifacts task under Agent job 1
      • Display name: Publish Artifact: deploy
      • Path to publish: $(Build.Repository.LocalPath)/Northwind/
      • Artifact name: Deployment
      • Control options - set Continue on error to false
  • Click Variables:
    • Click on 'Variable Groups'
    • Click 'Link variable group'
    • Select 'RedgateDeploy (2)'
    • Click 'Link'
  • Click Triggers
    • Tick 'Enable continuous integration'
• Click Save and Queue

Set up an Azure DevOps classic release pipeline for your self-hosted agent

1. Create a new release pipeline: 

• Click Pipelines → Releases
• Click New pipeline
• On the Select a template pane, click Empty job
• Click Variables:
  • Click on 'Variable Groups'
  • Click 'Link variable group'
  • Select 'RedgateDeploy (2)'
  • Click 'Link'
• In the Artifacts box, click + Add
  • Project - use the default (Northwind)
  • Source (build pipeline): Northwind-CI
  • Default version: Latest
  • Source  alias - use the default (_Northwind-CI)
  • Click Add
• In the Artifacts box, click the Lightning bolt circle
  • Set Continuous deployment trigger to enabled
• In the Stages box, click into Stage 1 so it opens the Agent job pane
  • Click on Agent job and click +
  • Add a PowerShell Script task
  • Click on the PowerShell script task
    • Type - use the default (File Path)
    • Script Path: Northwind/_Automation/DeployCmd.ps1
    • Click + under Environment variables to add a variable:
      • Name: FLYWAY_LICENSE_KEY
      • Value: $(FLYWAY_LICENSE_KEY)
    • Click + under Environment variables to add a variable:
      • Name: SQLCOMPARE_LICENSE_KEY
      • Value: $(SQLCOMPARE_LICENSE_KEY)
    • Click + under Environment variables to add a variable:
      
      • Name: WORKING_DIRECTORY
      • Value:  $(System.DefaultWorkingDirectory)
    • Click + under Environment variables to add a variable:
      
      • Name: DEPLOYDB_HOST
      • Value:  LOCALHOST
    • Click + under Environment variables to add a variable:
      
      • Name: DEPLOYDB_PORT
      • Value:  1433
    • Click + under Environment variables to add a variable:
      
      • Name: DEPLOYDB_NAME
      • Value:  Northwind_Test
    • Click + under Environment variables to add a variable:
      
      • Name: REPORT_OUPUT_PATH
      • Value:  C:\DBAutomation\Deploy\$(Release.ReleaseName)_$(Release.AttemptNumber)

Test your pipelines

Now that your database environment, Redgate Change Control Project, and Azure DevOps pipelines have been created you may test pushing changes through the pipelines. To do this:

• Use SQL Server Management Studio to make a schema change in the database Northwind_Dev
• Open your Redgate Change Control project and refresh the schema model
• Save your change to the project
• Go to the Generate Migrations tab and generate a migration script for your change
• Commit the change on the Version Control tab
• Push the change to your  Azure DevOps repo
• Validate that your build and release pipelines succeed
• Review the reports created in the C:\DBAutomation\Deploy folder on the Azure DevOps Agent