{"id":590,"date":"2020-10-28T15:49:18","date_gmt":"2020-10-28T22:49:18","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=590"},"modified":"2020-10-28T15:49:18","modified_gmt":"2020-10-28T22:49:18","slug":"continuous-delivery-for-azure-sql-db-using-azure-devops-multi-stage-pipelines","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/continuous-delivery-for-azure-sql-db-using-azure-devops-multi-stage-pipelines\/","title":{"rendered":"Continuous Delivery for Azure SQL DB using Azure DevOps Multi-stage Pipelines"},"content":{"rendered":"<p>In our previous overview of <a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/devops-for-azure-sql\/\">DevOps for Azure SQL DB<\/a>, we showed you how you can use the built-in tasks in Azure Pipelines to deliver changes continuously to an Azure SQL database. <a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/devops-for-azure-sql\/#build-and-release-pipelines\">That pipeline<\/a> was quite simple, and did not have a separate step to create the database. It also did not have any built-in mechanism for approvals.<\/p>\n<p>A more realistic scenario will have increased requirements. As an example, let\u2019s consider the following requirements for our pipeline:<\/p>\n<ol>\n<li>Have separate build (CI) and deliver (CD) stages in our database deployment pipeline.<\/li>\n<li>Use Azure CLI, running on a Linux-based agent, to create the logical Azure SQL server and Azure SQL DB.<\/li>\n<li>Use declarative \u201ceverything-as-code\u201d principles to drive the actual deployment actions:\n<ul>\n<li>Do nothing if the server and \/ or the database exists (\u201cidempotent\u201d deployments).<\/li>\n<li>Update the target to match the desired final state (for example, Service Objective) if needed.<\/li>\n<\/ul>\n<\/li>\n<li>Introduce an explicit approval requirement before actual DB deployment can proceed.<\/li>\n<\/ol>\n<p>The above are just some common requirements \u2013 the key point is that customers like yourself, would like to blend usage of preferred tools (including third party tools like Terraform, Ansible etc.), and preferred workflow, on top of the core step of deploying changes to the database.<\/p>\n<h2>Let&#8217;s get started!<\/h2>\n<p>This post shows you how you can easily accomplish these requirements. The tools and services we will use include Azure DevOps multi-stage pipelines, Azure CLI and the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/tools\/sqlpackage-download\">sqlpackage tools<\/a>. Let\u2019s briefly look at each of these tools individually before putting them all together.<\/p>\n<h3>Azure Pipelines<\/h3>\n<p>In case you are not familiar, <a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/devops\/pipelines\/\">Azure Pipelines<\/a> are comprised of a series of <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/process\/stages\">Stages<\/a>, each Stage in turn containing one or more <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/process\/phases\">Jobs<\/a>, each job in turn comprising of a set of Steps, which in turn contain <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/process\/tasks\">Tasks<\/a>. <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/get-started\/key-pipelines-concepts\">This blog post<\/a> does a great job summarizing the terminology and concepts. Specifically, Multi-Stage Pipelines allow a great degree of control and flexibility \u2013 see <a href=\"https:\/\/devblogs.microsoft.com\/premier-developer\/azure-devops-pipelines-multi-stage-pipelines-and-yaml-for-continuous-delivery\/\">this post<\/a> which has a great overview of those capabilities. Each job can execute on a distinct <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/agents\/pools-queues\">agent pool<\/a>, opening up a lot of possibilities even in a hybrid or tightly locked down setup. The key point here is that by organizing our pipeline into distinct set of stages and jobs, and by associating approval requirements with specific \u201cenvironments\u201d, we can accomplish all of the above stated requirements (and potentially much more).<\/p>\n<h3>CRUD tool options<\/h3>\n<p>There are several tooling options which support create, update, and delete (CRUD) operations for Azure SQL DBs and servers. While T-SQL supports <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/single-database-manage#transact-sql-t-sql\">Azure SQL DB creation<\/a>, other operations like creating a logical Azure SQL server, an elastic pool, etc. need to be done externally by eventually invoking the core Azure APIs. These tools wrap the relative complexity of working directly with the APIs and are easier to work with for most users. Most of these tools operate on the principle of declaratively specifying the intended state, and internally figure out what needs to be done to get there.<\/p>\n<h4>Azure CLI<\/h4>\n<p>The Azure Command Line Interface is a popular, multi-platform tool which provides (among many other commands) convenient way to create a <a href=\"https:\/\/docs.microsoft.com\/en-us\/cli\/azure\/sql\/server?view=azure-cli-latest#az_sql_server_create\">logical Azure SQL server<\/a>, to <a href=\"https:\/\/docs.microsoft.com\/en-us\/cli\/azure\/sql\/db?view=azure-cli-latest#az_sql_db_create\">create an Azure SQL DB<\/a> etc. In specific cases, Azure CLI is <em>idempotent<\/em>, which means that it will ensure that nothing is done if the target already exists. Also, if the target exists (albeit with different configuration) the target will be automatically updated to match the required configuration. This is because the create DB command internally reduces down to an underlying <a href=\"https:\/\/docs.microsoft.com\/en-us\/rest\/api\/sql\/Databases\/CreateOrUpdate\">Databases \u2013 Create Or Update<\/a> API call (for those interested, the corresponding source code for the AZ CLI is <a href=\"https:\/\/github.com\/Azure\/azure-cli\/blob\/dev\/src\/azure-cli\/azure\/cli\/command_modules\/sql\/custom.py#L818-L822\">here<\/a>). So, as an example, if the target DB exists, but has a different <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/resource-limits-vcore-single-databases\">service (tier) objective<\/a> of HS_Gen5_2, the DB will be updated to the desired service objective (let\u2019s say, HS_Gen5_24) etc.<\/p>\n<p>While many Azure CLI operations are \u201cidempotent\u201d in a way, Note that not all other Azure CLI operations are idempotent \u2013 you should test and validate if they behave as intended in your specific scenario. Ultimately, since the source code for the Azure CLI is <a href=\"https:\/\/github.com\/Azure\/azure-cli\">available on GitHub<\/a>, it should be feasible to determine the intended behavior outside of just empirical testing.<\/p>\n<h4>Azure PowerShell etc.<\/h4>\n<p><a href=\"https:\/\/docs.microsoft.com\/en-us\/powershell\/azure\">Azure PowerShell<\/a> also provides cmdlets for Azure SQL CRUD operations. You can refer to the docs for <a href=\"https:\/\/docs.microsoft.com\/en-us\/powershell\/module\/az.sql\/new-azsqldatabase\">New-AzSqlDatabase<\/a> as an example.<\/p>\n<h4>Terraform, Ansible etc.<\/h4>\n<p>Some customers prefer to use third-party Infrastructure-as-Code (IaC) tools like <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/developer\/terraform\/overview\">Terraform<\/a> or <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/developer\/ansible\/overview\">Ansible<\/a> (there are other tools like these &#8211; this is not a comprehensive list) when working with their Azure SQL environments. The good news (as you will see below) is that both these tools can leverage the underlying AZ CLI environment, in the context of an Azure DevOps pipeline, to obtain a security context to execute their requests against Azure. Also, while some IaC tools like Ansible do not support running on Windows, the good news is that AZ CLI command line supports running on Linux and macOS. Though we don\u2019t use any of these third party tools in this blog post, we\u2019ll see how we accommodate this potential OS requirement when we put all of this together later in this post.<\/p>\n<h3>sqlpackage<\/h3>\n<p>Earlier in this post, we linked to the sqlpackage download page. In this blog post, we used this tool to support a declarative \/ state-based approach to database deployments. For more details on this approach, and alternatives therein, please refer to our previous overview of <a href=\"https:\/\/devblogs.microsoft.com\/azure-sql\/devops-for-azure-sql\/\">DevOps for Azure SQL DB<\/a>. Sqlpackage is internally invoked by built-in activities within Azure DevOps to facilitate the deployment of changes to the target DB. For the core DB deployment step, this tool is idempotent \u2013 if the target database is already at the intended state, no changes are needed to the target DB. (Fine print: if the developer adds <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/ssdt\/how-to-specify-predeployment-or-postdeployment-scripts\">pre-deployment, or post-deployment<\/a> scripts to the SQL Project, idempotency of those scripts needs to be handled by the developer in the T-SQL code).<\/p>\n<p>It is useful to consider that while sqlpackage has some capabilities to create an Azure SQL DB as part of the deployment, it is rather limited. Our recommendation is to leverage Azure CLI, PowerShell or IaC tools to manage the CRUD of the server \/ pool \/ database, and then use sqlpackage to deliver the in-database changes. If you use other migration-based libraries like Flyway or DbUp, you will still need to rely on the Azure CLI, PowerShell or IaC tools for CRUD operations.<\/p>\n<h2>Putting it all together<\/h2>\n<p>The source code for the multi-stage Azure DevOps pipeline is available <a href=\"https:\/\/github.com\/arvindshmicrosoft\/azure-sql-devops\/blob\/add-pipeline\/azure-pipelines\/deploy-sqlproj.yml\">here<\/a>. The pipeline has 3 distinct stages:<\/p>\n<ul>\n<li>CreateDB \u2013 this stage has a single job, which uses the <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/tasks\/deploy\/azure-cli\">Azure CLI task<\/a> for CRUD of the database. This stage runs on an Azure DevOps-hosted Linux agent (to illustrate the flexible OS choice).<\/li>\n<li>BuildDACPAC \u2013 we build the DACPAC from the SQL project source code, and <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/artifacts\/build-artifacts\">publish it as an artifact<\/a> for subsequent deployment task. The DACPAC build job in this stage runs on a self-hosted Windows agent and uses the <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/tasks\/build\/visual-studio-build\">VSBuild task<\/a> to compile the SQLProject into a DACPAC. Note, this illustrates the flexibility on OS and environments that agent pools offer us.<\/li>\n<li>DeployDB \u2013 this stage contains 2 jobs \u2013 one used solely for the purposes of \u201cgating\u201d deployment till an approval is (manually) granted as part of a fictional workflow; the second job actually <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/tasks\/utility\/download-build-artifacts\">downloads the DACPAC artifacts<\/a>, and then deploys the DACPAC to the target logical Azure SQL server using the built-in <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/tasks\/deploy\/sql-azure-dacpac-deployment\">Azure SQL DB deployment task<\/a>.<\/li>\n<\/ul>\n<p>It is worth noting that Microsoft periodically updates the versions of the software (including AZ CLI and SQLPackage) on the Azure DevOps \/ Microsoft-hosted agents. The list of exactly which versions are currently installed is documented <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/agents\/hosted?view=azure-devops&amp;tabs=yaml#software\">here<\/a>.<\/p>\n<h3>Pipeline Artifacts<\/h3>\n<p>It is important to note that each job is run on a different agent VM. Even if it was part of the same pool, the VM state is reset between job runs. Hence, the usage of artifacts to save state and enable later jobs in the same pipeline to operate on the same effective state, is an important technique we leverage.<\/p>\n<h3>Pipelines UI<\/h3>\n<p>Here\u2019s how the pipeline looks when viewed in the Azure DevOps UI:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-591\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image.png\" alt=\"CD for Azure SQL using multi-stage pipeline\" width=\"762\" height=\"362\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image.png 960w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-300x143.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-768x365.png 768w\" sizes=\"(max-width: 762px) 100vw, 762px\" \/><\/p>\n<p>The main supporting objects for the above pipeline are described below.<\/p>\n<h3>Environment<\/h3>\n<p>Azure Pipelines allow the definition and targeting of specific \u201cenvironments\u201d as described <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/process\/environments\">here<\/a>. Environments can be configured so that they <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/process\/approvals\">require approvals<\/a> to deploy into. In this example, we configured the approval requirement so that a user called \u201cAzure SQL PR Reviewer\u201d needs to approve:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-592\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-1.png\" alt=\"Set an approval requirement for an environment in Azure Pipelines\" width=\"765\" height=\"321\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-1.png 1094w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-1-300x126.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-1-1024x430.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-1-768x322.png 768w\" sizes=\"(max-width: 765px) 100vw, 765px\" \/><\/p>\n<p>Here\u2019s how the pipeline UI looks when it is pending on approval for the \u201cProduction\u201d environment:<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-593\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-2.png\" alt=\"Pipeline waiting on approval before proceeding to actual DACPAC deployment\" width=\"873\" height=\"451\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-2.png 983w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-2-300x155.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-2-768x397.png 768w\" sizes=\"(max-width: 873px) 100vw, 873px\" \/><\/p>\n<p>The required approver, in turn, gets a notification which looks like this:<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-594\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-3.png\" alt=\"Approver's view of pipeline awaiting approval\" width=\"1517\" height=\"384\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-3.png 1517w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-3-300x76.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-3-1024x259.png 1024w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-3-768x194.png 768w\" sizes=\"(max-width: 1517px) 100vw, 1517px\" \/><\/p>\n<p>The DeployDB stage will proceed to the actual DACPAC deployment job, once the approval is granted.<\/p>\n<h3>Secrets Management \u2013 Library<\/h3>\n<p>Azure Pipelines <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/library\/\">Library<\/a> is where you can define and populate secrets. As seen below, we can store essential variables used by our sample pipeline in the Library. Variables can be marked as secret, so they are protected from accidental disclosure. You can also integrate with Azure Key Vault to access secrets.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-595\" src=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-4.png\" alt=\"Using Azure Pipelines Library for secrets\" width=\"565\" height=\"516\" srcset=\"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-4.png 876w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-4-300x274.png 300w, https:\/\/devblogs.microsoft.com\/azure-sql\/wp-content\/uploads\/sites\/56\/2020\/10\/word-image-4-768x701.png 768w\" sizes=\"(max-width: 565px) 100vw, 565px\" \/><\/p>\n<h3>Service Connections \u2013 Azure subscription<\/h3>\n<p>The <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/tasks\/deploy\/azure-cli\">AzureCLI task<\/a> within the pipeline requires a \u201cService Connection\u201d to an Azure subscription. The details on how to do this are <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/devops\/pipelines\/library\/service-endpoints\">here<\/a>. The service principal used for this purpose should have the required permissions for CRUD operations at a given resource group level.<\/p>\n<h2>Conclusion<\/h2>\n<p>Multi-stage pipelines in Azure DevOps offer a rich, declarative, YAML-based mechanism to implement and customize pipelines. Integrating multiple tools like Azure CLI, sqlpackage etc. across different OS platforms (Linux and Windows as shown in this example) is very easy, and allows you to quickly implement your Azure SQL DB CI \/ CD pipelines with maximum flexibility and alignment with other networking and security requirements. The sample YAML pipeline definition <a href=\"https:\/\/github.com\/arvindshmicrosoft\/azure-sql-devops\/blob\/add-pipeline\/azure-pipelines\/deploy-sqlproj.yml\">here<\/a> will hopefully help you get a quick start towards this!<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Multi-stage pipelines in Azure DevOps offer a rich, declarative, YAML-based mechanism to implement and customize Continuous Delivery for Azure SQL.<\/p>\n","protected":false},"author":32627,"featured_media":593,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1,92],"tags":[95,465,469,30,93,355],"class_list":["post-590","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-sql","category-devops","tag-azure-devops","tag-azuresql","tag-azuresqldb","tag-developers","tag-devops","tag-tools"],"acf":[],"blog_post_summary":"<p>Multi-stage pipelines in Azure DevOps offer a rich, declarative, YAML-based mechanism to implement and customize Continuous Delivery for Azure SQL.<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/590","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/32627"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=590"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/590\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/593"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=590"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=590"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=590"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}