Configurare l'accesso in sola lettura su una replica secondaria di un gruppo di disponibilità Always On

Si applica a:SQL Server

Per impostazione predefinita, l'accesso in lettura e scrittura e l'accesso con finalità di lettura sono entrambi consentiti nella replica primaria, ma non sono consentite connessioni alle repliche secondarie di un gruppo di disponibilità AlwaysOn. Questo argomento descrive come configurare l'accesso alla connessione in una replica di disponibilità di un gruppo di disponibilità Always On in SQL Server usando SQL Server Management Studio, Transact-SQL o PowerShell.

Per informazioni sulle implicazioni dell'abilitazione dell'accesso di sola lettura per una replica secondaria e per un'introduzione all'accesso alla connessione, vedere Informazioni sull'accesso alla connessione client per le repliche di disponibilità (SQL Server) e Repliche secondarie attive: Repliche secondarie leggibili (Gruppi di disponibilità AlwaysOn).

Prerequisiti e restrizioni

• Per configurare un accesso alla connessione diverso, è necessario essere connessi all'istanza del server che ospita la replica primaria.

Autorizzazioni

Attività	Autorizzazioni
Per configurare le repliche durante la creazione di un gruppo di disponibilità	Sono necessarie l'appartenenza al ruolo predefinito del server sysadmin e l'autorizzazione server CREATE AVAILABILITY GROUP oppure l'autorizzazione ALTER ANY AVAILABILITY GROUP o CONTROL SERVER.
Per modificare una replica di disponibilità	Sono necessarie l'autorizzazione ALTER AVAILABILITY GROUP nel gruppo di disponibilità, l'autorizzazione CONTROL AVAILABILITY GROUP permission, l'autorizzazione ALTER ANY AVAILABILITY GROUP o l'autorizzazione CONTROL SERVER.

Utilizzo di SQL Server Management Studio

Per configurare l'accesso su una replica di disponibilità

1. In Esplora oggetti connettersi all'istanza del server che ospita la replica primaria ed espandere l'albero del server.

2. Espandere il nodo Disponibilità elevata AlwaysOn e il nodo Gruppi di disponibilità.

3. Fare clic sul gruppo di disponibilità di cui si desidera modificare la replica.

4. Fare clic con il pulsante destro del mouse sulla replica di disponibilità e scegliere Proprietà.

5. Nella finestra di dialogo Proprietà replica di disponibilità è possibile modificare l'accesso alla connessione per il ruolo primario e per il ruolo secondario, come segue:

   • Per il ruolo secondario, selezionare un nuovo valore dall'elenco a discesa Secondario leggibile , come segue:

     No
     Non sono consentite connessioni utente ai database secondari di questa replica. I database non sono disponibili per l'accesso in lettura. Si tratta dell'impostazione predefinita.

     Solo con finalità di lettura
     Sono consentite solo connessioni in sola lettura ai database secondari di questa replica. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.

     Sì
     Sono consentite tutte le connessioni ai database secondari di questa replica, ma solo per l'accesso in lettura. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.

   • Per il ruolo primario, selezionare un nuovo valore dall'elenco a discesa Connessioni nel ruolo primario , come segue:

     Consenti tutte le connessioni
     Sono consentite tutte le connessioni ai database nella replica primaria. Si tratta dell'impostazione predefinita.

     Consenti connessioni in lettura/scrittura
     Se la proprietà Finalità dell'applicazione è impostata su Lettura/Scrittura o se tale proprietà non è impostata, la connessione è consentita. Non sono consentite le connessioni in cui la proprietà di connessione Finalità dell'applicazione è impostata su Sola lettura . In questo modo è possibile impedire la connessione, per errore, di un carico di lavoro con finalità di lettura alla replica primaria da parte dei clienti. Per altre informazioni sulla proprietà di connessione Finalità dell'applicazione, vedere Using Connection String Keywords with SQL Server Native Client.

Utilizzo di Transact-SQL

Per configurare l'accesso su una replica di disponibilità

Nota

Per un esempio di questa procedura, vedere Esempio (Transact-SQL)più avanti in questa sezione.

1. Connettersi all'istanza del server che ospita la replica primaria.

