How to Provision Azure SQL Database using Ansible

How to Provision Azure SQL Database using Ansible

Learn about writing a playbook with Ansible to provision the Azure SQL database. Learn about Ansible and its design constructs and concepts.

In this article, you will learn about writing a playbook with Ansible to provision the Azure SQL database. In addition, you will also learn about Ansible and its design constructs and concepts.

In this article, you will be introduced to the following concepts:

  1. Overview of Ansible
  2. Detail the automation abstraction
  3. Ansible playbook
  4. Prepare the environment
  5. Writing tasks and handlers
  6. Deploy Azure SQL Database with Ansible playbooks
  7. And more…

Overview of Ansible

Ansible is a simple, flexible, open-source, extremely powerful tool that gives us the ability to automate common infrastructure tasks, run ad-hoc commands or scripts, deploy the multi-tier application, and simplify cloud provision or resource management tasks. It gives us the flexibility to launch commands on a number of hosts in parallel. In addition, it helps seamless deployment and effectively-and-efficiently manages the deployment process using playbooks.


In my previous article, we discussed a lot about automation. Let us dive deep into some of the concepts. In general, If you’re managing a smaller group of IT infrastructure, some IT developers and administrators manage the mundane tasks by logging into them via SSH or RDP, making the required changes on the servers. In some cases, it is a hard fact in the bigger estate, many of them have still adopted a legacy manual method to handle the repetitive tasks.

Let’s consider a scenario, the Administrator needs to make the same change to a default value in a configuration file. In this case, without automation, the administrator would manually login to the server and repeatedly make the required change. If the server estate is small and this could be a simple exercise. In addition, if every change were thoroughly documented, this process wouldn’t be a problem. But, you all would agree that almost every company in existence, in-house complex and custom set up—in most cases, multiple applications are built that involve several customizations to the servers and configuration. For example, In my case, most servers have complicated firewall rules and dozens of tweaked parameters in the configuration files. And even with very good documentation and process, the manual process usually results in error-prone execution and failure in successful implementation.

I bet, some of you might be the admins of such companies and might have faced one of the several aforementioned scenarios. If you wanted to set up a new server exactly like one that is currently running then you would need to spend a good deal of time going through all the documentation, configurations, installed packages, and custom settings; and then you need to spend a lot of time manually reinstalling, updating, and tweaking parameters everything to get the new server to run close to your old server.

I have used PowerShell or Perl or Python scripts in all the automation but I’ve yet to see a complete skeleton of the script that handles all edge and use-cases which synchronize multiple server configuration and deploy new code.


Using Ansible, it is easy to run shell or batch commands or database scripts or configuration script or python script or handle service or deploy the installation process. In addition, it also provides a platform for converting the tasks into idempotent playbooks.

Ansible designed to operate from the central servers or control servers that push the instruction out to all the target servers and run locally on the target machines with no extra resource overhead incurred to manage the internal process.

Ansible comes with a wide variety of built-in modules that allow us to manage various system components, for example, users, group, iptables, systemd, mount points, packages, network, files, and services; commands; files; databases; cloud; windows; and more.

Prepare the environment

The first step is to create an Azure subscription if you don’t have one already. You can sign up for the free trial subscription and then get started.

Let us get started:

  1. Log-in to the Azure Portal
  2. On the top-right corner, you could see a PowerShell icon to open the Cloud Shell. If this is the first time you’re trying to open then you need to configure the Cloud Shell

login to Cloud shell

  1. Next, open the BASH shell.
  2. The shell authenticates the ansible tool using the environment variable. The authentication is based on the subscription id you are tagged using the shell.

In my case, my login is associated with multiple subscriptions. You can type in az account list and list the one which you want to use for the Azure SQL database provisioning using Ansible.

To list the subscriptions, run the following az command:

$az account list - -output table

Azure subscription list

Select the subscriptionId and set the AZURE_SUBSCRIPTION_ID environment variable using the export command.

Export Azure Subscription

To view the environment variable run echo $AZURE_SUBSCRIPTION_ID.

Export Azure Subscription


A playbook consists of one or more plays, which map groups of hosts to well-defined tasks. The preceding example contains four plays, each to configure one layer in the Azure SQL databases. In addition, plays also define the order in which tasks are configured. This allows us to orchestrate multitier deployments. For example, to configure Azure SQL Server—first, create a resource group, after that, start a new task to configure Azure SQL instance; Azure SQL databases and followed by firewall configuration.

Let’s now analyze what happened:

Ansible reads the AzureSQLProvision.yaml playbooks specified as an argument to the ansible-playbook command and starts executing plays (or tasks) in the same serial order in which it is created in the YAML.

azure sql database ansible developer

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Introduction to Structured Query Language SQL pdf

SQL stands for Structured Query Language. SQL is a scripting language expected to store, control, and inquiry information put away in social databases. The main manifestation of SQL showed up in 1974, when a gathering in IBM built up the principal model of a social database. The primary business social database was discharged by Relational Software later turning out to be Oracle.

Migrating SQL workloads to Microsoft Azure: Databases Trip to Azure SQL Database

In this article, we will discuss how to migrate an on-premises SQL Server database to Microsoft Azure SQL Database.

Migrate an On-premises SQL Server Database to an Azure SQL dDatabase

In this article, we will discuss and understand a method to migrate on-premises SQL Server database to Azure SQL database. This article will look at ways to modernize and transform applications and infrastructure by taking advantage of the agility and flexibility of the cloud

Azure SQL Database vs. SQL Server on Azure VMs

This article will help you understand the key differences between Azure SQL database and SQL Server on Azure VMs and options that work best for you. Azure SQL Database vs SQL Server on Azure VMs

Azure SQL Database Deployment Options | Azure SQL for beginners (Ep. 9)

This video dives deeper into the deployment options and service tiers for Azure SQL Database. For the full Azure SQL Fundamentals learning path on Microsoft ...