Configure el enrutamiento de solo lectura para un grupo de disponibilidad de Always On

Se aplica a:SQL Server

Para configurar un grupo de disponibilidad AlwaysOn para admitir el enrutamiento de solo lectura en SQL Server, puede usar Transact-SQL o PowerShell. Elenrutamiento de solo lectura hace referencia a la capacidad de SQL Server de enrutar las solicitudes de conexión de solo lectura a una réplica secundaria legible de AlwaysOn disponible (es decir, una réplica configurada para permitir cargas de trabajo de solo lectura al ejecutarse en un rol secundario). Para admitir el enrutamiento de solo lectura, el grupo de disponibilidad debe tener un agente de escucha del grupo de disponibilidad. Los clientes de solo lectura deben dirigir sus solicitudes de conexión a este cliente de escucha, y las cadenas de conexión de los clientes deben especificar la intención de la aplicación como de "solo lectura". Es decir, deben ser solicitudes de conexión con intención de lectura.

El enrutamiento de solo lectura está disponible en SQL Server 2016 (13.x) y versiones posteriores.

Requisitos previos

• El grupo de disponibilidad debe tener un cliente de escucha del grupo de disponibilidad. Para más información, consulte Crear o configurar un agente de escucha de un grupo de disponibilidad (SQL Server).

• Se debe configurar una o varias réplicas de disponibilidad para aceptar conexiones de solo lectura en el rol secundario (es decir, para que sean réplicas secundarias legibles). Para obtener más información, vea Configuración del acceso de solo lectura en una réplica de disponibilidad (SQL Server).

• Debe estar conectado a la instancia del servidor que hospeda la réplica principal actual.

• Si usa un inicio de sesión de SQL, asegúrese de que la cuenta esté configurada correctamente. Para más información, consulte Administración de inicios de sesión y de trabajos para las bases de datos de un grupo de disponibilidad (SQL Server).

¿Qué propiedades de réplica necesita configurar para admitir el enrutamiento de solo lectura?

• Para cada réplica secundaria legible que vaya a admitir el enrutamiento de solo lectura, debe especificar una dirección URL de enrutamiento de solo lectura. Esta dirección URL solo surte efecto cuando la réplica local se ejecuta con el rol secundario. La dirección URL de enrutamiento de solo lectura debe especificarse para cada réplica, según sea necesario. Cada dirección URL de solo lectura se usa para enrutar las solicitudes de conexión de intento de lectura a una réplica secundaria legible específica. Normalmente, cada réplica secundaria legible tiene asignada una URL de enrutamiento de solo lectura.

  Para obtener información sobre cómo calcular la dirección URL de enrutamiento de solo lectura para una réplica de disponibilidad, vea Calcular Read_only_routing_url para AlwaysOn

• Para cada réplica de disponibilidad que quiera que admita el enrutamiento de solo lectura cuando sea la réplica principal, debe especificar una lista de enrutamiento de solo lectura. Una lista de enrutamiento de solo lectura dada solo tiene efecto cuando la réplica local se ejecuta en el rol principal. Esta lista debe especificarse para cada réplica, según sea necesario. Normalmente, cada lista de enrutamiento de solo lectura contendría cada dirección URL de enrutamiento de solo lectura con la dirección URL de la réplica local al final de la lista.

  Nota

  Las solicitudes de conexión con intención de lectura se dirigen a la primera entrada disponible de la lista de enrutamiento de solo lectura de la réplica principal actual. Sin embargo, se admite el equilibrio de carga entre réplicas de solo lectura. Para obtener más información, vea Configuración del equilibrio de carga entre réplicas de solo lectura.

Permisos

Uso de Transact-SQL

Configuración de una lista de enrutamiento de solo lectura

Realice los pasos siguientes para configurar el enrutamiento de solo lectura mediante Transact-SQL. Para obtener un ejemplo de código, vea Ejemplo (Transact-SQL), más adelante en esta sección.

1. Conéctese a la instancia del servidor que hospeda la réplica principal.

