Beschrijving van algoritmen voor logboekregistratie en gegevensopslag die de betrouwbaarheid van gegevens in SQL Server

Oorspronkelijke productversie: SQL Server 2014, SQL Server 2012, SQL Server 2008, SQL Server 2005
Origineel KB-nummer: 230785

Samenvatting

In dit artikel wordt beschreven hoe Microsoft SQL Server logboekregistratie- en gegevensalgoritmen de betrouwbaarheid en integriteit van gegevens vergroten.

Zie het volgende document ACM Transactions on Database Systems (onder Volume 17, Number 1, maart 1992) voor meer informatie over de onderliggende concepten van de engines en over Algorithm for Recovery and Isolation Exploiting Semantics (ARIES):

Externe koppeling: ARIES: een transactieherstelmethode die ondersteuning biedt voor Fine-Granularity vergrendelen en gedeeltelijke terugdraaibewerkingen met behulp van Write-Ahead-logboekregistratie

In het document worden de SQL Server technieken beschreven om de betrouwbaarheid en integriteit van gegevens uit te breiden met betrekking tot fouten.

U wordt aangeraden de volgende artikelen in de Microsoft Knowledge Base te lezen voor meer informatie over discussies over caching en alternatieve foutmodus:

• Beschrijving van schijfcontrollers in cache opslaan in SQL Server

• Informatie over het gebruik van schijfcaches met SQL Server die elke databasebeheerder moet kennen

Termen die in dit artikel worden gebruikt

Voordat we beginnen met de diepgaande discussie, worden enkele van de termen die in dit artikel worden gebruikt, gedefinieerd in de volgende tabel.

Write-Ahead Protocol voor logboekregistratie (WAL)

De term protocol is een uitstekende manier om WAL te beschrijven. Het is een specifieke en gedefinieerde set implementatiestappen die nodig zijn om ervoor te zorgen dat gegevens correct worden opgeslagen en uitgewisseld en kunnen worden hersteld naar een bekende status als er een fout optreedt. Net zoals een netwerk een gedefinieerd protocol bevat om gegevens op een consistente en beveiligde manier uit te wisselen, zo beschrijft de WAL ook het protocol om gegevens te beveiligen.

Het ARIES-document definieert de WAL als volgt:

Het WAL-protocol beweert dat de logboekrecords die wijzigingen in bepaalde gegevens vertegenwoordigen zich al in stabiele opslag moeten bevinden voordat de gewijzigde gegevens de vorige versie van de gegevens in niet-onvolatile opslag mogen vervangen. Dat wil dus dat het systeem geen bijgewerkte pagina mag schrijven naar de niet-compatibele opslagversie van de pagina totdat ten minste de gedeelten van de logboekrecords, waarin de updates van de pagina worden beschreven, naar stabiele opslag zijn geschreven.

Zie het onderwerp Write-Ahead Transaction Log op SQL Server Books Online voor meer informatie over write-ahead-logboekregistratie.

SQL Server en de WAL

SQL Server maakt gebruik van het WAL-protocol. Om ervoor te zorgen dat een transactie correct wordt doorgevoerd, moeten alle logboekrecords die aan de transactie zijn gekoppeld, worden beveiligd in stabiele opslag.

Bekijk het volgende specifieke voorbeeld om deze situatie te verduidelijken.

BEGIN TRANSACTION
 INSERT INTO tblTest VALUES (1)
COMMIT TRANSACTION

Vervolgens kunt u de activiteit opsplitsen in eenvoudige logboekregistratiestappen, zoals beschreven in de volgende tabel.

Laat u niet verwarren door de termen 'vergrendelen' en 'logboekregistratie'. Hoewel belangrijk, zijn vergrendeling en logboekregistratie afzonderlijke problemen wanneer u met de WAL te maken hebt. In het vorige voorbeeld bevat SQL Server over het algemeen de vergrendeling op pagina 150 gedurende de tijd die nodig is om de fysieke invoegwijzigingen op de pagina uit te voeren, niet de hele tijd van de transactie. Het juiste vergrendelingstype wordt ingesteld om de rij, het bereik, de pagina of de tabel zo nodig te beveiligen. Raadpleeg de secties SQL Server Boeken Online vergrendelen voor meer informatie over vergrendelingstypen.

