Configuración del acceso de solo lectura a una réplica secundaria de un grupo de disponibilidad Always On

Se aplica a: SQL Server

De forma predeterminada, tanto el acceso de lectura y escritura como de intento de lectura se permiten en la réplica principal. No se permiten conexiones en las réplicas secundarias de un grupo de disponibilidad AlwaysOn. En este tema se describe cómo se configura el acceso de conexión de una réplica de disponibilidad de un grupo de disponibilidad AlwaysOn en SQL Server mediante SQL Server Management Studio, Transact-SQL o PowerShell.

Para obtener más información sobre las implicaciones de habilitar el acceso de solo lectura en una réplica secundaria y una introducción al acceso de conexión, vea Acerca del acceso de conexión de cliente a réplicas de disponibilidad (SQL Server) y Secundarias activas: réplicas secundarias legibles (grupos de disponibilidad AlwaysOn).

Requisitos previos y restricciones

• Para configurar otro acceso de conexión, debe estar conectado a la instancia de servidor que hospeda la réplica principal.

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.

Uso de SQL Server Management Studio

Para configurar el acceso en una réplica de disponibilidad

1. En el Explorador de objetos, conéctese a la instancia del servidor que hospeda la réplica principal y expanda el árbol.

2. Expanda los nodos Alta disponibilidad de AlwaysOn y Grupos de disponibilidad .

3. Haga clic en el grupo de disponibilidad cuya réplica desea cambiar.

4. Haga clic con el botón derecho en la réplica de disponibilidad y haga clic en Propiedades.

5. En el cuadro de diálogo Propiedades de réplica de disponibilidad , puede cambiar el acceso de conexión para el rol principal y para el secundario, del siguiente modo:

   • Para el rol secundario, seleccione un nuevo valor en la lista desplegable Secundario legible , del siguiente modo:

     No
     No se permiten conexiones de usuario a las bases de datos secundarias de esta réplica. No están disponibles para acceso de lectura. Esta es la configuración predeterminada.

     Solo intento de lectura
     Únicamente se permiten conexiones de solo lectura a las bases de datos secundarias de esta réplica. Todas las bases de datos secundarias están disponibles para acceso de lectura.

     Sí
     Se permiten todas las conexiones a las bases de datos secundarias de esta réplica, pero solo para acceso de lectura. Todas las bases de datos secundarias están disponibles para acceso de lectura.

   • Para el rol principal, seleccione un nuevo valor en la lista desplegable Conexiones de rol principal , del siguiente modo:

     Permitir todas las conexiones
     Se permiten todas las conexiones con las bases de datos de la réplica principal. Esta es la configuración predeterminada.

     Permitir conexiones de lectura o escritura
     Cuando la propiedad Application Intent está establecida en ReadWrite o no tiene ningún valor, se permite la conexión. No se permiten las conexiones en las que la propiedad de conexión Application Intent esté establecida en ReadOnly . Esto puede ayudar a evitar que los clientes conecten por equivocación una carga de trabajo de intención de lectura a la réplica principal. Para obtener más información sobre propiedad de conexión Application Intent, vea Using Connection String Keywords with SQL Server Native Client.

Usar Transact-SQL

Para configurar el acceso en una réplica de disponibilidad

Nota

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

   • Para configurar el acceso de conexión 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 ( ALLOW_CONNECTIONS = { NO | READ_ONLY | ALL } )

     donde,

     No
     No se permiten conexiones directas a las bases de datos secundarias de esta réplica. No están disponibles para acceso de lectura. Esta es la configuración predeterminada.

     READ_ONLY
     Únicamente se permiten conexiones de solo lectura a las bases de datos secundarias de esta réplica. Todas las bases de datos secundarias están disponibles para acceso de lectura.

     ALL
     Se permiten todas las conexiones a las bases de datos secundarias de esta réplica, pero solo para acceso de lectura. Todas las bases de datos secundarias están disponibles para acceso de lectura.

3. Para configurar el acceso de conexión 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 ( ALLOW_CONNECTIONS = { READ_WRITE | ALL } )

   donde,

   READ_WRITE
   No se permiten las conexiones en las que la propiedad de conexión Application Intent esté establecida en ReadOnly . Cuando la propiedad Application Intent está establecida en ReadWrite o no tiene ningún valor, se permite la conexión. Para obtener más información sobre propiedad de conexión Application Intent, vea Using Connection String Keywords with SQL Server Native Client.

   ALL
   Se permiten todas las conexiones con las bases de datos de la réplica principal. Esta es la configuración predeterminada.

Ejemplo (Transact-SQL)

En el siguiente ejemplo se agrega una réplica secundaria a un grupo de disponibilidad denominado AG2. Se especifica una instancia de servidor independiente, COMPUTER03\HADR_INSTANCE, para hospedar la nueva réplica de disponibilidad. Esta réplica configurada para permitir las conexiones de solo lectura-escritura para el rol principal y permitir las conexiones de solo intención de lectura para el rol secundario.

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  

