Database Snapshots – jak to działa

Ostatnio w pracy miałem okazję dyskutować z moim kolegą Piotrem (autorem bloga msbifun.pl) na temat mechanizmu o nazwie Database Snapshots. Zastanawialiśmy się czy zastosowanie tego mechanizmu będzie odpowiednim podejściem w naszej architekturze przepływu danych. Rozmowa ta zainspirowała mnie do tego aby napisać artykuł na ten temat w ramach niniejszego bloga – dlatego też zapraszam do dalszej lektury.

Na samym początku kilka słów na temat czym jest Snapshot. Mechanizm ten działa na poziomie stron danych bazy i jest niejako kopią bazy źródłowej (migawką) na określony moment w czasie. Przy czym w samej migawce znajdują się jedynie strony danych, które zostały zmodyfikowane od momentu stworzenia migawki. Tak więc na samym początku po stworzeniu snapshota żadne dane nie są kopiowane, a miejsce w pliku snapshotu nie jest alokowane. Na poniższej grafice można zauważyć przykładową tabelę w ramach bazy danych (source database) oraz jej snapshot. Poszczególne strony (od 1 do 9) w źródłowej tabeli nie zostały jeszcze zmienione dlatego też snapshot jest pusty.

Tak więc wykonując zapytanie SELECT na migawce tak naprawdę odpytywana jest źródłowa baza danych.

W przypadku gdy zmodyfikowana zostanie którakolwiek ze źródłowych stron danych to natychmiast robiona jest kopia przed jej modyfikacją i wysyłana do migawki. Nowa wartość z kolei ląduje w źródłowej bazie danych – operacja taka nazywana jest Copy On Write

W tym miejscu dosyć ciekawie wygląda odczyt danych ze snapshota – strony 1,3,4,5,6,7,8,9 są odczytywane ze źródłowej bazy danych natomiast strona 2 odczytywana jest bezpośrednio z migawki. Tak więc rozmiar migawki zależy od tego ile danych zostało zmodyfikowanych od czasu utworzeniu snapshotu. Możecie sobie zadawać pytanie jaka jest zaleta snapshotu nad robieniem kopii bazy i jej przywracaniem lub też mirroringiem i innymi mechanizmami wysokiej dostępności – odpowiedź jest bardzo prosta, a mianowicie zasoby dyskowe. Snapshot zajmuje dużo mniej miejsca w porównaniu do kopii i dopiero z czasem rozrasta się do pokaźnych rozmiarów. Wadą tego podejścia bez wątpienia jest spowolnienie modyfikacji danych w źródłowej bazie danych, ale o tym powiemy sobie w dalszej części niniejszego artykułu.

Sprawdźmy działanie tego mechanizmu w praktyce – na samym początku stwórzmy bazę źródłową oraz tabelę do której wstawimy testowe dane:

USE master
GO

CREATE DATABASE SOURCE_DB ON
( 
 NAME = SOURCE_DB_Data, 
 FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\Source_DB_data.mdf'  
)
GO

USE SOURCE_DB
GO


CREATE TABLE TestTable
(
id int,
name NVARCHAR(20)
)
GO

INSERT INTO TestTable
(
    id,
    name
)
SELECT
    1,
    REPLICATE(N'A',20)
GO 100

INSERT INTO TestTable
(
    id,
    name
)
SELECT
    2,
    REPLICATE(N'B',20)
GO 100

Następnie na stwórzmy snapshot nowo powstałej bazy danych – służy do tego standardowa składnia CREATE DATABASE ze słowem kluczowym AS SNAPSHOT OF. Ważne jest to aby nazwa logiczna pliku czyli właściwość NAME była taka sama jak bazy źródłowej. Zwróćcie uwagę również na to, że rozszerzenie samego pliku danych nie ma znaczenia i często w przypadku migawek stosuje się rozszerzenie “ss” aby odróżnić je od standardowych plików mdf i ndf. Sam plik snapshotu nazywamy mianem sparse file:

CREATE DATABASE SOURCE_DB_SNAPSHOT ON
( 
 NAME = SOURCE_DB_Data, 
 FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\Source_DB_data.ss'  
)
AS
SNAPSHOT OF SOURCE_DB
GO

Po stworzeniu naszego obiektu możemy go znaleźć np. poprzez Management Studio w specjalnej lokalizacji w eksploratorze obiektów nazwanej Database Snapshots:

Lub też odpytać widok systemowy sys.databases gdzie pole source_database_id wskazuje źródłową bazę dla snapshota. Jeśli to pole zawiera wartość NULL oznacza to, iż baza nie jest migawką.

Sprawdźmy sobie teraz rozmiar naszego snapshota używając obiektów systemowych sys.dm_io_virtual_file_stats oraz sys.master_files:

SELECT 
    DB_NAME(DB_ID('SOURCE_DB_SNAPSHOT')) AS [Database Name],
    VFS.[file_id] AS [FileID],
    MF.[name] AS [Logical File Name],
    (MF.[size] * 8) AS [Max Size (KB)],
    (VFS.size_on_disk_bytes / 1024) AS [Size on Disk (KB)]
