Passa al contenuto

Un’interessante feature di SQL Server: Snapshot Database

Un'interessante feature di SQL Server riguarda gli snapshot. Volendo definire snapshot, Microsoft lo definisce come “una vista statica in sola lettura di un database”. In altre parole si tratta di un istantanea, una fotografia, del database di origine, catturata in un determinato momento. 

Lo snapshot è strettamente legato al database di partenza e non può esistere se il database originario viene eliminato; ciò è reso possibile grazie a dei files che puntano (puntatori) alla fonte originale delle pagine dei dati, chiamati File Sparse. Per poter spiegare a cosa ci si riferisce quando si parla di File Sparse, occorre sapere che gli snapshot operano a livello di pagine di dati e quando una pagina viene modificata, la pagina originale viene copiata dal database di origine allo snapshot. Ecco quindi come, da quanto detto, risulta facile desumere come mai uno snapshot appena creato occupa uno spazio vicino allo zero, mentre man mano che il database di origine subisce modifiche, lo snapshot cresce di dimensione poiché contiene le pagine non modificate del database padre. I file sparse sono una caratteristica dell’ NTFS di conseguenza gli snapshot non trovano applicazione in file system FAT32.              

Prima di addentrarci nelle procedure e ulteriori valutazioni sullo snapshot, vediamo quando può essere usato e a cosa può tornarci utile: 

  • Possiamo utilizzare gli snapshot per salvaguardare il database di origine da aggiornamenti errati a seguito di delete, drop update non voluti, ripristinando il database a partire dallo snapshot catturato. Ad esempio, prima di eseguire un’update importante sul Database di origine, è possibile creare uno snapshot immediatamente prima dell’operazione; se l’operazione di aggiornamento non dovesse dare i risultati sperati, si può sempre ripristinare il database di origine, partendo dallo snapshot preliminarmente catturato.
  • Gli snapshot possono essere utilizzati per attività di reporting.Si potrebbero ad esempio catturare degli snapshot ad intervalli regolari e mostrare i dati tramite reporting, fermi ad un determinato arco temporale. 

Sempre in ambito di reporting, l’utilizzo degli snapshot del database con il mirroring del database, consente di rendere disponibili, per la creazione di report, i dati archiviati nel server mirror. L’esecuzione di query sul mirror DB consente di liberare risorse sul server principale. 

L’acquisizione di più snapshot può risultare utili per l’audit o per effettuare analisi di periodi differenti corrispondenti all’acquisizione degli snapshot. 

Occorre  però sottolineare che non bisogna confondere lo snapshot con il backup del database: lo snapshot è legato al database di origine e a suoi data files e nel momento in cui i files dovessero risultare incoerenti per qualsiasi ragione, gli snapshot non potrebbero fare riferimento a questi e di conseguenza il ripristino da istantanea risulterebbe impossibile. 

Su SQL Server non è presente nessuna GUI per la generazione degli snapshot, di conseguenza occorrerà utilizzare del codice per poter operare con gli snapshot. 

Supponiamo di voler catturare degli snapshot ad intervalli prestabiliti ogni 6 ore che coprano le 24 ore giornaliere, per avere delle viste statiche del nostro database. Per poter coprire le 24 ore creeremo 4 snapshot. Nomineremo gli snapshot come: 

  • snapshot_06 
  • Snapshot_12 
  • Snapshot_18 
  • Snapshot_24. 

Il nostro database di origine è nominato: “Demo_DB_Snap” ed è costiuito da 4 tabelle

Casella di testoPer creare lo snapshot_06 procederemo come segue: 

CREATE DATABASE Snapshot_06 ON (
	NAME = Demo_DB_Snap
	,FILENAME = 'C\Temp\snapshot_06.ss'
	) AS SNAPSHOT OF Demo_DB_Snap;
GO

Da notare che lo snapshot può risidere su un disco diverso da quello in cui si trovano i datafiles del database di origine, inoltre l’estensione non assume importanza, possiamo utilizzare quello che vogliamo al di là del punto, come ad esempio snapshot.snap, snapshot.st, ecc. 

Eseguendo, lo stesso script sopra per generare gli altri snapshot, agli orari prestabiliti, ci troveremo con 4 istantanee nelle fasce orarie che ci eravamo preposti. 

CREATE DATABASE snapshot_12 ON (
	NAME = Demo_DB_Snap
	,FILENAME = ‘C: \Temp\snapshot_12.ss’
	) AS SNAPSHOT OF Demo_DB_Snap;
GO



CREATE DATABASE snapshot_18 ON (
	NAME = Demo_DB_Snap
	,FILENAME = ‘C: \Temp\snapshot_18.ss’
	) AS SNAPSHOT OF Demo_DB_Snap;
GO



CREATE DATABASE snapshot_24 ON (
	NAME = Demo_DB_Snap
	,FILENAME = ‘C: \Temp\snapshot_24.ss’
	) AS SNAPSHOT OF Demo_DB_Snap;
GO

Lo snapshot_06 è la fotografia alle ore 6 del mattino, lo snapshot_12 la fotografia alle ore 12, e così via. I diversi snapshot, potrebbero essere usati per la generazione di report in momenti diversi della giornata. Accanto ad ogni snapshot viene indicato “Read-Only”, poichè gli snapshot non possono subire modifiche dei dati. L’esecuzione di un comando di update o di drop table ad esempio, restituirebbe il seguente errore:

