Azure DevOps CI CD using GitHub Repo and Visual Studio Azure SQL Database Project


By:   |   Updated: 2020-09-15   |   Comments   |   Related: More > Azure


Problem

In my previous article, Deploy SQL Database from Azure DevOps with an ARM Template and DACPAC File, I discussed how to deploy an Azure SQL Database using Azure DevOps CICD. However, this article does not cover the process of connecting to a source repo and then using this repo in the Azure DevOps CICD pipelines. How can we create and deploy a Visual Studio Database project to Azure SQL Database with Azure DevOps for CICD and a GitHub repository for source control?

Solution

There are a few source-control options within Visual Studio. GitHub is one of these source control options and offers a number of benefits including advanced security options. Integrating multiple applications such as Visual Studio, GitHub, Azure DevOps and Azure SQL Database for a seamless CICD process is a growing need for many enterprises that are on a journey to modernize their data and infrastructure platform.

In this article, I will demonstrate an end-to-end solution for the following architectural flow to deploy an AdventureWorksLT2019 database from Visual Studio to an Azure SQL Database through a GitHub repository and then deployed by both an Azure DevOps Build (CI) and Release (CD) Azure pipeline.

CICDProcessFlow Process Flow for Azure DevOps CI/CD using GitHub Repo and Visual Studio Azure SQL Database Project

Pre-Requisites

1) Visual Studio 2019 with SSDT: See Visual Studio 2019 downloads and Download SQL Server Data Tools (SSDT) for Visual Studio.

2) GitHub Account & Repo: See How to create an account in GitHub and Create a repo.

3) Azure DevOps Account & Project: See Create an organization or project collection.

Create a Visual Studio SQL Database Project

Let’s begin by creating a new SQL Database Project in Visual Studio 2019.

CreateProject Steps to create a VS project

Next, configure and create the project.

ConfigureProject Steps to configure project

Install Visual Studio GitHub Extension

Since this Visual Studio solution will be linked to a GitHub source control repo, install the following GitHub Extension.

InstallGitHubExtension Steps to install GitHub Extension.

Close Visual Studio to complete the GitHub Extension installation.

ExtensionInstaller Image showing extension installer.

Import Adventure Works Database

Next, import the AdventureWorksLT2019 dacpac file. For more information on dacpac, see Data-tier applications.

AdventureWorks Sample databases can be downloaded and used for the import process. Notice that the databases are in .bak format and will need to be converted to .bacpac format.

For more information on converting .bak to .bacpac, See Import .BAK file to Azure SQL Database. In short, remember to restore the .bak file and then export it to .bacpac format.

Import DataTier Steps to Import Data Tier.
ImportDataTier2 Step 2 to import data tier dacpac.

Click Finish.

ImportProgress Image of Summary import progress

Once the data-tier application is imported, the schemas will be listed as folders in the SQL Server Database project.

AdventureWorksSolution Image of AdventureWorks Solution project

Ensure the database project target platform is set to Microsoft Azure SQL Database from the Database project properties GUI.

ProjectSrttings Image of project settings and target platform.

Connect to GitHub Repo Source Control

Now that the project had been imported and configured appropriately, add the solution to source control.

AddtoSrcControl Step to add solution to source control.

Connect to GitHub from Visual Studio.

SignIntoGitHub Steps to Sign into github

A prompt will appear to authorize Visual Studio and GitHub to integrate together.

AuthorizeVisualStudio Step to authorize Visual Studio

Check-In Visual Studio Solution to GitHub Repo

Now that the solution has been added to a GitHub Repo, click home and then sync to check in the solution to the GitHub Repo.

SynctoRepo Step to Sync to repo

Publish the solution to the desired GitHub repo.

PublishtoRepo Step to publish to repo

Verify that the solution has been checked into the selected GitHub Repo.

GitHubRepo Image of GitHub Repo

Install Azure Pipelines from GitHub

Now that we have integrated Azure GitHub with Visual Studio, it’s time to now install Azure pipelines from the GitHub Marketplace to integrate GitHub with Azure DevOps.

AzurePipelines Add Azure Pipelines

For more on Azure Pipelines, see What is Azure Pipelines?

Set up an Azure Pipelines Plan

AzurePipelinePlan Set up an Azure Pipeline plan

See Pricing for AzureDevOps for more information on Azure pipelines pricing.

For this demo, I have selected the Free account which has the following free benefits.

InstallAzurePipelinePlan Steps to install Azure Pipeline plan

Complete the order and begin the installation.

CompleteAzurePipelineInstall Steps to complete the Azure Pipeline installation plan.

