Dela via

Avlasta skrivskyddad arbetsbelastning till en sekundär replik av en AlwaysOn-tillgänglighetsgrupp

gäller för:SQL Server

De aktiva sekundära funktionerna i AlwaysOn-tillgänglighetsgrupper innehåller stöd för skrivskyddad åtkomst till en eller flera sekundära repliker (läsbara sekundära repliker). En läsbar sekundär replik kan vara i synkron-commit tillgänglighetsläge eller asynkron-commit tillgänglighetsläge. En läsbar sekundär replik ger åtkomst i endast läsläge till alla dess sekundära databaser. Läsbara sekundära databaser är dock inte skrivskyddade. De är dynamiska. En viss sekundär databas ändras när ändringar i motsvarande primära databas tillämpas på den sekundära databasen. För en typisk sekundärreplik är data i de sekundära databaserna, inklusive varaktiga minnesoptimerade tabeller, nästan i realtid. Dessutom synkroniseras fulltextindex med de sekundära databaserna. I många fall är datafördröjningen mellan en primär databas och motsvarande sekundära databas bara några sekunder.

Säkerhetsinställningar som inträffar i de primära databaserna sparas i de sekundära databaserna. Detta omfattar användare, databasroller och programroller tillsammans med deras respektive behörigheter och transparent datakryptering (TDE), om det är aktiverat på den primära databasen.

Anmärkning

Även om du inte kan skriva data till sekundära databaser kan du skriva till läs- och skrivbara databaser på serverinstansen som är värd för den sekundära repliken, inklusive användardatabaser och systemdatabaser som tempdb.

Always On-tillgänglighetsgrupper stöder också omdirigering av anslutningsbegäranden med läsintention till en läsbar sekundär replik (read-only routing). Information om styrning endast för läsning finns i att använda en lyssnare för att ansluta till en Read-Only sekundär replik (Read-Only styrning).

Fördelar

Att dirigera läskompatibla anslutningar till sekundära läsbara repliker ger följande fördelar:

  • Avlastar dina sekundära skrivskyddade arbetsbelastningar från din primärreplika, vilket bevarar dess resurser för dina affärskritiska arbetsbelastningar. Om du har verksamhetskritisk läsarbetsbelastning eller den arbetsbelastning som inte kan tolerera svarstid bör du köra den på den primära.

  • Förbättrar avkastningen på investeringen för de system som är värdar för läsbara sekundära repliker.

Läsbara sekundärfiler ger dessutom robust stöd för skrivskyddade åtgärder enligt följande:

  • Automatisk tillfällig statistik på läsbar sekundär databas optimerar läsfrågor på diskbaserade tabeller. För minnesoptimerade tabeller skapas den saknade statistiken automatiskt. Det finns dock ingen automatisk uppdatering av inaktuell statistik. Du måste uppdatera statistiken manuellt på den primära repliken. Mer information finns i Statistik för Read-Only Access-databaser senare i det här avsnittet.

  • Skrivskyddade arbetsbelastningar för diskbaserade tabeller använder radversionering för att ta bort blockerande konflikter på de sekundära databaserna. Alla frågor som körs mot de sekundära databaserna mappas automatiskt till transaktionsnivå för ögonblicksbildisolering, även när andra transaktionsisoleringsnivåer uttryckligen anges. Dessutom ignoreras alla låstips. Detta eliminerar läsar-/skrivkonflikt.

  • Skrivskyddade arbetsbelastningar för minnesoptimerade varaktiga tabeller får åtkomst till data på exakt samma sätt som de används i den primära databasen, med hjälp av inbyggda lagrade procedurer eller SQL Interoperabilitet med samma begränsningar för transaktionsisoleringsnivå (se Isoleringsnivåer i databasmotorn). Rapportering av belastningar eller skrivskyddade frågor som körs på primär replik kan köras på sekundär replik utan några ändringar. På samma sätt kan en rapporteringsarbetsbörda eller skrivskyddade frågor som körs på en sekundär replik också köras på den primära repliken utan att det krävs några ändringar. På samma sätt som diskbaserade tabeller mappas alla frågor som körs mot de sekundära databaserna automatiskt till transaktionsnivå för ögonblicksbildisolering, även när andra transaktionsisoleringsnivåer uttryckligen anges.

  • DML-åtgärder tillåts för tabellvariabler både för diskbaserade och minnesoptimerade tabelltyper på den sekundära repliken.

