FUNDAMENTALS: Copias de seguridad y registro de transacciones

RUBÉN GARRIGÓS

REL-310

Mentor –Área Motor Relacional

MCP – MCAD – MCSD – MCTS – MCT - MCITP

rgarrigos@solidq.com

α Modelos de recuperación en SQL Server

α Log de transacciones

α Optimización de backups β Backups parciales

β Compresión de backups

Objetivos de la sesión

Modelos de recuperación

α Los tres modelos aparecieron en SQL Server 2000 β Select into/bulkcopy

β Truncate Log on Checkpoint

α Al elegir un modelo u otro tenemos que evaluar el grado de protección, el espacio requerido para el log y el rendimiento esperado de las operaciones masivas.

Modelos de recuperación Introducción

Modelo Select Into /

BulkCopy

Truncate Log on

Checkpoint

Full Falso Falso

Bulk-Logged Verdadero Falso

Simple - Verdadero

α Modelo por defecto para las bases de datos nuevas

α Nos permitirá la recuperación en caso de desastre hasta un punto exacto en el tiempo

α Mayor protección en caso de fallo de un fichero de datos

α Todas las operaciones se registrarán de forma completa en el log de transacciones

α Requiere de backups del log de transacciones

α El dimensionado del log es crucial para evitar problemas de rendimiento

α Ideal cuando perder la menor cantidad de información es lo más importante

β Si con una baja frecuencia de backups no es suficiente…

β Database Mirroring, Replicación hardware síncrona/asíncrona de cabina, replicación software tipo Double-take, etc.

Modelos de recuperación Modelo de recuperación completo

α Modelo intermedio entre el modelo completo y el simple

α Se utiliza en escenarios en los cuales puntualmente tenemos operaciones masivas que consideramos «demasiado grandes» para ser registradas completamente

α Suele utilizarse solo durante una ventana de tiempo β Mantenimiento

β Cargas masivas

β Sin recuperación a un punto en el tiempo dentro de la ventana

β El backup del log incluirá todas los extents modificados

α Para que una operación de un BULK INSERT sea efectivamente procesada como mínimamente registrada deben cumplirse varias condiciones…

Modelos de recuperación Modelo de recuperación BULK LOGGED

α SQL 2008 R2

Modelos de recuperación Modelo de recuperación BULK LOGGED

Tipo de tabla ¿Vacía? Índices non-

clustered Hints Concurrencia

de cargas Mínimamente

registrada

Heap No No TABLOCK Si Si

Heap No No - Si No

Heap No Si TABLOCK No Parcialmente

Índice cluster Si No TABLOCK + ORDER No Si

Índice cluster Si No - Parcial Si

Índice cluster No No - Parcial Si

Índice cluster No No TABLOCK No Si

Índice cluster No Si - Parcial Parcialmente

Índice cluster No Si TABLOCK No Parcialmente

α Es un modelo donde la gestión del log de transacciones se realiza automáticamente por SQL Server

α Permite operaciones mínimamente registradas al igual que el BULK LOGGED

β No conviene pasar de full a simple para evitar invalidar la cadena del log Un backup full/diff para inicializarla de nuevo

α Se busca un compromiso entre evitar una pérdida de datos grande y penalizar el rendimiento de la base de datos

α En caso de desastre solo podremos recuperarnos con backups completos y diferenciales

β Puede ser válido para algunos entornos de producción (DW)

α Es una buena opción para bases de datos de entornos de desarrollo y testing

Modelos de recuperación Modelo de recuperación simple

Log de transacciones

α Es un fichero que almacena las transacciones realizadas en una base de datos

β Consistencia en caso de fallo, protocolo WAL

β Soporte a la replicación transaccional, Log shipping, Database Mirroring…

α Objetivo de mucha controversia y «quejas» β Mi SQL Server «escribe demasiado» en el log

β ¡Anda! ¿Hay que hacer backups del log de transacciones?

β Mi log de transacciones ocupa ya 900 GB. ¡Socorro!

β No puedo truncar el log de transacciones, mejor borro el fichero…

β ¿Que importancia puede tener que exista una transacción abierta desde hace 2 meses?

β Este rollback está tardando mucho, mejor reinicio el servicio…

Log de transacciones Introducción

