Opis algorytmów rejestrowania i przechowywania danych, które rozszerzają niezawodność danych w SQL Server

Oryginalna wersja produktu: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Oryginalny numer KB: 230785

Podsumowanie

W tym artykule omówiono sposób, w jaki firma Microsoft SQL Server algorytmy rejestrowania i danych rozszerzają niezawodność i integralność danych.

Aby dowiedzieć się więcej na temat podstawowych pojęć dotyczących aparatów i algorytmu odzyskiwania i izolacji wykorzystującej semantykę (ARIES), zobacz następujący dokument ACM Transactions on Database Systems (W obszarze Wolumin 17, Numer 1, marzec 1992 r.):

Link zewnętrzny: ARIES: metoda odzyskiwania transakcji obsługująca blokowanie Fine-Granularity i częściowe wycofywanie przy użyciu rejestrowania Write-Ahead

Dokument dotyczy technik SQL Server w celu rozszerzenia niezawodności i integralności danych w związku z awariami.

Zalecamy przeczytanie następujących artykułów w bazie wiedzy Microsoft Knowledge Base, aby uzyskać więcej informacji na temat buforowania i alternatywnych dyskusji dotyczących trybu awarii:

• Opis buforowania kontrolerów dysków w SQL Server

• Informacje o korzystaniu z pamięci podręcznych dysków z SQL Server, które powinien znać każdy administrator bazy danych

Terminy używane w tym artykule

Przed rozpoczęciem szczegółowej dyskusji niektóre terminy używane w tym artykule są zdefiniowane w poniższej tabeli.

protokół rejestrowania Write-Ahead (WAL)

Termin protokół jest doskonałym sposobem opisywania WAL. Jest to określony i zdefiniowany zestaw kroków implementacji niezbędnych do upewnienia się, że dane są przechowywane i wymieniane prawidłowo, a w przypadku awarii można je odzyskać do znanego stanu. Podobnie jak sieć zawiera zdefiniowany protokół do wymiany danych w spójny i chroniony sposób, tak samo wal opisuje protokół w celu ochrony danych.

Dokument usługi ARIES definiuje wal w następujący sposób:

Protokół WAL zapewnia, że rekordy dziennika reprezentujące zmiany niektórych danych muszą już znajdować się w stabilnym magazynie, zanim zmienione dane będą mogły zastąpić poprzednią wersję danych w magazynie nieulotowym. Oznacza to, że system nie może pisać zaktualizowanej strony w nieulotnej wersji magazynu strony, dopóki co najmniej części cofania rekordów dziennika, które opisują aktualizacje strony, zostały zapisane w stabilnym magazynie.

Aby uzyskać więcej informacji na temat rejestrowania zapisu z wyprzedzeniem, zobacz temat Write-Ahead Transaction Log (Dziennik transakcji zapisu z wyprzedzeniem) w witrynie SQL Server Books Online.

SQL Server i wal

SQL Server używa protokołu WAL. Aby upewnić się, że transakcja jest poprawnie zatwierdzona, wszystkie rekordy dziennika skojarzone z transakcją muszą być zabezpieczone w stabilnym magazynie.

Aby wyjaśnić tę sytuację, rozważ następujący konkretny przykład.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Następnie podziel działanie na uproszczone kroki rejestrowania, zgodnie z opisem w poniższej tabeli.

Nie należy mylić terminów "blokowanie" i "rejestrowanie". Chociaż ważne, blokowanie i rejestrowanie są oddzielnymi problemami w przypadku obsługi wal. W poprzednim przykładzie SQL Server zwykle przechowuje zatrzaśnięcie na stronie 150 w czasie niezbędnym do wykonania fizycznych zmian wstawiania na stronie, a nie przez cały czas transakcji. Odpowiedni typ blokady jest ustanawiany w celu ochrony wiersza, zakresu, strony lub tabeli w razie potrzeby. Aby uzyskać więcej informacji na temat typów blokad, zapoznaj się z sekcjami blokady usługi SQL Server Books Online.

