Alleen-lezen werkbelasting offloaden naar de secundaire replica van een Always On-beschikbaarheidsgroep

Van toepassing op:SQL Server

De Always On Beschikbaarheidsgroepen met actieve secundaire mogelijkheden bieden ondersteuning voor alleen-lezentoegang tot een of meer secundaire replica's (leesbare secundaire replica's). Een leesbare secundaire replica kan zich bevinden in de mogelijkheid van synchroon toepassen of in de mogelijkheid van asynchroon toepassen. Een leesbare secundaire replica biedt alleen-lezentoegang tot alle secundaire databases. Leesbare secundaire databases zijn echter niet ingesteld op alleen-lezen. Ze zijn dynamisch. Een bepaalde secundaire database wordt gewijzigd als wijzigingen in de bijbehorende primaire database worden toegepast op de secundaire database. Voor een typische secundaire replica zijn de gegevens, inclusief tabellen die zijn geoptimaliseerd voor duurzaam geheugen, in de secundaire databases nagenoeg in realtime. Bovendien worden indexen in volledige tekst gesynchroniseerd met de secundaire databases. In veel gevallen duurt de gegevenslatentie tussen een primaire database en de bijbehorende secundaire database slechts enkele seconden.

Beveiligingsinstellingen die voorkomen in de primaire databases, worden bewaard in de secundaire databases. Dit omvat gebruikers, databaserollen en toepassingenrollen, samen met hun respectieve machtigingen en TDE (Transparent Data Encryption), indien ingeschakeld voor de primaire database.

Always On-beschikbaarheidsgroepen ondersteunen ook het herrouteren van verbindingsaanvragen met leesintentie naar een leesbare secundaire replica (alleen-lezende routering). Zie Een listener gebruiken om verbinding te maken met een Read-Only secundaire replica (Read-Only routering) voor meer informatie over alleen-lezenroutering.

Voordelen

Het omsturen van alleen-lezen verbindingen naar leesbare secundaire replica's biedt de volgende voordelen:

• Offload uw secundaire alleen-lezen workloads van uw primaire replica, waardoor de resources worden bespaard voor uw essentiële workloads. Als u een bedrijfskritische leesbelasting of een workload hebt die geen latentie kan verdragen, moet u deze op de primaire uitvoeren.

• Verbetert uw rendement op investering voor de systemen die leesbare secundaire replica's hosten.

Daarnaast bieden leesbare secundaire secundaire bestanden als volgt robuuste ondersteuning voor alleen-lezenbewerkingen:

• Automatische tijdelijke statistieken voor leesbare secundaire databases optimaliseren alleen-lezende query's op op schijf gebaseerde tabellen. Voor tabellen die zijn geoptimaliseerd voor geheugen, worden de ontbrekende statistieken automatisch gemaakt. Er is echter geen automatische update van verouderde statistieken. U moet de statistieken op de primaire replica handmatig bijwerken. Zie Statistieken voor Read-Only Access-databases verderop in dit onderwerp voor meer informatie.

• Alleen-lezen werklasten voor schijfgebaseerde tabellen maken gebruik van rijversiebeheer om blokkades bij secundaire databases te verwijderen. Alle query's die op de secundaire databases worden uitgevoerd, worden automatisch toegewezen aan transactieniveau voor isolatie van momentopnamen, zelfs wanneer andere transactieisolatieniveaus expliciet zijn ingesteld. Ook worden alle vergrendelingshints genegeerd. Dit elimineert conflicten tussen lezer en schrijver.

• Alleen-lezen workloads voor duurzame tabellen geoptimaliseerd voor geheugen hebben toegang tot de gegevens op exact dezelfde manier als ze worden benaderd op de primaire database, met behulp van systeemeigen opgeslagen procedures of SQL-interoperabiliteit met dezelfde beperkingen van het transactieisolatieniveau (zie Isolatieniveaus in de database-engine). Rapportagebelastingen of alleen-lezen queries die op de primaire replica worden uitgevoerd, kunnen zonder aanpassingen worden uitgevoerd op de secundaire replica. Op dezelfde manier kunnen een rapportagewerkbelasting of alleen-lezen queries die op een secundaire replica worden uitgevoerd, zonder wijzigingen op de primaire replica worden uitgevoerd. Net als bij tabellen op basis van schijven worden alle query's die worden uitgevoerd op de secundaire databases automatisch toegewezen aan transactieniveau voor isolatie van momentopnamen, zelfs wanneer andere transactieisolatieniveaus expliciet zijn ingesteld.

