UGISS User Group Italiano SQL Server
SNAPHOT di Database
Lo snapshot di un database è una visualizzazione statica in sola lettura di un database; il database origine. In poche parole lo snapshot è una "foto" del database in sola lettura fatta al momento di creazione dello snapshot stessa. Per un database di origine possono esistere contemporaneamente più snapshot.
Uno snapshot si deve trovare nella stessa istanza di database del server origine.
È possibile utilizzare gli snapshot per la generazione di report. In caso di errore da parte di un utente in un database di origine, è inoltre possibile ripristinare lo stato in cui si trovava il database al momento della creazione dello snapshot. La perdita di dati viene quindi limitata agli aggiornamenti apportati al database dopo la creazione dello snapshot.
A differenza del backup creare uno snapshot è una operazione velocissima dell’ordine di pochissimi secondi.
Funzionamento degli snapshot
La creazione degli snapshot è veloce perchè in realtà SQL Server crea un insieme di file spare su file system. I file spare sono una funzionalità del file system del sistema operativo.
Un file spare è un file il cui spazio non viene allocato al momento della creazione ma durante la scrittura del file stesso. All’interno del file spare sono scritti sempre 64 KB o multipli (64 KB per SQL Server corrispondono ad un EXTENT formato da 8 pagine di 8 KB ciascuna).
SQL Server prima di modificare una pagina del database originario se esistono degli snapshot e questi non contengono la pagina in questione provvede a copiare la pagina all’interno dei file dei vari snapshot e soltanto a questo punto alla modifica della pagina stessa.
A questo punto quando andiamo a leggere da uno snapshot in realtà leggiamo o pagine dello snapshot, se modificate nel database originario, o pagine del database originario se non ancora modificate.
E’ grazie a questa tecnica che è possibile velocizzare la creazione dello snapshot di un database.
E’ ovvio a questo punto che la dimensione di uno snapshot cresce in base alla sua "anzianità".
Di seguito riporto i limiti degli snaphot come riportati sulla guida on line e rimandando alla guida stessa per altre informazioni sugli snapshot
Limiti del database di origine
In presenza di snapshot del database, al database di origine dello snapshot si applicano le limitazioni seguenti:
- Non è possibile eliminare, scollegare o ripristinare il database. Per informazioni sulla procedura di eliminazione di uno snapshot
- Le prestazioni sono ridotte a causa del numero maggiore di operazioni I/O sul database di origine derivanti da un’operazione di copia in scrittura per lo snapshot eseguita a ogni aggiornamento di una pagina.
- Non è possibile eliminare i file dal database di origine o da uno snapshot.
Limiti degli snapshot del database
Agli snapshot del database si applicano le limitazioni seguenti:
- È necessario creare uno snapshot del database sulla stessa istanza del server del database di origine.
- Uno snapshot del database rappresenta una copia del database nel momento in cui è iniziata la creazione dello snapshot, meno eventuali transazioni di cui non è stato eseguito il commit. Nel corso della creazione di uno snapshot del database viene eseguito il rollback delle transazioni di cui non è stato eseguito il commit poiché Database Engine (Motore di database) esegue il recupero sullo snapshot (senza effetti sulle transazioni nel database).
- Se, quando una pagina in fase di aggiornamento sul database di origine viene inserita in uno snapshot, questo esaurisce lo spazio su disco oppure si verifica un altro errore, lo snapshot diventa sospetto e deve essere eliminato.
- Gli snapshot sono di sola lettura.
- Gli snapshot dei database model, master e temp non sono consentiti.
- Non è possibile modificare le specifiche dei file degli snapshot del database.
- Non è possibile eliminare file da uno snapshot.
- Non è possibile eseguire il backup o il ripristino degli snapshot.
- Non è possibile collegare o scollegare snapshot.
- Non è possibile creare snapshot con file system FAT32 o partizioni RAW.
- L’indicizzazione full-text non è supportata per gli snapshot del database e i cataloghi full-text non vengono propagati dal database di origine.
- Uno snapshot del database eredita i vincoli di protezione del proprio database di origine esistenti al momento della creazione dello snapshot. Poiché gli snapshot sono di sola lettura, non è possibile modificare le autorizzazioni ereditate e le modifiche alle autorizzazioni effettuate sul database di origine non verranno riportate negli snapshot esistenti.
- Uno snapshot riflette sempre lo stato dei filegroup al momento della sua creazione. I filegroup in linea e quelli non in linea non modificano il proprio stato. Per ulteriori informazioni, vedere la sezione dedicata agli snapshot del database con filegroup non in linea più avanti in questo argomento.
- Se un database di origine acquisisce lo stato di RECOVERY_PENDING, potrebbe non essere possibile accedere ai relativi snapshot del database. Dopo aver risolto il problema sul database di origine, tuttavia, i relativi snapshot dovrebbero ridiventare disponibili.
- Il ripristino non è supportato per i filegroup di sola lettura e compressi. Eventuali tentativi di ripristinare filegroup di questo tipo determineranno un errore.
Utilizzo in automatico degli snapshot
E veniamo allo scopo di questo articolo.
Creare un processo automatico che mi crei degli snapshot del mio database di origine.
Supponiamo che il database di origine si chiami DBTEST.
La seguente procedura effettua le seguenti operazioni :
- crea un nuovo snapshot
- elimina lo snapshot presente di quel tipo più vecchio di 24 ore
- invia una mail per segnalare l’avvenuta operazione
Se creiamo un agent che esegue periodicamente questa stored abbiamo un processo automatico di gestione degli snapshot che possiamo utilizzare.
Ad esempio, eseguendo questa procedura 3 volte al giorno si hanno sempre in linea 3 "foto" del nostro database principale.
All’interno della procedura sono stati messi molti controlli sui comandi drop database per essere sicuro di non lanciare per errore un comando drop database sul database principale. In ogni caso è consigliabile lanciare questa stored dal database princiale stesso. Infatti, un comando drop database non ha effetto se ci sono delle connessioni attive al database stesso.
@Tiposnapshot int = 0 — Indica quale snapshot creare. 1 : Punti Ripristino
)
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;
DECLARE @PercorsoSnapshot varchar(500)
DECLARE @Comando varchar(MAX)
DECLARE @ComandoElim varchar(MAX)
DECLARE @Source int
DECLARE @Corpo varchar(1000)
DECLARE @Oggetto varchar(1000)
DECLARE @Suffisso varchar(20)
DECLARE @NomeSnapshot varchar(300)
DECLARE @Desc varchar(200)
DECLARE @DataCreazione datetime
DECLARE @DataEliminazione datetime
DECLARE @NomeDBDel varchar(500)
set @NomeSnapshot = ”
if @Tiposnapshot = 1
begin
set @DataCreazione = getdate()
set @DataEliminazione = dateadd(hour, -23, @DataCreazione)
set @Suffisso = ‘_’ + convert(varchar, @DataCreazione, 126)
set @Suffisso = replace(@Suffisso, ‘:’, ”)
set @Suffisso = replace(@Suffisso, ‘T’, ”)
set @Suffisso = replace(@Suffisso, ‘-‘, ”)
set @Suffisso = replace(@Suffisso, ‘.’, ”)
set @NomeSnapshot = ‘DBTEST_SNPR’+ @Suffisso
set @PercorsoSnapshot = ‘percorso su macchina SQL su cui si vuol memorizzare lo snapshot’
set @Desc = ‘per Gestione Punti Ripristino Giornalieri.’
end
else
begin
return
end
set @Comando = ”
— Creazione
SELECT @Comando = @Comando + COALESCE(‘( NAME = N”’ + Name + ”’, FILENAME = N”’+ @PercorsoSnapshot + Name + @Suffisso + ‘.ss’ + ”’),’ ,”)
FROM
sys.master_files AS s
WHERE
(s.database_id = db_id(‘DBTEST’) and s.state = 0 and type = 0)
order by file_id
set @Comando = left(@Comando,len(@Comando) – 1)
if @NomeSnapshot <> ”
begin
set @Source = 0
select @Source = Source_database_id from sys.databases where name = @NomeSnapshot
if @Source <> 0
begin
SET @ComandoElim = ‘DROP DATABASE [‘ + @NomeSnapshot + ‘]’
if (@ComandoElim <> ‘DROP DATABASE [DBTEST]’) AND (@ComandoElim <> ‘DROP DATABASE DBTEST’)
begin
EXEC (@ComandoElim)
end
end
SET @ComandoElim = ”
set @Comando = ‘CREATE DATABASE [‘ + @NomeSnapshot + ‘] ON ‘ + @Comando + ‘ AS SNAPSHOT OF [DBTEST]’
EXEC (@Comando)
if @Tiposnapshot in (1)
begin
set @NomeDBDel = ”
select TOP(1) @NomeDBDel = name
from sys.databases
where source_database_id = db_Id(‘DBTEST’) and create_date <= @DataEliminazione
and name LIKE ‘DBTEST_SNPR%’
ORDER BY create_date
if @NomeDbDel <> ”
begin
if (left(@NomeDbDel, 16) = ‘DBTEST_SNPR_’) and (@NomeDBDel <> @NomeSnapshot) and (@NomeDBDel <> ‘DBTEST’)
begin
set @Source = 0
select @Source = Source_database_id from sys.databases where name = @NomeDBDel
if @Source <> 0
begin
SET @ComandoElim = ‘DROP DATABASE [‘ + ltrim(rtrim(@NomeDBDel)) + ‘]’
if @ComandoElim = ‘DROP DATABASE [DBTEST]’
begin
set @ComandoElim = ”
end
end
else
begin
set @ComandoElim = ”
end
end
else
begin
set @ComandoElim = ”
end
end
if @ComandoElim <> ”
begin
EXEC (@ComandoElim)
end
end
set @Corpo = N’Esecuzione Snapshot ‘ + @Desc + char(13) + char(10) +
‘Snapshot Creato : ‘ + @NomeSnapshot + char(13) + char(10)
if @ComandoElim <> ”
begin
set @Corpo = @Corpo + N’Snapshot Eliminato : ‘ + @NomeDbDel + char(13) + char(10) +
‘Comando Eliminazione : ‘ + @ComandoElim + char(13) + char(10)
end
set @Oggetto = N’Esecuzione Snapshot ‘ + @Desc
exec msdb.dbo.sp_send_dbmail
@profile_name = ‘profilo di spedizione’,
@recipients = ‘email a cui spedire’,
@body = @Corpo,
@subject = @Oggetto;
end
Note Dolenti
Unica nota dolente di questo meccanismo è la versione di SQL Server 2005 necessaria. Infatti questa tecnologia è disponibile solo per SQL Server 2005 Enterprise Edition
