Konfigurieren Sie die Leseskalierung für eine Always On-Verfügbarkeitsgruppe

Gilt für:SQL Server

Sie können unter Windows eine SQL Server-„Always On“-Verfügbarkeitsgruppe für Workloads mit Leseskalierung konfigurieren. Es gibt zwei Architekturtypen für Verfügbarkeitsgruppen:

  • Eine Architektur für Hochverfügbarkeit, die einen Cluster-Manager zur Gewährleistung einer verbesserten Geschäftskontinuität verwendet und lesbare sekundäre Replikate enthalten kann. Informationen zur Erstellung dieser Architektur für Hochverfügbarkeit finden Sie unter Erstellung und Konfiguration von Verfügbarkeitsgruppen unter Windows.
  • Eine Architektur, die nur leseskalierende Workloads unterstützt.

In diesem Artikel wird erläutert, wie eine Verfügbarkeitsgruppe ohne einen Cluster-Manager für leseskalierte Workloads erstellt wird. Diese Architektur unterstützt ausschließlich die Skalierung von Lesezugriffen. Es bietet keine Hochverfügbarkeit.

Hinweis

Eine Verfügbarkeitsgruppe mit CLUSTER_TYPE = NONE kann Replikate enthalten, die auf verschiedenen Betriebssystemplattformen gehostet werden. Sie kann keine Unterstützung für Hochverfügbarkeit bieten. Weitere Informationen für Linux-Betriebssysteme finden Sie unter Konfigurieren einer SQL Server-Verfügbarkeitsgruppe zur Leseskalierung unter Linux.

Voraussetzungen

Bevor Sie die Verfügbarkeitsgruppe erstellen, müssen Sie:

  • Ihre Umgebung so konfigurieren, dass alle Server kommunizieren können, die Verfügbarkeitsreplikate hosten sollen.
  • Installieren Sie SQL Server. Ausführliche Informationen finden Sie im SQL Server-Installationshandbuch .

Aktivieren von Always On-Verfügbarkeitsgruppen und Neustarten von mssql-server

Hinweis

Der folgende Befehl verwendet Cmdlets aus dem sqlserver-Modul, das in der PowerShell-Katalog veröffentlicht ist. Sie können dieses Modul mithilfe des Install-Module Befehls installieren.

Aktivieren Sie Always On-Verfügbarkeitsgruppen auf jedem Replikat, das eine SQL Server-Instanz hostet. Starten Sie anschließend den SQL Server-Dienst neu. Führen Sie für die Aktivierung und das Neustarten der SQL Server-Dienste den folgenden Befehl aus:

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

Aktivieren einer AlwaysOn_health-Ereignissitzung

Um Sie bei der Analyse der Grundursache bei der Problembehandlung einer Verfügbarkeitsgruppe zu unterstützen, können Sie optional eine Sitzung für erweiterte Ereignisse (XEvents) für Always On-Verfügbarkeitsgruppen aktivieren. Führen Sie hierfür auf jeder SQL Server-Instanz den folgenden Befehl aus:

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

Weitere Informationen zu dieser XEvents-Sitzung finden Sie unter Konfigurieren erweiterter Ereignisse für Verfügbarkeitsgruppen.

Authentifizierung über den Datenbankspiegelungsendpunkt

Damit die Synchronisierung ordnungsgemäß funktioniert, müssen sich die an der Verfügbarkeitsgruppe zur Leseskalierung beteiligten Replikate über den Endpunkt authentifizieren. Im nächsten Abschnitt werden die beiden wichtigsten Szenarios behandelt, die Sie für diese Authentifizierung verwenden können.

Dienstkonto

In einer Active Directory-Umgebung, in der sämtliche sekundäre Replikate mit derselben Domäne verknüpft sind, kann SQL Server eine Authentifizierung über das Dienstkonto durchführen. Sie müssen auf jeder SQL Server-Instanz explizit einen Anmeldenamen für das Dienstkonto erstellen:

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

