Najważniejsze wskazówki dotyczące efektywnej konserwacji bazy danych

Autor: Paul S. Randal

Opublikowano: 3 grudnia 2008

Zawartość strony

	Zarządzanie danymi i plikami dzienników
	Fragmentacja indeksu
	Statystyka
	Wykrywanie uszkodzeń
	Kopie zapasowe
	Podsumowanie

Kilka razy w tygodniu jestem proszony o radę, jak efektywnie konserwować eksploatacyjną bazę danych. Czasami pytanie pochodzi od administratorów baz danych, którzy implementują nowe rozwiązania i chcą tak przystosować praktyki konserwacji, aby pasowały do charakterystyk ich nowych baz danych. Częściej jednak te pytania pochodzą od osób, które nie są zawodowymi administratorami, lecz z tego lub innego powodu stały się właścicielami baz danych i są za nie odpowiedzialne. Nazwijmy tę rolę „mimowolny administrator bazy danych". Celem tego artykułu jest dostarczenie elementarza najlepszych praktyk konserwacji bazy danych dla wszystkich mimowolnych administratorów baz danych.

Tak jak dla większości zadań i procedur w świecie IT, nie istnieje łatwe, uniwersalne rozwiązanie dotyczące efektywnej konserwacji bazy danych, jest jednak kilka kluczowych obszarów, którymi prawie zawsze trzeba się zająć. Oto moich pięć najważniejszych obszarów zainteresowań (kolejność nie ma tu znaczenia):

• Zarządzanie danymi i plikami dzienników
• Fragmentacja indeksu
• Statystyka
• Wykrywanie uszkodzeń
• Kopie zapasowe

Niekonserwowana (lub słabo konserwowana) baza danych może spowodować problemy w jednym lub więcej z tych obszarów, co może ostatecznie prowadzić do słabej wydajności aplikacji lub nawet do przestoju i utraty danych.

W tym artykule wyjaśniam, dlaczego te kwestie mają znaczenie i pokazuję proste metody złagodzenia tych problemów. Wyjaśnienia są oparte na SQL Server­® 2005, lecz zwracam także uwagę na główne różnice, na jakie można napotkać w Server 2000 oraz SQL Server 2008.

Zarządzanie danymi i plikami dzienników

Pierwszy obszar, który zawsze zalecam do sprawdzenia przy przejmowaniu bazy danych, dotyczy ustawień związanych z zarządzaniem plikami danych i dzienników (transakcji). W szczególności trzeba się upewnić, czy:

• Pliki danych i dziennika są oddzielone od siebie, a także odizolowane od całej reszty
• Automatyczne powiększanie jest prawidłowo skonfigurowane
• Skonfigurowana jest natychmiastowa inicjalizacja pliku
• Automatyczne zmniejszenie nie jest uaktywnione, a zmniejszanie nie stanowi części żadnego planu konserwacji

Kiedy pliki danych i dziennika (najlepiej byłoby, gdyby były na całkiem oddzielnych woluminach) współużytkują wolumin z jakąkolwiek inną aplikacją, która tworzy lub rozszerza pliki, istnieje możliwość fragmentacji. W plikach danych nadmierna fragmentacja pliku może w pewnym stopniu przyczyniać się do słabej wydajności zapytań (szczególnie tych, które przeszukują bardzo duże ilości danych). W plikach dzienników może to mieć bardziej znaczący wpływ na wydajność, szczególnie gdy automatyczne powiększanie jest ustawione na zwiększanie rozmiaru każdego pliku tylko o bardzo mały rozmiar, za każdym razem, gdy jest to konieczne.

Pliki dzienników są podzielone wewnętrznie na sekcje nazywane wirtualnymi plikami dziennika (Virtual Log Files, VLF) i im bardziej plik dziennika jest pofragmentowany (używam tutaj liczby pojedynczej, ponieważ nic nie zyskamy na posiadaniu wielu plików dziennika – powinien być tylko jeden dziennik na bazę danych), tym więcej jest plików VLF. Kiedy plik dziennika ma więcej niż, powiedzmy, 200 plików VLF, wydajność może mieć negatywny wpływ na operacje związane z dziennikami, takie jak odczyt dzienników (na przykład dla replikacji/wycofania transakcji), kopie zapasowe dzienników, a nawet wyzwalacze w SQL Server 2000 (implementacja wyzwalaczy została zmieniona w SQL Server 2005 na strukturę wersjonowania wierszy zamiast dziennika transakcji).

