DB Maintenance: SQL Server backup common strategies

Avere una corretta strategia di backup per la farm SQL Server risulta vitale per proteggersi da eventi di corruzione, perdita di dati o fermi produttivi.

Ogni corretta strategia di backup deve tenere in considerazione i seguenti punti:

• Recovery Model: Simple, Full e Bulk-logged
• Requisiti di RPO e RTO:
  
  • RPO la quantità di tempo di lavoro che mi posso permettere di perdere, in proporzione alla quantità di dati generata in un determinato intervallo temporale es. "mi posso permettere di perdere un’ora workload del database?"
  • RTO tempo massimo che posso attendere per il restore dei database;
• Retention dei backup;
• Restore.

Cosa sono quei file che trovo all’interno del mio database?

Come possiamo vedere dall’immagine sopra ogni database è composto da diversi file ognuno con la sua funzione:

• mdf: file principale del database contente gli oggetti, ne troviamo uno per database; questa tipologia di file può essere associati ad altri con estensione ndf;
  
• ldf: è il file del Transaction Log, ogni volta che viene apportata una modifica a un oggetto del database o ai dati, tutti i cambiamenti verranno scritti al suo interno. Questo permette di effettuare un rollback nel caso in cui ci sia un arresto anomalo del database, annullamento di una transazione ecc.. permettendo di tornare a uno stato coerente dei dati contenuti. I dati verranno poi scritti all’interno dei/del data file nei seguenti casi:
  
  • Checkpoint;
  • La transazione inizialmente scritta ha effettuato il commit;
  • Effettuato il backup dei log (recovery model full).
• ndf: questo tipo di file viene solitamente creato quando il primario (mdf) raggiunge dimensioni eccessive e si vuole introdurre un partizionamento;

Cos’è il recovery model di un database?

All’interno di SQL Server troviamo 3 tipi di recovery model:

• Simple
• Full
• Bulk-logged

Queste 3 tipologie determinano la quantità di dati che possiamo permetterci di perdere e le tempistiche di recupero.

Simple

Con questo recovery model hai la possibilità di ripristinare il database dall’ultimo backup effettuato, full o differenziale. Non permette di effettuare il recupero dei dati point-in-time.

Ogni transazione effettuata viene scritta all’interno del Transaction Log; i record registrati al suo interno verranno rimossi automaticamente quando la transazione è completata o quando si verifica un checkpoint.

Questo significa che il log delle transazioni viene pulito automaticamente permettendo di avere delle dimensioni ridotte.

Il ripristino dei dati sarà possibile solo dall’ultimo backup full o diff effettuato. Solo nel caso del SIMPLE il checkpoint (manuale o automatico) libera anche i VLF, nel caso del FULL ciò non è vero.

Solitamente questo modello di recupero si consiglia:

• Database non critici o per i quali non è necessario prevedere un RPIT;
• Database di test o sviluppo;
• Dati che possono essere facilmente recuperati e di conseguenza c’è una tolleranza nelle tempistiche di recupero.

Full

Supporta tutte le tipologie di backup: full, diff e log, permettendo di applicare il RPIT.

A differenza del modello di recupero simple, in questo caso tutte le transazioni rimangono all’interno del transaction log. I record saranno mantenuti al suo interno fino a quando non verrò effettuato il backup del transaction log; una volta effettuato il backup questi verranno rimossi e le porzioni precedentemente occupate dai record (VLFs) saranno riutilizzabili.

Questo significa che il transaction log deve avere una dimensione sufficiente da supportare la registrazione di tutte le transazioni tra due backup consecutivi, altrimenti c’è il rischio (in caso di  esaurimento dello spazio su cui risiede il transactionLog) di incorrere nell’errore 9002 e rendere il database accessibile solo in lettura.

L’intervallo di tempo tra un backup del transaction log e l’altro varia in base alla schedulazione dei full e diff. Si può decidere di effettuare un backup del TL anche ogni 10min.

I motivi per cui si sfrutta questo modello di recupero:

• Database critici o per i quali è necessario prevedere un RPIT
• Utilizzo del mirroring del database;
• Utilizzo AlwaysOn o AvailabilityGroup

