Dica do Mês – Default Trace To Audit Events – Backup and Restore

Hello everybody!!!

Tudo bem? Final de mês chegando e o que isso significa para você?

Se fosse aquele apresentador do programa do fim de noite do domingo poderia dizer, NADA, mas no meu blog todo final de mês é sempre muito importante e aguardado, é a hora de mais um post dedicado a sessão dica do mês ser publicado, alias se você ainda não conheço ou acessou os dois primeiros posts desta nova sessão não perca seu tempo, utilize um dos links abaixo e conheça mais sobre esta na área do meu blog dedicada especialmente em apresentar como um recurso ou script pode se tornar uma dica extremamente útil:

Vamos em frente, no post de hoje vou destacar um velho e conhecido assunto existente no Microsoft SQL Server e bastante difundido a partir da versão 2005, denominado Default Trace File (Arquivo de rastreamento/monitoramento padrão) em conjunto com os Audit Events (Eventos de Auditoria) introduzidos a partir da versão 2008, ambos os recursos combinados se tornando uma fantástica ferramenta para consulta e monitoramento das atividades, tarefas e processos em execução no Microsoft SQL Server.

Como de costume, vou apresentar um pouco sobre estes recursos de forma breve e resumida, tentando ilustrar sua importância:

  • Default Trace File: Surguiu da necessidade que os profissionais de banco de dados em suas atividades monitoramento tinham em colocar dados sobre o que estava sendo executado em seu servidor ou instância SQL Server de uma maneira mais “aberta”, ou seja, diferente do que acontecia na versão 2000 onde o monitoramento era realizado de uma forma conhecida como “caixa preta”, sem possibilitar algum tipoo de interação.A partir da versão 2005 este recurso foi aprimorado, se tornando uma grande ferramenta na coleta de dados sobre o comportamento do SQL Server. Com esta evolução muitas respostas para problemas de performance, deadlock e troubleshotting começaram a ser respondidas e entendidas de forma mais rápida, fácil e dinâmica. Em diversos momentos o Default Trace File, foi considerado uma ferramenta valiosa e moderna para os profissionais de Banco de DAdos – DBA.

    Oferece uma riqueza de informações, enquanto minimamente, impactando o sistema. O Default Trace File é um recurso amplamente divulgado do SQL Server 2005, mas com o passar do tempo ganhou fama e evolução nas demais versões do produto, por oferece aos administradores a capacidade de obter informações detalhadas sobre:

    • Auditoria de eventos;
    • Eventos de banco de dados;
    • Eventos de erros;
    • Eventos de texto completo;
    • Criação do objeto;
    • Exclusão de objeto; e eventos de alteração do objeto.

 

  • Audit Events: Introduzido a partir da versão 2008, o Audit Events tornou-se uma verdadeira solução de auditoria dentro do Microsoft SQL Server, considerada por muitos um complemento aos recursos e funcionalidades desempenhadas pelo Default Trace File. Composto por um longo conjunto de eventos que a cada nova versão do SQL Server é incrementado com outros os eventos de auditoria possibilitam coletar e catalogar de maneira mais detalhada e repleta de dados todo comportamento de um servidor, banco de dados e objetos existentes em um servidor ou instância SQL Server.Outro fator importante quando falamos dos eventos de auditoria esta relacionado com os pilares de segurança do SQL Server, possibilitando todo controle e monitoramento de acesso a objetos e dados direcionados ao SQL Server, possibilitando detectar possíveis tentativas de acesso não autorizado ou acessões maliciosas por partes de usuários com acesso legítimo.

Após este breve resumo sobre estes dois recursos, você pode estar se perguntando como ou quando podemos fazer uso deste tipo de funcionalidade? A resposta é bem simples, de diversas forma, dentre elas apresento algumas:

  • Captura de eventos e ocorrência de eventos;
  • Captura da lista e detalhes de eventos;
  • Informações sobre crescimento de um banco de dados;
  • Informações sobre erros e alertas disparados pelo SQL Server;
  • Informações sobre alertadas de ordenação de dados;
  • Quando um procedimento de Shrink foi realizado em um banco de dados ou arquivo de log;
  • Quando um determinado comando DBCC foi executado;
  • Quando um Backup de Banco de Dados ou Restore de Banco de Dados foi realizado; e
  • Pesquisa de informações sobre auto estatísticas ou eventos de estatísticas.

Enfim sobre muita coisa relacionada ao SQL Server é possível fazer uso de um Default Trace File em conjunto com o Audit Events, desta maneira na dica de hoje escolhi uma das maneiras mais comuns porém não menos importante para obter informações sobre os eventos relacionados a execução de Backup Database e Restore Database através do dois exemplos apresentados a seguir:

 


 

— Exemplo 1 – Obtendo informações sobre a ocorrência de Backup Database —

Declare @path NVARCHAR(260)

Select @path=path From sys.traces Where is_default = 1

 

SELECT DatabaseName, TextData,

             Duration, StartTime,

             EndTime, SPID,

             ApplicationName, LoginName

FROM sys.fn_trace_gettable(@path, DEFAULT)

WHERE EventClass IN (115)

and EventSubClass=1

ORDER BY StartTime DESC

Go

 

 

— Exemplo 2 – Obtendo informações sobre a ocorrência de Restore Database —

Declare @path NVARCHAR(260)

Select @path=path From sys.traces Where is_default = 1

 

SELECT DatabaseName, TextData,

             Duration, StartTime,

             EndTime, SPID,

             ApplicationName, LoginName

FROM sys.fn_trace_gettable(@path, DEFAULT)

WHERE EventClass IN (115)

and EventSubClass=2

ORDER BY StartTime DESC

 

Espero que você tenha observado que ambos os códigos de exemplo são bem similares, o que determina qual conjunto de informações será apresentada de acordo com o evento é a coluna EventSubClass, onde:

  • EventSubClass = 1 — Representa as informações sobre a sub-classe de eventos relacionada a ocorrência de Backup;
  • EventSubClass = 2 — Representa as informações sobre a sub-classe de eventos relacionada a ocorrência de Restore; e
  • EventSubClass = 3 — Representa as informações sobre a sub-classe de eventos relacionada a ocorrência de Backup Log.

Caso você queira saber mais sobre os eventos de auditoria relacionadas a Backup/Restore acesse:
https://msdn.microsoft.com/en-us/library/ms175015.aspx ou https://msdn.microsoft.com/en-us/library/ms175481.aspx para obter mais sobre a classe de eventos existente no SQL Server.

sys.fn_trace_gettable e sys.traces existentes nas atuais versões do SQL Server 2012 e 2014, segundo a documentação oficial da Microsoft ambas catalog views serão removidas nas futuras versões do produto.

 


 

É isso ai galera, chegamaos ao final de mais uma dica de mês. Com certeza uma dica bastante diferente que forma como o SQL Server é composto por diversos recursos aparentemente independentes e isolados mas que podem ser utilizados em conjunto formando uma grande ferramenta de trabalho.

Espero que você tenha gostado, nos encontramos no final do mês de Abril com mais uma dica do mês.

Um grande abraço, obrigado por sua visita.

Até mais.

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 pela Uninove - Campus São Roque. 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. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - 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, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. 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. 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.

Um comentário em “Dica do Mês – Default Trace To Audit Events – Backup and Restore”

Deixe uma resposta

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