Automating Azure SQL Database maintenance tasks (2° part)

We describe in depth the solution that allows to perform the database maintenance in Azure SQL by the use of Azure Elastic Job Agents

6 min readMar 2, 2020

Introduction

In the previous article Automating Azure SQL Database maintenance tasks (1° part) we described the activities and possible solutions to perform the database maintenance in Azure SQL; moreover we explained the more convenient services to use that the platform Microsoft Azure made available.

This article will study in depth the solution that allows to perform the database maintenance in Azure SQL by the use of Azure Elastic Job Agents. An Azure Elastic Job Agent performs processes whose definitions are filed in a database on Azure SQL. A process is a T-SQL script performed on a group of database in Azure SQL.

Script for the execution of integrity check and maintenance of indexes and statistics

For the execution of integrity check of the database and maintenance of indexes and statistics, the use of the free suite SQL Server Maintenance Solution developed by Ola Hallengren is recommended. The suite SQL Server Maintenance Solution includes the scripts to perform the maintenance operations on all the editions of SQL Server (from SQL Server 2005 to SQL Server 2017). This article will describe how to automate the indexes and statistics maintenance operations using the procedures given by the suite SQL Server Maintenance Solution.

The backup automation is not necessary because it is given by Azure SQL Database, the automation of the integrity check is similar to the automation of the maintenance of indexes and statistics that will be described hereafter. The entire maintenance solution is here available, but because it is not completely supported in Azure SQL Database, the cumulative script can’t be executed so that there will be the creation of single database objects instead. Executing the creation of these following objects on each Azure SQL database you want to maintain:

DatabaseBackup.sql: Stored procedure to backup databases
DatabaseIntegrityCheck.sql: Stored procedure to check the integrity of databases
IndexOptimize.sql: Stored procedure to rebuild and reorganize indexes and update statistics
CommandExecute.sql: Stored procedure to execute and log commands
CommandLog.sql: Table to log commands
Queue.sql: Table for processing databases in parallel
QueueDatabase.sql: Table for processing databases in parallel

Creation of an Azure Elastic Job Agent

The creation of an Azure Elastic Job Agent needs an empty database, with service level “SO” or higher hosted in Azure SQL Database. This database that represents the equivalent of the MSDB system database for the on-premise instance, will be used as “job database” during the creation of an Azure Elastic Job Agent.

In order to create an Elastic Job Agent, the Microsoft Azure portal can be used looking for “Elastic Job Agents” in the dedicated area of research resources and services (here the direct link) or the PowerShell command here described.

In order to create an Elastic Job Agent is sufficient to specify:

The Agent’s name
The Subscription’s name
The acceptance of the terms of use
The server SQL Azure and the name of the database in which Jobs will be saved, the database has to be of a standard range, with level service “S0” (10 DTU) or higher

The following picture describes the window of the creation of an Elastic Job Agent (Preview mode).

Processes creation and management

While this article is being written, the Elastic Job Agents service is in preview and a great deal of activities concerning processes creation and management has to be performed using T-SQL or PowerShell. Hereafter it will be described how to create, perform and manage the processes linked to an Elastic Job Agent. First of all, it is necessary to create credentials for the execution of the process in the job database previously created and linked to Elastic Job Agent. Credentials can be created using T-SQL or using PowerShell. The following T-SQL code performs the creation of the credentials in the job database called “DBJob”.

