Memorias de un DBA

Un blog dedicado a contribuir a la comunidad SQL Server en español

Recovery Models

leave a comment »

Los modelos de recuperación de SQL Server son 3, Full, Bulk-Logged y Simple. Pero cuál es la diferencia y beneficios de cada uno de estos modelos de recuperación. Pues en este articulo exploraremos a mas detalle la diferencia entre ellos, lo cual radica en el manejo que le dan al log de transacciones de la base de datos y en el nivel de recuperación de información en un caso de desastre.

Modelo de Recuperación Simple: En caso de desastre en el servidor de base de datos, este modelo solo nos permitiría recuperar la información de la base de datos, solo hasta el momento en el cual se tomo el ultimo backup full de la base de datos. Es decir que si todos los días a media noche se hace un backup full de la base de datos, se está asumiendo que el negocio puede perder el equivalente de 1 día de información en la base de datos, en el peor de los casos si el desastre tiene lugar a las 23:59 se habrá perdido el día entero en información. Normalmente este modelo lo usan bases de datos pequeñas y medianas debido a que el backup full de la base de datos no toma mucho tiempo en realizarse.

Pero bajo la perspectiva de este modelo de recuperación no todo es malo, la ventaja principal que ofrece este modelo de recuperación es la facilidad en la administración que ofrece debido a que no se necesitan hacer backups de log de transacciones de la base de datos para que el log de transacciones no crezca sin control, es más bajo este modelo de recuperación no está permitido hacer este tipo de backups. Cuando la base de datos se encuentra en este modelo de recuperación el log de transacciones se comporta de manera cíclica por defecto, lo cual permite mantener controlado el tamaño del log de transacciones. Pero como es que el log de transacciones se mantiene pequeño, y es que cada vez que dentro de la base de datos se ejecuta el proceso de “checkpoint”, el log de transacciones es truncado, es decir, que las entradas activas que se tienen dentro del mismo son marcadas como inactivas, lo cual permite sobrescribirlas de una manera cíclica. Esto lo podemos comprobar muy fácilmente con el siguiente ejemplo:

USE master
GO
-- Elimino la base de datos en el caso que ya se encuentre creada
IF (DATABASEPROPERTY('DBSimple','Version') > 0)
DROP DATABASE DBSimple
GO
-- Creo la base de datos desde cero y con los valores por defecto
CREATE DATABASE DBSimple
GO
-- Modifico el modelo de recuperación de la base de datos a simple
-- debido a que el modelo de recuperacion por defecto que tengo 
-- configurado es FULL
ALTER DATABASE DBSimple SET RECOVERY SIMPLE
GO
-- Cambio el contexto de la base de datos
USE DBSimple
GO
-- Creare una tabla simple e insertare algunos registros
CREATE TABLE dbo.TablaPrueba (Codigo int)
GO
INSERT INTO dbo.TablaPrueba VALUES (1),(2),(3),(4),(5)
GO
-- Ahora consultare las entradas activas del log de transacciones con la 
-- siguiente sentencia
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:0000001b:0001  LOP_BEGIN_XACT                  LCX_NULL
00000017:0000001b:0002  LOP_MODIFY_ROW                  LCX_BOOT_PAGE
...
00000017:0000003c:0014  LOP_SET_FREE_SPACE              LCX_PFS
00000017:0000003c:0015  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0016  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0017  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0018  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0019  LOP_COMMIT_XACT                 LCX_NULL
*/

-- Ahora forzare el proceso de checkpoint en la base de datos
CHECKPOINT
GO
-- Finalmente verificaremos que el log de transacciones de la base de datos
-- ha sido truncado y las entradas anteriores fueron marcadas inactivas
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(null,null)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:00000041:008a  LOP_BEGIN_CKPT                  LCX_NULL
00000017:00000079:0001  LOP_END_CKPT                    LCX_NULL
*/

Modelo de Recuperación Full: En caso de desastre en el servidor de base de datos, este modelo nos permitiría recuperar la información de la base de datos hasta la última transacción confirmada en la base de datos. Claro que para poder lograr esto se debe poder tener acceso al log de transacciones para poder hacer un backup de tipo “Tail Log”, en el caso que no se tenga acceso al log de transacciones de la base de datos, la información podrá ser recuperada solo hasta el último backup de log o de transacciones, cualquiera que fuera el más reciente. Es importante resaltar que bajo este modelo de recuperación se pueden plantear diversas estrategias de backups para bases de datos medianas,  grandes y muy grandes, las estrategias que se pueden plantear bajo este modelo de recuperación pueden combinar los backups full, diferencial y de log para poder proteger la información guardada dentro de la base de datos.

La administración de la base de datos bajo este modelo de recuperación ya es un poco más compleja debido a que se tiene que tener un correcto manejo y administración de los backups de la base de datos, en especial de los backups del log. Si no se sacan los backups de log de la base de datos, este archivo crecerá sin control y no podrá darse su comportamiento cíclico natural. Debido a que la premisa bajo este modelo es poder recuperar hasta la última transacción confirmada en la base de datos, esta necesita tener una copia de seguridad de todos los registros que se tienen dentro del log de transacciones, para de esta manera ir aplicándolos uno por uno secuencialmente y así recuperar toda la información.

