Deploying .dapacs to Azure SQL via Azure DevOps Pipelines

  • Thread starter Thread starter j_folberth
  • Start date Start date
J

j_folberth

_20b0d982-09ce-4f68-b4d2-4b1516177a98.jpg​




Introduction​


This post is part of the SQL Database series that I am compiling. This specific topic assumes that you have already built a .dacpac file via Azure DevOps YAML Pipeline and are ready to now deploy your .dacpac to Azure. Congratulations! If you'd like to follow along all source code is in my GitHub repository.



PreReqs​


To be successful here we'd require some items to be setup:




Deploy Steps​


When writing one of these I have found it can be helpful to write out the individual steps required for our build. In our case it will consist of:

  • Download the pipeline artifact
  • Open up on the Azure SQL Server Firewall to the Agent
  • Deploy .dacpac
  • Delete the Azure SQL Server Firewall rule



The good news here is that first, the job will automatically download the pipeline artifact. To reiterate this will download the .dacpac which was built in the previous stage so that subsequent jobs can leverage it for deployments to one or more environments. The second piece of good news is the opening of the Azure SQL Server Firewall rules can be handled by SqlAzureDacpacDeployment@1 task. In addition, there is the option to delete the firewall rule after the task has been completed.



So, this means we effectively just need a single job in our deployment stage!

SqlAzureDacpacDeployment@1​


Here is the YAML code for the job to handle the deployment:







Code: 
 jobs:
  - deployment: sqlmoveemecicd_app_dev_eus
    environment:
      name: dev
    dependsOn: []
    strategy:
      runOnce:
        deploy:
          steps:
          - task: SqlAzureDacpacDeployment@1
            displayName: Publish sqlmoveme on sql-moveme-dev2-eus.database.windows.net
            inputs:
              DeploymentAction: Publish
              azureSubscription: [Insert Service Connection Name]
              AuthenticationType: servicePrincipal
              ServerName: [SQL Server Destination]
              DatabaseName: [SQL Database Destination]
              deployType: DacpacTask
              DacpacFile: $(Agent.BuildDirectory)\sqlmoveme\**\*.dacpac
              AdditionalArguments: ''
              DeleteFirewallRule: True







So, an item here to discuss which is a bit of a pre-requisite when discussing Azure DevOps YAML Pipelines is the deployment job concept. I do cover this in a previous post in the YAML Pipeline series on Azure DevOps Pipelines: Tasks, Jobs, Stages. Suffice it to say deployment jobs are special types of jobs in Azure DevOps which are to be leveraged for the actual deployment of artifacts and one of the key capabilities of deployment jobs is the ability to tie them to an environment. Environments can have gates which is a set of criteria that can include manual or automatic checks prior to deployment.



Lets take a step back and talk a little more on some of the requirements. First, we need to establish authentication from Azure DevOps to our Azure SQL Server. The most secure way to do this would be through Entra Authentication. The credentials we will be using would be the service principal associated with an Azure DevOps Service connection. This connection will either have credentials stored as part of the App Registration or leveraged workload identity federation.



Personally, I would recommend using the workload identity federation process as this will eliminate the need for a secret. This Service Connection can be the same one used to deploy other resources in the environment, though I understand and respect the separation of data and management plane activities so a separate one specific to the database is acceptable. If you'd rather not use Entra Auth for authenticating to the database, you can alternatively pass credentials stored in a Variable Group, though usually it's a good idea not to use passwords when possible.



So now that we know how and what we are going to authenticate with it's time to go over how the access to the service account would be provisioned. When configuring an Azure SQL Server one can designate an Entra Security group as the Admin.



Below is a screenshot showing that the Microsoft Entra Admin ID has been granted to an Entra Security Group. If this is a new concept please follow up with Using automation to set up the Microsoft Entra admin for SQL Server - SQL Server | Microsoft Learn. Additionally, here is a great walkthrough put together by MVP Stephan van Rooij, Azure SQL and Entra ID authentication, tips from the field.



j_folberth_0-1725546136390.png



The service principle being used for the deployment is in turn added to this group. Thus, our deployment will have full access to deploy the .dacpac to the Azure SQL Server. Additionally, I have Microsoft Entra Authentication only configured; this is considered a best practice as SQL user credentials expose a potential credential liability. If new to this concept feel free to read more on Microsoft Entra-only authentication - Azure SQL Database & Azure SQL Managed Instance & Azure Synapse Analytics | Microsoft Learn



Results​


After we add this deployment stage to our previous build stage our results in ADO will look like:

j_folberth_0-1725548369525.png

A two stage pipeline where the first stage will generate an artifact of our .dacpac and the second stage which will take the .dacpac produced in the first stage and deploy it. A complete YAML definition of this pipeline can be found on my GitHub repository.



Next Steps​


Now that we have covered how to effectively build .sqlproj into a .dacpac anddeploy said .dacpac to Azure our next step will be to deploy tto multiple environments via different configurations! Feel free to subscribe to this series on SQL Databases alternatively if you like my posts feel free to follow me.

Continue reading...
 
Back
Top