SQL Server 2016 - Cómo archivar tablas históricas en Azure con Stretch Database

Una forma muy sencilla de implementar Stretch Database y comenzar a almacenar sus tablas en la nube de Microsoft Azure es utilizar la interfaz SQL Server Management Studio (SSMS). Me gusta mucho usar el Asistente para implementar Stretch Database, especialmente en la primera tabla, ya que ya te permite crear las bases de datos y las claves de cifrado (Always Encrypted) sin siquiera tener que abrir el Portal de Azure.

El primer paso es seleccionar la tabla que desea almacenar en la nube a través del "Explorador de objetos", haga clic derecho y seleccione el menú "Estirar base de datos" y haga clic en la opción "Habilitar".

La primera pantalla del Asistente para ampliar la base de datos muestra un resumen de cómo se configurará esta función.

Si su tabla tiene algún recurso que sea un Limitación de la extensión de la base de datos, te encontrarás con esta pantalla.

En el caso de la tabla SalesOrderHeader, las limitaciones que impiden el uso de Stretch Database son las restricciones de verificación, predeterminadas y de claves externas. Para solucionar esto, crearé una nueva tabla (SalesOrderHeader2) con el contenido de la tabla original (SalesOrderHeader), pero sin las restricciones.

Ahora, intentemos activar Stretch Database en la tabla recién creada. Tenga en cuenta que la opción predeterminada es “Tabla completa”, es decir, la tabla completa se almacenará en la nube. Si deseas mantener parte de los datos en la infraestructura local, incluso para tener un mejor rendimiento en las consultas más frecuentes, haz clic en este enlace para abrir la pantalla de configuración de la migración a la nube.

Si desea personalizar la forma de identificar datos “fríos” (datos poco utilizados – almacenados en la nube) y datos “calientes” (datos usados ​​frecuentemente – almacenados en el sitio), puede seleccionar la opción “Elegir filas”, definir un nombre para este filtro y elegir la columna y expresión que se utilizará para filtrar los datos, como se muestra a continuación:

Así es como se ve la pantalla de configuración de Stretch Database después de crear un filtro para seleccionar las líneas que se migrarán:

En esta pantalla debemos configurar la cuenta de Azure que almacenará los datos “fríos” de la tabla de origen.

En esta pantalla, configuraremos una contraseña segura para la clave maestra de la base de datos (DMK), que se utilizará para cifrar datos en la nube (usando Siempre cifrado, todo transparente para ti)

Y aquí podemos configurar el Firewall para esta base de datos, ya sea usando su IP actual o proporcionando un rango de IP para permitir el acceso. Esta configuración depende en gran medida de su red, por lo que puede cambiarse según su escenario. Si desea cambiar esta configuración del firewall después de activar Stretch Database, puede hacerlo a través del portal azul.

Resumen de las acciones que se llevarán a cabo:

¡La migración de datos se completó con éxito! Ahora solo espera a que finalice la migración de datos en segundo plano, de forma completamente transparente. Para monitorear el progreso de la transferencia de datos, use DMV sys.dm_db_rda_migration_status:

Tenga en cuenta que después de crear Stretch Database en al menos una tabla, el icono de la base de datos cambia en la pantalla SSMS Object Explorer.

Además, se crean 2 nuevos objetos: 1 Fuente de datos externa (Stretch Server) y 1 función de clasificación (fncUltimos_4_Anos)

Vídeo de demostración

Después de demostrar cómo implementar Stretch Database usando la interfaz SSMS, ahora compartiré con ustedes cómo hacer lo mismo, usando solo comandos T-SQL.

El primer paso es habilitar Stretch Database en la instancia:

EXEC sp_configure 'remote data archive' , '1';  
GO

RECONFIGURE;  
GO

Antes de poder habilitar Stretch Database en tablas individuales, debe habilitarlo en la base de datos. Habilitar Stretch Database en una base de datos o tabla requiere permisos db_owner y CONTROL DATABASE.

Deberá iniciar sesión manualmente en Azure Portal para crear un nuevo servidor de Stretch Database (si aún no tiene uno). Recuerda configurar las reglas del firewall para poder acceder.

Ahora es el momento de configurar el cifrado de los datos que se enviarán a Stretch Database:

USE [AdventureWorks]
GO  

CREATE MASTER KEY ENCRYPTION BY PASSWORD='minhasenha'; 
GO

CREATE DATABASE SCOPED CREDENTIAL MeuStretchDatabase  
    WITH IDENTITY = 'dirceuresende', SECRET = 'minha_senha';
GO

Y habilitemos Stretch Database en la base de datos:

ALTER DATABASE AdventureWorks  
    SET REMOTE_DATA_ARCHIVE = ON  
        (  
            SERVER = 'dirceuresende_stretchdb.database.windows.net', 
            CREDENTIAL = MeuStretchDatabase
        );
GO

Y habilitemos Stretch Database para la tabla:

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) );
GO

Si desea filtrar las filas que se cargarán en Stretch Database en lugar de almacenar la tabla completa en Azure, puede usar un filtro de predicado:

-- Criando a função de filtro
CREATE FUNCTION dbo.fn_stretch_by_date(@date datetime)  
RETURNS TABLE  
WITH SCHEMABINDING   
AS   
    RETURN SELECT 1 AS is_eligible WHERE @date < '2014-01-01'
GO

-- Iniciando o stretch database com o filtro
ALTER TABLE SalesHeaderOrder2 
  SET ( REMOTE_DATA_ARCHIVE = ON (
    FILTER_PREDICATE = dbo.fn_stretch_by_date(OrderDate),
    MIGRATION_STATE = OUTBOUND )
  )

Vale la pena señalar que solo se pueden usar funciones deterministas en el filtro Stretch Database, es decir, no podrá usar GETDATE(), por ejemplo, para crear un filtro de fecha dinámico. Si necesitas esto, tendrás que crear nuevas funciones, cambiando el filtro y aplicando un ALTER TABLE para cambiar la función del filtro:

-- Criando uma nova função de filtro para aumentar 1 ano da função criada no exemplo anterior
CREATE FUNCTION dbo.fn_stretch_by_date_201501(@date datetime)  
RETURNS TABLE  
WITH SCHEMABINDING   
AS   
    RETURN SELECT 1 AS is_eligible WHERE @date < '2015-01-01'
GO

-- Iniciando o stretch database com o filtro
ALTER TABLE SalesHeaderOrder2 
  SET ( REMOTE_DATA_ARCHIVE = ON (
    FILTER_PREDICATE = dbo.fn_stretch_by_date_201501(OrderDate),
    MIGRATION_STATE = OUTBOUND )
  )

También puedes crear la tabla usando Stretch Database:

CREATE TABLE SalesOrderHeader2
    ( ... )
    WITH ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) );
GO

Si analizas el plan de ejecución de una consulta antes de activar Stretch en esta tabla, verás que el plan no tendrá el operador “Remote Query” al leer datos de las tablas (a menos que uses datos externos, como Linked Server), solo operadores de datos locales, como Index Seek, Table Scan, Index Scan, etc.

Sin embargo, cuando activamos Stretch en esta tabla, vemos que al consultar los datos en el servidor remoto, el operador “Remote Query” pasa a formar parte de nuestro plan de ejecución. Esto ocurre tanto cuando consulta la tabla completa como cuando consulta datos que están solo en el servidor remoto.

Si el resultado de su consulta solo se almacena localmente, el operador "Consulta remota" no aparecerá en el plan de ejecución.

En el caso del ejemplo anterior, la columna “Descontinuada” se utilizó como filtro para determinar los datos que se deben migrar o no (1 = Migra, 0 = Permanece local), es decir, si no se realiza ningún filtro (trayendo toda la tabla) o algún otro filtro que devuelva datos con este flag con los valores 0 y 1 o solo el valor 1, el operador “Consulta Remota” estará presente, ya que parte de los datos estarán en el servidor remoto.

Si una consulta solo devuelve datos con este indicador = 0, el operador de “Consulta Remota” no será parte del plan de ejecución, ya que solo se devolverán datos que se encuentren en la infraestructura local.

Para pausar o reanudar la migración de datos en Azure, elija Estirar para una tabla en SQL Server Management Studio y elija Pausar para pausar la migración de datos o Reanudar para reanudar la migración de datos. También puede utilizar Transact-SQL para pausar o reanudar la migración de datos.

Cómo pausar la migración de datos

Para pausar la migración de datos, use el siguiente comando:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = PAUSED ) );
GO

Cómo reanudar/continuar/migración de datos remota

Para reanudar/reanudar/continuar la migración de datos, utilice el siguiente comando:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = OUTBOUND ) );
GO

Cómo deshabilitar la migración de datos para una tabla

Para deshabilitar la migración de datos y devolver los datos migrados a la nube a su infraestructura local (copiar datos remotos a la tabla de Azure a SQL Server genera costos de transferencia de datos), use el siguiente comando:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    SET ( REMOTE_DATA_ARCHIVE ( MIGRATION_STATE = INBOUND ) );
GO

Para deshabilitar la migración de datos y abandonar los datos migrados a la nube, use el siguiente comando:

USE AdventureWorks
GO

ALTER TABLE SalesOrderHeader2
    REMOTE_DATA_ARCHIVE = OFF_WITHOUT_DATA_RECOVERY (
        MIGRATION_STATE = PAUSED
    )
GO

Recuerde pausar la migración de datos en tablas individuales cuando desee solucionar problemas del servidor local o maximizar el ancho de banda de red disponible. Además, Deshabilitar Stretch Database para una tabla o base de datos no elimina el objeto remoto. Si desea eliminar la tabla remota o la base de datos remota, suéltela mediante el Portal de administración de Azure. Los objetos remotos siguen generando costos de Azure hasta que los elimina.

Cómo deshabilitar Stretch Database para una base de datos

Para deshabilitar Stretch Database para una base de datos, primero deberá deshabilitar Stretch para todas las tablas individuales que estén habilitadas en esa base de datos. Utilice la vista sys.remote_data_archive_tables para descubrir cuáles son estas tablas.

Después de deshabilitar Stretch para todas las tablas, use el siguiente comando para deshabilitar Stretch para la base de datos:

ALTER DATABASE AdventureWorks
    SET REMOTE_DATA_ARCHIVE = OFF
GO

recuerda que Deshabilitar Stretch Database para una base de datos no elimina la base de datos remota. Si desea eliminar la base de datos remota, suéltela mediante el Portal de administración de Azure. La base de datos remota continúa generando costos de Azure hasta que la elimina.

Una forma sencilla de monitorear el progreso de la migración de datos en Stretch Database es usar SQL Server Management Studio (SSMS), haciendo clic en la base de datos deseada, haciendo clic derecho, seleccionando la opción Tareas > Stretch > Monitor, como se muestra en la imagen a continuación:

De esta manera, se abrirá un reporte donde podrás ver el detalle de la cantidad de líneas elegibles para migrar y cuántas ya han sido migradas, así como las cargas (lotes de hasta 9,999 registros) realizadas:

Una forma de monitorear el progreso de la migración de datos usando Transact-SQL (T-SQL) es consultando la vista sys.dm_db_rda_migration_status, que devolverá información para cada lote de carga:

Para ver información sobre qué bases de datos tienen habilitada la base de datos Stretch, consulte la vista sys.remote_data_archive_databases

o la vista sys.remote_data_archive_tables para consultar las tablas que tiene la Base de Datos Stretch:

Si activas Stretch Database en tu entorno, es común tener dudas sobre las rutinas de respaldo/restauración de estas bases de datos/tablas que tienen datos locales y también en la nube. Bueno, en cuanto a la copia de seguridad, el proceso no cambia nada.

Copia de seguridad de la base de datos con Stretch Database habilitado

El respaldo de datos locales (datos “hot”), es decir, que aún no son elegibles para migración y están almacenados en su infraestructura local, se continúa realizando normalmente, usando comandos BACKUP DATABASE y automatizados a través de trabajos, junto con las bases/tablas que son 100% locales y de la misma manera que respalda las bases/tablas que no tienen Stretch Database, incluidos los datos que son elegibles pero aún no han sido migrados al momento de generarse el respaldo.

Los datos que ya han sido migrados y están en la nube de Microsoft son parte de la rutina de respaldo automático de Azure, realizado cada 8 horas (al menos) en las tablas Staging y con retención de datos de 7 días (recuperación de un punto en el tiempo), es decir, Azure ya respalda los datos “fríos” por usted.

Restaurar bases con Stretch Database habilitado

Cuando hablamos de restaurar datos de bancos/tablas que tienen Stretch Database habilitado, la situación es un poco diferente. Si los datos que deben restaurarse son datos no elegibles, es decir, datos que se encuentran en su infraestructura local (datos "calientes"), restaurará los datos de la forma tradicional, utilizando el comando RESTORE DATABASE, de la misma manera que restaura datos de bases de datos sin Stretch activado.

Después de completar el proceso de restauración, deberá ejecutar el siguiente comando para restablecer la conexión entre la base y la base remota en Azure:

USE [AdventureWorks]
GO

EXEC sys.sp_rda_reauthorize_db
    @credential = N'MeuStretchDatabase',
    @with_copy = 1;
GO

Nota: Puede buscar el nombre de la credencial en la vista sys.database_scoped_credentials.

Para restaurar datos que están en Azure, si el problema fue con los datos que ya se migraron, deberá realizar la restauración de la base de datos en el Portal de Azure. Si desea conectarse a una base de datos de Azure restaurada con un nombre diferente o en una región diferente, puede usar SP sys.sp_rda_deauthorize_db (requiere permisos db_owner) para eliminar la conexión autenticada entre una base de datos local habilitada para Stretch y la base de datos remota de Azure, lo que también es muy útil cuando el servidor remoto es inconsistente o no está disponible.

Después de ejecutar sp_rda_deauthorize_db, todas las consultas realizadas en tablas y bases de datos habilitadas para Stretch fallarán porque el modo de consulta está configurado en DISABLED. Para volver a utilizar estas consultas, debe elegir una de las 2 opciones siguientes:

• Utilice el sys.sp_rda_reauthorize_db para volver a conectarse a la base de datos remota de Azure. Esta operación restablece automáticamente el modo de consulta a LOCAL_AND_REMOTE, que es el comportamiento predeterminado para Stretch Database. Es decir, las consultas devuelven resultados de datos locales y remotos.
• Ejecute el sp_rda_set_query_mode con el argumento LOCAL_ONLY para permitir que las consultas continúen ejecutándose solo con datos locales, ignorando los datos remotos.

Recuperar una base de datos dinámica de Azure

El servicio SQL Server Stretch Database en Azure toma instantáneas de todos los datos dinámicos al menos cada 8 horas mediante Azure Storage Snapshots. Estas instantáneas se conservan durante 7 días. Esto le permite restaurar datos en al menos uno de los 21 puntos específicos desde los últimos 7 días hasta el momento en que se tomó la última instantánea.

Para restaurar una base de datos dinámica de Azure a un momento específico a través de Azure Portal, siga el procedimiento a continuación:

• Inicie sesión en el portal de Azure.
• En el lado izquierdo de la pantalla, seleccione “BUSCAR” y “Bases de datos SQL”.
• Navegue hasta la base de datos y selecciónela.
• En la parte superior de la hoja de la base de datos, haga clic en "Restaurar".
• Especifique un nuevo nombre de base de datos, seleccione un "Punto de restauración" y haga clic en "Crear".
• El proceso de restauración de la base de datos comenzará y podrá ser monitoreado con “NOTIFICACIONES”.

Recuperar una base de datos de Azure eliminada

El servicio SQL Server Stretch Database en Azure toma una instantánea de la base de datos antes de eliminarla y la conserva durante 7 días. Después de eso, ya no conserva las instantáneas de la base de datos. Esto le permite restaurar una base de datos eliminada hasta el punto en el que se eliminó.

Para restaurar una base de datos de Azure eliminada al punto en que se eliminó mediante Azure Portal, siga estos procedimientos:

• Inicie sesión en el portal de Azure.
• En el lado izquierdo de la pantalla, seleccione “BUSCAR” y “Servidores SQL”.
• Busque el servidor y selecciónelo.
• Desplácese hacia abajo hasta "Operaciones" en el servidor y haga clic en el mosaico "Bases de datos eliminadas".
• Seleccione la base de datos eliminada que desea restaurar.
• Especifique un nuevo nombre de base de datos y haga clic en "Crear".
• El proceso de restauración de la base de datos comenzará y podrá ser monitoreado con “NOTIFICACIONES”.

Limitaciones de las tablas habilitadas para Stretch

• La unicidad no se aplica a las restricciones UNIQUE y PRIMARY KEY en una tabla de Azure que contiene los datos migrados.
• No puede ACTUALIZAR ni ELIMINAR filas que se han migrado o filas que son elegibles para la migración en una tabla habilitada para Stretch o en una vista que incluye tablas habilitadas para Stretch.
• No puede INSERTAR filas en una tabla habilitada para Stretch en un servidor vinculado.
• No puede crear un índice para una vista que incluya tablas habilitadas para Stretch.
• Los filtros de índices no se propagan a la tabla remota.

Limitaciones que actualmente le impiden habilitar Stretch para una tabla

• Tablas que tienen más de 1023 columnas o más de 998 índices
• FileTables o tablas que contienen datos de FILESTREAM
• Tablas que se replican o que utilizan activamente el seguimiento de cambios o la captura de datos de cambios (CDC)
• Tablas optimizadas para memoria (OLTP en memoria)
• Tipos de datos de texto, ntext e imagen, marca de tiempo, sql_variant, XML, tipos de datos CLR, incluidos tipos de geometría, geografía, jerarquía y CLR definidos por el usuario.
• Columnas calculadas
• Restricciones predeterminadas y restricciones de verificación
• Restricciones de clave externa que hacen referencia a la tabla. En una relación padre-hijo (por ejemplo, Order y Order_Detail), puede habilitar Stretch para la tabla secundaria (Order_Detail) pero no para la tabla principal (Order).
• Índices de texto completo, XML o espaciales
• Vistas indexadas que hacen referencia a la tabla.

Cuando comencé a escribir este artículo sobre Stretch Database, no investigué mucho sobre el precio de este recurso, ya que en mi opinión, solo me cobrarían por los datos transferidos y almacenados en Azure. Cuando utiliza el Asistente SSMS, incluso le muestra una estimación de costos para su uso actual de Stretch Database:

Como puedes ver en la imagen, el coste estimado es de 61 dólares por Terabyte, al mes. Sin embargo, esta estimación es sólo para los datos transferidos. Cuando crea un servidor Stretch Database, también se le cobra por ese servidor y, contrariamente a esta estimación, no es nada barato. Me di cuenta de esto cuando recibí un correo electrónico, 4 días después de mis pruebas de Stretch Database, diciendo que ya había consumido mis 1.000 reales de crédito en Azure (aunque todavía no había usado nada).

Después de este susto (risas) terminé investigando más precio de este recurso, que se encuentra a continuación para que esté al tanto del costo de este servicio: