Akademia SQL - Część 4: Kompresja kopii zapasowej
 |
Zawartość strony
 | Kompresja kopii zapasowej – informacje podstawowe |
| Kompresja kopii zapasowej – laboratorium |
| Kompresja kopii zapasowej – referencje |
Kompresja kopii zapasowej – informacje podstawowe
Kompresja kopii zapasowej jest kolejną ważną nowością, którą można będzie wykorzystać w silniku baz danych SQL Server 2008 w wersji Enterprise. Skompresowany plik kopii zapasowej jest mniejszy niż odpowiadający mu plik nie skompresowany, który zawiera te same dane. W związku z tym przeprowadzenie operacji wykonania kopii zapasowej wymaga wykonania mniejszej ilości operacji wejścia – wyjścia, a to skutkuje zmniejszeniem czasu potrzebnego na wykonanie kopii. Informacje te mają fundamentalne znaczenie dla administratorów baz danych, ponieważ kopie zapasowe powinny być jak najmniejsze i wykonywać się jak najkrócej. Z drugiej strony należy pamiętać, iż kompresja danych znacząco zwiększa zapotrzebowanie na zasoby procesora, co może negatywnie wpłynąć na pracę serwera. W odnośniku [2] na końcu lekcji podano praktyczny przykład, jak używając innej funkcjonalności – Resource Governor, limitować zasoby potrzebne do wykonania kopii zapasowej.
Administrator baz danych może obserwować liczniki systemowe, które dostarczą informacji niezbędnych potrzebnych do oceny wpływu wykonywania skompresowanych kopii zapasowych na serwer:
a) Obiekt SQLServer:Backup device, licznik Device Throughput Bytes/sec
b) Obiekt SQLServer:Databases, licznik Backup/Restore Throughput/sec
c) Inne systemowe liczniki mierzące zapis/odczyt operacji dyskowych czy monitorujące zużycie procesora
Domyślnie kompresja kopii zapasowej jest wyłączona. Można włączyć kompresję kopii zapasowej globalnie – na poziomie całej instancji serwera zmieniając jedną z jego opcji – backup compression default, ustawiając jej wartość na 1. Taka zmiana powoduje, iż wszystkie kopie zapasowe będą kompresowane. Można oczywiście to zachowanie zmienić i decydować w każdym przypadku, czy kopia określonej bazy danych powinna zostać skompresowana czy nie.
Istotną informacją dotyczącą omawianego zagadnienia jest możliwość wyliczenia współczynnika kompresji za pomocą prostego zapytania operującego na tabeli backupset w systemowej bazie danych msdb. Wystarczy policzyć stosunek wartości zapisanej w kolumnie backup_size do compressed_backup_size
SELECT backup_size/compressed_backup_size as [współczynnik kompresji] FROM msdb..backupset
Dla przykładu, współczynnik kompresji 5:1 oznacza, iż oszczędza się 80% miejsca na dysku. Wartość współczynnika kompresji jest bezpośrednio zależna co najmniej od kilku czynników:
a) Typu danych – np. typ znakowy kompresuje się lepiej niż inne typy danych
b) Rozkładu danych na stronach – w przypadku, gdy na stronie danych jest wiele wierszy zawierających te same wartości to kompresja będzie wydajniejsza
c) Zastosowanego szyfrowania bazy danych, czyli włączenia mechanizmu Transparent Data Encryption (TDE) – jeżeli szyfrowanie danych jest włączone, to kompresja kopii zapasowej może nie zmniejszyć rozmiaru pliku lub zmniejszyć go tylko nieznacznie
Przeprowadzone testy wykonane na przykładowej bazie danych AdventureWorks (o rozmiarze nieco ponad 150MB) pokazały, iż czas wykonania kopii zapasowej zmniejszył się o prawie 50% natomiast rozmiar pliku kopii zmniejszył się ponad czterokrotnie.
Uwaga! Współczynnik kompresji nie jest konfigurowalny, tzn. w żaden sposób nie może zostać narzucony systemowi.
Na zakończenie wstępu teoretycznego warto pamiętać, iż są trzy bardzo istotne ograniczenia związane z kompresją kopii zapasowej:
a) Kopie zapasowe skompresowane i nie skompresowane nie mogą współistnieć na tzw. media-set
b) Poprzednie wersje systemu SQL Server nie mogą odczytywać skompresowanych kopii zapasowych
c) Kopie zapasowe wykonane poleceniem ntbackup nie mogą współdzielić taśm ze skompresowanymi kopiami zapasowymi systemu SQL Server
Kompresja kopii zapasowej – laboratorium
Celem laboratorium będzie wykonanie dwóch zadań. W pierwszym z nich należy skonfigurować i sprawdzić ustawienia kompresji kopii zapasowej na poziomie instancji serwera baz danych. W drugim zadaniu pokażemy, w jaki sposób można za pomocą kodu T-SQL wykonać skompresowaną kopię zapasową bazy danych oraz sprawdzimy, jak długo trwa wykonanie takiej kopii oraz ile wynosi współczynnik kompresji danych.
Zadanie 1
1. Uruchom SQL Server Management Studio (SSMS) i połącz się z serwerem EVALUATION używając uwierzytelnienia WINDOWS.
2. W oknie eksploratora obiektów (Object Explorer) zaznacz instancję Evaluation i z menu kontekstowego wybierz opcję Properties.
3. W oknie własności instancji wybierz stronę ustawień dla baz danych (Database settings) i zaznacz opcję Compress backup, jak pokazano na rysunku poniżej. Po zatwierdzeniu tej opcji wszystkie kopie zapasowe wykonywane w ramach instancji Evaluation będą kompresowane.
4. Podobną operację można wykonać za pomocą kodu T-SQL. Sprawdzenie, czy kompresja kopii zapasowej została włączona odbywa się za pomocą następującego polecenia:
USE master GO EXEC sp_configure 'backup compression default' GO
W wyniku uruchomienia pokazanego powyżej fragment kodu w nowym oknie (uruchamiane za pomocą klawiszy skrótów CTRL+N lub opcji New Query w głównym pasku narzędzi) otrzymamy następujący wynik:
Interesująca informacja jest zapisana w kolumnie run_value – wartość 1 oznacza, iż kompresja kopii zapasowej jest włączona na poziomie instancji serwera baz danych.
5. Zmiany ustawień na poziomie instancji możemy dokonać z pomocą dwóch skryptów T-SQL:
a. Włączenie kompresji
USE master GO EXEC sp_configure 'backup compression default',1 GO RECONFIGURE GO
b. Wyłączenie kompresji
USE master GO EXEC sp_configure 'backup compression default',0 GO RECONFIGURE GO
6. Zmiany ustawień kompresji kopii zapasowej dla danej bazy danych można dokonać w oknie zadania kopii zapasowej (Database Backup), które jest dostępne po zaznaczeniu bazy danych oraz wybraniu z menu kontekstowego opcji Tasks/Backup:
7. W oknie własności należy wybrać stronę opcji (Options) oraz wybrać metodę kompresji plików bazy danych:
Zadanie 2
1. W pierwszym kroku należy wykonać kopię zapasową bazy danych AdventureWorks – bez kompresji. Kod T-SQL, który pomaga wykonać to zadanie, ma postać:
BACKUP DATABASE AdventureWorks TO DISK = N'C:\Temp\AdventureWorks.bak' WITH NO_COMPRESSION GO
Pamiętajmy, iż włączyliśmy w zadaniu nr 1 kompresję dla wszystkich kopii baz danych, dlatego należy użyć frazy NO_COMPRESSION, aby mieć pewność, że kopia zostanie wykonana bez kompresji.
W wyniku uruchomienia powyższego kodu otrzymujemy następującą informację:
Processed 21904 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1. Processed 3 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1. BACKUP DATABASE successfully processed 21907 pages in 31.282 seconds (5.471 MB/sec).
Wykonanie nieskompresowanej kopi zapasowej przykładowej bazy danych AdventureWorks zajęło nieco ponad 31 sekund, a rozmiar pliku kopii zapasowej to 176MB.
2. W drugim kroku należy powtórzyć operację z punktu 1, ale kopia zapasowa baza danych ma zostać skompresowana:
BACKUP DATABASE AdventureWorks TO DISK = N'C:\Temp\AdventureWorks.bak' WITH COMPRESSION GO
W wyniku uruchomienia powyższego kodu otrzymujemy następującą informację:
Processed 21904 pages for database 'AdventureWorks', file 'AdventureWorks_Data' on file 1. Processed 2 pages for database 'AdventureWorks', file 'AdventureWorks_Log' on file 1. BACKUP DATABASE successfully processed 21906 pages in 18.549 seconds (9.226 MB/sec).
Wykonanie skompresowanej kopi zapasowej przykładowej bazy danych AdventureWorks zajęło 18.5 sekunda, a rozmiar pliku kopii zapasowej to 40MB.
3. W celu wyznaczenia współczynnika kompresji kopii zapasowej należy wykonać poniższe zapytanie, które dodatkowo wyświetla informacje o rozmiarach kopii zapasowej skompresowanej oraz bez kompresji. W przypadku, gdy obydwie te wartości są równe to oznacza, iż kopia zapasowa bazy danych wykonała się bez kompresji:
SELECT backup_size/compressed_backup_size AS [wsp_kompresji] ,backup_size AS [rozmiar pliku bez kompresji - MB] ,compressed_backup_size AS [rozmiar pliku skompresowanego - MB] FROM msdb..backupset WHERE database_name='AdventureWorks'
Po uruchomieniu kodu otrzymano następujące wyniku – zbieżne z wynikami z punktów 1 i 2 zadania nr 2:
4. Podsumowując punkty 1-3 z zadania nr 2 można zauważyć, że współczynnik kompresji wyniósł ponad 4.3 co oznacza, że zaoszczędzono ponad 75% miejsca na dysku. Dodatkowo czas potrzebny do wykonania kopii zapasowej skrócił się z 31s do 18s.
Kompresja kopii zapasowej – referencje
Dodatkowe informacje na temat kompresji kopii zapasowej można znaleźć w Internecie:
[1] Backup Compression in SQL Server 2008 Enterprise Edition - Books Online
[2] Use Resource Governor to Limit CPU Usage by Backup Compression - przykład rozwiązania
Quiz
| • |
 | Damian Widera, Project Manager & Team Lead (MCT, MCITP – DBA, MCSD.NET) |