Uso de réplicas de solo lectura para descargar cargas de trabajo de consulta de solo lectura

Se aplica a: Azure SQL Database Azure SQL Managed Instance

Como parte de la arquitectura de alta disponibilidad, cada base de datos única o base de datos de grupos elásticos del nivel de servicio Premium y Crítico para la empresa se aprovisiona automáticamente con una réplica principal de lectura y escritura y una o más réplicas secundarias de solo lectura. Las réplicas secundarias se aprovisionan con el mismo tamaño de proceso que la réplica principal. La característica Escalado horizontal de lectura le permite descargar las cargas de trabajo de solo lectura usando la capacidad de proceso de una de las réplicas de solo lectura, en lugar de ejecutarlas en la réplica de lectura y escritura. De este modo, algunas cargas de trabajo de solo lectura se pueden aislar de las cargas de trabajo principales de lectura y escritura sin que su rendimiento se vea afectado. La característica está diseñada para las aplicaciones que contienen cargas de solo lectura separadas de forma lógica; por ejemplo, análisis. En los niveles de servicio Premium y Crítico para la empresa, las aplicaciones podrían obtener ventajas de rendimiento gracias a esta capacidad sin costo adicional.

La característica Escalado horizontal de lectura también está disponible en el nivel de servicio Hiperescala cuando se añade al menos una réplica secundaria. Las réplicas con nombre secundarias de Hiperescala proporcionan escalado independiente, aislamiento de acceso, aislamiento de carga de trabajo, compatibilidad para varios escenarios de escalado horizontal de lectura y otras ventajas. Se pueden usar varias réplicas de alta disponibilidad secundarias para el equilibrio de carga de las cargas de trabajo de solo lectura que requieren más recursos de los disponibles en una réplica de alta disponibilidad secundaria.

La arquitectura de alta disponibilidad de los niveles de servicio Básico, Estándar y De uso general no incluye réplicas. La característica Escalado horizontal de lectura no está disponible en estos niveles de servicio. Sin embargo, al usar Azure SQL Database, las réplicas geográficas pueden proporcionar una funcionalidad similar en estos niveles de servicio. Al usar Azure SQL Managed Instance y grupos de conmutación por error, el agente de escucha de solo lectura del grupo de conmutación por error puede proporcionar una funcionalidad similar, respectivamente.

En el diagrama siguiente se muestra la característica de las instancias administradas y las bases de datos de nivel Premium y Crítico para la empresa.

La característica Escalado horizontal de lectura está habilitada de forma predeterminada en las nuevas bases de datos de nivel Premium, Crítico para la empresa e Hiperescala.

Nota:

El escalado horizontal de lectura siempre está habilitado en el nivel de servicio Crítico para la empresa de SQL Managed Instance y para las bases de datos de Hiperescala con al menos una réplica secundaria.

Si la cadena de conexión SQL está configurada con ApplicationIntent=ReadOnly, la aplicación se redirigirá a una réplica de solo lectura de esa base de datos o instancia administrada. Para más información sobre la propiedad ApplicationIntent, consulte Especificar el intento de la aplicación.

Únicamente en el caso de Azure SQL Database, si desea asegurarse de que la aplicación se conecta a la réplica principal sin tener en cuenta el valor ApplicationIntent de la cadena de conexión SQL, debe deshabilitar explícitamente el escalado horizontal de lectura cuando cree la base de datos o modifique su configuración. Por ejemplo, si actualiza una base de datos de nivel Estándar o De uso General al nivel Premium o Crítico para la empresa y desea asegurarse de que todas las conexiones siguen estableciéndose con la réplica principal, deshabilite el escalado horizontal de lectura. Para obtener información sobre cómo hacerlo, consulte Habilitación y deshabilitación del escalado horizontal de lectura.

Nota:

Las características Almacén de consultas y SQL Profiler no son compatibles con las réplicas de solo lectura.

Coherencia de datos

Los cambios de datos realizados en la réplica principal se conservan en las réplicas de solo lectura de forma sincrónica o asincrónica según el tipo de réplica. Sin embargo, para todos los tipos de réplica, las lecturas desde una réplica de solo lectura siempre son asincrónicas con respecto a la principal. En una sesión conectada a una réplica de solo lectura, las lecturas siempre son transaccionalmente coherentes. Dado que la latencia de propagación de los datos es variable, las diferentes réplicas pueden devolver datos en puntos ligeramente diferentes en el tiempo en relación con la principal y entre sí. Si una réplica de solo lectura deja de estar disponible y la sesión se vuelve a conectar, puede conectarse a una réplica que se encuentre en un momento diferente al de la réplica original. Del mismo modo, si una aplicación cambia datos mediante una sesión de lectura y escritura, y los lee de inmediato mediante una sesión de solo lectura en una réplica de solo lectura, es posible que los cambios más recientes no estén visibles inmediatamente.