Patrząc na przykład bardziej szczegółowo, możesz zapytać, co się stanie po uruchomieniu procesów LazyWriter lub CheckPoint. SQL Server wystawia wszystkie odpowiednie opróżnienia do stabilnego magazynu dla rekordów dziennika transakcyjnego skojarzonych ze stroną zanieczyszczoną i przypiętą. Dzięki temu strona danych protokołu WAL nigdy nie może być zapisywana w stabilnym magazynie, dopóki skojarzone rekordy dziennika transakcyjnego nie zostaną opróżnione.

SQL Server i stabilny magazyn

SQL Server rozszerza operacje na stronach dzienników i danych, uwzględniając wiedzę na temat rozmiarów sektorów dysków (często 4096 bajtów lub 512 bajtów).

Aby zachować właściwości ACID transakcji, SQL Server musi uwzględniać punkty awarii. Podczas awarii wiele specyfikacji dysków gwarantuje tylko ograniczoną liczbę operacji zapisu sektorowego. Większość specyfikacji gwarantuje ukończenie zapisu w jednym sektorze, gdy wystąpi awaria.

SQL Server używa stron danych o rozmiarze 8 KB i dziennika (jeśli zostanie opróżniony) na wielokrotnościach rozmiaru sektora. (Większość dysków używa 512 bajtów jako domyślnego rozmiaru sektora). Jeśli wystąpi awaria, SQL Server może uwzględniać operacje zapisu większe niż sektor, stosując parytet dzienników i rozdarte techniki zapisu.

Wykrywanie rozdartej strony

Ta opcja umożliwia SQL Server wykrywanie niekompletnych operacji we/wy spowodowanych awariami zasilania lub innymi awariami systemu. Gdy wartość true, powoduje to, że bit jest przerzucany dla każdego sektora 512-bajtowego na stronie bazy danych o rozmiarze 8 kilobajtów (KB) za każdym razem, gdy strona jest zapisywana na dysku. Jeśli bit jest w niewłaściwym stanie, gdy strona jest później odczytywana przez SQL Server, strona została napisana niepoprawnie; wykryto rozdartą stronę. Rozdarte strony są wykrywane podczas odzyskiwania, ponieważ każda strona, która została napisana niepoprawnie, prawdopodobnie zostanie odczytana przez odzyskiwanie.

Chociaż SQL Server stron bazy danych to 8 KB, dyski wykonują operacje we/wy przy użyciu sektora 512-bajtowego. W związku z tym 16 sektorów jest zapisywanych na stronie bazy danych. Rozdarta strona może wystąpić, jeśli system ustępuje (na przykład z powodu awarii zasilania) między czasem zapisu pierwszego sektora 512-bajtowego na dysku a ukończeniem operacji we/wy 8 KB. Jeśli pierwszy sektor strony bazy danych zostanie pomyślnie zapisany przed awarią, strona bazy danych na dysku będzie wyświetlana jako zaktualizowana, chociaż może się to nie powieść.

Korzystając z pamięci podręcznych kontrolera dysków opartych na baterii, możesz upewnić się, że dane zostały pomyślnie zapisane na dysku lub w ogóle nie zostały zapisane. W takiej sytuacji nie ustawiaj wartości "true" wykrywania rozdartej strony, ponieważ nie jest to konieczne.

Parzność dziennika

Sprawdzanie parity dzienników jest podobne do wykrywania rozdartych stron. Każdy sektor 512-bajtowy zawiera bity parzystości. Te bity parzystości są zawsze zapisywane z rekordem dziennika i oceniane podczas pobierania rekordu dziennika. Wymuszając zapisy dzienników na granicy 512-bajtowej, SQL Server może upewnić się, że operacje zatwierdzania są zapisywane w sektorach dysków fizycznych.

Wpływ na wydajność

Wszystkie wersje SQL Server otworzyć pliki dziennika i danych przy użyciu funkcji Win32 CreateFile. Element członkowski dwFlagsAndAttributes zawiera opcjęFILE_FLAG_WRITE_THROUGH, gdy są otwierane przez SQL Server.

FILE_FLAG_WRITE_THROUGH instruuje system, aby zapisywał w dowolnej pośredniej pamięci podręcznej i przechodził bezpośrednio na dysk. System nadal może buforować operacje zapisu, ale nie może ich leniwie opróżnić.

Opcja FILE_FLAG_WRITE_THROUGH zapewnia, że gdy operacja zapisu zwróci pomyślne ukończenie, dane są poprawnie przechowywane w stabilnym magazynie. Jest to zgodne z protokołem WAL, który zapewnia dane.

Wiele dysków (SCSI i IDE) zawiera dołączone pamięci podręczne o rozmiarze 512 KB, 1 MB lub większym. Jednak pamięci podręczne dysków zwykle opierają się na kondensatorze, a nie na rozwiązaniu opartym na baterii. Te mechanizmy buforowania nie mogą zagwarantować zapisów w całym cyklu zasilania lub podobnym punkcie awarii. Gwarantują one jedynie ukończenie operacji zapisu w sektorze. Właśnie dlatego wykrywanie parzystości zapisu i dziennika zostało wbudowane w SQL Server wersji 7.0 i nowszych. W miarę wzrostu rozmiaru dysków pamięci podręczne stają się większe i mogą uwidaczniać większe ilości danych podczas awarii.

Wielu dostawców sprzętu udostępnia rozwiązania kontrolera dysków z obsługą baterii. Te pamięci podręczne kontrolera mogą utrzymywać dane w pamięci podręcznej przez kilka dni, a nawet zezwalać na umieszczenie sprzętu buforowania na drugim komputerze. Po prawidłowym przywróceniu zasilania niepisane dane są opróżniane przed zezwoleniem na dalszy dostęp do danych. Wiele z nich umożliwia ustanowienie procentu pamięci podręcznej odczytu i zapisu w celu uzyskania optymalnej wydajności. Niektóre z nich zawierają duże obszary magazynowania pamięci. W rzeczywistości, dla określonego segmentu rynku, niektórzy dostawcy sprzętu zapewniają wysokiej klasy systemy kontrolera buforowania dysków z obsługą baterii z 6 GB pamięci podręcznej. Mogą one znacznie poprawić wydajność bazy danych.

Zaawansowane implementacje buforowania będą obsługiwać FILE_FLAG_WRITE_THROUGH żądanie, nie wyłączając pamięci podręcznej kontrolera, ponieważ mogą zapewnić rzeczywiste możliwości ponownego zapisywania w przypadku resetowania systemu, awarii zasilania lub innego punktu awarii.

Transfery we/wy bez użycia pamięci podręcznej mogą być dłuższe ze względu na czas mechaniczny wymagany do przenoszenia głowic dysków, szybkości wirowania i innych czynników ograniczających.

Kolejność sektorów

Powszechną techniką stosowaną w celu zwiększenia wydajności operacji we/wy jest kolejność sektorów. Aby uniknąć mechanicznego przenoszenia głowy, żądania odczytu/zapisu są sortowane, co pozwala na bardziej spójny ruch głowy w celu pobierania lub przechowywania danych.

Pamięć podręczna może przechowywać wiele żądań zapisu dzienników i danych w tym samym czasie. Protokół WAL i implementacja SQL Server protokołu WAL wymagają opróżnienia zapisów dziennika w stabilnym magazynie, zanim będzie można wydać zapis strony. Jednak użycie pamięci podręcznej może zwrócić powodzenie żądania zapisu dziennika bez zapisywania danych na rzeczywistym dysku (czyli zapisywanym w stabilnym magazynie). Może to prowadzić do SQL Server wysłania żądania zapisu strony danych.

Dzięki zaangażowaniu pamięci podręcznej zapisu dane są nadal uważane za w magazynie nietrwałym. Jednak z wywołania pliku WriteFile interfejsu API Win32 dokładnie tak, jak SQL Server widzi działanie, uzyskano pomyślny kod powrotny. SQL Server lub dowolny proces korzystający z wywołania interfejsu API WriteFile może określić tylko, że dane prawidłowo uzyskały stabilny magazyn.