Najlepszą praktyką związaną z wymiarowaniem plików danych i dzienników jest tworzenie ich z odpowiednim rozmiarem początkowym. Rozmiar początkowy dla plików danych powinien uwzględniać możliwość dodawania danych do bazy w krótkim przedziale czasu. Na przykład, jeśli początkowy rozmiar danych to 50 GB, lecz wiemy, że w ciągu następnych sześciu miesięcy zostanie dodane dodatkowe 50 GB danych, dobrze jest utworzyć od razu plik 100 GB, zamiast kilkukrotnie powiększać go w celu osiągnięcia tego rozmiaru.

Niestety dla plików dzienników jest to nieco bardziej skomplikowane i trzeba wziąć po uwagę takie czynniki, jak rozmiar transakcji (długotrwałe transakcje nie mogą być usuwane z dziennika, zanim nie zostaną zakończone) oraz częstotliwość tworzenia kopii zapasowych (ponieważ powoduje usunięcie nieaktywnej części dziennika). Więcej informacji można znaleźć we wpisie „8 Steps to Better Transaction Log Throughput”, w popularnym blogu pisanym przez moją żonę, Kimberly Tripp, na stronie SQLskills.com.

Po ich ustawieniu, rozmiary plików powinny być monitorowane o różnych porach i aktywnie powiększane ręcznie w odpowiednich godzinach. Automatyczne powiększanie trzeba zostawić jako ochronę na wszelki wypadek, aby w razie potrzeby pliki mogły nadal rosnąć, jeśli zdarzy się jakiś nietypowy przypadek. Argument przeciwko całkowitemu pozostawieniu zarządzania automatycznemu powiększaniu jest taki, że prowadzi ono do fragmentacji pliku, oraz jest to proces czasochłonny, który powoduje zastój obciążenia aplikacji na nieprzewidywalny czas.

Rozmiar automatycznego powiększania powinien być ustawiony na określoną wartość, zamiast w procentach, aby ograniczyć czas i miejsce potrzebne do jego wykonania. Na przykład można tak ustawić plik danych 100 GB, aby rozmiar automatycznego powiększania był równy 5 GB, zamiast, powiedzmy, 10 procent. Oznacza to, że plik zawsze będzie zwiększany o 5 GB, niezależnie od tego, jak duży będzie ostatecznie, zamiast stale rosnącego rozmiaru (10 GB, 11 GB, 12 GB itd.) za każdym razem, gdy plik staje się większy.

Kiedy dziennik transakcji jest powiększany (albo ręcznie, albo poprzez automatyczne powiększanie), jest zawsze inicjowany od zera. Pliki danych mają takie same domyślne zachowanie w SQL Server 2000, lecz w SQL Server 2005 można włączyć natychmiastową inicjalizację plików, która pomija ich inicjowanie od zera i w ten sposób operacje powiększania i automatycznego powiększania stają się praktycznie natychmiastowe. Wbrew powszechnemu przekonaniu, ta funkcja jest dostępna we wszystkich wydaniach SQL Servera. Aby uzyskać więcej informacji, trzeba wejść do hasła „instant file initialization” w indeksie Books Online dla SQL Server 2005 lub SQL Server 2008.

Na koniec trzeba pamiętać o tym, aby zmniejszanie nie było w żaden sposób możliwe. Może być ono stosowane do zmniejszenia rozmiaru plików danych lub dzienników, lecz jest bardzo inwazyjnym, obciążającym zasoby procesem, który powoduje znaczną fragmentację logiczną w plikach danych (szczegóły poniżej) i prowadzi do słabej wydajności. W SQL Server 2005 Books Online zmieniłem wpis dotyczący zmniejszania, aby włączyć ostrzeżenie na temat jego skutków. Ręczne zmniejszanie poszczególnych plików danych lub dzienników może być jednak do zaakceptowania w szczególnych okolicznościach.

Automatyczne zmniejszanie jest najgorszym winowajcą, ponieważ jest uruchamiane w tle co 30 minut i próbuje zmniejszyć bazę danych tam, gdzie opcja automatycznego zmniejszania jest ustawiona na Prawda. Jest to proces nieco nieprzewidywalny, ponieważ zmniejsza tylko bazy danych, które mają ponad 25 procent wolnej przestrzeni. Automatyczne zmniejszanie wykorzystuje dużo zasobów i powoduje obniżającą wydajność fragmentację, nie jest więc w żadnym wypadku dobrym planem. Automatyczne zmniejszanie można zawsze wyłączyć za pomocą polecenia:

