SQL Server

SQL Pytania i Odpowiedzi: Błędy we/wy, Database Mirroring i inne Udostępnij na: Facebook

Przez Paul S. Randal

Opublikowano: 22 sierpnia 2008 | Zaktualizowano: 22 sierpnia 2008

Zawartość strony
Błędy we/wy  Błędy we/wy
Problem z fragmentacją i kurczeniem bazy danych  Problem z fragmentacją i kurczeniem bazy danych
Database Mirroring  Database Mirroring

 

Pyt. Błędy we/wy

Odp.

W SQL Server® 2000 istniała stara sztuczka, która pozwalała uszkodzić bazę danych na potrzeby testów. Polegała ona na ręcznym usunięciu wiersza z tabeli sysindexes testowej bazy danych. Jednak w SQL Server 2005 uszkodzenie tabeli systemowej w ten sposób jest bardzo trudne. Zatem najlepszym sposobem uszkodzenia testowej bazy danych jest zastosowanie edytora hex do zmiany pliku danych, w czasie gdy baza danych jest zamknięta. Oto co należy zrobić:

  •  Zamknąć bazę danych, aby pliki danych nie były zablokowane (jednak nie należy odłączać bazy danych, ponieważ w przypadku uszkodzenia nieodpowiedniej strony jej ponowne podłączenie może okazać się niemożliwe).

  •  Wybrać przesunięcie większe niż powiedzmy 100 stron w pliku (przynajmniej 819200 bajtów), zwracając uwagę, aby było one rozmieszczone na 8192-bajtowej granicy (granicy strony). To pozwoli pominąć strony metadanych oraz bitmapy alokacji, umożliwiając uruchomienie bazy danych i wykonanie na niej kontroli DBCC CHECKDB.

  •  Zapisać kilka bajtów zer w pliku z wybranym przesunięciem. Zastosowanie tej techniki stanowi niemal gwarancję spowodowania pewnych uszkodzeń nagłówków stron.

Jednak najszybszy sposób stworzenia uszkodzonej testowej bazy danych polega na wykorzystaniu bazy danych stworzonej uprzednio przez kogoś innego.

Odnośnie drugiego pytania, co należy zrobić, aby wykrywać błędy we/wy: należy włączyć sumy kontrolne stron. Ta funkcja została wprowadzona w wersji SQL Server 2005 jako metoda zabezpieczania całej strony bazy danych przed błędami spowodowanymi przez podsystem we/wy.

Zasadniczo, gdy strona jest zapisywana na dysku, ostatnią czynnością wykonywaną przez SQL Server jest wyliczenie sumy kontrolnej całej 8 kilobajtowej strony i umieszczenie tej sumy kontrolnej na stronie. Gdy strona posiada sumę kontrolną w momencie wczytywania jej z dysku, suma ta jest wyliczana ponownie i porównywana z sumą kontrolną umieszczoną na stronie. Jeśli wartości te różnią się, oznacza to, że strona została uszkodzona poza systemem SQL Server i w związku z tym wywoływany jest błąd 824. Błąd jest wyświetlany w połączeniu, które spowodowało odczyt strony, jak również rejestrowany w dzienniku błędów SQL Server oraz dzienniku zdarzeń aplikacji systemu Windows® (Application Event Log).

Sumy kontrolne stron są włączone domyślnie dla wszystkich baz danych tworzonych w systemie SQL Server 2005 oraz SQL Server 2008. Jednak w przypadku baz danych aktualizowanych z poprzednich wersji SQL Server trzeba włączyć je własnoręcznie. Sumy kontrolne można włączyć przy pomocy następującego kodu:

ALTER DATABASE mydb SET PAGE_VERIFY CHECKSUM;

Pyt. Problem z fragmentacją i kurczeniem bazy danych

Odp.

Osoby konfigurujące plan konserwacji często napotykają ten problem. Jest on konsekwencją wpadnięcia w pułapkę cyklu kurczenie-wzrost-kurczenie-wzrost.

Gdy indeks jest przebudowywany, zanim usunięty zostaje istniejący indeks, tworzona jest jego kopia. Procedura ta wymaga dodatkowego miejsca w plikach bazy danych, zwykle mniej więcej o tym samym rozmiarze co miejsce wykorzystywane przez aktualny indeks. W SQL Server 2000 wymagane było również dodatkowe miejsce na sortowanie wierszy indeksów (około 20 procent rozmiaru indeksu), jednak w wersji SQL Server 2005 ten wymóg został wyeliminowany w przypadku prostego przebudowywania indeksu.

