Configurare l'accesso in sola lettura in una replica di disponibilità (SQL Server)Configure Read-Only Access on an Availability Replica (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.By default both read-write and read-intent access are allowed to the primary replica and no connections are allowed to secondary replicas of an Always On availability group. Questo argomento descrive come configurare l'accesso alla connessione in una replica di disponibilità di un gruppo di disponibilità AlwaysOn in SQL Server 2017SQL Server 2017 usando SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQLo PowerShell.This topic describes how to configure connection access on an availability replica of an Always On availability group in SQL Server 2017SQL Server 2017 by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or 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).For information about the implications of enabling read-only access for a secondary replica and for an introduction to connection access, see About Client Connection Access to Availability Replicas (SQL Server) and Active Secondaries: Readable Secondary Replicas (Always On Availability Groups).

Prima di iniziare Before You Begin

Prerequisiti e restrizioni Prerequisites and Restrictions

  • Per configurare un accesso alla connessione diverso, è necessario essere connessi all'istanza del server che ospita la replica primaria.To configure different connection access, you must be connected to the server instance that hosts the primary replica.

Sicurezza Security

Autorizzazioni Permissions

AttivitàTask AutorizzazioniPermissions
Per configurare le repliche durante la creazione di un gruppo di disponibilitàTo configure replicas when creating an availability group 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.Requires membership in the sysadmin fixed server role and either CREATE AVAILABILITY GROUP server permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.
Per modificare una replica di disponibilitàTo modify an availability replica È necessaria 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.Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

Utilizzo di SQL Server Management Studio Using SQL Server Management Studio

Per configurare l'accesso su una replica di disponibilitàTo configure access on an availability replica

  1. In Esplora oggetti connettersi all'istanza del server che ospita la replica primaria ed espandere l'albero del server.In Object Explorer, connect to the server instance that hosts the primary replica, and expand the server tree.

  2. Espandere il nodo Disponibilità elevata AlwaysOn e il nodo Gruppi di disponibilità .Expand the Always On High Availability node and the Availability Groups node.

  3. Fare clic sul gruppo di disponibilità di cui si desidera modificare la replica.Click the availability group whose replica you want to change.

  4. Fare clic con il pulsante destro del mouse sulla replica di disponibilità e scegliere Proprietà.Right-click the availability replica, and click Properties.

  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:In the Availability Replica Properties dialog box, you can change the connection access for the primary role and for the secondary role, as follows:

    • Per il ruolo secondario, selezionare un nuovo valore dall'elenco a discesa Secondario leggibile , come segue:For the secondary role, select a new value from the Readable secondary drop list, as follows:

      NoNo
      Non sono consentite connessioni utente ai database secondari di questa replica.No user connections are allowed to secondary databases of this replica. I database non sono disponibili per l'accesso in lettura.They are not available for read access. Si tratta dell'impostazione predefinita.This is the default setting.

      Solo finalità di letturaRead-intent only
      Sono consentite solo connessioni in sola lettura ai database secondari di questa replica.Only read-only connections are allowed to secondary databases of this replica. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.The secondary database(s) are all available for read access.

      Yes
      Sono consentite tutte le connessioni ai database secondari di questa replica, ma solo per l'accesso in lettura.All connections are allowed to secondary databases of this replica, but only for read access. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.The secondary database(s) are all available for read access.

    • Per il ruolo primario, selezionare un nuovo valore dall'elenco a discesa Connessioni nel ruolo primario , come segue:For the primary role, select a new value from the Connections in primary role drop list, as follows:

      Consenti tutte le connessioniAllow all connections
      Sono consentite tutte le connessioni ai database nella replica primaria.All connections are allowed to the databases in the primary replica. Si tratta dell'impostazione predefinita.This is the default setting.

      Consenti connessioni in lettura/scritturaAllow read/write connections
      Se la proprietà Finalità dell'applicazione è impostata su Lettura/Scrittura o se tale proprietà non è impostata, la connessione è consentita.When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. Non sono consentite le connessioni in cui la proprietà di connessione Finalità dell'applicazione è impostata su Sola lettura .Connections where the Application Intent connection property is set to ReadOnly are not allowed. 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.This can help prevent customers from connecting a read-intent work load to the primary replica by mistake. Per altre informazioni sulla proprietà di connessione Finalità dell'applicazione, vedere Using Connection String Keywords with SQL Server Native Client.For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

Utilizzo di Transact-SQL Using Transact-SQL

Per configurare l'accesso su una replica di disponibilitàTo configure access on an availability replica

Nota

Per un esempio di questa procedura, vedere Esempio (Transact-SQL)più avanti in questa sezione.For an example of this procedure, see Example (Transact-SQL), later in this section.

  1. Connettersi all'istanza del server che ospita la replica primaria.Connect to the server instance that hosts the primary replica.

  2. Se si specifica una replica per un nuovo gruppo di disponibilità, usare l'istruzione CREATE AVAILABILITY GROUP Transact-SQLTransact-SQL .If you are specifying a replica for a new availability group, use the CREATE AVAILABILITY GROUP Transact-SQLTransact-SQL statement. Se si aggiunge o si modifica una replica di un gruppo di disponibilità esistente, usare l'istruzione ALTER AVAILABILITY GROUP Transact-SQLTransact-SQL .If you are adding or modifying a replica of an existing availability group, use the ALTER AVAILABILITY GROUP Transact-SQLTransact-SQL statement.

    • 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:To configure connection access for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

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

      dovewhere,

      NoNO
      Non sono consentite connessioni dirette ai database secondari di questa replica.No direct connections are allowed to secondary databases of this replica. I database non sono disponibili per l'accesso in lettura.They are not available for read access. Si tratta dell'impostazione predefinita.This is the default setting.

      READ_ONLYREAD_ONLY
      Sono consentite solo connessioni in sola lettura ai database secondari di questa replica.Only read-only connections are allowed to secondary databases of this replica. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.The secondary database(s) are all available for read access.

      ALLALL
      Sono consentite tutte le connessioni ai database secondari di questa replica, ma solo per l'accesso in lettura.All connections are allowed to secondary databases of this replica, but only for read access. Il database o i database secondari sono tutti disponibili per l'accesso in lettura.The secondary database(s) are all available for read access.

  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:To configure connection access for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

    PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )PRIMARY_ROLE ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

    dovewhere,

    READ_WRITEREAD_WRITE
    Non sono consentite le connessioni in cui la proprietà di connessione Finalità dell'applicazione è impostata su Sola lettura .Connections where the Application Intent connection property is set to ReadOnly are disallowed. Se la proprietà Finalità dell'applicazione è impostata su Lettura/Scrittura o se tale proprietà non è impostata, la connessione è consentita.When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. Per altre informazioni sulla proprietà di connessione Finalità dell'applicazione, vedere Using Connection String Keywords with SQL Server Native Client.For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

    ALLALL
    Sono consentite tutte le connessioni ai database nella replica primaria.All connections are allowed to the databases in the primary replica. Si tratta dell'impostazione predefinita.This is the default setting.

Esempio (Transact-SQL) Example (Transact-SQL)

L'esempio seguente aggiunge una replica secondaria a un gruppo di disponibilità denominato AG2.The following example adds a secondary replica to an availability group named AG2. Un'istanza del server autonoma, COMPUTER03\HADR_INSTANCE, viene specificata per ospitare la nuova replica di disponibilità.A stand-alone server instance, COMPUTER03\HADR_INSTANCE, is specified to host the new availability replica. 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.This replica configured to allow only read-write connections for the primary role and to allow only read-intent connections for secondary role.

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 Using PowerShell

Per configurare l'accesso su una replica di disponibilitàTo configure access on an availability replica

Nota

Per un esempio di codice, vedere Esempio (PowerShell)più avanti in questa sezione.For a code example, see Example (PowerShell), later in this section.

  1. Cambiare la directory (cd) impostandola sull'istanza del server che ospita la replica primaria.Change directory (cd) to the server instance that hosts the primary replica.

  2. Quando si aggiunge una replica di disponibilità a un gruppo di disponibilità, usare il cmdlet New-SqlAvailabilityReplica .When adding an availability replica to an availability group, use the New-SqlAvailabilityReplica cmdlet. Quando si modifica una replica di disponibilità esistente, usare il cmdlet Set-SqlAvailabilityReplica .When modifying an existing availability replica, use the Set-SqlAvailabilityReplica cmdlet. I parametri pertinenti sono i seguenti:The relevant parameters are as follows:

    • Per configurare l'accesso alla connessione per il ruolo secondario, specificare ConnectionModeInSecondaryRolesecondary_role_keyword , dove secondary_role_keyword corrisponde a uno dei valori seguenti:To configure connection access for the secondary role, specify the ConnectionModeInSecondaryRolesecondary_role_keyword parameter, where secondary_role_keyword equals one of the following values:

      AllowNoConnectionsAllowNoConnections
      Non è consentita alcuna connessione diretta ai database nella replica secondaria e i database non sono disponibili per l'accesso in lettura.No direct connections are allowed to the databases in the secondary replica and the databases are not available for read access. Si tratta dell'impostazione predefinita.This is the default setting.

      AllowReadIntentConnectionsOnlyAllowReadIntentConnectionsOnly
      Sono consentite solo connessioni ai database nella replica secondaria in cui la proprietà Finalità dell'applicazione è impostata su Sola lettura.Connections are allowed only to the databases in the secondary replica where the Application Intent property is set to ReadOnly. Per altre informazioni su questa proprietà, vedere Using Connection String Keywords with SQL Server Native Client.For more information about this property, see Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnectionsAllowAllConnections
      Sono consentite tutte le connessioni ai database nella replica secondaria per l'accesso in sola lettura.All connections are allowed to the databases in the secondary replica for read-only access.

    • Per configurare l'accesso alla connessione per il ruolo primario, specificare ConnectionModeInPrimaryRoleprimary_role_keyword, dove primary_role_keyword corrisponde a uno dei valori seguenti:To configure connection access for the primary role, specify ConnectionModeInPrimaryRoleprimary_role_keyword, where primary_role_keyword equals one of the following values:

      AllowReadWriteConnectionsAllowReadWriteConnections
      Non sono consentite le connessioni in cui la proprietà di connessione Finalità dell'applicazione è impostata su ReadOnly.Connections where the Application Intent connection property is set to ReadOnly are disallowed. Se la proprietà Finalità dell'applicazione è impostata su ReadWrite o se tale proprietà non è impostata, la connessione è consentita.When the Application Intent property is set to ReadWrite or the Application Intent connection property is not set, the connection is allowed. Per altre informazioni sulla proprietà di connessione Finalità dell'applicazione, vedere Using Connection String Keywords with SQL Server Native Client.For more information about Application Intent connection property, see Using Connection String Keywords with SQL Server Native Client.

      AllowAllConnectionsAllowAllConnections
      Sono consentite tutte le connessioni ai database nella replica primaria.All connections are allowed to the databases in the primary replica. Si tratta dell'impostazione predefinita.This is the default setting.

    Nota

    Per visualizzare la sintassi di un cmdlet, usare il cmdlet Get-Help nell'ambiente SQL Server 2017SQL Server 2017 PowerShell.To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server 2017SQL Server 2017 PowerShell environment. Per altre informazioni, vedere Get Help SQL Server PowerShell.For more information, see Get Help SQL Server PowerShell.

    Per impostare e utilizzare il provider PowerShell per SQL ServerTo set up and use the SQL Server PowerShell provider