2. Se si specifica una replica per un nuovo gruppo di disponibilità, usare l'istruzione CREATE AVAILABILITY GROUP di Transact-SQL. Se si aggiunge o si modifica una replica di un gruppo di disponibilità esistente, usare l'istruzione ALTER AVAILABILITY GROUP di Transact-SQL.

   • Per configurare l'accesso alla connessione per il ruolo secondario, nella clausola ADD REPLICA o MODIFY REPLICA WITH specificare l'opzione SECONDARY_ROLE, come segue:

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

     dove

     NO
     Non sono consentite connessioni dirette ai database secondari di questa replica. I database non sono disponibili per l'accesso in lettura. Si tratta dell'impostazione predefinita.

     READ_ONLY
     Sono consentite solo connessioni in sola lettura ai database secondari di questa replica. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.

     ALL
     Sono consentite tutte le connessioni ai database secondari di questa replica, ma solo per l'accesso in lettura. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.

3. Per configurare l'accesso alla connessione per il ruolo primario, nella clausola ADD REPLICA o MODIFY REPLICA WITH specificare l'opzione PRIMARY_ROLE, come segue:

   PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

   dove

   READ_WRITE
   Non sono consentite le connessioni in cui la proprietà di connessione Finalità dell'applicazione è impostata su ReadOnly . Se la proprietà Finalità dell'applicazione è impostata su Lettura/Scrittura o se tale proprietà non è impostata, la connessione è consentita. Per altre informazioni sulla proprietà di connessione Finalità dell'applicazione, vedere Using Connection String Keywords with SQL Server Native Client.

   ALL
   Sono consentite tutte le connessioni ai database nella replica primaria. Si tratta dell'impostazione predefinita.

Esempio (Transact-SQL)

L'esempio seguente aggiunge una replica secondaria a un gruppo di disponibilità denominato AG2. Un'istanza del server autonoma, COMPUTER03\HADR_INSTANCE, viene specificata per ospitare la nuova replica di disponibilità. Questa replica è configurata per consentire unicamente le connessioni in lettura e scrittura per il ruolo primario e le connessioni con finalità di lettura per il ruolo secondario.

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  

Utilizzo di PowerShell

Per configurare l'accesso su una replica di disponibilità

Nota

Per un esempio di codice, vedere Esempio (PowerShell), più avanti in questa sezione.

1. Cambiare la directory (cd) impostandola sull'istanza del server che ospita la replica primaria.

2. Quando si aggiunge una replica di disponibilità a un gruppo di disponibilità, usare il cmdlet New-SqlAvailabilityReplica . Quando si modifica una replica di disponibilità esistente, usare il cmdlet Set-SqlAvailabilityReplica . I parametri pertinenti sono i seguenti:

   • Per configurare l'accesso alla connessione per il ruolo secondario, specificare ConnectionModeInSecondaryRolesecondary_role_keyword , dove secondary_role_keyword corrisponde a uno dei valori seguenti:

     AllowNoConnections
     Non è consentita alcuna connessione diretta ai database nella replica secondaria e i database non sono disponibili per l'accesso in lettura. Si tratta dell'impostazione predefinita.

     AllowReadIntentConnectionsOnly
     Sono consentite solo connessioni ai database nella replica secondaria in cui la proprietà Finalità dell'applicazione è impostata su Sola lettura. Per altre informazioni su questa proprietà, vedere Using Connection String Keywords with SQL Server Native Client.

     AllowAllConnections
     Sono consentite tutte le connessioni ai database nella replica secondaria per l'accesso in sola lettura.

   • Per configurare l'accesso alla connessione per il ruolo primario, specificare ConnectionModeInPrimaryRoleprimary_role_keyword, dove primary_role_keyword corrisponde a uno dei valori seguenti:

     AllowReadWriteConnections
     Non sono consentite le connessioni in cui la proprietà di connessione Finalità dell'applicazione è impostata su ReadOnly . Se la proprietà Finalità dell'applicazione è impostata su ReadWrite o se tale proprietà non è impostata, la connessione è consentita. Per altre informazioni sulla proprietà di connessione Finalità dell'applicazione, vedere Using Connection String Keywords with SQL Server Native Client.

     AllowAllConnections
     Sono consentite tutte le connessioni ai database nella replica primaria. Si tratta dell'impostazione predefinita.

   Nota

   Per visualizzare la sintassi di un cmdlet, usare il cmdlet Get-Help nell'ambiente SQL Server PowerShell. Per altre informazioni, vedere Get Help SQL Server PowerShell.

