Administración de la recuperación de bases de datos acelerada

Se aplica a: SQL Server 2019 (15.x)

Este artículo contiene información sobre los procedimientos recomendados para administrar y configurar la recuperación acelerada de la base de datos (ADR) para SQL Server 2019 (15.x) y versiones posteriores. Para más información sobre ADR en Azure SQL, consulte Recuperación acelerada de la base de datos en Azure SQL.

Nota:

En Azure SQL Database y Azure SQL Managed Instance, la recuperación acelerada de la base de datos (ADR) está habilitada en todas las bases de datos y no se puede deshabilitar. Si observa problemas con el uso del almacenamiento, transacciones de anulación elevadas y otros factores, consulte Solución de problemas de Recuperación acelerada de la base de datos o póngase en contacto con el Soporte técnico de Azure.

Quién debe considerar la recuperación de base de datos acelerada

Muchos clientes consideran que la recuperación acelerada de la base de datos (ADR) es una tecnología valiosa para mejorar el tiempo de recuperación. Antes de decidir qué bases de datos deben utilizar la ADR, hay que tener en cuenta la acumulación de los factores que se indican a continuación y saber si la acumulación de factores se inclina a favor o en contra del uso de la ADR.

• ADR se recomienda para cargas de trabajo con transacciones de larga duración que no pueden evitarse. Por ejemplo, en los casos en los que las transacciones de larga duración corren el riesgo de revertirse, ADR puede ser de ayuda.

• ADR se recomienda para cargas de trabajo que han detectado casos en los que las transacciones activas hacen que el registro de transacciones crezca de forma significativa.

• ADR se recomienda para las cargas de trabajo que han experimentado largos períodos de falta de disponibilidad de la base de datos debido a la recuperación de larga duración de SQL Server (por ejemplo, un reinicio inesperado de SQL Server o la reversión manual de transacciones).

• Recuperación acelerada de la base de datos no se admite para las bases de datos inscritas en la creación de reflejo de la base de datos.

• ADR no se recomienda para bases de datos de más de 100 terabytes debido al limpiador de versiones del almacén de versiones persistentes (PVS) de un solo subproceso.

• Si la aplicación realiza muchas actualizaciones incrementales no por lotes, como actualizar un registro cada vez que se accede a una fila o esta se inserta, es posible que la carga de trabajo no sea óptima para ADR. Considere la posibilidad de volver a escribir las consultas de la aplicación en actualizaciones por lotes, siempre que sea posible, hasta el final del comando y reducir un número elevado de pequeñas transacciones de actualización.

Evaluación de la viabilidad de la carga de trabajo para ADR

Una vez que haya habilitado ADR en la carga de trabajo, busque signos de que el almacén de versiones persistente (PVS) no puede seguir el ritmo. Se recomienda supervisar el estado de ADR mediante los métodos que se encuentran en Solución de problemas de recuperación acelerada de la base de datos.

ADR no se recomienda para entornos de base de datos con un alto recuento de actualizaciones o eliminaciones, como OLTP de gran volumen, sin un período de reposo/recuperación para que el proceso de limpieza de PVS reclame espacio. Normalmente, los ciclos de operación empresarial dejan este tiempo, pero en algunos escenarios puede que quiera iniciar el proceso de limpieza de PVS manualmente para aprovechar las condiciones de actividad de la aplicación.

• Si el proceso de limpieza de PVS se está ejecutando durante un largo período, es posible que el recuento de transacciones anuladas aumente, lo que también hará que aumente el tamaño de PVS. Use la vista de administración dinámica sys.dm_tran_aborted_transactions para notificar el recuento de transacciones anuladas y sys.dm_tran_persistent_version_store_stats para informar de las horas de inicio y finalización de la limpieza junto con el tamaño de PVS. Para más información, consulte sys.dm_tran_persistent_version_store_stats.

• Para activar el proceso de limpieza del almacén de versiones persistentes manualmente entre cargas de trabajo o durante las ventanas de mantenimiento, use sys.sp_persistent_version_cleanup. Para más información, consulte sys.sp_persistent_version_cleanup.