α En SQL Server 6.5 β Se creaba sobre un «dump device»

β Se realizaba un «dump» del log a otro device como forma de liberar espacio

α En SQL Server 7.0 – 2000 β Modelo similar al actual con backups para reutilizar el log

α En SQL Server 2000+ β Introducción de mejoras de rendimiento

γ 2005+ No utilizar el log para las pseudo-tablas en los triggers

γ 2005+ Menos impacto de la fragmentación de VLF

γ 2008+ Más operaciones mínimamente registradas

Log de transacciones Historia

α Se utiliza principalmente como forma de garantizar la durabilidad de las transacciones

α La «anotación» de las transacciones en el log se persiste a disco garantizándose el orden de las escrituras

β Cuidado con el almacenamiento ATA/SATA

α Cuando realizamos modificaciones en nuestra base de datos se realizan sobre las páginas en memoria y no sobre disco

α Los procesos checkpoint, lazy writer y eager write serán los que vuelquen a disco esas páginas modificadas.

Log de transacciones Protocolo WAL (Write Ahead Logging)

α Pruebas de stress para determinar la carga que debemos soportar y cuanta esperas vienen del log

β No fiarse de las especificaciones teóricas

β Considerar todo el path de entrada/salida

β Cuidado con los «vendemotos» de almacenamiento SAN

α Factores a considerar β Baja latencia de escrituras

β Rendimiento predecible y estable

β Cacheo de escrituras

β RAID 1/10 vs RAID 5/6

β Spindles dedicados

Log de transacciones Rendimiento

α Desde el punto de vista de rendimiento, no debemos esperar milagros si el rendimiento de nuestro log era ya bueno…

α SELECT INTO 1 ~1.2 millones de filas

α SELECT INTO 1 ~12 millones de filas

Log de transacciones Modelo de recuperación BULK LOGGED

Operación Modelo

Recuperación IOs Log SSD Magnético

SELECT INTO 1 Simple 45 2.68 MB 5 s 5 s

SELECT INTO 1 Completo 4182 236 MB 5 s 6 s

SELECT INTO 2 Simple 525 31 MB 56 s 53 s

SELECT INTO 2 Completo 41925 2687 MB 62 s 96 s

Log de transacciones & FULL vs SIMPLE

Optimización de backups

α La importancia de una buena política de backups (y restores) es por todos conocida

β Si no puedes restaurar no tienes backup

α Normalmente se busca la mayor frecuencia posible de backups para minimizar el tiempo de recuperación ante un desastre

α Tenemos que evaluar diversos factores β Espacio ocupado

β Tiempo necesario

β Capacidad de entrada/salida

β Disponibilidad parcial de la base de datos

β Particionado

β Datos de solo lectura

Optimización de backups Introducción

α Nos permiten realizar backups de parte de la base de datos y restauraciones parciales posteriormente

α Especialmente útil para datos históricos o de solo lectura β Hacemos backup una vez.. y ya no más veces

β No es raro tener ratios 1:10 entre datos modificables e históricos

α Mejoramos el espacio ocupado si necesitamos mantener backups almacenados durante tiempo

α El tiempo para los backups «diarios» se reduce

α No presionamos tanto el sistema de entrada/salida durante los backups

β Sistemas 24x7

β Sistemas en tiempo real

β Un backup no «bloquea» a los usuarios pero sí puede ralentizar

Optimización de backups Backups parciales

Optimización de backups

CREATE DATABASE test_conta

GO

USE [master]

GO

ALTER DATABASE test_conta ADD FILEGROUP ro_data

GO

ALTER DATABASE test_conta ADD FILE ( NAME = N'ro_data',

FILENAME = N'c:\ro_data.ndf' , SIZE = 3072KB , FILEGROWTH

= 1024KB )

GO

ALTER DATABASE test_conta MODIFY FILEGROUP ro_data

READONLY

GO

BACKUP DATABASE [test_conta] FILEGROUP = N'PRIMARY'

TO DISK = N'C:\test_conta.bak'

Backups parciales

Optimización de backups

BACKUP DATABASE [test_conta] FILEGROUP = N'ro_data'

TO DISK = N'C:\test_conta_ro_data.bak‘

GO

RESTORE DATABASE [test_conta2]

FILE = N'test_conta' FROM DISK = N'C:\test_conta.bak'

WITH FILE = 1,

MOVE N'test_conta' TO N'C:\test_conta2.mdf',

MOVE N'test_conta_log' TO N'C:\test_conta2_1.LDF',

PARTIAL

GO

RESTORE DATABASE [test_conta2]

FILE = N'ro_data'

FROM DISK = N'C:\test_conta_ro_data.bak' WITH FILE = 1,

MOVE N'ro_data' TO N'c:\test_conta2_0.ndf'

Backups parciales

α A lo largo de los años han proliferado multitud de herramientas de terceros

β Dependencia de dichas herramientas para algo tan vital como las copias de seguridad y las restauraciones

α Otras alternativas β Cintas con soporte de compresión nativo

β Compresión NTFS

α Desde SQL 2008 tenemos una solución nativa de compresión que busca un compromiso entre distintos factores

β Consumo de CPU

β Ratio de compresión

β Duración del backup

α Es sencilla de utilizar pero ofrece poca configurabilidad

Optimización de backups Compresión de backups

α En SQL Server 2008 apareció también la compresión de fila y de página

β Ayuda a disminuir la cantidad de entrada/salida necesaria a disco

β Aumenta la eficiencia de la memoria: más datos en menos páginas

β Aumenta el consumo de CPU ligeramente

α Con arquitecturas modernas se recomienda partir de 4GB de RAM por core para cargas generalistas

β El uso de compresión puede ayudar a equilibrar el sistema

β Podemos obtener ventajas analizando las tablas más compresibles y con menos cambios y habilitarla solo cuando sea más favorable

α La compresión NTFS no está soportada salvo para ficheros de solo lectura

Optimización de backups Compresión en SQL Server

Optimización de backups Ratios de compresión

Método de compresión Tamaño (bytes)

Reducción de tamaño

Backup no comprimido 470.897.152 0,00% Backup no comprimido + compresión NTFS 175.181.824 -62,80%

Backup comprimido SQL Server 2008 86.846.976 -81,56%

Backup comprimido + ZIP compresión normal 85.970.567 -81,74%

Backup comprimido + ZIP máxima compresión 85.896.047 -81,76%

Backup comprimido + RAR máxima compresión 85.685.920 -81,80%

Backup comprimido + RAR compresión normal 85.688.085 -81,80%

Backup no comprimido + ZIP compresión normal 63.851.585 -86,44%

Backup no comprimido + ZIP máxima compresión 59.704.340 -87,32%

Backup no comprimido + RAR compresión normal 37.185.294 -92,10%

Backup no comprimido + RAR máxima compresión 36.867.249 -92,17%

α Desde SQL Server 2008 disponemos de una característica de encriptado transparente (TDE)

α Todas las páginas de la base de datos pasan a estar encriptadas

β El compresor se encuentra con un conjunto aleatorio de bytes

Optimización de backups Encriptación y compresión

Encriptación TDE Compresión Backup Rendimiento Tamaño (bytes) Duración

No No 65,3 MB/s 223.442.432 3,2 s

No Si 144,2 MB/s 49.716.224 1,5 s

Si No 68 MB/s 223.442.432 3,1 s

Si Si 42,7 MB/s 221.069.824 4,9 s

α El tiempo total de compresión depende de muchos factores

α Existe un tiempo independiente del método de compresión utilizado requerido para la lectura de los datos

α Existe un tiempo asociado a la escritura del backup

α La disminución del volumen de datos a escribir suele compensar al consumo de CPU extra

β Las CPUs son mucho más rápidas que la entrada/salida a disco

β Es más notable cuanto menor es el ancho de banda del canal (cinta lenta, a través de la red, etc.)

Optimización de backups Rendimiento de la compresión

Método de copia de seguridad Duración Diferencia Estándar 35.355 s 0% Sobre volumen NTFS comprimido 64.130 s 81% Nativa SQL Server 2008 12.920 s -64%

Backups comprimidos/encriptados

Filegroups solo lectura y disponibilidad parcial

Si quieres disfrutar de las mejores sesiones de

nuestros mentores de España y Latino América,

ésta es tu oportunidad.

http://summit.solidq.com/madrid/