ALTER DATABASE MyDatabase SET AUTO_SHRINK OFF;

Plan regularnej konserwacji, który zawiera polecenie ręcznego zmniejszania bazy danych, jest równie zły. Jeśli stwierdzimy, że nasza baza danych wciąż rośnie po tym, jak plan konserwacji ją zmniejszył, to przyczyna tego jest taka, że baza danych potrzebuje przestrzeni, w której ma działać.

Najlepszą rzeczą, jaką można zrobić, jest pozwolenie na wzrost bazy do rozmiaru stacjonarnego i całkowite unikanie jej zmniejszania. Więcej informacji na temat minusów użycia zmniejszania oraz kilka komentarzy na temat nowych algorytmów w SQL Server 2005 można znaleźć w moim starym blogu MSDN® na stronie blogs.msdn.com/sqlserverstorageengine/archive/tags/Shrink/default.aspx.

 Do początku strony

Fragmentacja indeksu

Poza fragmentacją na poziomie systemu plików i w pliku dziennika, możliwa jest także fragmentacja w plikach danych, w strukturach, które przechowują tabelę oraz dane indeksu. Są dwa podstawowe typy fragmentacji, które mogą pojawić się w pliku danych:

• Fragmentacja w zakresie poszczególnych danych i stron indeksu (czasami nazwana fragmentacją wewnętrzną)
• Fragmentacja w zakresie składających się ze stron struktur indeksu lub tabeli (zwana fragmentacją logiczną – logical scan fragmentation oraz fragmentacją obszaru – extent scan fragmentation)

Fragmentacja wewnętrzna ma miejsce tam, gdzie jest dużo pustej przestrzeni na stronie. Jak widać na rysunku 1, każda strona w bazie danych ma rozmiar 8 KB i ma 96-bajtowy nagłówek strony; w rezultacie strona może przechowywać mniej więcej 8096 bajtów danych tabeli lub indeksu (konkretne informacje dotyczące tabeli i indeksu dla struktur danych i wierszy można znaleźć w moim blogu na stronie sqlskills.com/blogs/paul, w kategorii Inside The Storage Engine). Puste miejsce może się pojawić, jeśli każdy rekord tabeli lub indeksu stanowi więcej niż połowę rozmiaru strony, gdyż na jednej stronie wtedy może być przechowywany tylko pojedynczy rekord. To może być bardzo trudne lub niemożliwe do poprawienia, ponieważ wymagałoby to zmiany schematu tabeli lub indeksu, na przykład poprzez zmianę klucza indeksu na taki, który nie powoduje powstawania przypadkowych punktów wstawiania, tak jak w przypadku GUID.

Rysunek 1: Struktura strony bazy danych.

Fragmentacja wewnętrzna jest częściej rezultatem modyfikacji danych, takich jak wstawienia, aktualizacje i usunięcia, które mogą pozostawić wolne miejsce na stronie. Źle zarządzany współczynnik wypełnienia może również przyczyniać się do fragmentacji; więcej szczegółów na ten temat można znaleźć w Books Online. W zależności od schematu tabeli/indeksu oraz charakterystyki aplikacji, ta wolna przestrzeń może nigdy nie być ponownie użyta po jej powstaniu, co może prowadzić do stale rosnącej ilości niewykorzystanego miejsca w bazie danych.

Rozważmy na przykład tabelę ze 100 milionami wierszy o średnim rozmiarze rekordu równym 400 bajtów. Przez cały czas wzorzec modyfikacji danych aplikacji pozostawia każdą stronę ze średnio 2800 bajtami wolnego miejsca. Całkowita przestrzeń wymagana przez tabelę to około 59 GB, obliczone jako 8096-2800 / 400 = 13 rekordów na każdą 8-kilobajtową stronę, a następnie dzieląc 100 milionów przez 13, aby uzyskać liczbę stron. Gdyby miejsce nie zostało zmarnowane, wtedy na stronie zmieściłoby się 20 rekordów, co by zmniejszyło całkowitą wymaganą przestrzeń do 38 GB. To ogromna oszczędność!

Zatem niewykorzystane miejsce na stronach danych/indeksu może prowadzić do konieczności trzymania takiej samej liczby danych na większej liczbie stron. Nie tylko zabiera to więcej miejsca na dysku, lecz oznacza także, że zapytanie musi wykonać więcej operacji we/wy, aby przeczytać taką samą ilość danych. Ponadto wszystkie te dodatkowe strony zajmują więcej miejsca w pamięci podręcznej danych, wykorzystując w ten sposób więcej pamięci serwera.

