Microsoft SQL Server 2008

SQL Pytania i Odpowiedzi: Kompresja kopii zapasowych, przekierowywanie klientów korzystających z funkcjonalności dublowania baz danych i inne Udostępnij na: Facebook

Przez Paul S. Randal

Opublikowano: 4 września 2008 | Zaktualizowano: 4 września 2008

Pyt. Kompresja kopii zapasowej  Pyt. Kompresja kopii zapasowej
Pyt. Przełączanie awaryjne i zrywanie połączeń aplikacji  Pyt. Przełączanie awaryjne i zrywanie połączeń aplikacji
Pyt. Blokada całej partycjonowanej tabeli przy równoczesnych zapytaniach  Pyt. Blokada całej partycjonowanej tabeli przy równoczesnych zapytaniach
Pyt. Problemy z dyskiem a przebudowa uszkodzonego dziennika transakcji  Pyt. Problemy z dyskiem a przebudowa uszkodzonego dziennika transakcji

 

 

Pyt. Kompresja kopii zapasowej

  Zamierzamy przeprowadzić aktualizację większości z naszych serwerów do wersji SQL Server 2008, a jedną z oczekiwanych przeze mnie nowych funkcjonalności, którą zamierzam wykorzystać w środowisku produkcyjnym, jest kompresja kopii zapasowych. Wiem, że funkcję tę można domyślnie włączyć dla wszystkich baz danych na wszystkich serwerach, ale słyszałem również głosy ostrzegające, aby tego nie robić. Nie bardzo rozumiem, dlaczego nie miałbym domyślnie włączyć tej funkcji, ponieważ wydaje mi się, że nie mam nic do stracenia. Czy mógłby Pan podać uzasadnienie dla ostrzeżeń, z którymi się spotkałem?

Odp.

W tym przypadku właściwą odpowiedzią jest niezmiennie moja ulubiona odpowiedź: to zależy! Zanim przejdę do wyjaśnienia, podam kilka podstawowych informacji.

Kluczowym czynnikiem, który należy wziąć pod uwagę, jest współczynnik kompresji, jaki uzyskamy dla każdej kopii zapasowej po włączeniu funkcjonalności kompresowania kopii zapasowych. W przypadku każdego algorytmu kompresji osiągany współczynnik kompresji uzależniony jest od charakteru kompresowanych danych.

Przypadkowe dane (np. niewielkie liczby całkowite) nie poddają się zbyt dobrze kompresji, a więc maksymalny możliwy do osiągnięcia współczynnik kompresji zależeć będzie głównie od zawartości znajdujących się w bazie danych tabel i indeksów.

Poniżej podano kilka przykładów, w których kompresja kopii zapasowych może nie zapewniać wysokiego współczynnika kompresji:

  • Jeśli w bazie danych włączona została opcja przezroczystego szyfrowania danych, to współczynnik kompresji będzie bardzo niski, ponieważ kompresowane dane będą składać się z przypadkowych, niewielkich wartości.
  • Jeśli większość danych przechowywanych w bazie danych będzie skompresowana na poziomie kolumny, to współczynnik kompresji również będzie niski, ponieważ szyfrowanie kolumn nadaje danym charakter przypadkowy.
  • Jeśli kompresja danych została włączona dla większości znajdujących się w bazie danych tabel, to również w tym przypadku współczynnik kompresji będzie niski; kompresowanie danych, które przeważnie są już skompresowane, zwykle przynosi niewielki efekt.

W przypadku niskiego współczynnika kompresji problemem nie jest to, że współczynnik ten jest niski, ale fakt, że zasoby procesora są wykorzystywane do wykonywania algorytmu kompresji, który nie daje żadnych korzyści. Niezależnie od tego, jak bardzo można skompresować porcję danych, zasoby procesora będą zawsze wykorzystywane do wykonywania algorytmów kompresji i dekompresji.

Oznacza to, że zanim zdecydujemy się włączyć na stałe kompresję kopii zapasowej dla określonej bazy danych, należy sprawdzić, jak dobrze będzie kompresować się kopia tej bazy danych. W przeciwnym razie możemy po prostu marnować zasoby procesora. Fakt ten stanowi podstawę ostrzeżeń, z którym się Pan zetknął.

