Gráfico evolutivo del espacio en unidades de disco (II)

En este segundo artículo vamos a ver cómo presentar la información recogida en el artículo anterior. Vamos a hacer uso de Reporting Services de SQL Server, para crear de forma sencilla gráficas que representen la información almacenada y publicarlas en un entorno web.
A modo de recordatorio, en el primer capítulo creamos un script en powershell que generaba un fichero .txt en el que cada línea representaba una unidad de disco de un servidor, el espacio total de esta unidad, el utilizado y el libre. Este script era utilizado por una tarea programada, de forma programábamos su ejecución diaria. Por otra parte, creamos una base de datos con cuatro tablas y un procedimiento almacenado. El procedimiento almacenado también se ejecutaba a diario como un trabajo en el agente de SQL Server (aunque después de la ejecución script en powershell) de forma que utilizaba la información de contenida en el fichero txt de resultados y realizaba su inserción en la base de datos.
Una vez que disponemos de la información en nuestra BB.DD., el siguiente paso va a ser mostrarla. Como hemos dicho, emplearemos Reporting Services:

De tal manera que podremos, de forma sencilla, mostrar la información mediante gráficas en entorno web:

En primer lugar deberemos crear nuestro proyecto, para lo que utilizaremos la herramienta SQL Server Business Intelligence Development Studio (que no es una opción "marcada" por defecto en la instalación de SQL Server, por lo que deberemos comprobar si la tenemos instalada, o realizar su instalación).
Al abrir el SQL Server BI Development Studio, crearemos un nuevo proyecto de Report Server: menú "Archivo" à "Nuevo proyecto" y seleccionamos como tipo de proyecto "Business Intelligence Projects" y la plantilla "Report Server Project":

En el Explorador de Soluciones podremos agregar un nuevo elemento a nuestro proyecto

Que va a ser un "report"

El report que vamos a crear va a disponer de:

• dos textbox en los que introducir la fecha de la consulta (fecha de inicio/fecha final),
• dos listas desplegables (dropdownlist) enlazadas, una con la lista de servidores y otra con las unidades de disco que tiene cada uno,
• una gráfica, y
• una tabla dinámica que muestre los datos (espacio por fecha en el servidor y unidad de disco consultada).

Nuestro primer paso será crear las consultas SQL que emplearemos en nuestro proyecto.
La primera de las listas desplegables mostrará un listado de los servidores, cuyos datos como vimos en el primer artículo, se encuentran en la tabla TServidores. La consulta SQL será muy simple:
SELECT IdServidor, NServidor FROM TServidores

Para añadir esta consulta, debemos ir a la pestaña "Datos" y crearnos un nuevo "conjunto de datos", que en mi caso he denominado "Servidores":

La segunda lista desplegable mostrará, dependiendo del valor elegido en la lista de servidores, las unidades de disco "asociadas" a ese servidor. La consulta SQL hará un cruce entre las tablas de unidades de discos y las de servidores:

SELECT TServidores.NServidor, IdServer, NUnidad FROM TDiscosServ INNER JOIN TServidores ON TServidores.IdServidor=TDiscosServ.IdServer WHERE (IdServer = @IdentServ)

Este conjunto de datos lo he denominado "DiscosServ", y como vemos hacemos uso de la variable "IdentServ" que es un parámetro del informe, como veremos más adelante, que es simplemente el valor seleccionado en la otra lista desplegable:

Por último, deberemos definir la consulta SQL "principal", es decir, aquella que nos va a mostrar la evolución de la utilización del espacio en disco. Esta es:

SELECT NServ, NUnidad, TotalEspacio, EspacioUsado, EspacioLibre, FechReg FROM RegSpace WHERE (NServ = @NombreServidor) AND (NUnidad = @NombreDisco) AND (FechReg BETWEEN @FechaIni AND @FechaFin)

En esta consulta hacemos uso de cuatro variables, que definiremos como parámetros. Esta consulta es otro conjunto de datos, que he denominado "ServerFreeSpace":

Con esto ya hemos completado todos los elementos que vamos a utilizar en la pestaña "Datos" de nuestro proyecto. En el siguiente paso vamos a trabajar con la pestaña "Diseño", y vamos a insertar el gráfico y la tabla dinámica, además de crear los parámetros necesarios para pasar valores a las consultas que hemos visto anteriormente y las propias listas desplegables y los cuadros de texto para recoger las fechas.
Para crear un parámetro, pulsaremos con el botón secundario del ratón en el margen del informe como vemos en la siguiente captura:

