Opis algorytmów rejestrowania i przechowywania danych, które rozszerzają niezawodność danych w programie 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 algorytmy rejestrowania i danych programu Microsoft SQL Server 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 semantyki (ARIES), zobacz następujący dokument ACM Transactions on Database Systems (w obszarze Volume 17, Number 1, March 1992):

Link zewnętrzny: ARIES: metoda odzyskiwania transakcji obsługująca precyzyjne blokowanie i częściowe wycofywanie przy użyciu rejestrowania z wyprzedzeniem zapisu

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

Zalecamy zapoznanie się z następującymi artykułami w bazie wiedzy Microsoft Knowledge Base, aby uzyskać więcej informacji na temat buforowania i dyskusji na temat alternatywnego trybu awarii:

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

• Informacje o korzystaniu z pamięci podręcznych dysków z programem SQL Server, które powinien wiedzieć 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 zapisu z wyprzedzeniem (WAL)

Termin protokół jest doskonałym sposobem na opisanie wal. Jest to określony i zdefiniowany zestaw kroków implementacji niezbędnych do upewnienia się, że dane są przechowywane i wymieniane poprawnie i można je odzyskać do znanego stanu, jeśli wystąpi awaria. Podobnie jak sieć zawiera zdefiniowany protokół wymiany danych w spójny i chroniony sposób, dlatego też wal opisuje protokół ochrony danych.

Dokument ARIES definiuje plik WAL w następujący sposób:

Protokół WAL potwierdza, ż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 nienależącym do woluminu. Oznacza to, że system nie może zapisywać zaktualizowanej strony w wersji magazynu niezawolonego do 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 z wyprzedzeniem, zobacz temat Write-Ahead Transaction Log (Dziennik transakcji z wyprzedzeniem zapisu w usłudze SQL Server Books Online).

PROGRAM SQL Server i plik WAL

Program 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żmy 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". Mimo że ważne, blokowanie i rejestrowanie są oddzielnymi problemami podczas radzenia sobie z wal. W poprzednim przykładzie program SQL Server zazwyczaj przechowuje zatrzask na stronie 150 przez czas niezbędny 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 online książek programu SQL Server.

Patrząc na przykład bardziej szczegółowo, możesz zapytać, co się stanie po uruchomieniu procesów LazyWriter lub CheckPoint. Program SQL Server problemy ze wszystkimi odpowiednimi opróżnieniami do stabilnego magazynu dla transakcyjnych rekordów dziennika, które są skojarzone ze zanieczyszczoną i przypiętą stroną. 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.

PROGRAM SQL Server i stabilny magazyn

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

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

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

Wykrywanie rozdartej strony

Ta opcja umożliwia programowi SQL Server wykrywanie niekompletnych operacji we/wy spowodowanych awariami zasilania lub innymi awariami systemu. Jeśli to prawda, powoduje to przerzucanie bitów dla każdego sektora 512 bajtów 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 nieprawidłowym stanie, gdy strona jest później odczytywana przez program SQL Server, strona została zapisana niepoprawnie; Wykryto rozdartą stronę. Rozdarte strony są wykrywane podczas odzyskiwania, ponieważ każda strona, która została zapisana niepoprawnie, prawdopodobnie będzie odczytywana przez odzyskiwanie.

Chociaż strony bazy danych programu SQL Server mają rozmiar 8 KB, dyski wykonują operacje we/wy przy użyciu sektora 512 bajtów. W związku z tym 16 sektorów jest zapisywanych na stronie bazy danych. Rozdarta strona może wystąpić, jeśli system ulegnie awarii (na przykład z powodu awarii zasilania) między czasem zapisywania pierwszego sektora 512 bajtów na dysku i ukończenia 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 nie powiodła się.

Korzystając z pamięci podręcznych kontrolera dysku opartego 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 należy ustawiać rozdartego wykrywania strony na wartość "true", ponieważ nie jest to konieczne.

Parzystość dzienników

Sprawdzanie parzystości dzienników jest podobne do wykrywania rozdartej strony. Każdy sektor 512 bajtów zawiera bity parzystości. Te bity parzystości są zawsze zapisywane przy użyciu rekordu dziennika i oceniane podczas pobierania rekordu dziennika. Wymuszając zapisywanie dzienników na granicy 512 bajtów, program SQL Server może upewnić się, że operacje zatwierdzające są zapisywane w sektorach dysków fizycznych.

Wpływ na wydajność

Wszystkie wersje programu SQL Server otwierają pliki dziennika i danych przy użyciu funkcji Win32 CreateFile. Element członkowski dwFlagsAndAttributes zawiera FILE_FLAG_WRITE_THROUGH opcję po otwarciu przez program SQL Server.

