Delen via

Een AlwaysOn-beschikbaarheidsgroep maken met behulp van Transact-SQL (T-SQL)

Van toepassing op:SQL Server

In dit onderwerp wordt beschreven hoe u Transact-SQL gebruikt voor het maken en configureren van een beschikbaarheidsgroep op exemplaren van SQL Server waarop de functie AlwaysOn-beschikbaarheidsgroepen is ingeschakeld. Een beschikbaarheidsgroep definieert een set gebruikersdatabases die als één eenheid een failover uitvoeren en een set failoverpartners die bekend staan als beschikbaarheidsreplica's, die failover ondersteunen.

Opmerking

Zie Overzicht van AlwaysOn-beschikbaarheidsgroepen (SQL Server)voor een inleiding tot beschikbaarheidsgroepen.

Opmerking

Als alternatief voor het gebruik van Transact-SQL kunt u de wizard Beschikbaarheidsgroep maken of SQL Server PowerShell-cmdlets gebruiken. Zie De wizard Beschikbaarheidsgroep (SQL Server Management Studio) gebruiken, het dialoogvenster Nieuwe beschikbaarheidsgroep (SQL Server Management Studio) gebruiken of een beschikbaarheidsgroep maken (SQL Server PowerShell) voor meer informatie.

Vereisten, beperkingen en aanbevelingen

  • Voordat u een beschikbaarheidsgroep maakt, controleert u of de exemplaren van SQL Server die beschikbaarheidsreplica's hosten zich bevinden op een ander WSFC-knooppunt (Windows Server Failover Clustering) binnen hetzelfde WSFC-failovercluster. Controleer ook of elk van de serverexemplaren voldoet aan alle andere vereisten voor AlwaysOn-beschikbaarheidsgroepen. Voor meer informatie raden we u ten zeerste aan vereisten, beperkingen en aanbevelingen voor AlwaysOn-beschikbaarheidsgroepen (SQL Server)te lezen.

Machtigingen

Vereist lidmaatschap van de sysadmin vaste serverfunctie en de servermachtiging CREATE AVAILABILITY GROUP, de machtiging ALTER ANY AVAILABILITY GROUP, of de machtiging CONTROL SERVER.

Een beschikbaarheidsgroep maken en configureren met Transact-SQL

Overzicht van taken en bijbehorende Transact-SQL-instructies

De volgende tabel bevat de basistaken die betrokken zijn bij het maken en configureren van een beschikbaarheidsgroep en geeft aan welke Transact-SQL instructies voor deze taken moeten worden gebruikt. De Always On-beschikbaarheidsgroepen taken moeten worden uitgevoerd in de volgorde waarin ze in de tabel worden weergegeven.

Opdracht Transact-SQL verklaring(en) Waar taken moeten worden uitgevoerd*****
Eindpunt voor databasespiegeling maken (eenmaal per SQL Server-exemplaar) MAAK EINDPUNT AANendpointName ... VOOR DATABASE_MIRRORING Voer de taak uit op elke serverexemplaar dat geen eindpunt voor databasespiegeling heeft.
Beschikbaarheidsgroep maken BESCHIKBAARHEIDSGROEP MAKEN Voer uit op het serverexemplaar dat host is voor de eerste primaire replica.
Secundaire replica toevoegen aan een beschikbaarheidsgroep WIJZIG BESCHIKBAARHEIDSGROEPgroepsnaam TOEVOEGEN Voer de opdracht uit op elke serverexemplaar waarop een secundaire replica wordt gehost.
De secundaire database voorbereiden BACKUP en HERSTEL. Maak back-ups op de serverinstantie die de primaire replica host.

Herstel back-ups op iedere serverexemplaren die als host fungeren voor een secundaire replica, door RESTORE WITH NORECOVERY te gebruiken.
Gegevenssynchronisatie starten door elke secundaire database toe te voegen aan een beschikbaarheidsgroep ALTER DATABASEdatabase_name SET HADR AVAILABILITY GROUP = group_name Voer de opdracht uit op elke serverexemplaar waarop een secundaire replica wordt gehost.