2. Si va a especificar una réplica para un nuevo grupo de disponibilidad, use la CREATE AVAILABILITY GROUP instrucción Transact-SQL. Si va a agregar o modificar una réplica para un grupo de disponibilidad existente, use la ALTER AVAILABILITY GROUP instrucción Transact-SQL.

   • Para configurar el enrutamiento de solo lectura para el rol secundario, en la cláusula ADD REPLICA o MODIFY REPLICA WITH, especifique la opción SECONDARY_ROLE, de la siguiente manera:

     ROL_SECUNDARIO ( URL_DE_ENRUTAMIENTO_SOLO_LECTURA ='TCP://dirección-del-sistema:puerto')

     Los parámetros de la dirección URL de enrutamiento de solo lectura son los siguientes:

     dirección del sistema
     Es una cadena, como un nombre de sistema, un nombre de dominio completo o una dirección IP, que identifica sin ambigüedad el equipo de destino.

     puerto
     Es un número de puerto que usa el motor de base de datos de la instancia de SQL Server .

     Por ejemplo: SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://COMPUTER01.contoso.com:1433')

     En una cláusula MODIFY REPLICA el ALLOW_CONNECTIONS es opcional si la réplica ya está configurada para permitir conexiones de solo lectura.

     Para obtener más información, consulte Cálculo de read_only_routing_url para Always On.

   • Para configurar el enrutamiento de solo lectura para el rol principal, en la cláusula ADD REPLICA o MODIFY REPLICA WITH, especifique la opción PRIMARY_ROLE, del siguiente modo:

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

     donde server identifica una instancia del servidor que hospeda una réplica secundaria de solo lectura en el grupo de disponibilidad.

     Por ejemplo: PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('Server1','Server2'))

     Nota

     Debe establecer la dirección URL de enrutamiento de solo lectura antes de configurar la lista de enrutamiento de solo lectura.

Configurar el equilibrio de carga entre réplicas de solo lectura

A partir de SQL Server 2016 (13.x), puede configurar el equilibrio de carga entre un conjunto de réplicas de solo lectura. Anteriormente, el enrutamiento de solo lectura siempre dirigía el tráfico a la primera réplica disponible de la lista de rutas. Para aprovechar esta característica, use un nivel de anidación de paréntesis alrededor de las instancias de servidor de READ_ONLY_ROUTING_LIST en los comandos CREATE AVAILABILITY GROUP o ALTER AVAILABILITY GROUP.

Por ejemplo, la siguiente carga de la lista de enrutamiento equilibra la solicitud de conexión con intención de lectura entre dos réplicas de solo lectura, Server1 y Server2. Los paréntesis anidados que rodean estos servidores identifican el conjunto con equilibrio de carga. Si ninguna réplica está disponible en dicho conjunto, tratará de conectarse de forma secuencial a las demás réplicas, Server3 y Server4, en la lista de enrutamiento de solo lectura.

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

Tenga en cuenta que cada entrada de la lista de enrutamiento puede ser un conjunto de réplicas de solo lectura con equilibrio de carga. En el siguiente ejemplo se muestra cómo hacerlo.

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

Se admite solo un nivel de paréntesis anidados.

Ejemplo (Transact-SQL)

En el ejemplo siguiente se modifican dos réplicas de disponibilidad de un grupo de disponibilidad existente, AG1, para admitir el enrutamiento de solo lectura si una de estas réplicas posee actualmente el rol principal. Para identificar las instancias de servidor que hospedan la réplica de disponibilidad, este ejemplo especifica los nombres de instancia COMPUTER01 y 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  
  

Uso de PowerShell

Configuración de una lista de enrutamiento de solo lectura

Realice los pasos siguientes para configurar el enrutamiento de solo lectura mediante PowerShell. Para obtener un ejemplo de código, vea Ejemplo (PowerShell), más adelante en esta sección.

1. Establezca el valor predeterminado (cd) en la instancia del servidor que hospeda la réplica principal.

2. Para agregar una réplica de disponibilidad a un grupo de disponibilidad, use el cmdlet New-SqlAvailabilityReplica . Para modificar una réplica de disponibilidad existente, use el cmdlet Set-SqlAvailabilityReplica . Los parámetros pertinentes son los siguientes:

   • Para configurar el enrutamiento de solo lectura para el rol secundario, especifique el parámetro ReadonlyRoutingConnectionUrl"url" .

     donde, url es el nombre de dominio completo de conectividad (FQDN) y el puerto que se va a usar al enrutar a la réplica para las conexiones de solo lectura. Por ejemplo: -ReadonlyRoutingConnectionUrl "TCP://DBSERVER8.manufacturing.Adventure-Works.com:7024"

     Para obtener más información, consulte Cálculo de read_only_routing_url para Always On.

   • Para configurar el acceso de conexión para el rol principal, especifique ReadonlyRoutingList"server" [ , ...n ], donde server identifica una instancia del servidor que hospeda una réplica secundaria de solo lectura en el grupo de disponibilidad. Por ejemplo: -ReadOnlyRoutingList "SecondaryServer","PrimaryServer"

     Nota

     Debe establecer la dirección URL de enrutamiento de solo lectura de la réplica antes de configurar su lista de enrutamiento de solo lectura.

   Nota

   Para ver la sintaxis de un cmdlet, use el cmdlet Get-Help en el entorno de SQL Server PowerShell. Para más información, consulte Get Help SQL Server PowerShell.