• Las cargas de trabajo que presentan consultas de larga duración en los modos de aislamiento SNAPSHOT o READ COMMITTED SNAPSHOT pueden retrasar la limpieza de PVS de ADR en otras bases de datos, lo que hace que el archivo PVS aumente de tamaño. Para obtener más información, consulte la sección sobre exámenes de instantáneas activas largas en Solución de problemas de la recuperación acelerada de la base de datos. Esto se aplica a las instancias de SQL Server y Azure SQL Managed Instance, o en un grupo elástico de Azure SQL Database.

Procedimientos recomendados para la recuperación acelerada de la base de datos

Esta sección contiene instrucciones y recomendaciones para ADR.

• En el caso de SQL Server, aísle el almacén de la versión de PVS en un grupo de archivos en un almacenamiento de nivel superior, como un SSD de alta gama, un SSD avanzado o una memoria persistente (PMEM), a veces denominada memoria de la clase de almacenamiento (SCM). Para obtener más información, consulte Cambiar la ubicación del PVS a otro grupo de archivos. Esta opción no está disponible para Azure SQL Database y Azure SQL Managed Instance.

• Asegúrese de que hay suficiente espacio en la base de datos para tener en cuenta el uso del PVS. Si la base de datos no tiene espacio suficiente para que el PVS crezca, ADR no podrá generar versiones. ADR ahorra espacio en el almacén de versiones en comparación con el almacén de versiones tempdb.

• Evite múltiples transacciones de ejecución larga en la base de datos. Aunque un objetivo de ADR es acelerar la recuperación de la base de datos debido a la fase de puesta al día, múltiples transacciones de ejecución larga pueden retrasar la limpieza de versiones y aumentar el tamaño del almacén de versiones persistente.

• Evite transacciones grandes con cambios en la definición de datos u operaciones DDL. ADR usa un mecanismo SLOG (secuencia de registro del sistema) para realizar el seguimiento de las operaciones DDL usadas en la recuperación. El SLOG solo se usa mientras la transacción está activa. SLOG tiene puntos de control, por lo que evitar transacciones grandes que usan SLOG puede ayudar al rendimiento general. Estos escenarios pueden hacer que SLOG ocupe más espacio:

  • Muchos DDL se ejecutan en una transacción. Por ejemplo, en una transacción, crear y quitar rápidamente tablas temporales.

  • Una tabla tiene un gran número de particiones o índices que se modifican. Por ejemplo, una operación DROP TABLE en dicha tabla requeriría una gran reserva de memoria SLOG, lo que retrasaría el truncamiento del registro de transacciones y así como las operaciones de deshacer y rehacer. La solución alternativa puede ser quitar los índices individual y gradualmente y, luego, quitar la tabla. Para más información sobre SLOG, consulte Componentes de recuperación de ADR.

• Evite o reduzca situaciones anuladas innecesarias. Una alta tasa de anulación presionará al limpiador del almacén de versiones persistente y reducirá el rendimiento de ADR. Las anulaciones pueden provenir de una alta tasa de interbloqueos, claves duplicadas u otras infracciones de restricción.

  • La DMV sys.dm_tran_aborted_transactions muestra todas las transacciones anuladas en la instancia de SQL Server. La columna nested_abort indica que la transacción se ha confirmado, pero hay partes anuladas (puntos de retorno o transacciones anidadas) que pueden bloquear el proceso de limpieza del almacén de versiones persistentes. Para más información, consulte sys.dm_tran_aborted_transactions (Transact-SQL).

Habilitar y controlar la recuperación de bases de datos acelerada

Nota:

En Azure SQL Database y Azure SQL Managed Instance, la recuperación acelerada de la base de datos (ADR) está habilitada en todas las bases de datos y no se puede deshabilitar ni mover a un grupo de archivos diferente.

ADR está desactivada de forma predeterminada en SQL Server 2019 (15.x) y se puede controlar mediante la sintaxis de DDL:

ALTER DATABASE [DB] SET ACCELERATED_DATABASE_RECOVERY = {ON | OFF};

Use esta sintaxis para controlar si la característica está activada o desactivada, y para designar un grupo de archivos específico para los datos del almacén de versiones persistentes (PVS). Si no se especifica ningún grupo de archivos, el PVS se almacenará en el grupo de archivos PRIMARY.

