Aiello DBA

Nueva funcion CHOOSE en SQL 2012

2012-02-08T09:55:00.000-03:00

En esta oportunidad veremos la función CHOOSE es una de las novedades de SQL 2012. Nos permite pasar un número (primer parámetro) y seleccionar un valor en base a éste.
Este mismo funcionamiento lo podíamos obtener con una tabla de códigos con la cual hacer el INNER JOIN, pero de esta forma podemos hacerlo mas simple. Veamos un ejemplo.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 18/01/12
-- Description: Nueva funcion CHOOSE en SQL 2012
-- =============================================

-- Primero creamos una tabla con valores de ejemplo para poder probar la funcion
CREATE TABLE #PruebaCHOOSE(
id INT PRIMARY KEY, codigo INT
)

INSERT INTO #PruebaCHOOSE VALUES
(1, -2),
(2, 3),
(3, 2),
(4, 1),
(5, 5),
(6, 2),
(7, 1)

-- Utilizamos el campo codigo para seleccionar una de las tres posibles descripciones
SELECT id, CHOOSE(codigo, 'CODIGO1', 'CODIGO2', 'CODIGO3')
FROM #PruebaCHOOSE

DROP TABLE #PruebaCHOOSE

Como podemos ver en el ejemplo el uso es muy simple, y en caso de que el índice no se encuentre entre 1 y la cantidad de opciones retorna null.
Esta función requiere siempre que como mínimo se le pasen dos parámetros, el primero el del índice y el segundo un código (al menos uno).

Pro
Muy simple de utilizar y muy clara
Contra
Va contra la normalización
No genera error de fuera de índice. Esto es muy personal pero me parece que es para problemas que no genere una excepción cuando el valor ingresado no corresponde a una de las opciones

Lanzamiendo online de SQL2012

2012-02-03T13:01:00.003-03:00

El 7 de marzo es el lanzamiento online del SQL2012! les dejo un link con la agenda para que puedan ir palpitandolo:

http://www.sqlserverlaunch.com/LATAM/agenda

LAG y LEAD, ver el registro anterior o siguiente en SQL 2012

2012-02-03T09:42:00.000-03:00

El SQL 2012 sigue teniendo novedades interesantes para mostrar, así que continuaré con la introducción a las nuevas funciones TSQL. Hoy es el turno de LAG y LEAD.
Una cosa que siempre me molestó es cuando un cliente decía "pero esto es fácil, en el excel lo hago" y quizá en SQL no era tan simple de expresar. Un caso común de esto es cuando una "celda" se calculaba utilizando valores de su registro pero también del anterior.
La forma habitual de hacer esto en sql es con un self join, y ahí empezar a remarla. En la versión 2012 contamos con una forma mucho mas cómoda y es con las funciones LAG y LEAD.
La función LAG nos permite consultar el registro inmediatamente anterior al que estamos procesando, y si lo deseamos tambien dos anteriores o el número deseado. Veamos con un ejemplo que es mas claro.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 18/01/12
-- Description: Hacer un cálculo en base al registro anterior en SQL 2012: LAG y LEAD
-- =============================================

-- Primero creamos una tabla con valores de ejemplo para poder probar la funcion
CREATE TABLE #PruebaLAG_LEAD(
id INT PRIMARY KEY, dia DATE, ventas INT
)

INSERT INTO #PruebaLAG_LEAD VALUES
(1, '20120101', 10),
(2, '20120102', 11),
(3, '20120103', 13),
(4, '20120104', 8),
(5, '20120105', 10),
(6, '20120106', 15),
(7, '20120107', 10)

/*
Dada la siguiente tabla deseamos saber la diferencia de ventas de un día con el anterior, así que con los valores de ejemplo obtendriamos:
(1, '20120101', NULL),
(2, '20120102', 1),
(3, '20120103', 2),
(4, '20120104', -5),
(5, '20120105', 2),
(6, '20120106', 5),
(7, '20120107', -5)
*/
-- En la forma tradicional esto lo podríamos hacer así:
SELECT t1.id, t1.dia, t1.ventas, t2.ventas ventaAnterior, t1.ventas - t2.ventas ventaDiferencia
  FROM #PruebaLAG_LEAD t1
 LEFT JOIN #PruebaLAG_LEAD t2 ON t1.id -1 = t2.id

-- Es importante resaltar el null del primer registro, causado por el LEFT JOIN. Esto se debe a que en mi primer registro no tiene sentido comparar con el valor anterior, se va fuera de la lógica de negocio.

-- Ahora veamos como podríamos escribir esto mismo en SQL 2012

SELECT *, LAG(ventas, 1, null)OVER(ORDER BY dia) as ventaAnterior, 
    ventas - LAG(ventas, 1)OVER(ORDER BY dia) as ventaDiferencia
  FROM #PruebaLAG_LEAD

DROP TABLE #PruebaLAG_LEAD

Como ven es muy comodo de utilizar y viene en diferentes sabores. Los tres parámetros que toma (los últimos dos opcionales) son:
LAG(campo, cantidad de registros anteriores a buscar, valor default si es nulo)

En caso de que no se indique cuantos registros atras mirar se asume uno. Y en caso de no indicar un valor default es null (tal como en el ejemplo que utilice una vez null de forma explícita y otra implícita).

Lo único que no vimos es que pasaría si quisiera ignorar el registro inicial, para no tener ese valor nulo. En las versiones anteriores me bastaría con cambiar el LEFT JOIN por un INNER JOIN, pero ahora debo poner un poco mas de mano y utilizar una CTE.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 18/01/12
-- Description: Hacer un cálculo en base al registro anterior en SQL 2012: LAG y LEAD
-- =============================================

-- Primero creamos una tabla con valores de ejemplo para poder probar la funcion
CREATE TABLE #PruebaLAG_LEAD(
id INT PRIMARY KEY, dia DATE, ventas INT
)

INSERT INTO #PruebaLAG_LEAD VALUES
(1, '20120101', 10),
(2, '20120102', 11),
(3, '20120103', 13),
(4, '20120104', 8),
(5, '20120105', 10),
(6, '20120106', 15),
(7, '20120107', 10)

-- Como ignorar el registro inicial para que no quede el NULL

;WITH cte AS(
SELECT ROW_NUMBER()OVER(ORDER BY dia) rn, *, LAG(ventas, 1, null)OVER(ORDER BY dia) as ventaAnterior, 
    ventas - LAG(ventas, 1)OVER(ORDER BY dia) as ventaDiferencia
  FROM #PruebaLAG_LEAD
)
SELECT * 
  FROM cte
WHERE rn>1

DROP TABLE #PruebaLAG_LEAD

La función LEAD es equivalente pero para el siguiente registro en lugar del anterior así que no entraremos en mas detalle.

Pro
Hace que sea mucho mas simple el tipo de cálculos de valores precedentes
Es mas declarativo y eficiente que la vieja forma
Es muy flexible, puede ser variable el segundo campo, pudiendo utilizar algo rebuscado como comparar todo contra el principio de mes
Contra
Es incomodo si no se quiere incluir el registro inicial

Mas info de LAG: http://msdn.microsoft.com/es-us/library/hh231256(v=sql.110).aspx
Mas info de LEAD: http://msdn.microsoft.com/es-us/library/hh213125(v=sql.110).aspx

Saber el fin de mes en SQL 2012: EOMONTH

2012-01-30T09:33:00.000-03:00

Buenas a todos!!! En este articulo continuaremos contando las novedades del TSQL de SQL 2012.
En esta oportunidad quiero presentarles la función EOMONTH. Esta función se puede utilizar de dos maneras, con un solo parámetro o con dos.
Cuando la utilizamos con un solo parámetro, éste debe ser de tipo fecha y la función lo que retorna es el último día del mes correspondiente al parámetro. Esto en versiones anteriores de SQL se podía hacer de varias formas, pero de todas era bastante engorroso.
En el ejemplo veremos alguna de esas alternativas.
La segunda opción, muy práctica por cierto, es pasandole la fecha y otro parámetro que indica cuantos meses sumar o restar. Esto lo que hará es retornar el fin de mes siguiente. Veamos como funcionan.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 18/01/12
-- Description: Saber el fin de mes en SQL 2012
-- =============================================

-- Primero declaramos una variable de tipo fecha para la prueba. Puede ser datetime sin inconveniente.
DECLARE @date DATE;
SET @date = GETDATE();

-- Veamos una de las opciones de como calcular el principio de mes y fin de mes con la sintaxis permitida en versiones anteriores de SQL.
-- Repito, esta es una forma de hacerlo pero hay varias
SELECT @date AS FechaOriginal,
    DATEADD(DAY,-1 * (DATEPART(DAY, @date))+1, @date) AS PrincipioDeMes,
    DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(DAY,-1 * (DATEPART(DAY, @date))+1, @date))) AS FinDeMes
        

-- Ahora con la función EOMONTH podemos expresarlo de forma directa
SELECT EOMONTH ( @date ) as FinDeMes,
       EOMONTH ( @date, 1 ) as FinSiguienteMes,
       EOMONTH ( @date, -1 ) as FinMesAnterior;

-- Y si en cambio no queremos el fin de mes sino el comienzo, podemos hacer lo siguiente
-- -> Calcular el fin del mes anterior y sumarle un dia
SELECT DATEADD(DAY, 1, EOMONTH ( @date, -1 )) as PrincipioDeMes;

Pro

Permite escribir de forma muy declarativa y simple el fin de mes, algo que es muy utilizado
La sintaxis es muy intuitiva

Contra

No entiendo porque no se aplica lo mismo para principio de mes, esto hacer perder ortogonalidad. Podria ser otra o un parámetro que indique si se desea el fin o el comienzo de mes

Para mas información: http://msdn.microsoft.com/en-us/library/hh213020%28v=sql.110%29.aspx

Funciones de fechas en SQL 2012

2012-01-24T08:57:00.000-03:00

En éste articulo hablaremos de las nuevas funciones para manejo de fechas que se incoporan en SQL 2012. Son un conjunto de funciones que se llaman xxxFROMPARTS y lo que permiten es ingresar los valores numéricos que forman cada parte de una fecha y retornar en el tipo fecha que corresponda.
Estas funciones son 6, una para que tipo de fecha que maneja el SQL Server. Tomemos como ejemplo la primera para poder fijar la idea.
Si yo tengo tres campos numericos, en uno guardo el dia, en otro el mes y en otro el año, antes debíamos hacer varias operaciones para poder pasar eso a una fecha (string mediante), lo cual siempre me pareció extraño, porque el camino inverso (dada una fecha obtener el número del mes) era posible facilmente con la función DATEPART.
Ahora utilizando la función DATEFROMPARTS le pasamos esos 3 argumentos y nos retorna algo de tipo fecha.
Si en lugar de hacerlo para una fecha lo queremos hacer para una hora, tenemos TIMEFROMPARTS, y así para que tipo de datos "tiempo" que tiene el SQL Server.
Veamos el ejemplo:

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 13/01/12
-- Description: Manejo de fechas en SQL 2012
-- =============================================

SELECT DATEFROMPARTS             ( 2010, 12, 31 ) AS Salida;
SELECT TIMEFROMPARTS             ( 23, 59, 59, 0, 0 ) AS Salida;

SELECT SMALLDATETIMEFROMPARTS    ( 2010, 12, 31, 23, 59 ) AS Salida
SELECT DATETIMEFROMPARTS         ( 2010, 12, 31, 23, 59, 59, 0 ) AS Salida;
SELECT DATETIME2FROMPARTS        ( 2010, 12, 31, 23, 59, 59, 0, 0 ) AS Salida;
SELECT DATETIMEOFFSETFROMPARTS   ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Salida;

Pros

Es muy cómodo de utilizar
Era algo deseable visto que el camino inverso (fecha->número) ya era posible con DATEPART
Deja de ser necesario y trabajoso pasar por un VARCHAR para poder transformar una fecha desde sus partes numéricas

Contras

Para una transformación es DATEPART pero la vuelta es con otra, no junta los conceptos
Muchas funciones para lo mismo en lugar de hacer una sobrecarga y que lo defina el motor
Funciones muy "sensibles". Siempre esperan todos los parámetros. Calculo que ésto es consecuencia del punto anterior

Espero sus opiniones.
Saludos!

OFFSET y FETCH, paginando sin el TOP en SQL 2012

2012-01-17T09:59:00.000-03:00

