Configurare il routing di sola lettura per un gruppo di disponibilità (SQL Server)Configure Read-Only Routing for an Availability Group (SQL Server)

Per configurare un gruppo di disponibilità Always On in modo da supportare il routing di sola lettura in SQL ServerSQL Server, è possibile usare Transact-SQLTransact-SQL o PowerShell.To configure an Always On availability group to support read-only routing in SQL ServerSQL Server, you can use either Transact-SQLTransact-SQL or PowerShell. Conrouting di sola lettura si intende la capacità di SQL ServerSQL Server di instradare le richieste di connessione in sola lettura valide a una replica secondaria leggibile Always On, ovvero una replica configurata per consentire carichi di lavoro di sola lettura quando viene eseguita nel ruolo secondario.Read-only routing refers to the ability of SQL ServerSQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). Per supportare il routing di sola lettura, il gruppo di disponibilità deve possedere un listener del gruppo di disponibilità.To support read-only routing, the availability group must possess an availability group listener. I client in sola lettura devono indirizzare le richieste di connessione al listener e le stringhe di connessione del client devono specificare la finalità dell'applicazione come in sola lettura,Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." ovvero devono essere richieste di connessione con finalità di lettura.That is, they must be read-intent connection requests.

Il routing di sola lettura è disponibile in SQL Server 2016SQL Server 2016 e versioni successive.Read-only routing is available in SQL Server 2016SQL Server 2016 and later.

Nota

Per informazioni su come configurare una replica secondaria leggibile, vedere Configurare l'accesso in sola lettura in una replica di disponibilità (SQL Server).For information about how to configure a readable secondary replica, see Configure Read-Only Access on an Availability Replica (SQL Server).

Prima di iniziare Before You Begin

Prerequisiti Prerequisites

Proprietà della replica da configurare per il supporto del routing di sola lettura What Replica Properties Do you Need to Configure to Support Read-Only Routing?

  • Per ogni replica secondaria leggibile che deve supportare il routing di sola lettura, è necessario specificare un URL di routing di sola lettura.For each readable secondary replica that is to support read-only routing, you need to specify a read-only routing URL. L'URL viene usato solo quando la replica locale viene eseguita nel ruolo secondario.This URL takes effect only when the local replica is running under the secondary role. L'URL di routing di sola lettura deve essere specificato per ogni singola replica in base alle esigenze.The read-only routing URL must be specified on a replica-by-replica basis, as needed. Ogni URL di routing di sola lettura viene usato per il routing delle richieste di connessione con finalità di lettura a una replica secondaria leggibile specifica.Each read-only routing URL is used for routing read-intent connection requests to a specific readable secondary replica. In genere, a ogni replica secondaria leggibile viene assegnato un URL di routing di sola lettura.Typically, every readable secondary replica is assigned a read-only routing URL.

    Per informazioni sul calcolo dell'URL di routing di sola lettura per una replica di disponibilità, vedere Calcolo di read_only_routing_url per Always OnFor information about calculating the read-only routing URL for an availability replica, see Calculating read_only_routing_url for Always On

  • Per ogni replica di disponibilità che deve supportare il routing di sola lettura quando viene eseguita come replica primaria, è necessario specificare un elenco di routing di sola lettura.For each availability replica that you want to support read-only routing when it is the primary replica, you need to specify a read-only routing list. L'elenco di routing di sola lettura viene usato solo quando la replica locale viene eseguita nel ruolo primario.A given read-only routing list takes effect only when the local replica is running under the primary role. L'elenco deve essere specificato per ogni singola replica in base alle esigenze.This list must be specified on a replica-by-replica basis, as needed. In genere, ciascun elenco di routing di sola lettura deve contenere tutti gli URL di routing di sola lettura, con l'URL della replica locale alla fine dell'elenco.Typically, each read-only routing list would contain every read-only routing URL, with the URL of the local replica at the end of the list.

    Nota

    Le richieste di connessione con finalità di lettura vengono instradate alla prima voce leggibile disponibile nell'elenco di routing di sola lettura della replica primaria corrente.Read-intent connection requests are routed to the first available entry on the read-only routing list of the current primary replica. Tuttavia è supportato il bilanciamento del carico tra le repliche di sola lettura.However, load-balancing across read-only replicas is supported. Per altre informazioni, vedere Configurare il bilanciamento del carico tra le repliche di sola lettura.For more information, see Configure load-balancing across read-only replicas.

Nota

Per informazioni sui listener del gruppo di disponibilità e altre informazioni sul routing di sola lettura, vedere Listener del gruppo di disponibilità, connettività client e failover dell'applicazione (SQL Server).For information about availability group listeners and more information about read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

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.

Con Transact-SQL Using Transact-SQL

Configurare un elenco di routing di sola letturaConfigure a read-only routing list

Usare la procedura seguente per configurare il routing di sola lettura con Transact-SQL.Use the following steps to configure read-only routing using Transact-SQL. Per un esempio di codice, vedere Esempio (Transact-SQL), più avanti in questa sezione.For a code example, 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 modifica una replica per un gruppo di disponibilità esistente, usare l'istruzione ALTER AVAILABILITY GROUP Transact-SQLTransact-SQL .If you are adding or modifying a replica for an existing availability group, use the ALTER AVAILABILITY GROUP Transact-SQLTransact-SQL statement.

    • Per configurare il routing di sola lettura per il ruolo secondario, nella clausola ADD REPLICA o MODIFY REPLICA WITH specificare l'opzione SECONDARY_ROLE, come segue:To configure read-only routing for the secondary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the SECONDARY_ROLE option, as follows:

      SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')SECONDARY_ROLE ( READ_ONLY_ROUTING_URL ='TCP://system-address:port')

      I parametri dell'URL del routing di sola lettura sono i seguenti:The parameters of the read-only routing URL are as follows:

      system-addresssystem-address
      Stringa, ad esempio un nome di sistema, un nome di dominio completo o un indirizzo IP, che identifica in modo univoco il computer di destinazione.Is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.

      portport
      Numero di porta utilizzato dal motore di database dell'istanza di SQL ServerSQL Server .Is a port number that is used by the Database Engine of the SQL ServerSQL Server instance.

      Esempio: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')For example: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

      In una clausola MODIFY REPLICA l'argomento ALLOW_CONNECTIONS è facoltativo se la replica è già configurata per consentire connessioni in sola lettura.In a MODIFY REPLICA clause the ALLOW_CONNECTIONS is optional if the replica is already configured to allow read-only connections.

      Per altre informazioni, vedere Calcolo di Read_only_routing_url per Always On.For more information, see Calculating read_only_routing_url for Always On.

    • Per configurare il routing di sola lettura per il ruolo primario, nella clausola ADD REPLICA o MODIFY REPLICA WITH specificare l'opzione PRIMARY_ROLE, come segue:To configure read-only routing for the primary role, in the ADD REPLICA or MODIFY REPLICA WITH clause, specify the PRIMARY_ROLE option, as follows:

      PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =(‘server [ ,...n ] ))PRIMARY_ROLE ( READ_ONLY_ROUTING_LIST =(‘server [ ,...n ] ))

      dove server identifica un'istanza del server in cui viene ospitata una replica secondaria di sola lettura nel gruppo di disponibilità.where, server identifies a server instance that hosts a read-only secondary replica in the availability group.

      Esempio: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))For example: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

      Nota

      È necessario impostare l'URL del routing di sola lettura prima di configurare l'elenco di routing di sola lettura.You must set the read-only routing URL before configuring the read-only routing list.

Configurare il bilanciamento del carico tra le repliche di sola lettura Configure load-balancing across read-only replicas

A partire da SQL Server 2016SQL Server 2016, è possibile configurare il bilanciamento del carico in un set di repliche di sola lettura.Beginning with SQL Server 2016SQL Server 2016, you can configure load-balancing across a set of read-only replicas. In precedenza, il routing di sola lettura indirizzava sempre il traffico alla prima replica disponibile nell'elenco di routing.Previously, read-only routing always directed traffic to the first available replica in the routing list. Per sfruttare i vantaggi di questa funzionalità, usare un livello di parentesi nidificate per racchiudere le istanze del server READ_ONLY_ROUTING_LIST nei comandi CREATE AVAILABILITY GROUP o ALTER AVAILABILITY GROUP .To take advantage of this feature, use one level of nested parentheses around the READ_ONLY_ROUTING_LIST server instances in the CREATE AVAILABILITY GROUP or ALTER AVAILABILITY GROUP commands.

Ad esempio, l'elenco di routing seguente bilancia il carico della richiesta di connessione con finalità di lettura tra due repliche di sola lettura, Server1 e Server2.For example, the following routing list load balances read-intent connection request across two read-only replicas, Server1 and Server2. Le parentesi nidificate che racchiudono questi server identificano il set con carico bilanciato.The nested parentheses that surround these servers identify the load-balanced set. Se nessuna replica è disponibile in tale set, verrà eseguito il tentativo di connettersi in modo sequenziale alle altre repliche, Server3 e Server4, nell'elenco di routing di sola lettura.If neither replica is available in that set, it will proceed to attempt to sequentially connect to the other replicas, Server3 and Server4, in the read-only routing list.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), 'Server3', 'Server4')  

Si noti che ogni voce nell'elenco di routing può essere un set di repliche di sola lettura con carico bilanciato.Note that each entry in the routing list can itself be a set of load-balanced read-only replicas. L'esempio seguente illustra questa operazione.The following example demonstrates this.

READ_ONLY_ROUTING_LIST = (('Server1','Server2'), ('Server3', 'Server4', 'Server5'), 'Server6')  

È supportato solo un livello di parentesi nidificate.Only one level of nested parentheses is supported.

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

Nell'esempio seguente vengono modificate due repliche di disponibilità di un gruppo di disponibilità esistente, AG1 , per supportare il routing di sola lettura se a una di queste repliche è attualmente assegnato il ruolo primario.The following example modifies two availability replicas of an existing availability group, AG1 to support read-only routing if one of these replicas currently owns the primary role. Per identificare le istanze del server che ospitano la replica di disponibilità, in questo esempio vengono specificati i nomi delle istanze,COMPUTER01 e COMPUTER02.To identify the server instances that host the availability replica, this example specifies the instance names—COMPUTER01 and COMPUTER02.

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));  
ALTER AVAILABILITY GROUP [AG1]  
 MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER02.contoso.com:1433'));  

ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER01' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER02','COMPUTER01')));  

ALTER AVAILABILITY GROUP [AG1]   
MODIFY REPLICA ON  
N'COMPUTER02' WITH   
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('COMPUTER01','COMPUTER02')));  
GO  

Con PowerShell Using PowerShell

Configurare un elenco di routing di sola letturaConfigure a read-only routing list

Usare la procedura seguente per configurare il routing di sola lettura con PowerShell.Use the following steps to configure read-only routing using PowerShell. 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. Impostare il valore predefinito (cd) sull'istanza del server che ospita la replica primaria.Set default (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 il routing di sola lettura per il ruolo secondario, specificare il parametro ReadonlyRoutingConnectionUrl"url" ,To configure read-only routing for the secondary role, specify the ReadonlyRoutingConnectionUrl"url" parameter.

      dove url è il nome di dominio completo (FQDN) e la porta di connettività da usare in caso di routing alla replica per le connessioni di sola lettura.where, url is the connectivity fully-qualified domain name (FQDN) and port to use when routing to the replica for read-only connections. Esempio: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"For example: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

      Per altre informazioni, vedere Calcolo di Read_only_routing_url per Always On.For more information, see Calculating read_only_routing_url for Always On.

    • Per configurare l'accesso alla connessione per il ruolo primario, specificare ReadonlyRoutingList"server" [ ,...n ], dove server identifica un'istanza del server in cui viene ospitata una replica secondaria di sola lettura nel gruppo di disponibilità.To configure connection access for the primary role, specify ReadonlyRoutingList"server" [ ,...n ], where server identifies a server instance that hosts a read-only secondary replica in the availability group. Esempio: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"For example: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

      Nota

      È necessario impostare l'URL del routing di sola lettura di una replica prima di configurare il relativo elenco di routing di sola lettura.You must set the read-only routing URL of a replica before configuring its read-only routing list.

    Nota

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

Impostare e usare il provider PowerShell per SQL ServerSet up and use the SQL Server PowerShell provider

Esempio (PowerShell) Example (PowerShell)