La latencia típica de propagación de datos entre la réplica principal y las réplicas de solo lectura varía en el intervalo entre decenas de milisegundos y segundos de un solo dígito. Sin embargo, no hay ningún límite superior fijo en la latencia de propagación de datos. Condiciones como el uso elevado de recursos de la réplica pueden aumentar considerablemente la latencia. Las aplicaciones que requieren una coherencia de datos garantizada entre sesiones o que requieren que los datos confirmados se puedan leer de inmediato deben usar la réplica principal.

Nota:

La latencia de propagación de datos incluye el tiempo necesario para enviar y conservar registros (si procede) a una réplica secundaria. También incluye el tiempo necesario para rehacer (aplicar) estas entradas de registros en páginas de datos. Para garantizar la coherencia de los datos, los cambios no son visibles hasta que se aplique el registro de confirmación de transacciones. Cuando la carga de trabajo usa transacciones más grandes, se aumenta la latencia de propagación de datos efectiva.

Para supervisar la latencia de propagación de datos, consulte la sección Supervisión y solución de problemas de la réplica de solo lectura.

Conexión a una réplica de solo lectura

Cuando se habilita el escalado horizontal de lectura en una base de datos, la opción ApplicationIntent de la cadena de conexión proporcionada por el cliente dictamina si la conexión se enruta a la réplica de escritura o a una réplica de solo lectura. Específicamente, si el valor de ApplicationIntent es ReadWrite (valor predeterminado), la conexión se dirigirá a la réplica de lectura y escritura. Es idéntico al comportamiento cuando ApplicationIntent no está incluido en la cadena de conexión. Si el valor de ApplicationIntent es ReadOnly, la conexión se enruta a una réplica de solo lectura.

Por ejemplo, la siguiente cadena de conexión conecta el cliente a una réplica de solo lectura (los elementos entre corchetes angulares se sustituyen por los valores correctos para su entorno y se quitan dichos corchetes):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Para conectarse a una réplica de solo lectura mediante SQL Server Management Studio (SSMS), seleccionar Opciones

Seleccionar Parámetros de conexión adicionales, escribir ApplicationIntent=ReadOnly y, a continuación, seleccionar Conectar

Cualquiera de las siguientes cadenas de conexión conecta el cliente a una réplica de lectura-escritura (los elementos entre corchetes angulares se sustituyen por los valores correctos para su entorno y se quitan dichos corchetes):

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadWrite;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Server=tcp:<server>.database.windows.net;Database=<mydatabase>;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Verificación de que se trata de una conexión a una réplica de solo lectura

Puede comprobar si está conectado a una réplica de solo lectura mediante la ejecución de la consulta siguiente en el contexto de la base de datos. Si está conectado a una réplica de solo lectura, se devolverá READ_ONLY.

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');

Nota:

En los niveles de servicio Premium y Crítico para la empresa, solo se puede acceder a una de las réplicas de solo lectura en un momento dado. La Hiperescala admite varias réplicas de solo lectura.

Supervisión y solución de problemas de réplicas de solo lectura

Cuando se conecta a una réplica de solo lectura, las vistas de administración dinámica (DMV) reflejan el estado de la réplica y se pueden consultar con fines de supervisión y solución de problemas. El motor de base de datos proporciona varias vistas para exponer una amplia variedad de datos de supervisión.

Las vistas siguientes se usan normalmente para la supervisión y la solución de problemas de las réplicas:

