By: Ron L'Esteve | Comments | Related: > DevOps
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.001.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.002.png)
Next, configure and create the project.
![ConfigureProject Steps to configure project](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.003.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.004.png)
Close Visual Studio to complete the GitHub Extension installation.
![ExtensionInstaller Image showing extension installer.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.005.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.006.png)
![ImportDataTier2 Step 2 to import data tier dacpac.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.007.png)
Click Finish.
![ImportProgress Image of Summary import progress](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.008.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.009.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.010.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.011.png)
Connect to GitHub from Visual Studio.
![SignIntoGitHub Steps to Sign into github](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.012.png)
A prompt will appear to authorize Visual Studio and GitHub to integrate together.
![AuthorizeVisualStudio Step to authorize Visual Studio](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.013.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.014.png)
Publish the solution to the desired GitHub repo.
![PublishtoRepo Step to publish to repo](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.015.png)
Verify that the solution has been checked into the selected GitHub Repo.
![GitHubRepo Image of GitHub Repo](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.016.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.017.png)
For more on Azure Pipelines, see What is Azure Pipelines?
Set up an Azure Pipelines Plan
![AzurePipelinePlan Set up an Azure Pipeline plan](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.018.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.019.png)
Complete the order and begin the installation.
![CompleteAzurePipelineInstall Steps to complete the Azure Pipeline installation plan.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.020.png)
Click Install.
![InstallPipelines Steps to install Azure Pipelines.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.021.png)
When prompted, select your Azure DevOps pipeline project and organization and click continue.
![SetUpAzurePipelines Set Up azure Pipeline project](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.022.png)
When prompted, Authorize the integration between Azure pipelines and GitHub.
![AuthorizeAzurePipes Step to Authorize Azure Pipelines](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.023.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.024.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.025.png)
Let’s start with empty job.
![SelectEmptyTemplate Select an Empty template.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.026.png)
Next, select GitHub as the source. Also, select the repo.
![SelectGitHUBSource Select the GitHub Source](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.027.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.028.png)
Verify the MSBuild Configurations.
![MSBuildConfig Image of MSBuildConfig](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.029.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.030.png)
Verify the configuration options.
![CopyFilesconfig 
Image od copy files config](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.031.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.032.png)
Verify the publish build artifact configurations.
![PublishArtifactsConfig Step showing publish Artifact config](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.033.png)
Click save and queue.
![TaskSummary Summary of Build Tasks](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.034.png)
Verify the run pipeline parameters and click save and run.
![RunPipelineTask Image of Run Pipeline task config](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.035.png)
Notice the build pipeline summary which contains details and status of job.
![PipelineRunSummary Summary of run pipeline.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.036.png)
Confirm a successful job status once the Agent job completes.
![JobSummary Image of Build Job Summary](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.037.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.038.png)
Select the build artifact and click add.
![AddReleaseArtifact2 Add the build artifact to the release](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.039.png)
Add an empty job for stage
![SelectTemplate Select the empty job template.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.040.png)
Verify the Stage properties.
![VerifyStage Verify the Stage properties.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.041.png)
Add a task
![AddTask Add a stage task](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.042.png)
Verify the Agent job details.
![ConfigureReleaseAgent Image of release agent config.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.043.png)
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](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.044.png)
Populate the necessary Azure SQL Database deployment parameters.
![DacpacParams Populate the necessary Azure SQL Database deployment parameters.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.045.png)
Click manage to connect to your Azure account.
![ConnectAzure Connect to Azure](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.046.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.047.png)
Proceed to create the new release.
![CreateRelease1 Create a new release](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.048.png)
Verify that the release pipeline succeeded.
![VerifyPipeline Image showing the release pipeline run status](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.049.png)
Also, verify the Agent job steps.
![AgentJobSummary Image showing the Release agent job summary.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.050.png)
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.](/tipimages2/6557_azure-devops-cicd-using-github-repo-and-visual-studio.051.png)
Next Steps
- For more information on getting started with GitHub for SQL Server, see Getting Started with GitHub for SQL Server.
- For more on building a SQL Server Database in Azure DevOps, see: Build your SQL Server Database.
- Watch the video on Continuous Builds with your GitHub projects using Azure Pipelines.
- Read more on Connecting Azure Boards to GitHub.
- Read more about GitHub integration with Azure Pipelines.
- Read about Continuous Database deployments with Azure DevOps.
- Read about How to Connect a Visual Studio Project to GitHub.
- Read about How to Build GitHub Repos.
About the author
![MSSQLTips author Ron L'Esteve](/images/ron-lesteve.jpg)
This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips