How to use Azure Automation to maintain SQL indexes and statistics

Posted On July 28, 2020

When you migrate to Azure SQL, you might think that Azure does all SQL maintenance, including the maintenance of your database… But the truth is, you will need to setup some maintenance yourself for your databases. Microsoft doesn’t know what is best for your application or database. With this manual you should be able to setup basic database maintenance on Azure SQL.

Prerequisites

• SQL Management Studio
• Maintenance script for Azure SQL
• Azure Admin account

Manual

STEP 1: Login to your Azure SQL Database using SQL Management studio.

STEP2: Open a new Query window, and run the Maintenance script for Azure SQL from Yachanan: https://raw.githubusercontent.com/yochananrachamim/AzureSQL/master/AzureSQLMaintenance.txt

STEP 3: Next step is to create a SQL user that will have DB Owner permissions to perform the maintenance. For security purposes we just create a database user. Run the following script, adjust where needed (username/password)

CREATE USER ServiceAccount
WITH PASSWORD = 'SecureStrongPassword'
GO

EXEC sp_addrolemember 'db_owner', 'ServiceAccount';

STEP 4: Now go to the Azure Portal (https://portal.azure.com) and create a new resource, and select Automation

Create a name for your automation account, select a subscription and resource group, and click create.

Once the creation task is completed, go to your resource

STEP 5: Now we will need to import the SQL module for Azure automation. Go to Modules and Browse the gallery

Now search for the SqlServer module from matteot_msft.

Now import the module

STEP 6: Now we will need to add the credentials from step 3. Go to Credentials, and click on Add a credential.

Fill in the credentials from step 3, make sure to create the credential with the name: SqlCredential

STEP 7: Time to implement the script it self. Now go to Runbooks, and Create a runbook

Give your runbook a name, and select PowerShell as runbook type. Click create when done.

A new window will open, copy and paste the script below

$AzureSQLServerName = "<Servername>"
$AzureSQLDatabaseName = "<Databasename>"

$AzureSQLServerName = $AzureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name "SqlCredential"
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1
Write-Output $SQLOutput​

Make sure to fill in the server name without the .database.windows.net name.

When done click on save, and the click on Test pane.

STEP 8: Time to test the runbook. When ready click on start to run the maintenance script.

Once the test is completed, close the test pane, and in the Edit mode from the runbook click on Publish.

STEP 9: Last step is to implement a schedule to run on a weekly base. So in your runbook, go to Schedules, and click on Add a schedule.

Now link a schedule

Create a new schedule

Now create your required schedule on your needs. This is an example for a weekly schedule. Click create and OK when ready.

You are finished!

From your automation account you should be able to view completed and failed jobs:

Or from the Runbook itself you should be able to check the job status from the last days