Siguiendo con los artículos sobre nuevas funcionalidades para desarrolladores en SQL 2012 ahora les presento el concepto de paginación.
Hay otras bases del mercado que ya hace bastante tiempo tienen este concepto, pero en SQL Server si queriamos no traer toda la consulta sino solamente una parte la única herramienta con la que contabamos era la sentencia TOP (o algunas trampas para poder simular la paginacion). La sentencia TOP nos permitía traer solamente los N primeros valores de una consulta. Pero es muy habitual tener una interfaz de cara al cliente que le muestra un gran resultado por páginas, entonces desearía poder traer solamente lo que voy a mostrar, y para la primer página me sirve el TOP pero para el resto no.
Con el objetivo de apalear este problema es que se incorporó OFFSET y FETCH. Al utilizar estas sentencias en lugar de devolver todos los valores, devuelve a partir del valor indicado en OFFSET. ¿Cuantos valores retirna? la cantidad que se indique en FETCH.
Para que esto funcione es necesario que la consulta tenga un ORDER BY. Veamos un ejemplo.

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 13/01/12
-- Description: Paginacion en SQL 2012
-- =============================================

-- Llenemos una tabla con valores para poder probar
CREATE TABLE #Temp(id INT PRIMARY KEY, campo2 INT, campo3 VARCHAR(100))
DECLARE @i INT
SET @i = 0
WHILE @i<100
BEGIN
    INSERT INTO #Temp
    SELECT @i, @i*7, 'Fila ' + CAST(@i AS VARCHAR)
    SET @i = @i + 1
END
GO

-- Primera prueba: ver el contenido
SELECT * FROM #Temp
GO

-- Forma vieja de paginar
SELECT TOP 10 * 
  FROM #Temp
ORDER BY campo2

SELECT TOP 10 * 
  FROM #Temp
 WHERE campo2>=70
ORDER BY campo2

-- Forma nueva de paginar
-- Obtengo los primeros 10 resultados
SELECT * 
  FROM #Temp
ORDER BY campo2
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

-- Obtengo los siguientes 10!!!
SELECT * 
  FROM #Temp
ORDER BY campo2
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
GO

DROP TABLE #Temp
GO

Según la documentación oficial se recomienda utilizar FETCH en lugar del viejo TOP. El motivo es que con el TOP es necesario ejecutar tantas consultas como páginas si deseen, y siempre es necesario hacer nuevamente una búsqueda. Con el FETCH el motor se encarga de que cuando volvamos a pedir algo no requiera reprocesarlo sino utilizar lo que ya se calculó.
Para que esto funcione correctamente se deben cumplir algunas simples condiciones:

La informacion a consultar no debe cambiar entre consulta y consulta
El ORDER BY debería ser sobre una columna (o varias) que sea única

Si todo esto se cumple (lo cual no es complicado) podremos comenzar a disfrutar de los beneficios del FETCH.

Manejo de excepciones en SQL 2012

2012-01-13T09:35:00.001-03:00

Antes que nada quiero saludar a todo el mundo luego de un mes de descanzo totalmente alejado de la pc! Ahora con todas las ganas de comenzar el 2012! Ahora a comenzar con el artículo.
En SQL 2008R2 o inferior la forma que teniamos de hacer el manejo de errores era utilizando el TRY y CATCH y luego en caso de ser necesario generar un error con RAISE ERROR. Cualquiera que esté familiarizado con el paradigma de objetos también lo está con el concepto de "lanzar excepciones". Este paso es lo que se ha implementado en esta nueva versión de SQL con la instrucción THROW.
Veamos un ejemplo de como manejabamos las excepciones antes:

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 13/01/12
-- Description: Ejemplo de manejo de errores en SQL2012
-- =============================================

BEGIN TRY
 SELECT 1/0
END TRY
BEGIN CATCH
    DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
    SELECT @ErrMsg = ERROR_MESSAGE(),
        @ErrSeverity = ERROR_SEVERITY()

    RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
GO

En cambio ahora con esta nueva instrucción sería:

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 13/01/12
-- Description: Ejemplo de manejo de errores en SQL2012
-- =============================================

BEGIN TRY
 SELECT 1/0
END TRY
BEGIN CATCH
     THROW 51000, 'Mensaje de error a elección!', 1;
END CATCH
GO

Nuestra lógica no sufrió grandes cambios pero esto permite empezar a pensar en un paradigma de excepciones nuestro código.
Algunas diferencias entre el comportamiento de una y otra son:
RAISE ERROR

El error debe estar definido en la sys.messages
Los mensajes pueden utilizar el caracter % para ser parametrizados
Se puede definir la severidad del error, siendo esta cualquier número, incluyendo las mas graves
No se puede arrojar en cadena (hacia los llamadores) la excepción. Cuando se genera una nueva "pisa" la anterior

En cambio con la nueva instrucción THROW

El mensaje de error se define al momento de lanzar la excepción
Los mensajes NO pueden ser parametrizados. Tener en cuenta que al generarse en el momento los mensajes, esto no debería ser un gran problema
La severidad del error es siempre la misma, severidad 16
Un sp que captura una excepción puede relanzarla tal como en cualquier entorno de objetos

A mi ver es algo muy cómodo. Lo bueno es que no deberán salir todos corriendo a cambiar su código porque aún se mantendrá compatibilidad con ambas.
Espero que les haya servido.

Para mas info:
http://msdn.microsoft.com/en-us/library/ee677615%28v=sql.110%29.aspx

Nuevas funciones TRY_PARSE y TRY_CONVERT en SQL 2012

2011-11-29T10:31:00.001-03:00

Hay muchas funciones nuevas en SQL 2012 apuntadas al desarrollo, y me gustaría ir dedicandole un lugar. La primera que veremos es TRY_PARSE y TRY_CONVERT que permiten ejecutar lo mismo que sus predecesoras pero sin dar error cuando la conversión no se puede realizar. Veremos algunos ejemplos de su aplicación:


-- =============================================
-- Create:        Andrés Aiello
-- Create date: 17/11/11
-- Description: Ejemplo de manejo de errores en SQL2012 aca Denali
-- =============================================

-- Llenemos una tabla con valores para poder probar
CREATE TABLE #Temp(id INT PRIMARY KEY, campo2 INT, campo3 VARCHAR(100))
INSERT INTO #Temp SELECT 1, 1, '1'
INSERT INTO #Temp SELECT 2, 1, '001'
INSERT INTO #Temp SELECT 3, 1, 'fake1'
INSERT INTO #Temp SELECT 4, 1, 'fullfake'
INSERT INTO #Temp SELECT 5, 1, '01/31/2012'
INSERT INTO #Temp SELECT 6, 1, '31/01/2012'

-- Primera prueba: ver el contenido
SELECT * FROM #Temp

-- Segunda prueba: Castear la columna 3 a entero usando la vieja forma
SELECT CONVERT(INT, campo3) FROM #Temp
/* Resultado:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'fake1' to data type int.
*/

-- Tercera prueba: Hacer lo mismo pero con Try_Convert
SELECT TRY_CONVERT(INT, campo3) FROM #Temp
-- No hay error!!! Los dos primeros registros se convirtieron y los siguientes 3 son nulos

-- Cuarta prueba: Hacer lo mismo pero con fechas
SELECT CONVERT(DATE, campo3) FROM #Temp
/* Resultado:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
*/

-- Cuarta prueba: Hacer lo mismo pero con Parse
SELECT PARSE(campo3 AS DATETIME) FROM #Temp
-- Error!!! pero si usamos TRY_PARSE...
SELECT id, TRY_PARSE(campo3 AS DATETIME USING 'en-US') FROM #Temp
-- La salida parsea los valores que pudo y deja en nulo el resto!

-- Y si cambiamos la referencia cultural...
SELECT id, TRY_PARSE(campo3 AS DATETIME USING 'es-ES') FROM #Temp
-- Obtenemos otro de los registros!!! sin tener que recordar los odiosos codigos 101 etc

DROP TABLE #Temp

Mas info: http://msdn.microsoft.com/en-us/library/hh213126%28v=sql.110%29.aspx

Listar todas las fechas de un intervalo en TSQL

2011-11-23T14:47:00.001-03:00

Hace poco en un foro alguien preguntó como hacer una consulta SQL para obtener todas las fechas en un intervalo dado, y como me gustó la solución la reescribi de forma mas generica y me gustaría compartirla con todos



-- Declaro las variables
DECLARE @desde DATETIME
DECLARE @hasta DATETIME
-- Seteo un valor inicial
SELECT @desde='20110101',@hasta='20111101';
-- Primero calculo la cantidad de dias y luego listo tantos numeros como diferencia de dias
WITH 
    CantDias AS (SELECT DATEDIFF(DAY, @desde, @hasta) Cantidad),
    Numeros AS(
        SELECT 1 numero
        UNION ALL
        SELECT n.numero + 1 numero
          FROM Numeros n, CantDias c 
        WHERE n.numero<=c.Cantidad
)
SELECT DATEADD(DAY, Numeros.numero-1, @desde) FROM Numeros OPTION (MAXRECURSION 0);

Microsoft SQL Server 2012 Release Candidate 0 (RC0)

2011-11-17T14:58:00.001-03:00

Ya se encuentra disponible el RC0 del SQL2012!!! para el que lo quiera bajar dejo el link:
http://www.microsoft.com/download/en/details.aspx?id=28145

Entre esta semana y la que viene estaré subiendo algunos pequeños artículos apuntando a las novedades, pero para variar voy a enfocar en los cambios que afectan a los desarrolladores.

Como saber si un dia es fin de semana en SQL

2011-11-16T14:02:00.001-03:00

Hace poco en un foro vi dos veces seguida esta pregunta así que lo comparto con todos. La pregunta era como hacer para obtener si un día es fin de semana o no. Esto era para poder sumar cierta información solamente de los días hábiles.

La solución propuesta es la siguiente:

DECLARE @fecha1 DATETIME = '20111111 14:30'
SELECT CASE WHEN (DATEPART(WEEKDAY, @fecha1) + @@DATEFIRST)%7 IN (0, 1) THEN 'Fin de semana' ELSE 'Dia laboral' END

Es interesante como hay que tener en cuenta DATEFIRST para independizarse de como se ha configurado el servidor.

Licencias de SQL 2012

2011-11-10T13:16:00.000-03:00

Dejo un link explicando los cambios que se vienen en el licenciamiento de SQL 2012
Link oficial: http://www.microsoft.com/sqlserver/en/us/future-editions/sql2012-licensing.aspx

Buen link al respecto:

http://blogs.flexerasoftware.com/elo/2011/11/revolutionary-changes-to-microsoft-sql-server-2012-license-models.html

Creo que lo que mas me apena es que nos deja la version Datacenter, pero era un final anunciado...

Recompilando vistas en SQL Server

2011-11-09T11:05:00.000-03:00

Esta vez haré un post mas corto de lo habitual. Ayer a un colega se le presentó una situación, que por un cambio en varias cosas de su motor debía recompilar una vista. Esto es facil utilizando

EXEC SP_REFRESHVIEW [miVista];

El problema es que ocurre cuando en lugar de una vista son cientas y potencialmente divididas en varias bases de datos. La solución que le brindé es un TSQL que le generase un script con todos los refresh para que despues pueda ejecutarlo y guardarlo como documentación. Para esto me valí de un procedimiento no documentado del sql que es el sp_MSforeachdb.

Les comparto como quedó el script final

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 09/11/11
-- Description: Como recompilar todas las vistas
-- =============================================

sp_MSforeachdb '
SELECT ''USE [?]
GO 
EXEC SP_REFRESHVIEW [''+NAME+''];
GO'' FROM SYSOBJECTS WHERE TYPE = ''V'' AND NOT ''?'' IN (''master'', ''tempdb'', ''msdb'', ''model'');
'

More: http://msdn.microsoft.com/en-us/library/ms187821.aspx

Aplicar filtros al sp_who2 en SQL Server

2011-10-25T15:16:00.001-03:00

Muchas veces usamos el sp_who2 para ver las conexiones activas y determinada información sobre esta, pero esto es comodo si los resultados son pocos, en el caso de ser muchos registros, cientos o miles esto ya no es aplicable.
Para estos casos deseariamos poder aplicar un filtro al sp_who2 pero esto lamentablemente no es posible. Lo que veremos en este ejemplo es como hacer un script, que podemos tener siempre a mano, que se encargue de aplicar los filtros.
El primer paso será crear una tabla temporal donde gaurdará los datos devueltos por el sp_who2. Como siempre primero controlo si la tabla ya existe para que no me arroje error al ejecutar.



