Trabalhando com SMO – Resumo

Então pessoas, abaixo os links dos artigos que trataram sobre SQL Server Management Objects (SMO) que escrevi utilizando PowerShell e .NET para administrar o SQL Server.

Primeiramente os artigos em C#:

Gerenciando o SQL Server por meio de aplicações .NET – Primeiros passos

Gerando backups do SQL Server por .NET

Gerando scripts do banco de dados por .NET – Passo 1

Gerando scripts do banco de dados por .NET – Passo 2

Criando e manipulando Tabelas e Colunas no SQL Server com .NET

Criando Constraints e Índices no SQL Server com .NET

Capturando consultas executadas no SQL Server (Trace) com .NET via SMO

Criando um arquivo de trace com .NET via SMO

Capturando eventos do SQL Server com .NET via SMO sem Triggers ou Trace

Capturando eventos de um determinado bancos de dados com .NET via SMO sem Triggers ou Trace

Monitorando Deadlocks do SQL Server com SMO

E os artigos que traduzi para PowerShell:

Gerando backups do SQL Server por PowerShell

Gerando scripts do banco de dados por PowerShell – Passo 1

Gerando scripts do banco de dados por PowerShell – Passo 2

Criando e manipulando Tabelas e Colunas no SQL Server com PowerShell

Criando Constraints e Índices no SQL Server com PowerShell

Capturando consultas executadas no SQL Server (Trace) com PowerShell via SMO

Criando um arquivo de trace com PowerShell via SMO

Anúncios

Monitorando Deadlocks do SQL Server com SMO

Na semana passada vimos um exemplo de Deadlock com SQL Server, e para monitorar e rastrear eventos deste gênero podemos optar por várias alternativas, dentre elas a utilização de Trace Flags e SQL Server Profiler, e também podemos monitorar de forma bem transparente com os eventos do SMO.

De uma maneira bem simples, o código abaixo monitora os eventos de Deadlock de uma determinada instancia do SQL Server, e quando ocorre uma Deadlock, ele informa os SPID que estão relacionadas a este evento, e gera um Deadlock File para obter maiores detalhes sobre o deadlock.

using System;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent3
    {
        public static void Run()
        {
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            var eventSet = new ServerTraceEventSet(
                ServerTraceEvent.LockDeadlock,
                ServerTraceEvent.LockDeadlockChain,
                ServerTraceEvent.DeadlockGraph
                );

            server.Events.SubscribeToEvents(eventSet, OnDeadlock);

            server.Events.StartEvents();

            Console.ReadKey();

            server.Events.StopEvents();

            server.Events.UnsubscribeFromEvents(eventSet);
        }

        private static void OnDeadlock(object sender, ServerEventArgs e)
        {
            switch (e.EventType)
            {
                case EventType.DeadlockGraph:
                    DeadlockGraph(e);
                    break;
                case EventType.LockDeadlockChain:
                    LockDeadlockChain(e);
                    break;
                case EventType.LockDeadlock:
                    Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
                    Console.WriteLine("Vítima escolhida: SPID {0}", e.Spid);
                    break;
            }

            Console.WriteLine();
        }

        private static void LockDeadlockChain(ServerEventArgs e)
        {
            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine(((string)textData.Value).TrimEnd());
        }

        private static void DeadlockGraph(ServerEventArgs e)
        {
            var path = string.Format(@"C:\TEMP\{0}.xdl", Guid.NewGuid());

            var textData = e.Properties.FirstOrDefault(prop => prop.Name == "TextData");

            if (textData == null) return;

            var xml = XDocument.Parse(((string)textData.Value))
                .Elements("TextData")
                .Elements("deadlock-list")
                .FirstOrDefault();

            if (xml == null) return;

            using (var file = new StreamWriter(path)) file.Write(xml.ToString(SaveOptions.DisableFormatting));

            Console.WriteLine("Data/Hora: {0:dd/MM/yyyy HH:mm}", e.PostTime);
            Console.WriteLine("Arquivo gerado: {0}", path);
        }
    }
}

A execução do código:

Monitorando Deadlock com SMO

Os arquivos gerados:

Deadlock Files

E um arquivo de deadlock file gerado pelo SMO aberto no SQL Server Management Studio, possuindo informações sobre os processos, os objetos em lock e a query que foi executada.

Deadlock File

