Backups y restauraciones en SQL Server (I): Almacenamiento en disco

Vamos a comenzar una serie de artículos destinados a comprender los entresijos básicos de SQL Server. Considero que es crítico comprender cómo almacena SQL Server los datos y los escribe en disco, para comprender los requisitos de cualquier técnica de backup de SQL Server. Tipos de archivo utilizados:

• Ficheros de datos primarios: cada BB.DD. tiene un único fichero de datos primario con la extensión por defecto .MDF. El fichero de datos primario es el único que tiene no sólo la información contenida en la BB.DD. sino también la información sobre la propia BB.DD. Cuando la BB.DD. es creada la ubicación de cada archivo se almacena en la BB.DD. master, pero también se incluye en el fichero de datos primario.
• Fichero de datos secundarios: una BB.DD. también puede tener uno o varios ficheros de datos secundarios que tienen por defecto la extensión .NDF. Generalmente se utilizan para crear espacio de almacenamiento en unidades de disco distintas a la del fichero de datos primario, o para mantener el tamaño de cada fichero de datos individual en un tamaño máximo práctico, por lo general, por cuestiones de portabilidad.
• Registro de transacciones: una BB.DD. debe tener al menos un fichero de registro de transacciones con una extensión por defecto .LDF. El registro o log es imprescindible pues sin un registro de transacciones accesible, no se podría realizar ningún cambio en la BB.DD. Para la mayoría de las BB.DD. en producción es crítico un sistema de copia de seguridad adecuado para el registro de transacciones. Cuando se realiza un cambio en la BB.DD. se hace en el contexto de una transacción: una o más unidades de trabajo que deben tener éxito o fallar en su conjunto.

Cuando se realiza un cambio en la BB.DD. los datos no se escriben directamente en los archivos de datos. En su lugar, el servidor de BB.DD. primero escribe el cambio en el registro de transacciones. Cuando se complete la transacción, se marcará como confirmada en el registro de transacciones. Esto no significa que los datos se muevan desde el registro de transacciones al fichero de datos. Una transacción confirmada sólo significa que todos los elementos de la transacción se han completado satisfactoriamente. El caché de buffer se puede actualizar, pero no necesariamente el fichero de datos. Periódicamente se realiza un punto de control. Esto le indica a SQL Server que se asegure de que todas las transacciones realizadas son escritas en el fichero de datos apropiado.
Los puntos de control pueden realizarse por estos motivos:

• Alguien lanza un comando Checkpoint.
• Se alcanza el intervalo de recuperación configurado en la instancia (ver imagen). Esto indica con qué frecuencia se producen los puntos de control.
• Se realiza una copia de seguridad de la BBDD (en modo de recuperación simple).
• La estructura de archivos de BB.DD. se ve alterada (en modo de recuperación simple).
• El motor de la BB.DD. se ha detenido.

El proceso de escritura de datos a disco se realiza de forma automática, como se indica en el siguiente esquema:

No se escribe directamente a un fichero .mdf o .ndf. Todos los datos primero van al registro de transacciones (.ldf), siguiendo los pasos siguientes:

1. Se ejecuta algún tipo de comando INSERT, UPDATE o DELETE.
2. Los datos se escriben inmediatamente a la caché de log interna.
3. El caché de log actualiza el fichero de log físico (.ldf) y realiza cambios en el buffer caché de datos.
4. El buffer caché de datos se limpia eventualmente y el fichero de datos (.mdf o .ndf) se actualiza.

Cuando se realiza un backup de la BBDD, sólo los datos actuales y los ficheros del registro de transacciones se escriben a disco. Las transacciones realizadas que aún no se han escrito a disco permanecerán en el log de transacciones y se actualizan durante el proceso de recuperación mientras la BBDD se vuelve a conectar. Las transacciones incompletas se revierten durante la recuperación.

En el siguiente artículo veremos los modos de recuperación de SQL Server.