Krav för tillgänglighetsgruppen

  • Läsbara sekundära repliker (nödvändigt)

    Databasadministratören måste konfigurera en eller flera repliker så att de, när de körs under den sekundära rollen, tillåter antingen alla anslutningar (enbart för skrivskyddad åtkomst) eller endast anslutningar med läsintention.

    Anmärkning

    Alternativt kan databasadministratören, om så önskas, konfigurera någon av tillgänglighetsreplikerna för att utesluta läsbehöriga anslutningar när de agerar som primär roll.

    Mer information finns i Om klientanslutningsåtkomst till tillgänglighetsrepliker (SQL Server).

    Varning

    Endast repliker som finns i samma större version av SQL Server kan läsas. Mer information finns i Grundläggande om löpande uppgradering .

  • Lyssnare för tillgänglighetsgrupp

    För att stödja skrivskyddad routning måste en tillgänglighetsgrupp ha en tillgänglighetsgrupplyssnare. Den endast läsbara klienten måste dirigera sina anslutningsförfrågningar till den här lyssnaren, och klientens anslutningssträng måste ange programmets avsikt som "endast läsbar". Det vill säga: de måste vara anslutningsförfrågningar med läs-intent.

  • Skrivskyddad routning

    Skrivskyddad routning avser möjligheten för SQL Server att dirigera inkommande anslutningsbegäranden med läs avsikt, som dirigeras till en tillgänglighetsgruppslyssnare, till en tillgänglig läsbar sekundär replik. Förutsättningarna för skrivskyddad routning är följande:

    • För att stödja skriv-/lässkyddad routning kräver en läsbar sekundär replik en skriv-/lässkyddad routnings-URL. Den här URL gäller bara när den lokala repliken körs i en sekundär roll. Den skrivskyddade routnings-URL:en måste anges på replik-för-replik-basis efter behov. Varje lässkyddad routing-URL används för att dirigera läsintention anslutningsbegäranden till en specifik läsbar sekundär replik. Normalt tilldelas varje läsbar sekundär replik en skrivskyddad routnings-URL.

    • Varje tillgänglighetsreplik som ska stödja skrivskyddad routning när det är den primära repliken kräver en skrivskyddad routningslista. En viss endast läsbar routningslista gäller bara när den lokala repliken körs i den primära rollen. Den här listan måste anges på replik-för-replik-basis efter behov. Vanligtvis skulle varje skrivskyddad routningslista innehålla alla skrivskyddade routnings-URL:er, med den lokala replikans URL sist i listan.

      Anmärkning

      Anslutningsbegäranden med läsintention kan lastbalanseras mellan repliker. För mer information, se Konfigurera belastningsutjämning för skrivskyddade repliker.

    Mer information finns i Konfigurera Read-Only routning för en tillgänglighetsgrupp (SQL Server).

Anmärkning

Information om tillgänglighetsgrupplyssnare och mer information om skrivskyddad routning finns i Tillgänglighetsgrupplyssnare, Klientanslutning och Programredundans (SQL Server).

Begränsningar och restriktioner