*Als u een bepaalde taak wilt uitvoeren, maakt u verbinding met de aangegeven serverinstantie of instanties.

Transact-SQL gebruiken

Opmerking

Zie voorbeeld: Een beschikbaarheidsgroep configureren die Gebruikmaakt van Windows-verificatie voor een voorbeeldconfiguratieprocedure met codevoorbeelden van elk van deze Transact-SQL-instructies.

  1. Maak verbinding met het serverexemplaar dat als host fungeert voor de primaire replica.

  2. Maak de beschikbaarheidsgroep met behulp van de instructie CREATE AVAILABILITY GROUPTransact-SQL.

  3. Voeg de nieuwe secundaire replica toe aan de beschikbaarheidsgroep. Zie Een secundaire replica toevoegen aan een beschikbaarheidsgroep (SQL Server)voor meer informatie.

  4. Maak voor elke database in de beschikbaarheidsgroep een secundaire database door recente back-ups van de primaire database te herstellen met BEHULP van RESTORE WITH NORECOVERY. Zie voorbeeld voor meer informatie : Een beschikbaarheidsgroep instellen met windows-verificatie (Transact-SQL), te beginnen met de stap waarmee de databaseback-up wordt hersteld.

  5. Voeg elke nieuwe secundaire database toe aan de beschikbaarheidsgroep. Zie Een secundaire replica toevoegen aan een beschikbaarheidsgroep (SQL Server)voor meer informatie.

Voorbeeld: Een beschikbaarheidsgroep configureren die gebruikmaakt van Windows-verificatie

In dit voorbeeld wordt een configuratieprocedure voor AlwaysOn-beschikbaarheidsgroepen gemaakt die gebruikmaakt van Transact-SQL voor het instellen van eindpunten voor databasespiegeling die gebruikmaken van Windows-verificatie en voor het maken en configureren van een beschikbaarheidsgroep en de secundaire databases.

Dit voorbeeld bevat de volgende secties:

Vereisten voor het gebruik van de voorbeeldconfiguratieprocedure

Deze voorbeeldprocedure heeft de volgende vereisten:

  • De serverexemplaren moeten AlwaysOn-beschikbaarheidsgroepen ondersteunen. Zie vereisten, beperkingen en aanbevelingen voor AlwaysOn-beschikbaarheidsgroepen (SQL Server)voor meer informatie.

  • Er moeten twee voorbeelddatabases, MyDb1 en MyDb2, aanwezig zijn op het serverexemplaren waarop de primaire replica wordt gehost. De volgende codevoorbeelden maken en configureren deze twee databases en maken een volledige back-up van elke database. Voer deze voorbeelden van code uit op het serverexemplaar waarop u de voorbeeld beschikbaarheidsgroep wilt maken. Dit serverexemplaar host de eerste primaire kopie van de voorbeeldbeschikbaarheidsgroep.

    1. In het volgende Transact-SQL voorbeeld worden deze databases gemaakt en gewijzigd om het volledige herstelmodel te gebruiken:

      -- Create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  

CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO

    2. In het volgende codevoorbeeld wordt een volledige databaseback-up van MyDb1 en MyDb2 gemaakt. In dit codevoorbeeld wordt een fictieve back-upshare \\FILESERVER\SQLbackups gebruikt.

      -- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT;  
GO  

BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT;  
GO

[TopOfExample]

Voorbeeldconfiguratieprocedure

In deze voorbeeldconfiguratie wordt de beschikbaarheidsreplica gemaakt op twee zelfstandige serverexemplaren waarvan de serviceaccounts worden uitgevoerd onder verschillende, maar vertrouwde, domeinen (DOMAIN1 en DOMAIN2).

De volgende tabel bevat een overzicht van de waarden die in deze voorbeeldconfiguratie worden gebruikt.

