Azure SQL Databases Maintenance Using Azure Automation and PowerShell

Author: Eric Rouach, Madeira Data Solutions

Date: March 2022

The following guide describes how to execute and schedule any T-SQL command for Each Azure SQL database inside a given Azure SQL Server.

Pre-requisites:

· an existing Azure subscription

· an existing Azure SQL Server and Azure SQL Database(s)

Let’s begin!

In your Microsoft Azure subscription, create a new Automation Account:

Select your subscription, an existing Resource group (or create a new one if needed), Automation account name and Region. Then, click Review + Create:

You should be directed to the resource’s deployment screen. Once completed, click Go to resource:

This will direct you to your Automation account’s home page.

For future use in our PowerShell script and since we do not want our Server’s credentials to appear in clear text, our next step will the creation of a credential within the Automation account. This will hold the credentials (username and password) that we’d use for connecting to our SQL Server target.

From the leftmost pane, select Credentials, then Add a credential:

Give your credential a meaningful name and enter the credentials. Then click Create:

Next, you must add the SqlServer module. From the leftmost pane, select Modules and click Add a module:

Select the following options:

From the gallery, choose the SqlServer module:

Click Select, then add the Runtime version, and click Import:

The import process may take a few minutes.

You now need to create a new Runbook. From the leftmost pane, select Runbooks, then Create a new Runbook:

At the Runbook creation page, fill the fields as suggested below and click Create. The description field is optional.

You will be directed to the Runbook edit page.

Copy and paste the PowerShell code into the code canvas, from the latest version in our Madeira Toolbox:

https://github.com/MadeiraData/MadeiraToolbox/blob/master/Maintenance%20of%20Azure%20SQL%20Databases/ScriptForAzureAutomation.ps1

You may notice that the Azure SQL Server’s credentials are called in the PowerShell script using the Get-AutomationPSCredential CMDLET.

You may also notice that the table creation command is called using a URI from GitHub which contains the T-SQL command. You may replace the second $Query variable with a T-SQL command.

Click Save and Publish.

You will be directed to the Runbook’s main page where you should check if it works as expected by running it manually. Click the Start button:

In this use-case, your test table should be created in all Azure SQL Databases.

All that’s left to do is creating a schedule for your Runbook. From the Runbook’s main page, click on Link to schedule:

Then, choose the Schedule option and add a schedule:

Simply fill the fields and create the schedule.

That’s it! You’re done!