-- =============================================
-- Create:        Andrés Aiello
-- Create date: 25/10/11
-- Description: Filtrar los resultados del sp_who2
-- =============================================

-- Creo la tabla temporaria donde guardaré la salida del sp_who
IF NOT OBJECT_ID('tempdb.dbo.#sp_who2') IS NULL
DROP TABLE dbo.#sp_who2

CREATE TABLE #sp_who2
    (SPID INT, 
    Status VARCHAR(1000) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(1000) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(1000) NULL, 
    ProgramName VARCHAR(1000) NULL, 
    SPID2 INT,
    REQUESTID INT) 

GO

Una vez creada guardo en ella la salida del sp_who2



-- Inserto los valores
INSERT INTO #sp_who2
EXEC sp_who2
GO

El paso siguiente es solamente hacer un select sobre dicha tabla, pero ahora aplicando filtro u orden, es decir lo mismo que haría en cualquier tabla. Por ejemplo filtrar solo las conexiones de mi usuario ordenadas por fecha.



SELECT *
  FROM #sp_who2
-- Pongo el filtro que deseo
 WHERE Login = 'AAIELLO'
-- O el orden que deseo!!!
ORDER BY LastBatch DESC
GO

Por último elimino las estructuras creadas para no dejar "sucia" la base.



 -- Elimino las estructuras temporarias
DROP TABLE #sp_who2
GO

Espero que les sirva!!! dejo a continuación el script copleto para que sea comodo de guardar.

 

-- =============================================
-- Create:        Andrés Aiello
-- Create date: 25/10/11
-- Description: Filtrar los resultados del sp_who2
-- =============================================

-- Creo la tabla temporaria donde guardaré la salida del sp_who
IF NOT OBJECT_ID('tempdb.dbo.#sp_who2') IS NULL
DROP TABLE dbo.#sp_who2

CREATE TABLE #sp_who2
    (SPID INT, 
    Status VARCHAR(1000) NULL, 
    Login SYSNAME NULL, 
    HostName SYSNAME NULL, 
    BlkBy SYSNAME NULL, 
    DBName SYSNAME NULL, 
    Command VARCHAR(1000) NULL, 
    CPUTime INT NULL, 
    DiskIO INT NULL, 
    LastBatch VARCHAR(1000) NULL, 
    ProgramName VARCHAR(1000) NULL, 
    SPID2 INT,
    REQUESTID INT) 

GO

-- Inserto los valores
INSERT INTO #sp_who2
EXEC sp_who2
GO

SELECT *
  FROM #sp_who2
-- Pongo el filtro que deseo
 WHERE Login <> 'sa'
-- O el orden que deseo!!!
ORDER BY LastBatch DESC
GO

-- Elimino las estructuras temporarias
DROP TABLE #sp_who2
GO

Screencast: Local Server Groups en SQL

2011-10-23T15:16:00.000-03:00

Hola!!! para los que les pareció interesante el artículo de como ejecutar un mismo script sql sobre varios servidores, acá dejo un screencast explicando de forma mas didáctica como hacer esto.
Saludos!

Obtener la IP del servidor desde SQL

2011-10-21T10:00:00.001-03:00

En esta oportunidad me gustaría hacer un pequeño artículo sobre como obtener la ip del servidor en el cual se encuentra corriendo el SQL. La gracia es hacerlo sin salir al sistema operativo o el registro obviamente.
La primer opción es:



-- =============================================
-- Create:        Andrés Aiello
-- Create date: 17/10/11
-- Description: Como obtener la ip y puerto del servidor
-- =============================================

-- Obtengo la info basandome en mi conexion
SELECT local_net_address, local_tcp_port
  FROM sys.dm_exec_connections c
 WHERE c.session_id = @@SPID

Esta opción lo que hace es buscar en la metadata de la conexion que yo tengo establecida cual es la ip de destino. Suena muy práctico y lo es en la mayoría de los casos. Esto puedo usarlo tanto de forma directa como dentro de un sp.
Ahora bien, si deseo programar esto para, por ejemplo, guardar una auditoría, entonces llamaré a el sp correspondiente desde un job (suena lógico). Y sorpresivamente me dará como resultado NULL. ¿a que se debe? Si miramos con mas detalle, cuando ejecutamos desde el job hay una diferencia en una de las columnas que no nos estamos trayendo, y esta es net_transport. En esa columna el primer caso nos diría TCP porque es una conexion TCP/IP. Pero en el caso del job nos arrojaría "shared memory".
Para solucionar esto podemos reemplazar nuestra consulta inicial por:



-- Obtengo la info basandome en cualquier conexion
SELECT TOP 1 local_net_address, local_tcp_port, net_transport
  FROM sys.dm_exec_connections c
 ORDER BY local_net_address DESC

Para mas info: http://msdn.microsoft.com/en-us/library/ms181509.aspx

Post CodeCamp: Codigo seguro en SQL

2011-10-18T09:22:00.004-03:00

Que buen sábado!!! la pasé genial con mucha gente copada, algunos que esperaba encontrarme y otros que no. En particular en mi presentación me gustó mucho el público que se copo con el tema, y por los comentarios se notó que es algo que muchos viven en el día a día. Gracias a todos!!! en particular a Microsoft, Daniel Levi y Miguel Saez por invitarme a participar.

Y como no podía faltar unas fotos de la charla!!!

Ejecutar el mismo script en multiples servidores SQL

2011-10-17T10:30:00.002-03:00

En esta oportunidad quiero comentar algo muy comodo y poco usado del Managment y es la opción de ejecutar el mismo script en varios servidores SQL al mismo tiempo.

Para esto abriremos el Managment Studio y seleccionaremos "Registered Servers" en la parte inferior izquierda como se ve en la siguiente imagen

Eso me muestra dos subcarpetas:

Local Servers Groups
Central Managment Servers

Lo ideal es utilizar el CMS, pero esto requiere tener un servidor dedicado a esta tarea y lo veremos en otro post. La forma que encararemos hoy es con Local Server Groups, donde uno puede ir agregando diferentes instancias simplemente haciendo click derecho y escribiendo "New Server Registration".

Una vez que se hayan agregado todos los que uno quiera (estos quedan guardados de forma local en la pc) podemos hacer click derecho y elegir "New query". Nos abrirá una ventana en blanco pero a diferencia de la ventana standard, esta tendrá la barra inferior rosa en lugar de amarillo. Esto indica que lo que se ejecute en esa ventana se disparará contra todos los servidores registrados.

Es importante jugar también con la opción de registrar "grupos", para de esta forma hacer click derecho sobre el grupo y que la consulta no vaya contra todos los servidores registrados sino solamente contra los del grupo.

Saludos!

CodeCampBA: Charla SQL

2011-10-15T23:30:00.003-03:00

Muchas gracias a todos los que estuvieron en la charla!!! les dejo el ppt y los ejemplos y prometo en la semana subir algunas fotos ;)

Saludos y gracias!!!

https://sites.google.com/site/aaiello/CodeCamp_Que_No_Te_Ataquen.rar

Conectarse a SQL Server desde UNIX

2011-10-13T12:20:00.002-03:00

Les comparto un artículo que encontré y me pareció interesante que explica diferentes formas de conectarse al SQL Server desde UNIX

http://www.sommarskog.se/mssqlperl/unix.html

SQL Server 2008 SP3!

2011-10-07T09:44:00.001-03:00

Está disponible el SP3 del SQL Server 2008:
http://blogs.technet.com/b/dataplatforminsider/archive/2011/10/06/sql-server-2008-sp3-is-now-available.aspx

Setea el check_policy para los logins que no lo tengan

2011-10-04T15:45:00.000-03:00

Esta vez voy a hacer un post bien simple pero útil. Hay dos opciones muy interesantes al crear logins que son el check_policy y el check_expiration. La primera nos permite controlar que las contraseñas ingresadas por los usuarios cumplan con los requisitos de seguridad de complejidad de contraseña mientras que la segunda habilita que las contraseñas expiran una vez vencido cierto tiempo. Es importante notar (error muy común) que el check_policy se evalúa AL MOMENTO de setear la contraseña. Esto quiere decir que si creo un usuario, le pongo como contraseña '1' y luego habilito el check_poclicy, dicho usuario va a poder conectarse sin problema. Pero sin en cambio creo el usuario, habilito el check_policy y luego pongo como contraseña '1' no me dejará definirla por no cumplir los requisitos de seguridad. En el primer caso la contraseña '1' le permitirá loguearse pero al momento de querer cambiarla no podrá poner como contraseña '2' sino que ahi si deberá cumplir las reglas definidas.

Una vez terminada la introducción y dejando los conceptos en claro vamos a los bifes. La idea es hacer una consulta que me liste todos los usuarios que no cumplen esta buena práctica y poder cambiar su estado. El script que armé es el siguiente:


-- =============================================
-- Author: Andrés Aiello
-- Create date: 02/05/2011
-- Setea el check_policy para los logins que no lo tengan
-- =============================================

DECLARE @SQLQuery varchar(1000)
DECLARE @LoginName varchar(255)

DECLARE cLogins CURSOR FORWARD_ONLY FOR
SELECT name
--,is_policy_checked,'ALTER LOGIN [' + name + '] WITH CHECK_POLICY=ON' Query
FROM sys.sql_logins
WHERE is_policy_checked = 0
/* Ignoro los logins que considere que por algun motivo no deben ser tenidos en cuenta */
AND NOT name IN ('xxxxxxxxxxx')
ORDER BY NAME

OPEN cLogins
FETCH NEXT FROM cLogins INTO @LoginName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLQuery = 'ALTER LOGIN [' + @LoginName + '] WITH CHECK_POLICY=ON'
PRINT @SQLQuery
FETCH NEXT FROM cLogins INTO @LoginName
END
CLOSE cLogins
DEALLOCATE cLogins

Este script como pueden ver no ejecuta el código sino que lo saca por la salida. Esto se debe a que una buena idea sería previo a hacer los cambios documentarlos salvando el script y luego ejecutarlo.

En este ejemplo fue para el check_policy, pero si quisieran hacer lo mismo para el expiration es igual solamente que el campo a filtrar es is_expiration_checked.
Andrés

Controlar CMDShell en SQL

2011-10-04T09:33:00.004-03:00

Todos saben que no es deseable tener el CMDShell activado en los servidores, pero hay veces que uno llega a un server y ya se encuentra activado y ahí surge la pregunta de que hacer. La primer medida es cambiar las credenciales para que se ejecute con permisos mínimos, pero no hablaremos de eso ahora. Junto con esto es importante identificar donde se utiliza visto que los permisos dependerán de las llamadas que haga. Como no podemos recorrer base por base viendo si se utiliza, les dejo una consulta que hice para este fin

EXEC sp_MSForeachdb 'SELECT ''?'' DBName, text FROM [?].SYS.SYSCOMMENTS WHERE text LIKE ''%XP_CMDSHELL%'''

Esta consulta retorna todos los lugares donde se utiliza el cmdshell. Hay algunas referencias del sistema que las deberán ignorar.

Saludos!

nota: recordar que la función sp_MSForeachdb es una función no documentada

Renombrar filename de una base SQL

2011-09-21T14:12:00.000-03:00

Hoy quiero contar un problema que me presentó un amigo hace unos días y me parece que a muchos les puede pasar. La persona en cuestión muchas veces había renombrado una base de datos, o había cambiado su nombre lógico, pero las veces que tenía que cambiar el nombre físico el procedimiento era:
1) Deatach de la base
2) Renombrado del file
3) Atach de la base

Es procedimiento es un poco extremo y aprovecharé para explicarles como hacerlo de forma mas simple.
El primer paso es asegurarme cuales son los files que tengo definidos en la base, en el ejemplo consideraremos la base "Librería". Para obtener esta información ejecutamos:


SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID('Libreria');

Ahí obtendremos como resultado tres columnas, Name (nombre lógico), CurrentLocation (donde se encuentra actualmente el file) y state_desc (estado del file). Es importante que se recuerde que el nombre lógico es como se refenciará internamente al archivo, mientras que el físico es su nombre real. Puedo cambiar uno sin cambiar el otro.
La consulta dará como mínimo dos registros de salida, una para el archivo mdf (data) y otra para el ldf (log).

Supongamos que quiero cambiar ambos archivos y renombrarlos LibreriaDATA y LibreriaLOG respectivamente, entonces debo poner offline la base y luego ejecutar la siguiente sentencia:


-- Pongo a la base offline
ALTER DATABASE Libreria SET OFFLINE WITH ROLLBACK IMMEDIATE

-- Cambio los nombres a donde apuntan. El NAME es el nombre logico que quiero modificar y el FILENAME es el NUEVO nombre fisico
ALTER DATABASE Libreria
MODIFY FILE (NAME = Libreria, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\LibreriaDATA.mdf' )
ALTER DATABASE Libreria
MODIFY FILE (NAME = Libreria_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA\LibreriaLOG.LDF' )
-- Muevo los archivos a nivel sistema operativo

Si todo está ok pongo la base nuevamente en línea. Es importante que noten que puedo hacer el alter apuntando a un archivo inexistente, el control se hará al momento de poner la base online.


-- Una vez renombrados la pongo nuevamente en linea
ALTER DATABASE Libreria SET ONLINE

-- Controlo que todo quede como yo quería
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files

Si todo está ok ya terminé mi trabajo. En caso de que haya por error apuntado a un archivo inexistente (ej. me faltó renombrar un archivo) la base no se pondrá online arrojando el siguiente error:

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file

Es una buena práctica al finalizar ejecutar nuevamente la consulta inicial y corroborar que realmente todo quedó como queríamos que quedara.
Espero que les sirva!

Que no te ataquen!!! Desarrollando código seguro en SQL

2011-09-21T08:42:00.003-03:00

Les dejo la invitación al Codecamp de este año.Estaré dando una charla sobre código seguro en SQL junto a mi amigo Mariano Alvarez (http://blog.josemarianoalvarez.com).Espero que pueda ir mucha gente!!!!

Screencast: CTE recursivas en SQL Server

2011-09-12T21:37:00.001-03:00

Mi primer Screencast!!! Espero que les guste mi primer screencast, trata sobre CTE recursivas (ver post anterior)

La recursion es divina - Consultas recursivas en SQL

2011-09-12T13:14:00.000-03:00

El lenguaje SQL no solía ser lo mas comodo para realizar consultas en donde participe el concepto de "recursión", pero claramente algunos problemas son muy incomodos si los abordamos de otra forma. Por suerte para nosotros, desde la aparición de las CTE, esto ha cambiado y ahora es "simple" realizar consultas recursivas, veremos un ejemplo de como podemos realizarlo.

Consideremos la siguiente tabla con datos de ejemplo:


CREATE TABLE #Empleados(idEmpleado INT, Nombre VARCHAR(10), idJefe INT)

INSERT INTO #Empleados
SELECT 0, 'Bob', null UNION
SELECT 1, 'Tom', 0 UNION
SELECT 2, 'Joe', null UNION
SELECT 3, 'John', 2 UNION
SELECT 4, 'Ringo', 1 UNION
SELECT 5, 'Paul', 4 

SELECT * FROM #Empleados

Esta tabla contiene los empleados de una empresa e indica sus respectivos jefes. Lo divertido es que los jefes son también empleados, por lo tanto son registros de la misma tabla.

Supongamos que quiero averiguar el nombre del jefe de cada empleado. Esto lo puedo realizar con un "self join" sin necesidad de recursividad.


SELECT e.Nombre Empleado, j.Nombre Jefe
FROM #Empleados e
INNER JOIN #Empleados j ON e.idJefe=j.idEmpleado;

Pero ahora supongamos que quiero una consulta mas compleja, una que me retorne no el jefe directo, sino el jefe superior de cada empleado. Cualquiera que tenga conocimientos de algoritmos sabe que la mejor forma de responder a esta pregunta es con una estructura recursiva. No pretendo en éste artículo escribir que es la recursión, sino como utilizarla en SQL, así que daré por sentado que saben de que hablo.

Una estructura recursiva requiere básicamente de dos partes:

1) El caso base

2) El caso recursivo

Para escribir esto utilizaremos una CTE que tendrá estas dos partes, y deben unirse con un UNION ALL. La primer parte será el caso base (en el WHERE debo filtrar para que lo sea) y en la segunda el caso recursivo, el cual hace un JOIN con la CTE. Este join es lo que indica que es un caso recursivo. Nótese que defino la cte utilizandola a ella misma, por eso mismo es recursiva.


WITH cte
AS(
-- Caso base
SELECT e.idEmpleado, e.Nombre Nombre, e.idEmpleado Jefe
FROM #Empleados e
WHERE e.idJefe IS NULL
-- Fin Caso base
UNION ALL
-- Caso recursivo
SELECT e.idEmpleado, e.Nombre Nombre, j.Jefe
FROM #Empleados e
INNER JOIN cte j ON e.idJefe=j.idEmpleado
-- Fin Caso recursivo
)
SELECT c.Nombre Empleado, j.Nombre Jefe
FROM cte c
INNER JOIN #Empleados j ON c.Jefe=j.idEmpleado

DROP TABLE #Empleados

Con esto obtengo el código del jefe superior de cada uno, entonces en el SELECT lo cruzo con la tabla de empleados para obtener su nombre. Parece medio confuso pero es cuestión de acostumbrarse.

Hay algunos inconvenientes que pueden presentarse, pero eso lo comentaré en el próximo artículo.

Como leer desde SQL los MeasureGroups de AS

2011-09-08T11:47:00.000-03:00

Hace poco me pasó que necesitaba trabajar con los id de los MeasureGroups desde SQL, y me encontré con que las consultas que podía hacer retornaban siempre el nombre pero no el id. Por este motivo hice un script de powershell que me brinde la información y la cargue en una tabla. La parte de SQL la hice rápido y podría mejorarse con objetos de powershell pero lo importante es lo otro.

#Datos del ejemplo:

#Servidor de AS: AS1

#Base en AS: DB1

#Cubo: Cub1

## El primer parametro es la instancia donde guarda la informacion, segundo base

$sqlServer = $args[0];
$dbName = $args[1];
$tbl = "mgRecolectadas"

# Recolecto la informacion del AS
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") > $null
[Microsoft.AnalysisServices.Server]$svr = new-Object([Microsoft.AnalysisServices.Server])

# Tomo uno como ejemplo
$svr.Connect("AS1")
$DatabaseID = "DB1"
$db = $svr.Databases.Item($DatabaseID)
$cubo = $db.Cubes.Item("Cub1")

#Variables donde armaré la cadena de SQL
$mgid = ""
$mgname = ""
$sql = ""
#Recorro la coleccion de MeasureGroups

foreach ($mg in $cubo.MeasureGroups){

    #Obtengo el ID
    $mgid = $mg.id
    $mgname = $mg.name
    #Armo la cadena
    if ($sql){
        $sql = "$sql UNION "
    }
    $sql = "$sql
        SELECT '$mgid' mg, '$mgname' mgname"
}
$svr.Disconnect()

## Establezco la conexion para guardar la info
$conn = New-Object System.Data.SqlClient.SqlConnection("Data Source=$sqlServer;Initial Catalog=$dbName; Integrated Security=SSPI")
$conn.Open()
$cmd = $conn.CreateCommand()

# Borro lo anterior
$cmd.CommandText = "TRUNCATE TABLE $tbl"
$null = $cmd.ExecuteNonQuery()
echo "Table truncada"

# Agrego todos los registros nuevos
$cmd.CommandText = "INSERT INTO $tbl SELECT * FROM ($sql) t"
$null = $cmd.ExecuteNonQuery()
echo "Insercion finalizada"

$conn.Close()

Restore de CDC en SQL 2008

2011-08-24T12:07:00.001-03:00

El cdc es una feature muy buena del SQL2008 visto que de forma sencilla nos permite llevar un registro de los cambios en una tabla, tanto sea para cuestiones de auditoría, procesamientos parciales de información, etc.

Así mismo, cuando se trabaja con cdc es normal que se deseen probar, testear, desarrollar procedimientos que utilicen esta información y naturalmente no queremos que accedan al ambiente productivo hasta estar testeadas. Aquí es cuando nos topamos que si hacemos un restore de una base con cdc en otra instancia no aparecen las tablas correspondientes. Para evitar esto debemos incluir la opción WITH KEEP_CDC al momento de hacer restore. No se si será poco usada, pero no se asusten si no es coloreada en el Managment Studio, funciona a la perfección.

El código debería quedar algo así:


RESTORE DATABASE [AIELLO_DB]
FROM DISK = N'D:\Backup\[AIELLO_DB][SQL2008].bak'
WITH
KEEP_CDC,
FILE = 1,
MOVE N'AIELLO_DB'
TO N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AIELLO_DB.mdf',
MOVE N'AIELLO_DB_log'
TO N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AIELLO_DB_log.ldf',
MOVE N'AIELLO_DB_cdc'
TO N'D:\MSSQL10.MSSQLSERVER\MSSQL\DATA\AIELLO_DB_cdc.ndf',
NOUNLOAD,
REPLACE,
STATS = 10

Con esto tendrán la información para poder explotarla, pero no se activará la captura de datos. Si desean que esto ocurra hay que incluir los jobs correspondientes con sys.sp_cdc_add_job aplicando tanto 'capture' como 'cleanup'.

Saludos,

Andrés

Para mas información:

RESTORE Arguments (Transact-SQL): http://msdn.microsoft.com/en-us/library/ms178615.aspx

sys.sp_cdc_add_job (Transact-SQL): http://msdn.microsoft.com/en-us/library/bb510626.aspx

SQL2008: Entendiendo el Waitresource

2011-08-11T12:28:00.005-03:00

Introduccion
Al ver los xml de bloqueos o deadlocks muchas veces aparecen números casi inentendibles, y uno de los importantes es el WAITRESOURCE. Este número codifica que objeto de la base de datos está participando en la pelea por el bloqueo. El recurso puede principalmente ser:

Una tabla
Una página
Una clave

Puede ser algunas cosas mas pero para simplificar el análisis nos enfocaremos en estos.

Manos a la obra

Lo primero que debemos hacer es identificar cual de las tres cosas es, lo cual es fácil leyendo el texto de wait resourse que puede ser:

“OBJECT: 19:1275867612:10”

“PAGE: 12:1:79868773”

“KEY: 12:397371816017920 (760119e06bff)”

En el primer caso es cuando se trata de una tabla. Para este caso (y para los siguientes) lo primero que debemos hacer es analizar el primer número antes del separador (:) . Este número en todos los casos indica el id de la base de datos, por lo tanto debo hacer la siguiente consulta para obtenerla:


SELECT * FROM sys.sysdatabases WHERE Dbid=19

Lo cual me da por resultado Mibase. Recordemos esto porque es común a los tres casos.

Ahora siguiendo con el análisis del primer caso la información brindada se debe analizar de la siguiente manera:

OBJECT: dbId:ObjectId:IndexId

Con la salvedad de que indexId vale 0 cuando se trata del heap y 1 cuando se trata de un índice cluster. En otro caso figurará el número del índice.

Para obtener esta información en un formato útil debería hacer las siguientes consultas:

-- Nota: debo estar situado en MiBase
SELECT OBJECT_NAME(1275867612) 
-- O bien
SELECT * FROM MiBase.sys.all_objects WHERE object_id = 1275867612
-- Y para buscar el índice en caso de ser mayor que 1:
SELECT * FROM MiBase.sys.indexes WHERE object_id=1275867612

De esta forma ya sabemos que table y que índice participaba en el bloqueo.

Para el caso 2 (página) comenzaremos averiguando la base de la misma forma y la siguiente información nos viene como:

PAGE: dbId:FileId:PageId

Si no tenemos nuestra base con varios archivos sino todo en uno del primary el segundo campo siempre será 1.

Para analizar el último debemos obtener la información de la página (79868773 en el ejemplo). Para esto tenemos dos formas:

DBCC TRACEON ( 3604 )

DBCC PAGE (12,1,79868773)

DBCC PAGE (12 , 1, 79868773) WITH TABLERESULTS,NO_INFOMSGS

En el primer caso es necesario el traceon porque sino no podemos ver la salida, la cual saldrá en modo texto. En el segundo no es necesario visto que la salida la veremos en modo tabla.

Sea cual fuera el caso que elegimos debemos buscar el renglon (o registro) que tenga la siguiente información:

m_objId (AllocUnitId.idObj) = 1051306955 m_indexId (AllocUnitId.idInd) = 7

y con esto obtengo el ObjectId para poder continuar mi análisis como en el caso anterior.

Por último nos queda el caso del índice (KEY: 12:397371816017920 (760119e06bff)). En este caso debemos leerlo como:

KEY: dbId:hObjecto (hash)

El hash puede ser ignorado, visto que no tenemos función para transformarlo (es la gracia de los hash!), así que nos quedaremos con el hObj. Este número se encuentra almacenado en la msdb así que lo podemos utilizar para calcular el objetcId:

SELECT * FROM MiBase.sys.partitions
WHERE hobt_id = 397371816017920

De esta consulta obtenemos el object_id y podemos hacer nuestro análisis de siempre.

Traduciendo jobs names de SQL

2011-07-26T13:02:00.005-03:00

Muchas veces estamos monitoreando en tiempo real nuestro SQL y hay cosas interesantes para observar. Este pequeño articulo es la puerta de entrada al de la semana que viene donde profundizaremos este tema.
Por ahora lo que quiero mencionar es simplemente un pequeño y usual problema . La forma mas simple es mediante el sp_who2, el cual en la columna BlkBy nos indicará si un proceso se encuentra proqueado y en la columna ProgramName podremos obtener que programa es la pobre víctima. Es muy importante que si este proceso fue lanzado por un job no veremos el nombre sino una secuencia extraña de números que debemos traducir, por ejemplo:

SQLAgent - TSQL JobStep (Job 0xF64F718235C7154DB6F21B5935D7218A : Step 1)

Para traducir esto debemos tomar la parte "numerica" del mensaje y copiarla en la siguiente consulta:


SELECT name
FROM msdb.dbo.sysjobs
WHERE job_id = CAST( 0xF64F718235C7154DB6F21B5935D7218A AS UNIQUEIDENTIFIER)

así obtendremos el nombre del job en cuestión.
Con esta info ahora solo nos resta cruazarla con las tablas del sistema para saber si un spid en particular está molestando o no, pero como dije eso lo veremos la semana siguiente.
Saludos,
Andrés

Cluster desde powershell

2011-07-18T11:34:00.000-03:00

Cuando se trabaja con SQL en entornos de alta disponibilidad es muy comun trabajar con windows en cluster y tener que lidiar con varias tareas que involucran tanto sea al sql como al cluster.
La forma tradicional de como hacer esto es con el comando Cluster.exe, pero para esto deberíamos habilitar el cmd shell lo cual nunca es recomendable.
Por suerte desde 2008 tenemos de forma nativa en los jobs incorporar rutinas de powershell, pero hasta ese entonces la única salida era seguir acudiendo al cluster.exe.
Esto ha cambiado con la salida de Windows Server 2008 R2 (no confundir con SQL Server 2008 R2!!!), donde se ha incorporado un modulo de cluster al powershell que hace muy comoda su adminstración.
Para utilizar este modulo primero debemos entrar a nuestra consola de powershell y ejecutar el siguiente comando:

Import-Module FailoverClusters

Con esto se incorporarán las funciones de cluster y podremos utilizarlas. En mi caso me ha servido porque en conjunto con SQL 2008 puedo llamarlas desde un job de sql configurandolo con rutina powershell y

esto es flexible y seguro.

Les adjunto un link de como pasar las tareas de Cluster.exe a powershell:

Como usarlo: http://technet.microsoft.com/en-us/library/ee619751%28WS.10%29.aspx

Mapeo: http://technet.microsoft.com/en-us/library/ee619744%28WS.10%29.aspx

====================================================================

When working with SQL in high availability is very common to work over windows cluster and dealing with various tasks involving both sql server and the windows cluster.
The traditional way of how to do this is with the command Cluster.exe, but for this should enable the cmd shell it is never recommended.
Luckily since 2008 We have powershell natively in jobs routines, but the only way out was to keep going to the cluster.exe.
This has changed with the release of Windows Server 2008 R2 (not to be confused with SQL Server 2008 R2!!!), which has a powershell module of the cluster that makes it very comfortable its management.
To use this module must first enter our powershell console and run the following command:

Import-Module FailoverClusters

This will incorporate the functions of cluster and we use them. In my case I have served it in conjunction with SQL 2008 I can call them from a sql job of setting it powershell with routine and that is

flexible and secure.

I attached a link of how to change tasks Cluster.exe to powershell:

How to use: http://technet.microsoft.com/en-us/library/ee619751%28WS.10%29.aspx
Mapping: http://technet.microsoft.com/en-us/library/ee619744%28WS.10%29.aspx

Restore de FILESTREAM

2011-07-07T16:13:00.000-03:00

El filestream es una funcionalidad nueva del SQL2008 que a veces es discutida pero a mi ver es muy buena en muchos casos. En esta oportunidad me gustaría hablar sobre un caso interesante y es como hacer para restorear una base que tiene campos filestream pero que no ha sido diseñado pensando en esto. ¿A que me refiero? Uno puede diseñar la base desde un principio alojando las tablas que contienen archivos en filegroups diferentes para asegurarse que en un restore parcial no afecten, pero no siempre las cosas nacen en este orden, muchas veces las aplicaciones vienen de hace tiempo y "llegan" al sql 2008 y en la tabla que se encuentra el binario tambien se encuentra otra información indispensable para el funcionamiento del sisetema. En estos casos no restorear el filegroup en cuestión es lo mismo que no restorear nada. Para estos casos el filestream es una buena solución visto que en cierto modo puedo hacer un filegroup "vertical".
Supongamos una tabla "cliente" que tenga los campos id, nombre, apellido, Documento, siendo el último un VARBINARY(MAX). Si esta tabla la pongo en un filegroup diferente y hago un restore del resto obtendría error al realizar la siguiente consulta:


SELECT id, nombre, apellido FROM Cliente

Restore con PARTIAL

Ahora supongamos que mi campo base tiene definido FILESTREAM y el campo Documento se encuentra albergado de esa forma. En este caso ante un problema por el cual deseo recuperar mi base podría hacer lo siguiente:


RESTORE DATABASE [AIELLO_DBA]
FROM DISK = N'D:\Prueba_fs.bak'
WITH REPLACE, PARTIAL, RECOVERY

Y ahora la consulta antes mencionada funcionaría sin problemas, solamente obtendría un error si intento acceder al campo documento, como sería con la siguiente consulta:


SELECT id, nombre, apellido, Documento FROM Cliente

Msg 670, Level 16, State 1, Line 2
Large object (LOB) data for table "dbo.Cliente" resides on an offline filegroup ("AIELLO_DBA_FS") that cannot be accessed.

Lo que logré es poner mi base en linea en un tiempo mucho menor. Pero lamentablemente para recuperar todo debo volver a hacer el restore full en otro momento.

Restore operativo por partes
No todo está perdido!! como dice el dicho, hecha la ley hecha la trampa, entonces lo que hice es lo siguiente.
En mi backup nocturno hago lo siguiente:


-- =============================================
-- Author: Andrés Aiello
-- Create date: 07/07/2011
-- Backup full poniendo readonly el filestream
-- =============================================

ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READONLY
BACKUP DATABASE [AIELLO_DBA]
TO DISK = N'D:\Prueba_fs_readonly.bak'
WITH NOFORMAT, NOINIT, NAME = N'PRUEBA FS', SKIP, NOREWIND, NOUNLOAD, STATS = 10
ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READWRITE

Por lo tanto el filegroup de los documentos es guardado como readonly. Al momento de hacer restore lo puedo hacer de dos formas:


--============== MODO 1 - Full
-- 7:38 min

RESTORE DATABASE [AIELLO_DBA]
FROM DISK = N'D:\Prueba_fs_readonly.bak'
WITH REPLACE, RECOVERY
ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READWRITE

Esta forma hace un restore completo de la forma tradicional, dejando el servicio bajo el tiempo que dure el restore (en una base donde el mayor porcentaje del espacio son archivos esto puede crecer mucho).
Y el plan b...


-- =============================================
-- Author: Andrés Aiello
-- Create date: 07/07/2011
-- Restore inteligente
-- =============================================
--============== MODO 2 - Sin los binarios
-- 0:46 
RESTORE DATABASE [AIELLO_DBA]
FROM DISK = N'D:\Prueba_fs_readonly.bak'
WITH REPLACE, PARTIAL, RECOVERY
ALTER DATABASE [AIELLO_DBA] SET RECOVERY FULL WITH NO_WAIT
-- Base online sin los binarios!!!
-- Recuperar todo...
-- 8:30
RESTORE DATABASE [AIELLO_DBA]
FILEGROUP='AIELLO_DBA_FS'
FROM DISK = N'D:\Prueba_fs_readonly.bak'
WITH REPLACE, RECOVERY
ALTER DATABASE [AIELLO_DBA] MODIFY FILEGROUP [AIELLO_DBA_FS] READWRITE

De esta forma la base en solo 45 segundos se encuentra operativa!!! y mientras se va utilizando se va recuperando en background los documentos del otro filegroup. Para evitar inconsistencias mientras se recupera esa parte el filegroup se mantiene en readonly.
Andrés

CREATE USER WITHOUT LOGIN

2011-04-08T11:33:00.000-03:00

Una instrucción no muy utilizada es la de create user without login. ¿En que consiste? tal como su nombre lo indica lo que hace es crear un usuario sin asociarlo a un login en el servidor, ahora veamos porque querríamos utilizar esto.
Antes que nada vayamos a la sintaxis

CREATE USER user_name
WITHOUT LOGIN
WITH DEFAULT_SCHEMA = [dbo]

Este usuario podrá recibir permisos, ser incluido en roles y todo lo que considere necesario, pero nadie podrá loguearse con dicho usuario. Un posible uso (aunque a mi ver no recomendado) es cuando aún no se el nombre del login que tendrá y quiero dejarlo listo para una vez definido todo modificar el login asociado y ya tengo todo andando. Esto lo podré hacer con la sentencia:

EXEC sp_change_users_login 'update_one', @username, @loginname

¿Porque digo que no recomiendo esto? porque en estos casos una mejor práctica es asignar los permisos a un role, y una vez definido y creado el usuario simlemente se lo agrega al role. Es mucho mas comoda la administración de permisos si uno utiliza roles.

EXECUTE AS

El uso mas interesante viene de la mano de la opción EXECUTE AS. Esta opción me permite hacer que un procedimiento, función o trigger se ejecute con los permisos de otro usuario que no sea necesariamente el que lo invocó. De esta forma podría por ejemplo hacer un procedimiento que muestre información que de otra forma el usuario no hubiera podido ver. Al no estar este usuario asociado a ningún login no se corre peligro de que alguien se loguee con dicho usuario y escale permisos.
La forma de hacer esto es, por ejemplo:

CREATE PROCEDURE dbo.procedimiento
WITH EXECUTE AS 'UsuarioSinLogin'
AS
TRUNCATE TABLE ...
GO

En este ejemplo un usuario con permisos de datareader y ejecución sobre el stored puede tener una forma comoda y segura de limpiar una tabla en particular.

Huerfanos

Hay un caso muy particular en el cual mucha gente se topa con la sentencia WITHOUT LOGIN sin desearlo, y a veces sin entender porque. Supongamos que tenemos el usuario1 y login1 en la base de datos1 en el servidor1. Mediante backup y restore movemos la base al servidor2. El usuario, como muchos ya saben, queda huerfano de su login, y es necesario utilizar la función nombrada en la primer parte para volver a asociarlo (esto se debe a los ids internos de los usuarios). Si deseamos hacer un script completo de la base nos vamos a encontrar que los logins van a aparecer como WITHOUT LOGIN y esto se debe a que al no coincidir el id interno el motor asume que no tiene login. Es importante considerar esto antes de ponerse a buscar como loco que procedimiento hace un EXECUTE AS o algo similar. En la página de microsoft se puede encontrar un script muy simple para corregir estos casos cuando el usuario y el login tienen el mismo nombre.

Para mas información:
http://msdn.microsoft.com/en-us/library/ms173463.aspx
http://msdn.microsoft.com/es-es/library/ms174378.aspx
http://msdn.microsoft.com/en-us/library/ms188354.aspx

Contained Databases SQL Denali (2011)

2011-03-16T12:13:00.000-03:00

Una de las novedades, a mi ver, mas interesantes del SQL 2011 son las bases autocontenidas (Contained Databases). En las versiones actuales hay cierta información que se guarda en la base, y otra que se guarda en la instancia, como por ejemplos los logins o los jobs. Todo esto seguirá existiendo pero ahora habrá una nueva forma de guardar las cosas y es dentro de la misma base. Esto va a significar una mejora muy importante cuando uno realiza un movimiento de una base de un servidor a otro, permitiendo que toda la lógica de negocio correspondiente a la base viaje con esta, disminuyendo de esta forma las probabilidades de olvidar algo.

Logins
Una de las caractecterísticas mas importantes son los logins. Las bases contenidas permiten que el login lo maneje la misma base y no la instancia. Comunmente cuando uno realiza una migración los usuarios de la base se encuentran asociados a un login de la instancia (tanto sea uno de seguridad integrada o un login sql), y al momento de migrar esta relación se pierde dejando al usuario huerfano. Para solucionar esto es necesario al momento de restorear ejecutar un script que se encargue de mapear nuevamente el usuario con el login correspondiente. Esto no sería necesario en una base contenida visto que toda la información de login se encuentra almacenada en si misma. Es muy importante resaltar que para que esto funcione al momento de establecer la conexion a la base hay que indicar como base default dicha base y no la master como suele venir por defecto.
NOTA: No tuve tiempo de probar cuando es seguridad integrada como se comporta al mover de un servidor a otro y mas allá de que el usuario se mantiene relacionado con el login, ver si el login se encuentra bien definido o requiere algo extra.

Linked servers
Los linked servers también pueden ser almacenados en la base de este nuevo modelo. Es sumamente util esto cuando son utilizados por la aplicación dueña de la base. Los beneficios son notables al momento de puesta en producción inicial, movimientos de base, y para tener mas aislados los elementos de cada base/aplicación. Es una función que me parece va a volverse un MUST en el diseño de bases de datos.

Jobs
Otro elemento que puede pertenecer a la base. En este caso creo que el mayor beneficio es el aislar la lógica de la aplicación. ¿Todos mis jobs van a pertenecer a alguna base ahora? la respuesta es NO. Los jobs de mantenimiento, por ejemplo, va a seguir siendo mejor mantenerlos en la instancia. Ejemplo si tengo un job para hacer backup de mis bases todos los días a las 11PM ese job está bien que se mantenga como un job de la instancia. Lo mismo los de reindexado, actualización de indices etc. En cambio si tengo un job que hace algo propio de la aplicación (ejemplo todas las noches actualiza el estado de los usuarios) ese si debe ir en la base.

Manos a la obra!
El primer paso es habilitar nuestra instancia para utilizar Conteined Databases. El código es el siguiente:

sp_configure 'show advanced', 1;
RECONFIGURE WITH OVERRIDE;
go
sp_configure 'contained database authentication', 1;
RECONFIGURE WITH OVERRIDE;
go

Y luego crear la base, indicando que será una base contenida:

CREATE DATABASE MiBase CONTAINMENT = PARTIAL;
go

Con esto ya tenemos nuestra base lista!!!
Saludos y espero que les sirva. Para mas información: http://msdn.microsoft.com/en-us/library/ff929071%28v=SQL.110%29.aspx .
Andrés

Sp_who en Oracle

2011-01-25T08:19:00.000-03:00

En el tiempo que llevo trabajando con SQL Server creo que el comando que mas veces ejecuté es el sp_who o su versión extendida sp_who2. Este comando cuando se está administrando una base es muy comodo por su sencillez y nos da un buen resumen de que está ocurriendo en el servidor. Claramente cuando hay alguna situación fuera de lo normal esto es solo la puerta de entrada para despues controlar las DMVs, los contadores del sistema operativo, etc, pero siempre es un buen comienzo. Muchas personas les pasa que acostumbradas a ambientes SQL Server se sientan frente a un sistema Oracle y buscan algo equivalente y no lo encuentran, así que veamos como sería una consulta equivalente en Oracle.
No contamos con un stored procedure prearmado que nos brinde la información, pero al igual que en SQL Server, esta información se nutre de las sesiones, así que consultaremos la V_$SESSION al igual que el sp_who original.
En este caso para que les sea mas comodo acomodé los campos para que queden igual que en la versión original.

SELECT sid, status, Username, terminal, blocking_session_status, schemaname, a.name Command_Action, logon_time, program
FROM SYS.V_$SESSION s
INNER JOIN AUDIT_ACTIONS a ON s.command=a.action;

Como podrán ver aparte de consultar la vista de sesiones hice un join con la tabla AUDIT_ACTIONS que es la que contiene las descripciones de cada evento para que sepamos que está haciendo la session.
Lo que recomiendo es guardar esta consulta en un script a mano y poco a poco irse internalizando mas con la SYS.V_$SESSION visto que tiene mucho para darnos. Quien no esté acostumbrado se va a sorprender al encontrar muchos mas campos que en su equivalente de SQL Server.
Pueden encontrar mas detalles en:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2088.htm

Por último si lo que se desea es hacer algo mas semejante al sp_who2 y no tienen ganas de internalizarse en los demas campos, puede hacerse una función que nos retorne los resultados de la consulta. Esta función sería:

-- Creacion de funcion que retorna como resultado una tabla
CREATE OR REPLACE function sp_who2 return sp_who2_tab PIPELINED
IS
CURSOR cur0
IS
SELECT sid, status, Username, terminal, blocking_session_status, schemaname, a.name Command_Action, logon_time, program
FROM SYS.V_$SESSION s
INNER JOIN AUDIT_ACTIONS a ON s.command=a.action;

out_rec sp_who2_rec := sp_who2_rec(NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);
BEGIN
OPEN cur0;
LOOP
FETCH cur0 INTO out_rec.sid, out_rec.status, out_rec.Username,
out_rec.terminal, out_rec.blocking_session_status, out_rec.schemaname, out_rec.Command_Action, out_rec.logon_time, out_rec.program;
EXIT WHEN cur0%NOTFOUND;
PIPE ROW (out_rec);
END LOOP;
CLOSE cur0;
RETURN;

END;

Y como sucede con las funciones que retornan tablas en Oracle, la forma de utilizarla sería:
SELECT * FROM TABLE(sp_who2);
Claramente a esto hay que sumarle la creación de los objetos necesarios (record y table).
Espero que les haya servido!

Drop and Create

2011-01-17T09:51:00.001-03:00

Muchas veces al armar los scripts figuran creación de objetos, y obviamente es deseable que nuestro script no falle por mas que dicho objeto ya se encuentre creado. En el caso de los stored procedures, en Oracle, tenemos la opción de CREATE OR REPLACE, pero cuando nos manejamos con tablas esta opción, lamentablemente, no existe ni en Oracle ni en SQL Server.
Para eliminar y crear una tabla desde cero en SQL Server es bastante conocido el método (de echo en la versión 2008 en adelante ya lo tenemos a un click del managment). El código es:

-- Prueba 1: Controlo si existe la tabla, si es necesario la elimino y la vuelvo a crear
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.prueba_creacion') AND type in (N'U'))
DROP TABLE dbo.prueba_creacion

CREATE TABLE prueba_creacion(clave INT);

De esta forma controlamos si la tabla existe, y en caso afirmativo la eliminamos. Luego podemos proceder a crearla sin problema.
Otra es la historia cuando la tabla es una tabla temporaria. Lo intuitivo sería ahcer lo mismo con el nombre de la tabla temporaria, pero esto arrojaría error. ¿porqué? porque la tabla temporaria se encuentra alojada en la tempdb. El código para poder realizar lo mismo con tablas temporarias sería:

-- Prueba 2: Controlo si existe la tabla temporaria, si es necesario la elimino y la vuelvo a crear
IF NOT OBJECT_ID('tempdb.dbo.#prueba_creacion_temp') IS NULL
DROP TABLE dbo.#prueba_creacion_temp

CREATE TABLE #prueba_creacion_temp(clave INT);

Pueden ver que se código no importa cuantas veces se ejecute nunca arrojará error.
Por último es interesante resaltar que en oracle no podemos realizar ninguno de estos controles debido a que el parser arrojará error la primera vez al hacer un DROP TABLE de una tabla que aún no existe. Por este motivo la forma de realizar lo mismo en Oracle sería:

-- Prueba 3: Controlo si existe la tabla en oracle, si es necesario la elimino y la vuelvo a crear

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE prueba_creacion';
EXCEPTION WHEN OTHERS THEN NULL;
END;

CREATE TABLE prueba_creacion(clave INT);

De esta forma se ejecutará siempre el DROP TABLE y arrojará error de sintaxis cuando no exista la tabla, pero el error será en el sql dinámico y no en todo el script, y estará capturado por el WHEN OTHERS, haciendo que sea transparente para el resto de la ejecución.

SQL Server Denali - Sequence

2011-01-06T12:13:00.000-03:00

Estuve probando el SQL 11 y una de las cosas que mas contento me puso es la incorporación de las SECUENCIAS tal como lo maneja Oracle. ¿Qué es esto? básicamente es un objeto en el motor que podemos crear y nos arroja números de forma ordenada según el criterio que hayamos definido. El principal objetivo es no depender de claves de tipo IDENTITY.
En este artículo hablaré de 3 cosas:
1) Como se utilizan las secuencias (sequence)
2) Comparar con Oracle el manejo de secuencias
3) Stress test comparando secuencias con campos identity