Initiële rol Systeem SQL Server-exemplaar hosten
Primair COMPUTER01 AgHostInstance
Secundair COMPUTER02 Standaardinstantie.

  1. Maak een eindpunt voor databasespiegeling met de naam dbm_endpoint op het serverexemplaar waar u de beschikbaarheidsgroep wilt aanmaken (dit is een exemplaar genaamd AgHostInstance op COMPUTER01). Dit eindpunt maakt gebruik van poort 7022. Houd er rekening mee dat het serverexemplaar waarop u de beschikbaarheidsgroep maakt de primaire replica host.

    -- Create endpoint on server instance that hosts the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

  2. Maak een eindpunt dbm_endpoint op het serverexemplaren waarop de secundaire replica wordt gehost (dit is het standaardserverexemplaren op COMPUTER02). Dit eindpunt maakt gebruik van poort 5022.

    -- Create endpoint on server instance that hosts the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=5022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO

  3. Opmerking

    Als de serviceaccounts van de serverexemplaren die als host fungeren voor uw beschikbaarheidsreplica's, worden uitgevoerd onder hetzelfde domeinaccount, is deze stap niet nodig. Sla deze over en ga rechtstreeks naar de volgende stap.

    Als de serviceaccounts van de serverexemplaren worden uitgevoerd onder verschillende domeingebruikers, maakt u bij elk serverexemplaar een aanmelding voor de andere serverexemplaren en verleent u deze aanmelding de machtigingen voor toegang tot het eindpunt voor het spiegelen van de lokale database.

    In het volgende codevoorbeeld worden de Transact-SQL-instructies getoond voor het maken van een login en het toekennen van permissies op een eindpunt. Het domeinaccount van het exemplaar van de externe server wordt hier weergegeven als domain_name\user_name.

    -- If necessary, create a login for the service account, domain_name\user_name  
-- of the server instance that will host the other replica:  
USE master;  
GO  
CREATE LOGIN [domain_name\user_name] FROM WINDOWS;  
GO  
-- And Grant this login connect permissions on the endpoint:  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [domain_name\user_name];  
GO

  4. Maak de beschikbaarheidsgroep op het serverexemplaar waar de gebruikersdatabases zich bevinden.

    In het volgende codevoorbeeld wordt een beschikbaarheidsgroep met de naam MyAG gemaakt op het serverexemplaar waarop de voorbeelddatabases MyDb1 en MyDb2 zijn gemaakt. Het lokale serverexemplaar, AgHostInstance, op COMPUTER01 wordt eerst opgegeven. In deze instantie wordt de eerste primaire replica gehost. Een extern serverexemplaar, het standaardserverexemplaar op COMPUTER02, wordt opgegeven om een tweede replica te hosten. Beide beschikbaarheidsreplica zijn geconfigureerd om de asynchrone-commit modus te gebruiken met handmatige failover (voor asynchrone-commit replica's met handmatige failover betekent dit geforceerde failover met mogelijk gegevensverlies).

    -- Create the availability group, MyAG:   
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:5022',  
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = MANUAL  
         );   
GO

    Zie CREATE AVAILABILITY GROUP (Transact-SQL) voor meer Transact-SQL codevoorbeelden voor het maken van een beschikbaarheidsgroep.

  5. Voeg de secundaire replica toe aan de beschikbaarheidsgroep op het serverexemplaar waar de secundaire replica wordt gehost.

    In het volgende codevoorbeeld wordt de secundaire replica COMPUTER02 gekoppeld aan de MyAG beschikbaarheidsgroep.

    -- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO

  6. Maak de secundaire databases op het serverexemplaar dat de host is voor de secundaire replica.

    In het volgende codevoorbeeld worden de secundaire databases MyDb1 en MyDb2 gemaakt door databaseback-ups te herstellen met RESTORE WITH NORECOVERY.

    -- On the server instance that hosts the secondary replica,   
-- Restore database backups using the WITH NORECOVERY option:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY;  
GO  

RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY;  
GO

  7. Op het serverexemplaar waarop de primaire replica gehost wordt, maak een back-up van het transactielogboek voor elke primaire database.

    Belangrijk

    Wanneer u een echte beschikbaarheidsgroep configureert, raden we u aan om voordat u deze logboekback-up maakt, logboekback-uptaken voor uw primaire databases op te schorten totdat u de bijbehorende secundaire databases hebt gekoppeld aan de beschikbaarheidsgroep.

    In het volgende codevoorbeeld wordt een back-up van transactielogboeken gemaakt op MyDb1 en op MyDb2.

    -- On the server instance that hosts the primary replica,   