• DML-bewerkingen zijn toegestaan op tabelvariabelen voor zowel schijf-gebaseerde als geheugen-geoptimaliseerde tabeltypen op de secundaire replica.

Vereisten voor de beschikbaarheidsgroep

• Secundaire replica's die leesbaar zijn (vereist)

  De databasebeheerder moet een of meer replica's configureren, zodat bij uitvoering onder de secundaire rol alle verbindingen (alleen voor alleen-lezentoegang) of alleen leesintentieverbindingen zijn toegestaan.

  Opmerking

  Desgewenst kan de databasebeheerder een van de beschikbaarheidsreplica's configureren om alleen-lezenverbindingen uit te sluiten wanneer deze wordt uitgevoerd onder de primaire rol.

  Zie Over clientverbindingstoegang tot beschikbaarheidsreplica's (SQL Server) voor meer informatie.

  Waarschuwing

  Alleen replica's die zich in dezelfde primaire build van SQL Server bevinden, kunnen worden gelezen. Zie Rolling upgrade basisbeginselen voor meer informatie.

• Listener voor beschikbaarheidsgroep

  Om alleen-lezenroutering te ondersteunen, moet een beschikbaarheidsgroep beschikken over een beschikbaarheidsgroepluisteraar. De alleen-lezende cliënt moet zijn verbindingsverzoeken naar deze listener sturen, en de verbindingsreeks van de cliënt moet de toepassingsintentie als alleen lezen specificeren. Dat wil zeggen, ze moeten verbindingsverzoeken met leesintentie zijn.

• Alleen-lezen-routering

  Alleen-lezenroutering verwijst naar de mogelijkheid van SQL Server om binnenkomende verbindingsaanvragen voor leesintentie te routeren die naar een listener van een beschikbaarheidsgroep gericht zijn, naar een beschikbare leesbare secundaire replica. De vereisten voor alleen-lezen routering zijn als volgt:

  • Voor ondersteuning van alleen-lezen-routering is een alleen-lezen-routerings-URL vereist voor een leesbare secundaire kopie. Deze URL wordt alleen van kracht wanneer de lokale replica wordt uitgevoerd onder de secundaire rol. De alleen-lezen routerings-URL moet voor elke replica afzonderlijk worden opgegeven, indien nodig. Elke alleen-lezen-routerings-URL wordt gebruikt voor het routeren van verbindingsaanvragen voor leesintenties naar een specifieke leesbare secundaire replica. Meestal krijgt elke leesbare secundaire replica een read-only routerings-URL toegewezen.

  • Elke beschikbaarheidsreplica die ondersteuning biedt voor alleen-lezenroutering wanneer het de primaire replica is, vereist een alleen-lezen routeringslijst. Een bepaalde lijst met alleen-lezenroutering wordt alleen van kracht wanneer de lokale replica actief is in de primaire rol. Deze lijst moet indien nodig per replica worden opgegeven. Normaal gesproken bevat elke alleen-lezen routeringslijst elke alleen-lezen routerings-URL, met de URL van de lokale replica aan het einde van de lijst.

    Opmerking

    Verbindingsaanvragen voor leesgerichte toegang kunnen worden verdeeld over reproducties. Zie Taakverdeling configureren voor alleen-lezen replica's voor meer informatie.

  Zie Read-Only Routering configureren voor een beschikbaarheidsgroep (SQL Server) voor meer informatie.

Beperkingen en beperkingen

Sommige bewerkingen worden niet volledig ondersteund, zoals volgt:

