El papel del DBA y la administración de SQL Server en la nube.

Les comparto un artículo publicado en la revista SQL Magazine. Que habla del role del DBA cuando migramos a la nube. He de decir que no estoy de acuerdo en algunos de los puntos tratados acá pues en algunos casos se expone como que la configuración de soluciones de alta disponibilidad híbridos (On Premise - Cloud) son sumamente complejos. Si bien es cierto no son configuraciones step By step son procesos relativamente simples que no requieren habilidades extra a las que un buen DBA ya trae consigo. Aparte de lo mencionado anteriormente si es de recalcar que los que somos DBAs siempre pensamos que tareas tendríamos que realizar con la llegada de SQL Azure Cloud Database pues muchas de las tareas como respaldos, administración del disco ya no serían de administración nuestra.

Acá les dejo el link para que saquen el rato para leerlo. 

 Saludos.

 http://sqlmag.com/cloud/dba-cloud-threat-or-opportunity

Cuando el Detach y el Attach no es suficiente.

Cuantas veces nos han dicho que para mover una base de datos en SQL Server es tan sencillo como hacer un Detach, mover archivos y luego hacer un Attach de nuevo para que la base de datos quede operacional? 

La respuesta es simple : N veces. En un mundo ideal en donde existe el orden, la documentación y a planeación debería de ser así de simple, pero la realidad es otra, por este motivo es necesario el documentar la localización real de mis bases de datos para evitar sorpresas cuando requiera realizar este tipo de tareas, ya sea por migraciones, simple re localización de archivos o cual sea el motivo.

Recientemente me encontré un caso interesante. La tarea era sencilla a simple vista. El requerimiento "Necesito mover las bases de datos de unidad porque no tengo espacio"

Formas de realizar el proceso hay muchas, algunos pensarán, "Bueno si tengo respaldos de las bases de datos puedo hacer restore de las BD y listo", claro, puede ser, siempre y cuando la app no sea tan transaccional que pueda darme el lujo de restaurar un respaldo de hace unas horas o bien que pueda respaldar la BD en el preciso momento de la restauración. Aun así administrativamente es un proceso un poco mas complejo que realizar el proceso de Detach y Attach. 

Para este caso utilicé una serie de queries que me permitieron documentar bien la ubicación de las bases de datos.

--El primero de ellos me muestra la ubicación específica de los archivos para una sola BD, en este caso la llamada TestDB

SELECT name, physical_name as PhysicalPath FROM sys. master_files

WHERE DB_Name(database_id)='Testdb'

Si requieren conocer las rutas de Todas las bases de datos incluyendo las de sstema pueden utilizar el siguiente query.

--Si requieren mover tambien las BD de Sistema, utilizan este query para documentar su ruta

SELECT D.name as DatabaseName,MF.name as LogicalName, physical_name as PhysicalPath FROM sys. master_files MF

INNER JOIN sys.databases D on MF.database_id=D.database_id

order by 1

Este escenario que ustedes ven en la imagen es probablemente lo que ustedes se vayan a encontrar en la vida real, no digo que en todo lugar sea así, pero al menos en un 80% de empresas si lo es, principalmente en aquellas pequeñas o medianas empresas. El query es útil si requiero documentar también la ubicación de las bases de datos de sistema (Cuyo proceso de re localización de archivos es distinto y repasaremos luego su proceso)

Ahora bien si lo que busco es únicamente conocer la ruta de las bases de datos de usuario, ejecuto el siguiente script.

--Si no requieren mover las BD de sistema, solo la TEMDB entonces se utiliza este query para documentar las rutas

SELECT D.name as DatabaseName,MF.name as LogicalName, physical_name as PhysicalPath FROM sys. master_files MF

INNER JOIN sys.databases D on MF.database_id=D.database_id

WHERE D.database_id NOT IN(1,3,4)

order by 1 

Cual es la importancia de documentar? Expongamos el siguiente caso.

Supongamos que ustedes dicen conocer su ambiente de base de datos, y simplemente realizan un detach de la base de datos que desean mover, sin haber documentado su ruta original, seguidamente van a buscar la ruta en donde ustedes creen tener la Base de Datos, pasan los archivos y realizan el proceso de Attach. A los minutos comienzan a recibir llamadas que los pedidos que se han hecho en los últimos días no se ven reflejados en pantalla. En este momento ustedes se preguntan "Cómo es posible? si yo solo hice un Detach y un Attach"

Probablemente eso es cierto, pero lo hice a partir de los archivos correctos? Probablemente muchos otros DBA o personal de TI que en algún momento haya hecho tareas de DBA copió archivos de esa base datos en alguna otra ruta.

Este tipo de casos pueden suceder cuando no se tiene documentada la ruta real de las bases de datos. Dicho esto, les recomiendo la utilización de estos scripts para evitar pasar un mal rato en lo que normalmente es un simple proceso de Detach y Attach. 

Saludos.

Problemas de rendimiento y el horario de ejecución de Jobs

En días pasados estaba identificando posibles cuellos de botella en una base de datos SQL Server 2012, pues se reportaban problemas serios de rencimiento en la aplicación a la misma hora todos los días de la semana. 

Aunado a la configuración de contadores de rendimiento, ejecución de SQL Server Profiler, utilicé algunas de las vistas de Su Magestad Glen Berry  de SQLSkills para identificar cuales procesos automáticos se ejecutan durante el día. La vista original de una idea general de los Jobs que se ejecutan en SQL Server. Sin embargo la vista no descarta aquellos jobs inactivos y tampoco muestra las horas de ejecución. por lo que me tomé la libertad de modificar la vista e incluir un par de columnas necesarias. La fecha y hora de la próxima ejecución del Job, además de modificar para que únicamente despliegue aquellos Jobs activos. De esta forma podemos identificar claramente cuales son los procesos que se ejcutan en determinado momento. 

Ya de esta manera logramos identificar cuales procesos nos estaban generando los problemas de sobrecarga en el servidor de base de datos y moverlos a horas en que la carga es menor y de paso afinar los SP que se ejecutan.

Acá les dejo el script modificado.

--******************************************************************************
--* Copyright (C) 2014 Glenn Berry, SQLskills.com
--* All rights reserved. 
--*
--* For more scripts and sample code, check out 
--* http://sqlskills.com/blogs/glenn
--*
--* You may alter this code for your own *non-commercial* purposes. You may
--* republish altered code as long as you include this copyright and give due credit. 
--*
--*
--* THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
--* ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
--* TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
--* PARTICULAR PURPOSE. 
--*

--******************************************************************************

--Modified by Adrian Miranda in order to include next Exection Date, time and status

-- Get SQL Server Agent jobs and Category information (Query 4) (SQL Server Agent Jobs)

SELECT sj.name AS [JobName], sj.[description] AS [JobDescription], SUSER_SNAME(sj.owner_sid) AS [JobOwner],
sj.date_created, sj.[enabled],jq.next_run_date,CASE WHEN LEN(jq.next_run_time)= 6 THEN SUBSTRING(CAST((jq.next_run_time) AS VARCHAR),1,2) + ' hour(s), ' ELSE SUBSTRING(CAST((jq.next_run_time) AS VARCHAR),1,1) + ' hour(s), ' END + SUBSTRING(CAST(( jq.next_run_time) AS VARCHAR),3,2) + ' min ' + SUBSTRING(CAST(( jq.next_run_time) AS VARCHAR),5,2) + ' sec' AS Next_Run_Time, sj.notify_email_operator_id, sc.name AS [CategoryName]
FROM msdb.dbo.sysjobs AS sj WITH (NOLOCK)
INNER JOIN msdb.dbo.syscategories AS sc WITH (NOLOCK)
ON sj.category_id = sc.category_id
INNER JOIN msdb.dbo.sysjobschedules jq
ON sj.Job_id = jq.job_id
INNER JOIN msdb.dbo.sysschedules s ON s.schedule_id=jq.schedule_id
WHERE sj.[enabled] = 1 AND s.enabled=1