Un ejemplo de como utilizar este nuevo objeto sería el siguiente:

USE SQLDENA
GO

-- La forma tradicional de hacer un "autonumerico" es
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO

CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY IDENTITY,
dato1 VARCHAR(100)
)
GO

-- Pero ahora con las secuencias lo podemos hacer de la siguiente forma
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PruebaSecuencia]') AND type in (N'U'))
DROP TABLE [dbo].PruebaSecuencia
GO

-- Creamos la tabla que SIN campo identity
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR(100)
)
GO

-- Creo la secuencia, se puede definir como se incrementa, etc
-- Como se puede ver son muy flexibles permitiendo configurar como se van a comportar
CREATE SEQUENCE dbo.NuevaSec
AS INT
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20
;
GO

-- Obtengo unos valores de ejemplo
SELECT NEXT VALUE FOR dbo.NuevaSec
GO 4

-- Reinicio la secuencia para el ejemplo
ALTER SEQUENCE dbo.NuevaSec RESTART
GO

-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select next value for dbo.NuevaSec as nro, 'Valor1' as dato1

-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')

-- Que valor ingrese ultimo?
select current_value from sys.sequences where name = 'NuevaSec'

-- Esto me permite pasarle el valor a otra rutina que tenga que insertar en base a lo ingresado

