Configurare l'accesso Read-Only su una replica di alta disponibilità (SQL Server)

Per impostazione predefinita, sia l'accesso in lettura-scrittura che quello con intento di lettura sono consentiti alla replica primaria, mentre non sono permesse 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à 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 repliche 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

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, in aggiunta, l'autorizzazione server CREATE AVAILABILITY GROUP, oppure l'autorizzazione ALTER ANY AVAILABILITY GROUP, o l'autorizzazione CONTROL SERVER.
Per modificare una replica di disponibilità	Sono necessarie l'autorizzazione ALTER AVAILABILITY GROUP nel gruppo di disponibilità, l'autorizzazione CONTROL AVAILABILITY GROUP, l'autorizzazione ALTER ANY AVAILABILITY GROUP o l'autorizzazione CONTROL SERVER.

Uso di SQL Server Management Studio

Per configurare l'accesso su una replica di disponibilità

1. Nell'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 vuole 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 indicato di seguito:

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

     No
     Non sono consentite connessioni utente ai database secondari di questa replica. Non sono disponibili per l'accesso in lettura. Questa è l'impostazione predefinita.

     Sola finalità di lettura
     Solo le connessioni di sola lettura sono consentite ai database secondari di questa replica. I database secondari sono tutti disponibili per l'accesso in lettura.

     Sì
     Tutte le connessioni sono consentite ai database secondari di questa replica, ma solo per l'accesso in lettura. I database secondari sono tutti disponibili per l'accesso in lettura.

   • Per il ruolo primario, selezionare un nuovo valore nell'elenco a discesa Connessioni nel ruolo primario , come indicato di seguito:

     Consenti tutte le connessioni
     Sono consentite tutte le connessioni ai database nella replica primaria. Questa è l'impostazione predefinita.

     Consenti connessioni di lettura/scrittura
     Quando la proprietà Finalità applicazione è impostata su ReadWrite o la proprietà di connessione Finalità applicazione non è impostata, la connessione è consentita. Le connessioni in cui la proprietà di connessione Finalità applicazione è impostata su ReadOnly non sono consentite. Ciò può aiutare a impedire ai clienti di connettere un carico di lavoro con finalità di lettura alla replica primaria per errore. 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 a una replica di disponibilità

Annotazioni

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 Transact-SQL CREATE AVAILABILITY GROUP . Se si aggiunge o si modifica una replica di un gruppo di disponibilità esistente, usare l'istruzione ALTER AVAILABILITY GROUPTransact-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 indicato di seguito:

     SECONDARY_ROLE ( PERMETTI_CONNESSIONI = { NO | SOLO_LETTURA | TUTTO } )

     dove,

     NO
     Nessuna connessione diretta è permessa ai database secondari di questa replica. Non sono disponibili per l'accesso in lettura. Questa è l'impostazione predefinita.

     SOLO_LETTURA
     Solo le connessioni di sola lettura sono consentite ai database secondari di questa replica. I database secondari sono tutti disponibili per l'accesso in lettura.

     Tutti
     Tutte le connessioni sono consentite ai database secondari di questa replica, ma solo per l'accesso in lettura. 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 indicato di seguito:

   RUOLO_PRIMARIO ( CONSENTI_CONNESSIONI = { LETTURA_SCRITTURA | TUTTI } )

   dove,

   LETTURA_SCRITTURA
   Non sono consentite le connessioni in cui la proprietà di connessione Finalità dell'applicazione è impostata su ReadOnly . Quando la proprietà Finalità applicazione è impostata su ReadWrite o la proprietà di connessione Finalità applicazione 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.

   Tutti
   Sono consentite tutte le connessioni ai database nella replica primaria. Questa è l'impostazione predefinita.

Esempio (Transact-SQL)

Nell'esempio seguente viene aggiunta una replica secondaria a un gruppo di disponibilità denominato AG2. Un'istanza autonoma del server, COMPUTER03\HADR_INSTANCE, viene specificata per ospitare la nuova replica di disponibilità. Questa replica configurata per consentire solo connessioni in lettura/scrittura per il ruolo primario e consentire solo 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à

Annotazioni

Per un esempio di codice, vedere gli esempi di PowerShell più avanti in questa sezione.

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

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

   • Per configurare l'accesso alla connessione per il ruolo secondario, specificare il ConnectionModeInSecondaryRole parametro secondary_role_keyword, dove secondary_role_keyword è uguale a uno dei valori seguenti:

     AllowNoConnections
     Non sono consentite connessioni dirette ai database nella replica secondaria e i database non sono disponibili per l'accesso in lettura. Questa è l'impostazione predefinita.

     AllowReadIntentConnectionsOnly
     Le connessioni sono consentite solo ai database nella replica secondaria in cui la proprietà Finalità applicazione è impostata su ReadOnly. 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
     Le connessioni in cui la proprietà di connessione Finalità applicazione è impostata su ReadOnly non sono consentite. Quando la proprietà Finalità applicazione è impostata su ReadWrite o la proprietà di connessione Finalità applicazione 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. Questa è l'impostazione predefinita.

   Annotazioni

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

Per configurare e usare il provider PowerShell di SQL Server, vedere Provider PowerShell di SQL Server.

Nell'esempio seguente, entrambi i parametri ConnectionModeInSecondaryRole e ConnectionModeInPrimaryRole vengono impostati 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

Proseguo: dopo la configurazione dell'accesso Read-Only 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 alle repliche secondarie leggibili:

  	Prerequisito	Collegamento
  	Assicurarsi che il gruppo di disponibilità disponga di 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 possono influire sui trigger e sulle attività dopo un failover

Se sono presenti trigger e processi che avranno esito negativo durante l'esecuzione in un database secondario non leggibile o in un database secondario leggibile, è necessario creare uno script per i trigger e i processi per verificare in una determinata replica se il database è un database primario o è un database secondario leggibile. Per ottenere queste informazioni, utilizzare la funzione DATABASEPROPERTYEX per restituire la proprietà Updatability del database. Per identificare un database di sola lettura, specificare READ_ONLY come valore, come indicato di seguito:

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

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

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 del secondario leggibile

• Always On: perché sono disponibili due opzioni per abilitare una replica secondaria per il carico di lavoro di lettura?

• Always On: Configurazione della replica secondaria leggibile

• Always On: Ho appena abilitato la funzionalità Secondaria leggibile ma la mia query è bloccata?

• Always On: rendere disponibili le statistiche più recenti su database secondario leggibile, Read-Only database e snapshot del database

• Always On: Sfide con le statistiche sul database ReadOnly, sullo snapshot del database e sulla replica secondaria

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

• Always On: impatto del carico di lavoro di creazione di report sul secondario leggibile per l'isolamento istantaneo

• Always On: riduzione al minimo del blocco del thread REDO durante l'esecuzione del carico di lavoro di report nella replica secondaria

• Always On: secondario leggibile e latenza dei dati

Vedere anche

Panoramica dei gruppi di disponibilità AlwaysOn (SQL Server)
Secondarie Attive: Repliche Secondarie Accessibili per Lettura (Gruppi di Disponibilità AlwaysOn)
Informazioni sull'accesso alla connessione client per le repliche di disponibilità (SQL Server)