Podsumowując, jeśli włączenie kompresji kopii zapasowych będzie korzystne dla większości baz danych, to sensowne jest włączenie tej funkcji na poziomie całego serwera i jej ręczne wyłączenie dla nielicznych zadań tworzenia kopii zapasowej, za pomocą opcji WITH_NO_COMPRESSION. Alternatywnie, jeśli większość baz danych nie odniesie korzyści z włączenia kompresji kopii zapasowych, lepiej będzie pozostawić kompresję wyłączoną na poziomie serwera i ręcznie włączać ją dla wybranych zadań tworzenia kopii zapasowych, stosując opcję WITH COMPRESSION.



 

Pyt. Przełączanie awaryjne i zrywanie połączeń aplikacji

  W zeszłym roku przeprowadziliśmy aktualizację naszych baz danych, wprowadzając funkcję dublowania (ang. mirroring), aby w razie wystąpienia awarii możliwe było przełączenie się na kopię lustrzaną i kontynuowanie działania aplikacji. Podczas projektowania naszego systemu wypróbowywaliśmy w praktyce awaryjne przełączanie bazy danych i wszystko działało prawidłowo. W zeszłym tygodniu mieliśmy jednak prawdziwą awarię i nastąpiło awaryjne przełączenie bazy danych, ale wszystkie transakcje aplikacji zostały zatrzymane, a aplikacje nie przełączyły się na serwer zapasowy. Jak mogę skonfigurować na przyszłość serwer SQL Server, aby w trakcie przełączania awaryjnego nie zrywał on połączeń aplikacji i możliwe było dokończenie transakcji?

Odp.

Pozwolę sobie rozbić ten problem na dwie części - jak aplikacje mogą poradzić sobie z procesem przełączania awaryjnego oraz jak realizować przekierowania klientów w przypadku dublowania bazy danych.

W trakcie trwania procesu przełączania awaryjnego korzystającego z dowolnej technologii wysokiej dostępności, oferowanej przez serwer SQL Server, połączenie klienta z uszkodzonym serwerem zostanie zerwane i wszelkie trwające w tym czasie transakcje zostaną utracone. Nie ma możliwości przenoszenia pomiędzy serwerami trwających transakcji (zarówno w trakcie procesu przełączania awaryjnego, jak i w każdej innej sytuacji). W zależności od używanej technologii wysokiej dostępności transakcje wykonywane w chwili awarii albo w ogóle nie będą dostępne na serwerze awaryjnym, albo będą istniały jako transakcje w trakcie wykonywania, lecz zostaną one wycofane przez proces przywracający bazę danych do trybu online na serwerze awaryjnym.

W przypadku dublowania bazy danych, które polega na ciągłym ekspediowaniu wpisów dziennika transakcji z serwera podstawowego na serwer lustrzany, zwykle będziemy mieć do czynienia z drugim z tych przypadków - wszelkie transakcje realizowane w chwili wystąpienia awarii zostaną wycofane podczas przywracania bazy danych do trybu online na serwerze lustrzanym, który stanie się nowym serwerem podstawowym.

Tak więc istnieją dwie rzeczy, które aplikacja musi umieć zrobić, aby zachować płynność działania podczas pracy z serwerem, którego funkcje mogą zostać awaryjnie przełączone na inny serwer:

  1. Aplikacja musi potrafić w płynny sposób obsłużyć zerwanie połączenia z serwerem, a następnie odnowić je po upływie niewielkiej ilości czasu.
  2. Aplikacja musi potrafić w płynny sposób obsłużyć przerwanie transakcji, a następnie ponowić ją po ponownym nawiązaniu połączenia z serwerem zapasowym (być może wykorzystując do tego menedżera transakcji z warstwy pośredniej).

W tym przypadku jedyną technologią wysokiej dostępności, która nie wymaga żadnych zmian po stronie klienta, aby umożliwić przekierowanie połączenia tego klienta po wykonaniu przełączania awaryjnego, jest technologia klastra awaryjnego (ang. failover clustering). Klienci łączą się z serwerem używając nazwy serwera wirtualnego i są w sposób przezroczysty przekierowywani do tego fizycznego węzła klastra, który akurat jest aktywny.