Nombre	Propósito
sys.dm_db_resource_stats	Proporciona métricas de uso de recursos durante la última hora, incluida la CPU, la E/S de datos y el uso de la escritura de registros en relación con los límites de los objetivos de servicio.
sys.dm_os_wait_stats	Proporciona estadísticas de espera agregadas para la instancia del motor de base de datos.
sys.dm_database_replica_states	Proporciona estadísticas de sincronización y estado de mantenimiento de la réplica. La tasa y el tamaño de la cola de la fase de puesta al día sirven como indicadores de la latencia de propagación de datos en la réplica de solo lectura.
sys.dm_os_performance_counters	Proporciona contadores de rendimiento del motor de base de datos.
sys.dm_exec_query_stats	Proporciona estadísticas de ejecución por consulta, como el número de ejecuciones, el tiempo de CPU usado, etc.
sys.dm_exec_query_plan()	Proporciona planes de consulta almacenados en caché.
sys.dm_exec_sql_text()	Proporciona texto de consulta para un plan de consulta almacenado en caché.
sys.dm_exec_query_profiles	Proporciona el progreso de la consulta en tiempo real mientras las consultas están en ejecución.
sys.dm_exec_query_plan_stats()	Proporciona el último plan de ejecución real conocido, incluidas las estadísticas de tiempo de ejecución de una consulta.
sys.dm_io_virtual_file_stats()	Proporciona las IOPS de almacenamiento, el rendimiento y las estadísticas de latencia para todos los archivos de base de datos.

Nota:

Las DMV sys.resource_stats y sys.elastic_pool_resource_stats de la base de datos lógica master devuelven información sobre uso de los recursos en la réplica principal.

Supervisión de las réplicas de solo lectura con eventos extendidos

No se puede crear una sesión de eventos extendidos si está conectado a una réplica de solo lectura. Sin embargo, en Azure SQL Database y Azure SQL Managed Instance, las definiciones de las sesiones de eventos extendidos creadas y alteradas en la réplica principal se replican en réplicas de solo lectura, incluidas las réplicas geográficas, y capturan eventos en réplicas de solo lectura.

En Azure SQL Database, una sesión de eventos extendidos en una réplica de solo lectura que se basa en una definición de sesión de la réplica principal se puede iniciar y detener independientemente de la sesión de la réplica principal.

En Azure SQL Managed Instance, para iniciar un seguimiento en una réplica de solo lectura, primero debe iniciar el seguimiento en la réplica principal para poder iniciar el seguimiento en la réplica de solo lectura. Si no inicia primero el seguimiento en la réplica principal, recibirá el siguiente error al intentar iniciar el seguimiento en la réplica de solo lectura:

Mensaje 3906, nivel 16, estado 2, línea 1 No se pudo actualizar la base de datos "maestra" porque la base de datos es de solo lectura.

Después de iniciar el seguimiento primero en la réplica principal y luego en la réplica de solo lectura, puede detener el seguimiento en la réplica principal.

Para quitar una sesión de eventos en una réplica de solo lectura, siga estos pasos:

1. Conecte el Explorador de objetos SSMS o una ventana de consulta a la réplica de solo lectura.
2. Detenga la sesión en la réplica de solo lectura, ya sea seleccionando Detener sesión en el menú contextual de la sesión en Explorador de objetos o ejecutando ALTER EVENT SESSION [session-name-here] ON DATABASE STATE = STOP; en una ventana de consulta.
3. Conecte el Explorador de objetos SSMS o una ventana de consulta a la réplica principal.
4. Quite la sesión en la réplica principal, ya sea seleccionando Eliminar en el menú contextual de la sesión o ejecutando DROP EVENT SESSION [session-name-here] ON DATABASE;

Nivel de aislamiento de transacción en réplicas de solo lectura

Las transacciones en réplicas de solo lectura siempre usan el nivel de aislamiento de transacción de instantánea, independientemente del nivel de aislamiento de transacción de la sesión, e independientemente de las sugerencias de consulta. El aislamiento de instantáneas usa las versiones de fila para evitar situaciones de bloqueo en que los lectores bloquean a los escritores.

En raras ocasiones, si una transacción de aislamiento de instantánea accede a los metadatos de objeto que se han modificad en otra transacción simultánea, puede recibir el error 3961: "Error de la transacción de aislamiento de instantánea en la base de datos "%.*ls" porque el objeto al que tuvo acceso la instrucción se modificó mediante una instrucción DDL de otra transacción simultánea desde el inicio de esta transacción. Está deshabilitada porque los metadatos no tienen versiones. Una actualización simultánea de los metadatos puede dar lugar a incoherencias si se combina con el aislamiento de la instantánea."

Consultas de ejecución prolongada en réplicas de solo lectura

Las consultas que se ejecutan en réplicas de solo lectura deben tener acceso a los metadatos de los objetos a los que se hace referencia en la consulta (tablas, índices, estadísticas, etc.). En raras ocasiones, si se modifica un objeto de metadatos en la réplica principal mientras una consulta mantiene un bloqueo en el mismo objeto en la réplica de solo lectura, la consulta puede bloquear el proceso que aplica los cambios de la réplica principal a la de solo lectura. Si este tipo de consulta se ejecutara durante mucho tiempo, haría que la réplica de solo lectura no estuviera sincronizada con la réplica principal. En el caso de las réplicas que son posibles destinos de conmutación por error (réplicas secundarias en los niveles de servicio Premium y Crítico para la empresa, réplicas de alta disponibilidad de Hiperescala y todas las réplicas geográficas), esto también retrasaría la recuperación de la base de datos si se produjese una conmutación por error, lo que provocaría un tiempo de inactividad mayor del esperado.

Si una consulta de ejecución prolongada en una réplica de solo lectura provoca directa o indirectamente este tipo de bloqueo, puede finalizarse automáticamente para evitar una latencia excesiva de datos y un posible efecto en la disponibilidad de la base de datos. La sesión recibirá el error 1219, "Su sesión se ha desconectado debido a una operación DDL de elevada prioridad" o el error 3947, "Se ha anulado la transacción porque el proceso secundario no pudo ponerse al día. Vuelva a intentar la transacción".

Nota:

Si recibe el error 3961, 1219 o 3947 al ejecutar consultas en una réplica de solo lectura, vuelva a intentar la consulta. Como alternativa, evite operaciones que modifiquen los metadatos de los objetos (cambios de esquema, mantenimiento de índices, actualizaciones de estadísticas, etc.) en la réplica principal mientras se ejecuten consultas de ejecución prolongada en las réplicas secundarias.

Sugerencia

En los niveles de servicio Prémium y Crítico para la empresa, cuando se conecta a una réplica de solo lectura, las columnas redo_queue_size y redo_rate de la vista de administración dinámica sys.dm_database_replica_states se pueden usar para supervisar el proceso de sincronización de datos, que sirven como indicadores de la latencia de propagación de datos en la réplica de solo lectura.

Habilitación y deshabilitación del escalado horizontal de lectura para SQL Database

Para SQL Managed Instance, el escalado horizontal de lectura se habilita automáticamente en el nivel de servicio Crítico para la empresa y no está disponible en el nivel de servicio De uso general. No es posible deshabilitar y volver a habilitar el escalado horizontal de lectura.

Para SQL Database, el escalado horizontal de lectura está habilitada de forma predeterminada en los niveles de servicio Premium, Crítico para la empresa e Hiperescala. No se puede habilitar en los niveles de servicio Básico, Estándar o De uso general. Se deshabilita automáticamente en las bases de datos del nivel Hiperescala que no están configuradas con ninguna réplica secundaria.

En el caso de bases de datos únicas y agrupadas en Azure SQL Database, puede deshabilitar y volver a habilitar el escalado horizontal de lectura en los niveles de servicio Premium o Crítico para la empresa mediante Azure Portal y Azure PowerShell. Estas opciones no están disponibles para SQL Managed Instance, ya que no se puede deshabilitar el escalado horizontal de lectura.

Nota:

Para bases de datos únicas y de grupos elásticos, la posibilidad de deshabilitar la característica Escalado horizontal de lectura se proporciona por motivos de compatibilidad con versiones anteriores. No se puede deshabilitar el escalado horizontal de lectura en las instancias administradas de tipo Crítico para la empresa.

Azure portal

Para Azure SQL Database, puede administrar la configuración de escalado horizontal de lectura en el panel de base de datos Proceso y almacenamiento, disponible en Configuración. El uso de Azure Portal para habilitar o deshabilitar el escalado horizontal de lectura no está disponible para Azure SQL Managed Instance.

PowerShell

Importante

El módulo de Azure Resource Manager para PowerShell todavía es compatible, pero todo el desarrollo futuro se realizará para el módulo Az.Sql. El módulo de Azure Resource Manager continuará recibiendo correcciones de errores hasta diciembre de 2020 como mínimo. Los argumentos para los comandos del módulo Az y los módulos de Azure Resource Manager son esencialmente idénticos. Para más información sobre la compatibilidad, consulte Presentación del nuevo módulo Az de Azure PowerShell.

Para administrar el escalado horizontal de lectura en Azure PowerShell se requiere la versión de Azure PowerShell de diciembre de 2016 o posterior. Para saber dónde encontrar la versión más reciente de PowerShell, consulte Azure PowerShell.

En Azure SQL Database, puede habilitar o deshabilitar la característica Escalado horizontal de lectura en Azure PowerShell invocando el cmdlet Set-AzSqlDatabase y pasando el valor deseado, Enabled o Disabled, al parámetro -ReadScale. La deshabilitación del escalado horizontal de lectura para SQL Managed Instance no está disponible.

