Azure Automation Methods

Over the past year, I have presented many sessions on Azure SQL Database as well as authoring numerous articles and blogs. I’m often asked if database maintenance is still an important factor when using Azure SQL Database. Yes – tasks such as index maintenance, statistics updates, and consistency checking are still important, and it’s up to the DBA to schedule these tasks. The confusion stems from Azure SQL Database being a Platform as a Service and Microsoft being responsible for the infrastructure as well as handling the backups. While some aspects of physical corruption might be accounted for, logical corruption within the database is not. For that reason, I still recommend clients run DBCC CHECKDB to ensure they are fully protected.

The issue that arises with any new DBA working with Azure SQL Database is that there isn’t a built in SQL Server Agent like we’re used to with SQL Server Standard and Enterprise Editions.

To schedule maintenance jobs against an Azure SQL Database, you have a number of options:

• Linked servers
• Database Maintenance Plans
• Powershell
• Azure Services
• Elastic Jobs

The following demos assume you’ve already configured login accounts, firewall rules, and other security settings to remotely access your Azure SQL Databases.

Linked Servers

Connecting to an Azure SQL Database using a linked server is a very common approach since most DBAs are already familiar with creating and managing linked servers. The two most common ways that I’ve seen clients using linked servers is with either the SQL Server Native Client or Microsoft OLE DB Provider for ODBC Drivers as the provider. If you use the native client, you’ll have to provide the server name as the data source; however if using the ODBC driver, then you’ll need to get the connection string and use that as the provider string. Both of these values can be found in the Azure Portal for your database. Once you click on your database, you will see the server name and an option to show the database connection strings. This server name, sqlperformance.database.windows.net is what I would use for the SQL Server Native Client data source.

When you click on “Show database connection strings”, you currently have options for ADO.NET, JDBC, ODBC, and PHP. To see the connection string for ODBC, click on the ODBC tab.

Next you’ll need to create the linked server in SSMS. Under “Server Objects”, right click on “Linked Servers”, select “New Linked Server”, and type in the required information depending on your data source provider choice.

Next click on “Security” and define your preferences. Typically I see the option “Be made using this security context” with a remote login and password provided.

Once you have all this defined, click OK. You can now right click on your new linked server and test the connection.

You can now reference the linked server to call any stored procedures, such as Ola Hallengren’s Index Optimize and DatabaseIntegrityCheck directly against the Azure SQL Database in a SQL Agent job step.

Database Maintenance Plans

If you plan to use a database maintenance plan for your maintenance, the process is a bit easier. To get started, simply create your maintenance plan manually or using the wizard. If you are using the wizard, once you create the maintenance plan, you can edit the plan, and then add the Azure connection. You then change each task to use the new connection. Your connection screen should look similar to the following:

You can now schedule your database maintenance plans to run during your maintenance window.

PowerShell

PowerShell is an excellent option for working with repeatable task and using PowerShell with Azure SQL Database is straight forward. You can use the Invoke-SqlCmd function to query or execute statements against your databases.

A common approach is to use a script similar to:

  $params = @{
   'Database' = 'YourDatabase'
   'ServerInstance' = 'instance.database.windows.net'
   'Username' = 'UserName'
   'Password' = 'ComplexP@$$word'
   'Query' = 'Your Query Here'
  }
  Invoke-Sqlcmd @params

For your query, you can use Ola Hallengren’s index optimize and consistency checks, or any custom script that you have been using. You’ll then need to schedule your PowerShell scripts using whatever scheduler you use for your organization.

Azure Services

Built into the Azure platform is Azure Automation and to get started, you have to create an automation account. You’ll need to provide a name for the account, select your subscription, resource group, location, and determine if you want to create an Azure Run As account.

Once you create your account, you can then start creating runbooks. You can do just about anything with the runbooks. There are numerous existing run books that you can browse through and modify for your own use, including provisioning, monitoring, life cycle management, and more.

You can create the runbooks offline, or using the Azure Portal, and they’re built using PowerShell. In this example, we will reuse the code from the PowerShell demo and also demonstrate how we can use the built in Azure Service scheduler to run our existing PowerShell code and not have to rely on an on-premises scheduler, task scheduler, or Azure VM to schedule a job.

Start by clicking on Runbooks

Next click on “Add a runbook”

Click “Create a new runbook”

Provide a name and runbook type, I selected PowerShell for my demo.

You are then in an edit screen for your new runbook. Here is where you can configure the details and build your runbook. For this demo, I am just running a PowerShell script to call a stored procedure against a database. Once you have all your code worked out, you can publish and save your runbook.

From here, you can start the runbook and validate everything works accordingly, as well as schedule the runbook to run at specific times. Let’s step through this process by clicking on “Schedule”

We’ll need to click on “Link a schedule to your runbook” and since we haven’t created any schedules before, we’ll need to define a new one by clicking on “Create a new schedule”.

Much like we do in SQL Server Agent, provide a schedule name, a description if you would like, when to start, and how often it should run. I set this one occur every day at 2AM.

I now have a published runbook, scheduled to run each night at 2AM to perform index maintenance on one of my databases.

Elastic Jobs

Elastic Jobs is still currently in preview, so I won’t go into great detail due to the high probability that screens and functionality will change.

Using Elastic Jobs requires that you’ve defined an elastic database pool and assign at least one database to the pool to run jobs against. Once you’ve created the elastic pool and added a database, you can then click create job.

Give your job a descriptive name and provide the username and password to connect to the databases with, as well as the script you would like to run. Click save and you now have an elastic job.

You can then chose to run the job, view script, or cancel the job if it is running.

While there are certain things you can do through the Azure Portal with the elastic jobs, the real power and configuration options are available through the PowerShell API. To schedule a job, you have to use the cmdlet New-AzureSQLJobSchedule. More details about the additional features and how to schedule jobs can be found here:

• Create and manage SQL Database elastic jobs using PowerShell

Overall I like the elastic jobs feature and hope that when it is made generally available, that more functionality will be built into the Azure Portal without having to manage it with PowerShell. I like that you can run T-SQL directly, without having to run it within PowerShell and how it can run against all databases in the pool.

Summary

When it comes to Azure SQL Databases, yes, you still are responsible for certain maintenance on your databases. You have numerous methods of scheduling jobs, and depending on your need and the size of your environment, certain options make better solutions. Linked servers and database maintenance plans are quick and easy methods if you have on-premises or Azure VMs with SQL server already configured, and a small Azure deployment. PowerShell is always a good option, you just have to find a solution for scheduling the scripts to run. Azure automation is a very robust solution allowing you to create runbooks to accomplish just about anything and easily schedule the runbooks and elastic jobs is another great Azure based solution if you have tasks that you need to run against a group of databases in an elastic pool.