Automazione delle attività di manutenzione in Azure SQL Database

Introduzione

Azure SQL Database non possiede un servizio di schedulazione nativo paragonabile a SQL Agent presente nelle istanze on-premise. Quando si implementano soluzioni database in Azure SQL, dopo la fase di progettazione dello schema del DB, devono essere studiate le possibili soluzioni per effettuare le attività di manutenzione database come Integrity Check, Index Rebuild ecc.. ad esclusione dell’attività di Backup che ha già un’ottima configurazione di default fornita dalla piattaforma.

In questo articolo vengono elencate le attività di cui è necessario farsi carico per effettuare la manutenzione dei database in Azure SQL, vengono inoltre descritte le possibili soluzioni e spiegati i servizi più opportuni da utilizzare.

Attività di manutenzione

Le attività di manutenzione di cui è necessario farsi carico quando si implementano soluzioni in Azure SQL Database sono:

• Integrity check
• Index rebuild (oppure Index Defrag)
• Statistics update

I metodi tipici per effettuare queste attività sono:

• Piani di Manutenzione
• Script T-SQL della suite SQL Server Maintenance Solution
• Utilizzo dei DBATools con PowerShell

Tipicamente, le attività di manutenzione vengono schedulate con SQL Agent, Windows Scheduled Task, Cron in ambiente Linux oppure con Tools di terze parti.

In Azure SQL Database, esistono diversi metodi per effettuare le operazioni di manutenazione, questo è l’elenco completo:

1. Linked Server
2. Database Maintenance Plan
3. PowerShell
4. Azure Automation Services
5. Azure Elastic Job Agents

Ad eccezione del metodo indicato al punto 2 (utilizzo di un Piano di Manutenzione nativo di SQL Server), per l’implementazione delle attività di manutenzione, si consiglia di utilizzare le stored procedure della suite SQL Server Maintenance Solution realizzate da Ola Hallengren che possono essere installate anche in un database ospitato in Azure SQL. Di seguito verrà approfondito ogni metodo elencato precedentemente, prendendo come esempio l’esecuzione dell’attività di Integity Check e spiegando quali azioni sono gestite e quali no.

Manutenzione di Azure SQL Database via Linked Server

La creazione di un Linked Server verso un database Azure SQL è considerato il metodo più semplice per raggiungere e manutenzionare un database remoto. E’ necessario, però, disporre di un’istanza SQL Server on-premise oppure in una Virtual Machine in Azure. Per la creazione di un Linked Server che collega un database in Azure SQL è sufficiente specificare:

• Il nome del Linked Server
• Il provider che si desidera utilizzare scegliendolo tra
  • SQL Server Native Client
  • Microsoft OLE DB Provider for ODBC Driver
• Il nome del server Azure SQL nel campo Data Source
• Il nome del database che si desidera collegare nel campo Catalog
• Le credenziali di accesso nella scheda Security
• L’attivazione (True) dell’opzione “RPC Out” nella scheda Server Options

L’utilizzo del Linked Server verso Azure segue le stesse modalità di un classico Linked Server “on-premise to on-premise”. Dopo aver creato il Linked Server, per eseguire la stored procedure dbo.DatabaseIntegrityCheck (realizzata da Ola Hallengren per il controllo dell’integrità del DB) sará sufficiente specificare il suo fully qualified name. Ipotizzando di aver assegnato il nome “LSAzureDB” al Linked Server che collega l’istanza Azure SQL nella quale è presente il database di esempio WideWorldImporters, il comando di esecuzione della stored procedure dbo.DatabaseIntegrityCheck (nel database WideWorldImporters) sarà simile a questo:

EXEC LSAzureDB.WideWorldImporters.dbo.DatabaseIntegrityCheck
   @Databases = 'WideWorldImporters'
   ,@LogToTable='Y'

Effettuando l’operazione da SQL Server Management Studio, otterremo un output simile a questo:

Date and time: 2019-08-13 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: 2019-08-13 22:33:45
Database: [WideWorldImporters]
State: ONLINE
Standby: No
Updateability: READ_WRITE
User access: MULTI_USER
Recovery model: FULL
Date and time: 2019-08-13 22:33:45

