Leesschaal configureren voor een AlwaysOn-beschikbaarheidsgroep

van toepassing op:SQL Server-

U kunt een SQL Server AlwaysOn-beschikbaarheidsgroep configureren voor workloads op leesschaal in Windows. Er zijn twee typen architectuur voor beschikbaarheidsgroepen:

• Een architectuur voor hoge beschikbaarheid die gebruikmaakt van een clusterbeheerder om verbeterde bedrijfscontinuïteit te bieden en die leesbare secundaire replica's kan bevatten. Zie Beschikbaarheidsgroepen maken en configureren in Windowsom deze architectuur voor hoge beschikbaarheid te maken.
• Een architectuur die alleen workloads op leesschaal ondersteunt.

In dit artikel wordt uitgelegd hoe u een beschikbaarheidsgroep zonder clusterbeheerder maakt voor leesgeoptimaliseerde workloads. Deze architectuur biedt alleen leesschaal. Het biedt geen hoge beschikbaarheid.

Voorwaarden

Voordat u de beschikbaarheidsgroep maakt, moet u het volgende doen:

• Stel uw omgeving zo in dat alle servers waarop beschikbaarheidsreplica's worden gehost, kunnen communiceren.
• Installeer SQL Server. Zie de installatiehandleiding voor SQL Server voor meer informatie.

AlwaysOn-beschikbaarheidsgroepen inschakelen en mssql-server opnieuw starten

Schakel AlwaysOn-beschikbaarheidsgroepen in op elke replica die als host fungeert voor een SQL Server-exemplaar. Start vervolgens de SQL Server-service opnieuw op. Voer de volgende opdracht uit om de SQL Server-services in te schakelen en opnieuw op te starten:

Enable-SqlAlwaysOn -ServerInstance <server\instance> -Force

Een AlwaysOn_health-gebeurtenissessie inschakelen

Als u wilt helpen bij de diagnose van de hoofdoorzaak bij het oplossen van problemen met een beschikbaarheidsgroep, kunt u eventueel een XEvents-sessie (Extended Events) van AlwaysOn-beschikbaarheidsgroepen inschakelen. Voer hiervoor de volgende opdracht uit op elk exemplaar van SQL Server:

ALTER EVENT SESSION AlwaysOn_health ON SERVER WITH (STARTUP_STATE = ON);
GO

Zie Uitgebreide gebeurtenissen configureren voor beschikbaarheidsgroepen voor meer informatie over deze XEvents-sessie.

Eindpuntverificatie bij databasespiegeling

Synchronisatie werkt alleen goed als de replica's die betrokken zijn bij de lees-schaal beschikbaarheidsgroep worden geverifieerd via het eindpunt. De twee belangrijkste scenario's die u voor dergelijke verificatie kunt gebruiken, worden behandeld in de volgende secties.

Dienstaccount

In een Active Directory-omgeving waarin alle secundaire replica's zijn gekoppeld aan hetzelfde domein, kan SQL Server worden geverifieerd door gebruik te maken van het serviceaccount. U moet expliciet een aanmelding voor het serviceaccount maken op elk SQL Server-exemplaar:

CREATE LOGIN [<domain>\service account] FROM WINDOWS;

SQL-aanmeldingsverificatie

In omgevingen waarin de secundaire replica's mogelijk niet zijn gekoppeld aan een Active Directory-domein, moet u SQL-verificatie gebruiken. Met het volgende Transact-SQL script maakt u een aanmelding met de naam dbm_login en een gebruiker met de naam dbm_user. Vervang <password> door een geldig wachtwoord. Als u de gebruiker van het eindpunt voor databasespiegeling wilt maken, voert u de volgende opdracht uit op alle SQL Server-exemplaren.

CREATE LOGIN dbm_login WITH PASSWORD = '<password>';
CREATE USER dbm_user FOR LOGIN dbm_login;

Certificaatverificatie

Als u een secundaire replica gebruikt waarvoor verificatie met SQL-verificatie is vereist, gebruikt u een certificaat voor verificatie tussen de eindpunten voor spiegeling.

Met het volgende Transact-SQL script maakt u een hoofdsleutel en een certificaat. Vervolgens wordt een back-up van het certificaat gemaakt en wordt het bestand beveiligd met een persoonlijke sleutel. Werk het script bij met sterke wachtwoorden. Voer het script uit op het primaire SQL Server-exemplaar om het certificaat te maken:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<dmk-password>';
CREATE CERTIFICATE dbm_certificate WITH SUBJECT = 'dbm';

BACKUP CERTIFICATE dbm_certificate
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        ENCRYPTION BY PASSWORD = '<private-key-password>'
    );