Para deshabilitar el escalado horizontal de lectura en una base de datos existente (los elementos entre corchetes angulares deben sustituirse por los valores correctos en función del entorno y sin incluir dichos corchetes):

Set-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Disabled

Para deshabilitar el escalado horizontal de lectura en una nueva base de datos (los elementos entre corchetes angulares deben sustituirse por los valores correctos en función del entorno y sin incluir dichos corchetes):

New-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Disabled -Edition Premium

Para volver a habilitar el escalado horizontal de lectura en una base de datos existente (los elementos entre corchetes angulares deben sustituirse por los valores correctos en función del entorno y sin incluir dichos corchetes):

Set-AzSqlDatabase -ResourceGroupName <resourceGroupName> -ServerName <serverName> -DatabaseName <databaseName> -ReadScale Enabled

API DE REST

Para crear una base de datos con el escalado horizontal de lectura deshabilitado o para cambiar la configuración de una base de datos existente, utilice el método siguiente con la propiedad readScale establecida en Enabled o Disabled como en la siguiente solicitud de ejemplo.

Method: PUT
URL: https://management.azure.com/subscriptions/{SubscriptionId}/resourceGroups/{GroupName}/providers/Microsoft.Sql/servers/{ServerName}/databases/{DatabaseName}?api-version= 2014-04-01-preview
Body: {
   "properties": {
      "readScale":"Disabled"
   }
}

Para más información, consulte Bases de datos: crear o actualizar.

Uso de la base de datos tempdb en una réplica de solo lectura

La base de datos tempdb de la réplica principal no se replica a las de solo lectura. Cada réplica tiene su propia base de datos tempdb, que se crea a la vez que lo hace la réplica. Esto garantiza que tempdb se puede actualizar y modificar durante la ejecución de consultas. Si la carga de trabajo de solo lectura depende del uso de objetos de tempdb, debe crear estos objetos como parte de la misma carga de trabajo mientras está conectado a una réplica de solo lectura.

Uso del escalado horizontal de lectura con las bases de datos con replicación geográfica

Las bases de datos secundarias con replicación geográfica tienen la misma arquitectura de alta disponibilidad que las principales. Si se conecta a la base de datos secundaria con replicación geográfica con el escalado horizontal de lectura habilitado, las sesiones con ApplicationIntent=ReadOnly se redirigirán a una de las réplicas de alta disponibilidad del mismo modo que se redirigen en la base de datos de escritura principal. Las sesiones sin ApplicationIntent=ReadOnly se enrutarán a la réplica principal de la secundaria con replicación geográfica, que también es de solo lectura.

De este modo, la creación de una réplica geográfica puede proporcionar varias réplicas de solo lectura adicionales para una base de datos principal de lectura y escritura. Cada réplica geográfica adicional proporciona otro conjunto de réplicas de solo lectura. Las réplicas geográficas se pueden crear en cualquier región de Azure, incluida la región de la base de datos principal.

Nota:

No se permite aplicar el método round-robin ni ningún otro enrutamiento de carga equilibrada entre las réplicas de una base de datos secundaria con replicación geográfica, a excepción de una réplica geográfica de Hiperescala con más de una réplica de alta disponibilidad. En ese caso, las sesiones con intención de solo lectura se distribuyen entre todas las réplicas de alta disponibilidad de una réplica geográfica.

Compatibilidad con características en réplicas de solo lectura

A continuación se muestra una lista del comportamiento de algunas características en réplicas de solo lectura:

• La auditoría en las réplicas de solo lectura se habilita automáticamente. Para más información sobre la jerarquía de las carpetas de almacenamiento, las convenciones de nomenclatura y el formato del registro, consulte el artículo sobre el formato del registro de auditoría de SQL Database.
• Información de rendimiento de consultas se basa en los datos del Almacén de consultas, que actualmente no realiza el seguimiento de la actividad en la réplica de solo lectura. Información de rendimiento de consultas no mostrará las consultas que se ejecutan en la réplica de solo lectura.
• El ajuste automático se basa en Almacén de consultas, como se detalla en el documento Ajuste automático. El ajuste automático solo funciona para las cargas de trabajo que se ejecutan en la réplica principal.

Pasos siguientes

• Para más información sobre la oferta de SQL Database de nivel Hiperescala, consulte el artículo sobre el nivel de servicio Hiperescala.