Vissa åtgärder stöds inte fullt ut enligt följande:

  • Så snart en läsbar replik har aktiverats för läsning kan den börja acceptera anslutningar till sina sekundära databaser. Men om det finns några aktiva transaktioner i en primär databas är radversionerna inte helt tillgängliga i motsvarande sekundära databas. Alla aktiva transaktioner som var på den primära repliken när den sekundära repliken konfigurerades måste bekräftas eller rullas tillbaka. Tills processen är klar är mappningen på transaktionsisoleringsnivå på den sekundära databasen ofullständig och frågor blockeras tillfälligt.

    Varning

    Tidskrävande långa transaktioner påverkar antalet versionsrader som sparas, både för diskbaserade och minnesoptimerade tabeller.

  • I en sekundär databas med minnesoptimerade tabeller, även om radversioner alltid genereras för minnesoptimerade tabeller, blockeras frågor tills alla aktiva transaktioner som fanns i den primära repliken när den sekundära repliken aktiverades för läsning slutförd. Detta säkerställer att både diskbaserade och minnesoptimerade tabeller är tillgängliga för rapporteringsbelastningen och skrivskyddade frågor samtidigt.

  • Ändringsspårning och ändringsdatainsamling stöds inte på sekundära databaser som tillhör en läsbar sekundär replik:

    • Ändringsspårning är uttryckligen inaktiverat på sekundära databaser.

    • Ändring av datainsamling kan inte bara aktiveras på en sekundär replikdatabas. Ändringsdatainsamling kan aktiveras på den primära replikdatabasen och ändringarna kan läsas från CDC-tabellerna med hjälp av funktionerna i den sekundära replikdatabasen.

  • Eftersom läsåtgärder mappas till transaktionsnivån för ögonblicksbildisolering kan borttagandet av spökposter på den primära repliken blockeras av transaktioner på en eller flera sekundära repliker. Rensningsaktiviteten för spökposter rensar automatiskt spökposterna i diskbaserade tabeller på den primära repliken när de inte längre behövs av någon sekundär replik. Detta liknar vad som görs när du kör transaktioner på den primära repliken. I det extrema fallet på den sekundära databasen måste du avsluta en tidskrävande läsfråga som blockerar spökrensningen. Observera att spökrensning kan blockeras om den sekundära repliken kopplas bort eller när dataflyttningen pausas på den sekundära databasen. Spökposter använder fysiskt utrymme i en datafil. Detta kan orsaka problem med återanvändning av utrymme. Mer information finns i borttagning av spökposter. Det här tillståndet förhindrar även loggtrunkering, så om det här tillståndet kvarstår rekommenderar vi att du tar bort den här sekundära databasen från tillgänglighetsgruppen. Det finns inget problem med rensning av spökposter med minnesoptimerade tabeller eftersom radversionerna sparas i minnet och är oberoende av radversionerna på den primära repliken.

  • DBCC SHRINKFILE-åtgärden för filer som innehåller diskbaserade tabeller kan misslyckas på den primära repliken om filen innehåller spökposter som fortfarande behövs på en sekundär replik.

  • Från och med SQL Server 2014 (12.x) kan läsbara sekundära repliker förbli online även när den primära repliken är offline på grund av användaråtgärder eller ett fel, till exempel om synkroniseringen avbröts på grund av ett användarkommando eller ett fel, eller om en replik löser statusen på grund av att WSFC är offline. Skrivskyddad routning fungerar dock inte i den här situationen eftersom tillgänglighetsgruppslyssnaren inte är tillgänglig. Klienter måste ansluta direkt till skrivskyddade sekundära repliker för skrivskyddade arbetsbelastningar.

Anmärkning

Om du kör frågor mot den dynamiska hanteringsvyn sys.dm_db_index_physical_stats på en serverinstans som kör en läsbar sekundär replik kan du stöta på ett REDO-blockeringsproblem. Det beror på att den här dynamiska hanteringsvyn hämtar ett IS-lås i den angivna användartabellen eller vyn som kan blockera begäranden från en REDO-tråd för ett X-lås i användartabellen eller vyn.

Prestandaöverväganden

I det här avsnittet beskrivs flera prestandaöverväganden för läsbara sekundära databaser

i det här avsnittet:

Datasvarstid

Det är användbart att implementera skrivskyddad åtkomst till sekundära repliker om dina läsintensiva arbetsbelastningar kan tolerera viss datafördröjning. I situationer där datafördröjningen är oacceptabel kan du överväga att köra skrivskyddade arbetsbelastningar mot den primära repliken.

Den primära repliken skickar loggposter med ändringar i den primära databasen till de sekundära replikerna. På varje sekundär databas tillämpar en dedikerad redo-tråd loggposter. I en sekundär databas med läsåtkomst visas inte en viss dataändring i frågeresultatet förrän loggposten som innehåller ändringen har tillämpats på den sekundära databasen och transaktionen har checkats in på den primära databasen.

Det innebär att det finns viss svarstid, vanligtvis bara några sekunder, mellan de primära och sekundära replikerna. I ovanliga fall, till exempel om nätverksproblem minskar dataflödet, kan svarstiden bli betydande. Svarstiden ökar när I/O-flaskhalsar inträffar och när dataöverföringen suspenderas. Om du vill övervaka suspenderade dataförflyttningar kan du använda Always On-instrumentpanelen eller sys.dm_hadr_database_replica_states dynamisk hanteringsvy.

Datasvarstid för databaser med minnesoptimerade tabeller