-- La forma de realizar esto mismo en ORACLE hubiera sido:
/* PRUEBA ORACLE */
CREATE TABLE PruebaSecuencia(
id INT PRIMARY KEY,
dato1 VARCHAR2(100)
)

CREATE SEQUENCE NuevaSec
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 999999
CACHE 20;

-- Obtengo unos valores de ejemplo
SELECT NuevaSec.NEXTVAL FROM DUAL

-- Ejemplo de como la usaria
INSERT INTO PruebaSecuencia
select NuevaSec.NEXTVAL as nro, 'Valor1' as dato1 FROM DUAL

-- O tambien...
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (NuevaSec.NEXTVAL, 'Valor1')

-- Que valor ingrese ultimo?
select NuevaSec.CURRVAL FROM DUAL

/* FIN PRUEBA ORACLE */

Comparando ambas formas creo que por suerte mantuvieron una sintaxis muy similar en lo que corresponde a la creación del objeto, y como oracle no tiene su hermoso CREATE OR REPLACE para las secuencias entonces es totalmente consistente. En lo que respecta a su uso diario, me parece mucho mas comoda y agradable la sintaxis de Oracle, principalmente la forma de ver el valor actual que es algo que no entiendo como no incorporaron. Por otra parte en Oracle es muy incomodo reiniciar una secuencia (es necesario restarle el valor actual) , mientras que SQL ha simplificado esta tarea para evitarnos dolores de cabeza.

Volviendo al SQL Server, realice un stress test con ambas tablas, una con identity y otra sin, utilizando las siguientes instrucciones
-- test1
INSERT INTO PruebaSecuencia (dato1)
VALUES ('Valor1')
GO 50000

-- test2
INSERT INTO PruebaSecuencia (id, dato1)
VALUES (next value for dbo.NuevaSec, 'Valor1')
GO 50000