W przypadku takich technologii wysokiej dostępności, jak ekspediowanie dzienników i replikacja, nazwa serwera awaryjnego jest inna, co oznacza, że po wykonaniu przełączenia awaryjnego konieczne będzie ręczne przekierowanie połączeń klientów. Ręczne przekierowanie można zrealizować na kilka sposobów:

  • Nazwa serwera awaryjnego może zostać na stałe wpisana do aplikacji klienta, dzięki czemu próby odnowienia połączenia będą mogły być kierowane do serwera awaryjnego.
  • Możliwe jest użycie funkcji równoważenia obciążenia sieciowego (Network Load Balancing) z konfiguracją 100/0—0/100, która umożliwi przełączenie połączeń do serwera awaryjnego.
  • Możliwe jest wpisanie do systemu DNS aliasu serwera lub przełączanie wpisów w tabeli DNS.

W przypadku dublowania baz danych, każda z wymienionych powyżej możliwości będzie działać. Jednak funkcjonalność dublowania baz danych posiada także wbudowane możliwości przekierowywania klientów. W konfigurowanym na kliencie łańcuchu znakowym opisującym połączenie można jawnie określić nazwę serwera lustrzanego i, jeśli klient nie będzie mógł skontaktować się z serwerem głównym, podejmie automatycznie próbę połączenia się z serwerem lustrzanym. Proces ten nosi nazwę jawnego przekierowania.

Jeśli nie można zmienić skonfigurowanego na kliencie łańcucha znakowego z opisem połączenia, to w sytuacji, gdy uszkodzony serwer jest ponownie dostępny jako serwer lustrzany, możliwe jest niejawne przekierowywanie klientów. Wszelkie połączenia do tego serwera zostaną przekierowane do nowego serwera głównego, ale mechanizm ten działa tylko wtedy, gdy nowy serwer lustrzany działa i jest dostępny.

Opcje te zostały wyjaśnione bardziej szczegółowo w artykule z serii „white paper”, poświęconym serwerowi SQL Server 2005 i zatytułowanym „Implementing Application Failover with Database Mirroring” (Implementacja awaryjnego przełączania aplikacji przy użyciu funkcji dublowania bazy danych).


 

Pyt. Blokada całej partycjonowanej tabeli przy równoczesnych zapytaniach

 

Po przeprowadzeniu aktualizacji do wersji SQL Server 2005, zaprojektowaliśmy od nowa nasze największe tabele dzieląc je na partycje, aby umożliwić korzystanie z zalet procesu utrzymywania partycjonowanych tabel oraz z mechanizmu przesuwającego się okna. Korzyści te zostały opisane w Pańskim artykule z sierpnia 2008 („Partitioning, Consistency Checks, and More” – „Partycjonowanie tabel, sprawdzanie spójności i inne problemy”). Napotkaliśmy jednak pewien problem. Czasami zdarza się, że działające równocześnie zapytania aplikacji napotykają na blokadę całej tabeli, mimo że zapytania te nawet nie odczytują tej samej partycji. Słyszałem, że problem ten został rozwiązany w wersji SQL Server 2008. Czy mógłby Pan wyjaśnić, w jaki sposób możemy zapobiec tym blokadom?


Odp.

Obserwowany przez Państwa problem powodowany jest przez mechanizm noszący nazwę eskalacji blokady. Server SQL Server żąda nałożenia blokady danych, aby chronić je podczas ich odczytywania lub zapisywania przez wykonywane zapytanie. Serwer może żądać nałożenia blokady na całą tabelę, na wybrane strony z danymi albo na pojedyncze wiersze tabeli lub indeksu, a każda blokada zajmuje niewielką ilość pamięci.

Jeśli wykonywane zapytanie powoduje żądanie założenia zbyt dużej liczby blokad, to serwer SQL Server może zdecydować się na zastąpienie wszystkich blokad pojedynczych wierszy lub stron tabeli jedną blokadą całej tabeli (wartością progową powodującą wystąpienie takiej sytuacji jest około 5000 blokad, ale dokładny algorytm jest skomplikowany i może być konfigurowany). Proces ten nosi nazwę eskalacji blokad.

