In our previous overview of DevOps for Azure SQL DB, we showed you how you can use the built-in tasks in Azure Pipelines to deliver changes continuously to an Azure SQL database. That pipeline 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.

A more realistic scenario will have increased requirements. As an example, let’s consider the following requirements for our pipeline:

1. Have separate build (CI) and deliver (CD) stages in our database deployment pipeline.
2. Use Azure CLI, running on a Linux-based agent, to create the logical Azure SQL server and Azure SQL DB.
3. Use declarative “everything-as-code” principles to drive the actual deployment actions:

• Do nothing if the server and / or the database exists (“idempotent” deployments).
• Update the target to match the desired final state (for example, Service Objective) if needed.

1. Introduce an explicit approval requirement before actual DB deployment can proceed.

The above are just some common requirements – 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.

Let’s get started!

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 sqlpackage tools. Let’s briefly look at each of these tools individually before putting them all together.

Azure Pipelines

In case you are not familiar, Azure Pipelines are comprised of a series of Stages, each Stage in turn containing one or more Jobs, each job in turn comprising of a set of Steps, which in turn contain Tasks. This blog post does a great job summarizing the terminology and concepts. Specifically, Multi-Stage Pipelines allow a great degree of control and flexibility – see this post which has a great overview of those capabilities. Each job can execute on a distinct agent pool, 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 “environments”, we can accomplish all of the above stated requirements (and potentially much more).

#azure sql #devops #azure devops #azuresql #azuresqldb #developers #tools