Con el lanzamiento de Microsoft SQL Server 2016, se introdujeron muchas características nuevas, incluyendo las tablas temporales. Las tablas temporales te permiten ver el estado de tus datos en un momento dado, dándote la capacidad de retroceder en el tiempo con tus datos. Sin embargo, un desafío al utilizar tablas temporales es el aumento potencial en el consumo de espacio de almacenamiento.

En este artículo, exploraremos cómo minimizar el espacio utilizado por las tablas de historial en SQL Server 2016 aprovechando la función de Bases de Datos Estiradas en conjunto con las tablas temporales.

Comprendiendo los beneficios y desafíos

Las tablas temporales ofrecen varios beneficios, como auditoría de datos, dimensiones cambiantes lentamente en un almacén de datos y detección de anomalías. Sin embargo, registrar cada evento en tus datos puede consumir rápidamente una cantidad significativa de espacio. Para abordar este desafío, podemos utilizar la función de Bases de Datos Estiradas en SQL Server 2016.

Configurando tablas temporales y Bases de Datos Estiradas

Primero, creemos una tabla temporal simple con algunos datos de muestra:

USE Master;
GO

DROP DATABASE IF EXISTS TemporalDemo
CREATE DATABASE TemporalDemo;
GO

USE TemporalDemo;
GO

CREATE TABLE Author (
    Id INT IDENTITY(1, 2) PRIMARY KEY NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL,
    StartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    EndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (StartTime, EndTime)
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.Author_History));

GO

SET IDENTITY_INSERT [dbo].[Author] ON

INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (1, N'Stefanie', N'Regina')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (3, N'Sandy', N'Roy')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (5, N'Lee', N'Dewayne')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (7, N'Regina', N'Beth')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (9, N'Daniel', N'Jolene')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (11, N'Dennis', N'Nicolas')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (13, N'Myra', N'Tricia')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (15, N'Teddy', N'Hilary')
INSERT [dbo].[Author] ([Id], [Name], [Surname]) VALUES (17, N'Annie', N'Shane')

SET IDENTITY_INSERT [dbo].[Author] OFF
GO

Una vez que hayamos creado la tabla temporal e insertado algunos datos de prueba, podemos actualizar los registros para generar historial:

UPDATE Author
SET Surname = CONCAT(Surname, '_', LEFT(Name, 1))
WHERE Name LIKE 'T%'

SELECT * FROM Author FOR SYSTEM_TIME ALL WHERE Name LIKE 'T%'
SELECT * FROM Author_History

Ahora que tenemos datos de historial en nuestra tabla de historial, podemos proceder a habilitar el archivo estirado:

sp_configure 'remote data archive', 1
GO
RECONFIGURE
GO

Antes de habilitar el estiramiento, asegúrate de agregar una regla de firewall a tu servidor SQL de Azure para permitir conexiones desde tu IP actual. A continuación, configura la base de datos para conectarse a Azure para la funcionalidad de estiramiento:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SuperSecurePassword'
CREATE DATABASE SCOPED CREDENTIAL remoteArchive WITH IDENTITY = 'AzureSqlServerUsername', SECRET = 'AzureSqlServerSupersecurepassword'
GO

ALTER DATABASE TemporalDemo SET REMOTE_DATA_ARCHIVE = ON (SERVER = '*********.database.windows.net', CREDENTIAL = remoteArchive);
GO

Finalmente, habilita el archivo remoto para la tabla de historial:

ALTER TABLE Author_History
SET (REMOTE_DATA_ARCHIVE = ON (MIGRATION_STATE = OUTBOUND))

Siguiendo estos pasos, hemos configurado correctamente nuestra tabla de historial para estirarse a Azure. Puedes verificar esto consultando la tabla de historial y revisando el plan de consulta, así como observando la creación de una nueva base de datos en Azure.

Consideraciones y reflexiones finales

Aunque utilizar Bases de Datos Estiradas con tablas temporales puede ayudar a minimizar el consumo de espacio, es importante considerar el impacto potencial en el rendimiento de las consultas. Recuperar datos de las tablas de historial puede ser más lento debido a la necesidad de obtener datos del archivo de datos remoto. Si se requiere un acceso frecuente a los datos de historial, se deben considerar soluciones alternativas como agregar un índice de columnas agrupadas en las tablas de historial.

Además, si el archivado es el objetivo principal y no se necesitan los beneficios de las tablas temporales, utilizar el almacenamiento de Azure BLOB y crear tablas externas al almacenamiento de BLOB puede ser una opción viable.

En conclusión, al aprovechar la función de Bases de Datos Estiradas en SQL Server 2016, podemos maximizar la eficiencia del espacio al utilizar tablas temporales. Sin embargo, es importante considerar cuidadosamente los compromisos y elegir la solución más adecuada según tus requisitos específicos.