W wersji SQL Server 2005, jeśli zapytanie A operować będzie na pojedynczej partycji tabeli i spowoduje nałożenie dostatecznie dużej liczby blokad, aby spowodować eskalację blokady, to zablokowana zostanie cała tabela. Taka blokada może uniemożliwić zapytaniu B korzystanie z innej partycji tej samej tabeli. Tak więc zapytanie B pozostanie zablokowane, aż do chwili, gdy zapytanie A zakończy swoje działanie i zwolni blokadę.

W wersji SQL Server 2008 mechanizm eskalacji blokad został udoskonalony, umożliwiając eskalowanie blokad tabeli do poziomu blokady partycji. W przypadku opisywanego wcześniej przykładu oznacza to, że eskalacja blokady spowodowana przez zapytanie A zablokuje tylko jedną partycję używaną przez to zapytanie, a nie całą tabelę.

Zapytanie B będzie wówczas mogło swobodnie operować na innej partycji tej samej tabeli i nie będzie blokowane. Zapytane B może nawet samo stać się źródłem eskalacji blokad, która doprowadzi do zablokowania tylko tej partycji, z której korzysta zapytanie B, a nie całej tabeli.

Ten model eskalacji blokad można ustawić za pomocą następującego polecenia:

ALTER TABLE MyTable SET (LOCK_ESCALATION = AUTO);
GO

Polecenie to instruuje menedżera blokad serwera SQL Server, aby w przypadku tabel podzielonych na partycje używać eskalacji blokad do poziomu partycji, a dla tabel niepodzielonych na partycje używać zwykłej eskalacji blokad do poziomu tabeli. Domyślnie stosowana jest eskalacja blokad do poziomu tabeli. Konfigurując tę opcję należy jednak zachować ostrożność, gdyż w zależności od wykonywanych zapytań może ona prowadzić do powstawania zastojów.

Np. jeśli zarówno zapytanie A, jak i zapytanie B, powodują eskalację blokad na różnych partycjach tabeli, a następnie każde z tych zapytań spróbuje skorzystać z partycji zablokowanej przez drugie zapytanie, to jedno z tych zapytań zostanie przerwane przez proces monitora zastojów.


Rysunek 1: Sprawdzanie blokad nałożonych na partycjonowaną tabelę.

Na rysunku 1 pokazany został przykład rezultatów zwracanych przez zapytanie skierowane do widoku katalogu systemowego sys.partitions (pierwszy zbiór wynikowy) oraz przez zapytanie skierowane do dynamicznego widoku zarządzającego sys.dm_os_locks (drugi zbiór wynikowy). Rezultaty te pozwalają na zapoznanie się z blokadami nałożonymi przez zapytania na partycjonowanej tabeli dla przypadku eskalacji blokad do poziomu partycji. W tym przypadku istnieją dwie wyłączne blokady nałożone na poziomie partycji (widoczne w wynikach jako blokady typu HOBT), ale blokady tabeli (widoczne w wynikach jako blokady typu OBJECT) nie są blokadami wyłącznymi, a więc z partycji tych może korzystać wiele zapytań, nawet jeśli doszło już do eskalacji blokad. Należy zauważyć, że identyfikatory zasobów dla tych dwóch blokad partycji są takie same jak identyfikatory pierwszych dwóch partycji tabeli, które są widoczne w wynikach pierwszego zapytania skierowanego do widoku sys.partitions.

Na początku tego roku zamieściłem na swoim blogu skrypt demonstrujący sposób działania eskalacji blokad do poziomu partycji oraz potencjalną możliwość wystąpienia zastojów. Wszystkie aspekty związane z działaniem blokad w wersji SQL Server 2008 zostały opisane w dokumentacji SQL Server 2008 Books Online, w temacie zatytułowanym „Locking in the Database Engine” (Blokady nakładane przez motor bazy danych).


 

