Konfigurieren des schreibgeschützten Zugriffs auf ein Verfügbarkeitsreplikat (SQL Server)

 

Standardmäßig sind sowohl der Lese-/Schreibzugriff als auch der Zugriff für beabsichtigte Lesevorgänge für das primäre Replikat zulässig, während für sekundäre Replikate einer AlwaysOn-Verfügbarkeitsgruppe keine Verbindungen zulässig sind. In diesem Thema wird beschrieben, wie der Verbindungszugriff für ein Verfügbarkeitsreplikat einer AlwaysOn-Verfügbarkeitsgruppe in SQL Server 2016 unter Verwendung von SQL Server Management Studio, Transact-SQL oder PowerShell konfiguriert wird.

Informationen dazu, welche Auswirkungen die Aktivierung des schreibgeschützten Zugriffs für ein sekundäres Replikat hat, und eine Einführung in den Verbindungszugriff finden Sie unter Informationen zum Clientverbindungszugriff auf Verfügbarkeitsreplikate (SQL Server) und Aktive sekundäre Replikate: Lesbare sekundäre Replikate (AlwaysOn-Verfügbarkeitsgruppen).

Voraussetzungen und Einschränkungen

  • Um einen anderen Verbindungszugriff zu konfigurieren, benötigen Sie eine Verbindung zur Serverinstanz, die das primäre Replikat hostet.

Sicherheit

Berechtigungen

TaskBerechtigungen
So konfigurieren Sie Replikate beim Erstellen einer VerfügbarkeitsgruppeErfordert die Mitgliedschaft in der festen Serverrolle sysadmin und die CREATE AVAILABILITY GROUP-Serverberechtigung, ALTER ANY AVAILABILITY GROUP-Berechtigung oder CONTROL SERVER-Berechtigung.
So ändern Sie ein VerfügbarkeitsreplikatErfordert die ALTER AVAILABILITY GROUP-Berechtigung für die Verfügbarkeitsgruppe, die CONTROL AVAILABILITY GROUP-Berechtigung, die ALTER ANY AVAILABILITY GROUP-Berechtigung oder die CONTROL SERVER-Berechtigung.

So konfigurieren Sie den Zugriff auf einem Verfügbarkeitsreplikat

  1. Stellen Sie im Objekt-Explorer eine Verbindung mit der Serverinstanz her, die das primäre Verfügbarkeitsreplikat hostet, und erweitern Sie die Serverstruktur.

  2. Erweitern Sie die Knoten Hohe Verfügbarkeit mit AlwaysOn und Verfügbarkeitsgruppen.

  3. Klicken Sie auf die Verfügbarkeitsgruppe, deren Replikat geändert werden soll.

  4. Klicken Sie mit der rechten Maustaste auf das Verfügbarkeitsreplikat, und klicken Sie auf Eigenschaften.

  5. Im Dialogfeld Eigenschaften des Verfügbarkeitsreplikats können Sie den Verbindungszugriff für die primäre Rolle und die sekundäre Rolle wie folgt ändern:

    • Wählen Sie für die sekundäre Rolle aus der Dropdownliste für die lesbare sekundäre Rolle ** ** wie folgt einen neuen Wert aus:

      Nein
      Es werden keine Verbindungen mit sekundären Datenbanken dieses Replikats zugelassen. Sie sind für den Lesezugriff nicht verfügbar. Dies ist die Standardeinstellung.

      Nur beabsichtigte Lesevorgänge
      Es sind nur schreibgeschützte Verbindungen zu sekundären Datenbanken dieses Replikats zulässig. Die sekundären Datenbanken sind alle für Lesezugriff verfügbar.

      ja
      Alle Verbindungen zu sekundären Datenbanken dieses Replikats sind zugelassen, aber nur für Lesezugriff. Die sekundären Datenbanken sind alle für Lesezugriff verfügbar.

    • Wählen Sie für die primäre Rolle aus der Dropdownliste für Verbindungen in der primären Rolle ** ** einen neuen Wert wie folgt aus:

      Alle Verbindungen zulassen
      Für die Datenbanken im primären Replikat sind alle Verbindungen zugelassen. Dies ist die Standardeinstellung.

      Verbindungen mit Lese-/Schreibzugriff zulassen
      Wenn die Eigenschaft für die Anwendungsabsicht auf ReadWrite festgelegt ist oder keine Verbindungseigenschaft für die Anwendungsabsicht festgelegt wurde, wird die Verbindung zugelassen. Verbindungen, bei denen die Verbindungseigenschaft für die Anwendungsabsicht auf ReadOnly festgelegt ist, werden zugelassen. Dies kann verhindern, dass Kunden mit dem primären Replikat versehentlich eine leseintensive Arbeitsauslastung verbinden. Weitere Informationen zur Verbindungseigenschaft für die Anwendungsabsicht finden Sie unter Using Connection String Keywords with SQL Server Native Client.