• Zodra een leesbare replica is ingeschakeld voor lezen, kan deze beginnen met het accepteren van verbindingen met de secundaire databases. Als er echter actieve transacties bestaan in een primaire database, zijn de rijversies niet volledig beschikbaar in de bijbehorende secundaire database. Actieve transacties die aanwezig waren op de primaire replica toen de secundaire replica werd geconfigureerd, moeten worden bevestigd of teruggedraaid. Totdat dit proces is voltooid, is de mapping van het transactie-isolatieniveau op de secundaire databank onvolledig, en worden query's tijdelijk geblokkeerd.

  Waarschuwing

  Het uitvoeren van lange transacties is van invloed op het aantal opgeslagen versies, zowel voor schijf-gebaseerde als geheugen-geoptimaliseerde tabellen.

• Op een secundaire database met tabellen die zijn geoptimaliseerd voor geheugen, hoewel rijversies altijd worden gegenereerd voor tabellen die zijn geoptimaliseerd voor geheugen, worden query's geblokkeerd totdat alle actieve transacties die aanwezig waren in de primaire replica toen de secundaire replica was ingeschakeld voor lezen voltooid. Dit zorgt ervoor dat zowel op schijf gebaseerde als in geheugen geoptimaliseerde tabellen tegelijkertijd beschikbaar zijn voor de rapportageworkload en read-only queries.

• Het bijhouden van wijzigingen en het vastleggen van wijzigingengegevens wordt niet ondersteund voor secundaire databases die deel uitmaken van een leesbare secundaire replica:

  • Wijzigingen bijhouden is expliciet uitgeschakeld voor secundaire databases.

  • Change Data Capture kan niet alleen worden ingeschakeld op een secundaire replicadatabase. Change Data Capture kan worden ingeschakeld voor de primaire replicadatabase en de wijzigingen kunnen worden gelezen uit de CDC-tabellen met behulp van de functies in de secundaire replicadatabase.

• Omdat leesbewerkingen zijn gekoppeld aan het isolationstransactieniveau voor momentopnamen, kan het opschonen van spookrecords op de primaire replica worden geblokkeerd door transacties op een of meer secundaire replica's. De taak voor het opschonen van ghostrecords zal automatisch de ghostrecords opschonen voor op schijf gebaseerde tabellen op de primaire replica, wanneer ze niet meer nodig zijn voor enige secundaire replica. Dit is vergelijkbaar met wat er wordt gedaan wanneer u transacties uitvoert op de primaire replica. In het extreme geval op de secundaire database moet u een langdurende leesquery beëindigen die de ghost cleanup blokkeert. Opmerking: de ghost clean kan worden geblokkeerd als de secundaire replica wordt losgekoppeld of wanneer de gegevensverplaatsing wordt onderbroken op de secundaire database. Ghost-records maken gebruik van fysieke ruimte in een gegevensbestand. Dit kan problemen met hergebruik van ruimte veroorzaken. Zie ghost cleanup voor meer informatie. Deze status voorkomt ook afkapping van logboeken. Als deze status zich blijft voordoen, wordt u aangeraden deze secundaire database uit de beschikbaarheidsgroep te verwijderen. Er zijn geen problemen met het opschonen van spookgegevens bij geheugen-geoptimaliseerde tabellen, omdat de versies van rijen in het geheugen worden bewaard en onafhankelijk zijn van de versies van rijen op de primaire replica.

• De DBCC SHRINKFILE-bewerking op bestanden met schijftabellen kan mislukken op de primaire replica als het bestand ghostrecords bevat die nog nodig zijn op een secundaire replica.

• Vanaf SQL Server 2014 (12.x) kunnen leesbare secundaire replica's online blijven, zelfs wanneer de primaire replica offline is vanwege een gebruikersactie of een fout, bijvoorbeeld omdat de synchronisatie is onderbroken vanwege een gebruikersopdracht of een fout, of een replica de status oplost omdat de WSFC offline is. Alleen-lezenroutering werkt echter niet in deze situatie omdat de listener van de beschikbaarheidsgroep ook offline is. Clients moeten rechtstreeks verbinding maken met de alleen-lezen secundaire replica's voor alleen-lezen workloads.

Prestatieoverwegingen

In deze sectie worden verschillende prestatieoverwegingen besproken voor leesbare secundaire databases

In deze sectie:

• Gegevenslatentie

• Read-Only Werkbelasting impact