Fragmentacja logiczna (logical scan fragmentation) jest rezultatem operacji nazywanej podziałem strony. Ma ona miejsce, gdy rekord ma być wstawiony na określoną stronę indeksu (zgodnie z definicją klucza indeksu), lecz na stronie nie ma wystarczającego miejsca, aby umieścić wstawiane dane. Strona jest dzielona na pół i mniej więcej 50 procent rekordów jest przenoszonych do nowo przydzielonej strony. Ta nowa strona zwykle nie sąsiaduje fizycznie ze starą stroną i dlatego jest określana jako pofragmentowana. Fragmentacja obszaru (extent scan fragmentation) ma podobną koncepcję działania. Fragmentacja w strukturach tabeli/indeksu ma wpływ na możliwości SQL Servera do wykonywania skutecznego przeszukiwania w całej tabeli bądź indeksie albo ograniczonego przez klauzulę WHERE (taką jak SELECT * FROM MyTable WHERE Column1 > 100 AND Column1 < 4000).

Na rysunku 2 pokazano nowo utworzone strony indeksu ze 100-procentowym współczynnikiem wypełnienia i bez żadnej fragmentacji – strony są pełne, a ich kolejność fizyczna jest zgodna z kolejnością logiczną. Na r ysunku 3 pokazano fragmentację, która może się pojawić po losowych wstawieniach/aktualizacjach/usunięciach.

Rysunek 2: Nowo utworzone strony indeksu bez fragmentacji; strony wypełnione w 100%.

Rysunek 3: Strony indeksu pokazujące wewnętrzną i zewnętrzną fragmentację po swobodnych wstawieniach, aktualizacjach i usunięciach.

Fragmentacji można czasami zapobiec, zmieniając schemat tabeli/indeksu, lecz jak wspomniałem wcześniej, może to być trudne lub niemożliwe. Jeśli nie ma możliwości zapobiegania fragmentacji, są metody jej usuwania, gdy już się pojawi — w szczególności poprzez przebudowanie lub reorganizację indeksu.

Przebudowanie indeksu obejmuje tworzenie nowej kopii indeksu – dobrze upakowany i na tyle zwarty, jak to jest możliwe – a następnie usunięcie starego, pofragmentowanego. Ponieważ SQL Server tworzy nową kopię indeksu przed usunięciem starego, wymaga wolnego miejsca w plikach danych w przybliżeniu równego rozmiarowi indeksu. Przebudowywanie indeksu w SQL Server 2000 było zawsze operacją w trybie offline. Jednak, z kilkoma ograniczeniami, w SQL Server 2005 Enterprise Edition przebudowa indeksu może odbywać się w trybie online. Z drugiej strony, reorganizowanie wykorzystuje dostępny algorytm upakowania i defragmentacji indeksu; to wymaga tylko 8 KB dodatkowego miejsca do uruchomienia – i zawsze działa w trybie online. W rzeczywistości, w SQL Server 2000 specjalnie napisałem kod reorganizacji indeksu jako wydajną pod względem miejsca alternatywę przebudowania indeksu w trybie online.

Poleceniami do zbadania w serwerze SQL Server 2005 są ALTER INDEX … REBUILD – do przebudowy indeksów oraz ALTER INDEX … REORGANIZE – do ich reorganizacji. Ta składnia zastępuje odpowiednio polecenia SQL Server 2000: DBCC DBREINDEX oraz DBCC INDEXDEFRAG.

Istnieje wiele kompromisów między tymi metodami, takie jak liczba wygenerowanych zapisów transakcji, wymagana ilość wolnego miejsca w bazie danych oraz to, czy proces można przerwać bez utraty pracy. Dokument techniczny, który omawia te kompromisy, a także inne informacje, można znaleźć na stronie microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx. Dokument ten jest oparty na SQL Server 2000, lecz koncepcje te dobrze przekładają się na późniejsze wersje.

Niektóre osoby wybierają prostą metodę przebudowy lub reorganizacji wszystkich indeksów codziennie lub co tydzień (przy użyciu na przykład opcji planu konserwacji) zamiast szukania, które indeksy są pofragmentowane i zastanawiania się, czy usunięcie fragmentacji da jakieś korzyści. Chociaż może to być dobre rozwiązanie dla mimowolnych DBA, który tylko marzą, aby zrobić coś przy minimalnym wysiłku, może być bardzo zły wybór dla dużych baz danych lub systemów, gdzie zasoby są na wagę złota.

Bardziej złożone podejście obejmuje użycie DMV sys.dm_db_index_physical_stats (lub DBCC SHOWCONTIG w SQL Server 2000) do okresowego określania, które indeksy są pofragmentowane, a następnie wybranie, czy i jak na nich działać. W dokumencie technicznym omówione jest także zastosowanie bardziej ukierunkowanych wyborów. Ponadto można zobaczyć trochę przykładów kodu do wykonywania tego filtrowania w przykładzie D we wpisie Books Online dla DMV sys.dm_db_index_physical_stats w SQL Server 2005 (msdn.microsoft.com/library/ms188917) lub przykładzie E we wpisie Books Online dla DBCC SHOWCONTIG w SQL Server 2000 i wersjach późniejszych (msdn.microsoft.com/library/aa258803).

Niezależnie od użytej metody, wskazane jest regularne badanie i naprawianie fragmentacji.

 Do początku strony

Statystyka

Procesor zapytań (Query Processor) jest częścią SQL Servera, która decyduje, jak zapytanie powinno być wykonywane — w szczególności, które tabele i indeksy mają być użyte, oraz które operacje mają na nich być wykonane, aby uzyskać dane rezultaty; nosi to nazwę planu zapytania. Jednymi z najważniejszych czynników w procesie podejmowania decyzji są statystyki, które opisują wartości danych dla kolumn w tabeli lub indeksie. Aby statystyki były użyteczne dla procesora zapytań, muszą być oczywiście dokładne i aktualne, bo w innym przypadku można wybrać plany zapytań o słabej wydajności.

Statystyki są generowane poprzez czytanie danych tabeli/indeksu i określenie dystrybucji danych dla odpowiednich kolumn. Statystyki mogą być budowane poprzez skanowanie wszystkich wartości danych dla poszczególnych kolumn (pełny skan), lecz mogą także być oparte na określonym przez użytkownika procencie danych (skan próbkowany). Jeśli dystrybucja wartości w kolumnie jest dość płaska, wtedy próbkowany skan może być dość dobry i za jego pomocą można tworzyć i aktualizować statystyki szybciej niż za pomocą pełnego skanu.

Zwróćmy uwagę, że statystyki mogą być automatycznie tworzone i utrzymywane poprzez włączenie opcji bazy danych AUTO_CREATE_STATISTICS oraz AUTO_UPDATE_STATISTICS, jak pokazano na rysunku 4. Są one domyślnie włączone, lecz jeśli właśnie odziedziczyliśmy bazę danych, możemy to sprawdzić, aby się upewnić. Czasami statystyki mogą się stać nieaktualne i w takim przypadku można je ręcznie zaktualizować przy użyciu operacji UPDATE STATISTICS na określonych zestawach statystyk. Alternatywnie można użyć zapisanej procedury sp_updatestats, która aktualizuje wszystkie nieaktualne statystyki (w SQL Server 2000 sp_updatestats aktualizuje wszystkie statystyki, niezależnie od ich wieku).

Rysunek 4: Zmienianie ustawień bazy danych za pomocą SQL Server Management Studio.

Jeśli chcemy aktualizować statystyki w ramach naszego regularnego planu konserwacji, istnieje haczyk, którego trzeba być świadomym. Zarówno UPDATE STATISTICS, jak i sp_updatestats domyślnie stosują wcześniej określony poziom próbkowania (jeśli jakikolwiek) – i może być on mniejszy niż pełny skan. Indeks automatycznie przebudowuje aktualizację statystyk za pomocą pełnego skanu. Jeśli ręcznie zaktualizujemy statystyki po przebudowie indeksu, to możemy ostatecznie otrzymać mniej dokładne statystyki! To może się zdarzyć, jeśli próbkowany skan z ręcznej aktualizacji zastąpi pełny skan generowany przez przebudowę indeksu. Z drugiej strony, reorganizacja indeksu w ogóle nie aktualizuje statystyk.

Również w tym przypadku wiele osób stosuje plan konserwacji, który aktualizuje wszystkie statystyki w jakiejś chwili przed lub po przebudowaniu wszystkich indeksów — a więc nieświadomie pozostają ze statystykami, które mogą być mniej dokładne. Jeśli wybierzemy przebudowanie od czasu do czasu wszystkich indeksów, zadbamy również o statystyki. Jeśli wybierzemy bardziej skomplikowaną metodę usuwania fragmentacji, trzeba to również zrobić dla konserwacji statystyk. Oto moja propozycja:

• Analizujmy indeksy i określajmy, którymi mamy się zająć i jak dokonać usunięcia fragmentacji.
• Dla wszystkich indeksów, które nie zostały przebudowane, aktualizujmy statystyki.
• Aktualizujmy statystyki dla wszystkich nieindeksowanych kolumn.

Więcej informacji na temat statystyk można zobaczyć w dokumencie technicznym „Statistics Used by the Query Optimizer in Microsoft® SQL Server 2005” (microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx).

 Do początku strony

Wykrywanie uszkodzeń

Do tej pory omówiono konserwację związana z wydajnością. Teraz zajmiemy się wykrywaniem i minimalizowaniem uszkodzeń.

Jest bardzo mało prawdopodobne, że baza danych, którą zarządzamy, zawiera całkowicie bezużyteczne informacje, o które nikt nie dba – jak więc dopilnować, że dane pozostają nieuszkodzone i można je odzyskać w przypadku awarii? Tajniki łączenia strategii pełnego odtwarzania po awarii oraz wysokiej dostępności wykraczają poza zakres tego artykułu, lecz jest kilka prostych rzeczy, które na początek można zrobić.

Przytłaczająca większość uszkodzeń jest spowodowana przez „sprzęt”. Dlaczego w cudzysłowie? No cóż, tutaj określenie sprzęt tak naprawdę oznacza skrót dla „czegoś w podsystemie we/wy poniżej SQL Servera”. Podsystem we/wy składa się z takich elementów, jak system operacyjny, sterowniki system plików, sterowniki urządzeń, kontrolery RAID, kable, sieci oraz same napędy dysków. Jest dużo miejsc, w który problem może się pojawić (i się pojawia).

Jednym z najczęstszych problemów jest wystąpienie awarii zasilania, gdy dysk jest w trakcie wypisywania strony bazy danych. Jeśli dysk nie może zakończyć zapisu przed wyczerpaniem się zasilania (lub operacje zapisu są buforowane i jest zbyt mała rezerwa baterii to opróżnienia bufora dysku) rezultatem może być niepełny obraz strony na dysku. To może się zdarzyć, ponieważ 8-kilobajtowa strona bazy danych składa się w rzeczywistości z 16 sąsiadujących ze sobą 512-bajtowych sektorów dysku. Niekompletny zapis może zapisać niektóre z sektorów z nowej strony, a pozostawić niektóre sektory z obrazu poprzedniej strony. Taka sytuacja nosi nazwę „przedartej” strony. Jak można wykryć, że tak się stało?

SQL Server ma mechanizm do wykrywania takiej sytuacji. Obejmuje on przechowywanie pary bitów z każdego sektora strony i zapisywanie określonego wzoru na ich miejsce (to dzieje się tuż przed zapisaniem strony na dysk). Jeśli podczas wczytywania strony wzór nie jest taki sam, SQL Server wie, że strona została „rozdarta” i pokazuje błąd.

W SQL Server 2005 i późniejszych wersjach dostępny jest bardziej wszechstronny mechanizm o nazwie sumy kontrolne stron, który może wykryć dowolne uszkodzenie na stronie. Obejmuje to zapisywanie sumy kontrolnej całej strony na stronie tuż przed wypisaniem jej z pamięci, a następnie przetestowanie, gdy strona jest ponownie wczytywana, tak jak przy wykrywaniu rozdarcia strony. Po uaktywnieniu sum kontrolnych, strona musi być wczytana do puli bufora, zmieniona w jakiś sposób, a następnie wypisana na dysk zanim będzie chroniona przez sumę kontrolną strony.

Najlepszą więc praktyką jest włączenie sum kontrolnych dla SQL Server 2005, z wykrywaniem uszkodzonych stron także dla SQL Server 2000. Aby włączyć sumy kontrolne strony, używamy polecenia:

ALTER DATABASE MyDatabase SET PAGE_VERIFY CHECKSUM;

Aby włączyć wykrywanie rozdartych stron dla SQL Server 2000, używamy polecenia:

ALTER DATABASE MyDatabase SET TORN_PAGE_DETECTION ON;