Quando si abilita questa opzione bisogna tenere in considerazione anche che il Transaction Log richiederà maggiore spazio, in base alla frequenza del backup dei log, scritture e modifiche.

Bulk Logged

Questo modello di recupero è quello meno utilizzato rispetto a quelli appena descritti.

Riduce lo spazio dei log necessario perché i record delle transazioni vengono memorizzati in minima parte mentre vanno in esecuzione gli statements evitando ad esempio di registrare operazioni bulk insert, select into e operazioni di rebuild degli indici.

Solitamente viene impostato quando sono pianificate operazioni particolari che portano un vantaggio nel suo utilizzo, altrimenti si consiglia di utilizzare sempre i modelli simple o full.

Come impostare il  backup?

Come spiegato sopra quando vado a impostare un backup a livello di SQL Server la prima cosa che bisogna avere chiaria è il recovery model dei database presenti.

Come possiamo implementare i nostri backup?

• Utilizzando script ad hoc;
• Prodotti di terze parti come Veeam Backup Replication;
• Maintenance Plan Wizard.

Una volta deciso come implementarli e la modalità di recovery model si può procedere con la loro configurazione.

Di seguito faremo un esempio di implementazione utilizzando gli script di Ola Hallengren un’opzione molto utilizzata nel modo di SQL Server perché di semplice implementazione e affidabile.

Consigliamo sempre di affiancare ai backup anche un integrity check per rilevare eventuali corruzioni dei database e poter intervenire in tempi rapidi.

Solitamente si consiglia di lanciare lo script di installazione delle stored procedure, per le operazioni di manutenzione, all’interno del database master e non all’interno dei database utente.

Configureremo un job di manutenzione che contenga le due operazioni per i backup full e diff più integrity check.

Invece per il transaction log sarà sufficiente configurare lo step di backup.

Sotto possiamo vedere le stored procedure importate:

Offrono una serie di parametri che ci permettono di configurare l’operazione di manutenzione.

Come possiamo vedere il primo step configurato è quello del checkdb, di seguito i comandi:

EXECUTE dbo.DatabaseIntegrityCheck -- richiamo la stored procedure
@Databases = 'USER_DATABASES', -- effettuo il checkdb su tutti i database
@CheckCommands = 'CHECKDB' -- comando lanciato
@LogToTable = 'Y'

Configuriamo il job in da intercettare una corruzione e fare fallire l’intero job, in questo modo avremo sempre backup consistenti potendo intervenire immediatamente.

Questo step sarà il medesimo per tutti i job di backup full e diff.

Ora andremo a configurare il secondo step quello di backup full:

EXECUTE dbo.DatabaseBackup -- richiamo la stored procedure
@Databases = 'USER_DATABASES', -- effettuo il checkdb su tutti i database
@Directory = 'C:\Backup', -- directory di backup che potrebbe essere anche una share di rete
@BackupType = 'FULL', -- tipologia di backup
@Verify = 'Y', -- verifica il backup cioè che sia utilizzabile il file non la struttura db
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24 -- retention in ore
@LogToTable = 'Y' -- ti permette di registrare i comandi ed errori all’interno della tabella dbo.CommandLog

Si può specificare il nome dei database su cui si vuole effettuare il backup e la tipologia di backup.

Infatti, per implementare il differenziale è sufficiente cambiare il comando di @BackupType = ‘DIFF’.

Per quanto riguarda i database in recovery model full sarà necessario implementare un job di backup sul transaction log. La frequenza può variare da qualche minuto a un’ora in funzione di:

• Necessità aziendali;
• Frequenza di modifica dei dati all’interno dei database;
• Dimensione dei database.

Un esempio di comando e configurazione può essere il seguente:

EXECUTE [dbo].[DatabaseBackup]
@Databases = 'USER_DATABASES',
@Directory = 'Z:\',
@BackupType = 'LOG',
@Verify = 'Y',
@CleanupTime = 168,
@CheckSum = 'Y',
@LogToTable = 'Y'

Articolo a cura di:

Giada Salvatori

DB & APM Specialist di AD Consulting