Controlar CMDShell en SQL

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

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

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

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

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

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

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

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)

o

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

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

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