FILE_FLAG_WRITE_THROUGH instruuje system do zapisu za pośrednictwem dowolnej pośredniej pamięci podręcznej i przechodzi bezpośrednio na dysk. System nadal może buforować operacje zapisu, ale nie może je z opóźnieniem 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 wbudowane pamięci podręczne o rozmiarze 512 KB, 1 MB lub większym. Jednak pamięci podręczne dysków zwykle polegają na pojemności, a nie na rozwiązaniu opartym na baterii. Te mechanizmy buforowania nie mogą zagwarantować zapisu w cyklu zasilania lub podobnym punkcie awarii. Gwarantują one tylko ukończenie operacji zapisu w sektorze. W szczególności dlatego rozdarte wykrywanie parzystości zapisu i dziennika zostało wbudowane w program SQL Server 7.0 i nowsze wersje. W miarę zwiększania rozmiaru dysków pamięć podręczna staje się większa i może uwidaczniać większe ilości danych podczas awarii.

Wielu dostawców sprzętu zapewnia rozwiązania kontrolera dysków oparte na baterii. Te pamięci podręczne kontrolera mogą przechowywać dane w pamięci podręcznej przez kilka dni, a nawet umożliwiają umieszczenie sprzętu buforowania na drugim komputerze. Po poprawnym przywróceniu zasilania niepisane dane są opróżniane przed zezwoleniem na dalszy dostęp do danych. Wiele z nich umożliwia ustanowienie procentu odczytu i pamięci podręcznej zapisu w celu uzyskania optymalnej wydajności. Niektóre zawierają duże obszary magazynowania pamięci. W rzeczywistości w przypadku określonego segmentu rynku niektórzy dostawcy sprzętu zapewniają wysokiej klasy systemy buforowania dysków oparte na 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ć prawdziwe 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, który jest wymagany do przenoszenia głowic napędu, szybkości spinowania i innych czynników ograniczających.

Porządkowanie sektora

Typową techniką służącą do zwiększania wydajności operacji we/wy jest porządkowanie sektorów. Aby uniknąć mechanicznego przenoszenia głowy, żądania odczytu/zapisu są sortowane, co pozwala na bardziej spójny ruch głowy do pobierania lub przechowywania danych.

Pamięć podręczna może jednocześnie przechowywać wiele żądań zapisu dzienników i danych. Protokół WAL i implementacja protokołu WAL programu SQL Server wymagają opróżniania zapisów dziennika do stabilnego magazynu przed wystawieniem zapisu strony. Jednak użycie pamięci podręcznej może zwrócić powodzenie z żądania zapisu dziennika bez zapisywania danych na dysku rzeczywistym (czyli zapisywanym w stabilnym magazynie). Może to prowadzić do wystawienia żądania zapisu strony danych przez program SQL Server.

W przypadku zaangażowania pamięci podręcznej zapisu dane są nadal uznawane za nietrwałe. Jednak z wywołania WriteFile interfejsu API Win32 dokładnie w jaki sposób program SQL Server widzi działanie, otrzymano kod powrotny zakończony powodzeniem. Program SQL Server lub dowolny proces korzystający z wywołania interfejsu API WriteFile może określić tylko, że dane zostały prawidłowo uzyskane w stabilnym magazynie.

W celach dyskusyjnych 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 serwera bazy danych należy wziąć pod uwagę wiele czynników. Najważniejsze z nich jest to: "Czy mój system zezwala na prawidłowe FILE_FLAG_WRITE_THROUGH możliwości?"

Testowanie wykazało, że wiele konfiguracji dysków może zawierać buforowanie zapisu bez odpowiedniej baterii Backup. Dyski SCSI, IDE i EIDE korzystają w pełni z pamięci podręcznych zapisu. Aby uzyskać więcej informacji na temat sposobu współdziałania dysków SSD z programem SQL Server, zobacz następujący blog inżynierów programu SQL Server CSS:

SQL Server i 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 narzędzia przesiadkowego znajdującego 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 mogą być często wyłączone przez system operacyjny. Jeśli masz jakiekolwiek pytanie, skontaktuj się z producentem napędu, aby uzyskać odpowiednie narzędzia.

Zapisywanie stosu pamięci podręcznej

Stos pamięci podręcznej zapisu jest podobny do porządkowania sektorów. Następująca 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 dane zapisu hosta w buforze do momentu ukończenia buforu lub zakończenia transferu hosta.

Zadanie zapisu dysku rozpoczyna przechowywanie danych hosta na dysku. Polecenia zapisu hosta są nadal akceptowane i dane przesyłane do buforu do momentu zapełnienie stosu poleceń zapisu lub bufor danych jest pełny. Dysk może zmienić kolejność poleceń zapisu w celu zoptymalizowania przepływności dysku.

Automatyczna alokacja zapisu (AWR)