Y al pulsar en "Parámetros del informe" nos aparecerá una ventana en la que podremos ir creando los parámetros que necesitemos. Hemos visto que necesitamos cinco para las consultas SQL: IdentServ, NombreServidor, NombreDisco, FechaIni, FechaFin. Los tres primeros serán parámetros "internos", es decir, simplemente para pasar datos:

Y los dos parámetros de fecha serán de tipo DateTime, de forma que automáticamente Reporting Services nos genera un textbox con un icono de calendario en el lado derecho de dicho cuadro:

Añadimos valores predeterminados, de forma que si el usuario no introduce fecha alguna, realizará una consulta desde el primer día en el que comenzamos a almacenar datos hasta la fecha actual.

Además vamos a definir otros dos parámetros que nos generarán las listas desplegables. Para ello utilizaremos como "Tipo de datos" un string, y especificaremos que los "valores disponibles" sean de consulta, especificando los conjuntos de datos creados con anterioridad:

Una vez que hemos definido nuestros parámetros y nuestros elementos para especificar la consulta, procedemos a insertar el gráfico:

Y la tabla dinámica:

Y por último, para dejar más bonito nuestro informe, podemos añadir en la cabecera del informe un cuadro de texto que muestre el nombre del servidor del que estamos mostrando los datos:

Este es un sencillo ejemplo en el que hemos combinado la utilización de Powershell y el uso de una herramienta como Reporting Services para obtener una aplicación web que nos permita ir conociendo la evolución del uso de los discos en nuestro servidores, ya sea para descubrir visualmente posibles descensos bruscos en la capacidad de los mismos, o para prever cuándo podemos empezar a tener problemas de espacio y buscar soluciones.

Gráfico evolutivo del espacio en unidades en disco (I)

En los siguientes artículos voy a tratar cómo crear un sencillo sistema que permita almacenar en una BB.DD. la evolución del espacio en disco en los distintos servidores existentes en nuestra red. La idea es hacer uso de powershell y SQL Server para crearnos este sistema. Aprovecharemos Reporting Services de SQL Server para mostrar gráficamente los resultados obtenidos.
En primer lugar necesitaremos un servidor de BB.DD. con un SQL Server instalado, preferiblemente con una versión 2005 o superior (aunque también es posible instalar Reporting Services en SQL Server 2000). Crearemos en una base de datos, que en mi caso he denominado ServerFreeSpace. Vamos a crear 4 tablas y un procedimiento almacenado:

El procedimiento almacenado, RegSpaceImport, nos servirá para coger un fichero de texto plano (un .txt) que va a contener un conjunto de líneas que recogen, cada una de ellas:

• el nombre del servidor,
• la unidad de disco,
• el espacio total de la unidad de disco,
• el espacio usado de esa unidad y
• el espacio libre.

Este fichero txt es el resultado de un script en powershell que veremos más adelante.
El código de este procedimiento almacenado es el siguiente:

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[sp_RegSpaceImport] @PathFileName varchar(100), @FechaHoy datetime AS --Paso 1: Construir una clausula BULK INSERT valida DECLARE @SQL varchar(2000) -- Formato valido (ejemplo): NombreServidor,C:,33.91,6.59,27.32 SET @SQL = "BULK INSERT TmpRegSpace FROM '"+@PathFileName+"' WITH (FIELDTERMINATOR = ',') " --Paso 2: Ejecutar la clausula BULK INSERT EXEC (@SQL) --Paso 3: INSERT datos al final de la tabla REGSPACE INSERT RegSpace (NServ,NUnidad,TotalEspacio,EspacioUsado,EspacioLibre,FechReg) SELECT NServ,NUnidad,TotalEspacio,EspacioUsado,EspacioLibre,@FechaHoy FROM TmpRegSpace --Paso 4: Vaciar la tabla temporal TRUNCATE TABLE TmpRegSpace

Este procedimiento almacenado deberemos utilizarlo en un trabajo del Agente de SQL Server, para que sea ejecutado a diario:

Lo que haremos en este trabajo es lanzar el procedimiento almacenado descrito, la ruta en la que queremos que se almacene el fichero .txt resultante y la fecha de ejecución:

Por otra parte, las cuatro tablas que deberemos crear en nuestra BB.DD. son las siguientes:

• Tabla RegSpace: es la tabla principal en la que se almacenaran los datos obtenidos junto a la fecha.

