Utilizando Events Notifications em conjunto com a Função EventData() e a Linguagem XQuery no Microsoft SQL Server 2008 R2 e 2012.

Pessoal, boa tarde!

Após o meu período de férias que a mais de 2 anos eu não aproveitava, estou voltando a minha rotina normal de trabalho, como DBA, Consultor e Professor Universitário. Além disso, é claro, com as minhas atividades relacionadas à Comunidade Microsoft em especial ao Mundo de Banco de Dados e o SQL Server.

Pois bem, ainda não estou no mesmo ritmo, então vou começar com uma pequena dica, abordando um pouco sobre Events Notifications, Função EventData() e a Linguagem XQuery.

Antes de apresentar qualquer código, quero destacar um pouco sobre cada recurso:

  • Events Notifications: Notificações de eventos executam em resposta a uma variedade de eventos do rastreamento do SQL e instruções de DDL (linguagem) de definição de dados Transact-SQL, enviando informações sobre esses eventos para um serviço do Service Broker.

As Notificações de eventos podem ser usadas para fazer o seguinte:

  • Registrar e revisar as alterações ou atividades que ocorrerem no banco de dados.
  • Executar uma ação em resposta a um evento em forma assíncrona em vez de síncrona.

Quando utilizamos Notificações de eventos podemos oferecer uma programação alternativa para uso de gatilhos DDL e rastreamento do SQL. Os eventos são executados assincronamente, fora do escopo de uma transação.

Portanto, ao contrário de gatilhos DDL, notificações de evento podem ser usadas dentro de um aplicativo de banco de dados para responder a eventos sem usar quaisquer recursos definidos pela transação imediata, como também, para executar uma ação dentro de uma instância do SQL Server em resposta a um evento do rastreamento do SQL.

As Informações do evento são entregues para o Serviço Service Broker como uma variável de tipo xml, que fornece informações sobre quando ocorre um evento, sobre o objeto de banco de dados afetado, a instrução de lote Transact-SQL envolvida e outras informações.

  • Função EventData():Retorna informações sobre eventos de servidor ou banco de dados. EVENTDATA é chamado quando uma notificação de evento é acionado, e os resultados são retornados para o corretor do serviço especificado. EVENTDATA também pode ser usado dentro do corpo de um gatilho DDL ou de logon.
  • Linguagem XQuery: A Transact-SQL oferece suporte a um subconjunto da linguagem da XQuery usado para consultar o tipo de dados xml. Essa implementação da XQuery está alinhada com o Working Draft de julho de 2004.

A XQuery é uma linguagem que pode consultar dados XML estruturados ou semi-estruturados. Com o suporte ao tipo de dados xml fornecido no Mecanismo de Banco de Dados, os documentos podem ser armazenados em um banco de dados e consultados usando a XQuery.

Outra característica, que a XQuery apresenta, relaciona-se com a linguagem XPath existente, com suporte adicional para uma melhor iteração, melhores resultados de classificação e com a capacidade de construir o XML necessário.

Legal, agora que conhecemos um pouquinho sobre os recursos que vamos trabalhar, suas funcionalidades e importância, podemos começar a se envolver com o nosso código de exemplo, como de costume dividido em algumas partes para facilitar o entendimento e compreensão de todos.

Parte 1

O primeiro passo será a criação da Tabela responsável em armazenar as informações referentes às transações que estaremos trabalhando. Esta tabela será denominada DDL_Transacoes.

Abaixo apresenta o Código 1, referente a estrutura da tabela DDL_Transacoes:

— Código 1 – Criando a Tabela DDL_Transacoes —

Create Table DDL_Transacoes

(TransID Int Identity(1,1) Primary Key,

 DataTransacao DateTime Default GetDate(),

 UsuarioTransacao VarChar(100) Default User_Name(),

 LoginTransacao Varchar(100) Default Suser_Name(),

 LoginUsuarioSQLTransacao Varchar(100) Default Original_Login(),

 TipoEvento NVarchar(200) Null,

 Objeto NVarchar(200)

 TipoObjeto NVarchar(200) Null,

 Comando_TSQL NVarchar(Max) Null)

Observe que as colunas TipoEvento, Objeto, TipoObjeto e Comando_TSQL foram definidas com o Tipo de Dados NVarChar, o que vai possibilitar armazenar valores que contenham qualquer tipo de caractere e formato, inclusive XML.