Als u het voorbeeld gedetailleerder bekijkt, kunt u zich afvragen wat er gebeurt wanneer de LazyWriter- of CheckPoint-processen worden uitgevoerd. SQL Server alle geschikte flushes naar stabiele opslag voor transactionele logboekrecords die zijn gekoppeld aan de vuile en vastgemaakte pagina. Dit zorgt ervoor dat de gegevenspagina van het WAL-protocol nooit naar stabiele opslag kan worden geschreven totdat de bijbehorende transactionele logboekrecords zijn leeggemaakt.

SQL Server en stabiele opslag

SQL Server verbetert de bewerkingen van logboek- en gegevenspagina's door de kennis van de grootte van de schijfsector (meestal 4096 bytes of 512 bytes) op te nemen.

Als u de ACID-eigenschappen van een transactie wilt behouden, moet de SQL Server rekening houden met foutpunten. Tijdens een storing garanderen veel schijfspecificaties slechts een beperkt aantal schrijfbewerkingen in de sector. De meeste specificaties garanderen voltooiing van één sector schrijven wanneer er een fout optreedt.

SQL Server gebruikt gegevenspagina's van 8 kB en het logboek (indien leeggemaakt) op veelvouden van de sectorgrootte. (De meeste schijfstations gebruiken 512 bytes als de standaardsectorgrootte.) Als er een fout optreedt, kan SQL Server rekening houden met schrijfbewerkingen die groter zijn dan een sector door gebruik te maken van logboekpariteit en schrijftechnieken voor gescheurde schrijfbewerkingen.

Detectie van gescheurde pagina's

Met deze optie kunnen SQL Server onvolledige I/O-bewerkingen detecteren die worden veroorzaakt door stroomstoringen of andere systeemstoringen. Als dit waar is, wordt er een beetje gespiegeld voor elke sector van 512 bytes in een databasepagina van 8 kilobyte (KB) wanneer de pagina naar de schijf wordt geschreven. Als een bit de verkeerde status heeft wanneer de pagina later wordt gelezen door SQL Server, is de pagina onjuist geschreven. Er wordt een gescheurde pagina gedetecteerd. Er worden tijdens het herstel verscheurde pagina's gedetecteerd omdat een pagina die onjuist is geschreven, waarschijnlijk wordt gelezen door herstel.

Hoewel SQL Server databasepagina's 8 kB groot zijn, voeren schijven I/O-bewerkingen uit met behulp van een sector van 512 bytes. Daarom worden per databasepagina 16 sectoren geschreven. Een gescheurde pagina kan optreden als het systeem uitvalt (bijvoorbeeld vanwege een stroomstoring) tussen het moment dat het besturingssysteem de eerste 512-bytessector naar de schijf schrijft en de voltooiing van de I/O-bewerking van 8 kB. Als de eerste sector van een databasepagina is geschreven vóór de fout, wordt de databasepagina op schijf weergegeven als bijgewerkt, hoewel dit mogelijk niet is gelukt.

Door gebruik te maken van schijfcontrollercaches met batterij-ondersteuning, kunt u ervoor zorgen dat gegevens naar de schijf worden geschreven of helemaal niet worden geschreven. Stel in dit geval de detectie van gescheurde pagina's niet in op 'true' omdat dit niet nodig is.

Logboekpariteit

Controle van logboekpariteit is vergelijkbaar met detectie van gescheurde pagina's. Elke sector van 512 bytes bevat pariteitsbits. Deze pariteitsbits worden altijd geschreven met de logboekrecord en geëvalueerd wanneer de logboekrecord wordt opgehaald. Door schrijfbewerkingen in logboeken op een grens van 512 bytes af te dwingen, kunt SQL Server ervoor zorgen dat doorvoerbewerkingen naar de fysieke schijfsectoren worden geschreven.

Invloed op de prestaties

Alle versies van SQL Server de logboek- en gegevensbestanden openen met behulp van de win32-functie CreateFile. Het lid dwFlagsAndAttributes bevat de FILE_FLAG_WRITE_THROUGH optie wanneer ze worden geopend door SQL Server.

FILE_FLAG_WRITE_THROUGH geeft het systeem de opdracht om door een tussenliggende cache te schrijven en rechtstreeks naar de schijf te gaan. Het systeem kan schrijfbewerkingen nog steeds in de cache opslaan, maar kan ze niet lui leegmaken.

De FILE_FLAG_WRITE_THROUGH optie zorgt ervoor dat wanneer een schrijfbewerking een geslaagde voltooiing retourneert, de gegevens correct worden opgeslagen in stabiele opslag. Dit komt overeen met het WAL-protocol dat zorgt voor de gegevens.

Veel schijfstations (SCSI en IDE) bevatten onboard-caches van 512 kB, 1 MB of groter. De stationscaches zijn echter meestal afhankelijk van een condensator en niet van een oplossing met batterij-ondersteuning. Deze mechanismen voor opslaan in cache kunnen schrijfbewerkingen in een stroomcyclus of een vergelijkbaar storingspunt niet garanderen. Ze garanderen alleen de voltooiing van de schrijfbewerkingen in de sector. Dit is specifiek de reden waarom de detectie van torn schrijf- en logboekpariteit is ingebouwd in SQL Server 7.0 en latere versies. Naarmate de stations steeds groter worden, worden de caches groter en kunnen ze grotere hoeveelheden gegevens beschikbaar maken tijdens een storing.

Veel hardwareleveranciers bieden oplossingen voor schijfcontrollers met batterij-ondersteuning. Deze controllercaches kunnen de gegevens enkele dagen in de cache bewaren en zelfs toestaan dat de cachehardware op een tweede computer wordt geplaatst. Wanneer de stroom correct is hersteld, worden de ongeschreven gegevens leeggemaakt voordat verdere gegevenstoegang wordt toegestaan. Veel van hen maken het mogelijk om een percentage lees- en schrijfcache tot stand te brengen voor optimale prestaties. Sommige bevatten grote opslagruimten voor geheugen. Voor een specifiek segment van de markt bieden sommige hardwareleveranciers geavanceerde schijfcachingcontrollersystemen met 6 GB cache. Deze kunnen de databaseprestaties aanzienlijk verbeteren.

Geavanceerde caching-implementaties verwerken de FILE_FLAG_WRITE_THROUGH aanvraag door de cache van de controller niet uit te schakelen, omdat ze echte herschrijfmogelijkheden kunnen bieden in het geval van een systeemherstel, stroomstoring of een ander storingspunt.

I/O-overdrachten zonder het gebruik van een cache kunnen langer duren vanwege de mechanische tijd die nodig is om de stationskoppen, spinsnelheden en andere beperkende factoren te verplaatsen.

Sectorvolgorde

Een veelgebruikte techniek voor het verbeteren van de I/O-prestaties is het ordenen van sectoren. Om mechanische hoofdverplaatsing te voorkomen, worden de lees-/schrijfaanvragen gesorteerd, waardoor een meer consistente beweging van het hoofd mogelijk is om gegevens op te halen of op te slaan.

De cache kan meerdere logboek- en gegevensschrijfaanvragen tegelijk bevatten. Voor het WAL-protocol en de SQL Server implementatie van het WAL-protocol moeten de logboekschrijfbewerkingen naar stabiele opslag worden leeggemaakt voordat het schrijven van pagina's kan worden uitgegeven. Het gebruik van de cache kan echter een geslaagde aanvraag voor het schrijven van logboeken retourneren zonder dat de gegevens naar het werkelijke station worden geschreven (dat wil gezegd, geschreven naar stabiele opslag). Dit kan ertoe leiden dat SQL Server de schrijfaanvraag voor de gegevenspagina uitgeeft.

