Trabalhando com SQL CLR – Resumo

Então pessoas, uma das features que mais gosto do SQL Server é o suporte a CLR, que permite muitas possibilidades na criação de Stored Procedures, Funções, Types e Triggers com as linguagens C# e Visual Basic, e outras linguagens .NET, como o Delphi e F#.

No blog, busquei explorar de forma introdutória como trabalhar com esta feature e uma visão geral de cada objeto que é possível desenvolver. Em paralelo também tratei algumas extensibilidades, como trabalhar como F#, Web Services, referenciar projetos de terceiros nos projetos CLR e novidades no lançamento do Visual Studio 2010.

Introdução:

Este é um passo-a-passo de como iniciar com SQL CLR, desde questões mais á nível de SQL Server, até questões á nível de desenvolvimento (debug e publicação).

Integração SQL CLR no SQL Server – Uma Visão Geral [NOVO]

https://sqlfromhell.wordpress.com/2012/04/20/integracao-sql-clr-no-sql-server-visao-geral/

Habilitando CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/09/19/habilitando-clr

Criando funções CLR para o SQL Server:

https://sqlfromhell.wordpress.com/2009/09/26/funcoes-clr

Debug de funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/03/debug-clr

Publicando Assemblies e Funções CLR no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/10/publicando-clr

Publicando Assemblies no SQL Server pelo Visual Studio:

https://sqlfromhell.wordpress.com/2010/05/15/publicando-assemblies-clr-no-sql-server-pelo-visual-studio

Visão Geral:

Para quem quer conhecer cada um dos objetos que permitem desenvolvimento na integração SQL CLR, elaborei uma visão geral orientada á muitos exemplos de cada um.

Stored Procedure:

https://sqlfromhell.wordpress.com/2010/07/03/clr-stored-procedure

https://sqlfromhell.wordpress.com/2010/07/13/clr-stored-procedure-2

User Defined Function Scalar:

https://sqlfromhell.wordpress.com/2010/07/25/trabalhando-com-clr-user-defined-function-scalar

Table-value User Defined Functions:

https://sqlfromhell.wordpress.com/2010/12/02/sql-clr-table-value-user-defined-functions

User Defined Aggregate Functions:

https://sqlfromhell.wordpress.com/2010/12/14/user-defined-aggregate-functions

User Defined Type:

https://sqlfromhell.wordpress.com/2010/12/22/sql-clr-user-defined-type

Triggers:

https://sqlfromhell.wordpress.com/2010/12/31/sql-clr-trigger

Estendendo:

Atendendo alguns pedidos e uma “aventura” pessoal (F#).

Web Services:

https://sqlfromhell.wordpress.com/2010/03/07/webservice-e-funes-clr-no-sql-server

F# no SQL Server:

https://sqlfromhell.wordpress.com/2009/10/17/fsharp

Referenciando uma Class Library em Projetos SQL-CLR (Draft):

https://sqlfromhell.wordpress.com/2009/09/30/crl-classlibrary

Trabalhando com SQL CLR e Spatial Data [NOVO]

https://sqlfromhell.wordpress.com/2011/07/27/trabalhando-com-sql-clr-e-spatial-data/

Novidades:

Como com cada lançamento do Visual Studio temos o suporte SQL CLR permitindo a utilização das novas .NET Framework, mostrei que no caso do Visual Studio 2010 tivemos até algumas novidades mais interessantes.

Novidades do Visual Studio 2010:

https://sqlfromhell.wordpress.com/2010/05/16/o-que-mudou-nos-projetos-sql-clr-do-visual-studio-2010

Anúncios

Imagens no banco de dados – Resumo

Nestes três anos, trabalhei várias formas de utilizar imagens armazenadas em banco de dados com C# e SQL Server, eu espero que tenham gostado:

Imagens no banco de dados 1 – ASP.NET Web Forms

Imagens no banco de dados 2 – ASP.NET MVC

Imagens no banco de dados 3 – Windows Forms

Imagens no banco de dados 4.1 – Relatórios em RDLC

Imagens no banco de dados 4.2 – Relatórios em RDLC

Imagens no banco de dados 5 – Importando arquivos por T-SQL

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

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 .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