Se necesita un bloqueo exclusivo en la base de datos para cambiar este estado. Esto significa que el comando ALTER DATABASE se detendrá hasta que todas las sesiones activas hayan desaparecido y que las sesiones nuevas esperarán detrás del comando ALTER DATABASE. Si es importante completar la operación y quitar el bloqueo, puede usar la cláusula de terminación WITH ROLLBACK [IMMEDIATE | AFTER {number} SECONDS | NO_WAIT] para anular las sesiones activas en la base de datos. Para obtener más información, consulte Opciones de ALTER DATABASE SET.

Administración del grupo de archivos del almacén de versiones persistentes

La característica ADR consiste en tener versiones de los cambios y conservar distintas versiones de los elementos de datos en el PVS. Hay algunas consideraciones que hay que tener en cuenta con respecto a la ubicación del PVS y cómo administrar el tamaño de los datos en el PVS.

Para habilitar ADR sin especificar un grupo de archivos

Esta operación requiere acceso exclusivo a la base de datos.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON;
GO

En este caso, cuando no se especifica el grupo de archivos del PVS, el grupo de archivos PRIMARY contiene los datos del PVS.

Para habilitar ADR y especificar que el PVS se almacene en el grupo de archivos

Puede configurar ADR para que use otro grupo de archivos, aparte del grupo de archivos predeterminado PRIMARY, para almacenar los datos de PVS.

Antes de habilitar ADR en un grupo de archivos además de PRIMARY, debe crear el grupo de archivos y el archivo de datos.

Cree el grupo de archivos VersionStoreFG y cree un nuevo archivo de datos en el grupo de archivos. Por ejemplo:

ALTER DATABASE [MyDatabase] ADD FILEGROUP [VersionStoreFG];
GO
ALTER DATABASE [MyDatabase] ADD FILE ( NAME = N'VersionStoreFG'
, FILENAME = N'E:\DATA\VersionStore.ndf'
, SIZE = 8192KB , FILEGROWTH = 65536KB )
TO FILEGROUP [VersionStoreFG];
GO

Solo después de haber creado el grupo de archivos y un archivo de datos secundario, habilite ADR y especifique que el PVS debe almacenarse en un grupo de archivos específico. Esta operación requiere acceso exclusivo a la base de datos.

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
(PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);

Para deshabilitar la característica ADR

ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
GO

Incluso después de deshabilitar la característica ADR, habrá versiones almacenadas en el almacén de versiones persistentes que sigan siendo necesarias en el sistema para la reversión lógica.

Cambiar la ubicación del PVS a otro grupo de archivos

En SQL Server, es posible que tenga que trasladar la ubicación del PVS a otro grupo de archivos por diversos motivos. Por ejemplo, el PVS puede requerir más espacio o un almacenamiento más rápido.

Cambiar la ubicación del PVS es un proceso de tres pasos.

1. Desactive la característica ADR.

   ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = OFF;
   GO
   

2. Espere a que se liberen todas las versiones almacenadas en el PVS.

   Para poder activar ADR con una nueva ubicación para el almacén de versiones persistentes, asegúrese primero de que toda la información de versiones se ha purgado de la ubicación anterior del PVS. Para forzar que se produzca la limpieza, ejecute el comando:

   EXEC sys.sp_persistent_version_cleanup [database name];
   

   El procedimiento almacenado sys.sp_persistent_version_cleanup es sincrónico, lo que significa que no se completará hasta que se haya limpiado toda la información de versiones del PVS actual. Una vez que se haya completado, compruebe que se ha quitado la información de la versión; para ello, haga una consulta a sys.dm_tran_persistent_version_store_stats de DMV y examine el valor de persistent_version_store_size_kb.

   SELECT DB_Name(database_id), persistent_version_store_size_kb 
   FROM sys.dm_tran_persistent_version_store_stats where database_id = [MyDatabaseID];
   

   Cuando el valor de persistent_version_store_size_kb es 0, puede volver a habilitar la característica ADR; para ello, configure que el PVS se encuentre en el nuevo grupo de archivos.

3. Active la característica ADR mediante la especificación de la nueva ubicación para PVS:

   ALTER DATABASE [MyDatabase] SET ACCELERATED_DATABASE_RECOVERY = ON
   (PERSISTENT_VERSION_STORE_FILEGROUP = [VersionStoreFG]);
   

Pasos siguientes

• Conceptos de la recuperación de base de datos acelerada
• Solución de problemas de la recuperación de bases de datos acelerada
• Recuperación de base de datos acelerada en Azure SQL