Met de betrokkenheid van de schrijfcache worden de gegevens nog steeds beschouwd als in vluchtige opslag. Van de Win32 API WriteFile-aanroep is echter een geslaagde retourcode verkregen, precies zoals SQL Server de activiteit ziet. SQL Server of een proces dat gebruikmaakt van de WriteFile-API-aanroep, kan alleen bepalen of de gegevens op de juiste wijze stabiele opslag hebben verkregen.

Voor discussiedoeleinden wordt ervan uitgegaan dat alle sectoren van de gegevenspagina zijn gesorteerd om te schrijven vóór de sectoren van de overeenkomende logboekrecords. Dit is onmiddellijk in strijd met het WAL-protocol. De cache schrijft een gegevenspagina vóór de logboekrecords. Tenzij de cache volledig wordt ondersteund door de batterij, kan een fout catastrofale resultaten veroorzaken.

Wanneer u de optimale prestatiefactoren voor een databaseserver evalueert, zijn er veel factoren om rekening mee te houden. De belangrijkste hiervan is: 'Staat mijn systeem geldige FILE_FLAG_WRITE_THROUGH mogelijkheden toe?'

Tests hebben aangetoond dat veel schijfconfiguraties schrijfcache kunnen bevatten zonder de juiste back-up van de batterij. SCSI-, IDE- en EIDE-stations maken optimaal gebruik van schrijfcaches. Zie het volgende blogartikel over CSS SQL Server Engineers voor meer informatie over hoe SSDs samenwerken met SQL Server:

SQL Server en SSDs - Leernotities van RDORR - Deel 1

In veel configuraties is de enige manier om de schrijfcache van een IDE- of EIDE-station correct uit te schakelen met behulp van een specifiek hulpprogramma van de fabrikant of door jumpers op het station zelf te gebruiken. Neem contact op met de fabrikant van het station om ervoor te zorgen dat de schrijfcache is uitgeschakeld voor het station zelf.

SCSI-stations hebben ook schrijfcaches. Deze caches kunnen echter meestal worden uitgeschakeld door het besturingssysteem. Als er een vraag is, neemt u contact op met de fabrikant van het station voor de juiste hulpprogramma's.

Stacking van schrijfcache

Write Cache Stacking is vergelijkbaar met Sector Ordering. De volgende definitie is rechtstreeks afkomstig van de website van een toonaangevende fabrikant van IDE-stations:

Normaal gesproken is deze modus actief. De schrijfcachemodus accepteert dat de host gegevens naar de buffer schrijft totdat de buffer vol is of de hostoverdracht is voltooid.

Een schijfschrijftaak begint met het opslaan van de hostgegevens op schijf. Schrijfopdrachten van de host blijven geaccepteerd en gegevens worden overgedragen naar de buffer totdat de schrijfopdrachtstack vol is of de gegevensbuffer vol is. Het station kan de volgorde van schrijfopdrachten wijzigen om de doorvoer van het station te optimaliseren.

Automatische hertoewijzing van schrijfbewerkingen (AWR)

Een andere veelgebruikte techniek die wordt gebruikt om gegevens te beschermen, is het detecteren van slechte sectoren tijdens gegevensmanipulatie. De volgende uitleg is afkomstig van de website van een toonaangevende fabrikant van IDE-stations:

Deze functie maakt deel uit van de schrijfcache en vermindert het risico op gegevensverlies tijdens uitgestelde schrijfbewerkingen. Als er een schijffout optreedt tijdens het schrijfproces van de schijf, stopt de schijftaak en wordt de verdachte sector opnieuw toegewezen aan een groep alternatieve sectoren die zich aan het einde van het station bevinden. Na de hertoewijzing gaat de schrijftaak van de schijf door totdat deze is voltooid.

