Grimpi IT Blog

septiembre 28, 2008

sql_expression_dependencies y Filtered Index en SQL Server 2008

Filed under: Metada, SQL Server, SQL Server 2008 — Etiquetas: , — grimpi @ 2:06 am

SQL Server incorpora montón de nuevas novedades, muy interesantes.
Una de ellas, son los Índices Filtrados, (Filtered Index), que permite indexar pero solo para determinado rango de valores dada una expresión. No es intención del post entrar en detalles de esto. Si quieren información al respecto, recomiendo ver este link.

Ahora, como hago para saber los objetos que están siendo referenciados en la expresión del filtro del índice? SQL Server 2008 incorpora una nueva vista de sistema, que se llama sys.sql_expression_dependencies y que contiene una fila para cada dependencia por nombre en una entidad definida por el usuario en la base de datos actual.

Dada esta vista, saber por ejemplo todas las columnas usadas dentro de expresiones en un índice filtrado, resulta sumamente fácil:

SELECT I.name AS IndexName, OBJECT_NAME(I.object_id) AS TableName, C.name
FROM sys.sql_expression_dependencies SED
INNER JOIN sys.indexes I ON I.object_id = SED.referencing_id AND I.index_id = SED.referencing_minor_id
INNER JOIN sys.columns C ON C.object_id = SED.referenced_id AND C.column_id = SED.referenced_minor_id

Para más información sobre esta Vista, ver este link.

septiembre 14, 2008

Como hacer un Attach dinámicamente de un conjunto de MDF a una instancia de SQL Server

Filed under: SQL Server — Etiquetas: , , — grimpi @ 10:30 pm

Recientemente tuve la necesidad de attach a una instancia de SQL Server, más de 200 bases de datos. Tenía los archivos .mdf (Datos) y .ldf (Transaction log) de cada una de las 200 bases.
Por supuesto que hacer esta operación manualmente es algo bastante engorroso. Por lo cual, investigué un poco como hacer esta operación dinámicamente. O sea, hacer un script que lea todos los archivos .mdf de un directorio y haga el attach de cada uno de los mdf a la instancia del SQL Server, obviamente con su respectivo archivo de transaction log.

Paso 1:
Lo primero que necesitamos es tener un listado de todos los archivos .mdf que se encuentran dentro de un directorio y que deseamos hacer el attach.
Existe en SQL Server 2005, un store procedure extendido no documentado, que se llama xp_dirtree, que devuelve todos los archivos existentes dentro de una carpeta.
Para capturar la info de este store, almacenamos dentro de una tabla temporal el resultado de la consulta:

declare @Path nvarchar(255)
select @Path = N‘C:\Data\’
create table #filetmp (Name nvarchar(255) NOT NULL, depth int NOT NULL, IsFile bit NULL)
insert #filetmp EXECUTE master.dbo.xp_dirtree @Path, 1, 1

Paso 2:
Necesitamos saber a partir del .mdf, cual es el nombre de la base de datos que tenemos que crear. Por ejemplo, un archivo puede llamarse Test1.mdf, sin embargo, lógicamente, el nombre de la base que contiene es distinto. Para averiguar este dato, debemos usar el comando DBCC no documentado de Microsoft, CheckPrimaryFile.

DBCC CHECKPRIMARYFILE (‘C:\Data\ISMM2_Data.MDF’ , 2)

(El primer parámetro es el path completo y el nombre del archivo mdf que se desea tener información, el segundo parámetro, indica que tipo de información queremos extraer)

El resultado de esta consulta es el siguiente:



Como podemos observar, este comando nos devuelve un listado de propiedades del .mdf, donde la primer propiedad, Database name, es el nombre lógico de la base de datos. Debemos capturar este valor para usarlo más adelante.


Paso 3:
Luego, debemos obtener todos los archivos dependientes de la base. Por ejemplo, una base de datos puede tener además obviamente del .mdf, otros archivos secundarios, como por ejemplo el archivo .ldf (transaction log) y en caso de que tenga más de un filegroup, sus respectivos archivos .ndf.
Para averiguar esta información, nuevamente debemos usar el comando CHECKPRIMARYFILE, pero el segundo parámetro ahora debe ser un 3.

DBCC CHECKPRIMARYFILE (‘C:\Data\ISMM2_Data.MDF’ , 3)

El resultado de esta consulta es el siguiente:



En este caso, la instrucción devuelve el nombre físico y lógico de todos los archivos dependientes de la base de datos. Debemos almacenar el valor del campo filename dentro de una tabla temporal (ver el script de ejemplo para ver como se hace).


Paso 4:
Ok, ya tenemos toda la info que necesitamos para hacer el attach dinámico de todas la base de datos a la instancia del SQL Server, pero como se hace vía script un attach a partir de un mdf en SQL Server? Bueno, existen 2 alternativas. Una es usando la instrucción CREATE DATABASE … FOR ATTACH y la otra es usando el Store Procedure sp_attach.
Personalmente encuentro mucho más claro el primer método.

Ejemplo de uso:

CREATE DATABASE ISMM ON
(FILENAME = N‘C:\Data\ISMM_Data.mdf’),
(FILENAME = N‘C:\Data\ISMM_log.ldf’) FOR ATTACH

Paso 5:
Ahora viene lo más difícil. Tenemos toda la info sobre las bases de datos y sabemos cómo hacer un attach individual de un mdf. Pero ahora tenemos que juntar toda la información y generar un script que haga el attach de cada una de las bases con todos sus archivos dependientes.
Copiar este script en la web, sería poco claro de entender. Por lo tanto, dejo a disposición de quien ande necesitando algo similar, un script genérico, para efectuar el attach dinámico, usando los 4 pasos anteriores.
Lo pueden bajar desde acá: http://opendbdiff.com/AttachMasivo.txt

Cualquier comentario, duda, mejora u error, no duden en informar. Espero que les sea útil. A mí me ahorro bastante tiempo.

Blog de WordPress.com.