SQL-Anmeldeauthentifizierung

In Umgebungen, in denen die sekundären Replikate nicht mit einer Active Directory-Domäne verknüpft sind, müssen Sie die SQL-Authentifizierung verwenden. Das folgende Transact-SQL-Skript erstellt eine Anmeldung mit dem Namen dbm_login und einen Benutzer mit dem Namen dbm_user. Ersetzen Sie <password> durch ein gültiges Kennwort. Führen Sie zum Erstellen des Datenbankspiegelungsendpunktbenutzers den folgenden Befehl für alle SQL Server-Instanzen aus.

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

Zertifikatauthentifizierung

Wenn Sie ein sekundäres Replikat verwenden, für das eine Authentifizierung über die SQL-Authentifizierung erforderlich ist, verwenden Sie ein Zertifikat für die Authentifizierung zwischen den Spiegelungsendpunkten.

Das folgende Transact-SQL-Skript erstellt einen Hauptschlüssel und ein Zertifikat. Anschließend werden das Zertifikat und die Datei mit einem privaten Schlüssel gesichert. Aktualisieren Sie das Skript durch sichere Kennwörter. Führen Sie das Skript auf der primären SQL Server-Instanz aus, um das Zertifikat auszuführen:

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

Zu diesem Zeitpunkt weist Ihr primäres SQL Server-Replikat ein Zertifikat unter c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.cer und einen privaten Schlüssel unter c:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\dbm_certificate.pvk auf. Kopieren Sie diese beiden Dateien auf allen Servern, die Verfügbarkeitsreplikate hosten werden, an den gleichen Speicherort.

Stellen Sie bei jedem sekundären Replikat sicher, dass das Dienstkonto der SQL Server-Instanz über Berechtigungen für den Zugriff auf das Zertifikat verfügt.

Erstellen des Zertifikats auf sekundären Servern

Das folgende Transact-SQL-Skript erstellt einen Hauptschlüssel und ein Zertifikat aus der Sicherung, die Sie auf dem primären SQL Server-Replikat erstellt haben. Der Befehl autorisiert auch die Benutzer, damit diese auf das Zertifikat zugreifen können. Aktualisieren Sie das Skript durch sichere Kennwörter. Das Entschlüsselungskennwort ist das gleiche Kennwort, mit dem Sie in einem vorherigen Schritt die PVK-Datei erstellt haben. Führen Sie das folgende Skript auf allen sekundären Replikaten aus, um das Zertifikat zu erstellen:

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

Erstellen Sie Datenbankspiegelungsendpunkte auf allen Replikaten

Datenbankspiegelungsendpunkte verwenden das Transmission Control Protocol (TCP), um Nachrichten zwischen den Serverinstanzen zu senden und zu empfangen, die an Datenbankspiegelungssitzungen teilnehmen oder Verfügbarkeitsreplikate hosten. Der Datenbank-Spiegelungsendpunkt überwacht eine eigene TCP-Portnummer.

Das folgende Transact-SQL-Skript erstellt für die Verfügbarkeitsgruppe einen Überwachungsendpunkt mit dem Namen Hadr_endpoint. Es startet den Endpunkt und erteilt eine Verbindungsberechtigung für das Dienstkonto oder die SQL-Anmeldung, die Sie in einem vorherigen Schritt erstellt haben. Bevor Sie das Skript ausführen, ersetzen Sie die Werte zwischen < ... >. Sie können optional eine IP-Adresse einschließen (LISTENER_IP = (0.0.0.0)). Bei der IP-Adresse des Listeners muss es sich um eine IPv4-Adresse handeln. Sie können auch 0.0.0.0 verwenden.

Aktualisieren Sie auf sämtlichen SQL Server-Instanzen das folgende Transact-SQL-Skript für Ihre Umgebung:

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>];

Der TCP-Port in der Firewall muss für den Listenerport geöffnet sein.