Dit kan een krachtige functie zijn als back-up van de batterij wordt geleverd voor de cache. Dit biedt de juiste aanpassing bij het opnieuw opstarten. Het verdient de voorkeur om de schijffouten te detecteren, maar de gegevensbeveiliging van het WAL-protocol vereist opnieuw dat dit in realtime gebeurt en niet op een uitgestelde manier. Binnen de WAL-parameters kan de AWR-techniek geen rekening houden met een situatie waarin het schrijven van een logboek mislukt vanwege een sectorfout, maar het station vol is. De database-engine moet onmiddellijk op de hoogte zijn van de fout, zodat de transactie correct kan worden afgebroken, de beheerder kan worden gewaarschuwd en de juiste stappen kan worden genomen om de gegevens te beveiligen en de situatie met mediafouten te corrigeren.

Gegevensbeveiliging

Er zijn verschillende voorzorgsmaatregelen die een databasebeheerder moet nemen om de veiligheid van de gegevens te waarborgen.

• Het is altijd een goed idee om ervoor te zorgen dat uw back-upstrategie voldoende is om te herstellen van een catastrofale fout. Opslag op locatie en andere voorzorgsmaatregelen zijn passend.
• Test de herstelbewerking van de database regelmatig in een secundaire database of testdatabase.
• Zorg ervoor dat alle caching-apparaten alle storingssituaties kunnen verwerken (stroomuitval, slechte sectoren, slechte stations, systeemstoringen, vergrendelingen, pieken in de voeding, enzovoort).
• Zorg ervoor dat uw cacheapparaat:
  • Heeft geïntegreerde batterijback-up
  • Schrijfbewerkingen kunnen opnieuw worden uitgegeven bij het inschakelen
  • Kan volledig worden uitgeschakeld als dat nodig is
  • Verwerkt het opnieuw toepassen van slechte sector in realtime
• Detectie van gescheurde pagina's inschakelen. (Dit heeft weinig invloed op de prestaties.)
• Configureer RAID-stations die een hot swap van een ongeldig schijfstation mogelijk maken, indien mogelijk.
• Gebruik nieuwere cachecontrollers waarmee u meer schijfruimte kunt toevoegen zonder het besturingssysteem opnieuw op te starten. Dit kan een ideale oplossing zijn.

Stations testen

Als u uw gegevens volledig wilt beveiligen, moet u ervoor zorgen dat alle gegevens in de cache correct worden verwerkt. In veel gevallen moet u de schrijfcache van het schijfstation uitschakelen.

Microsoft heeft tests uitgevoerd op verschillende SCSI- en IDE-stations met behulp van het SQLIOSim hulpprogramma. Dit hulpprogramma simuleert zware asynchrone lees-/schrijfactiviteit naar een gesimuleerd gegevensapparaat en logboekapparaat. Testprestatiestatistieken tonen de gemiddelde schrijfbewerkingen per seconde tussen 50 en 70 voor een station met uitgeschakelde schrijfcache en een RPM-bereik tussen 5.200 en 7.200.

Zie het volgende artikel in de SQLIOSim Microsoft Knowledge Base voor meer informatie over het hulpprogramma:

Het hulpprogramma SQLIOSim gebruiken om SQL Server activiteit op een schijfsubsysteem te simuleren

Veel computerfabrikanten bestellen de stations door de schrijfcache uit te schakelen. Uit tests blijkt echter dat dit niet altijd het geval is. Test daarom altijd volledig.

Gegevensapparaten

In alle behalve niet-geregistreerde situaties hoeft SQL Server alleen de logboekrecords te worden leeggemaakt. Bij het uitvoeren van niet-geregistreerde bewerkingen moeten de gegevenspagina's ook worden leeggemaakt naar stabiele opslag; er zijn geen afzonderlijke logboekrecords om de acties opnieuw te genereren in het geval van een fout.

