Automating Azure SQL Database maintenance tasks (1° part)
Possible solutions will be explained and more convenient services will be described!
Introduction
Azure SQL Database has not got a native scheduling service comparable to SQL Agent that is present in the on-premise instances. When database solutions are implemented in Azure SQL, after the design phase of DB schema, possible solutions to perform database maintenance activities like Integrity Check, Index Rebuild etc…have to be studied. On the contrary, Backup activity already has an excellent default configuration given by Microsoft Azure platform.
In this article we will list the necessary activities to undertake in order to perform the maintenance of databases in Azure SQL; moreover, possible solutions will be explained and more convenient services will be described.
Maintenance activities
The necessary maintenance activities to undertake, when database solutions are implemented in Azure SQL, are:
- Integrity check
- Index rebuild (or Index reorganize)
- Statistics update
The typical methods to do these activities are:
- Maintenance Plans
- T-SQL script of suite SQL Server Maintenance Solution
- DBATools with PowerShell
Normally, maintenance activities are scheduled with SQL Agent, Windows Scheduled Task, Cron in Linux environment or with third parties Tools. In Azure SQL Database, we have several methods to schedule maintenance operations; this is the complete list:
- Linked Server
- Database Maintenance Plan
- PowerShell
- Azure Automation Services
- Azure Elastic Job Agents
Except for the method specified at point 2 (use of a maintenance native plan of SQL Server), to implement the maintenance activities, it is advised to use the stored procedures of suite SQL Server Maintenance Solution by Ola Hallengren. They can also be installed in a database hosted in Azure SQL. Hereafter, each method previously listed will be deepened: the execution of Integrity Check activity will be taken as an example and it will be explained which actions are dialed and which not.
Maintenance of Azure SQL Database by Linked Server
The creation of a Linked Server through which an Azure SQL database can be linked is considered the simplest method to reach a remote database. But it is necessary that a SQL Server instance is available on-premise or in an Azure Virtual Machine.
To create a Linked Server that connects a database in Azure SQL is sufficient to specify:
- The Linked Server’s name
- The provider we want to use choosing between: SQL Server Native Client or Microsoft OLE DB Provider for ODBC Driver
- The name of the Azure SQL in the blank Data Source
- The name of the database we want to connect in the blank Catalog
- The access credentials in the tab Security
- The activation of the option “RPC Out” in the tab Server Options
The use of a Linked Server to connect an Azure SQL database follows the same modalities as a classic Linked Server “on-premise to on-premise”. After creating the Linked Server, for the execution of the stored procedure dbo.DatabaseIntegrityCheck (of suite SQL Server Maintenance Solution) it will be sufficient to specify its “fully qualified name”. Supposing we assign the name “LSAzureDB” to the Linked Server that connects the Azure SQL in which the sample database WideWorldImporters is available, the execution of the stored procedure dbo.DatabaseIntegrityCheck will be similar to this:
EXEC LSAzureDB.WideWorldImporters.dbo.DatabaseIntegrityCheck
@Databases = ‘WideWorldImporters’
,@LogToTable=‘Y’
Doing the operation from SQL Server Management Studio, we will obtain an output similar to this:
Date and time: 2020–01–01 22:33:45
Server: <server_name>
Version: 12.0.2000.8
Edition: SQL Azure
Platform: Windows
Procedure: [WideWorldImporters].[dbo].[DatabaseIntegrityCheck]
Parameters: @Databases = ‘WideWorldImporters’, @CheckCommands = ‘CHECKDB’, @PhysicalOnly = ’N’, @NoIndex = ’N’, @ExtendedLogicalChecks = ’N’, @TabLock = ’N’, @FileGroups = NULL, @Objects = NULL, @MaxDOP = NULL, @AvailabilityGroups = NULL, @AvailabilityGroupReplicas = ‘ALL’, @Updateability = ‘ALL’, @TimeLimit = NULL, @LockTimeout = NULL, @LockMessageSeverity = 16, @DatabaseOrder = NULL, @DatabasesInParallel = ’N’, @LogToTable = ’N’, @Execute = ‘Y’
Version: 2019–06–14 00:05:34
Source: https://ola.hallengren.com
Date and time: 2020–01–01 22:33:45
Database: [WideWorldImporters]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Date and time: 2020–01–01 22:33:45
Command: DBCC CHECKDB ([WideWorldImporters]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY
Outcome: Succeeded
Duration: 00:05:03
Date and time: 2020–01–01 22:38:48
Maintenance of Azure SQL Database by Maintenance Plan
The use of Database Maintenance Plan follows the rules that are applicable to on-premise databases, the only exception is represented by the connection, in which the parameters to reach the remote database have to be specified. After creating the connection to Azure SQL, it will be possible to select the database which is the subject of the maintenance activities.
Maintenance of Azure SQL Database by Azure Automation Services
The use of Azure Automation Services involves the creation of an Azure Automation Account that provides the opportunity to create Runbooks that are adequate to the activities we want to perform. The creation and handling of Runbooks in Microsoft Azure is documented here. In order to perform the check of the database integrity using Azure Automation Services, we have created an Automation Account named DBMaintenance with a Runbook in, that is a PowerShell type named DBCheckIntegrity, as illustrated in the picture below.
In the picture below you can see the PowerShell Runbook; it can execute the stored procedure dbo.DatabaseIntegrityCheck available in the database WideWorldImporters.
The button “Test pane” will allow to access the Runbook test area. The Runbook will be published afterwards pressing the button “Publish”. The command “Invoke-Sqlcmd”, specified in the last line of the PowerShell script, is included in the package “SqlServer” available in the PowerShell gallery. It can be installed following the instructions available in this documentation. The published Runbooks can be scheduled and thanks to the scheduling Runbooks will reach the aim we wanted to at the beginning of this article.
In my opinion, the most important limit the Runbook has is the impossibility to be used for an execution of three consecutive hours at the most. As a consequence, after three hours, the Runbook will be “suspended” to allow other Runbook to operate. The mechanism is controlled by checkpoint as the Azure documentation of this article describes.
Maintenance of Azure SQL Database by Elastic Job Agents
The use of Elastic Job Agents (available in Preview while this article is being written) represents the most complete solution for scheduled activities automation to execute in Azure SQL. It’s worth writing: “The last but not the least”.
In order to create an Elastic Job Agent is sufficient to specify:
- The Agent’s name
- The subscription Microsoft Azure of reference
- The acceptance of the terms of the Preview mode use
- The Azure SQL and the name of the database in which the Job will be saved, the database has to be of standard range, at least a “S0” (10 DTU)
The following picture describes the page of the creation of an Elastic Job Agent (Preview mode).
An Agent of Elastic Job is “similar” to the service SQL Agent of the on-premise instances. It allows you to execute more than one job whose definition is memorized inside an Azure SQL database (the equivalent of the MSDB system database for the on-premise instances). The job is represented by a T-SQL script that will be scheduled and executed for a group of database in Azure SQL. At the moment, the job’s creation and their handling can only be performed with T-SQL or PowerShell, following the instructions written in this documentation.
Elastic Job Agents allows to:
- Performing job on several database
- Performing job among several server
- Extending the execution of the job to different database pool if there are any
- Extending the execution of the job also among different Azure Subscriptions
The following picture, included in the Azure SQL Database documentation describes how an Agent of Elastic Job can link and execute processes among different target groups.
Conclusions
The implementation of a database solution, whether it is small or big, always needs a maintenance archives strategy, in Azure SQL as well. The Azure platform natively provides only the execution and the storage of database backups according to the policy and the configurations done. The other maintenance activities (Integrity check, Index rebuild, etc..) have to be configured manually. Azure SQL Database has not got a native service similar to SQL Agent. The best solution to automate the database maintenance activities is to use the Elastic Job Agents. An Elastic Job Agent allows to schedule the execution of T-SQL or PowerShell jobs containing the definition of the activities.
