Database Snapshots – jak to działa

DatabaseSnapshotsHowITWorks_00

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:

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:

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:

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.

Następnie odpytajmy bazę migawkową o dane:

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:

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.

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

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):

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

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ą:

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.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

Latest posts by Adrian Chodkowski (see all)

3 Comments

  1. Gość

    Msg 1844, Level 16, State 1, Line 1
    Database Snapshot is not supported on Express Edition.
    ______________________________________________________

    Szkoda 🙁

    Reply
    1. Adrian Chodkowski (Post author)

      polecam zainstalować SQL 16 SP1 🙂

      Reply
      1. Gość

        Tak, ale na mojej Viście “najnowszą” wersją którą mogę uruchomić, jest 2012.

        Reply

Leave a Comment

Your email address will not be published. Required fields are marked *