Configurar el acceso Read-Only en una réplica de disponibilidad (SQL Server)

De forma predeterminada, se permite el acceso de lectura y escritura y intención de lectura a la réplica principal y no se permite ninguna conexión a réplicas secundarias de un grupo de disponibilidad AlwaysOn. En este tema se describe cómo configurar el acceso de conexión en una réplica de disponibilidad de un grupo de disponibilidad AlwaysOn en SQL Server 2014 mediante SQL Server Management Studio, Transact-SQL o PowerShell.

Para obtener información sobre las implicaciones de habilitar el modo solo lectura para una réplica secundaria y para obtener una introducción al acceso de conexión, consulte Acerca del acceso de conexión de cliente a réplicas de disponibilidad (SQL Server) y Secundarias activas: Réplicas secundarias con lectura habilitada (grupos de disponibilidad AlwaysOn).

Antes de empezar

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.

Seguridad

Permisos

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 el nodo Alta disponibilidad AlwaysOn y el nodo 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 configuración es la 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 configuración es la 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.

Uso de Transact-SQL

Para configurar el acceso en una réplica de disponibilidad

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 configuración es la predeterminada.

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

     TODOS
     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,

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

   TODOS
   Se permiten todas las conexiones con las bases de datos de la réplica principal. Esta configuración es la 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  

Uso de PowerShell

Para configurar el acceso en una réplica de alta disponibilidad

1. Cambie el directorio (cd) a la instancia del servidor que hospeda la réplica principal.

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

   • Para configurar el acceso de conexión para el rol secundario, especifique el ConnectionModeInSecondaryRole parámetro secondary_role_keyword, donde secondary_role_keyword 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 configuración es la 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 ConnectionModeInPrimaryRoleprimary_role_keyword, donde primary_role_keyword 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 configuración es la predeterminada.

   Nota:

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

Para configurar y usar el proveedor de SQL Server PowerShell, consulte Proveedor de SQL Server PowerShell.

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

• Al utilizar la utilidad bcp o la utilidad sqlcmd, puede configurar acceso de solo lectura a cualquier réplica secundaria habilitada para dicho acceso especificando el modificador -K ReadOnly.

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

  	Prerrequisito	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 Updatability 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],'Updatability') = N'READ_ONLY'  

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

Tareas relacionadas

• 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

Véase también

Información general de los grupos de disponibilidad AlwaysOn (SQL Server)
Secundarios activos: réplicas secundarias accesibles para lectura (grupos de disponibilidad AlwaysOn)
Acerca del acceso de conexión de cliente a réplicas de disponibilidad (SQL Server)