Azure SQL Database is a PaaS solution for migrating your on-premises databases to the Azure cloud infrastructure. It is a managed service and Azure manages the infrastructure, database availability, backup restore, and compute resources. 
Regularly maintaining your database is a crucial responsibility for database administrators to ensure optimal performance for your application queries. In the case of an on-premises SQL Server, it is common practice to set up SQL Server Agent jobs with customized T-SQL scripts or utilize database maintenance plans. These plans are designed to regularly perform index maintenance, adjusting as needed based on predefined thresholds. 

• Do we need to do index maintenance on Azure SQL Databases as well? 
• How can we do index maintenance on Azure databases? 

There is a misconception that database maintenance is unnecessary for Azure databases. Users are accountable for managing index and statistics maintenance on these databases; otherwise, performance may decline over time. However, default configurations related to statistics are in place. 

• Auto Create Statistics: True 
• Auto Create Incremental Statistics: False 
• Auto Update Statistics: True 
• Auto Update Incremental Statistics: True

Azure Database does not have any SQL Server agent to run the scripts. Therefore, we do have a question here: 

How can we automate the Azure SQL Database index and statistics maintenance? 

In this article, we use azure automation accounts and runbooks for scheduling maintenance scripts.  

Implement database maintenance for Azure SQL Database 

Index and statistics maintenance for the Azure database requires the following steps. 
AzureSQLMaintenance stored procedure 

We would recommend using AzureSQLMaintenance stored procedure. It is a custom stored procedure developed by Microsoft’s Yochanan Rachamim. It is suitable for Azure SQL and compatible with its supported features. 
To use this stored procedure, download it from GitHub, execute it on your SQL database.

You can run Execute AzureSQLMaintenance to get a brief overview of its parameters, their supported values. 

• @Operation: Its supported values are index, statistics or all. 
• @Mode: Smart(default) or dummy 
  • smart: It checks only modified statistics and choose index maintenance by % of fragmentation 
  • dummy: It checks all statistics or indexes 

• @logtotable: 
  • 1 to log output in a [AzureSQLMaintenanceLog] table (auto-created). 
  • 0: disable logging 

Considering a career in Azure?

Start with a free course in Azure Fundamentals AZ-900

Import sqlserver module for Azure automation on automation account  

In this article, we use the cmdlet from the sqlserver PowerShell module. By default, this module is not installed for Azure runbooks. In the azure automation account, navigate to Modules -> Browse gallery. 

Search for sqlserver modules and import the following modules created by matteott_msft. 

It shows import succeeded in the notification however if you check in the modules, it still showing as Importing. 

It takes a few minutes to import the module completely. 

Provide contributor access on Azure role assignment in Azure automation accounts

Automation account – Search Identity (It’s for System defined identity)-save 

Create Azure SQL Database and automation credential. 

To establish credentials in Azure SQL DB and associate them with automation accounts, proceed to the Azure automation dashboard’s credentials section. Click on “Add a credential” to initiate the process. 

In the new credential window, enter the following details: 

• Azure automation credential name 
• Description 
• Username and password: Enter the username and password of your choice. We need to create the same login and user in the Azure SQL database. 

It generates credentials within your automation account, as depicted below. 

Now, connect to Azure SQL DB using SQL Server Management Studio and create the login that we specified in the automation credential using the below script.

CREATE LOGIN [az-auto-cred]  WITH PASSWORD = 'XYZ'  
GO
Also add [az-auto-cred]  to dbmanager and login manager in master db
CREATE USER [az-auto-cred]  FROM LOGIN [az-auto-cred] 
ALTER ROLE dbmanager ADD MEMBER [az-auto-cred]  ;
GO 
ALTER ROLE loginmanager ADD MEMBER [az-auto-cred]  ; 
GO 
Now, switch database context to your Azure SQL database and create a user and assign db_owner permissions. 
CREATE USER [az-auto-cred]  FROM LOGIN [az-auto-cred] 
 ALTER ROLE db_owner ADD MEMBER [az-auto-cred]   ; 
GO 

Add Azure SQL SERVER network to public access 

Level up your data expertise with Azure Data Fundamentals DP-900 training

Create the variables to use in the runbooks. 

Within the Azure runbook, variables can be defined either within the PowerShell script itself or in the Azure Automation account. If variables are created in the automation account, they can be referenced across various scripts. 
For example, in the below screenshot, we define two variables. 

• IndexServer: It stores the FQDN of my azure SQL Server, i.e. abcdef.database.windows.net 
• IndexDB: Azure SQL DB in which we want to do index and statistics maintenance. 
  
  To establish these variables, access the Azure Automation account and select Variables, then choose New Variable. 

Create runbook for index maintenance in Azure SQL database 

In this segment, we generate a runbook through Azure Automation to carry out index and statistics maintenance. This runbook executes the AzureSQLMaintenance stored procedure, as detailed earlier in this article.
In the below, the first part of the script, we do the following actions.  

• Get the azure credentials details using the cmdlet Get-AutomationPSCredential and stores them into the variable $azureSQLCred variable 
• It fetches the information from variable IndexServer and stores it into another variable $SQLServerName 
• Similarly, it fetches the azure SQL database name from the database variable and stores into $database. It uses the cmdlet Get-AutomationVariable for fetching the information 

Before advancing, let’s execute this notebook to verify the accuracy of the variable information. Click on the Test Pane, and then initiate the test by selecting Start. 
As shown below, it writes the Azure SQL server, database names and credentials information. 
Now, we connect to the Azure SQL database using the Invoke-Sqlcmd cmdlet. We pass the following arguments in this cmdlet. 

• Credential: We have credentials stored in the $azureSQLCred variable; therefore, specify the variable name here 
• Database: Enter the variable name in which we stored the azure SQL DB name 
• Query: In this parameter, we specify the index and statistics maintenance stored procedure executes the query 
• We also specify the connection and query timeouts in the PowerShell script 

Initiate a test run of the Azure runbook. The resulting output is illustrated below: 

• Displays the total number of indexes, their average fragmentation and number of fragmented indexes 
• It prints statistics information such as total modification and modified statistics 
• It prints the alter index statements that stored procedures execute for index reorg or rebuild 

The stored procedure was configured with the parameter @LogToTable set to 1. Consequently, it records queries, status, start time, and end time in the [AzureSQLMaintenanceLog] table. You can retrieve and examine this information by querying the table, as demonstrated below. 

Publish the azure automation runbook. 

Once we have tested and published the runbook, we can link to an existing schedule or create a new schedule. Click on the Link to schedule. 

Author: Mayank Minal is a Principal Consultant at Fusion Practices