CICD in Synapse SQL: How to deliver your database objects across multiple environments

Published Mar 30 2022 02:01 PM 4,046 Views
Microsoft

In my previous article, I demonstrated how you could take advantage of CI/CD to automate the delivery of your Synapse Workspace artifacts across multiple environments. As a complement to this, I'm adding this new tutorial where you can learn how to complement that automation by adding the delivery of your Synapse SQL pool objects across multiple environments. 

 

In this article I will describe a scenario where I'm using Visual Studio and Azure DevOps to automate the delivery of database changes from a lower environment to a target environment. 

 

This article is divided into three stages:

 

  1. A first stage where you will set up your development environment using Visual Studio, preparing the foundations for the CI/CD processes.
  2. A second stage, the CI, where you will use Azure DevOps Pipelines to build the Continuous Integration process. 
  3. The third and final stage, the CD, where you will use Azure DevOps Release Pipelines to set up the Continuous Delivery process to deliver the database changes to the target SQL pool. 

 

First stage - The Visual Studio side of things -

 

This is the stage where you will set up your development environment using Visual Studio and prepare the foundations for the CI/CD processes.

 

Open Visual Studio and click "Create a new Project" when prompted to Get started.

 

RuiCunha_0-1648282081414.png

 

When prompted to "Create a new project" search for the keyword "database" and select the "SQL Server Database Project" from your search results.

 

RuiCunha_1-1648282115492.png

 

Configure your new project and select "Create" once finished

 

RuiCunha_2-1648282146069.png

 

After creating your database project, the first thing you need to do is to integrate your Visual Studio Solution with your Git Repository. In this case, you are going to select the "Git" menu and then select the "Create Git Repository..." menu option (you can integrage with an existing repository in case you already have one). In this case I'm integrating with a new Azure DevOps Git repo.

 

RuiCunha_3-1648282170648.png

 

 

Select "Create and Push" to start the syncronization between your Visual Studio solution and your Git repository. 

 

RuiCunha_5-1648282262184.png

 

You can open a new browser tab and explore your Git repository. Check the VS solution and the project files hosted in your master branch.

 

RuiCunha_6-1648282312919.png

 

 

Once you get your VS solution integrated with your Git repo, you will start importing your source database. From the Solution Explorer, right click on your project name and select Import --> Database…

 

RuiCunha_7-1648282335087.png

 

Hit the "Select Connection...." button to connect to your source database. You can check/uncheck the import settings options according to your requirements.

RuiCunha_8-1648282471480.png

 

 

Note: you can uncheck the "Import application-scoped objects only" to make sure you import server-level objects as well.

 

Select "Start" to start importing the database objects to your VS solution. Once the import is finished, you can go to the "Git Changes" blade in Visual Studio and flag all imported objects as pending changes waiting to be pushed to your Git repository.

 

RuiCunha_9-1648282500563.png

 

 

To push these pending changes to your Git Repository,  add a brief description to this commit , click on the down arrow close to the "Commit All" button and select the "Commit All and Push" option.

 

RuiCunha_10-1648282523400.png

 

Once the Commit All and Push is completed, go to your Git repo and check the new folder structure containing the sql script files for all your database objects.

 

RuiCunha_11-1648282568265.png

 

 

Once you have reached this step and you have confirmed the folder structure containing the sql files for your objects, you can move forward to the next stage.

 

Second stage - The Continuous Integration side of things -

 

Let's start this second stage, where you will use Azure DevOps Pipelines to build the Continuous Integration process. 

 

From your Azure DevOps project menu, select the "Pipelines" menu option to create a new CI Pipeline.

 

RuiCunha_12-1648282618846.png

 

Create a new Pipeline and select the "Use the classic editor" option to configure your own Pipeline.

 

RuiCunha_13-1648282646155.png

 

 

RuiCunha_16-1648282701976.png

 

Select the source that is hosting your Git repo, in my case I'm selecting "Azure Repos Git" .

 

RuiCunha_17-1648282745860.png

 

 

