Joey on SQL Server

How To Automate Tasks in Azure SQL Database

Knowing how to automate tasks in the cloud will make you a more productive DBA. Here are the key concepts to understand about cloud scripting and a rundown of the best tools for automating code in Azure.

• By Joey D'Antoni
• 01/22/2021

The cloud makes everything "software defined," which means all resources are accessible via an API or some other code path. Since there is a software endpoint for everything, you can encapsulate nearly all tasks into code. What does that mean for database administrators (DBAs) and system administrators?

When you move to the cloud, you should learn how to script against your cloud platform -- whether that scripting language is PowerShell, CLI or even Python. This column will focus on doing this in Microsoft Azure, but Amazon Web Services (AWS) offers similar options in terms of scripting and automation.

Cloud Scripting Basics
There are a couple of important concepts to understand about scripting in the cloud. The first is the concept of the "control plane." The control plane is where you define and manage the resources in your cloud environment. This is where you define resources like virtual networks, databases, virtual machines (VMs) and everything else.

When we talk about automation, it is important to separate the concept of the control plane from those resources. While operations on the control plane are fairly straightforward, operating within those resources (e.g., executing a task inside of a VM) requires a bit of extra work. A basic example of this is creating a VM as opposed to executing a program within that VM. Creating the VM simply requires authentication and authorization from the cloud platform, while executing the program within the VM requires you to perform that initial authorization and then authenticate into the VM to execute that program.

Automating things for SQL Server DBAs in a traditional on-premises world was easy: You simply used the SQL Server Agent, a service that provides scheduling, notifications and job handling. Given the long history of the agent (it was introduced in version 7.0), there are a multitude of open source scripts, blog posts and general knowledge related to using it to manage database jobs and beyond.

How does the cloud change this? First of all, Azure SQL Database does not have the SQL Server Agent as a service. If you are running SQL Server in an Azure VM, or are using Azure SQL Managed Instance, you can use the Agent but may have needs that are beyond its scope.

Here's an example from one of our clients. They have an Azure VM running SQL Server that is used for running an extract, transform and load (ETL) process. To save money, this VM is powered down when not in use. However, we still need to take a daily backup of this VM. In order to execute that backup, we check the status of the VM: Is it running, or is it stopped? If the VM is powered off, it gets turned on and a backup is executed from within the VM.

While this task seems simple, it requires some pretty advanced scripting techniques. Let's talk about the ways you can automate things in the Azure platform.

Azure Automation
The most obvious solution for automating code in Azure is Azure Automation. You have an Automation account, and within that account, you create runbooks -- the units of work -- where you build your scripts. Your code options for your runbooks are PowerShell and Python, but you also have the option to use graphical runbooks, though they have limited functionality. In my experience, 99 percent of the runbooks I've seen have been written in PowerShell. Automation runbooks can be executed on a schedule or called on-demand.

You will also need to import the specific PowerShell modules you need into your Automation account. Anything that is in the PowerShell gallery is available in Automation. It also provides functionality for scheduling and secure credential storage, though I would recommend storing credentials in Azure Key Vault, as it has a more robust security model.

By default, Automation operates on the control plane. This means you can operate on Azure resources or even within an Azure SQL Database using cmdlets from the Az.SQL module. In order to execute runbooks inside of a VM, you will need to configure a hybrid runbook, which will require configuration within the VM. Once that is completed, you can run scripts on that VM. This hybrid model can also work for on-premises VMs, as long as they have network access to Azure.

Automation can be a bit daunting for a new user, but it is robust and -- due to the flexibility of scripting -- can be very powerful. 

Logic Apps
While Automation is powerful and complex, Azure Logic Apps is very easy to get started with.

Logic Apps (and Automation, too) falls into a class of cloud services called "serverless" computing, which means that while they still run on a server, you do not need to think about what server they run on; it's handled by the platform.

Additionally, Logic Apps is considered to be a low-code platform. Low-code platforms allow you quickly build automation flows using predefined connections, triggers and actions. You can quickly connect them together. Take the example shown in the figure above: When an item is added to a SharePoint list, an automation runbook is triggered, then an e-mail is sent with the output of the runbook.

While this is a fairly complex example, you can do much simpler operations like execute queries against Azure SQL Database. Logic Apps can be executed on a schedule or be triggered by an action (as in the above example). Until recently, Logic Apps had a two-minute timeout, but recently Microsoft added functionality for longer-running operations. 

Elastic Jobs
Elastic Jobs is specific to Azure SQL Database and allows you to execute T-SQL across a group of databases. This feature is still in preview of this writing, but it was introduced in 2015, so it is probably not going away anytime soon.

The benefit of Elastic Jobs is that you can take a T-SQL-only approach. Elastic Jobs is somewhat similar to the SQL Server Agent. However, it's somewhat limited in that it can only operate on Azure SQL Database. Elastic Jobs also requires you to create an Azure SQL Database to manage the jobs.

One other option that I don't have the space to fully address here is Azure Functions. Azure Functions is similar to Logic Apps but can be fully customized using a number of programming languages. Functions can do just about anything in Azure, or anything you can do in a programming language.

As you can see, there are a number of ways to automate tasks in Azure. For most of our customers, I have been able to do everything that I've needed to do using Automation and Logic Apps. Typically, my rubric is if I need to operate on a large number of resources, I will use Automation. If there is a one-off task or something I just need to do quickly, I will create a Logic App. In practice, I haven't used Elastic Jobs much, because I find Automation works much better in most cases.

At any rate, learning how to automate tasks in your cloud environment will make you a much more productive DBA.