Assim terminamos os artigos sobre SMO, espero que tenham gostado de conhecer um pouco mais sobre esta feature.

Capturando eventos de um determinado bancos de dados com .NET via SMO sem Triggers ou Trace

Assim como explicado no artigo da semana passada, o monitoramento de eventos do SMO pode capturar eventos tanto no nível mais macro do SQL Server, quanto nos níveis mais específicos, como eventos relativos a tabelas ou outras estruturas do banco de dados.

Quem viu o artigo da semana passada irá perceber que não há muita diferença para capturar eventos de objetos específicos do SQL Server, pois as diferenças básicas são os objetos aos quais se associam os eventos (eventos de banco de dados, se associa a um objeto Database, eventos de instancia, a um objeto Server) e o grupo de eventos (EventSet) a ser utilizado para identificar quais os eventos que serão monitorados.

No exemplo a seguir veremos como implementar a captura de eventos de um determinado banco de dados:

using System;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent2
    {
        public static void Run()
        {
            // Definindo um grupo de eventos a serem monitorados
            var dbEventSet = new DatabaseEventSet(DatabaseEvent.CreateTable);

            // Relacionando os eventos de um determinado banco de dados
            //   e definindo o método OnCreate para o tratamento destes eventos
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            #region Criando um banco de dados

            var db1 = new Database(server, "NovoBanco");

            db1.Create();

            #endregion

            var database = server.Databases["NovoBanco"];

            database.Events.SubscribeToEvents(dbEventSet, OnCreate);

            // Iniciando a captura dos eventos
            database.Events.StartEvents();

            #region Criando uma tabela no banco de dados criado

            Console.ReadKey();

            var table1 = new Table(db1, "NovaTabela", "dbo");

            table1.Columns.Add(new Column(table1, "Codigo", DataType.Int) { Nullable = false });

            table1.Create();

            #endregion

            // Terminando a captura dos eventos

            Console.ReadKey();

            database.Events.StopEvents();

            database.Events.UnsubscribeFromEvents(dbEventSet);

            #region Excluindo o banco de dados criado

            db1.Drop();

            #endregion
        }

        private static void OnCreate(object sender, ServerEventArgs e)
        {
            // Exibindo o SPID e a data do evento
            Console.WriteLine("SPID: {0}", e.Spid);
            Console.WriteLine("Data: {0:dd/MM/yyyy HH:mm}", e.PostTime);

            // Exibindo informações sobre o que foi executado
            Console.WriteLine("Foi criada uma tabela!");

            // Relacionando outras propriedades que serão exibidas

            var showProperties = new[] { "DatabaseName", "LoginName" };

            var properties = e.Properties.Where(prop => showProperties.Contains(prop.Name));

            foreach (var prop in properties)
            {
                Console.WriteLine("{0}: {1}", prop.Name, prop.Value);
            }

            // Exibindo o comando que acionou o evento

            var command = e.Properties.FirstOrDefault(prop => prop.Name == "TSQLCommand");

            if (command != null)
            {
                var xml = XDocument.Parse((string)command.Value)
                    .Elements("TSQLCommand")
                    .Elements("CommandText")
                    .FirstOrDefault();

                if (xml != null)
                {
                    Console.WriteLine("CommandText: {0}", xml.Value);
                }
            }

            Console.WriteLine();
        }
    }
}

E como resultado deste exemplo:

Aqueles que gostariam de saber como funciona a implementação de eventos de notificação, podem encontrar no link abaixo referências de como implementá-los com T-SQL, no qual conhecimento de Service Broker será indispensável:
http://msdn.microsoft.com/en-us/library/ms178080.aspx

Capturando eventos do SQL Server com .NET via SMO sem Triggers ou Trace

Neste artigo de número 300, vermos uma funcionalidade bem interessante do SMO, que permite capturar eventos do SQL Server, seja em nível de instância, de banco de dados ou até no nível de tabela, permitindo monitorar criação e alterações de estruturas e configurações do SQL Server sem a necessidade de usar triggers ou trace, tudo por meio do serviço de notificações de eventos do Service Broker. Mas se você não “manja” de Service Broker, fique tranquilo, o SMO permite a utilização desta funcionalidade de forma bem transparente.

Neste exemplo, veremos como capturar/monitorar eventos de criação de banco de dados e de tabelas com o SMO:

using System;
using System.Linq;
using System.Xml.Linq;
using Microsoft.SqlServer.Management.Smo;

namespace DemoSmo
{
    static class DemoEvent1
    {
        public static void Run()
        {
            // Definindo um grupo de eventos a serem monitorados
            var eventSet = new ServerEventSet(ServerEvent.CreateDatabase, ServerEvent.CreateTable);

            // Relacionando os eventos a uma instância do SQL Server
            //   e definindo o método OnCreate para o tratamento destes eventos
            const string serverName = @".\SQLEXPRESS";

            var server = new Server(serverName);

            server.Events.SubscribeToEvents(eventSet, OnCreate);

            // Iniciando a captura dos eventos
            server.Events.StartEvents();

            Console.ReadKey();

            // Executando alguns comandos para testar se os eventos estão sendo monitorados

            const string query = @"
USE [master]
GO

CREATE DATABASE [NovoBanco]
GO

USE [NovoBanco]

CREATE TABLE [NovaTabela] (CODIGO INT)
GO

USE [master]
GO

DROP DATABASE [NovoBanco]
GO";

            server.ConnectionContext.ExecuteNonQuery(query);

            // Terminando a captura dos eventos

            Console.ReadKey();

            server.Events.StopEvents();

            server.Events.UnsubscribeFromEvents(eventSet);
        }

        private static void OnCreate(object sender, ServerEventArgs e)
        {
            // Exibindo o SPID e a data do evento
            Console.WriteLine("SPID: {0}", e.Spid);
            Console.WriteLine("Data: {0:dd/MM/yyyy HH:mm}", e.PostTime);

            // Exibindo informações sobre o que foi executado
            switch (e.EventType)
            {
                case EventType.CreateDatabase:
                    Console.WriteLine("Foi criado um banco de dados!");
                    break;
                case EventType.CreateTable:
                    Console.WriteLine("Foi criada uma tabela!");
                    break;
            }

            // Relacionando outras propriedades que serão exibidas

            var showProperties = new[] { "DatabaseName", "TableName", "LoginName" };

            var properties = e.Properties.Where(prop => showProperties.Contains(prop.Name));

            foreach (var prop in properties)
            {
                Console.WriteLine("{0}: {1}", prop.Name, prop.Value);
            }

            // Exibindo o comando que acionou o evento

            var command = e.Properties.FirstOrDefault(prop => prop.Name == "TSQLCommand");

            if (command != null)
            {
                var xml = XDocument.Parse((string)command.Value)
                    .Elements("TSQLCommand")
                    .Elements("CommandText")
                    .FirstOrDefault();

                if (xml != null)
                {
                    Console.WriteLine("CommandText: {0}", xml.Value);
                }
            }

            Console.WriteLine();
        }
    }
}

E como os eventos serão exibidos neste exemplo:

Capturando eventos do SQL Server com .NET via SMO

Próxima semana, eu pretendo demonstrar como capturar/monitorar eventos em um nível mais específico, e na última semana deste mês, veremos como monitorar alguns eventos que vão além de alterações estruturais do banco de dados.

Criando um arquivo de trace com PowerShell via SMO

Não muito diferente do artigo original com .NET, com PowerShell também conseguimos criar um script simples para gerar um arquivo de trace de uma determinada instancia do SQL Server.

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll")
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfoExtended.dll")

function Get-Date2(){
    [System.DateTime]::UtcNow
}

$template = "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Tuning.tdf"

$date = Get-Date

$file = "C:\Temp\Trace$($date.Year)-$($date.Month)-$($date.Day) $($date.Hour)-$($date.Minute)-$($date.Second).trc"

$conn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo

$conn.ServerName = "Dalilah"

$traceReader = new-object Microsoft.SqlServer.Management.Trace.TraceServer

$traceReader.InitializeAsReader($conn, $template)

$traceFile = new-object Microsoft.SqlServer.Management.Trace.TraceFile

$traceFile.InitializeAsWriter($traceReader, $file)

$timeout = (Get-Date2).AddSeconds(30)

while ($traceFile.Write() -eq $TRUE -and $timeout -gt (Get-Date2)) { }

$traceFile.Close()

$traceReader.Close()

E o resultado:

Arquivo de trace gerado com PowerShell via SMO

Criando um arquivo de trace com .NET via SMO

Uma prática rápida para otimizar um ou vários bancos de dados é a criação de um arquivo de Trace por meio do SQL Server Profiler e utilizar este arquivo como carga para o Database Engine Tuning Advisor (DTA). O DTA, por sua vez, gerará uma relação de índices e estatísticas para os bancos de dados baseado no arquivo utilizado como carga.

Para a criação deste arquivo de trace, se utiliza normalmente o SQL Server Profiler com o template Tuning. Mas também podemos utilizar as bibliotecas do SMO para gerar este arquivo trace, como veremos a seguir.

Em relação a utilização do SMO, da mesma forma que utilizamos um objeto TraceServer como para capturar as consultas executadas no SQL Server, este objeto também será utilizado neste exemplo para o mesmo fim, só que em conjunto de um objeto TraceFile que escreverá a captura em um arquivo trace.

Então vamos ao código:

using System;
using System.Threading;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;

namespace DemoSmo
{
    static class DemoTrace2
    {
        // Caminho do template de Tuning
        private const string TuningFile =
            @"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Tuning.tdf";
        
        /// <summary>
        /// Ref.: http://technet.microsoft.com/en-US/library/ms345134.aspx
        /// </summary>
        public static void Run()
        {
            // Caminho onde será salvo o arquivo de trace
            var file = string.Format(@"C:\Temp\Trace{0:yyyy-MM-dd HH-mm}.trc", DateTime.Now);

            var conn = new SqlConnectionInfo { ServerName = @"DALILAH" };

            // Criando o objeto TraceServer
            var traceReader = new TraceServer();

            // Iniciando a leitura do trace a partir do template Tuning do SQL Server Profiler
            traceReader.InitializeAsReader(conn, TuningFile);

            // Criando o objeto TraceFile
            var traceFile = new TraceFile();

            // Iniciando a escrita da captura no arquivo de trace
            traceFile.InitializeAsWriter(traceReader, file);

            // Executando a escrita dos traces
            var ts = new Thread(() => { while (traceFile.Write()) { } });

            ts.Start();

            // Esperar 30 segundos
            Thread.Sleep(30000);

            // Terminando a escrita do trace
            traceFile.Close();

            // Terminando a captura do trace
            traceReader.Close();
        }
        
    }
}

E o nosso resultado:

Arquivo de trace gerado com .NET via SMO

Capturando consultas executadas no SQL Server (Trace) com PowerShell via SMO

Diferente do artigo original “Capturando consultas executadas no SQL Server (Trace) com .NET via SMO”, não consegui utilizar no PowerShell Threads ou Jobs (Jobs do PowerShell e não do SQL Server) para criar uma estrutura de timeout simples para este exemplo.

Assim optei por limitar a quantidade de linhas geradas pelo Trace (no caso 10 linhas), ao invés de criar um timeout com Get-Date (que vou demonstrar no exemplo da próxima semana).

Sem mais, segue o script para trabalhar com Trace no SQL Server com PowerShell:

# Referências
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll")
[void][system.reflection.assembly]::loadfrom("C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfoExtended.dll")

$template = "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\Templates\Microsoft SQL Server\100\Standard.tdf"

$conn = new-object Microsoft.SqlServer.Management.Common.SqlConnectionInfo

$conn.ServerName = ".\SQLEXPRESS"

#Criando o objeto TraceServer
$traceReader = new-object Microsoft.SqlServer.Management.Trace.TraceServer

#Iniciando a leitura do trace a partir do template Standard do SQL Server Profiler
$traceReader.InitializeAsReader($conn, $template)

$line = 0

#Lê até 10 comandos executados (enquanto $line < 10)
while ($traceReader.Read() -eq $TRUE -and $line -lt 10)
{
    $name = $traceReader.GetValue($traceReader.GetOrdinal("EventClass"));

    if ($name.Equals("SQL:BatchCompleted") -or $name.Equals("RPC:Completed")) {

        $spid = $traceReader.GetValue($traceReader.GetOrdinal("SPID"))

        $query = $traceReader.GetValue($traceReader.GetOrdinal("TextData"))

        $duration = $traceReader.GetValue($traceReader.GetOrdinal("Duration"))

        "-- SPID: $spid | Duration: $duration"

        "`n"

        Write-Host $query

        "`n `n"

        $line++
    }
}

#Fechando o trace de leitura
$traceReader.Close()