-- Backup the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT;  
GO  

BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NOFORMAT;  
GO

    Aanbeveling

    Normaal gesproken moet een logboekback-up worden gemaakt op elke primaire database en vervolgens worden hersteld op de bijbehorende secundaire database (met BEHULP van WITH NORECOVERY). Deze logboekback-up is echter mogelijk niet nodig als de database zojuist is gemaakt en er nog geen logboekback-up is gemaakt of als het herstelmodel zojuist is gewijzigd van SIMPLE in FULL.

  8. Pas op het serverexemplaar waarop de secundaire replica wordt gehost logboekback-ups toe op de secundaire databases.

    In het volgende codevoorbeeld worden back-ups toegepast op secundaire MyDb1 - en MyDb2-databases door databaseback-ups te herstellen met RESTORE WITH NORECOVERY.

    Belangrijk

    Wanneer u een echte secundaire database voorbereidt, moet u elke logboekback-up toepassen die is gemaakt sinds de databaseback-up van waaruit u de secundaire database hebt gemaakt, te beginnen met de vroegste en altijd met RESTORE WITH NORECOVERY. Als u zowel volledige als differentiële databaseback-ups herstelt, hoeft u alleen de logboekback-ups toe te passen die zijn gemaakt na de differentiële back-up.

    -- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY;  
GO

  9. Voeg de nieuwe secundaire databases toe aan de beschikbaarheidsgroep op het serverexemplaar waarop de replica wordt gehost.

    Het volgende codevoorbeeld voegt de secundaire MyDb1-database toe en vervolgens de secundaire MyDb2-databases aan de MyAG-beschikbaarheidsgroep .

    -- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  

ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO

Voorbeeld van volledige code voor voorbeeldconfiguratieprocedure

In het volgende voorbeeld worden de codevoorbeelden samengevoegd uit alle stappen van de voorbeeldconfiguratieprocedure. De volgende tabel bevat een overzicht van de tijdelijke aanduidingen die in dit codevoorbeeld worden gebruikt. Zie Vereisten voor het gebruik van de voorbeeldconfiguratieprocedure en voorbeeldconfiguratieprocedure, eerder in dit onderwerp voor meer informatie over de stappen in dit codevoorbeeld.

Plaatsvervanger Beschrijving
\\ FILESERVER\SQL-backups Fictieve back-up-share.
\\ FILESERVER\SQLbackups\MyDb1.bak Back-upbestand voor MyDb1.
\\ FILESERVER\SQLbackups\MyDb2.bak Back-upbestand voor MyDb2.
7022 Poortnummer dat is toegewezen aan elk eindpunt voor databasespiegeling.
COMPUTER01\AgHostInstance Serverexemplaar dat als host fungeert voor de eerste primaire replica.
COMPUTER02 Serverexemplaar dat de eerste secundaire replica host. Dit is het standaardserverexemplaar op COMPUTER02.
dbm_endpoint De naam die is opgegeven voor elk eindpunt voor databasespiegeling.
MyAG Naam van voorbeeld van beschikbaarheidsgroep.
MyDb1 Naam van de eerste voorbeelddatabase.
MyDb2 Naam van de tweede voorbeelddatabase.
DOMAIN1\gebruiker1 Serviceaccount van het serverexemplaar dat als host fungeert voor de initiële primaire replica.
DOMAIN2\gebruiker2 Serviceaccount van de serverexemplaar dat de initiële secundaire replica host.
TCP:// COMPUTER01.Adventure-Works.com:7022 Eindpunt-URL van het AgHostInstance-exemplaar van SQL Server op COMPUTER01.
TCP:// COMPUTER02.Adventure-Works.com:5022 Eindpunt-URL van het standaardexemplaar van SQL Server op COMPUTER02.

Opmerking

Zie CREATE AVAILABILITY GROUP (Transact-SQL) voor meer Transact-SQL codevoorbeelden voor het maken van een beschikbaarheidsgroep.

