En el artículo «Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos» (parte 1parte 2), publicado con anterioridad en este blog, dejábamos pendiente de desarrollar una mejora que nos permitiera acelerar los tiempos de creación de los archivos de script, ya que el uso de algunas colecciones proporcionadas por la clase Database, más concretamente Views, StoredProcedures y UserDefinedFunctions, al contener una importante cantidad de objetos del sistema, provocaba que el tiempo de ejecución del proceso se extendiese más de lo necesario, en el caso de que no necesitáramos añadir al script tales tipos de objetos.

Por tal motivo, la finalidad del presente artículo será ofrecer una solución a dicho problema, exponiendo una técnica mediante la cual conseguiremos reducir los mencionados tiempos de ejecución.

 

Creación del proyecto de Integration Services

Como gestor de datos utilizaremos SQL Server Denali CTP3, y como entorno de desarrollo «SQL Server Business Intelligence Development Studio» (SSBIDS), con el que crearemos un nuevo proyecto de tipo «Integration Services Project» al que daremos el nombre EstructurasBBDDOptimizado. Para más detalles acerca de la configuración del proyecto: paquete de integración .dtsx, variables, referencias a ensamblados, etc., consultar el proyecto desarrollado en el artículo mencionado al comienzo, ya que su modo de creación es equivalente. En cuanto al código fuente del paquete, utilizaremos como punto de partida el existente en dicho proyecto, sobre el que progresivamente iremos realizando las modificaciones necesarias.

 

Las vistas INFORMATION_SCHEMA.XXX

Si queremos averiguar cuáles son las vistas que el usuario ha creado en una base de datos, tenemos a nuestra disposición la vista del sistema INFORMATION_SCHEMA.VIEWS, que podemos localizar en el nodo «Views | System Views» de SQL Server Management Studio.

OptimizandoCreacionScriptsEstructuraBDSSIS_01

 

Una consulta sobre esta vista nos revelará, a través de sus campos TABLE_NAME y TABLE_SCHEMA, el nombre y esquema respectivamente de las vistas de usuario de la base de datos a la que estemos conectados.

OptimizandoCreacionScriptsEstructuraBDSSIS_02

De igual manera ocurre para los procedimientos almacenados y funciones, cuyos nombres y esquemas podemos encontrar en los campos ROUTINE_NAME y ROUTINE_SCHEMA de INFORMATION_SCHEMA.ROUTINES, otra vista del sistema, en la que para diferenciar el tipo de objeto emplearemos el campo ROUTINE_TYPE, comprobando que su valor sea «PROCEDURE» o «FUNCTION».

Utilizando las anteriores vistas del sistema crearemos, dentro del código del paquete de nuestro proyecto de integración, un conjunto de colecciones auxiliares, que contendrán, respectivamente, los nombres de las vistas, procedimientos almacenados y funciones de usuario de la base de datos. Posteriormente recorreremos estas colecciones, y por cada uno de sus elementos, accederemos a la colección-elemento equivalente que existe en la clase Microsoft.SqlServer.Management.Smo.Database, para ejecutar su método Script, agregando la sentencia de creación resultante al archivo de script.

 

Los espacios de nombres System.Data.SqlClient, System.Collections.Specialized y la creación de colecciones auxiliares

Accediendo al editor de código del paquete, comenzaremos a implementar las operaciones que acabamos de describir declarando el espacio de nombres System.Data.SqlClient, mediante el cual tendremos acceso a las clases SqlConnection, SqlCommand y SqlDataReader, necesarias para manipular los objetos de la base de datos con los que deberemos trabajar.

A continuación nos desplazaremos hasta el bucle foreach que recorre la colección Databases del objeto Server, y mediante una conexión (objeto SqlConnection) contra la base de datos en curso, ejecutaremos una consulta a través de un objeto SqlCommand sobre la vista INFORMATION_SCHEMA.VIEWS, que nos devolverá, en forma de objeto SqlDataReader, el conjunto de resultados con los nombres de las vistas y esquemas de la base de datos. Seguidamente recorreremos este SqlDataReader y añadiremos sus valores a un objeto StringCollection (para usar este tipo de colección declararemos el espacio de nombres System.Collections.Specialized). Como resultado, obtendremos una colección con los nombres de las vistas de usuario y esquemas de la base de datos que queremos incluir en el script, evitando de esta manera las vistas del sistema.