• Tabla TDiscosServ: tabla en la que se almacena las unidades de disco que tiene cada servidor. La emplearemos en un dropdownlist del Reporting Services.

• Tabla TmpRegSpace: se trata de una tabla intermedia que utilizará el procedimiento almacenado para insertar los datos obtenidos del fichero txt. Si nos fijamos en el procedimiento almacenado, primero hacemos una inserción masiva (BULK INSERT, paso 2) en TmpRegSpace, y posteriormente utilizamos estos datos para insertar en la tabla RegSpace (paso 3) añadiendo la fecha.

• Tabla TServidores: tabla que almacena los distintos servidores existentes. La emplearemos para llenar un dropdownlist del Reporting Services. 

Una vez que hemos visto la BB.DD. que vamos a emplear, pasamos a ver el script en powershell. Este script será ejecutado a través de una tarea programada en una máquina donde tengamos instalado Powershell (sólo en W2008 R2 ya viene incluido). Si no lo tenemos instalado, podemos obtenerlo desde aquí . Yo he empleado la versión 1.0 que era la que tenía instalada en un W2003.

La tarea programada lanzará a diario este script en powershell, de la siguiente forma:
C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe C:\InformeEspacioDisco\ReportBBDDEspacio.ps1
Indicando la ruta donde se encuentra instalado Powershell, así como la ruta de nuestro script (en mi caso lo he denominado ReportBBDDEspacio.ps1). El script debe ejecutarse antes que el procedimiento almacenado RegSpaceImport de SQL Server.
El script deberá ejecutarse desde una máquina que tenga acceso a los diferentes servidores que queremos analizar y empleará dos ficheros de texto plano (.txt). Uno de salida (fichero de resultados), que en mi caso he ubicado en una carpeta compartida en el fichero de BBDD, y otro fichero que contiene los nombres de los servidores cuyas unidades de disco queremos analizar.
Una primera opción es no especificar un usuario con privilegios en el código del script (por lo que se empleará el usuario indicado en la tarea programada):

#Fichero de resultados $freeSpaceFileName = file://nombre_servidor_bbdd/InformeEspacioDisco/EspacioLibre.txt #Fichero con los servidores analizados (un servidor por linea) $serverlist = "C:\InformeEspacioDisco\ListaServidores.txt" New-Item -ItemType file $freeSpaceFileName -Force Function writeDiskInfo { param( $fileName,$servidor,$devId,$frSpace,$totSpace) $totSpace=[math]::Round(($totSpace/1073741824),2) if ($totSpace -eq 0) {     $totSpace = 1 } $frSpace=[Math]::Round(($frSpace/1073741824),2) $usedSpace = $totSpace - $frspace $usedSpace=[Math]::Round($usedSpace,2) $linea = $servidor+","+$devid+","+$totSpace+","+$usedSpace+","+$frSpace Add-Content $fileName $linea } foreach ($server in Get-Content $serverlist) { $dp = Get-WmiObject win32_logicaldisk -ComputerName $server | Where-Object {$_.drivetype -eq 3} foreach ($item in $dp) { Write-Host $server $item.DeviceID $item.FreeSpace $item.Size writeDiskInfo $freeSpaceFileName $server $item.DeviceID $item.FreeSpace $item.Size } }

Pero también podemos indicar el usuario en el propio código, encriptando el password previamente y almacenándolo en un fichero de texto (Credencial.txt). Esto nos puede ser de utilidad si tenemos varios dominios y no existen relaciones de confianza entre ellos.

#Fichero de resultados $freeSpaceFileName = file://nombre_servidor_bbdd/InformeEspacioDisco/EspacioLibre.txt #Fichero con los servidores analizados (un servidor por linea) $serverlist = "C:\InformeEspacioDisco\ListaServidores.txt" New-Item -ItemType file $freeSpaceFileName -Force Function writeDiskInfo { param( $fileName,$servidor,$devId,$frSpace,$totSpace) $totSpace=[math]::Round(($totSpace/1073741824),2) if ($totSpace -eq 0) {     $totSpace = 1 } $frSpace=[Math]::Round(($frSpace/1073741824),2) $usedSpace = $totSpace - $frspace $usedSpace=[Math]::Round($usedSpace,2) $linea = $servidor+","+$devid+","+$totSpace+","+$usedSpace+","+$frSpace Add-Content $fileName $linea } foreach ($server in Get-Content $serverlist) { $pw = Get-Content C:\InformeEspacioDisco\Credencial.txt | ConvertTo-SecureString $cred = New-Object System.Management.Automation.PsCredential("Dominio\usuario", $pw) $dp = Get-WmiObject win32_logicaldisk -ComputerName $server -Credential $cred | Where-Object {$_.drivetype -eq 3} foreach ($item in $dp) { Write-Host $server $item.DeviceID $item.FreeSpace $item.Size writeDiskInfo $freeSpaceFileName $server $item.DeviceID $item.FreeSpace $item.Size } }