-- on the server instance that will host the primary replica,   
-- create sample databases:  
CREATE DATABASE MyDb1;  
GO  
ALTER DATABASE MyDb1 SET RECOVERY FULL;  
GO  
  
CREATE DATABASE MyDb2;  
GO  
ALTER DATABASE MyDb2 SET RECOVERY FULL;  
GO  
  
-- Backup sample databases:  
BACKUP DATABASE MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FORMAT;  
GO  
  
BACKUP DATABASE MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FORMAT;  
GO  
  
-- Create the endpoint on the server instance that will host the primary replica:  
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- Create the endpoint on the server instance that will host the secondary replica:   
CREATE ENDPOINT dbm_endpoint  
    STATE=STARTED   
    AS TCP (LISTENER_PORT=7022)   
    FOR DATABASE_MIRRORING (ROLE=ALL);  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the primary replica,   
-- create a login for the service account   
-- of the server instance that will host the secondary replica, DOMAIN2\user2,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
CREATE LOGIN [DOMAIN2\user2] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN2\user2];  
GO  
  
-- If both service accounts run under the same domain account, skip this step. Otherwise,   
-- On the server instance that will host the secondary replica,  
-- create a login for the service account   
-- of the server instance that will host the primary replica, DOMAIN1\user1,   
-- and grant this login connect permissions on the endpoint:  
USE master;  
GO  
  
CREATE LOGIN [DOMAIN1\user1] FROM WINDOWS;  
GO  
GRANT CONNECT ON ENDPOINT::dbm_endpoint   
   TO [DOMAIN1\user1];  
GO  
  
-- On the server instance that will host the primary replica,   
-- create the availability group, MyAG:  
CREATE AVAILABILITY GROUP MyAG   
   FOR   
      DATABASE MyDB1, MyDB2   
   REPLICA ON   
      'COMPUTER01\AgHostInstance' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER01.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         ),  
      'COMPUTER02' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER02.Adventure-Works.com:7022',  
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,  
         FAILOVER_MODE = AUTOMATIC  
         );   
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join the secondary replica to the availability group:  
ALTER AVAILABILITY GROUP MyAG JOIN;  
GO  
  
-- Restore database backups onto this server instance, using RESTORE WITH NORECOVERY:  
RESTORE DATABASE MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NORECOVERY;  
GO  
  
RESTORE DATABASE MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NORECOVERY;  
GO  
  
-- Back up the transaction log on each primary database:  
BACKUP LOG MyDb1   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH NOFORMAT;  
GO  
  
BACKUP LOG MyDb2   
TO DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH NOFORMAT  
GO  
  
-- Restore the transaction log on each secondary database,  
-- using the WITH NORECOVERY option:  
RESTORE LOG MyDb1   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb1.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
RESTORE LOG MyDb2   
    FROM DISK = N'\\FILESERVER\SQLbackups\MyDb2.bak'   
    WITH FILE=1, NORECOVERY;  
GO  
  
-- On the server instance that hosts the secondary replica,   
-- join each secondary database to the availability group:  
ALTER DATABASE MyDb1 SET HADR AVAILABILITY GROUP = MyAG;  
GO  
  
ALTER DATABASE MyDb2 SET HADR AVAILABILITY GROUP = MyAG;  
GO

Gerelateerde taken

Eigenschappen van beschikbaarheidsgroep en replica configureren

De configuratie van de beschikbaarheidsgroep voltooien

Alternatieve manieren om een beschikbaarheidsgroep te maken

AlwaysOn-beschikbaarheidsgroepen inschakelen

Een eindpunt voor databasespiegeling configureren

Problemen met de configuratie van AlwaysOn-beschikbaarheidsgroepen oplossen

Verwante inhoud

Zie ook

Het Eindpunt voor Databasespiegeling (SQL Server)
overzicht van AlwaysOn-beschikbaarheidsgroepen (SQL Server)
Listeners voor Beschikbaarheidsgroepen, Clientconnectiviteit en Toepassingsfailover (SQL Server)
vereisten, beperkingen en aanbevelingen voor AlwaysOn-beschikbaarheidsgroepen (SQL Server)