El respaldo y la recuperación son algunas de las tareas de DBA más importantes, aunque se ven suficientemente simples y usualmente usted los configura, los deja corriendo en un itinerario y sólo vuelve si fallan – hay un nuevo mundo acerca de la optimización que usted puede hacer para hacerlos más rápidos, mejores y … más pequeños.

¿Pero por qué molestarse? Considerando que las bases de datos modernas crecen a un ritmo tan rápido, usted puede que se enfrente a una situación donde no puede cumplir con sus ventanas de mantenimiento o las obligaciones del contrato de servicio. Veamos las tres áreas principales donde podemos trabajar:

Parte uno, optimizar la base de datos para operaciones de respaldo y recuperación más rápidas, parte dos, optimizar el proceso exacto de respaldo de la base de datos y parte tres, optimizar la copia de seguridad en sí misma.

Antes de sumergirnos en esto, veamos el ambiente de laboratorio con el que estaremos trabajando hoy – Tenemos un par de máquinas Azure coriendo en MS SQL Server 2014 SP1 corriendo en Windows 2012 R2. Los datos de la base de datos y los archivos de registros están propagados en diferentes discos, tempdb tiene sus propis discos. Adicionalmente tenemos un dispositivo dedicado al respaldo.

La base de datos que será usada para las pruebas es una AdventureWorks2008R2 agrandada* hasta 17GB (solamente).

Parte uno, Optimizar la base de datos

Al momento tenemos un archivo de base de datos, un archivo de registros y un archivo de copia de seguridad. Las operaciones estándar de respaldo van a suceder en sólo 36 minutos, respaldando hasta 1823434 páginas en un archivo de copia de seguridad de 14GB.

Para optimizar la base de datos es importante que usted esté familiarizado con los objetos en ella. Con la base de datos AdventureWorks revisaré los dos objetos más grandes Sales.SalesOrderDetailEnlarged y Sales.SalesOrderHeaderEnlarged y mostraré las posibles reducciones de tamaño si aplico la compresión de filas o páginas.

Saque una muestra de los posibles ahorros usando el procedimiento almacenado “sp_estimate_data_compression_savings”:

Los resultados de muestra representan una porción de los datos dentro del objeto comprimido por SQL Server – tome en cuenta que la compresión final puede tener diferentes ratios.

A juzgar por los resultados, tendremos mejores usando la compresión PAGE. Usted puede aplicar la compresión por índice o toda la tabla como sigue:

Los resultados de la compresión:

Comparando la duración de la operación de respaldo:

Processed 1110760 pages for database ‘AdventureWorks2008R2’, file ‘AdventureWorks2008R2_Data’ on file 1.
Processed 2 pages for database ‘AdventureWorks2008R2’, file ‘AdventureWorks2008R2_Log’ on file 1.
BACKUP DATABASE successfully processed 1110762 pages in 1653.560 seconds (5.247 MB/sec).

La operación de respaldo se completó en 27 minutos, 8 minutos más rápido y el archivo de copia de seguridad es 5.5GB más pequeño.

Podemos ir incluso más allá y dividir la base de datos en dos partes, esto nos ayudará doblando las operaciones de lectura para el proceso de respaldo. Para lograr esto crearemos un nuevo archivo de base de datos dentro de un nuevo grupo de archivos llamado SECONDARY, después de eso moveremos aproximadamente 50% de los objetos de la base de datos en términos de tamaño aquí.

Creando el nuevo grupo de archivos:

Creando un nuevo archivo J:\AdventureWorks2008R2_Data2.ndf en el grupo de archivos SECONDARY.

Reconstruyendo el Índice Agrupado de la tabla más grande especificando el grupo de archivos exacto para moverlo al nuevo archivo:

La operación de respaldo está leyendo desde dos archivos de datos como usted puede ver en la salida:

Beneficiándonos de las dos Fuentes hemos logrado ahorrar 10 minutos más de la duración de la operación de respaldo.

Usted puede considerar la marca de seguimiento SQL –T1117**cuando esté usando múltiples archivos de datos – eso causará que todos los archivos en un grupo de archivos crezcan automáticamente juntos de acuerdo a su incremento de auto crecimiento especificado. Note que esto afecta a todas las bases de datos en el servidor en que lo habilitó.

Parte dos, Optimizando el proceso de respaldo

Ahora que tenemos dos archivos de datos hay dos corrientes IO leyendo los datos durante el proceso de respaldo, pero una sola escribiendo los datos en el contenedor de la copia de seguridad. Para mejorar más el proceso podemos respaldar la base de datos en dos archivos de respaldo. Los datos serán divididos entre los dos contenedores y usted necesitará ambos durante las operaciones de recuperación.

Usar dos corrientes de escritura nos ahorró algo más de tiempo, pero sea cauto, ya que dividir los contenedores de respaldo en múltiples archivos complica un poco las operaciones de recuperación.

Ahora ajustemos algunos de los parámetros que están disponibles al comando BACKUP – MAXTRANSFERSIZE y BUFFERCOUNT.

MAXTRANSFERSIZE especifica la unidad de transferencia usada por SQL Server para realizar las copias de seguridad. El valor por defecto es 1024MB – los posibles valores son múltiplos de 65536 bytes (64KB) hasta 4MB.

BUFFERCOUNT determina el número de búferes IO usados por las operaciones de respaldo. Los valores para eso son dinámicamente calculados por el Servidor MSSQL; de todas maneras, no siempre son óptimos. De todos modos, sea cauto, ya que valores altos pueden conducir a errores ‘out of memory’.

Los dos parámetros trabajan juntos – BUFFERCOUNT determina con cuántos búferes IO usted estará trabajando y MAXTRANSFERSIZE establece cuán llenos estarán estos búferes. Búferes IO = BUFFERCOUNT * MAXTRANSFERSIZE.

Usted puede habilitar marcas de seguimiento para ver información adicional acerca de sus copias de seguridad y ver el BUFFERCOUNT calculado por SQL Server:
3605 – Eso envía el resultado al registro de errores; y 3213 – El cual provee información acerca del rendimiento del respaldo o recuperación y otras configuraciones.

Para ajustar MAXTRANSFERSIZE podemos hacer lo siguiente:

Y estos son los resultados promedio usando cada valor posible:

El valor por defecto que SQL Server calculó para mi copia de seguridad era 14, descubrí que el óptimo está cerca de XYZ. Para ajustar BUFFERCOUNT podemos hacer lo siguiente:

Y de nuevo, estos son los resultados usando diferentes valores:

Parte tres, Optimizar la copia de seguridad

Hay dos posibilidades para nosotros aquí – podemos incrementar la velocidad del respaldo un poco más y reducir el tamaño de la copia de seguridad.

El último parámetro para el comando BACKUP es BLOCKSIZE, que especifica el tamaño del bloque físico, en bytes. Los tamaños soportados son desde 512 bytes a 65536 (64KB) bytes. El valor por defecto para contenedores de copias de seguridad en discos es 512 bytes y 64KB para dispositivos de cintas. El comando es como sigue:

Abajo están las pruebas usando todos los valores posibles:

Para obtener el último incremento posible en términos de velocidad y tamaño usaremos compresión de copias de seguridad. Ahora está disponible en la edición Standard así como en SQL 2008R2. Usa una compresión de tipo zip y el tamaño de la copia de seguridad es reducido durante su creación – lo que significa que en la mayoría de los escenarios el respaldo no será sólo más pequeño, sino que más rápido (considerando que usted no está almacenando sólo jpegs y usa encripción). Esta es la forma más fácil y transparente de optimizar las operaciones de respaldo. Podemos crear una copia de seguridad SQL comprimida como sigue:

Usted también puede alterar los ajustes del Servidor de modo que todas las copias de seguridad sean creadas con compresión – pero asegúrese de verificar si todos sus SQL Servers soportan copias de seguridad comprimidas.

La operación de respaldo se completó en ~230 segundos, ~90% más rápido que la original con la que empezamos; el tamaño es 2.5GB, 83% más pequeña que la original. Y adivinó – las operaciones de recuperación también serán más rápidas.

Referencias

BACKUP – Microsoft KB
* Enlarging the AdventureWorks database
** Trace flag –T1117

• Acerca de
• Últimas entradas

Kaloyan Kosev

Kaloyan Kosev is a SQL Server expert working with enterprise companies for the last 8 years. His daily work routine is a mixture of troubleshooting bugs and issues, consulting and advising clients and presenting to students. 

View all posts by Kaloyan Kosev

Últimas entradas de Kaloyan Kosev (ver todo)

• Optimización de copias de seguridad en MS SQL Server - October 2, 2016