Entonces después de lo establecido en el párrafo anterior se puede inferir que el log de transacciones de la base de datos marca como inactivos los registros de las transacciones, solo si estos han sido descargados en algún backup de log, si no se ha sacado ningún backup de log este jamás podrá marcar como inactivos sus registros y por ende tendrá que crecer, lo cual como ya vimos en el post anterior es algo malo para la performance de base de datos. A continuación veremos un ejemplo que graficara el comportamiento del log bajo este modelo de recuperación:

USE master
GO
-- Elimino la base de datos en el caso que ya se encuentre creada
IF (DATABASEPROPERTY('DBFull','Version') > 0)
DROP DATABASE DBFull
GO
-- Creo la base de datos desde cero y con los valores por defecto
CREATE DATABASE DBFull
GO
-- Modifico el modelo de recuperación de la base de datos a full
-- solo para asegurarme que el modelo esta configurado a FULL
ALTER DATABASE DBFull SET RECOVERY FULL
GO
-- realizare un primer backup full de la base de datos
BACKUP DATABASE DBFull TO DISK = N'D:\Backups\DBFull_Full.bak' WITH INIT, STATS
GO
-- Cambio el contexto de la base de datos
USE DBFull
GO
-- Creare una tabla simple e insertare algunos registros
CREATE TABLE dbo.TablaPrueba (Codigo int)
GO
INSERT INTO dbo.TablaPrueba VALUES (1),(2),(3),(4),(5)
GO
-- Ahora consultare las entradas activas del log de transacciones con la    
-- siguiente sentencia
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:0000001b:0001  LOP_BEGIN_XACT                  LCX_NULL
00000017:0000001b:0002  LOP_MODIFY_ROW                  LCX_BOOT_PAGE
...
00000017:0000003c:0014  LOP_SET_FREE_SPACE              LCX_PFS
00000017:0000003c:0015  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0016  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0017  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0018  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0019  LOP_COMMIT_XACT                 LCX_NULL
*/

-- Ahora realizare un backup full de la base de datos
BACKUP DATABASE DBFull TO DISK = N'D:\Backups\DBFull_Full2.bak' WITH INIT, STATS
GO
-- Ahora volvemos a consultar las entradas activas del log de transacciones    
-- como vemos los registros del log aun siguien activos
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:0000001b:0001  LOP_BEGIN_XACT                  LCX_NULL
00000017:0000001b:0002  LOP_MODIFY_ROW                  LCX_BOOT_PAGE
...
00000017:0000003c:0014  LOP_SET_FREE_SPACE              LCX_PFS
00000017:0000003c:0015  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0016  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0017  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0018  LOP_INSERT_ROWS                 LCX_HEAP
00000017:0000003c:0019  LOP_COMMIT_XACT                 LCX_NULL
*/

-- Ahora haremos un checkpoint en la base de datos
CHECKPOINT
GO
-- Volvemos a consultar las entradas activas del log de transacciones    
-- como vemos el comportamiento de este modelo de recuperacion no es igual que el Simple
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(NULL,NULL)
GO
-- Ahora realizare un backup del log de la base de datos
BACKUP LOG DBFull TO DISK = N'D:\Backups\DBFull_Log.bak' WITH INIT,STATS=10
GO
-- Finalmente verificaremos que luego del backup del log de transacciones,
-- este ha sido truncado y las entradas anteriores fueron marcadas inactivas
SELECT [Current LSN],[Operation],[Context] FROM ::fn_dblog(null,null)
GO
/*
Current LSN             Operation                       Context
----------------------- ------------------------------- -------------------------------
00000017:00000041:008a  LOP_BEGIN_CKPT                  LCX_NULL
00000017:00000079:0001  LOP_END_CKPT                    LCX_NULL
*/

Modelo de Recuperación Bulk-Logged: Este modelo de recuperación es muy parecido en su funcionamiento y comportamiento al modelo “Full”, pero la gran diferencia con el “Full” es que bajo este modelo, el log de transacciones no graba toda la información de las operaciones Bulk, solo la asignación de páginas y extends, evitando de esta manera que el log de transacciones crezca en el caso que se haga una operación bulk que consuma muchos recursos de la base de datos.

Este modelo de recuperación se creó para que cuando se tiene definida ya una estrategia de backups en el modelo Full, se pueda cambiar al modelo “Bulk-Logged” y viceversa sin afectar la cadena de backups, en el caso que se vaya a realizar una operación “Bulk” bastante intensa lo cual causaría que el log de transacciones crezca de una manera descontrolada. Un ejemplo de una operación “Bulk” bastante intensa puede ser la reconstrucción de todos los índices de la base de datos, una carga de datos a un datawarehouse.

Ya que hemos mencionado al cadena de backups es importante conocer que cuando se tiene una base de datos en el modelo “Full” y se cambia al modelo “Simple”, la cadena de backups se pierde, es decir que cuando se regrese al modelo “Full” y se intente sacar un backup de log, este fallara y la única alternativa será sacar nuevamente un backup “Full” de la base de datos y reiniciar la cadena de backups. En el futuro se escribirá un post detallando un poco mas este tema.

En conclusión es importante conocer el comportamiento de cada uno de los modelos de recuperación de base de datos, debido a que cada uno de ellos nos ofrecen diversas ventajas, y conociendo su funcionamiento podremos sacar provecho de todas las capacidades que nos ofrece cada uno de ellos. Bueno espero que les haya servido este post y próximamente vendrán más.

Written by dbamemories

julio 5, 2011 a 11:15 am

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: