Configurar el acceso de solo lectura en una réplica de disponibilidad (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 2012 mediante SQL Server Management Studio, Transact-SQL o PowerShell.

Para obtener información acerca de 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).

• Antes de empezar:  

  Requisitos previos y restricciones

  Seguridad

• Para configurar el acceso de una réplica de disponibilidad con:  

  SQL Server Management Studio

  Transact-SQL

  PowerShell

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

• Tareas relacionadas

• Contenido relacionado

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

[Arriba]

Usar 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 de servidores.

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 secundario 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. Este es el valor predeterminado.

     • 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. Este es el valor predeterminado.

     • 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 la propiedad de conexión Application Intent, vea Usar palabras clave de cadena de conexión con SQL Server Native Client.

[Arriba]

Usar Transact-SQL

Para configurar el acceso en una réplica de disponibilidad

[!NOTA]

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

     • 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 Usar palabras clave de cadena de conexión con SQL Server Native Client.

   • ALL
     Se permiten todas las conexiones con las bases de datos de la réplica principal. Este es el valor predeterminado.

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

[Arriba]

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 de 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 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 ConnectionModeInSecondaryRole 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. Este es el valor predeterminado.

     • 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 Usar palabras clave de cadena de conexión con 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 ConnectionModeInPrimaryRole primary_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 la propiedad de conexión Application Intent, vea Usar palabras clave de cadena de conexión con SQL Server Native Client.

     • AllowAllConnections
       Se permiten todas las conexiones con las bases de datos de la réplica principal. Este es el valor predeterminado.

   [!NOTA]

   Para ver la sintaxis de un cmdlet, utilice el cmdlet Get-Help en el entorno de SQL Server 2012 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

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

[Arriba]

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

• 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.	Crear o configurar un agente de escucha del grupo de disponibilidad (SQL Server)
  	Configure el enrutamiento de solo lectura en un grupo de disponibilidad.	Configurar el enrutamiento de solo lectura para un grupo de disponibilidad (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.

[Arriba]

Tareas relacionadas

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

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

[Arriba]

Contenido relacionado

• AlwaysOn: propuesta de valor de secundaria legible

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

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

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

• AlwaysOn: 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

• AlwaysOn: 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

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

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

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

• AlwaysOn: secundaria legible y latencia de datos

[Arriba]

Vea también

Conceptos

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)