Home > Articoli > Utilizzo di Snapshot Database per la protezione da errori software

Utilizzo di Snapshot Database per la protezione da errori software

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.

 

CREATE PROCEDURE [dbo].[GenerazioneSnapshotDatabase] (
  @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

 

Chi è Davide Mauri

Microsoft Data Platform MVP dal 2007, Davide Mauri si occupa di Data Architecture e Big Data nel mondo dell'IoT. Attualmente ricopre il ruolo di "Director Software Development & Cloud Infrastructure" in Sensoria, societa specializzata nella creazione di Wearables e sensori per l'IoT applicati a tessuti ed oggetti sportivi.

Leggi Anche

Unit testing: Il framework tSQLt e l’esecuzione di un test!

Nell’articolo precedente, il primo di questa serie, abbiamo descritto che cosa è lo Unit Test …