Lo snapshot del database può essere utile anche per ripristinare il database in caso di errori accidentali; prendiamo ad esempio il caso seguente, in cui andremo a creare uno snapshot nominato “snapshot_test” e cancelliamo una tabella presente nel nostro database di origine “Demo_DB_Snap”. 

Eseguiamo quindi nel database di origine: 

DROP TABLE [dbo].[Table_Snap_01]

Ci troveremo di conseguenza nella situazione in cui, il database di origine manca della tabella Table_Snap_01, mentre sarà ancora presente nello “snapshot_test”.

Se volessimo ripristinare il database a partire dallo “snapshot_test” a seguito di una drop accidentale, ci basterà effettuare la restore a partire proprio dallo “snapshot_test”. 

Eseguiamo: 

USE master;

RESTORE DATABASE [Demo_DB_Snap]
FROM DATABASE_SNAPSHOT = ’snapshot_test’;
GO

Riusciamo così a ripristinare il database e conseguentemente la tabella cancellata in precedenza.

Il ripristino del database può essere utile anche nel caso di DELETE, TRUNCATE o UPDATE accidentali, ripristinando il record o la tabella interessata. 

Per simulare quanto sopra, prendiamo adesso in considerazione la tabella “dbo. Table_Snap_01” ed eliminiamo il record con id=5: 

DELETE
FROM [Demo_DB_Snap].[dbo].[Table_snap_01]
WHERE ID_customer = 5

Volendo ripristinare solo il record eliminato accidentalmente, potremmo effettuare una semplice insert a partire dalla tabella presente nello snapshot “snapshot_test” precedentemente creato:

SET IDENTITY_INSERT [dbo].[Table_Snap_01] ON

INSERT INTO [dbo].[Table_Snap_01] (
	[ID_customer]
	,[Nome]
	,[Cognome]
	,[Indirizzo]
	,[numero civico]
	)
FROM [snapshot_06].[dbo].[Table_Snap_01]
WHERE id_customer = 5
GO

SET IDENTITY_INSERT [dbo].[Table_Snap_01] OFF

Ecco il record ripristinato:

Come già detto lo snapshot occupa uno spazio vicino allo 0 appena creato, e cresce di volta in volta che il database originario viene modificato. Lo snapshot può crescere anche molto velocemente quindi occorre monitorare lo spazio che occupa. La seguente query ci permette di visualizzare le dimensioni che gli snapshot occupano su disco: 

SELECT DB_NAME(sd.source_database_id) AS [SourceDatabase]
	,Sd.name AS [Snapshot]
	,Mf.name AS [Filename]
	,Size_on_disk_bytes / 1024 AS [size_on_disk(KB)]
	,
FROM sys.master_files mf
JOIN sys.DATABASE sd ON mf.database_id = sd.database_id
JOIN sys.master_files mf2 ON sd.source_database_id = mf2.database_id
	AND mf.file_id = mf2.file_id
CROSS APPLY sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse = 1
	AND mf2.is_sparse = 0
ORDER BY 1;

Casella di testoPer eliminare lo snapshot, si può eseguire semplicemente:

DROP DATABASE Snapshot_test


Conclusioni

Per chiudere, ecco una serie di punti da tenere presenti quando si utilizzano gli snapshot database in SQL Server:

  • Uno snapshot deve risiedere sullo stesso server in cui risiede il database originario. 
  • L’utilizzo degli snapshot porta ad un incremento di I/O, di conseguenza possono verificarsi problemi di prestazioni 
  • Il ripristino da snapshot, sarà, in molti casi, più veloce di un ripristino dal backup ma NON è possibile sostituire una politica di backup mediante l’utilizzo degli snapshot poiché in caso di database corrotti lo snapshot smetterebbe di funzionare. 
  • Gli snapshot possono diventare anche molto grandi e richiedere quindi sempre più spazio; ciò dipende naturalmente dal numero di movimentazioni che subisce il database originario, ma bisogna prestare attenzione, poiché se lo snapshot satura lo spazio a disposizione, questo risulta non più utilizzabile anche a seguito di un allargamento del disco. 
  • Non è possibile eseguire il ripristino da snapshot, di un database offline o danneggiato 
  • Con SQL Server 2019, il database di origine può contenere un filegroup MEMORY_OPTIMIZED_DATA 
  • Lo snapshot database supporta tutti i recovery Model 
  • Il database di origine non deve essere scollegato e non può essere ripristinato o eliminato, poichè lo snapshot smetterebbe di funzionare. 
  • Il backup del database di origine non viene influenzato dalla creazione dello snapshot. 
  • Non è possibile creare snapshot dei database: tempdb, model, master. 
  • Non è possibile effettuare backup dello snapshot altresì non può essere scollegato o collegato. 
  • L’indicizzazione full-text non è supportata. 
  • Gli snapshot ereditano le autorizzazioni dal database di origine. 

di Luciano Maugeri, pubblicato il 29 ottobre 2024

Introduzione alla crittografia delle connessioni in SQL Server