Click Install.

InstallPipelines Steps to install Azure Pipelines.

When prompted, select your Azure DevOps pipeline project and organization and click continue.

SetUpAzurePipelines Set Up azure Pipeline project

When prompted, Authorize the integration between Azure pipelines and GitHub.

AuthorizeAzurePipes Step to Authorize Azure Pipelines

Build CI Pipeline from GitHub Repo

We’re now ready to create a Build Continuous Integration pipeline from the GitHub Repo.

Use classic editor to create the build pipeline without yaml.

WheresCode Step to select where the code is.

Note that there is also an option to create the pipeline with Yaml and will require yaml code. For more information on YAML, see YAML Tutorial: Everything You Need to Get Started in Minutes.

For this demo, I will only use the classic editor to create the pipeline.

YAML Image showing YAML code option.

Let’s start with empty job.

SelectEmptyTemplate Select an Empty template.

Next, select GitHub as the source. Also, select the repo.

SelectGitHUBSource Select the GitHub Source

Search for and Add the MSBuild task to the pipeline. This will build the Visual Studio solution. For more information, see MSBuild Task.

MSBuild Add the MSBuild Task

Verify the MSBuild Configurations.

MSBuildConfig Image of MSBuildConfig

Also, add the Copy files Task which will copy files to the artifact staging directory. For more information, see the Copy files Task.

CopyFiles Add copyfiles task

Verify the configuration options.

CopyFilesconfig 
Image od copy files config

Finally, add a Publish build artifacts task to publish the staging directory artifacts to Azure Pipelines. For more information, see Publish Build Artifact tasks.

PublishBuildArtifacts Step to Add Publish Build Artifacts

Verify the publish build artifact configurations.

PublishArtifactsConfig Step showing publish Artifact config

Click save and queue.

TaskSummary Summary of Build Tasks

Verify the run pipeline parameters and click save and run.

RunPipelineTask Image of Run Pipeline task config

Notice the build pipeline summary which contains details and status of job.

PipelineRunSummary Summary of run pipeline.

Confirm a successful job status once the Agent job completes.

JobSummary Image of Build Job Summary

Release CD Pipeline from DevOps Artifact Repo

Now that the build pipeline has been created and successfully deployed, its time to create a release pipeline.

Begin by adding a release pipeline artifact.

AddReleaseArtifact Step to add release artifact

Select the build artifact and click add.

AddReleaseArtifact2 Add the build artifact to the release

Add an empty job for stage

SelectTemplate Select the empty job template.

Verify the Stage properties.

VerifyStage Verify the Stage properties.

Add a task

AddTask Add a stage task

Verify the Agent job details.

ConfigureReleaseAgent Image of release agent config.

Add the Azure SQL Database deployment task which will deploy the Azure SQL Database using a DACPAC file. For more information, see Azure SQL Database Deployment task

SQLDeploymentTask Add the Azure SQL Database deployment task

Populate the necessary Azure SQL Database deployment parameters.

DacpacParams Populate the necessary Azure SQL Database deployment parameters.

Click manage to connect to your Azure account.

ConnectAzure Connect to Azure

Also, select the AdventureWorks2019LT dacpac file that was built and stored in the artifact drop directory from the previous build pipeline and click OK.

SelectDacpacfile Select the dacpac file.

Proceed to create the new release.

CreateRelease1 Create a new release

Verify that the release pipeline succeeded.

VerifyPipeline Image showing the release pipeline run status

Also, verify the Agent job steps.

AgentJobSummary Image showing the Release agent job summary.

Verify Deployed Azure SQL AdventureWorks Database

Lastly, log into the Azure SQL Database and verify that the AdventureWorksLT2019 database exists in the specified at deployment.

VerifyDeployedDB Verify the deployed Azure SQL Database.
Next Steps


Last Updated: 2020-09-15


get scripts

next tip button



About the author
MSSQLTips author Ron L'Esteve Ron L'Esteve is a seasoned Data Architect who holds an MBA and MSF. Ron has over 15 years of consulting experience with Microsoft Business Intelligence, data engineering, emerging cloud and big data technologies.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Adding Users to Azure SQL Databases

Connect to On-premises Data in Azure Data Factory with the Self-hosted Integration Runtime - Part 1

Continuous database deployments with Azure DevOps

Process Blob Files Automatically using an Azure Function with Blob Trigger

Using Azure Blueprints to deploy Azure SQL Server and Database with Key Vault Secrets








get free sql tips
agree to terms


Learn more about SQL Server tools