Command: DBCC CHECKDB ([WideWorldImporters]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY

Outcome: Succeeded
Duration: 00:05:03
Date and time: 2019-08-13 22:38:48

Manutenzione di Azure SQL Database via Maintenance Plan

L’utilizzo di Database Maintenance Plan segue le regole valide per i database on-premise, unica particolarità è rappresentata dalla connessione nella quale dovranno essere specificati i parametri per raggiungere il database remoto. Dopo aver creato la connessione all’istanza Azure SQL, sarà possibile selezionare il database oggetto delle attività di manutenzione.

Manutenzione di Azure SQL Database via Azure Automation Services

L’utilizzo di Azure Automation Services prevede la creazione di un Azure Automation Account che fornisce la possibilità di creare Runbook appropiati alle attività che si desidera eseguire. La creazione e gestione dei Runbook in Microsoft Azure è documentata qui.

Per eseguire il controllo di integrità del database utilizzando Azure Automation Services abbiamo creato un Automation Account chiamato “DBMaintenance” con all’interno un Runbook di tipo PowerShell chiamato “DBCheckIntegrity” come illustrato nella figura seguente.

Il comando PowerShell in grado di eseguire la stored procedure dbo.DatabaseIntegrityCheck (della suite SQL Server Maintenance Solution) presente nel database WideWorldImporters è illustrato nella figura seguente.

Il tasto “Test pane” permettà di accedere all’area di test del Runbook che potrà successivamente essere pubblicato agendo sul tasto “Publish”. Il comando “Invoke-Sqlcmd” specificato nell’ultima riga dello script PowerShell è incluso nel packages “SqlServer” presente nella PowerShell gallery, installabile seguendo le istruzioni riportate in questa documentazione. I Runbook pubblicati possono essere schedulati; con la schedulazione del Runbook otterremo l’obiettivo che ci siamo posti all’inizio di questo articolo.

La limitazione più forte che, a mio avviso, rende i Runbook inutilizzabili per questo scopo è data dal limite massimo di tre ore consecutive di esecuzione. Superate le tre ore, il Runbook verrà “sospeso” per dare spazio all’esecuzione di altri Runbook in un meccanismo governato da checkpoint come descrive la documentazione di Azure in questo articolo.

Manutenzione di Azure SQL Database via Elastic Job Agents

L’utilizzo di Elastic Job Agents (disponibile solo in modalità Preview durante la stesura di questo articolo) rappresenta la soluzione più completa per l’automazione di attività schedulate da eseguire su database Azure SQL.. è proprio il caso di scriverlo “ultimo, ma non meno importante”.

Per la creazione di un Elastic Job Agent è sufficiente specificare:

• Il nome dell’Agent
• La subscription Microsoft Azure di riferimento
• L’accettazione dei termini di utilizzo della modalità Preview
• Il server Azure ed il nome del database in cui verranno salvati i job, il database dovrà essere di fascia standard, minimo un S0 (10 DTU)

La figura seguente illustra la finestra di creazione di un Elastic Job Agent (Preview mode).

Un Agent di Elastic Job è “simile” al servizio SQL Agent delle istanze on-premise, permette di eseguire più job la cui definizione è memorizzata all’interno di un database Azure SQL (l’equivalente del database MSDB per le istanze on-premise). Il job è rappresentato da uno script T-SQL che verrà schedulato ed eseguito su un gruppo di database in Azure. Al momento, la creazione e la gestione dei job può essere effettuata solo via T-SQL o PowerShell, seguendo le istruzioni riportate in questa documentazione.

Elastic Job Agents permette di:

• Eseguire job su diversi database
• Eseguire job tra diversi server
• Estendere l’esecuzione dei job a differenti database pool nel caso ci fossero
• Estendere l’esecuzione dei job anche tra subscription differenti

La figura seguente, riportata dalla documentazione di Azure SQL Database, illustra come un Elastic Job Agent possa collegare ed eseguire processi tra diversi tipi di gruppi di destinazione.

Conclusioni

L’implementazione di una soluzione database, piccola o grande che sia, necessita sempre di una strategia di manutenzione archivi, anche in Azure SQL Database. La piattaforma Azure fornisce nativamente solo l’esecuzione e la conservazione dei backup dei DB secondo le policy e le configurazioni eseguite. Le altre attività di manutenzione (Integrity check, Index rebuild, ecc..) devono essere configurate manualmente. Azure SQL Database non prevede un servizio nativo simile a SQL Agent. La soluzione migliore per automatizzare le attività di manutenzione archivi è quella che vede l’utilizzo del servizio Elastic Job Agent attraverso il quale schedulare l’esecuzione di job T-SQL o PowerShell contenenti la defizione delle attività, per queste ultime abbiamo utilizzato le famose stored procedure della suite SQL Server Maintenance Solution, realizzata da Ola Hallengren.

La seconda parte di questo articolo tratta in modo dettagliato la soluzione che prevede l’utilizzo di Azure Elastic Job Agent, la trovate qui: Automazione delle attività di manutenzione in Azure SQL Database (2 Parte).