Redirigir la carga de trabajo de solo lectura a la réplica secundaria de un grupo de disponibilidad Always On

Se aplica a:SQL Server

Las funcionalidades secundarias activas de Grupos de disponibilidad AlwaysOn incluyen compatibilidad con el acceso de solo lectura a una o varias réplicas secundarias (réplicas secundarias legibles). Una réplica secundaria legible puede estar en modo de disponibilidad de confirmación sincrónica o en el modo de disponibilidad de confirmación asincrónica. Una réplica secundaria legible permite el acceso de solo lectura a todas las bases de datos secundarias. Sin embargo, las bases de datos secundarias legibles no se establecen como de solo lectura. Son dinámicas. Una base de datos secundaria dada cambia a medida que se aplican los cambios en la base de datos principal correspondiente. En una réplica secundaria típica, los datos de las bases de datos secundarias, incluidas las tablas duraderas optimizadas para memoria, están casi en tiempo real. Además, los índices de texto completo se sincronizan con las bases de datos secundarias. En muchas circunstancias, la latencia de datos entre una base de datos principal y la base de datos secundaria correspondiente suele ser de solo unos pocos segundos.

La configuración de seguridad de las bases de datos principales se mantiene en las secundarias. Esto incluye usuarios, roles de base de datos y roles de aplicación, junto con sus permisos correspondientes, y también incluye cifrado de datos transparentes (TDE) si está habilitado en la base de datos principal.

Los grupos de disponibilidad Always On también admiten el reenrutamiento de las solicitudes de conexión con intención de lectura a una réplica secundaria legible para lectura (enrutamiento de solo lectura). Para obtener información sobre el enrutamiento de solo lectura, consulte Uso de un listener para conectarse a una réplica secundaria de solo lectura (enrutamiento de solo lectura).

Ventajas

Dirigir las conexiones de solo lectura a las réplicas secundarias legibles proporciona las siguientes ventajas:

• Descarga de la réplica principal las cargas de trabajo secundarias de solo lectura, lo que permite conservar sus recursos para las cargas de trabajo de misión crítica. Si tiene una carga de trabajo de lectura de gran importancia o si la carga de trabajo no puede tolerar la latencia, debe ejecutarla en el servidor principal.

• Mejora la rentabilidad de la inversión para los sistemas que hospedan las réplicas secundarias legibles.

Además, las secundarias legibles ofrecen un sólido soporte para las operaciones de solo lectura, como se indica a continuación:

• Las estadísticas temporales automáticas en las bases de datos secundarias legibles optimizan las consultas de solo lectura en tablas basadas en disco. Para las tablas con optimización para memoria, se crean automáticamente las estadísticas que faltan. Sin embargo, no hay actualizaciones automáticas de estadísticas en desuso. Deberá actualizar manualmente las estadísticas en la réplica primaria. Para obtener más información, vea Estadísticas de las bases de datos de acceso de solo lectura, más adelante en este tema.

• Las cargas de trabajo de solo lectura en tablas basadas en disco utilizan el control de versiones de fila para eliminar la contención por bloqueo en las bases de datos secundarias. Todas las consultas que se ejecutan en las bases de datos secundarias se asignan automáticamente al nivel de aislamiento de transacciones de instantánea, incluso cuando se establecen de forma explícita otros niveles de aislamiento de transacción. Asimismo, se pasan por alto todas las sugerencias de bloqueo. Esto elimina la contención entre lectores y escritores.

• Las cargas de trabajo de solo lectura para tablas duraderas optimizadas para memoria acceden a los datos exactamente de la misma manera que se accede a ellos en la base de datos principal, mediante procedimientos almacenados nativos o interoperabilidad de SQL, con las mismas limitaciones de los niveles de aislamiento de transacciones (Vea Niveles de aislamiento del motor de base de datos). La carga de trabajo de informes o las consultas de solo lectura que se ejecutan en la réplica principal se pueden ejecutar en la réplica secundaria sin necesidad de hacer ningún cambio. De forma similar, las cargas de trabajo de informes o las consultas de solo lectura que se ejecutan en una réplica secundaria se pueden ejecutar en la réplica principal sin necesidad de hacer ningún cambio. Al igual que ocurre con las tablas basadas en disco, todas las consultas que se ejecutan en las bases de datos secundarias se asignan automáticamente al nivel de transacción de aislamiento de instantánea, incluso cuando se establecen otros niveles de aislamiento de transacción de forma explícita.