ORDER BY sj.name OPTION (RECOMPILE);

Nueva versión - Crear Database Snapshot dinámicamente

Acá les adjunto la versión mas reciente del SP para crear Snapshots de base de datos de forma dinámica.
Permite la creación de multiples snapshots para una misma BD y la posibilidad de incluir el path en donde almacenar dichos Snapshots.


CREATE PROCEDURE usp_CreateSnapshot (@SourceDB varchar(200),@DestDB varchar(200)=NULL, @Message VARCHAR(200) =NULL OUTPUT)
AS 
--Created by Adrian Miranda Cordero
--Create a snapshot dinamically 
--Following script is intented to solve the problem of creating a database snapshot from the scratch by providing two or three parameters-

DECLARE @DatabaseName as varchar(200)
DECLARE @DestDBName as varchar(200)
DECLARE @SQLQuery as Nvarchar(MAX)
DECLARE @UseSQL as varchar (205)
DECLARE @Counter as smallint
DECLARE @FileId as smallint 
DECLARE @FileName as nvarchar(max)
DECLARE @LogicalName as varchar(200)
DECLARE @Files as varchar(max)
DECLARE @Inc_File int --Consecutive for a file name when you want multiple snapshots for the same DB

SET @DatabaseName =@SourceDB
IF @DestDB IS NULL 
SET @DestDBName= @SourceDB
ELSE
SET @DestDBName=@DestDB
SET @Inc_File = 0
SET @SQLQuery =''

IF (SELECT COUNT(1) FROM sys.databases WHERE name  in ('master', 'msdb',',model','tempdb') and  name = @Databasename) > 0
SET @Message='Can not create a database snapshot based on a system database'
ELSE IF (SELECT NAME FROM sys.databases WHERE NAME = @DatabaseName) IS NULL
SET @Message= 'Database not found.'
ELSE
BEGIN
SELECT @Inc_File=COUNT(1) FROM sys.databases WHERE source_database_id=DB_ID(@DatabaseName)
SET @Inc_File = @Inc_File + 1
DECLARE @DBFiles TABLE (
ID smallint Identity(1,1),
FileId int,
Name varchar (max),
FilePath varchar (max)
)
SET @SQLQuery = 'SELECT FILE_ID,name,Physical_Name FROM sys.database_files WHERE type_desc <>''''LOG'''''; 
SET @UseSQL = 'USE ' + @DatabaseName + '; EXEC sp_executesql N''' + @SQLQuery + '''';
INSERT INTO @DBFiles Exec (@UseSQL)
SET @SQLQuery = 'CREATE DATABASE ' + ISNULL(@DestDBName,@DatabaseName) + '_Snap' + LTRIM(STR(@Inc_File)) +  ' ON '
SELECT @Counter=count(1) FROM @DBFiles
SET @FileId =1
SET @Files = ''
WHILE @FileId <= @Counter
BEGIN
SELECT @LogicalName = Name, @FileName = FilePath FROM @DBFiles where ID=@Fileid
print @FileName
SET @Files = @Files + '(Name =' + @LogicalName + ',' + 'FileName = ' + '''' + LEFT(@FileName,CHARINDEX('.',@FileName) - 1)  

+  LTRIM(STR(@Inc_File)) +  '.ss'')'
IF @FileId <> @Counter
SET @Files=@Files + ','
SET @FileId = @FileId + 1 
END
SET @SQLQuery = @SQLQuery + @Files + ' AS SNAPSHOT OF ' + @DatabaseName
EXEC (@SQLQUERY)
IF @@ERROR =0
SET @Message= 'Database Snapshot created successfully'
END

Deadlocks. Cómo identificarlos?

 

Muchos de nosotros hemos tenido que lidiar con el tema de los deadlocks. Quizás la mayoría de nosotros piensa que son temas que debe de resolver el programador, algunos dirán que debe de ser el DBA. El resultado final es que debe de ser un esfuerzo conjunto entre los dos equipos. El Desarrollador necesita del DBA para identificar y guiar al programador y el Desarrollador hará su magia para resolver los conflictos que se puedan presentar.

Como detectarlos?

Tenemos varias formas de hacerlo, la fácil y la complicada. A mi parecer la complicada es el Profiler. Por qué? Sencillo, cuantos pasos se requieren para configurar el Profiler y adaptarlo para que capture los deadlocks? Lo han intentado utilizar en sistemas altamente transaccionales? En Profilers que almacenan cerca de 25 millones de líneas en unas 4 horas de ejecución. Analizar luego esa data es complejo, se los aseguro.
El uso de Profiler con deadlocks es un tema que analizaremos posteriormente. Hoy me quiero enfocar en el mas sencillo de los dos. Utilizar los eventos de WMI de Windows es la forma mas sencilla de configurar y detectar los deadlocks.

Cuales son los pasos?

1. Ingresar a la consola de SQL Server.
2. Expandir el SQL Server Agent.
3. Sobre Alerts, presionar botón derecho. Crear nueva Alerta.
4. Seleccionar el Type WMI Event Alert
5. En el query ingresar SELECT * FROM DEADLOCK_GRAPH



Idealmente se debe de crear un JOB que permita la respuesta a esta alerta. El job lo que hará es insertar los datos recolectados por a alerta para nuestro posterior análisis.

Se debe de crear la tabla en donde vamos a almacenar los datos.

CREATE TABLE [dbo].DeadlockTrace(

[AlertTime] [datetime] NULL,

[DeadlockGraph] [varchar](max) NULL

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

El Job debe de tener la siguiente sentencia :

INSERT INTO DeadlockTrace  (AlertTime, DeadlockGraph)
VALUES (getdate(), N'$(ESCAPE_SQUOTE(WMI(TextData))))')

Recordemos asignar en la alerta en el TAB Response el Job que recién acabamos de crear.

Para tener una vista comprendible de los datos ejecutamos la siguiente sentencia.

WITH CTE AS (
  SELECT AlertTime,
    CAST(REPLACE(REPLACE(CAST(DeadlockGraph AS VARCHAR(MAX)),'</TextData>)',''),'<TextData>','') AS XML) AS DeadlockGraph
  FROM dbo].[DeadlockTrace] )