So konfigurieren Sie den Zugriff auf einem Verfügbarkeitsreplikat

System_CAPS_ICON_note.jpg Hinweis


Ein Beispiel für diese Prozedur finden Sie weiter unten in diesem Abschnitt unter Beispiel (Transact-SQL).

  1. Stellen Sie eine Verbindung mit der Serverinstanz her, die das primäre Replikat hostet.

  2. Wenn Sie ein Replikat für eine neue Verfügbarkeitsgruppe angeben, verwenden Sie die -AnweisungTransact-SQL . Verwenden Sie zum Hinzufügen oder Ändern eines Replikats für eine vorhandene Verfügbarkeitsgruppe die -AnweisungTransact-SQL .

    • Geben Sie zum Konfigurieren des Verbindungszugriffs für die sekundäre Rolle in der ADD REPLICA- bzw. MODIFY REPLICA WITH-Klausel die SECONDARY_ROLE-Option wie folgt an:

      SECONDARY_ROLE ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

      Erläuterungen:

      NO
      Es werden keine direkten Verbindungen mit sekundären Datenbanken dieses Replikats zugelassen. Sie sind für den Lesezugriff nicht verfügbar. Dies ist die Standardeinstellung.

      READ_ONLY
      Es sind nur schreibgeschützte Verbindungen zu sekundären Datenbanken dieses Replikats zulässig. Die sekundären Datenbanken sind alle für Lesezugriff verfügbar.

      ALL
      Alle Verbindungen zu sekundären Datenbanken dieses Replikats sind zugelassen, aber nur für Lesezugriff. Die sekundären Datenbanken sind alle für Lesezugriff verfügbar.

  3. Geben Sie zum Konfigurieren des Verbindungszugriffs für die primäre Rolle in der ADD REPLICA- bzw. MODIFY REPLICA WITH-Klausel die PRIMARY_ROLE-Option wie folgt an:

    PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

    Erläuterungen:

    READ_WRITE
    Verbindungen, bei denen die Verbindungseigenschaft für die Anwendungsabsicht auf ReadOnly festgelegt ist, werden nicht zugelassen. Wenn die Eigenschaft für die Anwendungsabsicht auf ReadWrite festgelegt ist oder keine Verbindungseigenschaft für die Anwendungsabsicht festgelegt wurde, wird die Verbindung zugelassen. Weitere Informationen zur Verbindungseigenschaft für die Anwendungsabsicht finden Sie unter Using Connection String Keywords with SQL Server Native Client.

    ALL
    Für die Datenbanken im primären Replikat sind alle Verbindungen zugelassen. Dies ist die Standardeinstellung.

Beispiel (Transact-SQL)

Im folgenden Beispiel wird einer Verfügbarkeitsgruppe namens AG2ein sekundäres Replikat hinzugefügt. Zum Hosten des neuen Verfügbarkeitsreplikats wurde die eigenständige Serverinstanz COMPUTER03\HADR_INSTANCE angegeben. Dieses Replikat ist zum ausschließlichen Zulassen von Verbindungen mit Lese-/Schreibzugriff für die primäre Rolle sowie zum ausschließlichen Zulassen von Verbindungen mit beabsichtigten Lesevorgängen konfiguriert.

ALTER AVAILABILITY GROUP AG2   
   ADD REPLICA ON   
      'COMPUTER03\HADR_INSTANCE' WITH   
         (  
         ENDPOINT_URL = 'TCP://COMPUTER03:7022',  
         PRIMARY_ROLE ( ALLOW_CONNECTIONS = READ_WRITE ),  
         SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY )  
         );   
GO  

So konfigurieren Sie den Zugriff auf einem Verfügbarkeitsreplikat

System_CAPS_ICON_note.jpg Hinweis