USE [DBJob];
GO-- Creation of a master key (If it doesn't already exist) in the
-- database, using the own password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<EnterYourStrongPasswordHere>';-- Creation of the "jobcredential" credential within DBJob database
CREATE DATABASE SCOPED CREDENTIAL jobcredential WITH IDENTITY = 'sql-job-user', SECRET = '<EnterYourStrongPasswordHere>';
GO-- Creation of the "masterjobcredential" credential within DBJob
-- database for the login sql-job-master that will be created
-- afterwards in the master database of the application
CREATE DATABASE SCOPED CREDENTIAL masterjobcredential WITH IDENTITY = 'sql-job-master', SECRET = '<EnterYourStrongPasswordHere>';
GO

After creating the credentials, it proceeds with the creation of the logins and the creation of the database users in the target instance. When the job is being executed, the job uses the credentials created within the job database (DBJob in this example) to connect to a destination database, belonging to the destination group. These credentials are also used to connect to the master database of a Server or of an Elastic Pool used as a target. The following T-SQL code performs the logins creation and the creation of the users in the master database of the target instance: it hosts the database on which the scheduled processes will work.

USE [master];
GOCREATE LOGIN [sql-job-master] WITH PASSWORD = '<EnterYourStrongPasswordHere>';CREATE USER [sql-job-master] FOR LOGIN [sql-job-master];
GOCREATE LOGIN [sql-job-user] WITH PASSWORD = '<EnterYourStrongPasswordHere>';
GO

After creating logins and users on the master database, it proceeds with the user creation in the database on which the scheduled processes will work. In this article, the scheduled processes represent the database maintenance activities; the user created in the database will have to be able to perform the stored procedure dbo.IndexOptimize will have to be able to write in the log table and will have to be able to perform the stored procedure dbo.CommandExecute.

USE [<EnterTargetDBHere>];
GOCREATE USER [sql-job-user] FOR LOGIN [sql-job-user];
GOGRANT EXECUTE ON OBJECT::[dbo].[IndexOptimize] TO [sql-job-user];
GRANT EXECUTE ON OBJECT::[dbo].[CommandExecute] TO [sql-job-user];
GO

After creating the credentials, the logins and the users, it proceeds with the (target) group definition that contains the database in which the process is executed. The following T-SQL code performs the ServerGroupMaintenance creation.

USE [DBJob];
GO-- Add group ServerGroupMaintenance containing server
-- (one or more destination server)
EXEC jobs.sp_add_target_group 'ServerGroupMaintenance';-- Add server servermaintenance.database.windows.net
-- to the group named ServerGroupMaintenance
EXEC jobs.sp_add_target_group_member
  'ServerGroupMaintenance'
  ,@target_type = 'SqlServer'
  ,@refresh_credential_name = 'masterjobcredential'
  ,@server_name = 'servermaintenance.database.windows.net';
GO

After creating and setting up the ServerGroupMaintenance group, it proceeds with the job creation for the index Rebuild/Reorganize in the ServerGroupMaintenance group database. The following T-SQL code performs the job creation IndexOptimizeDB to which the step containg the script T-SQL to execute is linked.

USE [DBJob];
GO-- Add of the job IndexOptimizeDB
EXEC jobs.sp_add_job
  @job_name = 'IndexOptimizeDB'
  ,@description = 'Rebuild or reorganize all indexes with fragmentation’;-- Add the step containing the command to execute for the
-- indexes maintenance
EXEC jobs.sp_add_jobstep@job_name = 'IndexOptimizeDB',@command = N'EXECUTE dbo.IndexOptimize@Databases = ''<DBName>'',@FragmentationLow = NULL,@FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',@FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'',@FragmentationLevel1 = 5,@FragmentationLevel2 = 30',@credential_name = 'jobcredential',@target_group_name = 'ServerGroupMaintenance';
GO

The IndexOptimizeDB job manual execution can be initiated by means of the system stored procedure jobs.sp_start_job, specifying the job name to execute, as illustrated in the following example.

-- Manual execution of the job IndexOptimizeDB
EXEC jobs.sp_start_job 'IndexOptimizeDB';
GO

The aim is reached with the job schedule; in this article it is represented by the index maintenance job. The system stored procedure jobs.sp_start_job allows to plan the Azure Elastic Job Agents jobs. The following code T-SQL performs the IndexOptimizeDB job planning setting start at 2 AM , every day, from this day: Sunday the 1 (st) of March.

USE [DBJob];
GOEXEC jobs.sp_update_job
  @job_name = 'IndexOptimizeDB'
  ,@enabled = 1
  ,@schedule_interval_type = 'Days'
  ,@schedule_start_time = '20200301 02:00:00'
  ,@schedule_end_time = '9999–12–31 11:59:59.0000000';
GO

Conclusions

Azure Elastic Job Agents allows to:

Performing job on several database
Performing job among different server
Extending the job execution to several database pool in case there are any
Extending the job execution to different subscriptions

The following picture, present in the Azure SQL documentation, describes how an Elastic Job Agent is able to connect and perform processes among several kinds of destination groups.