SELECT ROW_NUMBER() OVER(PARTITION BY [AlertTime] ORDER BY [AlertTime] DESC) AS DeadlockNumber,
  [AlertTime],
  [DeadlockGraph],
  [PagelockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/pagelock[1]/@objectname', 'varchar(200)'),
  [DeadlockObject] = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/resource-list[1]/objectlock[1]/@objectname', 'varchar(200)'),
  [Victim] = CASE WHEN Deadlock.Process.value('@id', 'varchar(50)') = CTE.[DeadlockGraph].value('/deadlock-list[1]/deadlock[1]/@victim', 'varchar(50)') THEN 1 ELSE 0 END,
  [Procedure] = Deadlock.Process.value('executionStack[1]/frame[1]/@procname[1]', 'varchar(200)'),
  [LockMode] = Deadlock.Process.value('@lockMode', 'char(1)'),
  [Code] = Deadlock.Process.value('executionStack[1]/frame[1]', 'varchar(1000)'),
  [ClientApp] = Deadlock.Process.value('@clientapp', 'varchar(100)'),
  [HostName] = Deadlock.Process.value('@hostname', 'varchar(20)'),
  [LoginName] = Deadlock.Process.value('@loginname', 'varchar(20)'),
  [TransactionTime] = Deadlock.Process.value('@lasttranstarted', 'datetime'),
  [InputBuffer] = Deadlock.Process.value('inputbuf[1]', 'varchar(1000)')
FROM CTE
CROSS APPLY
  CTE.[DeadlockGraph].nodes('/deadlock-list/deadlock/process-list/process') AS Deadlock(Process)
ORDER BY 1 DESC

Y listo, ya podemos analizar de una manera mas sencilla los deadlocks en nuestra base de datos.

Script para crear Database Snapshots Dinámicos

Conforme han ido pasando las versiones de SQL Server notamos que aun no existe una interfaz gráfica que permita la creación de Database Snapshots, el código es sencillo si se cuenta con únicamente un datafile, el problema se hace mas grande cuando estas bases de datos tienen mas de un datafile. Pensando en estas situaciones es que decidí crear el siguiente SP que permite la creación dinámica de un snapshot de base de datos. Esta es la primer versión del SP, el cual estará sujeto a cambios o mejoras. Cualquier sugerencia es bien recibida.

Saludos.

CREATE PROCEDURE usp_CreateSnapshot (@SourceDB varchar(200),@DestDB varchar(200)=NULL, @Message VARCHAR(200) =NULL OUTPUT)

AS



--Created by Adrian Miranda Cordero

--Create a snapshot dinamically

--Code can be changed ad needed just remember to say thank you every time you rise a prayer XD
  DECLARE @DatabaseName as varchar(200)

DECLARE @DestDBName as varchar(200)

DECLARE @SQLQuery as Nvarchar(MAX)

DECLARE @UseSQL as varchar (205)

DECLARE @Counter as smallint

DECLARE @FileId as smallint

DECLARE @FileName as nvarchar(max)

DECLARE @LogicalName as varchar(200)

DECLARE @Files as varchar(max)

SET @DatabaseName =@SourceDB

IF @DestDB IS NULL

SET @DestDBName= @SourceDB




ELSE
  SET @DestDBName=@DestDB

SET @SQLQuery =''

IF (SELECT COUNT(1) FROM sys.databases WHERE name in ('master', 'msdb',',model','tempdb') and name = @Databasename) > 0

SET @Message='Can not create a database snapshot based on a system database'

ELSE IF (SELECT NAME FROM sys.databases WHERE NAME = @DatabaseName) IS NULL

SET @Message= 'Database not found.'




ELSE

BEGIN
   DECLARE @DBFiles TABLE (

ID smallint Identity(1,1),

FileId int,

Name varchar (max),

FilePath varchar (max)

)

SET @SQLQuery = 'SELECT FILE_ID,name,Physical_Name FROM sys.database_files WHERE type_desc <>''''LOG''''';

SET @UseSQL = 'USE ' + @DatabaseName + '; EXEC sp_executesql N''' + @SQLQuery + '''';

INSERT INTO @DBFiles Exec (@UseSQL)

SET @SQLQuery = 'CREATE DATABASE ' + ISNULL(@DestDBName,@DatabaseName) + '_Snap ON '

SELECT @Counter=count(1) FROM @DBFiles

SET @FileId =1

SET @Files = ''

WHILE @FileId <= @Counter

BEGIN

SELECT @LogicalName = Name, @FileName = FilePath FROM @DBFiles where ID=@Fileid

print @FileName

SET @Files = @Files + '(Name =' + @LogicalName + ',' + 'FileName = ' + ''''+ LEFT(@FileName,CHARINDEX('.',@FileName)) + 'ss'')'

IF @FileId <> @Counter

SET @Files=@Files + ','

SET @FileId = @FileId + 1

END

SET @SQLQuery = @SQLQuery + @Files + ' AS SNAPSHOT OF ' + @DatabaseName

EXEC (@SQLQUERY)

IF @@ERROR =0

SET @Message= 'Database Snapshot created successfully'




END