Database  »  Articoli  »  SQL Server 

In un precedente articolo abbiamo esaminato gli strumenti che SQL Server (all'interno del Management Studio) mette a disposizione di un database manager per gestire la manutenzione del proprio database.

SQL Server Maintenance Solution, che vedremo in questo secondo articolo, è la soluzione free basata sul linguaggio T-SQL realizzata nel 2008 da un DBA svedese chiamato Ola Hallengren, e oggi, dopo aver superato le 3000 linee di codice e aver ricevuto più volte il premio di Best Free Tool, Gold e Silver award della rivista SQL Server Magazine, è divenuto lo standard de-facto per la manutenzione dei database SQL Server, sia di piccole che di medie-grandi dimensioni.

Questa soluzione garantisce un'efficiente e flessibile manutenzione dei backup, degli indici e delle statistiche e il controllo dell’integrità dei database. L’ultima versione disponibile risale al Gennaio 2012 ed è supportata dalle versioni SQL Server 2005 / 2008 / 2008 R2 / 2012 RC0 (il rilascio della RTM di SQL Server 2012 è previsto per Maggio 2012) e da tutte le Edition fatta eccezione per la SQL Express in quanto non dispone del SQL Server Agent, il servizio di schedulazione dei Job.

Di seguito esamineremo gli aspetti fondamentali della SQL Server Maintenance Solution rimandando per i dettagli alla documentazione disponibile sul sito ufficiale dell’autore al seguente indirizzo http://ola.hallengren.com/Documentation.html.

SQL Maintenance Solution Setup

Il setup è davvero molto semplice. Basta infatti scaricare e lanciare un unico script (MaintenaceSolution.sql dal sito http://ola.hallengren.com/downloads.html) prestando attenzione però a modificarlo impostando correttamente:

• Il flag di creazione dei Job (linea 29)
• Il percorso in cui indirizzare i backup (linea 30)

USE [master] -- Specify the database in which the objects will be created.

SET NOCOUNT ON

DECLARE @CreateJobs nvarchar(max)
DECLARE @BackupDirectory nvarchar(max)
DECLARE @OutputFileDirectory nvarchar(max)
DECLARE @LogToTable nvarchar(max)
DECLARE @Version numeric(18,10)
DECLARE @Error int

SET @CreateJobs          = 'Y'          -- Specify whether jobs should be created.
SET @BackupDirectory     = N'C:\Backup' -- Specify the backup root directory.
SET @OutputFileDirectory = NULL         -- Specify the output file directory. If no directory is specified, then the SQL Server error log directory is used.
SET @LogToTable          = 'Y'          -- Log commands to a table.

Se impostata la variabile @CreateJobs a “Y”, saranno generati automaticamente tutti i job di cui abbiamo bisogno e che potremo poi schedulare e configurare nel modo più opportuno.

Figura 1: I job creati dallo script

Quindi dopo aver eseguito lo script di Setup passiamo ad analizzarne internamente gli aspetti più importanti. La soluzione è basata su un ristrettissimo numero di stored procedure, funzioni e tabelle create nel database di sistema master che automatizzano molto facilmente le attività di backup, controllo integrità e manutenzione indici e statistiche.

Figura 2:  stored procedure, funzione e tabelle

Di questi oggetti, tre soltanto sono le stored procedure che il DBA deve preoccuparsi di conoscere per poter configurare opportunamente le manutenzioni in quanto realizzano i task fondamentali di

• Backup (dbo.DatabaseBackup)
• Controllo dell’integrità (dbo.DatabaseIntegrityCheck)
• Re-build e reorganize degli indici e aggiornamento delle statistiche (dbo.IndexOptimize )

Tutte e tre tali stored procedure hanno in comune i seguenti parametri fondamentali:

@Databases nvarchar(max)

• l’elenco (separato da virgola) dei nomi dei database oggetto del task (es. ‘AdventureWorks, AdventureWorksLT, Pubs, Northwind, Master’)

oppure una delle seguenti keywords

• SYSTEM_DATABASES: tutti e solo i database di sistema ('master','model','msdb','tempdb')
• USER_DATABASES: tutti e solo i database utente
• ALL_DATABASES: tutti i database, sia essi utente che di sistema

In entrambi i casi è possibile escludere dall’insieme di database uno o più nomi di database anteponendo il segno – (meno) (es. USER_DATABASES, -dventure equivale a tutti i database utente eccetto quelli il cui nome contiene la parola dventure, quindi per esempio i database AdventureWorks e AdventureWorksLT)

@LogToTable nvarchar(max) = 'N'

• Se impostato a 'Y' attiva il log del comando nella tabella dbo.CommandLog.

@Execute nvarchar(max) = 'Y'

•  Se impostato a 'N' si limita a visualizzare i messaggi di output della stored procedure e eventualmente a loggare nella dbo.CommandLog in presenza di @LogToTale = 'Y' – molto utile per verificare l’attività che la procedura si appresta a svolgere prima di eseguirla realmente in produzione.

I restanti oggetti sono:

• la stored procedure denominata dbo.CommandExecute:viene richiamata dalle tre fondamentali di cui sopra per comandare al Database Engine l’esecuzione vera e propria del task;
• la table-valued function denomianata dbo.DatabaseSelect: viene richiamata anch'essa in tutte e tre le stored procedure fondamentali. Ha lo scopo di restituire in formato tabellare i nomi di database oggetto della manutenzione che sono passati come parametro alla stessa in formato stringa separati da virgola. Questa funzione verifica anche l’esistenza dei database passati come parametro, e in caso uno di questi nomi non corrisponde a un database realmente presente questo non viene restituito dalla funzione.

Figura 3: la funzione di selezione del database

• la tabella dbo.CommandLog: dove si può scegliere di loggare l’esito dei vari task impostando a “Y” il parametro @LogToTable che come già detto in precedenza è presente in tutte e tre le stored procedure fondamentali (dbo.DatabaseBackup, dbo.DatabaseIntegrityCheck, dbo.IndexOptimize). Attenzione però che di default @LogToTable è sempre impostato a “N”, quindi non avremo nessun log nella tabella dbo.CommandLog se non mettiamo mano ai vari job aggiungendo appunto @LogToTable = ‘Y’ tra i parametri della stored procedure.

Figura 3: il sistema di log

Per eliminare i vecchi log contenuti in questa tabella, il setup crea il job CommandLog Cleanup che di default cancella le righe più vecchie di 30 giorni (limite che può essere facilmente cambiato modificando la DELETE contenuto nel job stesso). Bisogna però ricordarsi di schedulare il job che come tutti gli altri viene creato senza alcuna schedulazione.

Altri articoli

Altre guide

Altre newsletter