Na potrzeby dyskusji załóżmy, że wszystkie sektory strony danych są sortowane do zapisu przed sektorami pasujących rekordów dziennika. To natychmiast narusza protokół WAL. Pamięć podręczna zapisuje stronę danych przed rekordami dziennika. Jeśli pamięć podręczna nie jest w pełni zasilana z baterii, awaria może spowodować katastrofalne wyniki.

Podczas oceny optymalnych czynników wydajności dla serwera bazy danych należy wziąć pod uwagę wiele czynników. Najważniejsze z nich to: "Czy mój system zezwala na prawidłowe FILE_FLAG_WRITE_THROUGH możliwości?"

Testy wykazały, że wiele konfiguracji dysków może zawierać buforowanie zapisu bez odpowiedniej kopii zapasowej baterii. Dyski SCSI, IDE i EIDE w pełni wykorzystują pamięć podręczną zapisu. Aby uzyskać więcej informacji na temat współpracy dysków SSD z SQL Server, zobacz następujący artykuł na blogu CSS SQL Server Engineers:

SQL Server i dyski SSD — uwagi szkoleniowe RDORR — część 1

W wielu konfiguracjach jedynym sposobem poprawnego wyłączenia buforowania zapisu dysku IDE lub EIDE jest użycie określonego narzędzia producenta lub użycie zworków znajdujących się na samym dysku. Aby upewnić się, że pamięć podręczna zapisu jest wyłączona dla samego dysku, skontaktuj się z producentem dysku.

Dyski SCSI mają również pamięci podręczne zapisu. Jednak te pamięci podręczne często mogą być wyłączone przez system operacyjny. Jeśli masz jakiekolwiek pytanie, skontaktuj się z producentem dysku w celu uzyskania odpowiednich narzędzi.

Zapisywanie stosu pamięci podręcznej

Układanie pamięci podręcznej zapisu jest podobne do porządkowania sektorów. Poniższa definicja została pobrana bezpośrednio z witryny internetowej wiodącego producenta dysków IDE:

Zwykle ten tryb jest aktywny. Tryb zapisu pamięci podręcznej akceptuje zapisywanie danych przez hosta w buforze do momentu zapełnienia buforu lub zakończenia transferu hosta.

Zadanie zapisu dysku zaczyna przechowywać dane hosta na dysku. Polecenia zapisu hosta są nadal akceptowane, a dane przesyłane do buforu do momentu zapełnienia stosu poleceń zapisu lub zapełnienia buforu danych. Dysk może zmienić kolejność poleceń zapisu w celu zoptymalizowania przepływności dysku.

Automatyczna alokacja zapisu (AWR)

Inną powszechną techniką, która jest używana do ochrony danych, jest wykrywanie nieprawidłowych sektorów podczas manipulowania danymi. Poniższe wyjaśnienie pochodzi z witryny internetowej wiodącego producenta dysków IDE:

Ta funkcja jest częścią pamięci podręcznej zapisu i zmniejsza ryzyko utraty danych podczas odroczonych operacji zapisu. Jeśli podczas procesu zapisu dysku wystąpi błąd dysku, zadanie dysku zostanie zatrzymane, a podejrzany sektor zostanie przesunięte do puli alternatywnych sektorów znajdujących się na końcu dysku. Po cofnięciu przydziału zadanie zapisu dysku będzie kontynuowane do momentu jego ukończenia.

Może to być zaawansowana funkcja, jeśli dla pamięci podręcznej jest dostępna bateria Kopia zapasowa. Zapewnia to odpowiednie modyfikacje po ponownym uruchomieniu. Lepiej jest wykrywać błędy dysku, ale bezpieczeństwo danych protokołu WAL ponownie wymagałoby, aby było to wykonywane w czasie rzeczywistym, a nie w sposób odroczony. W parametrach WAL technika AWR nie może uwzględniać sytuacji, w której zapis dziennika kończy się niepowodzeniem z powodu błędu sektora, ale dysk jest pełny. Aparat bazy danych musi natychmiast wiedzieć o awarii, aby transakcja mogła zostać poprawnie przerwana, może zostać wyświetlony alert administratora i podjęto odpowiednie kroki w celu zabezpieczenia danych i skorygowania sytuacji awarii nośnika.

