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 Sie den Verbindungszugriff auf ein Verfügbarkeitsreplikat einer AlwaysOn-Verfügbarkeitsgruppe in SQL Server 2014 mithilfe von SQL Server Management Studio, Transact-SQL oder PowerShell konfigurieren.

Informationen zu den Auswirkungen der Aktivierung des schreibgeschützten Zugriffs für ein sekundäres Replikat 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).

Vorbereitungen

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

Aufgabe	Berechtigungen
So konfigurieren Sie Replikate beim Erstellen einer Verfügbarkeitsgruppe	Erfordert 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ügbarkeitsreplikat	Erfordert 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.

Verwendung von SQL Server Management Studio

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 den Knoten Hohe Verfügbarkeit (immer aktiviert) und den Knoten 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.

Verwenden von Transact-SQL

So konfigurieren Sie den Zugriff auf einem Verfügbarkeitsreplikat

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 Transact-SQL-Anweisung CREATE AVAILABILITY GROUP. Verwenden Sie zum Hinzufügen oder Ändern eines Replikats für eine vorhandene Verfügbarkeitsgruppe die Transact-SQL-Anweisung ALTER AVAILABILITY GROUP.

   • 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:

     Nein
     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 den Anwendungszweck 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_INSTANCEangegeben. 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  

PowerShell

So konfigurieren Sie den Zugriff auf einem Verfügbarkeitsreplikat

Hinweis

Ein Codebeispiel finden Sie in den PowerShell-Beispielen weiter unten in diesem Abschnitt.

1. Ändern Sie das Verzeichnis (cd) zur Serverinstanz, die das primäre Replikat hostet.

2. Wenn Sie einer Verfügbarkeitsgruppe ein Verfügbarkeitsreplikat hinzufügen, verwenden Sie das New-SqlAvailabilityReplica-Cmdlet. Wenn Sie ein vorhandenes Verfügbarkeitsreplikat ändern, verwenden Sie das Set-SqlAvailabilityReplica-Cmdlet. Die relevanten Parameter lauten wie folgt:

   • Geben Sie zum Konfigurieren des Verbindungszugriffs für die sekundäre Rolle den ConnectionModeInSecondaryRoleparameter 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 ReadOnlyfestgelegt 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.

   • Geben Sie ConnectionModeInPrimaryRole zum Konfigurieren des Verbindungszugriffs für die primäre Rolle 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.

   Hinweis

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

Informationen zum Einrichten und Verwenden des SQL Server PowerShell-Anbieters finden Sie unter SQL Server PowerShell-Anbieter.

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

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

Nachverfolgung: Nach der Konfiguration des schreibgeschützten Zugriffs für ein Verfügbarkeitsreplikat

Schreibgeschützter Zugriff auf ein lesbares sekundäres Replikat

• Wenn Sie das Hilfsprogramm bcp oder sqlcmd verwenden, können Sie den schreibgeschützten Zugriff auf jedes sekundäre Replikat angeben, das für schreibgeschützten Zugriff aktiviert ist, indem Sie den -K ReadOnly Switch angeben.

• So ermöglichen Sie, dass Clientanwendungen eine Verbindung mit lesbaren sekundären Replikaten herstellen können

  	Voraussetzung	Link
  	Stellen Sie sicher, dass die Verfügbarkeitsgruppe über einen Listener verfügt.	Erstellen oder Konfigurieren eines Verfügbarkeitsgruppenlisteners (SQL Server)
  	Konfigurieren Sie das schreibgeschützte Routing für eine Verfügbarkeitsgruppe.	Konfigurieren des schreibgeschützten Routings für eine Verfügbarkeitsgruppe (SQL Server)

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.

Related Tasks

• Konfigurieren des schreibgeschützten Routings für eine Verfügbarkeitsgruppe (SQL Server)

• Erstellen oder Konfigurieren eines Verfügbarkeitsgruppenlisteners (SQL Server)

Verwandte Inhalte

• Always On: Value Proposition of Readable Secondary (Always On: Leistungsversprechen lesbarer sekundärer Replikate)

• Always On: Why there are two options to enable a secondary replica for read workload? (Always On: Warum gibt es zwei Optionen zum Aktivieren eines sekundären Replikats für die Lesearbeitslast?)

• Always On: Setting up Readable Secondary Replica (Always On: Einrichten eines lesbaren sekundären Replikats)

• Always On: I just enabled Readable Secondary but my query is blocked? (Always On: Ich habe gerade lesbare sekundäre Replikate aktiviert, meine Abfrage wird jedoch blockiert.)

• Always On: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot (Always On: Zur Verfügung stellen aktueller Statistiken auf lesbaren sekundären Replikaten, schreibgeschützten Datenbanken und Datenbankmomentaufnahmen)

• Always On: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica (Always On: Herausforderungen bei Statistiken auf schreibgeschützten Datenbanken, Datenbank-Momentaufnahmen und sekundären Replikaten)

• Always On: Impact on the primary workload when you run reporting workload on the secondary replica (Always On: Auswirkungen auf die primäre Arbeitslast, wenn die Berichterstellungsarbeitslast auf dem sekundären Replikat ausgeführt wird)

• Always On: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation (Always On: Auswirkungen der Zuordnung der Berichterstellungsarbeitslast vom lesbaren sekundären Replikat zur Momentaufnahmeisolation)

• Always On: Minimizing blocking of REDO thread when running reporting workload on Secondary Replica (Always On: Minimieren der Blockierung von REDO-Threads beim Ausführen der Berichterstellungsarbeitslast auf sekundären Replikaten)

• Always On: Readable Secondary and data latency (Always On: Lesbares sekundäres Replikat und Datenlatenz)

Weitere Informationen

Übersicht über AlwaysOn-Verfügbarkeitsgruppen (SQL Server)
Aktive sekundäre Replikate: Auslagern von schreibgeschützten Arbeitsauslastungen auf ein sekundäres Replikat einer Always On-Verfügbarkeitsgruppe
Informationen zum Clientverbindungszugriff auf Verfügbarkeitsreplikate (SQL Server)