Los resultados fueron parejos tardando un poco menos el ejemplo con la secuencia pero no de forma significativa. Es importante notar que la prueba no la realicé en un gran servidor sino en una pc virtual, y según varios test esta diferencia suele ser mayor.

RML para SQL 2008 - Parte 1

2011-01-03T08:32:00.001-03:00

A quien no le ha pasado de tener un sistema de base de datos, hacer unos cambios y no estar seguro si dichos cambios realmente mejoran la performance del proceso que se deseaba atacar? Este problema es muy común y por suerte contamos con unas series de herramientas que nos permiten solucionarlo. Este pack se llama RML y consta de varias herramientas, comentaré las mas importantes a mi parecer:
ReadTrace, OStress, y ORCA.
El ReadTrace nos permite hacer un análisis te las trazas de SQL y pasarlas a lo que se llaman archivos RML. Estos archivos a diferencia de la traza, se encuentran organizados por proceso, de esta forma se hace posible el simulacro.
El OStress toma los archivos RML y los lanza al SQL simulando el comportamiento original, pudiendo configurarle si lo debe lanzar una sola vez o muchas, cada que intervalo, y muchas cosas mas.

En esté primer artículo les dejo un overview de las pruebas realizadas.
Las herramientas son muy configurables y de un uso muy simple e intuitivo, y al ser por linea de comando es facil de documentar los pasos a seguir.
El readtrace viene con 3 archivos de ejemplo para una carga full, media o liviana. La herramienta esta pensada para hacer reportes y para trabajar con el Ostress, pero la verdad es que ninguno de estos tres archivos de configuración es adecuado para el OS. El archivo full consume muchisimo espacio, y el mediano no posee varios eventos que son requeridos por el OS.
El Ostress es un poco caprichoso con los eventos que requiere y las columnas que se deben haber capturado. Algunas (a mi ver) están de mas pero bue... sus mensajes no son del todo intuitivos visto que algunas veces les va a decir "falta tal evento y tal otro" y en realidad falta uno solo de los dos, pero se debe a que usa el mismo mensaje para determinado grupo de eventos (ejemplo los started y completed, si falta uno de los dos le va a decir que faltan los dos).
Me armé una traza personalizada que tiene menos eventos que la full pero los suficientes para que funcione el OStress, intentando minimizar las columnas tambien (hay algunos warnings que me da pero alcanza para que ejecute). Probé en un sistema con carga moderada y tardó un varias horas en llegar al giga de traza, pero en sistemas con mucho movimiento en menos de una hora ya rozaba el giga... Es una herramienta que hay que tener cuidado como se la usa visto la gran demanda de recursos (principalmente disco, compare los contadores y no hubo diferencias significativas en la performance del sistema al realizar la traza) así que me parece que lo adecuado es definir una franja horaria crítica o modelo y utilizarla sobre dicha franja. Claramente no es una traza para dejar corriendo durante toda la jornada.

Pro: Es muy facil una vez capturado hacer un simulacro para poder comparar la performance de un sistema antes y despues de hacer un cambio

Contra: Pese a poder configurar el paralelismo y los delay entre conexion, se dificulta hacer simulaciones de una ventana de tiempo prolongada o hacer la captura sin saber cuando será el momento crítico.

Les dejo el link para bajarlo: http://support.microsoft.com/kb/944837

Controlando el AS

2010-11-12T11:33:00.000-03:00

Miles de veces ejecutamos el bendito sp_who o sp_who2 entre otros, pero hablando con varias personas me encontré que desconocían como hacer lo mismo en Analisys Services.
Si queremos hacer un monitoreo del AS contamos con tablas parecidas a las DMV del SQL.
Para acceder a estas debemos conectarnos y generar una nueva consulta MDX, una vez ahí podemos escribir:

select * from $system.discover_commands

Y esto nos retorna la información equivalente al sp_who. Desde ahí podemos obtener el inputbuffer de cada comando que se encuentra en ejecución.
Esta es la que mas utilizo pero no es la única.
Para saber las conexiones existentes podemos utilizar:

select * from $system.discover_connections

Luego para analisis mas profundos contamos con otras como:
select * from $system.discover_memoryusage
select * from $system.discover_object_memory_usage
select * from $system.discover_object_activity where object_reads > 0
select * from $system.discover_partition_stat

Podemos obtener un listado completo de las tablas del sistema con la sentencia
SELECT TABLE_NAME
FROM $system.dbschema_tables
WHERE TABLE_SCHEMA = '$SYSTEM'
ORDER BY table_name"

ahí podremos ver que se encuentran divididas en 4 categorias:
DBSCHEMA_: Brinda información de la base
DISCOVER_: Brinda información para administración
DMSCHEMA_: Brinda información de datamining
MDSCHEMA_: Brinda información de la estructura de los cubos

En lo personal el que mas utilizo es el "discover", pero depende la tarea de cada uno esto puede variar.

Saludos!

Socorro! no puedo frenar del CDC!!!

2010-10-05T17:49:00.000-03:00

El CDC de SQL 2008 es en muchos aspectos una bendición, pero en algunos casos se puede volver una pesadilla si lo deseamos apagar. Una catarata de errores nos pueden ocurrir al intentar desactivar el CDC de una base o de una tabla, principalmente si no dejamos todo intacto como cuando lo creamos (ejemplo renombramos la tabla que tenia CDC).
En este caso vamos a ver que si borramos la tabla pasamos a estar peor, porque ahora no solo que no nos deja desactivar el CDC de la base sino que aparecerán algunos errores, así que lo que debemos hacer es erradicar todo rastro.
Primero debemos eliminar "dbo.systranschemas" y luego todas las referencias en el schema CDC.
Con una consulta nos alcanza para encontrarlas:

select * from sys.objects where schema_id=schema_id('cdc')

Esto nos devolvera cuales son las tablas, procedures y functions que se crearon. Debemos eliminar todos estos, con especial cuidado en las funciones que algunas tienen nombres raros como "fn_cdc_get_net_changes_ ..." y si no la ponen entre corchetes no se podrán eliminar.
Una vez borradas todas estas tablas ejecutamos el comando:

EXECUTE sys.sp_cdc_disable_db

y dejamos que el SQL se encargue del resto. Les recuerdo que esto es un último recurso, no deben hacerlo salvo que sea extremadamente necesario, nunca es recomentable borrar la información propia del SQL.
Saludos!
Andrés

Role de ejecución para todos los stored procedures

2010-09-16T13:25:00.000-03:00

Una situación bastante común es querer que un usuario (o grupo de usuarios) tenga permiso de ejecución para todos los stored procedures de una base. El SQL Server nos brinda los roles db_datareader, db_datawriter u otros, pero ninguno da solamente esos permisos. Para poder hacer esto con roles es necesario asignarlo al role db_owner, lo cual da muchos mas permisos de los deseados.
La única forma es dar los permisos de cada stored de forma individual, lo cual si tenemos muchos puede ser realmente molesto.
La solución que planteo es hacer un grupo al cual llamé db_executeall. Armé un script que recorre todas las bases y en todas crea dicho role.
El segundo paso es crear un cursor que recorra todos los stored procedures y asigne los permisos al role. El código estaría estructurado de la siguiente forma:

Cursor que recorre bases
begin
Crear role db_executeall si no existe
Cursor que recorre procedures
begin
Asignar permiso de ejecución al role
end
end

Esto nos dejaría en cada base un role nuevo que tiene todos los permisos de ejecución así solamente debemos asignarlo ahí al usuario. Una idea extra es agregar este script en un job diario, o por hora, para asegurarnos que nuestro role siempre esté actualizado con todos los permisos.
Si alguién necesita el código puede escribirme.
Saludos!

Defaul role como en Oracle

2010-09-07T10:35:00.001-03:00

Una cosa que mucha gente que trabaja con SQL Server extraña de Oracle es lo conocidos como Default Roles.
¿En que consiste esto? Consiste en tener un conjunto de permisos al comenzar la sesion y en tiempo de ejecución poder acceder a otros permisos de forma explicita. Un ejemplo util sería darle a un usuario permisos de DATAREADER con la opción de ascender a DATAWRITER. De esta forma si la mayoria de sus tareas consisten en solo leer datos no podría modificar cosas por error salvo que explicitamente suba sus permisos.
Para implementar esto haremos lo siguiente. Para empezar crearemos en la master (o en alguna base de DBA) una tabla que tenga la siguiente estructura:

T_Permisos(Usuario SYSNAME, Role SYSNAME, Defualt TINYINT)

Con esta tabla indicaremos los roles que tiene permitidos un usuario. La primer columna sera el nombre del LOGIN, la segunda el role y la última si este role lo tiene desde el comienzo o lo debe explicitar.
El segundo paso es generar un logon trigger. Este trigger lo que hará será leer la tabla con un filtro en el where de Default=1 y Usuario=ORIGINAL_LOGIN() y hacer con SQL Dinamico una asignación de permisos a los roles indicados y quitandole cualquier otro que pudiera tener.
Con esto ya tenemos garantizado que al momento de conectarse tendrá un subconjunto de permisos.
El último paso es permitir la escalada de permisos, lo cual lo haremos mediante un stored procedure. El stored GrantRoles(Role SYSNAME) deberán tener permiso de ejecución todos los miembros de public. Este stored lo que hará es un select para controlar si el usuario logueado tiene en la tabla registrado el rol que solicita, en caso negativo no se hace nada y en caso afirmativo se asigna de forma dinámica el role. Es importante que este stored utilice la sentencia EXECUTE AS SELF por ejemplo y se haya creado con un usuario administrador. La idea es que el no tenga los permisos pero el stored si, y con la lógica adecuada se los asigne.
Una vez terminada su tarea, cuando se desconecte mantendrá los permisos, pero al establecer una nueva conexion los perderá.
Es interesante el tema de conexiones simultaneas, ahí se pueden analizar varias variantes como controlar en la sys.dm_exec_sessions para ver de solo sacar los permisos si no hay otra conexion viva del mismo usuario.
Saludos!!

Restore

2010-08-26T09:42:00.000-03:00

Este artículo es corto pero muchas veces me lo preguntaron y yo mismo lo he necesitado asi que me parece un buen dato para tener siempre a mano. Cuantas veces uno tiene que hacer un restore de una base de varios gigas y no tiene idea de cuanto va a tardar, y del otro lado del teléfono tenemos al usuario reclamando que quiere su base online hace una hora (aunque el pedido se haya realizado hace 15 minutos, siempre es así el usuario). Para esto podemos utilizar una vista del sistema, DM_EXEC_REQUESTS (http://msdn.microsoft.com/es-es/library/ms177648.aspx). Una consulta sencilla que me permitirá saber el tiempo que resta para un restore es la siguiente:

SELECT CONVERT(NVARCHAR(3), CAST(percent_complete AS INTEGER)) + '%' Porcentaje
, r.estimated_completion_time/(1000*60) MinutosRestantes
FROM SYS.DM_EXEC_REQUESTS r
WHERE percent_complete > 0

Esta consulta nso dirá el tiempo esperado para finalizar el restore pero tiene un problema, en caso de estar realizando varios restore al mismo tiempo no nos indica que a que base corresponde cada tiempo estimado. Esta información la podemos enriquecer haciendo un join con la información obtenida en el SP_WHO2 y así tener una consulta mucho mas completa que nos muestre también cual es la base sobre la cual operamos.

CREATE TABLE #sp_who2
(SPID INT,
Status VARCHAR(1000) NULL,
Login SYSNAME NULL,
HostName SYSNAME NULL,
BlkBy SYSNAME NULL,
DBName SYSNAME NULL,
Command VARCHAR(1000) NULL,
CPUTime INT NULL,
DiskIO INT NULL,
LastBatch VARCHAR(1000) NULL,
ProgramName VARCHAR(1000) NULL,
SPID2 INT,
REQUESTID INT)

INSERT INTO #sp_who2
EXEC sp_who2

SELECT w.DBName
, CONVERT(NVARCHAR(3), CAST(percent_complete AS INTEGER)) + '%' Porcentaje
, r.estimated_completion_time/(1000*60) MinutosRestantes
FROM #sp_who2 w, SYS.DM_EXEC_REQUESTS r
WHERE r.SESSION_ID = w.SPID
AND w.Command IN ('RESTORE DATABASE')
AND r.percent_complete>0
AND w.DBName<>'master'
ORDER BY 1

DROP TABLE #sp_who2

Saludos!

Todo sobre logins (parte 2)

2010-08-13T10:56:00.000-03:00

Ya hablamos de como mantener bien nuestros logins, ahora lo que falta es controlar que todo funcione como queremos. La auditoria es una de las partes mas importantes a la hora de administrar la seguridad.
Hay varias cosas que podemos auditar pero principalmente quisiera hacer foco en dos:
1) Auditoria SQL
2) Logon triggers