Weitere Informationen finden Sie unter Den Datenbankspiegelungsendpunkt (SQL Server).

Erstellen einer Verfügbarkeitsgruppe

Erstellen einer Verfügbarkeitsgruppe Legen Sie CLUSTER_TYPE = NONE fest. Darüber hinaus konfigurieren Sie jedes Replikat mit FAILOVER_MODE = NONE. Clientanwendungen, die Analysen durchführen oder Berichte zu Workloads erstellen, können eine direkte Verbindung mit sekundären Datenbanken herstellen. Sie können ebenfalls eine schreibgeschützte Routingliste erstellen. Verbindungen zum primären Replikat leiten Leseverbindungsanforderungen im Round-Robin-Verfahren an die einzelnen sekundären Replikate aus der Routingliste weiter.

Im folgenden Transact-SQL-Skript wird eine Verfügbarkeitsgruppe namens ag1 erstellt. Das Skript konfiguriert die Verfügbarkeitsgruppenreplikate mit SEEDING_MODE = AUTOMATIC. Diese Einstellung bewirkt, dass SQL Server die Datenbank automatisch auf jedem sekundären Server erstellt, nachdem diese zur Verfügbarkeitsgruppe hinzugefügt wurde.

Aktualisieren Sie das folgende Skript für Ihre Umgebung. Ersetzen Sie die Werte <node1> und <node2> durch die Namen der SQL Server-Instanzen, auf denen die Replikate gehostet werden. Ersetzen Sie den Wert <5022> durch den Port, den Sie für den Endpunkt festgelegt haben. Führen Sie auf dem primären SQL Server-Replikat das folgende Transact-SQL-Skript aus:

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;

Verknüpfen von sekundären SQL Server-Instanzen mit der Verfügbarkeitsgruppe

Das folgende Transact-SQL-Skript verknüpft einen Server mit einer Verfügbarkeitsgruppe namens ag1. Aktualisieren Sie das Skript für Ihre Umgebung. Führen Sie folgendes Transact-SQL-Skript auf jedem sekundären SQL Server-Replikat aus, um die Verfügbarkeitsgruppe zu verknüpfen:

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

ALTER AVAILABILITY GROUP [ag1] GRANT CREATE ANY DATABASE;

Hinzufügen einer Datenbank zu einer Verfügbarkeitsgruppe

Stellen Sie sicher, dass sich die Datenbank, die Sie der Verfügbarkeitsgruppe hinzufügen, im vollständigen Wiederherstellungsmodell befindet und eine gültige Protokollsicherung hat. Wenn es sich bei der Datenbank um eine Testdatenbank oder eine neu erstellte Datenbank handelt, erstellen Sie eine Datenbanksicherung. Führen Sie zum Erstellen und Sichern einer Datenbank namens db1 das folgende Transact-SQL-Skript auf der primären SQL Server-Instanz aus:

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

Führen Sie zum Hinzufügen einer Datenbank namens db1 für eine Verfügbarkeitsgruppe namens ag1 das folgende Transact-SQL-Skript auf dem primären SQL Server-Replikat aus:

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

Sicherstellen, dass die Datenbank auf den sekundären Servern erstellt wird

Führen Sie zum Anzeigen, ob die Datenbank db1 erstellt und synchronisiert wurde, die folgende Abfrage auf allen sekundären SQL Server-Replikaten aus:

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;

Bei dieser Verfügbarkeitsgruppe handelt es sich nicht um eine Hochverfügbarkeitskonfiguration. Wenn Sie Hochverfügbarkeit benötigen, befolgen Sie die Anweisungen unter Konfigurieren einer Always On-Verfügbarkeitsgruppe für SQL Server unter Linux oder unter Erstellung und Konfiguration von Verfügbarkeitsgruppen unter Windows.

Mit schreibgeschützten sekundären Replikaten verbinden

Sie können auf zwei Arten eine Verbindung mit schreibgeschützten sekundären Replikaten herstellen:

  • Anwendungen können eine direkte Verbindung mit der SQL Server-Instanz herstellen, auf der das sekundäre Replikat gehostet wird, und die Datenbanken abfragen. Weitere Informationen finden Sie unter Lesbare sekundäre Replikate.
  • Anwendungen können auch schreibgeschütztes Routing verwenden. Hierfür ist ein Listener erforderlich. Wenn Sie ein Szenario mit Leseskalierung ohne einen Cluster-Manager bereitstellen, können Sie dennoch einen Listener erstellen, der auf die IP-Adresse des aktuellen primären Replikats und den Port verweist, dem auch SQL Server lauscht. Nach einem Failover müssen Sie den Listener neu erstellen, um auf die neue primäre IP-Adresse zu verweisen. Weitere Informationen finden Sie unter Schreibgeschütztes Routing.

Ausführen eines Failovers des primären Replikats auf eine schreibgeschützte Verfügbarkeitsgruppe

Jede Verfügbarkeitsgruppe hat nur ein primäres Replikat. Das primäre Replikat lässt Lese- und Schreibvorgänge zu. Um zu ändern, welches Replikat das primäre ist, können Sie ein Failover ausführen. In einer typischen Verfügbarkeitsgruppe automatisiert der Cluster-Manager den Failoverprozess. In einer Verfügbarkeitsgruppe mit dem Clustertyp „NONE“ erfolgt der Failovervorgang manuell.

Es gibt zwei Möglichkeiten, ein Failover für ein primäres Replikat in einer Verfügbarkeitsgruppe mit dem Clustertyp „NONE“ auszuführen:

  • Manuelles Failover ohne Datenverlust
  • Erzwungenes manuelles Failover mit Datenverlust

Manuelles Failover ohne Datenverlust

Verwenden Sie diese Methode, wenn das primäre Replikat verfügbar ist. Dabei müssen Sie allerdings vorübergehend oder dauerhaft ändern, welche Instanz das primäre Replikat hostet. Um potenzielle Datenverluste zu vermeiden, stellen Sie vor der Ausführung eines manuellen Failovers sicher, dass das sekundäre Zielreplikat aktuell ist.

So führen Sie ein manuelles Failover ohne Datenverlust durch:

  1. Legen Sie das aktuelle primäre und das sekundäre Zielreplikat als SYNCHRONOUS_COMMIT fest.

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

  2. Um zu ermitteln, ob aktive Transaktionen im primären Replikat und in mindestens einem synchronen sekundären Replikat committet wurden, führen Sie die folgende Abfrage aus:

    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;

    Das sekundäre Replikat wird synchronisiert, wenn synchronization_state_descSYNCHRONIZED ist.

  3. Aktualisieren Sie REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT auf 1.

    Das folgende Skript legt REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT auf „1“ für eine Verfügbarkeitsgruppe mit dem Namen ag1 fest. Ersetzen Sie ag1 vor der Ausführung des Skripts durch den Namen Ihrer Verfügbarkeitsgruppe:

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

    Diese Einstellung stellt sicher, dass für jede aktive Transaktion ein Commit auf das primäre Replikat und auf mindestens ein synchrones sekundäres Replikat ausgeführt wurde.

    Hinweis

    Diese Einstellung ist nicht failoverspezifisch und sollte anhand der Umgebungsanforderungen festgelegt werden.

  4. Legen Sie zur Vorbereitung auf die Rollenänderung das primäre Replikat und die sekundären Replikate, die nicht am Failover beteiligt sind, offline fest:

    ALTER AVAILABILITY GROUP [AGRScale] OFFLINE

  5. Stufen Sie das sekundäre Zielreplikat auf ein primäres hoch.

    ALTER AVAILABILITY GROUP AGRScale FORCE_FAILOVER_ALLOW_DATA_LOSS;

  6. Aktualisieren Sie die Rolle des alten primären Replikats und weiterer sekundärer Replikate auf SECONDARY, und führen Sie den folgenden Befehl auf der SQL Server-Instanz aus, die das alte primäre Replikat hostet:

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

    Hinweis

    Um eine Verfügbarkeitsgruppe zu löschen, verwenden Sie DROP AVAILABILITY GROUP. Führen Sie bei Verfügbarkeitsgruppen, die mit dem Clustertyp „NONE“ oder „EXTERNAL“ erstellt wurden, den Befehl für alle Replikate aus, die der Verfügbarkeitsgruppe angehören.

  7. Setzen Sie die Datenverschiebung fort, und führen Sie folgenden Befehl für jede Datenbank in der Verfügbarkeitsgruppe auf der SQL Server-Instanz aus, die das primäre Replikat hostet:

    ALTER DATABASE [db1]
     SET HADR RESUME

  8. Erstellen Sie jeden Listener neu, den Sie für Leseskalierungszwecke erstellt haben und der nicht von einem Cluster-Manager verwaltet wird. Wenn der ursprüngliche Listener auf das alte primäre Replikat zeigt, löschen Sie ihn, und erstellen Sie ihn neu, um auf das neue primäre Replikat zu verweisen.