Per impostare e utilizzare il provider PowerShell per SQL Server

• Provider PowerShell per SQL Server

Esempio (PowerShell)

Nell'esempio seguente vengono impostati i parametri ConnectionModeInSecondaryRole e ConnectionModeInPrimaryRole su AllowAllConnections.

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  
  

Completamento: Dopo la configurazione dell'accesso in sola lettura per una replica di disponibilità

Accesso in sola lettura a una replica secondaria leggibile.

• Quando si usa bcp Utility o sqlcmd Utility, è possibile specificare l'accesso in sola lettura a qualsiasi replica secondaria abilitata per l'accesso in sola lettura specificando l'opzione -K ReadOnly .

• Per consentire alle applicazioni client di connettersi a repliche secondarie leggibili:

Prerequisito	Collega
Assicurarsi che nel gruppo di disponibilità sia presente un listener.	Creare o configurare un listener del gruppo di disponibilità (SQL Server)
Configurare il routing di sola lettura per il gruppo di disponibilità.	Configurare il routing di sola lettura per un gruppo di disponibilità (SQL Server)

Fattori che potrebbero influire su trigger e processi dopo un failover

Se sono presenti trigger e processi che avranno esito negativo se vengono eseguiti su una replica secondaria non leggibile o su un database secondario leggibile, è necessario generare script per trigger e processi per effettuare una verifica su una replicato specifica per determinare se il database è un database primario o un database secondario leggibile. Per ottenere queste informazioni, usare la funzione DATABASEPROPERTYEX per restituire la proprietà Updateability del database. Per identificare un database di sola lettura, specificare il valore READ_ONLY come indicato di seguito:

DATABASEPROPERTYEX([db name],'UpdateAbility') = N'READ_ONLY'  

Per identificare un database di lettura/scrittura, specificare il valore READ_WRITE.

Attività correlate

• Configurare il routing di sola lettura per un gruppo di disponibilità (SQL Server)

• Creare o configurare un listener del gruppo di disponibilità (SQL Server)

Contenuto correlato

• AlwaysOn: Value Proposition of Readable Secondary (AlwaysOn: Proposta di valore di Secondario leggibile)

• AlwaysOn: Why there are two options to enable a secondary replica for read workload? (AlwaysOn: Perché ci sono due opzioni per abilitare una replica secondaria per il carico di lavoro di lettura?)

• Always On: impostazione della replica secondaria leggibile

• AlwaysOn: I just enabled Readable Secondary but my query is blocked? (AlwaysOn: Ho appena abilitato Secondario leggibile ma la query rimane bloccata?)

• AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot (AlwaysOn: Rendere disponibili le statistiche più recenti in Secondario leggibile, nel database di sola lettura e nello snapshot del database)

• AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica (AlwaysOn: Statistiche relative al database di sola lettura, allo snapshot del database e alla replica secondaria)

• AlwaysOn: Impact on the primary workload when you run reporting workload on the secondary replica (AlwaysOn: Impatto sul carico di lavoro primario quando viene eseguito il carico di lavoro di report nella replica secondaria)

• AlwaysOn: Impact of mapping reporting workload on Readable Secondary to Snapshot Isolation (AlwaysOn: Impatto del mapping del carico di lavoro di report in Secondario leggibile all'isolamento dello snapshot)

• AlwaysOn: Minimizing blocking of REDO thread when running reporting workload on Secondary Replica (AlwaysOn: Riduzione al minimo del blocco del thread REDO durante l'esecuzione del carico di lavoro di report nella replica secondaria)

• AlwaysOn: Readable Secondary and data latency (AlwaysOn: Secondario leggibile e latenza dei dati)

Vedi anche

Panoramica di Gruppi di disponibilità AlwaysOn (SQL Server)
Repliche secondarie attive: Repliche secondarie leggibili (gruppi di disponibilità Always On)
Informazioni sull'accesso alla connessione client per le repliche di disponibilità (SQL Server)