FROM sys.dm_io_virtual_file_stats(DB_ID('SOURCE_DB_SNAPSHOT'), NULL) VFS
JOIN sys.master_files MF
    ON VFS.[file_id] = MF.[file_id]
WHERE VFS.database_id = MF.database_id
AND MF.database_id = DB_ID('SOURCE_DB_SNAPSHOT')
AND MF.type_desc != 'LOG'
GO

Jak widać na ten moment zajmuje on na dysku tylko 128KB. Aby zobaczyć działanie mechanizmu w pełni wykonajmy na tabeli bazy źródłowej operację DELETE.

USE SOURCE_DB
GO
DELETE FROM TestTable

Następnie odpytajmy bazę migawkową o dane:

USE SOURCE_DB_SNAPSHOT
GO
SELECT count(*) AS NumberOfRows FROM TestTable

Jak widać wiersze nadal są w snapshocie mimo ich usunięcia z bazy źródłowej. Zobaczmy czy jego plik się zwiększył:

Jak widać rozmiar zwiększył się kilkukrotnie co tylko udowadnia, że strony w których zmodyfikowaliśmy dane znalazły się w migawce. Oczywiście oprócz kopii danych na określony moment w czasie może pojawić się potrzeba ich przywrócenia – możemy do tego również użyć naszego snapshotu. W przypadku odtwarzania bazy ze snapshotu nie mówimy o operacji RESTORE,a o operacji REVERT.Sama składnia polecenia przywracającego jest bardzo prosta i wygląda następująco:

RESTORE DATABASE SOURCE_DB from   
DATABASE_SNAPSHOT = 'SOURCE_DB_SNAPSHOT';  
GO

Tworzenie snapshotów może być bardzo przydatne i pozwala szybko przywrócić dane czy też usuniętą tabelę. Wspominałem również o tym, że mechanizm ten spowalnia nasze operacje modyfikujące – sprawdźmy to w małym teście. Na samym wstępie należy pamiętać o tym, że poniższy test jest obciążony błędem gdyż na czas odczytu ma wpływ wiele czynników natomiast mimo wszystko da nam to pewien pogląd na temat opóźnień. Tak więc stwórzmy sobie nową tabelę w ramach naszej bazy źródłowej i wstawmy do niej 100 000 wierszy.

CREATE TABLE TestTable2
(
id int,
name NVARCHAR(20)
)
GO

SET STATISTICS TIME OFF
GO

SET NOCOUNT ON
GO


INSERT INTO TestTable2
(
	id,
	name
)
SELECT
	1,
	REPLICATE(N'A',20)
GO 100000

Następnie zaktualizujmy wartość pola id dla wszystkich wierszy i zmierzmy czas komendą SET STATISTICS TIME ON

SET STATISTICS TIME ON
GO

UPDATE TestTable2
SET id=1000 

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 6 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 281 ms,  elapsed time = 275 ms.

Jak widać cała operacja zajęła 275 milisekund. Aby sprawdzić jakie będzie obciążenie migawki stwórzmy nową migawkę na podstawie naszej źródłowej bazy (tak – jest możliwe posiadanie wielu migawek na tej samej bazie danych):

CREATE DATABASE SOURCE_DB_SNAPSHOT2 ON
( 
 NAME = SOURCE_DB_Data, 
 FILENAME ='C:\Program Files\Microsoft SQL Server\MSSQL13.SQL16\MSSQL\DATA\Source_DB_data2.ss'  
)
AS
SNAPSHOT OF SOURCE_DB
GO

Następnie ponownie wykonajmy aktualizację – bardzo podobną do poprzedniego przypadku:

UPDATE TestTable2
SET id=2000 

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 422 ms,  elapsed time = 2005 ms.

W tym przypadku cała operacja zajęła 2005ms czyli kilkakrotnie dłużej niż w przypadku tabeli bez snapshota. Mimo wszystko jest to niewiele i w wielu przypadkach może nie mieć to dla nas żadnego znaczenia. Pamiętajmy również, że wydłużony czas operacji modyfikujących występuje tylko przy pierwszej modyfikacji danych – kolejne nie potrzebują już kopiowania oryginalnych stron danych do migawki bo te już się tam znajdują:

UPDATE TestTable2
SET id=3000

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 2 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 359 ms,  elapsed time = 351 ms.

Jak widać mechanizm ten daje nam spore możliwości zarówno pod kątem wersjonowania całej bazy danych jak i przywracania danych do określonego momentu w czasie. Jedyne o czym warto i trzeba pamiętać to fakt, iż nie jest to w żadnym wypadku alternatywa dla kopii zapasowych i wiąże się z tym mechanizmem pewne opóźnienie związane z modyfikacjami danych. Mimo wszystko warto znać ten mechanizm ponieważ może nam się to przydać w bardzo wielu scenariuszach.