Erzwungenes manuelles Failover mit Datenverlust

Wenn das primäre Replikat nicht verfügbar ist und nicht sofort wieder hergestellt werden kann, müssen Sie ein Failover auf das sekundäre Replikat mit Datenverlust erzwingen. Wenn sich das ursprüngliche primäre Replikat jedoch nach einem Failover erholt, übernimmt es die primäre Rolle. Um zu vermeiden, dass jedes Replikat einen anderen Zustand aufweist, entfernen Sie das ursprüngliche primäre Replikat nach einem erzwungenen Failover mit Datenverlust aus der Verfügbarkeitsgruppe. Sobald die ursprüngliche primäre Instanz wieder online ist, entfernen Sie die Verfügbarkeitsgruppe vollständig von ihr.

Gehen Sie folgendermaßen vor, um ein manuelles Failover mit Datenverlust vom primären Replikat N1 zum sekundären Replikat N2 zu erzwingen:

  1. Initiieren Sie auf dem sekundären Replikat (N2) das erzwungene Failover:

    ALTER AVAILABILITY GROUP [AGRScale] FORCE_FAILOVER_ALLOW_DATA_LOSS;

  2. Entfernen Sie auf dem neuen primären Replikat (N2) das ursprüngliche primäre Replikat (N1):

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

  3. Überprüfen Sie, ob der gesamte Anwendungsdatenverkehr an den Listener und/oder das neue primäre Replikat geleitet wird.

  4. Wenn der ursprüngliche primäre Knoten (N1) online kommt, nehmen Sie die Verfügbarkeitsgruppe „AGRScale“ auf dem ursprünglichen primären Knoten (N1) sofort offline:

    ALTER AVAILABILITY GROUP [AGRScale] OFFLINE

  5. Wenn Daten oder nicht synchronisierte Änderungen vorhanden sind, behalten Sie diese Datei über Sicherungskopien oder andere Datenreplikationsoptionen bei, die Ihren Geschäftsanforderungen entsprechen.

  6. Entfernen Sie als Nächstes die Verfügbarkeitsgruppe aus der ursprünglichen primären Datenbank (N1):

    DROP AVAILABILITY GROUP [AGRScale];

  7. Löschen Sie die Datenbank der Verfügbarkeitsgruppe auf dem ursprünglichen primären Replikat (N1):

    USE [master]
GO
DROP DATABASE [AGDBRScale]
GO

  8. (Optional) Sie können, wenn gewünscht, N1 jetzt wieder als neues sekundäres Replikat der Verfügbarkeitsgruppe „AGRScale“ hinzufügen.

Wenn Sie einen Listener für die Verbindungsherstellung verwenden, müssen Sie den Listener nach dem Failover neu erstellen.

Nächste Schritte

  • Last updated on