Op dit moment heeft uw primaire SQL Server-replica een certificaat op c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer en een persoonlijke sleutel op c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk. Kopieer deze twee bestanden naar dezelfde locatie op alle servers waarop beschikbaarheidsreplica's worden gehost.

Zorg ervoor dat op elke secundaire replica het serviceaccount voor het SQL Server-exemplaar machtigingen heeft voor toegang tot het certificaat.

Het certificaat maken op secundaire servers

Met het volgende Transact-SQL script maakt u een hoofdsleutel en een certificaat op basis van de back-up die u hebt gemaakt op de primaire SQL Server-replica. Met de opdracht kunnen gebruikers ook toegang krijgen tot het certificaat. Werk het script bij met sterke wachtwoorden. Het ontsleutelingswachtwoord is hetzelfde wachtwoord dat u in een vorige stap hebt gebruikt om het bestand .pvk te maken. Voer het volgende script uit op alle secundaire replica's om het certificaat te maken:

CREATE MASTER KEY ENCRYPTION BY PASSWORD= '<dmk-password>';

CREATE CERTIFICATE dbm_certificate
    AUTHORIZATION dbm_user
    FROM FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer'
    WITH PRIVATE KEY (
        FILE = 'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk',
        DECRYPTION BY PASSWORD = '<private-key-password>'
    );

Databasespiegelingseindpunten maken op alle replica's

Databasespiegelingseindpunten gebruiken TCP (Transmission Control Protocol) om berichten te verzenden en te ontvangen tussen de serverexemplaren die deelnemen aan databasespiegelingssessies of hostbeschikbaarheidsreplica's. Het eindpunt voor databasespiegeling luistert op een uniek TCP-poortnummer.

Met het volgende Transact-SQL script maakt u een luistereindpunt met de naam Hadr_endpoint voor de beschikbaarheidsgroep. Het eindpunt wordt gestart en verleent verbindingsmachtigingen voor het serviceaccount of SQL-aanmelding dat u in een vorige stap hebt gemaakt. Voordat u het script uitvoert, vervangt u de waarden tussen < ... >. U kunt desgewenst een IP-adres opnemen, LISTENER_IP = (0.0.0.0). Het IP-adres van de listener moet een IPv4-adres zijn. U kunt ook 0.0.0.0gebruiken.

Werk het volgende Transact-SQL script voor uw omgeving bij op alle SQL Server-exemplaren:

CREATE ENDPOINT [Hadr_endpoint]
    AS TCP (LISTENER_PORT = **<5022>**)
    FOR DATABASE_MIRRORING (
        ROLE = ALL,
        AUTHENTICATION = CERTIFICATE dbm_certificate,
        ENCRYPTION = REQUIRED ALGORITHM AES
    );
ALTER ENDPOINT [Hadr_endpoint] STATE = STARTED;
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [<service account or user>];

De TCP-poort op de firewall moet zijn geopend voor de listenerpoort.

Zie Het eindpunt voor databasespiegeling (SQL Server) voor meer informatie.

Een beschikbaarheidsgroep maken

Maak een beschikbaarheidsgroep. Stel CLUSTER_TYPE = NONEin. Stel bovendien elke replica in met FAILOVER_MODE = NONE. Clienttoepassingen die analyse- of rapportageworkloads uitvoeren, kunnen rechtstreeks verbinding maken met de secundaire databases. U kunt ook een alleen-lezen routeringslijst maken. Verbindingen met de primaire replica sturen verbindingsaanvragen door naar elk van de secundaire replica's vanuit de routeringslijst op round robin-wijze.

Met het volgende Transact-SQL script wordt een beschikbaarheidsgroep met de naam ag1gemaakt. Het script configureert de replica's van de beschikbaarheidsgroep met SEEDING_MODE = AUTOMATIC. Deze instelling zorgt ervoor dat SQL Server automatisch de database op elke secundaire server maakt nadat deze is toegevoegd aan de beschikbaarheidsgroep.

Werk het volgende script voor uw omgeving bij. Vervang de waarden van de <node1> en <node2> door de namen van de SQL Server-exemplaren die als host fungeren voor de replica's. Vervang de <5022> waarde door de poort die u voor het eindpunt hebt ingesteld. Voer het volgende Transact-SQL script uit op de primaire SQL Server-replica:

CREATE AVAILABILITY GROUP [ag1]
    WITH (CLUSTER_TYPE = NONE)
    FOR REPLICA ON
        N'<node1>' WITH (
            ENDPOINT_URL = N'tcp://<node1>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
                    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    ),
        N'<node2>' WITH (
		    ENDPOINT_URL = N'tcp://<node2>:<5022>',
		    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
		    FAILOVER_MODE = MANUAL,
		    SEEDING_MODE = AUTOMATIC,
		    SECONDARY_ROLE (ALLOW_CONNECTIONS = ALL)
		    );

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Secundaire SQL Server-exemplaren toevoegen aan de beschikbaarheidsgroep