Administratorzy chcą czasem usunąć dodatkowe miejsce, które zostało stworzone podczas przebudowywania indeksu i w tym celu dodają operację kurczenia do planu konserwacji po kroku przebudowania. Jednak nie wszyscy wiedzą, że ta operacja kurczenia ze względu na naturę jej algorytmu powoduje fragmentację indeksu. A to oznacza, że nowo przebudowany i zdefragmentowany indeks natychmiast stanie się fragmentowanym indeksem, niwecząc wcześniejszy efekt przebudowania.

Biorąc pod uwagę fakt, iż plik bazy danych ponownie zwiększy się podczas kolejnej operacji przebudowywania indeksu, lepiej jest umożliwić bazie danych obejmowanie dodatkowego obszaru i całkowicie pominąć uruchamianie operacji kurczenia (ponadto stałe zwiększanie i zmniejszanie rozmiaru plików bazy danych będzie powodowało fragmentację na poziomie systemu operacyjnego, która podobnie jak fragmentacja indeksu może przyczynić się do obniżenia wydajności).

Na zakończenie warto także rozważyć zredukowanie częstotliwości przebudowywania indeksów. Można nawet zdecydować się na wykorzystanie alternatywnej metody, takiej jak starsza operacja DBCC INDEXDEFRAG napisana dla systemu SQL Server 2000 lub nowsza składnia ALTER INDEX REORGANIZE w systemach SQL Server 2005 oraz SQL Server 2008.

Dostępny jest dokument omawiający fragmentację indeksów oraz instruujący, kiedy należy usuwać fragmentację (o adresie go.microsoft.com/fwlink/?LinkId=115154). Choć dokument ten został napisany z myślą o systemie SQL Server 2000, główne koncepcje pozostały niezmienione.

Wskazówka: Zmiana domyślnego portu SQL Server

Domyślnie skonfigurowany port dla instancji SQL Server to 1433. Jednak gdy port ten zostanie zajęty przez jedną instancję, nie może zostać wykorzystany przez inną. A zatem gdy zainstalujemy drugą (nazwaną) instancję nasłuchującą w sieci przy użyciu protokołu tcp, będzie ona potrzebowała innego portu. W niektórych przypadkach administrator może także chcieć zmienić port w celu jego utajnienia (choć ta forma utajniania jest marginalna i może zostać pokonana poprzez proste przeskanowanie portów). Oczywiście w konsekwencji musimy skonfigurować aplikacje klienckie tak, aby wykorzystywały one inny port. Istnieją trzy typowe podejścia do tego problemu.

Załóżmy, administrator zmienił port instancji na 5555. Stosując pierwszą metodę, możemy albo po prostu określić numer portu instancji wraz z nazwą maszyny, z którą chcemy się połączyć, wykorzystując składnię NazwaSerwera,5555. Jednak jeśli port znowu ulegnie zmianie, klienci będą musieli także zmodyfikować swoje ustawienia connectionString.

Inna opcja polega na wykorzystaniu aliasów SQL Server, które są konfigurowane po stronie klienta. Poza określeniem nazwy aliasu należy również podać nazwę serwera, nazwę portu i protokół. Po skonfigurowaniu alias może być wykorzystywany do łączenia się z instancją bazy danych zupełnie jak nazwa serwera. Zaletą tej metody jest to, że zmiany konfiguracji serwera mogą być rozmieszczane przez administratora domeny, gdyż ustawienia są składowane w rejestrze.

Trzecia opcja dotycząca nazwanych instancji, gdy użytkownik zna jedynie nazwę instancji i określa ją przy użyciu wyrażenia NazwaMaszyny\NazwaInstancji w ustawieniach connectionString, polega na wykorzystaniu usługi SQL Server Browser Service. Została ona zaimplementowana już w wersji SQL Server 2000 jako składnik uruchomionej usługi. Jednak w wersji SQL Server 2005 SQL Server Browser Service to osobna usługa. Poza wykrywaniem instancji na maszynie odpowiada ona również za przychodzące żądania UDP (User Datagram Protocol) na porcie 1434 przy użyciu odpowiedniego numeru portu dla żądanej instancji, umożliwiając przekierowywanie klientów oraz wspierając transparentne połączenia.

—Jens K. Suessmeyer, Database Consultant w firmie Microsoft

 

Pyt. Database Mirroring

Odp.

Odpowiedź na to pytanie jest taka, jak w wielu podobnych sytuacjach: To zależy! Opublikowane zalecenia wskazują, aby Database Mirroring nie obejmował więcej niż 10 baz danych per instancja, ale liczba 10 to jedynie zgrubne oszacowanie maksymalnej wartości na podstawie typowych zastosowań. Należy rozważyć następujące czynniki dotyczące konfiguracji sprzętowej:

  •  Ile pamięci posiada instancja principal i mirror? Idealnie byłoby, gdyby wartości te były równe.

  •  Ile mocy przetwarzania posiada instancja principal i mirror? Również powinny być równe.

  •  Jaką przepustowość posiada podsystem we/wy na instancji mirror? Powinna być ona taka sama jak na instancji principal.

  •  Jak duży dziennik transakcji generowany jest przez obciążenie robocze każdej z baz danych?

  •  Jaka przepustowość sieci jest dostępna między instancją principal a mirror?

Ostatnie dwa czynniki są najważniejsze. Jeśli przepustowość sieci dostępna między dwoma instancjami nie jest wystarczająco wysoka, aby obsłużyć połączony współczynnik generowania dziennika transakcji per sekunda ze wszystkich poddawanych mirroringowi baz danych, spowoduje to spadek wydajności baz danych principal. SQL Server 2008 pomaga w łagodzeniu obciążenia poprzez kompresję strumienia dziennika.

Kolejnym istotnym czynnikiem, który należy wziąć pod uwagę, są wymagania mechanizmu Database Mirroring dotyczące pamięci oraz wątków. Każda poddana mirroringowi baza danych zajmuje jeden wątek plus pewną pamięć. Na serwerach o niewielkich możliwościach duża liczba takich baz danych w połączeniu ze zwykłym obciążeniem roboczym może stanowić zbyt duże obciążenie.

Trzeba również zastanowić się, w jaki sposób ma być uruchamiany mechanizm Database Mirroring. W trybie synchronicznym transakcje w bazie danych principal nie mogą zostać zatwierdzone, dopóki wszystkie rekordy dziennika transakcji nie zostaną skopiowane do dziennika transakcji bazy danych mirror. Z tego względu jakiekolwiek opóźnienia spowodowane przez przeciążoną sieć mogą powodować problemy z wydajnością obciążenia roboczego na serwerze principal.

W trybie asynchronicznym transakcje mogą zostać zatwierdzone w bazie danych principal bez konieczności oczekiwania, ale opóźnienia w sieci mogą powodować wzrost porcji dziennika transakcji oczekującej na wysłanie do instancji mirror. To może powodować problemy z rozmiarem dziennika transakcji. A co gorsza niewysłane dzienniki transakcji zostają uszkodzone w przypadku wystąpienia awarii, a zatem im większa niewysłana porcja dziennika transakcji, tym większe ryzyko utraty danych w sytuacji odzyskiwania.

Scenariusze mogą znacznie się od siebie różnić. Autor tego artykułu miał okazję zobaczyć kilka interesujących przykładów w rzeczywistych środowiskach produkcyjnych. Na przykład jedno środowisko ze 150 bazami danych, przy czym każda z nich charakteryzowała się bardzo niewielką aktywnością i okresy aktywności w niewielkim stopniu się pokrywały. Mirroring wszystkich 150 baz danych przebiegał bez problemów.

Z drugiej strony w innej konfiguracji znajdowały się jedynie trzy, bardzo obciążone bazy danych, bez dobrego połączenia sieciowego. W tym scenariuszu możliwy był mirroring zaledwie jednej bazy danych, ponieważ niska przepustowość sieci powodowała degradację obciążeń roboczych.

Kluczem do sukcesu jest po pierwsze oszacowanie intensywności procesu generowania dziennika. Jeśli wydaje się, że dostępna przepustowość sieci pozwoli na wspieranie mirroringu wybranej liczby baz danych, wszystko powinno być w porządku. Należy przetestować konfigurację przed umieszczeniem jej w środowisku produkcyjnym oraz upewnić się, że uwzględnione zostały wszystkie operacje, które mogą generować dziennik transakcji, w szczególności przeprowadzane operacje konserwacji bazy danych.

 


Więcej informacji

Paul S. Randal pełni funkcję dyrektora zarządzającego w firmie SQLskills.com oraz SQL Server MVP. Pracował z zespole SQL Server Storage Engine w firmie Microsoft od 1999 do 2007 roku. Paul zajmował się tworzeniem funkcji DBCC CHECKDB/repair dla SQL Server 2005 i był odpowiedzialny za Core Storage Engine w fazie rozwoju systemu SQL Server 2008. Jako ekspert w dziedzinie odzyskiwania po awariach, wysokiej dostępności oraz konserwacji baz danych Paul regularnie prowadzi wykłady na konferencjach. Tworzy blog o adresie SQLskills.com/blogs/paul.

 Do początku strony Do początku strony


SQL Server