En el artículo «Utilizando SSIS para crear scripts de respaldo con la estructura de la base de datos» (parte 1 – parte 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.
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.
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.
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.
Deja un comentario