After selecting "Continue", select the ".NET Deskop" template and hit the "Apply" button

 

RuiCunha_18-1648282796697.png

 

 Name your pipeline (In my case: "Dacpac Automation - CI") and make sure you remove all template tasks except these below:

 

RuiCunha_19-1648282858285.png

 

Select "Save & Queue" and when prompted to run the pipeline, select "Save and Run".

 

RuiCunha_20-1648282898754.png

 

Wait for the pipeline run to finish and then click in your agent job name (in my case "Agent job 1") under the "Jobs" blade to get more details about the pipeline execution

 

RuiCunha_21-1648282948998.png

 

From the left menu, you can see the pipeline tasks and you can click in the "Build solution" task to get more details about this build. The highlighted code represents the Dacpac file that was generated.

 

RuiCunha_22-1648282990266.png

 

Once you confirm that the dacpac file has been generated, you can move forward to the third and final stage: the CD stage

 

Third and final stage - The Continuous Delivery side of things -

 

Once you have completed all the steps above to generate your dacpac file, you will use the Azure DevOps Release Pipeline to set up the Continuous Delivery process and publish the generated Dacpac file to your target SQL pool.

 

From your Azure DevOps project menu, select "Pipelines" and then select "Releases" to create a new Release Pipeline. Create a New Release Pipeline.

 

RuiCunha_23-1648283016559.png

 

RuiCunha_24-1648283048801.png

 

 

When prompted to select a template, select "Empty job"

 

RuiCunha_25-1648283066137.png

 

Name your release pipeline (in this case: Dacpac Automation CD) and then select "+ Add an artifact". This will define a source location to be used by your release tasks.

 

RuiCunha_26-1648283131377.png

 

When configuring your artifacts, select the source type as "Build". Then select your build pipeline that was created in the previous stage (in this case Dacpac Automation - CI ). Hit the "Add" button to create your artifact.

 

RuiCunha_27-1648283195090.png

 

Configure your release stage by selecting the "1 job , 0 task" link under your stage name.

 

RuiCunha_28-1648283216075.png

 

From your Agent Job task, click on the plus (+) sign to add a new task to your Release Pipeline.

 

RuiCunha_29-1648283237833.png

 

Search for "Data Warehouse" and select "Add" to add the Azure SQL Data Warehouse deployment task to your release.

 

RuiCunha_30-1648283259779.png

 

Before configuring this task, you can create some variables to avoid exposing some task parameter values. Select the "Variables" tab and add the following variables to your release pipeline:

 

RuiCunha_32-1648283318782.png

 

 

Note: you can secure any password in DevOps by using the locker icon when adding the variable. This will change the variable type to secret

RuiCunha_33-1648283341322.png

 

 

You can use these variables when configuring your task. Just type $(variable_name). Choose "SQL DACPAC file" as your deploy type and browse your .dacpac file from your artifact location.

 

RuiCunha_34-1648283377575.png

 

 

When browsing your dacpac file you should be looking into a path similar to this one below:

 

RuiCunha_35-1648283394251.png

 

Save your release and then select the "Create Release" option. Hit the "Create" button to trigger a new release.

 

RuiCunha_36-1648283414465.png

 

You can check the release progress by selecting the "Logs" option 

 

RuiCunha_37-1648283433387.png

 

 

Once the release is successfully finished, you can log into your target database to confirm the objects that have been deployed.

 

RuiCunha_38-1648283454728.png

 

Conclusion

 

When using Azure Synapse Analytics across multiple environments, you can take advantage of Azure DevOps capabilities to automate the integration and delivery of your work, either resulting from Synapse Studio (Workspace artifacts) or from your SQL pools (database objects).  With regard to the latter, you learned how to use Visual Studio capabilities to improve your Continuous Integration process by integrating all the database changes in Azure DevOps. Finally, and adding to this, you also learned how to improve your Continuous Delivery process by using Release Pipelines to deliver these changes to a target environment. 

4 Comments
Co-Authors
Version history
Last update:
‎Mar 30 2022 01:59 PM
Updated by: