Home > News > Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Automazione delle attività di manutenzione in Azure SQL Database (2 Parte)

Introduzione

Nell’articolo Automazione delle attività di manutenzione in Azure SQL Database abbiamo descritto le attività e le possibili soluzioni per effettuare la manutenzione dei database in Azure SQL; abbiamo inoltre descritto i servizi più opportuni da utilizzare, offerti dalla piattaforma Microsoft Azure.

In questo articolo verrà approfondita la soluzione che permette di effettuare le attività di manutenzione database in Azure SQL attraverso l’utilizzo di Azure Elastic Job Agents che esegue processi le cui definizioni sono archiviate all’interno di un database Azure SQL. Un processo è uno script T-SQL pianificato o eseguito ad-hoc su un gruppo di database in Azure SQL.

Script per l’esecuzione dei controlli di integrità e manutenzione indici e statistiche

Per l’esecuzione dei controlli di integrità del database e la manutenzione di indici e statistiche si consiglia l’utilizzo della suite gratuita SQL Server Maintenance Solution realizzata da Ola Hallengren. La suite SQL Server Maintenance Solution comprende gli script per effettuare le operazioni di manutenzione su tutte le edizioni di SQL Server (da SQL Server 2005 a SQL Server 2017). In questo articolo verrà descritto come automatizzare le operazioni di manutenzione Indici e Statistiche utilizzando Azure Elastic Job Agents per la schedulazione degli script della suite SQL Server Maintenance Solution. L’automazione del backup non è necessaria perchè fornita da Azure SQL Database, l’automazione dei controlli di Integrità è simile all’automazione della Manutenzione di Indici e Statistiche descritta di seguito.

L’intera soluzione di manutenzione è disponibile qui ma non essendo completamente supportata in Azure SQL Database, non si potrà eseguire lo script cumulativo ma dovrà essere eseguita la creazione dei singoli oggetti database.

Eseguire la creazione dei seguenti oggetti, attraverso gli omonimi script, su ogni database che si desidera manutenzionare:

  • DatabaseIntegrityCheck.sql: Contiene il codice T-SQL per creare la Stored Procedure che effettua i controlli di integrità del DB
  • IndexOptimize.sql: Contiene il codice T-SQL per creare la Stored Procedure che effettua la manutenzione degli Indici (Rebuild oppure Reorganize) e l’aggiornamento delle Statistiche
  • CommandExecute.sql: Contiene il codice T-SQL per creare la Stored Procedure che esegue e traccia i comandi nel log
  • CommandLog.sql: Contiene il codice T-SQL per creare la tabella che conterrà il log dei comandi eseguiti
  • Queue.sql: Contiene il codice T-SQL per creare la tabella che permette di eseguire le attività di manutenzione in parallelo
  • QueueDatabase.sql: Contiene il codice T-SQL per creare la tabella che permette di eseguire le attività di manutenzione in parallelo

Creazione di un Azure Elastic Job Agents

La creazione di un Agente di Azure Elastic Job necessita di un database vuoto, con livello di servizio “S0” o superiore, ospitato all’interno di Azure SQL Database. Questo database, che rappresenta l’equivalente del database di sistema MSDB per le istanze on-premise, verrà utilizzato come “database job” durante la creazione di un Azure Elastic Job Agent.

Per creare un Elastic Job Agent si può utilizzare il portale Microsoft Azure ricercando “Elastic Job Agents” nell’apposita zona di ricerca risorse e servizi, il link alla pagina di creazione, oppure il comando PowerShell descritto qui. Per la creazione di un Elastic Job Agent è sufficiente specificare:

  • Il nome dell’Agent
  • La subscription di riferimento
  • L’accettazione dei termini di utilizzo
  • Il server Azure SQL ed il nome del database in cui verranno salvati i Job, il database dovrà essere di fascia standard, con livello di servizio “S0” (10 DTU) o superiore

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

Figura 1 – Azure Elastic Job Agent

Creazione e gestione dei processi

Al momento della stesura di questo articolo, il servizio Elastic Job Agents si trova in “Preview” e gran parte delle attività di creazione e gestione dei processi deve essere eseguita usando T-SQL o PowerShell. Di seguito verrà descritto come creare, eseguire e gestire i processi collegati ad un Agente di Elastic Job.

Per prima cosa, è necessario creare una credenziale per l’esecuzione del processo nel database job creato (vuoto) in precedenza e collegato ad Elastic Job Agent. La credenziale può essere creata utilizzando T-SQL oppure PowerShell. L’esempio di codice T-SQL riportato di seguito effettua la creazione delle credenziali nel “database job” di esempio chiamato DBJob.

USE [DBJob];
GO
-- Creazione di una master key (se non esiste già) nel database,
-- usando la propria password
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Your strong password';
-- Creazione della credenziale jobcredential nell'ambito del database DBJob
CREATE DATABASE SCOPED CREDENTIAL jobcredential WITH IDENTITY = 'sql-job-user', SECRET = 'Your strong password';
GO
-- Creazione della credenziale masterjobcredential nell'ambito del database 
-- DBJob, per la login sql-job-master che verrà successivamente creata nel 
-- database master di Azure SQL database target (quella che ospita i 
-- database su cui dovranno lavorare i processi schedulati)
CREATE DATABASE SCOPED CREDENTIAL masterjobcredential WITH IDENTITY = 'sql-job-master', SECRET = 'Your strong password';
GO

Dopo aver creato le credenziali, si procede con la creazione delle login e degli utenti di database in Azure SQL (target). I job utilizzano le credenziali create nell’ambito del database job (DBJob in questo esempio) per connettersi ai database di destinazione, appartenenti al gruppo di destinazione, al momento dell’esecuzione del job. Queste credenziali vengono anche utilizzate per connettersi al database master di un Server o di un Elastic Pool utilizzato come target. L’esempio di codice T-SQL riportato di seguito effettua la creazione delle login e degli utenti nel database master di Azure SQL (target) che ospita i database su cui dovranno lavorare i processi schedulati.