• Las operaciones DML se admiten en variables de tabla tanto en los tipos de tabla basados en disco como en los tipos de tabla optimizados para memoria en la réplica secundaria.

Requisitos previos del grupo de disponibilidad

• Réplicas secundarias legibles (requeridas)

  El administrador de la base de datos debe configurar una o varias réplicas de modo que, cuando se ejecuten con el rol secundario, admitan todas las conexiones (únicamente para acceso de solo lectura) o solo conexiones de intención de lectura.

  Nota:

  Opcionalmente, el administrador de la base de datos puede configurar cualquiera de las réplicas de disponibilidad para excluir las conexiones de solo lectura cuando se ejecuten en el rol principal.

  Para más información, consulte Acerca del acceso de conexión de cliente a réplicas de disponibilidad (SQL Server).

  Advertencia

  Solo se podrán leer las réplicas que se encuentren en la misma compilación principal de SQL Server. Consulte Conceptos básicos de la actualización gradual para obtener más información.

• Escucha de grupo de disponibilidad

  Para admitir el enrutamiento de solo lectura, un grupo de disponibilidad debe tener un agente de escucha del grupo de disponibilidad. El cliente de solo lectura debe dirigir sus solicitudes de conexión a este cliente de escucha, y la cadena de conexión del cliente debe 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.

• Enrutamiento de solo lectura

  Enrutamiento de solo lectura se refiere a la capacidad de SQL Server para enrutar las solicitudes entrantes de conexión de solo lectura dirigidas a un agente de escucha de un grupo de disponibilidad a una réplica secundaria legible disponible. Los requisitos previos para el enrutamiento de solo lectura son los siguientes:

  • Para admitir el enrutamiento de solo lectura, una réplica secundaria legible requiere 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.

  • Cada réplica de disponibilidad que deba admitir el enrutamiento de solo lectura cuando actúe como réplica principal requiere 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 pueden tener equilibrio de carga entre réplicas. Para obtener más información, vea Configuración del equilibrio de carga entre réplicas de solo lectura.

  Para más información, consulte Configurar el enrutamiento de solo lectura para un grupo de disponibilidad (SQL Server).

Limitaciones y restricciones

Algunas operaciones no se admiten por completo, como se indica a continuación:

• Tan pronto como se habilita una réplica legible para lectura, puede comenzar a aceptar conexiones a sus bases de datos secundarias. Sin embargo, si hay transacciones activas en una base de datos principal, las versiones de fila no estarán del todo disponibles en la base de datos secundaria correspondiente. Las transacciones activas que existían en la réplica principal cuando se configuró la réplica secundaria deben confirmarse o revertirse. Hasta que el proceso finalice, la asignación del nivel de aislamiento de transacción en la base de datos secundaria estará incompleta y las consultas se bloquearán temporalmente.

  Advertencia

  La ejecución de transacciones largas afecta al número de filas versionadas que se conservan, tanto en tablas en disco como en tablas optimizadas para memoria.

• En las bases de datos secundarias con tablas con optimización para memoria, pese a que siempre se generan versiones de filas para las tablas con optimización para memoria, las consultas se bloquean hasta que se completan todas las transacciones activas que había en la réplica principal cuando se habilitó la réplica secundaria para lectura. De esta forma se garantiza que las tablas basadas en disco y las tablas optimizadas para memoria estén disponibles para la carga de trabajo de informes y para las consultas de solo lectura al mismo tiempo.

• El seguimiento de cambios y la captura de datos modificados no se admiten en las bases de datos secundarias que pertenecen a una réplica secundaria legible:

  • El seguimiento de cambios está deshabilitado de forma explícita en las bases de datos secundarias.

  • La captura de datos modificados no se puede habilitar solo en una base de datos de réplica secundaria. La captura de datos modificados puede habilitarse en la base de datos de réplica principal y los cambios pueden leerse desde las tablas de CDC mediante las funciones de la base de datos de réplica secundaria.