Configuración y uso del proveedor de SQL Server PowerShell

• Proveedor de PowerShell de SQL Server

• Obtener ayuda de SQL Server PowerShell

Ejemplo (PowerShell)

En el ejemplo siguiente se configura la réplica principal y una réplica secundaria en un grupo de disponibilidad para el enrutamiento de solo lectura. Primero, el ejemplo asigna una URL de enrutamiento de solo lectura a cada réplica. Después, establece la lista de enrutamiento de solo lectura en la réplica principal. Las conexiones con la propiedad "ReadOnly" configurada en la cadena de conexión se redirigirán a la réplica secundaria. Si esta réplica secundaria no es accesible para lectura (según la configuración de ConnectionModeInSecondaryRole), la conexión se redirigirá a la réplica principal.

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  

Seguimiento: después de configurar el enrutamiento de solo lectura

Una vez que la réplica principal actual y las réplicas secundarias legibles se han configurado para admitir el enrutamiento de solo lectura en ambos roles, las réplicas secundarias legibles pueden recibir solicitudes de conexión con intención de solo lectura de clientes que se conectan mediante la escucha del grupo de disponibilidad.

Requisitos y recomendaciones para las cadenas de conexión de cliente

Para que una aplicación cliente use el enrutamiento de solo lectura, la cadena de conexión debe cumplir los requisitos siguientes:

• Usar el protocolo TCP.

• Establezca el atributo o la propiedad de intención de la aplicación en solo lectura.

• Haga referencia al listener de un grupo de disponibilidad que está configurado para ser compatible con el enrutamiento de solo lectura.

• Hacer referencia a una base de datos en ese grupo de disponibilidad.

Además, se recomienda que las cadenas de conexión habiliten la conmutación por error de múltiples subredes, que admite un subproceso de cliente paralelo para cada réplica en cada subred. Esto reduce el tiempo de reconexión de cliente después de una conmutación por error.

La sintaxis para una cadena de conexión depende del proveedor de SQL Server que una aplicación está utilizando. El siguiente ejemplo de cadena de conexión para el proveedor de datos de .NET Framework 4.0.2 para SQL Server, muestra las partes de una cadena de conexión necesarias y que se recomiendan para que funcionen con el enrutamiento de solo lectura.

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

Para obtener más información sobre la intención de la aplicación de solo lectura y el enrutamiento de solo lectura, consulte Escuchas de grupos de disponibilidad, conectividad de clientes y conmutación por error de aplicaciones (SQL Server).

Si el enrutamiento de solo lectura no funciona correctamente

Para obtener información sobre la solución de problemas de una configuración de enrutamiento de solo lectura, vea El enrutamiento de solo lectura no funciona correctamente.

Revertir al comportamiento de enrutamiento predeterminado

A partir de SQL Server 2025 (17.x), puede especificar NONE o READ_WRITE_ROUTING_URL como el destino para revertir el enrutamiento especificado para la réplica de disponibilidad y dirigir el tráfico basándose en el comportamiento predeterminado. Para más información, revise ALTER AVAILABILITY GROUP Transact-SQL comando.

Pasos siguientes

Para ver las configuraciones del enrutamiento de solo lectura

• sys.disponibilidad_solo_lectura_enrutamiento_listas (Transact-SQL)

• sys.availability_replicas (Transact-SQL) (columna read_only_routing_url)

Para configurar el acceso a la conexión del cliente

• Crear o configurar un agente de escucha de un grupo de disponibilidad (SQL Server)

• Configurar el acceso de solo lectura en una réplica de disponibilidad (SQL Server)

Para usar las cadenas de conexión en aplicaciones

• Compatibilidad de SQL Server Native Client para la alta disponibilidad con recuperación de desastres

• Uso de palabras clave de la cadena de conexión con SQL Server Native Client

Blogs:

• Calcular Read_only_routing_url para AlwaysOn

• Blogs del equipo de AlwaysOn de SQL Server: blog oficial del equipo de AlwaysOn de SQL Server

• Blogs de los ingenieros de CSS de SQL Server

Contenido adicional

• Información general de los grupos de disponibilidad AlwaysOn (SQL Server)

• Secundarias activas: réplicas secundarias legibles (Grupos de disponibilidad AlwaysOn)

• Información sobre el acceso de las conexiones de cliente a las réplicas de disponibilidad (SQL Server)

• Agentes de escucha del grupo de disponibilidad, conectividad de cliente y conmutación por error de una aplicación (SQL Server)