I SQL Server 2014 (12.x) fanns särskilda överväganden kring datafördröjning på aktiva sekundärfiler – se SQL Server 2014 (12.x) Aktiva sekundärfiler: Läsbara sekundära repliker. Från och med SQL Server 2016 (13.x) finns det inga särskilda överväganden kring datasvarstid för minnesoptimerade tabeller. Den förväntade datafördröjningen för minnesoptimerade tabeller är jämförbar med svarstiden för diskbaserade tabeller.

Read-Only påverkan på arbetsbelastningen

När du konfigurerar en sekundär replik för skrivskyddad åtkomst använder dina skrivskyddade arbetsbelastningar på de sekundära databaserna systemresurser, till exempel CPU och I/O (för diskbaserade tabeller) från omgjorda trådar, särskilt om de skrivskyddade arbetsbelastningarna på diskbaserade tabeller är mycket I/O-intensiva. Det finns ingen I/O-påverkan vid åtkomst till minnesoptimerade tabeller eftersom alla rader finns i minnet.

Skrivskyddade belastningar på de sekundära replikerna kan blockera ändringar i datadefinitionsspråk (DDL) som tillämpas via loggar.

  • Även om läsåtgärderna inte tar delade lås på grund av radversionshantering, får dessa åtgärder schemastabilitet (Sch-S) lås, vilket kan blockera omgjorda åtgärder som tillämpar DDL-ändringar. DDL-åtgärder omfattar ALTER/DROP-tabeller och vyer men inte DROP eller ALTER för lagrade procedurer. Om du till exempel tar bort en tabell, oavsett om den är diskbaserad eller minnesoptimerad, från primärdatabasen. När REDO-tråden bearbetar loggposten för att ta bort tabellen måste den hämta ett SCH_M lås på tabellen och kan blockeras av en fråga som körs på tabellen. Det här är samma beteende på den primära repliken, förutom att borttagningen av tabellen görs som en del av en användarsession och inte som en del av en REDO-tråd.

  • Det finns ytterligare blockering Memory-Optimized tabeller. Om den inbyggda lagrade proceduren körs samtidigt på den sekundära repliken kan en borttagning av lagrad procedur orsaka att REDO-tråden blockeras. Detta beteende är samma för den primära repliken, förutom att borttagandet av den lagrade proceduren görs som en del av en användarsession och inte av en REDO-tråd.

Var medveten om bästa praxis för att skapa frågor och tillämpa dessa bästa praxis i de sekundära databaserna. Du kan till exempel schemalägga tidskrävande frågor, till exempel aggregeringar av data under tider med låg aktivitet.

Anmärkning

Om en återskapningstråd hindras av frågekommandon på en sekundär replik, utlöses sqlserver.lock_redo_blocked XEvent.

Indexering

Om du vill optimera läsintensiva arbetsbelastningar på sekundära repliker kanske du vill skapa index för tabellerna i de sekundära databaserna. Eftersom du inte kan göra schema- eller dataändringar på de sekundära databaserna skapar du index i de primära databaserna och tillåter att ändringarna överförs till den sekundära databasen via omprocessen.

Om du vill övervaka indexanvändningsaktiviteten på en sekundär replik, sök i kolumnerna user_seeks, user_scans och user_lookups i den dynamiska hanteringsvyn sys.dm_db_index_usage_stats.

Statistik för Read-Only Access-databaser

Statistik över kolumner i tabeller och indexerade vyer används för att optimera frågeplaner. För tillgänglighetsgrupper sparas statistik som skapas och underhålls på de primära databaserna automatiskt på de sekundära databaserna som en del av tillämpningen av transaktionsloggposterna. Den skrivskyddade arbetsbelastningen på de sekundära databaserna kan dock behöva annan statistik än den som skapas i de primära databaserna. Men eftersom sekundära databaser är begränsade till skrivskyddad åtkomst kan statistik inte skapas på de sekundära databaserna.

För att lösa det här problemet skapar och underhåller den sekundära repliken tillfällig statistik för sekundära databaser i tempdb. Suffixet _readonly_database_statistic läggs till i namnet på tillfällig statistik för att skilja dem från den permanenta statistik som finns kvar från den primära databasen.

Endast SQL Server kan skapa och uppdatera tillfällig statistik. Du kan dock ta bort tillfällig statistik och övervaka deras egenskaper med samma verktyg som du använder för permanent statistik:

  • Ta bort tillfällig statistik med hjälp av DROP STATISTICS Transact-SQL-instruktionen.

  • Övervaka statistik med hjälp av katalogvyerna sys.stats och sys.stats_columns . sys_stats innehåller en kolumn , is_temporary, för att ange vilken statistik som är permanent och vilka som är tillfälliga.