En la auditoria podemos definir los eventos que queremos que se controlen. Esto en la versión 2008 se ha echo muy sencillo, con solo un par de clicks podemos hacerlo. Si en cambio nuestra base es SQL 2005 deberemos trabajar un poco mas y habilitar el service broker (ENABLE_BROKER) y tenemos varios eventos que esta bueno monitorear y loguear en una tabla:
AUDIT_LOGIN_CHANGE_PROPERTY_EVENT
AUDIT_LOGIN_FAILED
AUDIT_ADD_LOGIN_TO_SERVER_ROLE_EVENT
AUDIT_ADDLOGIN_EVENT

Estos eventos nos permitiran monitorear si se han creado nuevos logins o se le asignaron permisos a los ya existentes. Seguro se estarán preguntando "y no voy a auditar si se borraron loggins?" el evento cuando se borra un login es "AUDIT_ADDLOGIN_EVENT" pero en el xml del evento el subclass es 2 en lugar de 1.

Lo otro que podemos hacer interesante es definir logon tringgers y aquí podemos dejar volar nuestra imaginación. Entre las ideas que se pueden implementar estaría:
- Definir que un login pueda conectarse solamente desde un host en particular
- Definir que un login pueda tener N ocurrencias en el día o simultaneas
- Guardar en una tabla los horarios de login del usuario
- No permitir que dos usuarios se conecten de forma simultanea

y esas son solo algunas ideas, cada uno puede adaptarlas según las necesidades de su empresa

Todo sobre logins (parte 1)

2010-08-04T18:38:00.001-03:00

Uno de los temas mas importantes en las bases de datos es la seguridad, y obviamente ésto viene de la mano de los logins.
En este articulo intentaré transmitir lo que para mi son las buenas prácticas al respecto, tanto sea para administradores principiantes como no.

Seguridad integrada
Para empezar recordemos que en SQL contamos con dos tipos de autentificación: integrada con windows o manejada por SQL. Aquí ya tenemos una de las primeras buenas prácticas, cuando sea posible es bueno definir los logins para utilizar seguridad integrada, esto permite que nuestra seguridad sea manejada de forma aislada. Nuestro segundo paso para asegurar la base sería que nuestros logins no sean usuarios de windows, sino usuarios de red, de forma deseable manejados desde AD. De esta forma si queremos denegar el acceso a un usuario de todas nuestras instancias, podemos hacerlo simplemente denegando el acceso desde AD. El tercer y último paso en esta linea de pensamiento es no definir al usuario de AD como login de nuestro SQL, sino definir un grupo de AD y en el SQL definir como login a dicho grupo. La administración de dar o quitar permisos ahora pasa simplemente a ser agregar o quitar una persona al grupo de AD.
Este modelo es muy seguro y tiene varios puntos fuertes:
1) Puedo dar o quitar permisos a un usuario en varias instancias SQL de forma inmediata.
2) Si otra persona debe tener los mismos permisos, no requiere ningún esfuerzo. Nota: es común que varias personas cumplan el mismo rol y por ende tengan los mismos permisos.
3) Es facil garantizar que todos los usuarios cumplan los mismos requisitos de seguridad de contraseñas (caducidad, complejidad, etc).
Este a mi ver es el mejor que se puede plantear, pero como siempre hay detalles a tener en cuenta. Tal es el caso si tengo varias instancias SQL y un grupo con permisos sobre diferentes bases en estos. Supongamos que ingresa una nueva persona que debe tener todos los mismos permisos SALVO que no debe poder acceder a una tabla que el resto si. Una opción poco práctica sería definir un nuevo grupo y replicar todos los permisos, pero es dificil de mantener visto que si agrego un nuevo permiso debo recordar agregarlo en ambos grupos. Otra opción, a mi ver mucho mas práctica, sería definir en mi base no solo al grupo sino también al login particular, y a éste denegarle el permiso a dicha tabla. De esta forma heredará los permisos del grupo pero le será denegado el acceso a la tabla.

Seguridad SQL
El esquema visto anteriormente es el ideal, pero es un echo que no siempre puedo adaptarme a éste modelo y a veces no hay mas remedio que utilizar seguridad SQL. En estos casos es una buena práctica que los logins deban cumplir con cierta complejidad de contraseña, así como también definir la caducidad. Recuerden tener esto en cuenta porque son dos clicks al dar de alta y nos dejará dormir mucho mas tranquilos. Pero es una tarea humana realizar esta alta en general, y como tal puede fallar. Por eso es importante corroborar que fehacientemente los logins cumplan con nuestros criterios de seguridad establecidos. En SQL 2008 podemos utilizar policys (http://msdn.microsoft.com/en-us/library/bb510667.aspx) que nos permiten hacer dos cosas en este contexto:
- Tener un listado periodico de todos los logins que no cumplan con determinados criterios
- Prohibir la creación de un login si no cumple los criterios
Esto es muy comodo porque nos evita los errores humanos, aunque en algunos contextos puede ser demasiado restrictivo.

Certificados
Por último una buena forma de tener acotados los permisos que les damos a los usuarios es ponerles una fecha de caducidad si sabemos que la tarea que van a realizar es solamente por un tiempo acotado. En estos casos podemos definir un certificado en la master que tenga un expiration date y asociar el login a dicho certificado. De ésta forma una vez vencido el certificado el usuario no podrá loguearse al sistema.

Saludos!

Jobs Matusalén (Procesos colgados)

2010-07-26T14:03:00.000-03:00

Un tema interesante que se me presentó hace unos meses fue encontrar un job que llevaba corriendo una semana y como ningún usuario reclamó nada pasó desapercibido. Ahí fue que decidí tomar una acción preventiva para estos casos. Cuando configuramos nuestras tareas de mantenimiento o control lo mas normal es que utilicemos jobs. Los jobs si ejecutan de forma correcta podemos configurar que nos mande un mail, o lo que es mas habitual, podemos configurar que nos envíe mail cuando falla (a nosotros o cualquier operador del SQL). El problema es que pasa cuando el proceso queda colgado, por lo tanto no recibimos ni mail de ok, ni mensaje de error y es muy probable que no nos demos cuenta.
Con la intención de alertar esta situación generé un job que se ejecute cada 15 minutos y controle todos los jobs que se encuentran vivos hace cuanto tiempo lo están. La forma de implementarlo fue hacer un procedimiento que para cada job que se encuentra corriendo hace mucho genere una excepción, y en caso contrario no haga nada, de esta forma puedo en mi job de control programar la alerta de mail y que la reciban todos los administradores.
Para obtener los jobs vivos utilicé "xp_sqlagent_enum_jobs", que es medio tramposa así que tengan en cuenta que deben pasarle como primer parámetro 0 y el segundo parametro no interesa pero debe ser algún texto (no importa cual). Esto se debe a que el primer parámetro hace que se ignore el segundo pero igual lo exije.
El resultado de esa consulta nos retorna jobs_id así que deberemos cruzarla con "msdb.dbo.sysjobs" y "msdb.dbo.sysjobactivity" para transoformala en algo humanamente entendible.
La pregunta interesante es la siguiente: cuando consideramos que un job está tardando? La mejor solución asumí que es basarnos en el historial. Por suerte contamos con la tabla msdb.dbo.sysjobhistory. Un error común podría ser querer promediar el tiempo que ahí se encuentra y generar una alerta cada vez que el job tarda mas que ese promedio. ERROR. ¿por que es esto incorrecto? porque si un job a veces tarda 58min y a veces 1hs 2min, no suena razonable que pasada una hora alerte que ocurrió un error, sino la mitad de las veces recibiría el mail, y bien sabemos que un mail que llega seguido termina siendo ignorado con las posibles consecuencías que esto trae. Por eso es necesario basarnos en la matemática y averiguar el desvio estandar de nuestra muestra, y calcular cual es el tiempo esperado tal que un 90% de los casos alcanza a terminar, asumiendo que el tiempo esperado suele ser de la forma valor esperado + error, teniendo el error una distrinución Gaussiana.
En la impementación me pareció bueno meter esto en otra tabla y actualizarla períodicamente con otro job que se encargue de calcular los tiempos (una vez por semana por ejemplo) y el job de control consulte estos tiempos. Una de las partes mas molestas de implementar esto es que la job_history no tiene un formato muy amigable para los tiempos, les recomiendo que lo calculen con una función y la tengan a mano porque sirve para muchas cosas y es muy molesta de rehacer.
Espero haber sido claro, si alguién desea el código puede enviarme un mensaje privado.

Auditoria de Active Directory con SQL 2008

2010-07-22T09:02:00.000-03:00

Hace poco se me presentó un problema que luego de buscarlo por varias páginas sin resultados buenos decidí implementar una solución desde cero. El equipo de seguridad informática necesitaba un producto para llevar una auditoría de cambios de AD y controlar que usuarios han sido modificados o se les han asignado nuevos permisos.
El primero intento (y el que abunda en la red) de llevar esto a SQL 2008 tiene sus problemas. Si la empresa está compuesta por pocos usuarios no hay inconveniente, se hace un open query de SQL a AD utilizando LDAP y se controla de a un usuario por vez si cambio algo. El problema es que el SQL no permite traer TODOS los usuarios si estos son muchos, así que hay que buscar otra solución.
La respuesta que plantee consiste de combinar varias tecnologías. Para poder hacer la auditoría primero cargaremos todos los datos de AD en un conjunto de tablas intermedias en SQL. La forma de hacer esto será mediante Power Shell. El Power Shell puede conectarse sin problemas al AD y cada resultado obtenido en el iterador lo volcaremos en la tabla SQL. Hay que tener cuidado con la normalización de las tablas visto que algunos de los campos de AD son multivaluados y debemos guardarlos como tablas independientes (el ejemplo mas importante es Member o MemberOf).
Una vez cargados los datos en las tablas intermedia SQL lo volcamos en la tabla final. Este paso intermedio lo hacemos para poder saber que cosas han cambiado sin tener que volcar una gran lógica en el Power Shell. En lugar de hacer complejas consultas actualizaremos la tabla final (Ej. T_AD_USERS) con la tabla intermedia (Ej. T_AD_USERS_TEMP). La forma mas rápida podría ser hacer un truncate y volcar todo el contenido, pero cambiaremos solamente lo que sea realmente necesario y ya veremos por que. El SQL 2008 nos brinda para esto el comando MERGE (http://technet.microsoft.com/en-us/library/bb510625.aspx) que nos permite de forma rápida incorporar los registros faltantes, modificar los existentes y borrar los dados de baja.

Con esto ya tenemos una tabla con la información de AD actualizada cuando querramos (podría ser un job que ejecute cada una hora). Lo que nos falta es llevar la auditoría en si misma, y para eso activaremos el CDC (change data capture) sobre la tabla en cuestión. Como nos encargamos de no borrar todo cada vez sino solamente actualizar según los cambios, el CDC llevará un registro de todos los usuarios que hayan sido actualizados. La única salvedad es que nuestra auditoría así como está planteada mostrará las altas, las bajas, y por cada vez que se ejecute el job todos los campos figurarán como actualizado, así que debemos hacer dos MERGE, uno de altas y bajas (WHEN NOT MATCHED BY TARGET y WHEN NOT MATCHED BY SOURCE) y otro para actualizar que contenga en el join que coincida la PK y alguno de los campos haya cambiado.

Con esto ya tendrémos una tabla de auditoría de AD (cdc.dbo_T_AD_USERS_CT), ahora lo único que nos falta es darle a la gente de seguridad informática una interfaz amigable para acceder a ésta. Recordemos que el CDC trabaja con marcas de agua y esto no es muy amigable para la gente de otras áreas, así que lo que recomiendo como mínimo es hacer un stored procedure que tome como parametros dos DATETIME (desde, hasta) y retorne todas las entradas del CDC que se encuentren en dicho período pero cambiando la marca de agua por su fecha correspondiente y mostrar esto desde reporting services u otra interfaz amigable.
Si alguien quiere el código de todo esto implementado no dude en ponerse en contacto conmigo.
Saludos!