• Indexeren

• Statistieken voor Read-Only Access-databases

Gegevenslatentie

Het implementeren van alleen-lezentoegang tot secundaire replica's is nuttig als uw alleen-lezen workloads enige gegevenslatentie kunnen verdragen. In situaties waarin gegevenslatentie onaanvaardbaar is, kunt u overwegen om alleen-lezenworkloads uit te voeren op de primaire replica.

De primaire replica verzendt logboekrecords met wijzigingen op de primaire database naar de secundaire replica's. Op elke secundaire database past een speciale redo-thread de logboekrecords toe. In een secundaire database met leestoegang wordt een bepaalde gegevenswijziging pas in queryresultaten weergegeven als de logboekrecord met de wijziging is toegepast op de secundaire database en de transactie is doorgevoerd op de primaire database.

Dit betekent dat er enige latentie is, meestal slechts een kwestie van seconden, tussen de primaire en secundaire replica's. In ongebruikelijke gevallen kan de latentie echter aanzienlijk worden als netwerkproblemen de doorvoer verminderen. Latentie neemt toe wanneer I/O-knelpunten optreden en wanneer gegevensverplaatsing wordt onderbroken. Als u de onderbroken gegevensverplaatsing wilt bewaken, kunt u het AlwaysOn-dashboard of de sys.dm_hadr_database_replica_states dynamische beheerweergave gebruiken.

Gegevenslatentie op databases met tabellen die zijn geoptimaliseerd voor geheugen

In SQL Server 2014 (12.x) waren er speciale overwegingen met betrekking tot gegevenslatentie op actieve secundaire databases. Zie SQL Server 2014 (12.x) Actieve secundaire replica's: leesbare secundaire replica's. Vanaf SQL Server 2016 (13.x) zijn er geen speciale overwegingen met betrekking tot gegevenslatentie voor tabellen die zijn geoptimaliseerd voor geheugen. De verwachte gegevenslatentie voor tabellen die zijn geoptimaliseerd voor geheugen is vergelijkbaar met de latentie voor tabellen op basis van schijven.

De impact van Read-Only-workload

Wanneer u een secundaire replica configureert voor alleen-lezentoegang, verbruiken uw alleen-lezenworkloads op de secundaire databases systeemresources, zoals CPU en I/O (voor tabellen op basis van schijven) van redothreads, met name als de alleen-lezenworkloads op schijftabellen zeer I/O-intensief zijn. Er is geen I/O-impact bij het openen van tabellen die zijn geoptimaliseerd voor geheugen, omdat alle rijen zich in het geheugen bevinden.

Bovendien kunnen lees-alleen workloads op de secundaire replica's DDL-wijzigingen (Data Definition Language) blokkeren die worden toegepast via logboekrecords.

• Hoewel de leesbewerkingen geen gedeelde vergrendelingen nemen vanwege rijversiebeheer, nemen deze bewerkingen schemastabiliteit (Sch-S) vergrendelingen, waardoor herbewerkingen die DDL-wijzigingen toepassen, kunnen worden geblokkeerd. DDL-bewerkingen omvatten ALTER- en DROP-instructies voor tabellen en weergaven, maar niet de DROP- of ALTER-instructies voor opgeslagen procedures. Als u bijvoorbeeld een tabel die op schijf is gebaseerd of geheugen-geoptimaliseerd is verwijdert van de primaire opslag. Wanneer de REDO-thread de logboekrecord verwerkt om de tabel te verwijderen, moet deze een SCH_M-vergrendeling op de tabel verkrijgen en kan het geblokkeerd worden door een actieve query die toegang heeft tot de tabel. Dit is hetzelfde gedrag op de primaire replica, behalve dat het verwijderen van de tabel plaatsvindt als onderdeel van een gebruikerssessie en niet als een REDO-thread.

• Er is extra blokkering bij tabel Memory-Optimized. Het verwijderen van een native opgeslagen procedure kan ertoe leiden dat de REDO-thread wordt geblokkeerd als de native opgeslagen procedure tegelijkertijd op de secundaire replica wordt uitgevoerd. Dit is hetzelfde gedrag op de primaire replica, behalve dat het verwijderen van de opgeslagen procedure wordt uitgevoerd als onderdeel van een gebruikerssessie en niet als een REDO-thread.