Con estos pasos ya hemos completado el primer paso del proyecto, la obtención de los datos y su almacenado en una BB.DD. En el próximo artículo veremos cómo mostrar esta información haciendo uso de Reporting Services.

Backups y restauraciones en SQL Server (V): Mover los ficheros de datos

En este artículo vamos a ver cómo podemos mover los ficheros de nuestras bases de datos en SQL Server de un servidor o instancia de SQL a otro/a. Vamos a ver en primer lugar la forma "clásica" (clasificada como deprecated por Microsoft) que es mediante sp_detach_db/sp_attach_db (para SQL Server 2000), y la forma recomendada en la actualidad (SQL Server 2005/2008) mediante ALTER DATABASE … MODIFY FILE.
Los datos y archivos de registro de transacciones de una base de datos pueden separarse y adjuntarse a la misma instancia de SQL Server (o a otra). Esto nos será útil si queremos cambiar la BB.DD. a otra instancia o mover sus ficheros de ubicación. La condición "sine qua non" es que nuestros SQL Server sean de la misma versión con el mismo juego de caracteres, el mismo criterio de ordenación y misma intercalación UNICODE.
Para realizar esta opción podemos utilizar el procedimiento almacenado de sistema sp_attach_db para adjuntar el fichero de la base de datos a nuestro SQL Server, de la siguiente forma:

EXEC sp_attach_db @dbname = N'BBDD',
@filename1 = N'Unidad:\ruta\BBDD_Data.mdf',
@filename2 = N'Unidad:\ruta\BBDD_log.ldf'

donde:

• @dbname es el nombre que le daremos a la base de datos.
• @filename1 es la ruta física de disco del fichero de la base de datos a adjuntar.
• @filename2 es la ruta física de disco del fichero de log de la base de datos.

Previamente deberemos "separar" los ficheros de la base de datos haciendo uso del procedimiento almacenado sp_detach_db, para lo cual se requiere que tengamos acceso exclusivo a la misma:

USE master;

--ponemos la BB.DD. en modo exclusivo

ALTER DATABASE BBDD SET single_user;

GO

--separamos el fichero de la BB.DD.

EXEC sp_detach_db @dbname = N'BBDD';

Al separar una base de datos, la estemos eliminando de la instancia de SQL Server, pero quedan intactos sus archivos de datos y de registro de transacciones. Estos archivos pueden utilizarse después para adjuntar la base de datos a cualquier instancia de SQL Server, incluyendo el servidor del que se separó.
No podremos separar una base de datos si se cumple cualquiera de las condiciones siguientes:

• La base de datos está replicada y publicada. Si está replicada, la base de datos no debe estar publicada. Antes de separarla, debemos deshabilitar la publicación ejecutando sp_replicationdboption. Este procedimiento crea o quita tablas específicas del sistema de réplica, cuentas de seguridad, etc., según las opciones proporcionadas. Para deshabilitar la publicación, la base de datos de publicaciones debe estar conectada. Si existe una instantánea de la base de datos para la base de datos de publicaciones, se debe quitar la instantánea antes de deshabilitar la publicación. Las instantáneas de base de datos son copias de sólo lectura y sin conexión de bases de datos, y no están relacionadas con una instantánea de réplica.

          sp_replicationdboption @dbname= 'BBDD' @optname='publish' @value='false'

Si tenemos algún problema al ejecutar este procedimiento almacenado, podemos realizar el proceso de forma manual siguiendo los pasos que se detallan en http://support.microsoft.com/kb/324401 .

• Existe una instantánea de base de datos en la base de datos (sólo versiones 2005 y 2008). Para poder separar la base de datos, debemos quitar todas sus instantáneas, haciendo uso del comando DROP DATABASE NombreInstantanea . Para identificar la instantánea que queremos eliminar, nos conectaremos mediante el Explorador de Objetos a la instancia de SQL Server, expandiremos Base de datos y a continuación haremos lo propio con Instantáneas de base de datos.
  
  
   
• Se está creando un reflejo de la base de datos. La base de datos no se puede separar hasta que finalice la sesión de creación de reflejo de la base de datos. Para ello utilizaremos la sentencia ALTER DATABASE BBDD PARTNER OFF.
• La base de datos es sospechosa. Debemos poner la base de datos sospechosa en modo de emergencia antes de separarla haciendo uso de la sentencia ALTER DATABASE db_state_option=' EMERGENCY' .
  
• La base de datos es una base de datos del sistema.

Como hemos indicado al inicio, estos dos procedimientos almacenados (sp_detach_db y sp_attach_db) han sido etiquetados como obsoletos en las versiones SQL Server 2005 y 2008 por lo que ya no se recomienda su uso. En su lugar, el método apropiado para reubicar ficheros en estas últimas versiones es con ALTER DATABASE … MODIFY FILE. Ejecutaremos este comando para cada fichero que queramos mover y conmutaremos el estado de la base de datos a online/offline.

ALTER DATABASE BBDD SET OFFLINE;

ALTER DATABASE BBDD

    MODIFY FILE (

        NAME='BBDD_Log',

        FILENAME='Unidad:\ruta\BBDD_Log.ldf');

ALTER DATABASE BBDD SET ONLINE;

Backups y restauraciones en SQL Server (IV): Restauración de una BBDD

Vamos a ver cómo recuperar una base de datos concreta, en el caso de realizar la recuperación en un servidor de destino diferente del origen. Voy a utilizar una base de datos denominada WSS_Content.

En primer lugar vamos a realizar una copia de seguridad completa de la BBDD. Para ello abriremos el analizador de consultas (en este ejemplo utilizo un SQL Server 2000 aunque sería lo mismo pero a través del Management Studio de SQL Server 2005/8) y nos conectaremos al servidor origen. Lanzamos el backup completo:

BACKUP DATABASE WSS_Content TO DISK = 'E:\bk_WSS_Content.bk' WITH INIT

Y a continuación haremos lo propio con el registro de transacciones:

BACKUP LOG WSS_Content TO DISK = 'E:\bk_WSS_Content_log.trn'

Para realizar la restauración deberemos tener en cuenta que la ubicación de la base de datos, al tratarse de otro servidor, puede ser diferente. Para ello haremos uso de la opción MOVE que nos servirá para indicar la ubicación tanto del fichero de datos como del registro de transacciones. Para conocer la ubicación en disco exacta pulsaremos con el botón secundario del ratón sobre la BBDD sobre la que vayamos a realizar la restauración, y seleccionamos "Propiedades". En las pestañas "Archivo de datos" y "Registro de transacciones" de la ventana que se abre, podremos conocer la ubicación de estos dos ficheros:

Vamos a partir del supuesto que la base de datos ya existe en el servidor destino. En caso negativo, deberemos crear la base de datos (simplemente crearemos una BB.DD. nueva, con el mismo nombre). Empezamos el proceso de restauración propiamente dicho.

En primer lugar deberemos poner nuestra base de datos en modo monousuario o exclusivo, para lo cual nos conectaremos al servidor destino mediante el analizador de consultas de SQL Server y ejecutaremos la sentencia:

ALTER DATABASE WSS_Content SET Single_User;

Podemos "expulsar" a los usuarios inmediatamente o tras un determinado tiempo haciendo de la opción ROLLBACK, para lo cual añadiremos a la sentencia anterior WITH
ROLLBACK AFTER segundos o WITH ROLLBACK IMMEDIATE.

 Y una vez realizado este paso, podemos comenzar el proceso de restauración. En primer lugar hacemos uso del Full Backup:

RESTORE DATABASE WSS_Content FROM DISK = 'E:\backupsProduccion\bk_WSS_Content.bak' WITH MOVE 'WSS_Content' TO 'E:\Archivos de Programa\Microsoft SQL Server\MSSQL\Data\WSS_Content_Data.mdf', MOVE 'WSS_Content_log' TO 'E:\Archivos de Programa\Microsoft SQL Server\MSSQL\Data\WSS_Content_log.ldf', NORECOVERY

Indicando la opción NORECOVERY. En el caso de que tuviésemos incrementales, deberíamos ir haciendo restauraciones sucesivas de cada uno de estos backups.
A continuación, restauramos el registro de transacciones:

RESTORE LOG WSS_Content FROM DISK = 'E:\backupsProduccion\bk_WSS_Content_log.trn' WITH RECOVERY

O si queremos hacer una restauración hasta un punto concreto en el tiempo podemos hacer uso de la opción STOPAT:

RESTORE LOG WSS_Content FROM DISK = 'E:\backupsProduccion\bk_WSS_Content_log.trn' WITH STOPAT = N'4/28/2010 11:01:45 PM', RECOVERY

Una vez finalizado el proceso de restauración, ponemos la BBDD en modo multiusuario:

ALTER DATABASE WSS_Content SET Multi_User;

 

Backups y restauraciones en SQL Server (III): T-SQL básico para la realización de backups

Vamos a ver en este artículo algo de transact SQL para la realización de los distintos tipos de backups.


Backups completos.    

Es necesario establecer un punto de referencia inicial, independientemente del modo de recuperación que vayamos a emplear. Para ello, crearemos un backup completo de nuestra BB.DD. La cláusula en T-SQL es:

BACKUP DATABASE Nombre_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.bak' WITH INIT

Con el parámetro WITH INIT nos aseguraremos de que el fichero de backup contiene una única copia de seguridad ya que, por defecto, el comando BACKUP lo añade al fichero existente. De esta forma nos aseguramos que el fichero se sobreescribe.
En los modos Full-Recovery o Bulk-Logged Recovery, los backups de los logs son esenciales, no sólo por el propósito de recuperación, sino también para controlar el tamaño del registro de transacciones activo. El modo de recuperación simple es el único que elimina las transacciones periódicamente.
Si nunca se realiza un respaldo, el registro de transacciones de una BBDD en modo Full-Recovery o Bulk-Logged Recovery continuará creciendo hasta consumir todo el espacio disponible en disco. Y si el disco se queda sin espacio, la BB.DD. se parará.
El comando para realizar el backup del fichero de log es:

BACKUP LOG Nombre_Log_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.trn'

Cada acción contra la BB.DD. se asigna a un Log Sequence Number (LSN). Para restaurar a un punto específico en el tiempo, debemos tener un continuo registro de LSNs.




Backups diferenciales.
La restauración de los bakups de transacciones tiende a ser una operación lenta, especialmente si nuestro backup completo es semanal, o incluso superior en su programación en el tiempo. Los backups diferenciales intentan decrementar el tiempo de recuperación. La cláusula T-SQL es:

BACKUP DATABASE Nombre_BBDD TO DISK = 'Unidad:\ruta\ficherobackup.dif' WITH DIFFERENTIAL, INIT

El backup "ficherobackup.dif" contiene todos los cambios realizados desde el último backup completo. Podemos utilizarlo durante el proceso de restauración en combinación a los backups del registro de transacciones. En primer lugar, restaurando el backup completo, seguido de la restauración del último diferencial, y a continuación restaurando cualquier log de transacciones posterior.
Consideraremos los siguientes elementos cuando hagamos uso de backups diferenciales:

• Si no realizamos backups completos frecuentemente, los backups diferenciales crecerán significativamente en tamaño para una BB.DD. operativa. Recordemos que un backup diferencial contiene todos los cambios desde el backup completo más reciente. Cuanto mayor sea el tiempo entre backups completos, más cambios recogerán en los diferenciales.
• Un backup diferencial está directamente ligado a un específico backup completo. La realización de un backup completo fuera del calendario habitual de backups puede hacer inservible una copia diferencial.
• A través del examen de la frecuencia de backups diferenciales, se puede establecer un plan de copias de seguridad. Cuando la BB.DD. sufre cambios frecuentemente, los backups diferenciales pueden consumir bastante espacio. Deberemos encontrar el equilibrio entre la velocidad de recuperación necesaria con respecto al espacio disponible.
  
   

NOTA: no debemos confundir los backups diferenciales con los incrementales. Los diferenciales incluyen todos los datos que han cambiado desde el último backup completo. Mientras, el incremental incluye todos los datos que han cambiado desde el último diferencial o completo. SQL Server no dispone de ningún equivalente a los incrementales.




Verificación de errores.
El proceso de backup puede realizar una verificación de los datos mientras se están respaldando ya sea verificando páginas dañadas o validando por checksums.
Debemos habilitar cualquier opción que deseemos en el nivel de la base de datos. La opción "Verificación de páginas" nos permitirá descubrir e informar sobre transacciones de E/S incompletas debidas a errores de E/S de disco. Podremos elegir entre las opciones "Ninguna", "checksum" y "TornPageDetection".

 

La opción "TornPagesDetection" (detección de páginas dañadas) verifica simplemente cada página de datos para ver si un proceso de escritura se ha completado en su totalidad. Si encuentra una página que ha sido sólo parcialmente escrita (debido a algún tipo de fallo hardware) simplemente se marca como "dañado".
La validación por comprobación de sumas ("Checksum") es una técnica de verificación de páginas que añade un valor para cada página de datos, esencialmente identificando el tamaño exacto en bytes de cada página. El proceso de backup puede validar por checksum comparando el valor almacenado en la base de datos con el valor asociado con la página de datos escrita en disco. Sin embargo, no lo hace por defecto. Si la validación por checksums está habilitada, podemos forzar el proceso de backup para realizar está validación.

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad:\ruta\fichero.back' WITH CHECKSUM

Cuando se encuentre con un error durante la validación por checksum, SQL Server escribirá un registro a MSDB..SUSPECT_PAGE. El comportamiento por defecto es STOP_ON_ERROR, permitiendo corregir el problema y continuar, lanzando el mismo comando con la adicción de RESTART.
La otra opción de validación por checksum es CONTINUE_ON_ERROR. Cuando está habilitada, el backup simplemente escribe el error en la tabla MSDB..SUSPECT_PAGE y continua. Sin embargo, esta tabla tiene un límite de 1000 filas, y si se alcanza, el backup fallará.
El habilitar la validación por checksum obviamente tiene un impacto en el rendimiento del proceso de backup, por lo que deberemos tener un ventana suficientemente grande para realizar las copias de seguridad.
Examinaremos la tabla MSDB..SUSPECT_PAGE para buscar el enfoque adecuado para hacer frente a los errores.

Backups divididos (striped).
Algunas BB.DD.s son demasiado grandes para crear un backup completo en una única cinta LTO o en un array de discos. En estos casos, podemos hacer uso de los backup striped, también denominados multiplexados. La ventaja es que casa dispositivo utiliza la totalidad de su capacidad para crear el backup. Su desventaja es que, en caso de fallo, todas las cintas o ficheros se necesitarán para completar una restauración.
Para crear un backup striped utilizaremos:

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad1:\ruta1\fichero1.bak' , 'unidad2:\ruta2\fichero2.bak', 'unidad3:\ruta3\fichero3.bak' WITH INIT, CHECKSUM, CONTINUE_ON_ERROR

El backup será "extendido" a través de todos los ficheros indicados.




Backups en espejo (mirrored).
Los backups en espejo son una característica incorporada desde la versión 2005 de SQL Server, y que nos permite escribir el mismo fichero de backup en múltiples ubicaciones. El comando para crear un backup en espejo es

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad1:\ruta1\fichero1.bak' MIRROR TO DISK = 'unidad2:\ruta2\fichero2.bak' MIRROR TO DISK = 'unidad3:\ruta3\fichero3.bak' WITH INIT, CHECKSUM, CONTINUE_ON_ERROR

La única restricción para realizar respaldos en múltiples localizaciones es que los dispositivos utilizados deben ser idénticos. En particular, múltiples dispositivos de cinta deben ser del mismo modelo del mismo fabricante.




Backups Sólo_copia (copy_only).
Podemos utilizar backup para propósitos distintos a los de recuperación en caso de desastre. Por ejemplo, un uso típico es utilizar un backup para mover una copia de la BBDD a un entorno de desarrollo. Como ya hemos indicado, los backups diferenciales están asociados directamente a un único backup completo. Desde SQL Server 2005 existe una característica, el backup "copy-only", que no resetea la cadena de backups. Cualquier copia de seguridad realizada fuera del esquema de los backups estándar debería ser realizada como un backup "only-copy":

BACKUP DATABASE Nombre_BBDD TO DISK = 'unidad:\ruta\ficheroBackup.bk' WITH INIT, CHECKSUM, COPY_ONLY

Si existe un calendario de respaldos en SQL Server, para cada respaldo creado existe un número de secuencia o LSN. Entonces si creamos un backup completo sin hacer uso de la opción "copy-only", esa secuencia se verá afectada y tendremos problemas para recuperar un respaldo diferencial realizado posteriormente. Necesitaremos contar con el respaldo completo que se realizó en medio de la secuencia entonces no podremos recuperar la información.
Si queremos conocer el número de secuencia podemos lanzar esta query:

SELECT database_name, backup_start_date, is_copy_only, first_lsn FROM msdb..backupset WHERE database_name = ''Nombre_BBDD' ORDER BY backup_start_date DESC

Backups y restauraciones en SQL Server (II): modelos de recuperación.

En este segundo artículo vamos a ver los modelos de recuperación existentes en SQL Server y las características principales de cada uno de ellos.




Existen tres modelos de recuperación distintos: Full Recovery, Simple Recovery y Bulk-Logged Recovery (http://msdn.microsoft.com/es-es/library/ms189275(v=sql.90).aspx ). Cada modelo determina como debería comportarse el registro de transacciones, tanto en el uso activo como durante el backup del registro de transacciones.

• Full Recovery (recuperación completa): modelo de recuperación por defecto para una BBDD recién creada. Todo el historial de transacciones se mantiene en el registro de transacciones que deberíamos administrar. Su ventaja es que se puede restaurar hasta el momento en el que una BB.DD. falla, o hasta un punto específico en el tiempo. Su desventaja es que si lo dejamos sin administrar, el registro de transacciones puede crecer rápidamente. Si se llena el disco, lo más probable es que la BB.DD. falle. Este modo no ofrece ningún tipo de mantenimiento automatizado del registro de transacciones. La única forma de limpiar es cambiar al modo de recuperación simple o realizar una copia de seguridad del registro de transacciones.
  
   
• Simple Recovery (recuperación simple): limpia el registro de transacciones activo de las transacciones realizadas durante un checkpoint. Su ventaja es que el registro de transacciones se mantiene y no necesita una administración directa. El inconveniente es que, en caso de desastre, está garantizado un cierto nivel de pérdida de datos. Es imposible restaurar el momento exacto del fallo. A pesar de que este modo de recuperación trunca el registro de transacciones periódicamente, esto no conlleva a que no crezca. Podemos fijar el tamaño del registro de transacciones a un determinado tamaño, pero si se ejecuta una inserción masiva (Bulk Insert), SQL Server lo tratará como una inserción simple. Por tanto, no sirve como respuesta para el mantenimiento de logs por lo que no deberíamos usarlo en BB.DD. en producción.
  
   
• Bulk-Logged Recovery (recuperación de registros masiva): Cuando se utiliza este modelo, el registro de transacciones se comporta exactamente como si se estuviese en modo de recuperación completa, con una excepción: las operaciones masivas se registran mínimamente. Las operaciones masivas incluyen:
  • Importaciones masivas de datos, que se podrían realizar haciendo uso de BCP (Bulk Copy Program), el comando BULK INSERT u OPENWROWSET con la cláusula Bulk.
  • Operaciones LOB (Large OBject), tales como WRITETEXT o UPDATETEXT, para columnas NText o Image.
  • Cláusulas SELECT INTO.
  • CREATE INDEX, ALTER INDEX, ALTER INDEX REBUILD o DBCC REINDEX.
  
      Como todas estas operaciones se registran en el modelo Full Recovery, el registro de transacciones puede crecer enormemente. La ventaja del modelo Bulk-Logged Recovery es que previene de un crecimiento del registro no deseado o imprevisto. Cada vez que se realiza una operación de registro masiva, SQL Server sólo registra los identificadores de las páginas de datos que se han visto afectados. Las páginas SQL Server tienen identificadores internos, así que se puede comprimir un aumento de identificadores de páginas en una pequeña parte del registro.
  Este modelo de recuperación tiene la desventaja de que la recuperación en un punto del tiempo no es técnicamente posible. Sólo podemos restaurar al punto de la última copia de seguridad del registro de transacciones. Será posible recuperar las transacciones masivas siempre y cuando se disponga de un backup del registro de transacciones que las contiene. Por otra parte, mientras que el registro de transacciones activo en este modo es más pequeño, la copia de seguridad del log contiene copias de las páginas modificadas durante el proceso de carga masiva. El resultado son copias del registro de transacciones potencialmente grandes.
  La clave para utilizar este modelo de recuperación de forma efectiva es invocarlo sólo cuando sea necesario, y a continuación, volver al modo de recuperación completa para un punto en el tiempo del backup. Se trata de un procedimiento de riesgo, y para mover entre Bulk-Logged Recovery y Full Recovery, siempre debemos seguir los siguientes pasos:

1. Cambiar de Full Recovery a Bulk-Logged Recovery.
2. Realizar la operación de registro masivo.
3. Una vez completado, volver inmediatamente al modo Full Recovery.
4. Realizar un backup completo de la BB.DD.

En el próximo artículo veremos algo de transact SQL…

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.