• Dado que las operaciones de lectura se asignan al nivel de aislamiento de transacciones de instantánea, la limpieza de registros fantasma en la réplica principal puede verse bloqueada por transacciones en una o más réplicas secundarias. La tarea de limpieza de registros fantasma limpiará automáticamente los registros fantasma para las tablas basadas en disco en la réplica principal cuando las réplicas secundarias ya no los necesiten. Esto es similar a lo que se realiza cuando se ejecutan transacciones en la réplica principal. En el caso extremo de la base de datos secundaria, deberá eliminar una consulta de lectura de ejecución prolongada que esté bloqueando la limpieza de registros fantasma. Tenga en cuenta que la limpieza de registros fantasma puede bloquearse si la réplica secundaria queda desconectada o si se suspende el movimiento de datos en la base de datos secundaria. Los registros fantasma usan el espacio físico en un archivo de datos, lo que puede provocar problemas de reutilización de espacio. Consulte Limpieza de registros fantasma para obtener más información. Este estado también evita el truncamiento del registro, por lo que si el estado persiste, se recomienda quitar esta base de datos secundaria del grupo de disponibilidad. No hay ningún problema de limpieza de registros fantasma con las tablas optimizadas para memoria porque las versiones de fila se conservan en memoria y son independientes de las versiones de fila de la réplica principal.

• Se puede producir un error en la operación DBCC SHRINKFILE en los archivos que contienen tablas basadas en disco en la réplica principal si el archivo contiene registros fantasma que siguen siendo necesarios en una réplica secundaria.

• A partir de SQL Server 2014 (12.x), las réplicas secundarias legibles pueden permanecer en línea incluso cuando la réplica principal esté sin conexión debido a una acción del usuario o a un error (por ejemplo, que una sincronización se suspenda debido a un error o a un comando del usuario) o que una réplica esté resolviendo su estado porque el WSFC está sin conexión. Sin embargo, el enrutamiento de solo lectura no funciona en esta situación porque el agente de escucha del grupo de disponibilidad está desconectado también. Los clientes deben conectarse directamente a las réplicas secundarias de solo lectura para las cargas de trabajo de solo lectura.

Consideraciones de rendimiento

En esta sección se describen las consideraciones de rendimiento para las bases de datos secundarias legibles

En esta sección:

• Latencia de datos

• Repercusión de la carga de trabajo de solo lectura

• Indexación

• Estadísticas de las bases de datos de acceso de solo lectura

Latencia de datos

Implementar acceso de solo lectura a réplicas secundarias resulta útil si las cargas de trabajo de solo lectura pueden tolerar cierto retraso en los datos. En las situaciones en las que la latencia de datos no es aceptable, considere la posibilidad de ejecutar cargas de trabajo de solo lectura en la réplica principal.

La réplica principal envía las entradas de registro de los cambios en la base de datos principal a las réplicas secundarias. En cada base de datos secundaria, un subproceso de rehacer dedicado aplica las entradas de registro. En una base de datos secundaria de acceso de lectura, un cambio determinado de datos no aparece en los resultados de la consulta hasta que la entrada del registro que contiene el cambio se haya aplicado a la base de datos secundaria y la transacción se haya confirmado en la base de datos principal.

Esto significa que hay latencia, normalmente solo se trata de unos segundos, entre las réplicas principales y secundarias. No obstante, en casos excepcionales, por ejemplo, si los problemas de red reducen el rendimiento, la latencia puede ser importante. La latencia aumenta cuando se producen cuellos de botella de E/S y cuando se suspende el movimiento de los datos. Para supervisar el movimiento de datos en suspensión, puede usar el panel Always On Dashboard o la vista de administración dinámica sys.dm_hadr_database_replica_states.

Latencia de datos en bases de datos con tablas optimizadas para memoria

En SQL Server 2014 (12.x) existían consideraciones especiales en torno a la latencia de datos en las secundarias activas: vea SQL Server 2014 (12.x) Secundarias activas: réplicas secundarias legibles. A partir de SQL Server 2016 (13.x) , no existe ninguna consideración especial en torno a la latencia de datos para tablas optimizadas para memoria. La latencia de datos esperada para tablas optimizadas para memoria es comparable a la latencia para tablas basadas en disco.

Repercusión de la carga de trabajo de solo lectura

Al configurar una réplica secundaria para el acceso de solo lectura, las cargas de trabajo de solo lectura en las bases de datos secundarias utilizan los recursos del sistema, como la CPU y E/S (para tablas basadas en disco) de los subprocesos REDO, especialmente si las cargas de trabajo de solo lectura en tablas basadas en disco realizan un uso intensivo de E/S. No hay ningún impacto en la E/S cuando se tiene acceso a tablas con optimización para memoria porque todas las filas residen en memoria.

Además, las cargas de trabajo de solo lectura en las réplicas secundarias pueden bloquear los cambios de lenguaje de definición de datos (DDL) que se aplican a través de las entradas de registro.

• Aunque las operaciones de lectura no tienen bloqueos compartidos debido a las versiones de fila, estas operaciones tienen bloqueos de estabilidad de esquema (Sch-S), que pueden bloquear las operaciones de puesta al día que aplican cambios DDL. Las operaciones DDL incluyen tablas de instrucciones ALTER/DROP y vistas, pero no incluyen instrucciones DROP o ALTER de procedimientos almacenados. Así, por ejemplo, si se elimina una tabla basada en disco u optimizada para memoria en la réplica principal. Cuando el hilo REDO procesa el registro del log para eliminar la tabla, debe adquirir un bloqueo SCH_M sobre la tabla y puede quedar bloqueado por una consulta en ejecución que está accediendo a la tabla. Este comportamiento es el mismo que en la réplica primaria, salvo que la acción de quitar la tabla forma parte de una sesión de usuario y no de un subproceso REDO.

• Hay un bloqueo adicional en las tablas optimizadas para memoria. La eliminación de un procedimiento almacenado nativo puede bloquear el hilo REDO si hay una ejecución concurrente de ese procedimiento almacenado nativo en la réplica secundaria. Este comportamiento es el mismo en la réplica primaria, salvo que la acción de quitar el procedimiento almacenado forma parte de una sesión de usuario y no de un subproceso REDO.

Debe tener en cuenta los procedimientos recomendados acerca de la creación de consultas y aplicarlos a las bases de datos secundarias. Por ejemplo, programe las consultas de ejecución prolongada tales como agregaciones de datos durante las horas de menos actividad.

Indización

Para optimizar las cargas de trabajo de solo lectura en réplicas secundarias legibles, tal vez desee crear índices en las tablas de las bases de datos secundarias. Debido a que no se pueden realizar cambios de esquema o de datos en las bases de datos secundarias, cree los índices en las bases de datos principales y permita que los cambios se transfieran a la base de datos secundaria mediante el proceso de puesta al día.

Para supervisar la actividad de uso de índices en una réplica secundaria, consulte las columnas user_seeks, user_scansy user_lookups de la vista de administración dinámica sys.dm_db_index_usage_stats .

Estadísticas de las bases de datos de acceso de solo lectura

Las estadísticas de las columnas de tablas y vistas indizadas se usan para optimizar los planes de consulta. Para los grupos de disponibilidad, las estadísticas que se crean y se mantienen en las bases de datos principales se conservan automáticamente en las bases de datos secundarias como parte de la aplicación de los registros de transacciones. No obstante, la carga de trabajo de solo lectura en las bases de datos secundarias puede necesitar estadísticas distintas de las que se crean en las bases de datos principales. Sin embargo, debido a que las bases de datos secundarias están restringidas al acceso de solo lectura, las estadísticas no se pueden crear en las bases de datos secundarias.

Para resolver este problema, la réplica secundaria crea y mantiene las estadísticas temporales para las bases de datos secundarias en tempdb. El sufijo _readonly_database_statistic se anexa al nombre de las estadísticas temporales para diferenciarlas de las estadísticas permanentes que se mantienen de la base de datos principal.

Solo SQL Server puede crear y actualizar las estadísticas temporales. No obstante, puede eliminar las estadísticas temporales y supervisar sus propiedades mediante las mismas herramientas que se usan para las estadísticas permanentes:

• Elimine las estadísticas temporales mediante la DROP STATISTICS instrucción Transact-SQL.

• Consulte las estadísticas mediante las vistas de catálogo sys.stats y sys.stats_columns. sys_stats incluye una columna, is_temporary, para indicar las estadísticas que son permanentes y las que son temporales.

No se permite la actualización de estadísticas automáticas para tablas con optimización de memoria en la réplica principal o secundaria. Debe supervisar el rendimiento de las consultas y los planes de consulta en la réplica secundaria, y actualizar manualmente las estadísticas en la réplica principal cuando sea necesario. Sin embargo, las estadísticas que faltan se crean automáticamente tanto en la réplica principal como en la secundaria.

Para obtener más información sobre las estadísticas de SQL Server, vea Estadísticas.

En esta sección:

• Estadísticas permanentes obsoletas en bases de datos secundarias

• Limitaciones y restricciones

Estadísticas permanentes obsoletas en bases de datos secundarias

SQL Server detecta cuándo están obsoletas las estadísticas permanentes de una base de datos secundaria. Pero no se pueden realizar cambios en las estadísticas permanentes, excepto a través de los cambios en la base de datos principal. Para la optimización de consultas, SQL Server crea estadísticas temporales para tablas basadas en disco en la base de datos secundaria y usa estas estadísticas en lugar de las estadísticas en desuso permanentes.

Cuando las estadísticas permanentes se actualizan en la base de datos principal, se guardan automáticamente en la base de datos secundaria. A continuación SQL Server usa las estadísticas actualizadas permanentes, más actuales que las estadísticas temporales.

Si se produce una conmutación por error del grupo de disponibilidad, las estadísticas temporales se eliminan en todas las réplicas secundarias.

Limitaciones y restricciones

• Debido a que las estadísticas temporales se almacenan en tempdb, el reinicio del servicio SQL Server provoca que desaparezcan todas las estadísticas temporales.

• El sufijo _readonly_database_statistic está reservado para las estadísticas que genera SQL Server. Este sufijo no se puede usar al crear estadísticas en una base de datos principal. Para más información, consulte Estadísticas.

Obtener acceso a tablas optimizadas para memoria en una réplica secundaria

Los niveles de aislamiento de transacción que se pueden usar con tablas optimizadas para memoria en una réplica secundaria son los mismos que en la réplica principal. Se recomienda establecer el nivel de aislamiento de nivel de sesión en READ COMMITTED y establecer la opción de nivel de base de datos MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT en ON. Por ejemplo:

ALTER DATABASE CURRENT SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON  
GO  
SET TRANSACTION ISOLATION LEVEL READ COMMITTED  
GO  
SELECT SUM(UnitPrice*OrderQty)   
FROM Sales.SalesOrderDetail_inmem  
GO  
  

Consideraciones de planeamiento de capacidad

• En el caso de las tablas basadas en disco, las réplicas secundarias legibles pueden requerir espacio en tempdb por dos motivos:

  • El nivel de aislamiento de instantánea copia las versiones de fila en tempdb.

  • Se crean estadísticas temporales para las bases de datos secundarias y se mantienen en tempdb. Las estadísticas temporales pueden causar un ligero aumento del tamaño de tempdb. Para obtener más información, vea Estadísticas de las bases de datos de acceso de solo lectura, más adelante en esta sección.

• Al configurar el acceso de lectura en una o varias réplicas secundarias, las bases de datos principales agregan 14 bytes de sobrecarga en las filas de datos eliminadas, modificadas o insertadas para almacenar punteros a versiones de fila en las bases de datos secundarias para tablas basadas en disco. Esta sobrecarga de 14 bytes se aplica a las bases de datos secundarias. A medida que se agrega la sobrecarga de 14 bytes a las filas de datos, se pueden producir divisiones de página.

  Los datos de versión de fila no son generados por las bases de datos principales. En su lugar, las bases de datos secundarias generan las versiones de fila. Sin embargo, el control de versiones de filas aumenta el almacenamiento de datos tanto en las bases de datos principales como en las secundarias.

  La adición de los datos de versión de fila depende de la configuración del nivel de aislamiento de instantánea o del aislamiento de instantánea de lectura confirmada (RCSI) en la base de datos principal. En la tabla siguiente se describe el comportamiento del control de versiones en una base de datos secundaria legible con configuraciones diferentes para las tablas basadas en disco.

  ¿Réplica secundaria legible?	¿Está habilitado el aislamiento de instantáneas o el nivel RCSI?	Base de datos principal	Base de datos secundaria
  No	No	Sin versiones de fila ni sobrecarga de 14 bytes	Sin versiones de fila ni sobrecarga de 14 bytes
  No	Sí	Versiones de fila y sobrecarga de 14 bytes	Sin versiones de fila, pero con sobrecarga de 14 bytes
  Sí	No	Sin versiones de fila, pero con sobrecarga de 14 bytes	Versiones de fila y sobrecarga de 14 bytes
  Sí	Sí	Versiones de fila y sobrecarga de 14 bytes	Versiones de fila y sobrecarga de 14 bytes

Tareas relacionadas

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

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

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

• Supervisar grupos de disponibilidad (Transact-SQL)

• Ver las propiedades de la réplica de disponibilidad (SQL Server)

• Utilizar el cuadro de diálogo Nuevo grupo de disponibilidad (SQL Server Management Studio)

Contenido relacionado

• Blog del equipo Always On de SQL Server: el blog oficial del equipo de Always On de SQL Server

Consulte también

Información general de los grupos de disponibilidad AlwaysOn (SQL Server)
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)
estadísticas