How to provision Azure SQL Database using Ansible

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.

Background

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.

Benefits

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

  3. Next, open the BASH shell
  4. 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:

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

Playbooks

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.

yamllint to validate Azure SQL database configuration

  • Note: I would suggest validating the YAML code using yamllint.com
  • The first play that we declared, runs against the “localhost” host.

    The localhost keyword is a special pattern that will match all hosts (similar to *). So, the tasks in the first play
    will be executed on all hosts.

    Before running any of the tasks, Ansible will gather information about the systems that it is going to configure.
    This information is collected in the form of facts.

    1. The first play includes the creation of the resource group
    2. Ansible then moves to the next play. During the second play, an Azure SQL Server instance is created
    3. In the third play, it will build the database
    4. The fourth play, it will add the firewall configuration

    Finally, Ansible prints the summary of the playbook run in the “PLAY RECAP” section. It indicates how many
    modifications were made if any of the hosts were unreachable, or execution failed on any of the systems


    Playbook output to see Azure SQL database provisiong steps

    Test the Azure SQL database connection using the sqlcmd command in the Cloud Shell.


    test connectivity using sqlcmd

    Summary

    In this article, you learned about automation abstracts, what Ansible playbooks are, what ansible components are
    consist of, and how to build Azure SQL database with ansible-playbook. You also see how to write and validate
    YAML—the core language used to create playbooks. In addition, you understand the working of playbooks by creating an
    Azure SQL database. In the upcoming article, you’ll see how to refactor the code and run the script using Ansible
    playbooks.

    It is always easy to write a simple script and it may work fine for a very simple setup. However, if we have
    multiple applications spanning across a number of hosts, this will quickly become unmanageable—Ansible may be one of
    the solutions to such problems.

    That’s all for now. Stay tuned for more details in my next article…

    Table of contents

    Prashanth Jayaram
    Latest posts by Prashanth Jayaram (see all)

Author: admin

Leave a Reply

Your email address will not be published.