Det finns inget stöd för automatisk statistikuppdatering för minnesoptimerade tabeller på den primära eller sekundär replika. Du måste övervaka frågeprestanda och planer på den sekundära repliken och manuellt uppdatera statistiken på den primära repliken vid behov. Den saknade statistiken skapas dock automatiskt både på den primära och sekundära repliken.

Mer information om SQL Server-statistik finns i Statistik.

i det här avsnittet:

Föråldrad permanent statistik över sekundära databaser

SQL Server identifierar när permanent statistik på en sekundär databas är inaktuell. Men ändringar kan inte göras i den permanenta statistiken förutom genom ändringar i den primära databasen. För frågeoptimering skapar SQL Server tillfällig statistik för diskbaserade tabeller i den sekundära databasen och använder den här statistiken i stället för den inaktuella permanenta statistiken.

När den permanenta statistiken uppdateras i den primära databasen sparas de automatiskt i den sekundära databasen. Sedan använder SQL Server den uppdaterade permanenta statistiken, som är mer aktuell än den tillfälliga statistiken.

Om tillgänglighetsgruppen misslyckas över raderas tillfällig statistik på alla sekundära repliker.

Begränsningar och restriktioner

  • Eftersom tillfällig statistik lagras i tempdb försvinner all tillfällig statistik om SQL Server-tjänsten startas om.

  • Suffixet _readonly_database_statistic är reserverat för statistik som genereras av SQL Server. Du kan inte använda det här suffixet när du skapar statistik på en primär databas. Mer information finns i Statistik.

Åtkomst till minnesoptimerade tabeller på en sekundär replik

De transaktionsisoleringsnivåer som kan användas med minnesoptimerade tabeller på en sekundär replik är desamma som på den primära repliken. Rekommendationen är att ange isoleringsnivån på sessionsnivå till READ COMMITTED och ange alternativet på databasnivå MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT till PÅ. Till exempel:

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

Överväganden för kapacitetsplanering

  • När det gäller diskbaserade tabeller kan läsbara sekundära repliker kräva utrymme i tempdb av två orsaker:

    • På isoleringsnivå för ögonblicksbilder kopieras radversioner till tempdb.

    • Tillfällig statistik för sekundära databaser skapas och underhålls i tempdb. Den tillfälliga statistiken kan orsaka en liten ökning av tempdb-storleken. Mer information finns i Statistik för Read-Only Access-databaser senare i det här avsnittet.

  • När du konfigurerar läsåtkomst för en eller flera sekundära repliker, lägger de primära databaserna till 14 byte i överliggande kostnad för borttagna, ändrade eller infogade datarader för att lagra pekare till radversioner på de sekundära databaserna för diskbaserade tabeller. Den här kostnaden på 14 byte överförs till de sekundära databaserna. Eftersom 14 bytes överförbrukning läggs till dataraderna kan sidbrytningar inträffa.

    Radversionsdata genereras inte av de primära databaserna. I stället genererar de sekundära databaserna radversionerna. Radversioner ökar dock datalagringen i både de primära och sekundära databaserna.

    Tillägget av radversionsdata beror på inställningen för ögonblicksbildisolering eller skrivskyddad ögonblicksbildisolering (RCSI) på den primära databasen. I tabellen nedan beskrivs beteendet för versionshantering på en läsbar sekundär databas under olika inställningar för diskbaserade tabeller.

    Läsbar sekundär replik? Är ögonblicksbildisolering eller RCSI-nivå aktiverat? Primär databas Sekundär databas
    Nej Nej Inga radversioner eller 14 byte overhead Inga radversioner eller 14 byte overhead
    Nej Ja Radversioner och 14 byte omkostnader Inga radversioner, men 14 byte överliggande
    Ja Nej Inga radversioner, men 14 byte överliggande Radversioner och 14 byte omkostnader
    Ja Ja Radversioner och 14 byte omkostnader Radversioner och 14 byte omkostnader

Relaterade uppgifter

Relaterat innehåll

Se även

översikt över AlwaysOn-tillgänglighetsgrupper (SQL Server)
om klientanslutningsåtkomst till tillgänglighetsrepliker (SQL Server)
Lyssnare för tillgänglighetsgrupper, klientanslutning och applikationsomkoppling (SQL Server)
Statistik