Usando instantáneas de bases de datos SQL Server para protegerse contra modificaciones accidentales de datos

Introducción

¿Cuán a menudo usted ha deseado simplemente poder deshacer rápidamente una sentencia DML sin tener que pasar por el largo proceso de restaurar su copia de seguridad de la base de datos?

Desde SQL Server 2005, SQL Server le permite crear instantáneas de la base de datos las cuales pueden ser un real salvavidas cuando su DBA principiante ejecutó su última actualización de sentencias sin añadir la cláusula WHERE.

En este artículo le voy a contar todo acerca de las instantáneas de bases de datos y cómo pueden ser usadas para ayudar a proteger su base de datos de ejecuciones de sentencias DML no deseadas.

También le mostraré cómo puede fácilmente deshacer sentencias accidentales en el caso de que no cuente con una instantánea de base de datos apropiada a mano.

Qué es una instantánea de base de datos

Las instantáneas de bases de datos son una característica exclusiva de la edición Enterprise que hizo su debut en SQL Server 2005.

Una instantánea de base de datos es una vista de cómo se veía la base de datos fuente en el momento en que la instantánea fue creada. Esto significa que todos los objetos serán los mismos que cuando la instantánea fue tomada y todos los datos serán exactamente como eran entonces.

Para usar instantáneas de bases de datos para recuperarse de una sentencia DML no deseada, usted necesita tener una instantánea adecuada.

Las instantáneas pueden ser creadas sólo usando una sentencia T-SQL. Aquí está un ejemplo de cómo crear una instantánea de base de datos.

