Instantáneas de base de datos
Las instantáneas de base de datos son una nueva característica incluida en SQL Server 2008. Una instantánea es una vista estática de sólo lectura de nuestra base de datos en un punto concreto en el tiempo. Es decir, es como una foto del estado de la base de datos en un momento determinado.
Las instantáneas de base de datos trabajan a nivel de página de datos. Esto significa que cuando una página de datos cambia, la original es copiada a la instantánea. Este mecanismo recibe el nombre de “Copia al escribir”, e implica que al crear la instantánea, esta se refiere a las páginas de datos de la base de datos. A medida que la base de datos vaya cambiando, los nuevos datos se irán almacenando en las páginas de datos de la base de datos. Antes de insertar un nuevo dato en una de estas páginas, el motor de SQL Server copiará la página original al espacio dedicado a la instantánea. El resultado de este comportamiento es que la instantánea irá aumentando su tamaño a medida que la base de datos vaya cambiando.
Las instantáneas de base de datos no se pueden modificar una vez han sido creadas. En lugar de hacer eso, debemos crear una nueva instantánea cada vez que queramos volver a guardar el estado de la base de datos.
Cuando creamos una instantánea, SQL Server genera un número de registro de secuencia, que es almacenado en la base de datos que actúa como origen de la instantánea. Inmediatamente después, SQL Server efectúa la recuperación de la base de datos. Esta operación tiene como consecuencia que las transacciones no confirmadas son revertidas en la instantánea, nunca en la base de datos. Después se genera un archivo para almacenar los datos de la instantánea. Este archivo trabaja con bloques de asignación de 64 KB.
Hemos de tener en cuenta que al crear una instantánea, la base de datos de origen tendrá transacciones abiertas. Además, una instantánea de base de datos es coherente en cuanto a las transacciones con su base de datos tal y como existía en el momento de creación de la instantánea, como ya he dicho. Esto implica que antes de que la instantánea esté disponible, el motor de base de datos revertirá las transacciones abiertas para que la instantánea pueda ser coherente con la base de datos origen en el momento de su creación.
Debido a que los clientes pueden consultar una instantánea como si se tratase de una base de datos normal, las instantáneas resultan útiles como origen de datos para la creación de informes sobre datos históricos. Debido a que la instantánea proporciona una vista estática de la base de datos, y los datos históricos no cambian, es decir, también son estáticos porque reflejan situaciones pasadas en el tiempo, podemos crear una instantánea de base de datos al final de un periodo para generar informes basados en los datos de la instantánea. Esta estrategia de creación de instantáneas se puede mantener en el tiempo cuanto se necesite, siempre que el espacio de almacenamiento disponible en disco lo permita.
También podemos crear instantáneas en servidores reflejados (tratados en el capítulo anterior). Esto permitiría que, mientras que el servidor espejo sigue replicando las transacciones que recibe el principal, sus datos puedan estar disponibles para que sean consultados o sean utilizados en los informes. Esto permitirá liberar carga de trabajo del servidor principal.
Otra utilidad de las instantáneas de base de datos puede ser la de proteger los datos contra operaciones o situaciones inesperadas. Antes de realizar actualizaciones masivas de datos, podemos crear una instantánea para salvaguardar el estado original de la base de datos. De este modo, si se produce un error, siempre podremos utilizar la instantánea para devolver a la base de datos a su estado anterior. Esta estrategia también nos puede servir para prevenirnos de otro tipo de situaciones, por ejemplo la eliminación accidental de una tabla, o de datos sensibles. En cualquier caso, las instantáneas no sustituyen a las copias de seguridad de la base de datos.
Creación de una instantánea
Las instantáneas son tratadas por SQL Server 2008 como bases de datos dentro del sistema. Por tanto, debemos asignarles un nombre único al crearlas, es decir, no nos sirve el mismo nombre de la base de datos. Sin embargo, el nombre no debe ser completamente diferente del de la base de datos original, ya que debe dar a entender que se trata de una instantánea. Normalmente, se suele utilizar el nombre de la base de datos original en combinación con algún otro dato, como por ejemplo la fecha de creación, o algún otro dato similar.
SQL Server Management Studio no admite la creación de instantáneas, por lo que solo podremos llevar a cabo esta tarea utilizando Transact-SQL. Para crear una instantánea, debemos utilizar la instrucción CREATE DATABASE, en combinación con la cláusula AS SNAPSHOT OF. Sólo podemos crear la instantánea a partir de los archivos de datos de la base de datos. No podemos incluir en la instrucción de creación de la instantánea archivos de registro de transacciones, archivos sin conexión o de restauración.
Para cada archivo de datos de la base de datos original, crearemos un archivo de instantánea. En ese archivo se almacenarán todas las páginas de datos de la instantánea que se correspondan con las tablas y otros objetos colocados en el archivo de datos original. La siguiente instrucción es un ejemplo de creación de una instantánea.
-- Instantáneas de base de datos. CREATE DATABASE Odyssey_Snapshot
ON (
Name = 'Odyssey_DATA', FILENAME = 'c:\Odyssey_Snapshot.ss' ), ( Name = 'Odyssey_INDEX', FILENAME = 'c:\Odyssey_Snapshot_IX.ss' ) AS SNAPSHOT OF Odyssey GO Código fuente 1
A pesar de estar utilizando la instrucción de creación de base de datos, no nos hacen falta más parámetros que el NAME y el FILENAME. En el parámetro NAME especificamos el nombre lógico del archivo de datos original, mientras que en el parámetro FILENAME especificamos la ruta del archivo de instantánea. La extensión recomendada para este archivo es .ss, aunque podemos utilizar cualquiera.
Una vez hayamos creado la instantánea de base de datos, los clientes del servidor la verán como una base de datos de solo lectura. Esto les permitirá consultar la instantánea como si fuera una base de datos normal aunque, obviamente, no podrán ejecutar ninguna instrucción que implique alguna modificación de datos u objetos.
Operaciones de mantenimiento
Las operaciones de mantenimiento que podemos realizar con las instantáneas son pocas. En general, podemos comprobar el espacio utilizado por los archivos dispersos de la instantánea, podemos revertir una base de datos a un estado anterior descrito por la instantánea, o también podemos eliminar una instantánea para crear otra.
Cálculo del tamaño de los archivos
Como hemos visto en la sección anterior, al crear una instantánea de base de datos, se crean unos archivos llamados dispersos donde se almacenan las páginas de datos de la instantánea. Los nombres de estos archivos los especificamos en la instrucción de creación de la instantánea, y podemos consultarlos en la columna physical_name de la vista de sistema sys.master_files. También podemos consultar los nombres de archivo de la base de datos o de la instantánea en la columna physical_name
de la vista de sistema sys.database_files.
Al crear un archivo disperso, este ocupa muy poco espacio en disco, ya que contiene muy pocos datos. A medida que escribimos datos en ese archivo, el sistema NTFS reserva espacio en disco para ese archivo de manera gradual. Estos archivos son una característica de NTFS.
Para calcular el tamaño real que utiliza un archivo disperso, podemos utilizar la función de sistema
fn_virtualfilestats. Esta función toma como parámetros el identificador de la base de datos y el identificador del archivo de base de datos, y devuelve el tamaño real expresado en bytes. Estos identificadores los podemos obtener con la función DB_ID y la vista sys.master_files respectivamente. -- Comprobación del tamaño de los archivos de todas las bases de datos.
SELECT * from fn_virtualfilestats(null, null)
-- Comprobación de los archivos de la base de datos AdventureWorks. SELECT * from fn_virtualfilestats(DB_ID('AdventureWorks'), null)
-- Comprobación del archivo de datos de la base de datos AdventureWorks.
SELECT * FROM fn_virtualfilestats(DB_ID('AdventureWorks'), (SELECT file_id FROM SYS.MASTER_FILES WHERE name = 'AdventureWorks_Data'))
GO
Código fuente 2
Para calcular el tamaño máximo de un archivo disperso, podemos utilizar la columna size de la vista de sistema sys.master_files. Esta columna nos devuelve el número máximo de páginas que puede utilizar un archivo disperso de instantánea. Si lo multiplicamos por el tamaño máximo de una página, 8kb, obtendremos el dato que buscamos.
-- Cálculo del tamaño máximo de un archivo.
SELECT (size * 8192) AS 'TamañoMaximo' FROM SYS.MASTER_FILES WHERE name = 'AdventureWorks_Data'
Código fuente 3
Revertir el estado de una base de datos
Ya he comentado las ventajas que podemos obtener al crear una instantánea de base de datos, y una de ellas es la de revertir el estado de la base de datos al de la instantánea.
La reversión sobrescribe las actualizaciones realizadas sobre la base de datos de origen desde la creación de la instantánea, ya que copia las páginas de la instantánea a los archivos de la base de datos. Es decir, este proceso sólo sobrescribe las páginas actualizadas. Además, la operación de reversión sobrescribe el archivo de registro de transacciones, sustituyendo el último por el de la instantánea. Debemos tener en cuenta que al revertir una base de datos a su instantánea, se perderán todos los catálogos de texto.
Para realizar la tarea de reversión de la base de datos a una instantánea, primero debemos eliminar todas las instantáneas que estén asociadas a la base de datos, excepto la que utilizaremos para la reversión. A continuación, utilizaremos la instrucción RESTORE DATABASE con la opción FROM DATABASE_SNAPSHOT.
-- Revertir la base de datos Odyssey.
RESTORE DATABASE Odyssey FROM DATABASE_SNAPSHOT = 'Odyssey_Snapshot' GO
Código fuente 4
No podemos utilizar las herramientas gráficas de Management Studio para revertir una instantánea. Si antes de revertir la base de datos hemos realizado una copia de seguridad del archivo de registro de transacciones, después de revertir la base de datos podemos restaurar esa copia de seguridad del registro para recuperar los datos que hemos perdido como consecuencia de la reversión. Para esto, antes de realizar la operación de restauración del archivo de copia, debemos configurar la base de datos con el modelo de recuperación completo.
Restricciones de una instantánea
Aunque las propiedades y beneficios que nos aportan las instantáneas son importantes, debemos tener en consideración el impacto que tienen las instantáneas sobre sus bases de datos de origen y sobre el sistema en general y las limitaciones que nos imponen.
Limitaciones de la instantánea
Las instantáneas de bases de datos nos imponen ciertas limitaciones sobre su uso. Por ejemplo, una instantánea de base de datos tiene que ser creada en el mismo servidor que contiene a su base de datos de origen. Esto limita mucho su ámbito de actuación, y nos impide utilizar esta tecnología para crear una estrategia de distribución de instantáneas.
Como ya he dicho, al crear la instantánea, el motor de base de datos revierte las transacciones no confirmadas en el momento de hacer la instantánea. Esto permite que, si sucede algún error en la base de datos origen justo después de crear la instantánea, no podamos recuperar todos los datos. Siempre habrá cierta latencia entre los datos de la base de datos y los datos de la instantánea en el momento de su creación.
Antes he comentado la estrategia de Copiar al escribir que implementa el motor de bases de datos para la gestión de las páginas de datos de las instantáneas. Si una página de datos es actualizada en la base de datos de origen, esto provocará que SQL Server inserte la página original en la instantánea. Si el archivo de la instantánea se queda sin espacio en disco, la instantánea pasa a ser sospechosa, y deberá ser eliminada. Es por esto que debemos tener mucho cuidado con el espacio disponible en disco para nuestra instantánea.
Debido a que las instantáneas son vistas estáticas de la base de datos, no nos permiten crear una réplica que pueda ser utilizada como alternativa para una estrategia de conmutación por error en caso de que la base de datos origen falle, ya que los usuarios no podrán modificar los datos de la instantánea.
Al estar enlazadas a la base de datos de origen, las instantáneas no permiten la creación o eliminación de un archivo de instantánea adicional. Tampoco permiten realizar las tareas de adjuntar o separar, que sí podemos realizar sobre una base de datos normal, y no podemos realizar una copia de seguridad o una restauración de una instantánea. Así mismo, no podemos crear una instantánea de base de datos en un sistema de archivos FAT32.
Las instantáneas de base de datos heredan la configuración de seguridad de la base de datos origen en el instante de creación de la instantánea. De este modo, los cambios de seguridad realizados sobre la base de datos origen no se propagan a la instantánea. Tampoco se nos permite modificar la configuración de seguridad de la instantánea de base de datos.
Las instantáneas de base de datos permiten, como ya he dicho, revertir la base de datos origen al estado en que se encontraba al crear la instantánea. Esta característica no es posible si la base de datos contiene un grupo de archivos de sólo lectura, o un grupo de archivos comprimido. Además, si la base de datos se encuentra en estado de RECOVERY_PENDING, no podremos tener acceso a la instantánea.
Restricciones sobre la base de datos origen
Las instantáneas de base de datos también imponen ciertas restricciones sobre la base de datos origen. Por ejemplo, la base de datos no se puede quitar, separar o restaurar mientras tenga una instantánea asociada a ella. Sin embargo, podremos realizar las copias de seguridad de la base de datos con toda normalidad. Esto se debe a que las instantáneas comparten las páginas de datos con la base de datos de origen.
La estrategia de copia al escribir reduce el rendimiento de la base de datos en las operaciones de modificación de datos, debido a la operación de copia de la página original al archivo de datos de la instantánea.
Si nuestra base de datos tiene grupos de archivos sin conexión, podemos crear igualmente una instantánea. Sin embargo, hemos de tener en cuenta que no se crean archivos dispersos para los grupos de archivos sin conexión. Si queremos poner en conexión un grupo de archivos de la base de datos origen, no tendremos ningún problema, pero el grupo de archivos seguirá sin conexión en la instantánea. El mismo comportamiento obtendremos si queremos poner fuera de conexión un grupo de archivos de la base de datos. SQL Server nos permitirá realizar esta opción, pero el grupo de archivos seguirá en conexión en la instantánea, aunque debemos tener en cuenta que es probable que se produzcan errores en las operaciones de entrada / salida al realizar una consulta sobre los datos de ese grupo de archivos de la instantánea.
Como he dicho antes, si un archivo de instantánea se queda sin espacio en disco, SQL Server la marca como sospechosa y deberemos eliminarla, aunque esto no afecta a la base de datos. Debemos, por tanto, tener muy en cuenta el espacio ocupado por nuestra instantánea. Cuanto más tiempo mantengamos una instantánea, más espacio ocupará esta. El tamaño máximo que puede alcanzar la instantánea es el que tenía la base de datos origen en el momento de crear la instantánea.