Bezpieczeństwo danych

Istnieje kilka środków ostrożności, które administrator bazy danych powinien podjąć w celu zapewnienia bezpieczeństwa danych.

• Zawsze dobrym pomysłem jest upewnienie się, że strategia tworzenia kopii zapasowych jest wystarczająca do odzyskania sprawności po awarii katastrofalnej. Magazyn poza lokacją i inne środki ostrożności są odpowiednie.
• Często testuj operację przywracania bazy danych w pomocniczej lub testowej bazie danych.
• Upewnij się, że wszystkie urządzenia buforujące mogą obsługiwać wszystkie sytuacje awarii (awaria zasilania, nieprawidłowe sektory, nieprawidłowe dyski, awaria systemu, blokady, skok zasilania itd.).
• Upewnij się, że urządzenie buforujące:
  • Ma zintegrowaną kopię zapasową baterii
  • Może ponownie tworzyć zapisy w dodatku power-up
  • Można w pełni wyłączyć, jeśli jest to konieczne
  • Obsługuje ponowne mapowanie nieprawidłowego sektora w czasie rzeczywistym
• Włącz wykrywanie strony rozdartej. (Ma to niewielki wpływ na wydajność).
• Jeśli jest to możliwe, skonfiguruj dyski RAID umożliwiające wymianę nieprawidłowego dysku na gorąco.
• Użyj nowszych kontrolerów buforowania, które umożliwiają dodanie większej ilości miejsca na dysku bez ponownego uruchamiania systemu operacyjnego. Może to być idealne rozwiązanie.

Dyski testowe

Aby w pełni zabezpieczyć dane, upewnij się, że wszystkie buforowanie danych jest prawidłowo obsługiwane. W wielu sytuacjach należy wyłączyć buforowanie zapisu dysku.

Firma Microsoft wykonała testy na kilku dyskach SCSI i IDE przy użyciu SQLIOSim narzędzia . To narzędzie symuluje dużą asynchroniczną aktywność odczytu/zapisu na symulowanym urządzeniu danych i urządzeniu dziennika. Statystyki wydajności testu pokazują średnie operacje zapisu na sekundę z zakresu od 50 do 70 dla dysku z wyłączonym buforowaniem zapisu i zakresem obr./min między 5200 a 7200.

Aby uzyskać więcej informacji o narzędziu SQLIOSim , zobacz następujący artykuł w bazie wiedzy Microsoft Knowledge Base:

Jak za pomocą narzędzia SQLIOSim symulować działanie SQL Server w podsystemie dysku

Wielu producentów komputerów zamawia dyski, wyłączając pamięć podręczną zapisu. Jednak testy pokazują, że nie zawsze tak jest. Dlatego zawsze przetestuj całkowicie.

Urządzenia danych

We wszystkich sytuacjach, ale niezarejestrowanych, SQL Server będzie wymagać opróżniania tylko rekordów dziennika. W przypadku wykonywania niezarejestrowanych operacji strony danych muszą być również opróżniane do stabilnego magazynu; Brak pojedynczych rekordów dziennika do ponownego wygenerowania akcji w przypadku awarii.

Strony danych mogą pozostać w pamięci podręcznej, dopóki proces LazyWriter lub CheckPoint nie opróżni ich do stabilnego magazynu. Użycie protokołu WAL w celu upewnienia się, że rekordy dziennika są poprawnie przechowywane, zapewnia, że odzyskiwanie może odzyskać stronę danych do znanego stanu.

Nie oznacza to, że zaleca się umieszczanie plików danych na dysku w pamięci podręcznej. Gdy SQL Server opróżnia strony danych do stabilnego magazynu, rekordy dziennika mogą zostać obcięte z dziennika transakcji. Jeśli strony danych są przechowywane w nietrwałej pamięci podręcznej, można obciąć rekordy dziennika, które zostaną użyte do odzyskania strony w przypadku awarii. Upewnij się, że zarówno dane, jak i urządzenia dziennika prawidłowo obsługują stabilny magazyn.

