Configurare l'accesso in sola lettura in una replica di disponibilità (SQL Server)

Per impostazione predefinita, gli accessi in lettura e scrittura e l'accesso con finalità di lettura sono entrambi consentiti alla replica primaria, ma alle repliche secondarie non sono consentite connessioni 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à AlwaysOn in SQL Server 2014 usando SQL Server Management Studio, Transact-SQL o PowerShell.

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

Prima di iniziare

Prerequisiti e restrizioni

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

Sicurezza

Autorizzazioni

Uso 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.

Uso di Transact-SQL

Per configurare l'accesso su una replica di disponibilità

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à

1. Spostarsi nella directory (cd) dell'istanza del server che ospita la replica primaria.

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

   • Per configurare l'accesso alla connessione per il ruolo secondario, specificare il ConnectionModeInSecondaryRole parametro secondary_role_keyword, in cui secondary_role_keyword è uguale 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 uguale 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 Get-Help cmdlet nell'ambiente PowerShell SQL Server 2014. Per altre informazioni, vedere Get Help SQL Server PowerShell.

Per configurare e usare il provider di PowerShell SQL Server, vedere SQL Server provider 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 l'utilità bcp o l'utilità sqlcmd, è 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	Collegamento
  	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à Updatability del database. Per identificare un database di sola lettura, specificare il valore READ_ONLY come indicato di seguito:

DATABASEPROPERTYEX([db name],'Updatability') = 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

• Always On: proposta di valore di Secondario leggibile

• Always On: motivi per cui sono presenti due opzioni per abilitare una replica secondaria per un carico di lavoro di lettura

• Always On: configurazione di una replica secondaria leggibile

• Always On: motivo per cui anche se Secondario leggibile è appena stato abilitato la query rimane bloccata

• Always On: come rendere disponibili le statistiche più recenti in Secondario leggibile, nel database di sola lettura e nello snapshot del database

• Always On: problematiche riguardanti le statistiche sul database di sola lettura, sullo snapshot del database e sulla replica secondaria

• Always On: impatto sul carico di lavoro primario quando viene eseguito il carico di lavoro di report nella replica secondaria

• Always On: impatto del mapping del carico di lavoro di report in Secondario leggibile all'isolamento dello snapshot

• Always On: riduzione al minimo del blocco del thread REDO quando si esegue il carico di lavoro di report nella replica secondaria

• Always On: Secondario leggibile e latenza dei dati

Vedere anche

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