Configurar el enrutamiento de solo lectura para un grupo de disponibilidad (SQL Server)

Para configurar el grupo de disponibilidad AlwaysOn para admitir el enrutamiento de solo lectura en SQL Server 2012, puede utilizar Transact-SQL o PowerShell. El enrutamiento 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 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 poseer un agente de escucha de grupo de disponibilidad. Los clientes de solo lectura deben dirigir sus solicitudes de conexión a este agente de escucha y las cadenas de conexión del cliente deben especificar el intento de aplicación como de “solo lectura”. Es decir, deben ser solicitudes de conexión de intento de lectura.

[!NOTA]

Para obtener información sobre cómo configurar una réplica secundaria legible, vea Configurar el acceso de solo lectura en una réplica de disponibilidad (SQL Server).

• Antes de empezar:

  Requisitos previos

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

  Seguridad

• Para configurar el enrutamiento de solo lectura, mediante:

  Transact-SQL

  PowerShell

  [!NOTA]

  SQL Server Management Studio no admite la configuración del enrutamiento de solo lectura.

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

• Tareas relacionadas

• Contenido relacionado

Antes de empezar

Requisitos previos

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

• Se deben configurar una o varias réplicas de disponibilidad para aceptar acceso de solo lectura para el rol secundario (es decir, para ser replicas secundarias legibles). Para obtener más información, vea Configurar el 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.

¿Qué propiedades de réplica debe 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 una dirección URL de enrutamiento de solo lectura. Esta dirección URL tiene efecto cuando la réplica local se ejecuta en el rol secundario. La dirección URL de enrutamiento de solo lectura debe especificarse réplica a 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 se asigna a una dirección 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 desee 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 se debe especificar réplica a 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 de intento de lectura se enrutan al primer elemento secundario legible disponible en la lista de enrutamiento de solo lectura de la réplica principal actual. No hay equilibrio de carga.

[!NOTA]

Para obtener información acerca de los agentes de escucha del grupo de disponibilidad y el enrutamiento de solo lectura, vea Agentes de escucha del grupo de disponibilidad, conectividad de cliente y conmutación por error de una aplicación (SQL Server).

Seguridad

Permisos

Tarea	Permisos
Para configurar réplicas al crear un grupo de disponibilidad	Se requiere la pertenencia al rol fijo de servidor sysadmin y el permiso de servidor CREATE AVAILABILITY GROUP, el permiso ALTER ANY AVAILABILITY GROUP o el permiso CONTROL SERVER.
Para modificar una réplica de disponibilidad	Se requiere el permiso ALTER AVAILABILITY GROUP en el grupo de disponibilidad, el permiso CONTROL AVAILABILITY GROUP, el permiso ALTER ANY AVAILABILITY GROUP o el permiso CONTROL SERVER.

[Arriba]

Usar Transact-SQL

Para configurar el enrutamiento de solo lectura

[!NOTA]

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 instrucción CREATE AVAILABILITY GROUP Transact-SQL. Si va a agregar o modificar una réplica para un grupo de disponibilidad existente, use la instrucción ALTER AVAILABILITY GROUP Transact-SQL.

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

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

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

     • system-address
       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.

     • port
       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, vea Calcular read_only_routing_url para AlwaysOn.

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

Ejemplo (Transact-SQL)

En el ejemplo siguiente se modifica la disponibilidad de las dos réplicas de un grupo de disponibilidad existente, AG1 para admitir el enrutamiento de solo lectura, si una de las 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

Usar PowerShell

Para configurar el enrutamiento de solo lectura

[!NOTA]

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 de disponibilidad 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 (FQDN) y puerto que se usa para el enrutamiento de 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, vea Calcular read_only_routing_url para AlwaysOn.

   • Para configurar el acceso de conexión para el rol principal, especifique ReadonlyRoutingList "server" [ ,…n ], donde server identifica una instancia de 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 obtener más información, vea Obtener ayuda de SQL Server PowerShell.

Para configurar y usar el 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. En el ejemplo, primero se asigna una dirección URL de solo lectura de enrutamiento 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" establecida en la cadena de conexión se redirigirán a la réplica secundaria. Si esta réplica secundaria no es legible (según lo determinado por la opción de configuración ConnectionModeInSecondaryRole), la conexión se dirigirá de nuevo 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

[Arriba]

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

Una vez que la réplica principal actual y las réplicas secundarias legibles están configuradas para admitir el enrutamiento de solo lectura en ambos roles, las réplicas secundarias legibles pueden recibir solicitudes de intentos de conexión de lectura de los clientes que se conectan mediante el agente de escucha de grupo de disponibilidad.

Sugerencia
Cuando se usa bcp (utilidad) o sqlcmd (utilidad), se puede especificar el acceso de solo lectura a cualquier réplica secundaria que esté habilitada para acceso de solo lectura mediante el modificador -K ReadOnly.

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.

• Establecer el atributo o propiedad para el intento de aplicaciones de solo lectura.

• Hacer referencia al agente de escucha de un grupo de disponibilidad que se configura para admitir 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 acerca del intento de aplicación de solo lectura y el enrutamiento de solo lectura, vea Agentes de escucha del grupo de disponibilidad, conectividad de cliente y conmutación por error de una aplicación (SQL Server).

Si el enrutamiento de solo lectura no funciona correctamente

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

Tareas relacionadas

Para ver las configuraciones del enrutamiento de solo lectura

• sys.availability_read_only_routing_lists (Transact-SQL)

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

Para configurar el acceso de la conexión de cliente

• Crear o configurar un agente de escucha del 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

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

[Arriba]

Contenido relacionado

• **Blogs: **

  Calcular read_only_routing_url para AlwaysOn

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

  Blogs de los ingenieros de SQL Server de CSS

• **Notas del producto: **

  Notas del producto de Microsoft para SQL Server 2012

  Notas del producto del equipo de asesoramiento al cliente de SQL Server

[Arriba]

Vea también

Conceptos

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

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

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

Acerca del acceso de conexión de cliente a 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)