Parte 2

Após a criação da tabela, partiremos para o próximo passo, que consiste na criação do Trigger DDL, denominado Trigger_DDL_Transacoes, sendo este o principal recurso que estaremos utilizando nesta dica.

Vale ressaltar, que os Triggers DDL são um tipo especial de gatilho acionados em resposta às instruções de DDL (Linguagem de Definição de Dados). Eles podem ser usados para executar tarefas administrativas no banco de dados, como auditoria e regulação de operações de banco de dados.

Nesta parte do código nosso Trigger DDL, estará sendo chamado quando os Eventos em Nível de Banco de Dados referente a comandos e instruções de definição de dados venham a ser executadas.

A seguir apresento o Código 2, responsável pela criação do Trigger DDL:

— Código 2 – Criação do Trigger – Trigger_DDL_Transacoes —

Create Trigger Trigger_DDL_Transacoes

On Database

For DDL_DATABASE_LEVEL_EVENTS

As

 Begin

  Set NoCount On

  Declare @DadosXML XML

  Set @DadosXML=EVENTDATA()

  Insert Into DDL_Transacoes(TipoEvento,Objeto,TipoObjeto,Comando_TSQL)

                                Values(@DadosXML.value(‘(EVENT_INSTANCE/EventType)[1]’,’NVarchar(200)’) ,

                                           @DadosXML.value(‘(EVENT_INSTANCE/ObjectName)[1]’,’NVarchar(200)’) ,

                                                                                                                           @DadosXML.value(‘(EVENT_INSTANCE/ObjectType)[1]’,’NVarchar(200)’) ,

                                                                                                                           @DadosXML.value(‘(EVENT_INSTANCE/TSQLCommand/CommandText)[1]’,’NVarchar(Max)’))

 End 

Muito bom, nosso ambiente já esta pronto, a tabela DDL_Transacoes e a Trigger Trigger_ DDL_Transacoes estão criados e preparados para serem utilizados em nossos cenários de teste.

Mais uma vez quero destacar que esta Dica não tem a finalidade de apresentar ou ilustrar uma forma de auditoria, o objetivo é destacar como estes recursos podem ser utilizados em conjunto, construíndo em ambiente de trabalho simples, mas com recursos que podem ser de extrema importância nos processos de definição de objetos.

Cenários de Teste

Para demonstrar o funcionamento dos objetos que criamos anteriormente vamos realizar as operações básicas executadas em qualquer aplicação ou sistema que faça uso de um banco de dados. Estou me referindo as operações de CRUD, onde:

  • C – Commit;
  • R – Read;
  • U – Update; e
  • D – Delete.

Vamos começar para criação da tabela Registros, conforme apresento abaixo:

Create Table Registros

 (Codigo Int,

  Descricao Varchar(100)) 

Go

Em seguida vamos realizar a Inserção de algumas linhas de dados, conforme apresentado a seguir:

Insert Into Registros Values (1,’Valor 1′)

Insert Into Registros Values (2,’ Valor 2′)

Insert Into Registros Values (3,’ Valor 3′)

Insert Into Registros Values (1,’Valor 4′)

Insert Into Registros Values (2,’ Valor 5′)

Insert Into Registros Values (3,’ Valor 6′)

Insert Into Registros Values (1,’Valor 7′)

Insert Into Registros Values (2,’ Valor 8′)

Insert Into Registros Values (3,’ Valor 9′)

Go

A seguir, realizaremos o processo de Atualização de Dados, fazendo uso do comando Update, logo abaixo:

Update Registros

Set Descricao=’Valor n3′

Where Codigo=3

Update Registros

Set Descricao=’Valor n5′

Where Codigo=5

Go

E finalmente vamos realizar a Exclusão dos registros e posteriormente a eliminação da tabela Registros, a seguir:

Delete From Registros

Where Codigo In (3,5)

Go

Drop Table Registros

Pronto, realizamos as operações básicas de Manipulação de Registros e Objetos e agora poderemos ver o resultado do que foi identificado, processado, conversado e armazenado pelos Events Notifications em conjunto com a Função EventData() e a Linguagem XQuery. Para isso vamos utilizar o Código 3 apresentado abaixo:

— Código 3 – Consultando os valores obtidos pelos Events Notifications na Tabela DDL_Transacoes –

Select * from DDL_Transacoes