Houd rekening met best practices voor het bouwen van query's en oefen deze best practices uit in de secundaire databases. Plan bijvoorbeeld langlopende query's, zoals aggregaties van gegevens tijdens tijden van lage activiteit.

Indexeren

Als u de alleen-lezen belasting op de secundaire replica's met alleen-lezen toegang wilt optimaliseren, kunt u indexen maken voor de tabellen in de secundaire databases. Omdat u geen schema- of gegevenswijzigingen kunt aanbrengen in de secundaire databases, maakt u indexen in de primaire databases en laat u de wijzigingen naar de secundaire database overzetten via het redo-proces.

Als u de indexgebruiksactiviteit op een secundaire replica wilt controleren, voert u een query uit op de kolommen user_seeks, user_scans en user_lookups van de sys.dm_db_index_usage_stats dynamische beheerweergave.

Statistieken voor Read-Only Access-databases

Statistieken over kolommen van tabellen en geïndexeerde weergaven worden gebruikt om queryplannen te optimaliseren. Voor beschikbaarheidsgroepen worden statistieken die worden gemaakt en onderhouden op de primaire databases automatisch op de secundaire databases bewaard als onderdeel van het toepassen van de transactielogboekrecords. De alleen-lezen werkbelasting op de secundaire databases heeft echter mogelijk andere statistieken nodig dan degenen die zijn gemaakt op de primaire databases. Omdat secundaire databases echter beperkt zijn tot alleen-lezentoegang, kunnen er geen statistieken worden gemaakt op de secundaire databases.

Om dit probleem op te lossen, maakt en onderhoudt de secundaire replica tijdelijke statistieken voor secundaire databases in tempdb. Het achtervoegsel _readonly_database_statistic wordt toegevoegd aan de naam van tijdelijke statistieken om deze te onderscheiden van de permanente statistieken die behouden blijven van de primaire database.

Alleen SQL Server kan tijdelijke statistieken maken en bijwerken. U kunt echter tijdelijke statistieken verwijderen en de eigenschappen ervan controleren met dezelfde hulpprogramma's die u gebruikt voor permanente statistieken:

• Verwijder tijdelijke statistieken met behulp van de instructie DROP STATISTICS Transact-SQL.

• Bewaak statistieken met behulp van de sys.stats en sys.stats_columns catalogusweergaven. sys_stats bevat een kolom, is_temporary, om aan te geven welke statistieken permanent zijn en welke tijdelijk zijn.

Er is geen ondersteuning voor het bijwerken van automatische statistieken voor tabellen die zijn geoptimaliseerd voor geheugen op de primaire of secundaire replica. U moet de queryprestaties en -plannen op de secundaire replica bewaken en de statistieken op de primaire replica handmatig bijwerken wanneer dat nodig is. De ontbrekende statistieken worden echter automatisch gemaakt op zowel primaire als secundaire replica.

Zie Statistieken voor meer informatie over SQL Server-statistieken.

In deze sectie:

• Verouderde permanente statistieken voor secundaire databases

• Limitaties en beperkingen

Verouderde permanente statistieken voor secundaire databases

SQL Server detecteert wanneer permanente statistieken op een secundaire database verlopen zijn. Maar wijzigingen kunnen niet worden aangebracht in de permanente statistieken, behalve door wijzigingen in de primaire database. Voor queryoptimalisatie maakt SQL Server tijdelijke statistieken voor schijftabellen in de secundaire database en gebruikt deze statistieken in plaats van de verouderde permanente statistieken.

Wanneer de permanente statistieken worden bijgewerkt op de primaire database, worden ze automatisch opgeslagen in de secundaire database. Sql Server gebruikt vervolgens de bijgewerkte permanente statistieken, die actueler zijn dan de tijdelijke statistieken.

Als er een failover van de beschikbaarheidsgroep wordt uitgevoerd, worden tijdelijke statistieken verwijderd op alle secundaire replica's.

Beperkingen en beperkingen