De gegevenspagina's kunnen in de cache blijven totdat ze door het LazyWriter- of CheckPoint-proces worden leeggemaakt naar stabiele opslag. Als u het WAL-protocol gebruikt om ervoor te zorgen dat de logboekrecords correct zijn opgeslagen, zorgt u ervoor dat herstel een gegevenspagina kan herstellen naar een bekende status.

Dit betekent niet dat het raadzaam is om gegevensbestanden op een station in de cache te plaatsen. Wanneer de SQL Server de gegevenspagina's leegzet naar stabiele opslag, kunnen de logboekrecords worden afgekapt uit het transactielogboek. Als de gegevenspagina's zijn opgeslagen in een vluchtige cache, is het mogelijk om logboekrecords af tekappen die worden gebruikt om een pagina te herstellen in het geval van een fout. Zorg ervoor dat zowel uw gegevens- als logboekapparaten op de juiste manier stabiele opslag bieden.

Prestaties verbeteren

De eerste vraag die bij u kan komen, is: 'Ik heb een IDE-station dat in de cache is opgeslagen. Maar toen ik het deed, werden mijn prestaties minder dan verwacht. Waarom?

Veel van de IDE-stations die door Microsoft zijn getest, worden uitgevoerd met 5.200 RPM en de SCSI-stations met 7.200 RPM. Wanneer u de schrijfcache van het IDE-station uitschakelt, kunnen de mechanische prestaties een factor worden.

Om het prestatieverschil te verhelpen, is de te volgen methode duidelijk: 'Adres de transactiesnelheid'.

Veel OLTP-systemen (Online Transaction Processing) vereisen een hoge transactiesnelheid. Voor deze systemen kunt u overwegen een cachecontroller te gebruiken die een schrijfcache op de juiste manier kan ondersteunen en de gewenste prestatieverbeteringen kan bieden, terwijl de gegevensintegriteit nog steeds wordt gewaarborgd.

Om belangrijke prestatiewijzigingen te zien die optreden in SQL Server op een cachestation, is de transactiesnelheid verhoogd met behulp van kleine transacties.

Testen toont aan dat een hoge schrijfactiviteit van buffers van minder dan 512 kB of groter dan 2 MB trage prestaties kan veroorzaken.

Bekijk het volgende voorbeeld:

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')

Hieronder volgen voorbeeldtestresultaten voor 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

Het proces waarbij de hele reeks INSERT bewerkingen in één transactie wordt verpakt, wordt in alle configuraties in ongeveer vier seconden uitgevoerd. Dit komt door het aantal logboeken dat is vereist. Als u geen enkele transactie maakt, wordt elke INSERT transactie verwerkt als een afzonderlijke transactie. Daarom moeten alle logboekrecords voor de transactie worden leeggemaakt. Elke flush is 512 bytes groot. Dit vereist een aanzienlijke mechanische aandrijvingsingreep.

Wanneer één transactie wordt gebruikt, kunnen de logboekrecords voor de transactie worden gebundeld en kan één grotere schrijfbewerking worden gebruikt om de verzamelde logboekrecords leeg te maken. Dit vermindert de mechanische interventie aanzienlijk.

Bekijk de instructies die zijn gekoppeld aan het leegmaken van logboeken om te bepalen of de waarde logbytes flushed/sec kan worden verminderd. In het vorige voorbeeld is één transactie gebruikt. In veel scenario's kan dit echter ongewenst vergrendelingsgedrag veroorzaken. Bekijk het ontwerp van de transactie. U kunt code gebruiken die vergelijkbaar is met de volgende code om batches uit te voeren om de frequente en kleine activiteit voor het leegmaken van logboeken te verminderen:

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 vereist dat systemen ondersteuning bieden voor gegarandeerde levering aan stabiele media, zoals beschreven in het downloaddocument SQL Server I/O-betrouwbaarheidsprogramma beoordelingsvereisten. Zie Invoer-/uitvoervereisten voor Microsoft SQL Server Database Engine voor meer informatie over de invoer- en uitvoervereisten voor de SQL Server database-engine.