Esempio (PowerShell) Example (PowerShell)

Nell'esempio seguente vengono impostati i parametri ConnectionModeInSecondaryRole e ConnectionModeInPrimaryRole su AllowAllConnections.The following example, sets the both the ConnectionModeInSecondaryRole and ConnectionModeInPrimaryRole parameters to 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à Follow Up: After Configuring Read-Only Access for an Availability Replica

Accesso in sola lettura a una replica secondaria leggibile.Read-only access to a readable secondary replica

  • Quando si usa l'utilità bcp o sqlcmd, è possibile specificare l'accesso in sola lettura a qualsiasi replica secondaria abilitata per l'accesso in sola lettura specificando l'opzione -K ReadOnly .When using the bcp Utility or sqlcmd Utility, you can specify read-only access to any secondary replica that is enabled for read-only access by specifying the -K ReadOnly switch.

  • Per consentire alle applicazioni client di connettersi a repliche secondarie leggibili:To enable client applications to connect to readable secondary replicas:

    PrerequisitiPrerequisite CollegamentoLink
    Casella di controlloCheckbox Assicurarsi che nel gruppo di disponibilità sia presente un listener.Ensure that the availability group has a listener. Creare o configurare un listener del gruppo di disponibilità (SQL Server)Create or Configure an Availability Group Listener (SQL Server)
    Casella di controlloCheckbox Configurare il routing di sola lettura per il gruppo di disponibilità.Configure read-only routing for the availability group. Configurare il routing di sola lettura per un gruppo di disponibilità (SQL Server)Configure Read-Only Routing for an Availability Group (SQL Server)

    Fattori che potrebbero influire su trigger e processi dopo un failoverFactors that might affect triggers and jobs after a 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.If you have triggers and jobs that will fail when running on a non-readable secondary database or on a readable secondary database, you need to script the triggers and jobs to check on a given replica to determine whether the database is a primary database or is a readable secondary database. Per ottenere queste informazioni, usare la funzione DATABASEPROPERTYEX per restituire la proprietà Updatability del database.To obtain this information, use the DATABASEPROPERTYEX function to return the Updatability property of the database. Per identificare un database di sola lettura, specificare il valore READ_ONLY come indicato di seguito:To identify a read-only database, specify READ_ONLY as the value, as follows:

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

Per identificare un database di lettura/scrittura, specificare il valore READ_WRITE.To identify a read-write database, specify READ_WRITE as the value.

Vedere ancheSee Also

Panoramica di Gruppi di disponibilità AlwaysOn (SQL Server) Overview of Always On Availability Groups (SQL Server)
Repliche secondarie attive: Repliche secondarie leggibili (Gruppi di disponibilità AlwaysOn) Active Secondaries: Readable Secondary Replicas (Always On Availability Groups)
Informazioni sull'accesso alla connessione client per le repliche di disponibilità (SQL Server)About Client Connection Access to Availability Replicas (SQL Server)