Zwiększanie wydajności

Pierwsze pytanie, które może wystąpić, brzmi: "Mam dysk IDE, który buforował. Ale kiedy go wyłączyłem, mój występ stał się mniejszy niż oczekiwano. Dlaczego?"

Wiele dysków IDE testowanych przez firmę Microsoft działa z prędkością 5200 obr./min, a dyski SCSI z prędkością 7200 obr./min. Po wyłączeniu buforowania zapisu dysku IDE wydajność mechaniczna może stać się czynnikiem.

Aby rozwiązać problem z różnicą wydajności, metoda do wykonania jest jasna: "Zaadeksuj współczynnik transakcji".

Wiele systemów przetwarzania transakcji online (OLTP) wymaga wysokiej szybkości transakcji. W przypadku tych systemów rozważ użycie kontrolera buforowania, który może odpowiednio obsługiwać pamięć podręczną zapisu i zapewnić żądany wzrost wydajności przy jednoczesnym zapewnieniu integralności danych.

Aby obserwować znaczące zmiany wydajności występujące w SQL Server na dysku buforowania, szybkość transakcji została zwiększona przy użyciu małych transakcji.

Testowanie pokazuje, że duża aktywność zapisu buforów mniejsza niż 512 KB lub większa niż 2 MB może powodować niską wydajność.

Rozważmy następujący przykład:

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 10000
INSERT INTO tblTest VALUES ('Test')

Poniżej przedstawiono przykładowe wyniki testów dla SQL Server:

SCSI(7200 RPM) 84 seconds
SCSI(7200 RPM) 15 seconds (Caching controller)

IDE(5200 RPM) 14 seconds (Drive cache enabled)
IDE(5200 RPM) 160 seconds

Proces opakowywania całej serii INSERT operacji w pojedynczą transakcję jest uruchamiany w około cztery sekundy we wszystkich konfiguracjach. Wynika to z liczby wymaganych opróżnień dziennika. Jeśli nie utworzysz pojedynczej transakcji, każda z tych INSERT transakcji będzie przetwarzana jako oddzielna transakcja. W związku z tym wszystkie rekordy dziennika dla transakcji muszą zostać opróżnione. Każde opróżnienie ma rozmiar 512 bajtów. Wymaga to znaczącej interwencji mechanicznej.

Gdy jest używana pojedyncza transakcja, rekordy dziennika dla transakcji mogą być powiązane, a pojedynczy, większy zapis może służyć do opróżniania zebranych rekordów dziennika. Znacznie zmniejsza to interwencję mechaniczną.

Sprawdź instrukcje skojarzone z opróżnianiem dziennika, aby ustalić, czy można zmniejszyć wartość Opróżnione bajty dziennika/s. W poprzednim przykładzie użyto jednej transakcji. Jednak w wielu scenariuszach może to spowodować niepożądane zachowanie blokowania. Sprawdź projekt transakcji. Możesz użyć kodu podobnego do następującego kodu, aby uruchamiać partie w celu zmniejszenia częstych i małych działań opróżniania dziennika:

BEGIN TRAN
GO

INSERT INTO tblTest VALUES ('Test')
WHILE @@IDENTITY < 50
    BEGIN
        INSERT INTO tblTest VALUES ('Test')
  
        if(0 = cast(@@IDENTITY as int) % 10)
        BEGIN
            PRINT 'Commit tran batch'
            COMMIT TRAN
            BEGIN TRAN
        END
    END
GO

COMMIT TRAN
GO

SQL Server wymaga, aby systemy obsługiwać gwarantowane dostarczanie do stabilnego nośnika, zgodnie z opisem w dokumencie pobierania wymagań dotyczących przeglądu programu niezawodności we/wy SQL Server. Aby uzyskać więcej informacji na temat wymagań dotyczących danych wejściowych i wyjściowych aparatu bazy danych SQL Server, zobacz Wymagania dotyczące danych wejściowych/wyjściowych aparatu bazy danych firmy Microsoft SQL Server.