Te mechanizmy pozwalają nam wykryć uszkodzenie na stronie, lecz tylko wtedy, gdy strona jest czytana. Jak prosto narzucić odczyt wszystkich alokowanych stron? Najlepszą metodą dokonania tego (i znalezienia każdego innego rodzaju uszkodzenia) jest użycie polecenia DBCC CHECKDB. Niezależnie od określonej opcji, to polecenie zawsze odczyta wszystkie strony w bazie danych, powodując w ten sposób weryfikację wszelkich sum kontrolnych strony oraz wykrywanie uszkodzenia stron. Należy także ustawić alerty, aby wiedzieć, kiedy użytkownicy napotkają problemy związane z uszkodzeniami podczas uruchamiania zapytań. Można także otrzymywać powiadomienia o wszystkich opisanych powyżej problemach, za pomocą alertu dla błędów o wadze 24 (rysunek 5).

Rysunek 5: Ustawienie alertu dla błędów o wadze 24.

Inną zalecaną praktyką jest regularne uruchamianie DBCC CHECKDB na bazach danych w celu sprawdzenia ich integralności. Jest wiele odmian tego polecenia i pytań dotyczących tego, jak często go uruchamiać. Niestety nie jest dostępny żaden dokument techniczny, który to omawia. Ponieważ jednak DBCC CHECKDB był główną częścią kodu, jaki napisałem dla SQL Server 2005, obszernie opisałem go w blogu. Dużo szczegółowych artykułów na temat sprawdzania spójności, najlepszych praktyk oraz porady typu „jak to zrobić” można znaleźć w moim blogu, w kategorii „CHECKDB From Every Angle” (sqlskills.com/blogs/paul). Praktyczną regułą dla mimowolnych DBA jest uruchamianie DBCC CHECKDB tak często, jak tworzona jest pełna kopia zapasowa bazy danych (więcej o tym poniżej). Polecam uruchamianie następującego polecenia:

DBCC CHECKDB ('MyDatabase') WITH NO_INFOMSGS, 

  ALL_ERRORMSGS;

Jeśli to polecenie daje jakiś wynik, to oznacza, że DBCC znalazł uszkodzenie w bazie danych. Pytanie, jakie wtedy powstaje, brzmi: co zrobić, jeśli DBCC CHECKDB znajdzie jakieś uszkodzenia. W tym miejscu do gry wchodzą kopie zapasowe.

 Do początku strony

Kopie zapasowe

Kiedy pojawi się uszkodzenie lub inna awaria, najbardziej efektywną metodą odzyskania jest przywrócenie bazy danych z kopii zapasowych. Po pierwsze zakładamy więc, że mamy kopie zapasowe i że one same nie są uszkodzone. Zbyt często ludzie chcą wiedzieć, jak ponownie uruchomić poważnie uszkodzoną bazę danych, gdy nie mają kopii zapasowej. Prosta odpowiedź jest taka, że nie można tego zrobić bez poniesienia jakiejś straty danych, co może wprowadzić zamęt w logice biznesowej oraz integralności relacyjnych danych.

Istnieje więc bardzo mocny argument za wykonywaniem regularnych kopii zapasowych. Niuanse tworzenia i przywracania kopii zapasowych wychodzą grubo poza zakres tego artykułu, lecz pozwolę sobie przedstawić krótki podręcznik tworzenia strategii kopii zapasowych.

Po pierwsze, trzeba tworzyć regularne, pełne kopie zapasowe bazy danych. Dzięki temu mamy pojedynczy punkt czasu, od którego możemy później dokonać przywracania. Pełną kopię zapasową bazy danych możemy zrobić za pomocą polecenia BACKUP DATABASE. Przykłady można znaleźć na stronie Books Online. W celu dodatkowej ochrony można użyć opcji WITH CHECKSUM, która sprawdza sumy kontrolne (jeśli istnieją) czytanych stron i oblicza sumę kontrolną całej kopii zapasowej. Trzeba wybrać częstotliwość, która odzwierciedla, ile danych lub pracy firma może bez problemu utracić. Na przykład tworzenie kopii zapasowej bazy danych raz na dzień oznacza w przypadku katastrofy ewentualność utraty danych wartych jednego dnia pracy. Jeśli stosujemy tylko pełne kopie zapasowe bazy danych, trzeba użyć modelu odzyskiwania SIMPLE (powszechnie nazywanego modelem odzyskiwania), aby uniknąć złożoności związanych z zarządzaniem zwiększaniem dzienników transakcji.