Nell'esempio seguente vengono configurate la replica primaria e una replica secondaria in un gruppo di disponibilità per il routing di sola lettura.The following example configures the primary replica and one secondary replica in an availability group for read-only routing. Innanzi tutto, nell'esempio viene assegnato un URL di routing di sola lettura a ciascuna replica.First, the example assigns a read-only routing URL to each replica. L'elenco di routing di sola lettura viene quindi impostato sulla replica primaria.Then it sets the read-only routing list on the primary replica. Le connessioni la cui proprietà "ReadOnly" è impostata nella stringa di connessione verranno reindirizzate alla replica secondaria.Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica. Se la replica secondaria non è leggibile (in base all'impostazione ConnectionModeInSecondaryRole ), la connessione verrà nuovamente indirizzata alla replica primaria.If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg  
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"  
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"  

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica  
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica  

Completamento: Dopo la configurazione del routing di sola lettura Follow Up: After Configuring Read-Only Routing

Una volta configurate la replica primaria corrente e le repliche secondarie leggibili per supportare il routing di sola lettura in entrambi i ruoli, le repliche secondarie leggibili potranno ricevere richieste di connessione con finalità di lettura dai client che si connettono tramite il listener del gruppo di disponibilità.Once the current primary replica and the readable secondary replicas are configured to support read-only routing in both roles, the readable secondary replicas can receive read read-intent connection requests from clients that connect via the availability group listener.

Suggerimento

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

Requisiti e indicazioni per le stringhe di connessione del client Requirements and Recommendations for Client Connection-Strings

Per consentire a un'applicazione client di utilizzare il routing di sola lettura, è necessario che la relativa stringa di connessione soddisfi i requisiti seguenti:For a client application to use read-only routing, its connection string must satisfy the following requirements:

  • Utilizzare il protocollo TCP.Use the TCP protocol.

  • Impostare la proprietà o l'attributo della finalità dell'applicazione su readonly.Set the application intent attribute/property to readonly.

  • Fare riferimento al listener di un gruppo di disponibilità configurato per supportare il routing di sola lettura.Reference the listener of an availability group that is configured to support read-only routing.

  • Fare riferimento a un database in tale gruppo di disponibilità.Reference a database in that availability group.

    È inoltre consigliabile che le stringhe di connessione consentano il failover su più subnet, che supporta un thread client parallelo per ogni replica in ogni subnet.In addition, we recommend that connection strings enable multi-subnet failover, which supports a parallel client thread for each replica on each subnet. In questo modo di riduce al minimo il tempo di riconnessione del client dopo un failover.This minimizes client reconnection time after a failover.

    La sintassi per una stringa di connessione dipende dal provider SQL Server utilizzato da un'applicazione.The syntax for a connection string depends on the SQL Server provider an application is using. Nella stringa di connessione di esempio seguente per il Provider di dati .NET Framework 4.0.2 per SQL Server sono illustrate le parti di una stringa di connessione necessarie e consigliate per il funzionamento del routing di sola lettura.The following example connection string for the .NET Framework Data Provider 4.0.2 for SQL Server illustrates the parts of a connection string that are required and recommended to work for read-only routing.

Server=tcp:MyAgListener,1433;Database=Db1;IntegratedSecurity=SSPI;ApplicationIntent=ReadOnly;MultiSubnetFailover=True  

Per altre informazioni sulla finalità dell'applicazione di sola lettura e sul routing di sola lettura, vedere Listener del gruppo di disponibilità, connettività client e failover dell'applicazione (SQL Server).For more information about read-only application intent and read-only routing, see Availability Group Listeners, Client Connectivity, and Application Failover (SQL Server).

Se il routing di sola lettura non funziona correttamenteIf Read-Only Routing is Not Working Correctly

Per informazioni sulla risoluzione dei problemi di una configurazione di routing di sola lettura, vedere Il routing di sola lettura non funziona correttamente.For information about troubleshooting a read-only routing configuration, see Read-Only Routing is Not Working Correctly.

Passaggi successivi Next Steps

Per visualizzare le configurazioni del routing di sola letturaTo view read-only routing configurations

Per configurare l'accesso alla connessione clientTo configure client connection access

Per utilizzare stringhe di connessione nelle applicazioniTo use connection strings in applications

Blog:Blogs:

White paper:White papers:

Contenuto aggiuntivoAdditional content