Pyt. Problemy z dyskiem a przebudowa uszkodzonego dziennika transakcji

  Na jednym z naszych serwerów wystąpiły pewne problemy z dyskiem, na którym przechowywany jest dziennik transakcji bazy danych, przez co baza ta stała się podejrzana. Ostatnia pełna kopia zapasowa pochodzi sprzed pięciu tygodni i odtworzenie wraz z nią wszystkich kopii zapasowych dziennika transakcji trwałoby zbyt długo. Ponieważ problem z dyskiem wystąpił poza normalnymi godzinami pracy przedsiębiorstwa, więc, aby uniknąć przestojów, przeprowadziliśmy przebudowę uszkodzonego dziennika transakcji. W pewnych okolicznościach takie działanie może jednak powodować problemy. Ponieważ podczas procesu przebudowy żaden proces nie korzystał z tej bazy danych, więc myślę, że jesteśmy bezpieczni. Czy dobrze postąpiliśmy?

Odp.

Prosta odpowiedź jest taka, że jedynym przypadkiem, w którym rozważałbym wykonanie przebudowy dziennika transakcji, jest brak możliwości odtworzenia danych z kopii zapasowej. Wprawdzie mają Państwo świadomość niebezpieczeństwa związanego z przebudową dziennika transakcji (tym z Czytelników, którzy nie wiedzą, dlaczego jest to niebezpieczne, polecam zapoznanie się z zamieszczonym na moim blogu artykułem zatytułowanym „Last resorts that people try first...” – „Ostateczna możliwość, od której wszyscy zaczynają ...” ), ale fakt, że baza danych stała się podejrzana, oznacza, że wystąpił błąd podczas odtwarzania - albo podczas odtwarzania po awarii typu crash, albo podczas wycofywania transakcji. Oznacza to realną możliwość, że dane zapisane w bazie danych są uszkodzone.

Wprawdzie opisywany problem wystąpił podczas okresu bezczynności, ale czy uwzględnili Państwo zaplanowane zadania lub zadania wykonywane w tle? Być może w chwili uszkodzenia dziennika działało jakieś zadanie administracyjne, wykonujące operację przebudowy lub reorganizacji indeksu grupującego. Zadania wykonywane w tle mogły natomiast przeprowadzać oczyszczanie stron sterty lub indeksu grupującego z tzw. zapisów widmowych (ang. ghost cleanup). W każdym z tych przypadków mogło dojść do wykonywania zmian w strukturach indeksu grupującego, które, jeśli nie zostały poprawnie wycofane, mogą doprowadzić do uszkodzenia bazy danych i możliwości utraty danych.

Ostateczna konkluzja jest taka, że ze względu na olbrzymią potencjalną możliwość powiększenia uszkodzeń i doprowadzenia do utraty danych we wszystkich scenariuszach odtwarzania po awarii operacja przebudowy dziennika transakcji powinna zawsze być brana pod uwagę jedynie jako absolutna ostateczność. Po wykonaniu tej operacji należy przynajmniej przeprowadzić pełne skanowanie bazy danych poleceniem DBCC CHECKDB, aby sprawdzić, czy istnieją w niej jakieś uszkodzenia.

Na przyszłość powinni Państwo zmienić swoją strategię sporządzania kopii zapasowych tak, by czas trwania operacji odtwarzania mieścił się w akceptowalnych granicach i by nie było potrzeby sięgania po tak drastyczne środki jak przebudowa dziennika transakcji. Opis kroków związanych z opracowaniem właściwej strategii archiwizacji wykraczałby poza ramy tego działu, ale planuję jeszcze w tym roku poświęcić temu zagadnieniu osobny, pełnowymiarowy artykuł. A więc zachęcam do śledzenia zawartości tego działu!

 

 Do początku strony Do początku strony


Więcej informacji

Paul S. Randal jest dyrektorem naczelnym firmy SQLskills.com i posiadaczem tytułu SQL Server MVP. W latach 1999-2007 pracował w firmie Microsoft w zespole zajmującym się silnikiem magazynowania danych serwera SQL Server. Jest autorem programu DBCC CHECKDB/repair dla wersji SQL Server 2005 i podczas prac nad wersją SQL Server 2008 był odpowiedzialny za główny silnik magazynowania danych (Core Storage Engine). Paul jest ekspertem w dziedzinie odtwarzania po awarii, wysokiej dostępności i utrzymywania bazy danych, a także regularnie występuje jako prezenter na różnych konferencjach odbywających się na całym świecie. Prowadzi również swój blog pod adresem SQLskills.com/blogs/paul.


Microsoft SQL Server 2008