Po drugie, zawsze przechowujmy kopie zapasowe z kilku dni na wypadek, gdy jedna ulegnie uszkodzeniu – kopia sprzed kilku dni jest lepsza niż brak jakiejkolwiek kopii. Trzeba także sprawdzić integralność swoich kopii zapasowych przy użyciu polecenia RESTORE WITH VERIFYONLY (ponownie zajrzyjmy do Books Online). Jeśli podczas tworzenia kopii zapasowej użyliśmy opcji WITH CHECKSUM, po uruchomieniu polecenia weryfikacji zostanie sprawdzone, czy suma kontrolna kopii zapasowej jest nadal ważna, a także zostaną ponowne sprawdzone wszystkie sumy kontrolne stron w kopii zapasowej.

Po trzecie, jeśli pełna dzienna kopia zapasowa nie wystarcza jako zabezpieczenie maksymalnej straty danych/pracy, jakiej może doznać firma, możemy sprawdzić różnicowe kopie zapasowe bazy danych. Różnicowa kopia zapasowa bazy danych jest oparta na pełnej kopii zapasowej i zawiera zapis wszystkich zmian od ostatniej pełnej kopii zapasowej (typowe wyobrażenie jest takie, że różnicowe kopie zapasowe są przyrostowe – a one nie są). Prostą strategią może być robienie pełnych, dziennych kopi zapasowych oraz różnicowych kopii zapasowych co cztery godziny. Kopia różnicowa zapewnia możliwość odzyskania danych w dodatkowym punkcie czasu. Jeśli tylko stosujemy pełne i różnicowe kopie zapasowe, nadal trzeba korzystać z modelu odzyskiwania SIMPLE.

I wreszcie, najważniejszy element możliwości odtwarzania pojawił się wraz z użyciem kopii zapasowych dzienników. Są one dostępne tyko w modelach odzyskiwania FULL (lub BULK_LOGGED) i zapewniają kopię zapasową wszystkich rekordów dzienników wygenerowanych przed poprzednią kopią zapasową dziennika. Utrzymywanie zestawu kopii zapasowych dziennika z okresową pełną kopią bazy danych (i może różnicową) daje nieograniczoną liczbę punktów czasu do odzyskiwania – łącznie z odzyskiwaniem z ostatniej chwili. Kompromis jest taki, że dziennik transakcji będzie stale się powiększał dopóki nie zostanie „uwolniony” poprzez zrobienie kopii zapasowej dziennika. Prostą strategią byłoby tutaj robienie codziennie pełnej kopii zapasowej, kopii różnicowej co cztery godziny, a kopii zapasowej dziennika co pół godziny.

Podejmowanie decyzji na temat strategii kopi zapasowej i ustawianie jej może być skomplikowane. Minimum stanowi posiadanie regularnych kopii zapasowych, aby zapewnić sobie przynajmniej jeden punkt odzyskania.

 Do początku strony

Podsumowanie

Jak widać zapewnienie, aby nasza baza danych pozostała zdrowa i dostępna, wymaga kilku koniecznych działań. Oto moja ostateczna lista dla mimowolnego administratora przejmującego kontrolę nad bazą danych:

• Usunąć nadmierną fragmentację pliku dziennika transakcji.
• Prawidłowo ustawić automatyczne powiększanie.
• Wyłączyć wszelkie planowane operacje zmniejszania.
• Włączyć natychmiastową inicjację pliku.
• Wdrożyć regularny proces wykrywania i usuwania fragmentacji indeksu.
• Włączyć AUTO_CREATE_STATISTICS i AUTO_UPDATE_STATISTICS, oraz mieć dostępny regularny proces aktualizacji statystyk.
• Włączyć sumy kontrolne strony (lub przynajmniej wykrywanie uszkodzenia stron w SQL Server 2000).
• Posiadać regularną procedurę uruchamiania DBCC CHECKDB.
• Mieć wdrożoną regularną procedurę wykonywania pełnych kopii zapasowych, plus kopie zapasowe pełne i różnicowe dla odzyskiwania w danym czasie.

W tym artykule podano polecenia T-SQL, lecz podobny efekt uzyskać można z poziomu SQL Management Studio. Mam nadzieję, że podałem nieco użytecznych wskazówek dla efektywnego zarządzania bazą danych. Swoje opinie lub pytania proszę podsyłać do mnie na adres paul@sqlskills.com.

O autorze

Paul S. Randal jest dyrektorem zarządzającym SQLskills.com oraz ma tytuł SQL Server MVP. Pracował w zespole SQL Server Storage Engine, w firmie Microsoft od 1999 do 2007. Paul jest ekspertem od odzyskiwania po awarii, wysokiej dostępności oraz konserwacji bazy danych. Pisze blog na stronie SQLskills.com/blogs/paul.

 Do początku strony

---