• Omdat tijdelijke statistieken worden opgeslagen in tempdb, zorgt een herstart van de SQL Server-service ervoor dat alle tijdelijke statistieken verdwijnen.

• Het achtervoegsel _readonly_database_statistic is gereserveerd voor statistieken die door SQL Server worden gegenereerd. U kunt dit achtervoegsel niet gebruiken bij het maken van statistieken in een primaire database. Zie Statistieken voor meer informatie.

Toegang tot tabellen die zijn geoptimaliseerd voor geheugen op een secundaire replica

De transactieisolatieniveaus die kunnen worden gebruikt met tabellen die zijn geoptimaliseerd voor geheugen op een secundaire replica, zijn hetzelfde als op de primaire replica. De aanbeveling is om het isolatieniveau op sessieniveau in te stellen op READ COMMITTED en de optie MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT op databaseniveau op AAN te zetten. Voorbeeld:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO  
  

Overwegingen voor capaciteitsplanning

• In het geval van tabellen op basis van schijven kunnen leesbare secundaire replica's om twee redenen ruimte in tempdb vereisen:

  • Isolatieniveau van momentopname kopieert rijversies naar tempdb.

  • Tijdelijke statistieken voor secundaire databases worden gemaakt en onderhouden in tempdb. De tijdelijke statistieken kunnen een lichte toename van de grootte van tempdb veroorzaken. Zie Statistieken voor Read-Only Access-databases verderop in deze sectie voor meer informatie.

• Wanneer u leestoegang configureert voor een of meer secundaire replica's, voegen de primaire databases 14 bytes overhead toe aan verwijderde, gewijzigde of ingevoegde gegevensrijen om aanwijzers op te slaan in rijversies op de secundaire databases voor schijftabellen. Deze overhead van 14 bytes wordt overgedragen naar de secundaire databases. Aangezien de overhead van 14 bytes wordt toegevoegd aan gegevensrijen, kunnen paginasplitsingen optreden.

  De rijversiegegevens worden niet door de primaire databases gegenereerd. In plaats daarvan genereren de secundaire databases de rijversies. Rijversiebeheer verhoogt echter de gegevensopslag in zowel de primaire als de secundaire databases.

  De toevoeging van de gegevens van de rijversie is afhankelijk van de isolatie van momentopnamen of de instelling op RCSI-niveau (Read-Committed Snapshot Isolation) op de primaire database. In de onderstaande tabel wordt het gedrag van versiebeheer op een leesbare secundaire database beschreven onder verschillende instellingen voor tabellen op basis van schijven.

  Leesbare secundaire replica?	Isolatie van momentopnamen of RCSI-niveau ingeschakeld?	Primaire database	Secundaire database
  Nee.	Nee.	Geen rijversies of overhead van 14 bytes	Geen rijversies of overhead van 14 bytes
  Nee.	Ja	Rijversies en overhead van 14 bytes	Geen rijversies, maar een overhead van 14 bytes
  Ja	Nee.	Geen rijversies, maar een overhead van 14 bytes	Rijversies en overhead van 14 bytes
  Ja	Ja	Rijversies en overhead van 14 bytes	Rijversies en overhead van 14 bytes

Gerelateerde taken

• Read-Only-toegang configureren op een beschikbaarheidsreplica in SQL Server

• Read-Only routering voor een beschikbaarheidsgroep (SQL Server) configureren

• een listener voor een beschikbaarheidsgroep (SQL Server) maken of configureren

• Beschikbaarheidsgroepen (Transact-SQL) controleren

• Eigenschappen van beschikbaarheidsreplica (SQL Server) weergeven

• het dialoogvenster Nieuwe beschikbaarheidsgroep (SQL Server Management Studio) gebruiken

Verwante inhoud

• SQL Server AlwaysOn-teamblog: de officiële SQL Server Always On-teamblog

Zie ook

overzicht van AlwaysOn-beschikbaarheidsgroepen (SQL Server)
Over Toegang voor Clientverbindingen tot Beschikbaarheidsreplica's (SQL Server)
Listeners voor Beschikbaarheidsgroepen, Clientconnectiviteit en Toepassingsfailover (SQL Server)
statistieken