Usar PowerShell

Para configurar el acceso en una réplica de disponibilidad

Nota

Para obtener un ejemplo de código, vea Ejemplo (PowerShell), más adelante en esta sección.

1. Cambie el directorio (cd) a 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 acceso de conexión para el rol secundario, especifique el parámetro ConnectionModeInSecondaryRolepalabra_clave_de_rol_secundario , donde palabra_clave_de_rol_secundario es igual a uno de los siguientes valores:

     AllowNoConnections
     No se permiten conexiones directas con las bases de datos de la réplica secundaria y las bases de datos no están disponibles para acceso de lectura. Esta es la configuración predeterminada.

     AllowReadIntentConnectionsOnly
     Solo se permiten conexiones con las bases de datos de la réplica secundaria en las que la propiedad Application Intent está establecida en ReadOnly. Para obtener más información acerca de esta propiedad, vea Using Connection String Keywords with SQL Server Native Client.

     AllowAllConnections
     Se permiten todas las conexiones con las bases de datos de la réplica secundaria para acceso de solo lectura.

   • Para configurar el acceso de conexión para el rol principal, especifique el parámetro ConnectionModeInPrimaryRolepalabra_clave_de_rol_principal, donde palabra_clave_de_rol_principal es igual a uno de los siguientes valores:

     AllowReadWriteConnections
     No se permiten las conexiones en las que la propiedad de conexión Application Intent esté establecida en ReadOnly. Cuando la propiedad Application Intent está establecida en ReadWrite o no tiene ningún valor, se permite la conexión. Para obtener más información sobre propiedad de conexión Application Intent, vea Using Connection String Keywords with SQL Server Native Client.

     AllowAllConnections
     Se permiten todas las conexiones con las bases de datos de la réplica principal. Esta es la configuración predeterminada.

   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.

Para configurar y usar el proveedor de SQL Server PowerShell

• Proveedor de SQL Server PowerShell Provider

Ejemplo (PowerShell)

En el siguiente ejemplo, los parámetros ConnectionModeInSecondaryRole y ConnectionModeInPrimaryRole se establecen en 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  
  

Seguimiento: Después de configurar el acceso de solo lectura para una réplica de disponibilidad

Acceso de solo lectura a una réplica secundaria legible

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

• Para habilitar las aplicaciones cliente para conectarse a réplicas secundarias legibles:

Requisito previo	Vínculo
Asegúrese de que el grupo de disponibilidad tiene un agente de escucha.	Configuración de un agente de escucha para un grupo de disponibilidad Always On (SQL Server)
Configure el enrutamiento de solo lectura en un grupo de disponibilidad.	Configuración del enrutamiento de solo lectura para un grupo de disponibilidad Always On (SQL Server)

Factores que podrían afectar a los desencadenadores y trabajos tras la conmutación por error

Si tiene desencadenadores y trabajos que darán error al ejecutarse en una base de datos secundarias no legible o en una base de datos secundaria legible, tiene que escribir los desencadenadores y los trabajos para controlar una réplica dad y determinar si la base de datos es una base de datos principal o si es una base de datos secundaria legible. Para obtener esta información, use la función DATABASEPROPERTYEX para devolver la propiedad Updateability de la base de datos. Para identificar una base de datos de solo lectura, especifique READ_ONLY como el valor, según se indica a continuación:

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

Para identificar una base de datos de solo escritura, especifique READ_WRITE como el valor.

Related Tasks

• Configuración del enrutamiento de solo lectura para un grupo de disponibilidad Always On (SQL Server)

• Configuración de un agente de escucha para un grupo de disponibilidad Always On (SQL Server)

Contenido relacionado

• Always On: propuesta de valor de secundaria legible

• Always On: ¿por qué hay dos opciones para habilitar una réplica secundaria para la carga de trabajo de lectura?

• Always On: configuración de una réplica secundaria legible

• Always On: acabo de habilitar una secundaria legible pero mi consulta está bloqueada

• Always On: ofrecer las estadísticas más recientes de la secundaria legible, la base de datos de solo lectura y la instantánea de base de datos

• Always On: desafíos de las estadísticas de la base de datos de solo lectura, la instantánea de base de datos y la réplica secundaria

• Always On: impacto sobre la carga de trabajo principal al ejecutar la carga de trabajo de informes en la réplica secundaria

• Always On: efectos de asignar la carga de trabajo de informes de la secundaria legible al aislamiento de instantánea

• Always On: minimizar el bloqueo de subprocesos REDO al ejecutar la carga de trabajo de informes en la réplica secundaria

• Always On: secundaria legible y latencia de datos

Consulte también

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)