CREATE DATABASE AdventureWorks2012_SS14Sep2013 ON 
(NAME = [AdventureWorks2012_Data],
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\
MSSQL\DATA\AdventureWorks2012_Data_SS_14Sep2013.ss') 
AS SNAPSHOT OF AdventureWorks2012

Para poder crear una instantánea de base de datos, usted necesitará permisos de CREATE DATABASE así como db_owner en la base de datos de la que quiere sacar una instantánea.

Cómo funciona una instantánea

Hay mucha información disponible acerca del funcionamiento interno de una instantánea de SQL Server, así que déjeme darle algunos datos:

1. Cuando usted crea una instantánea, un archivo disperso es creado para cada archivo de datos.
2. Cuando los datos son modificados en la base de datos fuente por primera vez, el valor antiguo de los datos modificados es copiado a los archivos dispersos.
3. Si los mismos datos son subsecuentemente cambiados de nuevo, esos cambios serán ignorados y no serán copiados a la instantánea.
4. Cuando usted consulta una instantánea, primero verifica si los datos están disponibles en la instantánea. Si están ahí, los lee desde la instantánea. Si no están ahí, los lee a través de la base de datos fuente y obtiene los datos de ahí, porque eso significa que los datos no han sido cambiados desde el momento en que se tomó la instantánea.

Archivos dispersos

Cuando una instantánea de base de datos es creada, un archivo disperso es añadido para cada archivo de base de datos de la cual se tomó la instantánea. Un archivo disperso es básicamente un archivo vacío. No contiene ningún dato hasta que un cambio es hecho a la base de datos fuente.

Si observa un archivo en el Explorador de Windows, parece tener el mismo tamaño que el archivo de datos real.

Pero si usted luego ve las propiedades del archivo, usted puede ver que el tamaño real es sólo 2.87MB. NTFS asignará el espacio de disco requerido gradualmente a medida que se vuelve requerido.

Algunos datos interesantes acerca de los archivos dispersos:

• El máximo tamaño al que puede crecer un archivo disperso es el tamaño del archivo original en el momento de la creación de base de datos.
• Los archivos dispersos están limitados a 16 GB en Windows 2008 y 64 GB en Windows 2003.
• Los archivos dispersos crecen en incrementos de 64 KB.
• Usted puede revisar si un archivo es un archivo disperso ejecutando el siguiente código:

SELECT name,is_sparse FROM sys.database_files

Condiciones

Hay algunas condiciones para crear instantáneas de bases de datos, aquí hay algunas cosas a tener en cuenta. Esta lista de ninguna manera es exhaustiva.

1. Las instantáneas de bases de datos dependen de la base de datos fuente. Sólo pueden ser creadas en el mismo servidor donde reside la base de datos (o el servidor al cual la base de datos es reflejada).
2. Mientras que haya instantáneas presentes, usted no puede eliminar la base de datos o añadir ningún archivo nuevo a ella.
3. Una vez que una instantánea de base de datos se convierte en sospechosa, no puede ser salvada. Sólo tiene que ser eliminada. Esto puede pasar si la instantánea se queda sin espacio o alcanza el límite máximo de tamaño del archivo disperso.
4. Usted no puede crear índices en instantáneas, son de lectura solamente de manera estricta.
5. Los permisos de usuario son exactamente los mismos que en la base de datos fuente. Usted no puede otorgar acceso a un usuario a la instantánea. Usted tiene que garantizar el acceso en la base de datos fuente y luego tomar otra instantánea primero antes de que el usuario pueda acceder a ella.

Cuál es el propósito de una instantánea de base de datos

Estoy seguro de que hay docenas de razones por las que usted podría querer usar una instantánea de base de datos, pero aquí están las 3 más importantes:

Leer desde su reflejo de base de datos

Una de las principales razones por la que usted desearía una instantánea es que pueda ganar acceso de lectura a su base de datos reflejo. Esto le permitirá descargar el procesamiento ETL para su almacén de datos desde su servidor de producción.

Protegerse de errores del usuario

La manera más rápida de recuperarse de una sentencia DML accidental es seleccionar los datos modificados de la instantánea de vuelta a su base de datos.

Facilita reinicios fáciles para pruebas o ambientes de entrenamiento

Cuando usted tiene un caso donde cada usuario debe ver la base de datos como era en un cierto punto del tiempo, usando el mismo servidor, las instantáneas de bases de datos son definitivamente la manera de lograrlo.

Por ejemplo, usted tiene una clase de entrenamiento para instruir a los usuarios acerca de cómo configurar una aplicación. Para clase de entrenamiento usted quiere que la configuración sea limpiada de la base de datos. En lugar de restaurar una base de datos entera para cada clase, usted puede simplemente volver a la instantánea tomada antes de que la clase comenzara.

Para volver a una instantánea, el siguiente script puede ser usado.

USE master
GO
ALTER DATABASE MYTEST SET SINGLE_USER  WITH ROLLBACK IMMEDIATE 
GO
RESTORE DATABASE MYTEST FROM DATABASE_SNAPSHOT = 'MYTEST_SS040620131300';
GO
ALTER DATABASE MYTEST SET MULTI_USER
GO

Tenga en mente que cuando usted vuelve a una instantánea, usted tiene que eliminar todas las otras instantáneas en esa base de datos. Si usted no elimina todas las otras instantáneas obtendrá el siguiente error:

Msg 3137, Level 16, State 4, Line 2
Database cannot be reverted. Either the primary or the snapshot names are improperly specified, all other snapshots have not been dropped, or there are missing files.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Cuál es el impacto en el desempeño

Las malas noticias son que las instantáneas de bases de datos sí tienen un impacto en el desempeño, por favor vea las buenas prácticas ofrecidas en la siguiente sección para ayudarle a minimizar el impacto en el desempeño.

Creando y manteniendo índices

El tiempo que toma crear un índice es mucho más largo cuando hay instantáneas en la base de datos. Mientras más instantáneas estén presentes en la base de datos fuente, más tiempo toma.

Incluso si los datos en sí mismo no cambian, las páginas aún son movidas. Dado que las instantáneas operan a nivel de página, las páginas movidas son copiadas a la instantánea.

Impacto de crear instantáneas en la base de datos reflejo

Dependiendo de la configuración del reflejo de la base de datos, tener muchas instantáneas de su base de datos reflejo puede también impactar en el desempeño en su servidor de producción.

Leer el desempeño de las instantáneas

El desempeño de las instantáneas en sí mismas puede ser bastante pobre. Si usted desea hacer algo al respecto, como crear un índice, usted tiene que crear el índice en la base de datos fuente y luego crear una instantánea para cosechar los beneficios.

Cuáles son las mejores prácticas

Cuando se planea usar instantáneas de bases de datos como parte de su estrategia de protección de datos, hay un par de cosas a tener en mente:

Nombre las instantáneas lógicamente

Si ocurriera una sentencia DML accidental, sería útil saber simplemente de un vistazo al nombre de la instantánea de qué instantánea recuperar los datos.

Como tal, es altamente recomendado incluir lo siguiente en el nombre de su instantánea:

• El nombre de la base de datos fuente
• La fecha en que fue tomada
• La hora en que fue tomada

No ponga sus archivos de instantáneas en los mismos discos que los archivos de datos

Para evitar la contención de disco y archivos sería mejor desde una perspectiva de desempeño poner los archivos de instantáneas en un disco diferente que el de los archivos de la base de datos fuente.

Limitar el número de instantáneas

Las instantáneas de bases de datos sí tienen un impacto en el desempeño. Así que trate de limitar el número de instantáneas en su base de datos. Las instantáneas pueden también crecer bastante, así que tener menos disminuirá su riesgo de quedarse sin espacio en el disco. Dado que usted puede tener múltiples instantáneas al mismo tiempo, y cada archivo puede crecer hasta el tamaño del archivo original de la base de datos, esto es realmente algo a lo que prestar atención.

No conserve las instantáneas por mucho tiempo

Como mencioné anteriormente, los archivos dispersos tienen un límite de tamaño. Cuando uno de los archivos de instantáneas de la instantánea de la base de datos alcanza el tamaño de archivo máximo, la instantánea ya no estará disponible y dará errores como:

The operating system returned error 1450(Insufficient system resources exist to complete the requested service.) to SQL Server during a write at offset 0x000031abb4e000 in file with handle 0x00000F74. This is usually a temporary condition and the SQL Server will keep retrying the operation. If the condition persists then immediate action must be taken to correct it

O:

The operating system returned error 665(The requested operation could not be completed due to a file system limitation) to SQL Server during a write at offset 0x000005bd3dc000 in file ‘MYTEST.mdf:MSSQL_DBCC8’

Para evitar este tipo de error, intente mantener sus archivos de datos debajo del límite de los archivos dispersos añadiendo más archivos. De esta manera este error puede ser evitado completamente.

Eliminar instantáneas antes de hacer mantenimiento de índices

Dado que las instantáneas afectan el tiempo que toma realizar un mantenimiento de índices, se recomienda eliminar las instantáneas antes de hacer el mantenimiento de índice o crear índices, de ser posible.

Cómo recuperarse de una sentencia DML no deseada cuando usted no tiene una instantánea adecuada

Bien, así que usted no tiene una instantánea reciente, no tiene tiempo para restaurar una copia de seguridad completa. Usted realmente sólo quiere encontrar y retrotraer un estúpido error que justamente sucedió, e idealmente antes de que los problemas se repliquen en otro lugar.

Aquí es donde ApexSQL Log entra en escena. ApexSQL Log es una herramienta de lectura de registros de transacciones de SQL Server, la cual puede ayudarle a recuperarse de sentencias DML no deseadas rápida y fácilmente leyendo los datos en su registro de transacciones en línea o respaldado, y creando un script deshacer para revertir el error.

Incluso puede identificar múltiples sentencias en una transacción y crear un script deshacer para todos ellos. Lo cual es algo que podría tomar mucho tiempo descifrar si usted tuviera que revertir el error manualmente.

Para propósitos de esta explicación usaré un ejemplo muy simplista.

Un usuario reporta que la cantidad de un artículo en uno de los almacenes es incorrecto. Él no sabe cómo pasó dado que él no puede ver ninguna venta en el sistema, no está seguro qué cantidad estaba antes de que cambiara, pero sabe que esta cantidad era diferente ayer y quiere cambiarla de vuelta.

Ahora, si usted tenía una instantánea de la base de datos la cual fue tomada ayer, usted podría revisar cuál era la cantidad y sólo cambiarla, pero ya que no tiene una, esto es lo que usted puede hacer.

• Use ApexSQL Log y abra una sesión a la base de datos en cuestión:

  

• Seleccione las casillas para incluir el registro en línea y todas las copias de seguridad hechas en las últimas 24 horas.

  

• Seleccione Open results in grid.

  

• Filtre en las últimas 24 horas. Dado que el usuario sabe que el valor estaba correcto aún ayer.

  

• Busque en los resultados hasta encontrar la actualización en cuestión.

  

  • Como puede ver, el valor ha cambiado de 100 a 1000.
  • Pero si usted mira más de cerca, verá que habían otras transacciones con el mismo sello de tiempo. Así que, obviamente, quien quiera que haya hecho el cambio hizo algo más en la misma transacción. Esto es algo que usted no podría determinar fácilmente sólo viendo una instantánea de la base de datos.

• Haga clic en la actualización y seleccione la operación “Select transaction” en el menú principal.

  

  • Esto remarcará todas las filas que fueron parte de esa transacción.

• Luego haga clic en el botón “Undo”.

  

  • Esto creará un script para deshacer todas las sentencias que fueron usadas como parte de esa transacción.

    

En conclusión

Microsoft ha dado grandes pasos en simplificar la manera en que los DBAs protegen sus datos. Con una estrategia apropiada y un poco de suerte, los DBAs pueden recuperarse de sentencias DML no deseadas sin restaurar una copia de seguridad.

Si usted tiene una instantánea reciente, usted podrá encontrar cuál era el valor original y revertir de vuelta a ese valor. Desafortunadamente, tener una instantánea no puede decirle qué otras acciones fueron realizadas en la misma transacción o quién lo hizo, a diferencia de ApexSQL Log. Así que incluso cuando las instantáneas de bases de datos son realmente útiles, yo aún recomendaría mantener un buen lector de registros para los momentos en que s estrategia le falle.

Recursos útiles:
Database Snapshots (SQL Server)
View the Size of the Sparse File of a Database Snapshot (Transact-SQL)
View a Database Snapshot (SQL Server)

Traductor: Daniel Calbimonte