//....
using System.Data.SqlClient;
using System.Collections.Specialized;
//....
foreach (Database oDatabase in oServer.Databases)
{
if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
{
Dts.Events.FireInformation(0, string.Empty, "Procesando BBDD: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
 
// conexión
SqlConnection oConnection = new SqlConnection("Data Source=" + oServer.Name +
";Initial Catalog=" + oDatabase.Name + ";Integrated Security=True");
 
SqlCommand oCommand = new SqlCommand();
oCommand.Connection = oConnection;
oCommand.CommandType = CommandType.Text;
 
oConnection.Open();
 
// vistas
oCommand.CommandText = "SELECT TABLE_NAME + ' ' + TABLE_SCHEMA AS Vista " +
"FROM INFORMATION_SCHEMA.VIEWS ORDER BY TABLE_SCHEMA, TABLE_NAME";
 
SqlDataReader oDataReader = oCommand.ExecuteReader();
StringCollection cllVistas = new StringCollection();
 
while (oDataReader.Read())
{
cllVistas.Add(oDataReader["Vista"].ToString());
}
 
oDataReader.Close();
 
//....

 

Con respecto a los procedimientos almacenados y funciones procederemos de igual modo, pero empleando en este caso la vista INFORMATION_SCHEMA.ROUTINES para crear las colecciones auxiliares, como vemos en el siguiente bloque de código.

//....
// procedimientos almacenados
oCommand.CommandText = "SELECT ROUTINE_NAME + ' ' + ROUTINE_SCHEMA AS ProcedimientoAlmacenado " +
"FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' " +
"ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";
 
oDataReader = oCommand.ExecuteReader();
StringCollection cllProcedimientosAlmacenados = new StringCollection();
 
while (oDataReader.Read())
{
cllProcedimientosAlmacenados.Add(oDataReader["ProcedimientoAlmacenado"].ToString());
}
 
oDataReader.Close();
 
// funciones
oCommand.CommandText = "SELECT ROUTINE_NAME + ' ' + ROUTINE_SCHEMA AS Funcion " +
"FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'FUNCTION' " +
"ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME";
 
oDataReader = oCommand.ExecuteReader();
StringCollection cllFunciones = new StringCollection();
 
while (oDataReader.Read())
{
cllFunciones.Add(oDataReader["Funcion"].ToString());
}
oDataReader.Close();
 
oConnection.Close();
//....

 

Creación de las sentencias para el script

Finalizada la creación de las colecciones auxiliares, nuestro siguiente paso consistirá en crear las sentencias de script para la base de datos y tablas; operaciones que no han sufrido modificación con respecto al anterior artículo, ya que en este caso no debemos lidiar con objetos del sistema.

//....
// base de datos
oScriptingOptions.FileName = sRutaArchivo + oDatabase.Name + ".sql";
oDatabase.Script(oScriptingOptions);
// tablas
foreach (Table oTable in oDatabase.Tables)
{
Dts.Events.FireInformation(0, string.Empty,
"Procesando tabla: " + oDatabase.Name + '-' + oTable.Schema + '.' + oTable.Name,
string.Empty, 0, ref bOtraVez);
oTable.Script(oScriptingOptions);
}
//....

 

A continuación crearemos un bucle foreach para recorrer cada una de las colecciones auxiliares, accediendo, en cada iteración, al elemento de la colección equivalente perteneciente al objeto Database. Para ello, cada cadena (conteniendo los nombres del objeto y esquema, separados por un carácter de espacio) que recuperemos de la colección auxiliar, será asignada, mediante el método String.Split, a un array de tipo string con dos elementos. Como separador para el método Split, utilizaremos un array de tipo char con un carácter de espacio en blanco.

Seguidamente, utilizando los valores del array string, accederemos al elemento de la colección que corresponda en cada caso (Views, StoredProcedures o UserDefinedFunctions) perteneciente al objeto Database, para ejecutar su método Script. Adicionalmente, emplearemos la enumeración ObjetosBD en el índice del array, declarada con ámbito de la clase ScriptMain, para facilitar la lectura del código.

//....
enum ObjetosBD : int
{
    Nombre,
    Esquema
};
 
public void Main()
{
//....
    foreach (Database oDatabase in oServer.Databases)
    {
        if ((!oDatabase.IsSystemObject) && (!oDatabase.Name.StartsWith("Report")))
        {
            //....
            char[] aSeparador = { ' ' };
 
            // vistas
            string[] aVista;
 
            foreach (string sVista in cllVistas)
            {
                aVista = sVista.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando vista: " + oDatabase.Name + '-' +
                    aVista[(int)ObjetosBD.Esquema] + '.' + aVista[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);
                ((View)oDatabase.Views[aVista[(int)ObjetosBD.Nombre], 
Vista[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }
 
            // procedimientos almacenados
            string[] aProcedimientoAlmacenado;
 
            foreach (string sProcedimientoAlmacenado in cllProcedimientosAlmacenados)
            {
                aProcedimientoAlmacenado = sProcedimientoAlmacenado.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando procedimiento almacenado: " + oDatabase.Name + '-' +
                    aProcedimientoAlmacenado[(int)ObjetosBD.Esquema] + '.' + 
ProcedimientoAlmacenado[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);
 
(StoredProcedure)oDatabase.StoredProcedures[aProcedimientoAlmacenado[(int)ObjetosBD.Nombre
], aProcedimientoAlmacenado[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }
 
            // funciones
            string[] aFuncion;
 
            foreach (string sFuncion in cllFunciones)
            {
                aFuncion = sFuncion.Split(aSeparador);
                Dts.Events.FireInformation(0, string.Empty,
                    "Procesando función: " + oDatabase.Name + '-' +
                    aFuncion[(int)ObjetosBD.Esquema] + '.' + aFuncion[(int)ObjetosBD.Nombre],
                    string.Empty, 0, ref bOtraVez);
 
(UserDefinedFunction)oDatabase.UserDefinedFunctions[aFuncion[(int)ObjetosBD.Nombre], 
Funcion[(int)ObjetosBD.Esquema]]).Script(oScriptingOptions);
            }
//....

Tras añadir el anterior bloque de código al paquete de integración, volveremos a ejecutar éste, comprobando cómo los tiempos de proceso se reducirán notablemente.

 

Ajustar el tamaño de la base de datos en la sentencia de creación

Si abrimos cualquiera de los archivos de script generados tras la ejecución del paquete, observaremos que la sentencia «CREATE DATABASE» asigna, para los archivos de datos y log, el tamaño actual de la base de datos.

OptimizandoCreacionScriptsEstructuraBDSSIS_03

 

Pero supongamos que algunos de estos archivos de datos y log tienen un tamaño considerable, y necesitamos que al ser creada la base de datos correspondiente mediante la ejecución del script, dicho tamaño inicial no sea elevado.

Por tal motivo, la técnica que proponemos para solucionar este problema consiste en acceder y manipular el contenido del archivo de script mediante los métodos de las clases File y String, modificando el tamaño inicialmente asignado por uno menor. Para ello, pasaremos el contenido del archivo a una variable de tipo string, dentro de la cual, buscaremos las asignaciones de tamaño para la base de datos, reemplazándolas por los nuevos valores. Finalmente, borraremos el archivo original, creando uno nuevo a partir del contenido modificado existente en la variable string.

//....
Dts.Events.FireInformation(0, string.Empty, "Ajustando tamaño inicial de archivos: " + oDatabase.Name, string.Empty, 0, ref bOtraVez);
 
string sTextoScript = File.ReadAllText(oScriptingOptions.FileName);
 
int nPosicionInicio = sTextoScript.IndexOf(", SIZE");
int nPosicionFin = sTextoScript.IndexOf(", MAXSIZE");
 
sTextoScript = sTextoScript.Replace(sTextoScript.Substring(nPosicionInicio,
 
(nPosicionFin - nPosicionInicio)), ", SIZE = 3072KB");
 
nPosicionInicio = sTextoScript.IndexOf(", SIZE", nPosicionInicio + 6);
nPosicionFin = sTextoScript.IndexOf(", MAXSIZE", nPosicionFin + 6);
 
sTextoScript = sTextoScript.Replace(sTextoScript.Substring(nPosicionInicio,
 
(nPosicionFin - nPosicionInicio)), ", SIZE = 1024KB");
 
File.Delete(oScriptingOptions.FileName);
 
File.WriteAllText(oScriptingOptions.FileName, sTextoScript);

 

Realizadas las anteriores modificaciones sobre nuestro código fuente, damos por concluido este artículo, en el que hemos expuesto una técnica para optimizar el tiempo empleado por un paquete de Integration Services en generar los archivos de script conteniendo las estructuras de las bases de datos de SQL Server. Espero que os sirva de ayuda.

Un saludo.