Met het volgende Transact-SQL script wordt een server gekoppeld aan een beschikbaarheidsgroep met de naam ag1. Werk het script voor uw omgeving bij. Voer het volgende Transact-SQL script uit op elke secundaire SQL Server-replica om lid te worden van de beschikbaarheidsgroep:

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = NONE);

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Een database toevoegen aan de beschikbaarheidsgroep

Zorg ervoor dat de database die u toevoegt aan de beschikbaarheidsgroep zich in het volledige herstelmodel bevindt en een geldige logboekback-up heeft. Als de database een testdatabase of een zojuist gemaakte database is, maakt u een back-up van de database. Als u een database met de naam db1wilt maken en er een back-up van wilt maken, voert u het volgende Transact-SQL script uit op het primaire SQL Server-exemplaar:

CREATE DATABASE [db1];
ALTER DATABASE [db1] SET RECOVERY FULL;
BACKUP DATABASE [db1]
   TO DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\db1.bak';

Als u een database met de naam db1 wilt toevoegen aan een beschikbaarheidsgroep met de naam ag1, voert u het volgende Transact-SQL script uit op de primaire SQL Server-replica:

ALTER AVAILABILITY GROUP [ag1] ADD DATABASE [db1];

Controleer of de database is gemaakt op de secundaire servers

Als u wilt zien of de db1-database is gemaakt en gesynchroniseerd, voert u de volgende query uit op elke secundaire SQL Server-replica:

SELECT * FROM sys.databases WHERE name = 'db1';
GO
SELECT DB_NAME(database_id) AS 'database', synchronization_state_desc FROM sys.dm_hadr_database_replica_states;

Deze beschikbaarheidsgroep is geen configuratie met hoge beschikbaarheid. Als u hoge beschikbaarheid nodig hebt, volgt u de instructies in Een AlwaysOn-beschikbaarheidsgroep configureren voor SQL Server op Linux of maken en configureren van beschikbaarheidsgroepen in Windows.

Verbinding maken met alleen voor lezen toegankelijke secundaire kopieën

U kunt op twee manieren verbinding maken met secundaire replica's met het kenmerk Alleen-lezen:

• Toepassingen kunnen rechtstreeks verbinding maken met het SQL Server-exemplaar dat als host fungeert voor de secundaire replica en query's uitvoeren op de databases. Zie leesbare secundaire replica'svoor meer informatie.
• Toepassingen kunnen ook alleen-lezenroutering gebruiken, waarvoor een listener is vereist. Als u een scenario met leesschaal implementeert zonder clusterbeheerder, kunt u nog steeds een listener maken die verwijst naar het IP-adres van de huidige primaire replica en naar dezelfde poort waarop SQL Server luistert. U moet de listener opnieuw maken om na een failover naar het nieuwe primaire IP-adres te verwijzen. Zie alleen-lezen routeringvoor meer informatie.

Voeren van een failover van de primaire replica in een leesgerichte beschikbaarheidsgroep

Elke beschikbaarheidsgroep heeft slechts één primaire replica. De primaire replica staat lees- en schrijfbewerkingen toe. Als u wilt wijzigen welke replica primair is, kunt u een failover uitvoeren. In een typische beschikbaarheidsgroep automatiseert de clusterbeheerder het failoverproces. In een beschikbaarheidsgroep met clustertype NONE is het failoverproces handmatig.

Er zijn twee manieren om een failover uit te voeren voor de primaire replica in een beschikbaarheidsgroep met clustertype NONE:

• Handmatige failover zonder gegevensverlies
• Geforceerde handmatige failover met gegevensverlies

Handmatige failover zonder gegevensverlies

Gebruik deze methode wanneer de primaire replica beschikbaar is, maar u moet tijdelijk of permanent wijzigen welk exemplaar als host fungeert voor de primaire replica. Als u mogelijk gegevensverlies wilt voorkomen, moet u ervoor zorgen dat de secundaire doelreplica up-to-date is voordat u de handmatige failover uitvoert.

Handmatig een failover uitvoeren zonder gegevensverlies:

1. Maak de huidige primaire en secundaire doel replica SYNCHRONOUS_COMMIT.

   ALTER AVAILABILITY GROUP [AGRScale] 
        MODIFY REPLICA ON N'<node2>' 
        WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);
   

2. Voer de volgende query uit om te bepalen dat actieve transacties worden doorgevoerd in de primaire replica en ten minste één synchrone secundaire replica:

   SELECT ag.name, 
      drs.database_id, 
      drs.group_id, 
      drs.replica_id, 
      drs.synchronization_state_desc, 
      ag.sequence_number
   FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
   WHERE drs.group_id = ag.group_id; 
   

   De secundaire replica wordt gesynchroniseerd wanneer synchronization_state_desc is SYNCHRONIZED.