Inną typową techniką używaną do ochrony danych jest wykrywanie złych sektorów podczas manipulowania danymi. Następujące 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 przeniesiony do puli alternatywnych sektorów znajdujących się na końcu dysku. Po reallocation zadanie zapisu dysku będzie kontynuowane do momentu ukończenia.

Może to być zaawansowana funkcja, jeśli kopia zapasowa baterii jest dostarczana dla pamięci podręcznej. Zapewnia to odpowiednią modyfikację po ponownym uruchomieniu. Lepiej jest wykryć błędy dysku, ale bezpieczeństwo danych protokołu WAL wymagałoby wykonania tego 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 można było poprawnie przerwać transakcję, administrator może zostać powiadomiony i wykonać 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ąć, aby zapewnić bezpieczeństwo danych.

• Zawsze dobrym pomysłem jest upewnienie się, że strategia tworzenia kopii zapasowych jest wystarczająca do odzyskania sprawności po katastrofalnym niepowodzeniu. Magazyn poza siedzibą firmy 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, złe sektory, złe dyski, awaria systemu, blokady, skok zasilania itd.).
• Upewnij się, że urządzenie buforujące:
  • Ma zintegrowaną kopię zapasową baterii
  • Możliwość ponownego zapisywania przy zasilaniu
  • Może być w pełni wyłączony, jeśli jest to konieczne
  • Obsługuje ponowne mapowanie sektora w czasie rzeczywistym
• Włącz wykrywanie rozdartych stron. (Ma to niewielki wpływ na wydajność).
• Skonfiguruj dyski RAID umożliwiające wymianę gorącą nieprawidłowego dysku, jeśli jest to możliwe.
• Użyj nowszych kontrolerów buforowania, które pozwalają dodać więcej 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 poprawnie obsługiwane. W wielu sytuacjach należy wyłączyć buforowanie zapisu dysku.

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

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

Jak za pomocą narzędzia SQLIOSim symulować aktywność programu SQL Server w podsystemie dysku

Wielu producentów komputerów zamawia dyski, wyłączając pamięć podręczną zapisu. Jednak testowanie pokazuje, że może to nie zawsze być możliwe. Dlatego zawsze testuje się całkowicie.

Urządzenia danych

We wszystkich sytuacjach bez rejestrowania program SQL Server będzie wymagał opróżniania tylko rekordów dziennika. W przypadku wykonywania nielogowanych operacji strony danych muszą być również opróżniane do stabilnego magazynu; w przypadku awarii nie ma żadnych pojedynczych rekordów dziennika w celu ponownego wygenerowania akcji.

Strony danych mogą pozostać w pamięci podręcznej do momentu opróżnienia ich do stabilnego magazynu przez proces LazyWriter lub CheckPoint. Korzystając z protokołu WAL, upewnij się, że rekordy dziennika są poprawnie przechowywane, upewnia się, że odzyskiwanie może odzyskać stronę danych ze znanym stanem.

Nie oznacza to, że zaleca się umieszczenie plików danych na dysku buforowanym. Gdy program SQL Server opróżni strony danych do stabilnego magazynu, rekordy dziennika mogą być obcięte z dziennika transakcji. Jeśli strony danych są przechowywane w nietrwałej pamięci podręcznej, możliwe jest obcięcie rekordów 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 mieszczą się w stabilnym magazynie.

Zwiększenie wydajności

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

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

Aby rozwiązać problem różnicy w wydajności, metoda, która ma być stosowana, jest jasna: "Rozwiąż problem z szybkością 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ć pożądany wzrost wydajności, jednocześnie zapewniając integralność danych.

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

Testowanie pokazuje, że wysokie działanie zapisu, które są mniejsze niż 512 KB lub większe niż 2 MB, może spowodować 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 testu dla programu 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 INSERT serii operacji w jedną transakcję jest uruchamiany w około czterech sekundach we wszystkich konfiguracjach. Jest to spowodowane liczbą opróżnień dzienników, które są wymagane. Jeśli nie utworzysz jednej transakcji, każda INSERT jest przetwarzana jako oddzielna transakcja. W związku z tym wszystkie rekordy dziennika transakcji muszą być opróżniane. Każde opróżnienie ma rozmiar 512 bajtów. Wymaga to znacznej interwencji napędu mechanicznego.

Gdy jest używana jedna 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óżnieniami dziennika, aby określić, czy można zmniejszyć wartość Opróżniane/s bajtów dziennika. W poprzednim przykładzie użyto pojedynczej transakcji. Jednak w wielu scenariuszach może to spowodować niepożądane zachowanie blokujące. Zbadaj projekt transakcji. Możesz użyć kodu podobnego do poniższego kodu, aby uruchomić partie, aby zmniejszyć częste i małe działanie 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

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