USE [master];
GO
CREATE LOGIN [sql-job-master] WITH PASSWORD = 'Your strong password';
CREATE USER [sql-job-master] FOR LOGIN [sql-job-master];
GO
CREATE LOGIN [sql-job-user] WITH PASSWORD = 'Your strong password';
GO

Dopo aver creato login e utenti nel database master, si procede con la creazione dell’utente nel database su cui dovranno lavorare i processi schedulati. In questo articolo, i processi schedulati rappresentano le attività di manutenzione database effettuate attraverso la suite SQL Server Maintenance Solution, l’utente creato nel database oggetto della manutenzioni dovrà poter eseguire la stored procedure [dbo].[IndexOptimize], dovrà poter scrivere all’interno della tabella di log e dovrà poter eseguire la stored procedure [dbo].[CommandExecute].

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

Dopo aver creato credenziali, login e utenti, si procede con la definizione del gruppo (target) che contiene i database nei quali eseguire il processo. L’esempio di codice T-SQL riportato di seguito effettua la creazione del gruppo ServerGroupMaintenance.

USE [DBJob];
GO
-- Definizione del gruppo ServerGroupMaintenance contenente server 
-- (uno o più server di destinazione)
EXEC jobs.sp_add_target_group 'ServerGroupMaintenance';
-- Aggiunta del server servermaintenance.database.windows.net al gruppo 
-- ServerGroupMaintenance
EXEC jobs.sp_add_target_group_member
'ServerGroupMaintenance',
@target_type = 'SqlServer',
@refresh_credential_name='masterjobcredential',
@server_name='servermaintenance.database.windows.net';

Dopo aver creato e configurato il gruppo (di esempio) ServerGroupMaintenance, si procede con la creazione del job per la Rebuild/Reorganize degli indici all’interno dei database del gruppo ServerGroupMaintenance. L’esempio di codice T-SQL riportato di seguito effettua la creazione del job (di esempio) IndexOptimizeDB a cui viene collegato lo step che contiene lo script T-SQL da eseguire.

USE [DBJob];
GO
-- Definizione del job IndexOptimizeDB
EXEC jobs.sp_add_job @job_name='IndexOptimizeDB', @description='Rebuild or reorganize all indexes with fragmentation';
-- Definizione dello step che contiene il comando da eseguire per 
-- la manutenzione degli indici

EXEC jobs.sp_add_jobstep 
  @job_name='IndexOptimizeDB',
  @command=N'EXECUTE dbo.IndexOptimize
  @Databases = '''',
  @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';

L’esecuzione manuale del job IndexOptimizeDB può essere avviata attraverso la stored procedure di sistema jobs.sp_start_job specificando semplicemente il nome del job da eseguire, come illustrato nell’esempio seguente.

-- Esecuzione manuale (dell’ultima versione) del job IndexOptimizeDB
EXEC jobs.sp_start_job 'IndexOptimizeDB';

L’obiettivo si raggiunge con la schedulazione del job che in questo articolo è rappresentato dal job di manutenzione indici. La stored procedure di sistema jobs.sp_update_job permette di pianificare i job Azure Elastic Job Agents. L’esempio di codice T-SQL riportato di seguito effettua la pianificazione del job IndexOptimizeDB impostando l’avviamento alle 2 am, ogni giorno, a partire da sabato 12 ottobre 2019.

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

Grazie ad Azure Elastic Job Agents, la manutenzione di indici e statistiche verrà eseguita ogni notte alle 2 am.

Il tema della manutenzione database in Azure SQL è stato trattato nella sessione Automating Azure SQL Database Maintenance Tasks al Global Azure Virtual 2020, la registrazione della sessione è disponibile sul canale UGISS di Vimeo.. la trovate qui:

Conclusioni

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 Agent di Elastic Job possa collegare ed eseguire processi tra diversi tipi di gruppi di destinazione.

Figura 2 – Gruppi di destinazione collegati ad un Agent di Elastic Job

Chi è Sergio Govoni

Sergio Govoni è laureato in Scienze e Tecnologie Informatiche. Da oltre 16 anni lavora presso una software house che produce un noto sistema ERP, distribuito a livello nazionale ed internazionale, multi azienda client/server su piattaforma Win32. Attualmente si occupa di progettazione e analisi funzionale, coordina un team di sviluppo ed è responsabile tecnico di prodotto. Lavora con SQL Server dalla versione 7.0 e si è occupato d'implementazione e manutenzione di database relazionali in ambito gestionale, ottimizzazione delle prestazioni e problem solving. Nello staff di UGISS si dedica alla formazione e alla divulgazione in ambito SQL Server e tecnologie a esso collegate, scrivendo articoli e partecipando come speaker ai workshop e alle iniziative del primo e più importante User Group Italiano sulla tecnologia SQL Server. Ha conseguito la certificazione MCP, MCTS SQL Server. Per il suo contributo nelle comunità tecniche e per la condivisione della propria esperienza con altri, dal 2010 riceve il riconoscimento SQL Server MVP (Microsoft Most Valuable Professional). Nel corso dell'anno 2011 ha contribuito alla scrittura del libro SQL Server MVP Deep Dives Volume 2 (http://www.manning.com/delaney/).

Leggi Anche

PASS Marathon BI (Dicembre 2019): Sessioni registrate!

Le sessioni presentate durante l’evento PASS Marathon “Edizione BI”, tenuto lo scorso 11 Dicembre 2019, …