3. Werk REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT bij naar 1.

   Met het volgende script wordt REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT ingesteld op 1 op een beschikbaarheidsgroep met de naam ag1. Voordat u het volgende script uitvoert, vervangt u ag1 door de naam van uw beschikbaarheidsgroep:

   ALTER AVAILABILITY GROUP [AGRScale] 
        SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);
   

   Deze instelling zorgt ervoor dat elke actieve transactie wordt doorgevoerd in de primaire replica en ten minste één synchrone secundaire replica.

   Notitie

   Deze instelling is niet specifiek voor failover en moet worden ingesteld op basis van de vereisten van de omgeving.

4. Stel de primaire replica en de secundaire replica('s) die niet deelnemen aan de failover offline in om de rolwijziging voor te bereiden.

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

5. Niveau verhogen van de secundaire doelreplica naar primair.

   ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS; 
   

6. Werk de rol van de oude primaire en andere secundaire databases bij naar SECONDARYen voer de volgende opdracht uit op het SQL Server-exemplaar dat als host fungeert voor de oude primaire replica:

   ALTER AVAILABILITY GROUP [AGRScale] 
        SET (ROLE = SECONDARY); 
   

   Notitie

   Als u een beschikbaarheidsgroep wilt verwijderen, gebruikt u DROP AVAILABILITY GROUP. Voor een beschikbaarheidsgroep die is gemaakt met clustertype NONE of EXTERNAL, voert u de opdracht uit op alle replica's die deel uitmaken van de beschikbaarheidsgroep.

7. Hervat gegevensverplaatsing, voer de volgende opdracht uit voor elke database in de beschikbaarheidsgroep op het SQL Server-exemplaar dat als host fungeert voor de primaire replica:

   ALTER DATABASE [db1]
        SET HADR RESUME
   

8. Maak opnieuw elke listener aan die u hebt gemaakt voor leesschaaldoeleinden en die niet wordt beheerd door een clusterbeheerder. Als de oorspronkelijke listener naar de oude primaire verwijst, verwijder deze en maak hem opnieuw aan zodat hij naar de nieuwe primaire verwijst.

Geforceerde handmatige failover met gegevensverlies

Als de primaire replica niet beschikbaar is en niet onmiddellijk kan worden hersteld, moet u een failover naar de secundaire replica afdwingen met gegevensverlies. Als de oorspronkelijke primaire replica echter na een failover herstelt, zal deze opnieuw de primaire rol op zich nemen. Als u wilt voorkomen dat elke replica een andere status heeft, verwijdert u de oorspronkelijke primaire replica uit de beschikbaarheidsgroep na een geforceerde failover met gegevensverlies. Zodra de oorspronkelijke primaire versie weer online is, verwijdert u de beschikbaarheidsgroep volledig.

Als u een handmatige failover wilt afdwingen met gegevensverlies van primaire replica N1 naar secundaire replica N2, voert u de volgende stappen uit:

1. Start op de secundaire replica (N2) de geforceerde failover:

   ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;
   

2. Verwijder op de nieuwe primaire replica (N2) de oorspronkelijke primaire replica (N1):

   ALTER AVAILABILITY GROUP [AGRScale]
   REMOVE REPLICA ON N'N1';
   

3. Controleer of al het toepassingsverkeer naar de listener en/of de nieuwe primaire replica wijst.

4. Als de oorspronkelijke primaire (N1) online komt, haalt u de beschikbaarheidsgroep AGRScale onmiddellijk offline op de oorspronkelijke primaire (N1):

   ALTER AVAILABILITY GROUP [AGRScale] OFFLINE
   

5. Als er gegevens of niet-gesynchroniseerde wijzigingen zijn, behoudt u deze gegevens via back-ups of andere opties voor het repliceren van gegevens die aansluiten bij uw zakelijke behoeften.

6. Verwijder vervolgens de beschikbaarheidsgroep uit de oorspronkelijke primaire groep (N1):

   DROP AVAILABILITY GROUP [AGRScale];
   

7. Verwijder de database van de beschikbaarheidsgroep op de oorspronkelijke primaire replica (N1):

   USE [master]
   GO
   DROP DATABASE [AGDBRScale]
   GO
   

8. (Optioneel) Desgewenst kunt u N1 weer toevoegen als een nieuwe secundaire replica aan de beschikbaarheidsgroep AGRScale.

Als u een listener gebruikt om verbinding te maken, moet u de listener opnieuw maken na het uitvoeren van de failover.

Volgende stappen

• een gedistribueerde beschikbaarheidsgroep configureren
• Meer informatie over beschikbaarheidsgroepen
• een geforceerde handmatige failover uitvoeren