Ein Codebeispiel finden Sie weiter unten in diesem Abschnitt unter Beispiel (PowerShell).

  1. Wechseln Sie mit cd in das Verzeichnis der Serverinstanz, auf der das primäre Replikat gehostet wird.

  2. Verwenden Sie zum Hinzufügen eines Verfügbarkeitsreplikats zu einer Verfügbarkeitsgruppe das Cmdlet New-SqlAvailabilityReplica. Verwenden Sie zum Ändern eines vorhandenen Verfügbarkeitsreplikats das Cmdlet Set-SqlAvailabilityReplica. Die relevanten Parameter lauten wie folgt:

    • Um den Verbindungszugriff für die sekundäre Rolle zu konfigurieren, geben Sie den Parameter ConnectionModeInSecondaryRole secondary_role_keyword an, wobei secondary_role_keyword einem der folgenden Werte entspricht:

      AllowNoConnections
      Für die Datenbanken im sekundären Replikat sind keine direkten Verbindungen zugelassen, und die Datenbanken sind für den Lesezugriff nicht verfügbar. Dies ist die Standardeinstellung.

      AllowReadIntentConnectionsOnly
      Verbindungen mit den Datenbanken im sekundären Replikat sind nur zulässig, wenn die Eigenschaft für die Anwendungsabsicht auf ReadOnly festgelegt ist. Weitere Informationen zu dieser Eigenschaft finden Sie unter Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnections
      Für alle Verbindungen mit den Datenbanken im sekundären Replikat ist der schreibgeschützte Zugriff zugelassen.

    • Um den Verbindungszugriff für die primäre Rolle zu konfigurieren, geben Sie ConnectionModeInPrimaryRole primary_role_keyword an, wobei primary_role_keyword einem der folgenden Werte entspricht:

      AllowReadWriteConnections
      Verbindungen, bei denen die Verbindungseigenschaft für die Anwendungsabsicht auf ReadOnly festgelegt ist, werden nicht zugelassen. Wenn die Eigenschaft für die Anwendungsabsicht auf ReadWrite festgelegt ist oder keine Verbindungseigenschaft für die Anwendungsabsicht festgelegt wurde, wird die Verbindung zugelassen. Weitere Informationen zur Verbindungseigenschaft für die Anwendungsabsicht finden Sie unter Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnections
      Für die Datenbanken im primären Replikat sind alle Verbindungen zugelassen. Dies ist die Standardeinstellung.

    System_CAPS_ICON_note.jpg Hinweis


    Um die Syntax eines Cmdlets anzuzeigen, verwenden Sie das Cmdlet Get-Help in der SQL Server 2016-PowerShell-Umgebung. Weitere Informationen finden Sie unter Get Help SQL Server PowerShell.

Einrichten und Verwenden des SQL Server PowerShell-Anbieters

Beispiel (PowerShell)

Im folgenden Beispiel wird sowohl der ConnectionModeInSecondaryRole -Parameter als auch der ConnectionModeInPrimaryRole -Parameter auf AllowAllConnectionsfestgelegt.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
Set-SqlAvailabilityReplica -ConnectionModeInSecondaryRole "AllowAllConnections" `   
-InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ConnectionModeInPrimaryRole "AllowAllConnections" `   
-InputObject $primaryReplica  
  

Schreibgeschützter Zugriff auf ein lesbares sekundäres Replikat

Faktoren, die sich auf Trigger und Aufträge nach einem Failover auswirken können

Wenn Sie Trigger und Aufträge haben, die beim Ausführen auf einer nicht lesbaren sekundären Datenbank oder einer lesbaren sekundären Datenbank fehlschlagen, müssen Sie ein Skript für die Trigger und Aufträge erstellen, die auf einem angegebenen Replikat kontrolliert werden sollen, um zu bestimmen, ob die Datenbank eine primäre Datenbank oder eine lesbare sekundäre Datenbank ist. Um diese Informationen abzurufen, verwenden Sie die DATABASEPROPERTYEX -Funktion, um die Updatability -Eigenschaft der Datenbank zurückzugeben. Um eine schreibgeschützte Datenbank zu identifizieren, geben Sie READ_ONLY wie folgt als Wert an:

DATABASEPROPERTYEX([db name],’Updatability’) = N’READ_ONLY’  

Um eine Datenbank mit Lese-/Schreibzugriff zu identifizieren, geben Sie READ_WRITE als Wert an.

Übersicht über AlwaysOn-Verfügbarkeitsgruppen (SQL Server)
Aktive sekundäre Replikate: Lesbare sekundäre Replikate (AlwaysOn-Verfügbarkeitsgruppen)
Informationen zum Clientverbindungszugriff auf Verfügbarkeitsreplikate (SQL Server)

Community-Beiträge

HINZUFÜGEN
Anzeigen: