SQL Server tiene muchas características excelentes, pero a veces no funcionan bien juntas. Un caso de esto es con la Encriptación Transparente de Datos (TDE) y la Compresión de Copias de Seguridad. Sin embargo, en SQL Server 2016, Microsoft anunció que pueden funcionar juntas, y en este artículo, analizaremos cómo usar TDE y la Compresión de Copias de Seguridad en SQL Server 2016.

Encriptación Transparente de Datos (TDE)

TDE encripta los datos en reposo, lo que significa que realiza encriptación y desencriptación en tiempo real de los datos de la base de datos, el registro y los archivos de copia de seguridad de SQL Server. Utiliza una clave simétrica asegurada por un certificado almacenado en la base de datos del sistema maestro, conocida como la Clave de Encriptación de la Base de Datos (DEK).

Compresión de Copias de Seguridad

La compresión de copias de seguridad de la base de datos es importante porque te permite ahorrar espacio en disco generando un archivo de copia de seguridad más pequeño que el tamaño de la base de datos. También ahorra tiempo requerido para hacer copias de seguridad y restaurar la base de datos.

Habilitar la Compresión de Copias de Seguridad para Bases de Datos Habilitadas con TDE

Antes de SQL Server 2016, la compresión de copias de seguridad no estaba disponible para bases de datos habilitadas con TDE. Sin embargo, a partir de SQL Server 2016, ahora puedes obtener los beneficios de la compresión de copias de seguridad para una base de datos habilitada con TDE.

Para verificar si TDE está habilitado en una base de datos, puedes consultar la vista del sistema sys.certificates para verificar el certificado utilizado para encriptar la base de datos:

SELECT name, pvt_key_encryption_type_desc, issuer_name, expiry_date, start_date
FROM sys.certificates

También puedes consultar la vista sys.dm_database_encryption_keys para listar todas las bases de datos con TDE habilitado:

SELECT db_name(database_id) as DATABASE_Name
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3

Para habilitar la compresión de copias de seguridad para una base de datos habilitada con TDE, necesitas establecer el parámetro de copia de seguridad MAXTRANSFERSIZE a un valor mayor que el valor predeterminado de 65536. MAXTRANSFERSIZE especifica la unidad más grande de transferencia de datos en bytes entre SQL Server y el medio de copia de seguridad, con valores posibles iguales a múltiplos de 65536 bytes (64 KB) y un valor máximo igual a 4MB.

Aquí tienes un ejemplo de cómo hacer una copia de seguridad completa para una base de datos habilitada con TDE dos veces, una vez sin compresión y otra vez con compresión habilitada:

BACKUP DATABASE [AdventureWorksDW2012_TDE]
TO DISK = N'D:\backupSQL\AdventureWorksDW2012_TDE_NotCompressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW2012_TDE-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, NO_COMPRESSION, STATS = 10
GO

BACKUP DATABASE [AdventureWorksDW2012_TDE]
TO DISK = N'D:\backupSQL\AdventureWorksDW2012_TDE_Compressed.bak'
WITH NOFORMAT, NOINIT, NAME = N'AdventureWorksDW2012_TDE-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO

Al explorar la ubicación de la copia de seguridad, podrás comparar el tamaño de los dos archivos, lo que mostrará que el archivo de copia de seguridad comprimido es significativamente más pequeño.

Es importante tener en cuenta que aunque puede haber ciclos de CPU adicionales consumidos para comprimir la copia de seguridad, Microsoft te permite hacer una copia de seguridad comprimida para una base de datos habilitada con TDE. Esto proporciona los beneficios de un archivo más pequeño y tiempos de copia de seguridad y restauración más rápidos.

En conclusión, SQL Server 2016 introdujo la capacidad de usar la compresión de copias de seguridad para bases de datos habilitadas con TDE. Esto te permite ahorrar espacio en disco y reducir los tiempos de copia de seguridad y restauración. Al establecer el parámetro de copia de seguridad MAXTRANSFERSIZE de manera adecuada, puedes optimizar el proceso de copia de seguridad para bases de datos habilitadas con TDE.