Após executar o Código 3, poderemos notar que o Eventos foram armazenados na Tabela DDL_Transacoes de forma correta e somente aqueles relacionados a Linguagem DDL – Linguagem de Definição de Dados, neste caso, os Comandos Create Table e Drop Table, conforme ilustra a Figura 1.

DDL_Transacoes_Registros

Figura 1 – Eventos armazenados na Tabela DDL_Transacoes

 

Desta forma, estou finalizando esta dica, acredito que conseguindo demonstrar de forma bem simples e prática uma das maneiras que podemos combinar recursos no SQL Server, fazendo uso de suas funcionalidades, criando mecanismos e facilidades que podem ser utilizadas em nosso dia-á-dia.

Mais uma vez obrigado por sua visita, fique a vontade para seus comentários, críticas, sugestões e observações.

Nos encontramos em breve.

Até mais.

Anúncios

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes, bem como, Professor Titular na Fatec São Roque. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

5 comentários em “Utilizando Events Notifications em conjunto com a Função EventData() e a Linguagem XQuery no Microsoft SQL Server 2008 R2 e 2012.”

  1. Galvao, pode me tirar uma duvida? na funcao EVENTDATA como eu sei quais campos ela tem que eu posso utilizar?
    no exemplo voce citou: EVENT_INSTANCE/EventType
    EVENT_INSTANCE/objectname

    se por exemplo eu quisesse pegar o IP?

    Curtir

  2. mas me ficou uma duvida que não consigo encontrar na web ou não estou sabendo como procurar na function EVENTDATA() como eu sei os campos que posso utilizar? No caso vc usou estes:

    SELECT @Evento.value(‘(/EVENT_INSTANCE/EventType/text())[1]’,’varchar(50)’) Tipo_Evento,

    @Evento.value(‘(/EVENT_INSTANCE/PostTime/text())[1]’,’datetime’) PostTime,

    Curtir

    1. Renata,

      Você vai ter que buscar informações para estas categorias:

      Extended Events Tasks
      ——————————————————————————–

      Using Management Studio or Transact-SQL to execute Transact-SQL Data Definition Language (DDL) statements, dynamic management views and functions, or catalog views, you can create simple or complex SQL Server Extended Events troubleshooting solutions for your SQL Server environment.

      Task Description
      Topic

      Use the Object Explorer to manage event sessions.
      Manage Event Sessions in the Object Explorer

      Describes how to create an Extended Events session.
      Create an Extended Events Session

      Describes how to view and refresh target data.
      View Event Session Data

      Describes how to use Extended Events tools to create and manage your SQL Server Extended Events sessions.
      Extended Events Tools

      Describes how to alter an Extended Events session.
      Alter an Extended Events Session

      Describes how to copy or export target data.
      Copy or Export Target Data

      Describes how to modify your trace results view to customize how you want to analyze your data.
      Modify the Trace Results View

      Describes how to get information about the fields associated with the events.
      Get the Fields for All Events

      Describes how to find out what events are available in the registered packages.
      View the Events for Registered Packages

      Describes how to determine what Extended Events targets are available in the registered packages.
      View the Extended Events Targets for Registered Packages

      Describes how to view the Extended Events events and actions that are equivalent to each SQL Trace event and its associated columns.
      View the Extended Events Equivalents to SQL Trace Event Classes

      Describes how to find the parameters you can set when you use the ADD TARGET argument in CREATE EVENT SESSION or ALTER EVENT SESSION.
      Get the Configurable Parameters for the ADD TARGET Argument

      Describes how to convert an existing SQL Trace script to an Extended Events session.
      Convert an Existing SQL Trace Script to an Extended Events Session

      Describes how to determine which queries are holding the lock, the plan of the query, and the Transact-SQL stack at the time the lock was taken.
      Determine Which Queries Are Holding Locks

      Describes how to identify the source of locks that are hindering database performance.
      Find the Objects That Have the Most Locks Taken on Them

      Describes how to use Extended Events with Event Tracing for Windows to monitor system activity.
      Monitor System Activity Using Extended Events

      E para estes Catalog Views:

      sys.server_event_sessions (Transact-SQL)
      sys.server_event_session_fields (Transact-SQL)
      sys.server_event_session_actions (Transact-SQL)
      sys.server_event_session_targets (Transact-SQL)
      sys.server_event_session_events (Transact-SQL)

      Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s