Azure database maintenance using Azure automation

One of the things often overlooked with Azure SQL databases is the fact that they need love in exactly the same way as their on-premises brethren. Sure, Azure will keep an eye out for the most blatant issues, but the old adage “a penny of prevention is worth a pound of cure” still holds water. Pieter Vanhove did a great blog post a few years back, but since then Larry Silverman made some updates to the original script so I decided to revisit the whole thing and put everything in the same place. A big thank you to both Pieter and Larry for the excellent groundwork.

The trouble with Azure SQL Database is the lack of an SQL Agent to schedule the usual scripts – like Ola Hallengren’s excellent Maintenance Solution. Another issue is the lack of cross-database scripting support in Azure, making the possibility of executing Ola’s scripts from a DBA-DB somewhere impossible.

There is a way to have Ola’s script maintain each and every database on your Azure SQL Database server, but due to the lack of cross-database scripting mentioned earlier, it requires that the procedures IndexOptimize and CommandExecute as well as the table CommandLog be set up in each and every database you deploy to your server. I’d recommend putting them in their own schema to be easily distinguished from the rest of the database, and in the example below I use the schema “dba”.

Download IndexOptimize.sql, CommandExecute.sql and CommandLog.sql from Ola’s site here.

Open them up in a text editor and run a search/replace for all instances of dbo and change that to dba. This is to make sure they end up where they are supposed to. Then let’s get cooking:

1. Create a DBA schema in the Azure SQL Database.

2. Run the three scripts you downloaded earlier, making sure that the two procedures and the table end up in the DBA schema.

At this stage you can test the scripts by running the following code and taking a look in the dba.CommandLog table:

3. Let’s create an automation account.

Click “Add” to create one.

Then we input a name for the account, a resource group and a location. For performance reasons it’s best to let the automation account be placed in the same datacenter as the database itself.

With the account set up, go ahead and click on “credentials” on the left. We need to set up a stored credential to enable the runbook to log into the database server.

Set a name of the credential (this will be referenced later), input the administrative user for the database server and the password for said user.

Go ahead and click on “overview”, folloed by “runbooks”.

On the runbook blade, click “Add a runbook” and fill out the name and the runbook type. The name must be the same as in the code we’re going to paste in and the type must be PowerShell Workflow.

Paste in the following code, overwriting the text that’s already there:

I’ve made some very, very slight modifications to the script that Larry Silverman in turn modified from Pieter Vanhove’s original script. My modifications consist of changing schema name from DBO to DBA and turning on logging.

At this stage it’s a good idea to test te runbook, so click “Test pane” up top. Input the parameters (the fully qualified server name and the name of the credential we set up previously) and make sure everything runs as it should.

If everything worked as expected, let’s go ahead and schedule the runbook.

Click schedule.

This part looks a lot like scheduling a job via the agent.

This is the only difference from using the agent. Input the parameters required (database server and credential for the server administrator) and you’re good to go.

A word of caution: as each and every database will hold it’s own copy of the scripts and table, it’s imperative to keep them at the same version. I.e, when updating the scripts in one database, make sure you do it on all the other databases on the same server.

Azure Automation and index maintenance gotchas

I decided to write up a blog post about index maintenance in SQL Azure databases (yes, I’m gravitating towards Azure just like everyone else these days). I had everything planned, what research to do, the case I was going to use, the whole nine yards. It would have been epic, had it not been for Pieter Vanhove, who’s already done it. Worse still, he did it way better than what I had planned.

Somewhat miffed, I decided to play with it anyway (just not write a blog post about it, no, I’m not bitter 😛 ).

Turns out there’s quite a gotcha with this:

AzureDB does not support cross database queries, which means that, since the runbook Powershell script creates a connection to the MGMTDB, it is ONLY within that database that the queries will execute. You can check this using a simple select * from sys.databases – it will ONLY show you the user database you’re in and the system databases. Ergo, Ola’s script will only execute on the database it is installed in. Okay, you say, let’s create the scripts in the MASTER database (generally a bad idea, but hey) and run them, as if I create a connection to the MASTER database, sys.databases shows every other user database on the server. Turns out that, well, you can’t. There seems to be no way to create objects in the master database, so we’re stuck at the moment.

Ola’s solution is to create his scripts in every user database, but while this works most excellent from a performance and integrity standpoint, the amount of manual labor required to either create new runbooks for each and every database that gets created (and remove said runbook for decommissioned databases) OR create a potentially HUGE runbook that encompasses all databases (that also needs to be manually maintained, ugh) is simply staggering. The hunt for a solution is on.



Aha! While attending the PASS Summit I got word from Brent Ozar that cross-database queries had arrived to Azure. I’ve yet to track down Ola and ask him about his scripts as the implementation is … maybe not quite as straight-forward as one might think, but the solution is a lot closer. Oh, and I spoke to Pieter as well – he’s updated the original blog post with his workaround. It’s a good example of what can be done with some ingenuity and a runbook. Check it out!