Salvo pelo Default Trace!

Olá pessoal,

Hoje vou falar de uma caso real, que aconteceu comigo. Um amigo entrou em contato comigo e disse que queria saber qual usuário excluiu algumas tabelas do banco de dados, só que no servidor não estava habilitado a auditoria, nem por triggers DDL e nem pela feature de Auditoria do SQL Server 2008.

E agora, como saber qual login executou o comando de DROP TABLE no banco de dados de produção?

A situação foi a seguinte: Um pessoa (mal intencionada) que tinha acesso ao banco de dados da empresa, insatisfeita por algum motivo que não vem ao caso agora, excluiu algumas tabelas do banco de dados com o objetivo de prejudicar a empresa e seus dados.

Por sorte, o problema foi resolvido parcialmente pois outro funcionário responsável pelo banco de dados tinha backups atualizados e conseguiu recuperar os dados, mas como provar para seus superiores quem causou essa indisponibilidade do serviço e convece-los de que o que ocorreu não foi uma falha da equipe de banco de dados?

Quando se tem uma política de auditoria habilitada e bem planejada fica fácil, mas quando nenhum desses recursos estão disponíveis, temos uma última carta na manga que é o Default Trace!.

Default Trace, como o nome já diz é um trace padrão que é criado após a instalação do SQL Server e fica habilitado capturando apenas alguns pequenos conjuntos de eventos, a intenção é capturar eventos que pode ser causas para problemas de estabilidade do sistema, etc. O eventos capturados são alterações ao banco de dados, erros, e para nossa sorte este captura  as operações de CREATE, ALTER e DROP em objetos, dentre outros tipos de eventos. O arquivo de saída do Default Trace, fica localizado dentro do diretório de instalação do SQL Server dentro do diretório Log. O arquivo pode atingir até 20 MB e um máximo de 5 arquivos.

Para simular o cenário proposto, vamos criar um banco de dados e uma tabela e logo após criaremos um login e usuário para excluir a tabela de exemplo.

CREATE DATABASE Producao

USE Producao

CREATE TABLE VENDAS
(
    ID INT IDENTITY(1,1),
    PRODUTO VARCHAR(30)
)

INSERT INTO VENDAS VALUES(‘A’)
INSERT INTO VENDAS VALUES(‘B’)
INSERT INTO VENDAS VALUES(‘C’)

Criando o login e usuário de exemplo.

USE [Producao]
GO
–Criando o login e usuario de teste.
CREATE LOGIN [Joao] WITH PASSWORD=‘password’,
DEFAULT_DATABASE = [Producao],CHECK_POLICY=OFF
GO
CREATE USER [Joao] FOR LOGIN [Joao] WITH DEFAULT_SCHEMA=[dbo]
GO
–Dando permissao para o usuario na tabela VENDAS.
GRANT CONTROL ON [dbo].[VENDAS] TO [Joao]
GO

Agora, que criamos o usuário vamos fazer logon utilizando o usuário e excluir a tabela. Após excluir a tabela vamos utilizar a função fn_trace_gettable para armazenar os dados do trace dentro de uma tabela temporária e analisar os dados.

USE Producao

–Excluindo a tabela VENDAS com o usuario Joao
DROP TABLE VENDAS

–Retornando os dados do trace.
SELECT * INTO temp_trc
FROM fn_trace_gettable (‘D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc’,default);

Agora com os resultados do trace dentro da tabela temporária temp_trc vamos selecionar os dados relativos ao usuário Joao, para recuperar as operações realizadas por este.

SELECT   DB_NAME(DatabaseID) AS [Database]
        ,HostName
        ,ApplicationName
        ,LoginName
        ,StartTime
        ,ObjectID
        ,EventClass
        ,ObjectName
        ,DatabaseName
FROM temp_trc
WHERE LoginName = ‘JOAO’

Podemos ver o resultado da query acima, e observe o EventClass 47 que significa Object:Deleted conforme a tabela relativa aos eventos que pode ser encontrada no BOL, se estiver abrindo o arquivo de trace utilizando o SQL Server Profiler é mostrado como o EventClass Object:Deleted.

image

Tabela de eventos do BOL.

47 Object:Deleted Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

Desta forma podemos ver que o Default Trace nos foi útil, capturando o evento e provando que o usuário Joao excluiu a tabela VENDAS, com detalhes de data e hora de execução da operação, aplicação utilizada, banco de dados, etc.

É bom lembrar que o Default Trace é limitado para apenas os eventos pré-configurados, e que este não é a solução ideal para auditoria, pois é criado apenas 5 arquivos e pode serem sobrescritos a cada restart do serviço SQL Server. Para uma solução eficiente e completa de auditoria, pode ser utilizar a feature presente no SQL Server 2008 de qual possui diversos recursos.

Espero que seja útil, abraços!

Monitorando Database Mirror com System Monitor

Olá pessoal,

Neste artigo vou mostrar alguns contadores do System Monitor relacionado ao Database Mirroring que acredito ser útil em situações onde é preciso monitorar a atividade de um banco de dados espelhado, verificar a taxa de transferencia dos registros de log, gerenciar a fila de registros em falha do banco de dados mirror ou principal, e com essas informações ter uma estimativa do tempo necessário para a sincronização dos databases, etc.

Neste artigo não irei abortar o processo de implementação e configuração do Database Mirror pois já tenho escrito um artigo com esse objetivo aqui. Como quase todas as features do SQL Server possui contadores de desempenho para monitorar suas atividades, com o Database Mirror não é diferente, vou explicar alguns dos principais contadores para Database Mirror e mostrar situações onde cada um deve ser verificado durante a atividade do sistema.

Para iniciarmos vamos entender como funciona os principais contadores:

Log Bytes Sent/sec: Este contador medi o número de bytes de log enviados ao mirror por segundo, é a taxa de qual o principal transfere registros de logs de transação para o banco de dados mirror.

Log Send Queue KB: Este contador medi a fila de dados de log de transação que não tem sido enviado ainda ao banco de dados mirror.

Transaction Delay: Este contador medi o total de delay em milisegundos que o banco de dados principal espera pelo “aviso” de commit a partir do banco de dados espelho.

Transactions/sec: Este contador medi o número de transações por segundos em um banco de dados.

No Banco de dados Mirror, vamos verificar os contadores abaixo:

Log Bytes Received/sec: Este contador medi o número de bytes de log recebidos pelo banco de dados mirror através do banco de dados principal.

Redo Queue KB: Este contador medi a fila de registros que não foram aplicados ainda ao banco de dados espelho.

Redo Bytes/sec: Este contador medi o número de bytes de log de transação estar sendo aplicado ao banco de dados espelho por segundo.

Irei detalhar as descrições dos contadores no decorrer do artigo. Para agente iniciar a monitoração, vamos abordar os três primeiros contadores descritos acima: Log Bytes Sent/sec, Log Send Queue KB, Transaction Delay. Para isso abra o System Monitor em sua instancia do banco de dados principal e adicione os contadores citados que estão localizados no Object Management SQLServer:Database Mirroring selecione os contadores e a instancia relacionada ao seu banco de dados espelhado, conforme a imagem abaixo.

image

Como disse acima para quem não tenha ainda configurado o Database Mirror neste artigo mostro passo a passo como configura-lo e suas devidos conceitos. No ambiente da simulação abaixo, estou utilizando o cenário de High Protection, ou seja, Alta Proteção da transferencia de dados entre o banco de dados principal e espelho, conforme a transação é primeiro confirmada “commit” no banco de dados espelho e após isso confirmada no banco de dados principal. Para monitorar as atividades no banco de dados espelho faça o mesmo procedimento acima selecionando os contadores Log Bytes Received/sec, Redo Queue KB, Redo Bytes/sec.

No meu exemplo estou utilizando um banco de dados chamado Performance e vou executar a seguinte instrução para simular uma carga de trabalho.

USE Performance

INSERT INTO TB1 VALUES(‘TESTE’)
GO 20000

Após executar a instrução acima, alterne para o System Monitor no banco de dados principal para iniciar a monitoração conforme a imagem abaixo. 

image

Neste momento alterne para o System Monitor no banco de dados mirror para verificar a monitoração conforme a imagem abaixo. (Poderia ter colocado todos os contadores do banco de dados principal e mirror monitorados pelo System Monitor remotamente, mas atrapalhou um pouco a visualização, por isso preferi executar o System Monitor em cada servidor separadamente.)

image

O gráfico mostrado no System Monitor no banco de dados principal medi os contadores Log Bytes Send/s de vermelho, Log Send Queue KB de verde e Transaction Delay na cor azul. Como descrevi acima o contador Log Bytes Send/s é a taxa de transferencia de bytes de log de transação para o banco de dados espelho e em nosso exemplo esse contador estar indicando um alto valor na transferencia de qual sua “linha” vermelha estar permanecendo na parte superior do gráfico, permanecendo até o final da transação. Podemos verificar que a quantidade de bytes de log enviados por segundo é alta. Já o contador Log Send Queue KB permaneceu como zero durante toda a transação pois como a taxa de envio de bytes de log por segundo (Log Bytes Send/s) e a taxa de recebimento de bytes de log (Log Bytes Received/sec) no banco de dados mirror estava estável o banco de dados principal não precisou armazenar registros dentro da fila de envio de log no log de transação, ou seja, o banco de dados principal estava conseguindo enviar os logs de transação em tempo satisfátório para o banco de dados espelho.

O contador Transaction Delay nos indica o total em milisegundos que o banco de dados principal espera pelo aviso de commit no banco de dados espelho. Em nosso gráfico esse contador permaneceu na parte inferior, indicando um baixo valor de espera. Esse contador é o principal contador a ser monitorado em um ambiente de High Protection e High Availability de qual utiliza Safety Full um alto valor constante pode impactar problemas de performance nas transações. Como disse que este contador indicava um total em milisegundos de delay nas transações, para verificar qual o delay estimado em cada transação apenas divida o contador Transaction Delay pelo Transactions/sec de qual irá indicar essa média.

No banco de dados mirror vamos analisar o contador Log Bytes Received/sec de qual indica a quantidade de bytes de log recebidos a partir do banco de dados principal. Em nosso gráfico este contador também é indicado pela cor vermelha e teve um alto valor permanecendo numa escala próximo ao 70% indicando que a taxa de recebimento dos bytes de log de transação estava alta. O contador Redo Bytes/sec indicado pela cor verde, variou por quase as mesmas medidas do contador Log Bytes Received/sec. Este contador medi o número de bytes de log de transação aplicados ao banco de dados espelho por segundo, em nosso caso podemos observar que a medida que os bytes de log foram recebidos estes já eram aplicados ao banco de dados espelho, quando o tráfego de envio de log é muito grande e o banco de dados espelho não consegui aplicar os registros em tempo satisfatório é gerada uma fila armazenado os registros recebidos pelo banco de dados principal mas que ainda nao tem sido aplicado ao banco de dados espelho. Essa fila é medida pelo contador Redo Queue KB indicada pela cor azul em nosso gráfico. Reparem que este contador permaneceu zero, ou seja, não foi preciso armazenar os registros na fila, pois estes já foram aplicados na medida em que eram recebidos.

Agora que já vimos alguns dos conceitos mais importantes, vamos simular cenários para agente verificar o comportamento dos contadores. Em nosso ambiente de testes vamos alterar o modo de segurançã da transação, vamos alterar para Safety OFF ,ou seja, o banco de dados principal nao irá esperar o “aviso” de commit a partir do banco de dados espelho para confirmar a transação, com isso vamos monitorar o comportamento do contador Transaction Delay. Para esse exemplo, adicione o contador Transactions/sec de qual estar no Object Management Databases ao System Monitor junto com os demais contadores e utilize a instrução abaixo para habilitar o modo de segurança OFF e execute a transação novamente.

ALTER DATABASE [Performance] SET SAFETY OFF

USE Performance

INSERT INTO TB1 VALUES(‘TESTE’)
GO 20000

image

No Gráfico acima monitado a partir do System Monitor do banco de dados principal selecionei o contador Transaction Delay para destacar que sua atividade foi zero. Com isso percebemos que o banco de dados principal não estar tendo delay na transação, não estar esperando o “aviso” do banco de dados espelho para confirmar a transação, a transação é confirmada e após enviada ao banco de dados espelho. Como já esperado o contador Log Bytes Send/s permaneceu na parte superior e o contador Transactions/sec também mediu as transações por segundo enviadas ao banco de dados espelho. Para melhor visualização alterei a escala do contador Transactions/sec para não sobrepor a linha do contador Log Bytes Send/s.

Agora iremos simular um cenário onde o banco de dados principal é desconectado do banco de dados espelho para verificar a fila de envio de log medida pelo contador Log Send Queue KB, para isso vou desconectar o adaptador de rede do servidor espelho simulando uma indisponibilidade qualquer. Se você é um daqueles DBA´s que só acredita vendo ou melhor “lendo” vamos ler o conteúdo do errorlog do SQL Server para verificar que o banco de dados espelho não estar respondendo, assim deixando o banco de dados principal desconectado do banco de dados espelho.

2010-07-07 11:13:05.32 spid23s     Error: 1479, Severity: 16, State: 1.
2010-07-07 11:13:05.32 spid23s     The mirroring connection to "TCP://SERVER2008:1450" has timed out for database "Performance" after 10 seconds without a response.  Check the service and network connections.
2010-07-07 11:13:05.32 spid23s     Database mirroring is inactive for database ‘Performance’. This is an informational message only. No user action is required.

Com o banco de dados principal isolado, emita novamente a transação. (Neste exemplo irei inserir 200 mil linhas para a fila crescer bastante para visualizarmos melhor)

USE Performance

INSERT INTO TB1 VALUES(‘TESTE’)
GO 200000

image

Podemos visualizar através do gráfico acima que o contador Log Send Queue KB indicado pela cor verde foi crescendo gradativamente de acordo com as transações vao preenchendo a fila até permanecer na parte superior do gráfico, o interessante é que este contador permanece estável durante e após a transação indicando que as transações estão na fila aguardando para serem enviadas ao servidor espelho. Neste momento se verificarmos a wait do log de transação indicando qual o motivo pelo qual este não pode ser sobrescrito podemos visualizar como DATABASE_MIRRORING de qual indica que existem transções ativas no log de qual são as transações que ainda não foram enviadas ao banco de dados espelho por isso o log de transação não pode ser truncado. Para visualizar emita a instrução abaixo:

SELECT name,log_reuse_wait_desc
FROM sys.databases
WHERE name = ‘Performance’

name              log_reuse_wait_desc
—————– —————————-
Performance   DATABASE_MIRRORING

(1 row(s) affected)

Caso quando emita a instrução acima, apareça na coluna log_reuse_wait_desc o valor BACKUP_LOG faça um backup do log de transação para retirar a porção inativa do log de transação causado por outras transações e emita novamete a transação para verificar o valor desta coluna.

Voltando ao gráfico podemos observar que o contador Transactions/sec permaneceu na parte superior do gráfico medindo as transações por segundo no banco de dados principal e logo mais este contador teve uma queda brusca pois a transação foi concluída. Reparem que os contadores Log Bytes Send/s e Transaction Delay permanecem como zero, pois não estar havendo transferencia de bytes de log para o banco de dados espelho.

Agora que já visualizamos o aumento da fila de envio de log vamos colocar novamente o banco de dados espelho no ar e monitorar os contadores no banco de dados principal e espelho. Após restabelecer a conexão com o banco de dados espelho verifique o errorlog do SQL Server e monitore os contadores em ambos os servidores conforme as imagens abaixo.

2010-07-07 15:44:47.18 spid23s Database mirroring is active with database ‘Performance’ as the principal copy. This is an informational message only. No user action is required.

image

No gráfico do banco de dados principal podemos ver que quando o banco de dados espelho foi colocado online a fila de envio de log medida pelo contador  Log Send Queue KB caiu gradativamente, os registros foram transferidos para o banco de dados espelho, como podemos observar o comportamente do contador Log Send Sent/sec que aumentou repentinamente assim que conexão foi restabelecida. Verifique as imagens abaixo sobre o gráfico do banco de dados espelho:

image

image

No primeiro gráfico podemos observar o comportamento dos contadores  Log Bytes Received/sec e Redo Bytes/sec que atigiram picos de atividade assim que a conexão com o banco de dados principal foi estabelecida indicando que os bytes de log de transação começaram novamente a ser recebidos e que já estavam começando a serem aplicados ao banco de dados espelho. O mais interessante do gráfico foi que a linha do contador Redo Queue KB começou a subir pelo fato de as transações estarem sendo armazenadas em fila para ser aplicadas ao banco de dados espelho, pois o volume de bytes de log recebidos não era o mesmo que o número de bytes aplicados ao banco de dados espelho, por isso foi preciso criar a fila.

No segundo e último gráfico podemos observar que a fila de redo (Redo Queue KB) subiu como disse acima mas no decorrer do gráfico essa começa a cair, conforme as transações são aplicadas ao banco de dados espelho. Conforme a linha do tempo do gráfico vai passando podemos verificar a atividade de sincronização do banco de dados espelho de forma clara, com todas as fases bem distintas.

Espero que este artigo seja útil para aqueles que precisem monitorar as atividades do Database Mirror, que precisem estimar dados de performance e utilização de recursos. Este artigo se aplica ao SQL Server 2005 e também SQL Server 2008, apesar do SQL Server 2008 possui mais alguns contadores (Este pode ser um tema para outro artigo).

Muito Obrigado.

Abraços.

Primeiros passos com Powershell.

Olá Pessoal,

Neste artigo vou mostrar alguns scripts úteis utilizando o Powershell e SQL Server. Eu não irei falar do poder do Powershell e porque você deva oimageu não utiliza-lo, pois o Laerte já mostrou por "N" formas qual sua utilidade e vantagens, vou deixar ao critério do amigo leitor chegar nessa conclusão. 

Para simular situações do dia a dia pensei em algumas tarefas importantes como por exemplo: Preciso criar um inventário simples de meus servidores, armazenando nome da instancia, processadores, memóra, etc. Podemos criar um script Powershell para se conectar em nossos servidores e listar essas opções, com isso iremos manter essas informações atualizadas de arcordo com a frequencia de execução do script. Para implementar nossa solução proposta acima vamos escrever o seguinte script: 

   1: $ServerList = Get-Content C:TempScriptServers.txt
   2: ForEach ($svr in $ServerList)
   3: { 
   4: $Server = Get-SqlServer -sqlserver $svr -username "usuario" -password "senha"
   5: $Server | Select-Object Name,Edition,Language,LoginMode,PhysicalMemory,Platform,Processors,Product,ProductLevel,VersionString,Collation,ErrorLogPath 
   6: | ConvertTo-Html -As LIST | Out-File C:TempScriptInventary.html -Append
   7: "" | Out-File C:TempScriptInventoryServers.html -Append
   8: }

Este script armazena na variável $ServerList o conteúdo do arquivo C:TempScriptServers.txt de qual contém os nomes dos servidores no padrão SERVERINSTANCE um em cada linha, após isso o script se conecta em cada servidor e lista as opções Name,Edition,Language,PhysicalMemory,Processors … e de "quebra" ainda manda para um arquivo HTML de qual pode ser hospedado em um servidor em sua intranet para uma melhor visualização. Veja a saída do comando abaixo.

image

Podemos perceber através das linhas marcadas os nomes dos servidores, e suas configurações, neste exemplo recuperamos configurações de duas instâncias SQL Server de qual com o mesmo script poderia se conectar em várias instâncias. A próxima situação é Como posso verificar quais databases de usuário em minhas instâncias estão com as opções AutoClose e AutoShrink habilitadas ?.Para esse exemplo vamos continuar com a mesma linha de raciocínio, conectar em nossas instâncias especificadas no arquivo Servers.txt e verificar os status das opções acima. Neste caso vamos fazer um filtro pois queremos apenas os bancos de dados de usuário.Vamos ao script:

   1: $ServerList = Get-Content C:TempScriptServers.txt
   2: ForEach ($svr in $ServerList)
   3: {
   4:  $Server = Get-SqlServer -sqlserver $svr -username "usuario" -password "senha"
   5:  $Databases = $Server.Databases
   6:  $Server.Name
   7:  
   8: ForEach ($db in $Databases)
   9: {
  10:  if (!$db.IsSystemObject) {
  11:  $db | Where-Object {$_.AutoClose -eq $true -or $_.AutoShrink -eq $true } | Select-Object Name,AutoClose,AutoShrink | Format-Table
  12:      }
  13:  }
  14: }

Neste script verificamos se o banco de dados é um banco de dados de sistema, e após filtramos apenas os banco de dados que tenham pelo menos uma das opções como True, ou seja, retorna apenas os databases que tenha a opção AutoClose ou AutoShrink habilitadas.

image

Neste momento voce deve estar pensando: "Legal, mas e se eu quiser alterar esses valores ?  quero que desabilite as duas opções nos bancos de dados.selecionados" Fácil vamos alterar nosso script para desabilitar essas opções.

   1: $ServerList = Get-Content C:TempScriptServers.txt
   2: ForEach ($svr in $ServerList)
   3: {
   4:  $Server = Get-SqlServer -sqlserver $svr -username "usuario" -password "senha"
   5:  $Databases = $Server.Databases
   6:  $Server.Name
   7:  
   8: ForEach ($db in $Databases)
   9:  {
  10:      if (!$db.IsSystemObject){
  11:          if (!$db.IsDatabaseSnapshot){
  12:              $db.AutoClose = $false
  13:              $db.AutoShrink = $false
  14:              $db.Alter()
  15:              }
  16:      $db | Select-Object Name,AutoClose,AutoShrink | Format-Table
  17:  
  18:          }
  19:      }
  20: } 

Notem que em nosso exemplo estamos validando além do banco de dados ser um banco de dados de usuário, se o banco de dados é um Database Snapshot pois sabemos que não é possível altera-lo. Logo após o script seta as opções do tipo Boolean para false, por último o script lista as opções alteradas.

image

Outra situação é "Como posso visualizar quando todas as minhas estatísticas em um determinado ou vários databases de uma ou várias tabelas foram atualizadas?" no script abaixo listamos a data da última atualização de todas as statísticas de todas as tabelas do banco de dados AdventureWorks.

   1: $Server = Get-SqlServer -sqlserver "SERVER2008PRODUCAO" -username "usuario" -password "senha"
   2: Get-SqlDatabase -sqlserver $Server -dbname "AdventureWorks" |Get-SqlTable | Get-SqlStatistic | Select-Object Table,Name,IsAutoCreated,LastUpdated | Format-Table –AutoSize

image

Sim, isso mesmo, para atualizarmos todas essas statísticas apenas adicionamos o cmd-let Update-Statistics como no script abaixo:

   1: $Server = Get-SqlServer -sqlserver "SERVER2008PRODUCAO" -username "usuario" -password "senha"
   2: Get-SqlDatabase -sqlserver $Server -dbname "AdventureWorks" |Get-SqlTable | Get-SqlStatistic | Update-SqlStatistic 

image

O Grande diferencial do Powershell é a possíbilidade de trabalhar em operações multi-servidores, o script acima poderia ser facilmente alterado para executar em uma range de servidores, automatizando algumas tarefas rotineiras de um DBA. Com isso concluo este artigo, espero ter demostrado um pouco da produtividade e flexibilidade oferecida pelo Powershell voltado ao SQL Server.

Abraços, até o próximo!

Referencias: http://laertejuniordba.spaces.live.com/

Curso Administração de banco de dados SQL Server 2008.

Olá Pessoal, 

Hoje trago uma notícia boa, foi confirmado o meu curso sobre Administração de banco de dados SQL Server 2008 na faculdade Unipac Teófilo Otoni, irei dar um curso rápido e introdutório sobre o SQL Server 2008, revisando alguns conceitos sobre bancos de dados e SQL Server.

O Curso será oferecido para as tumas de Sistemas de Informação e Ciencia da Computação nos dias 19 e 20 de Junho, vamos iniciar com uma introdução  teórica sobre o que é o SQL Server 2008, a família de produtos, principais edições e alguns fundamentos sobre arquitetura. Logo após iremos abortar algumas novas funcionalidades do SQL Server 2008 voltadas para a administração do banco dados como Auditoria, Resource Governor, Compressão de dados e Backup, Policy-Based Management e claro Powershell.

Conversando com o Laerte Junior um dos maiores influenciadores e pioneiros de Powershell e SQL Server no Brasil e no mundo, ele me autorizou a mostrar seus scripts e utiliza-los, dos quais serão de grande utilidade para os alunos que terão uma visão do poder do Powershell integrado ao SQL Server, (Claro que irei mostrar e mencionar o autor).

Segue abaixo os  tópicos que serão abordados no curso:      sql_se10

  • Pré-Requisitos e Instalação do Microsoft SQL Server 2008.
    • Preparar o ambiente de sistema operacional pra instalação do produto.
    • Verificar requisitos de software e hardware necessários.
    • Verificar componentes do SQL Server 2008 a serem instalados.
    • Selecionar funcionalidades do SQL Server 2008 a serem instaladas.
    • Especificar diretório para instalação de binários do SQL Server, definir instalação de instância nomeada ou padrão.
    • Definir configurações de segurança e serviços.
    • Especificar modos de autenticação.
  • Configuração de opções da instância.
    • Configuração de memória e processadores.
    • Definição dos diretórios de backup, e arquivos de banco de dados de sistema.
    • Configuração e otimização do banco de dados de sistema tempdb.
    • Configuração da instância para acesso remoto.
    • Definir configuração padrão para arquivos de log e dados.
    • Configurar opções de cursores, paralelismo, locks, etc.
  • Configuração de segurança e auditoria.
    • Criação de Logins para acesso a instância.
    • Entender as Fixed Server Roles e permissões de acesso.
    • Atribuir Logins a Fixed Server Roles.
    • Criar objetos de auditoria no nível de instância e bancos de dados.
    • Configurar auditoria para escrever em logs de aplicação, segurança e arquivos do Windows.
  • Criação e configuração de bancos de dados.
    • Criação do banco de dados, utilizando o SQL Server Management Studio e Transact-SQL.
    • Criação e configuração de Files e Filegroups.
    • Configuração do log de transação e melhores práticas.
    • Configuração de opções automáticas para manutenção do banco de dados e performance.
    • Configuração de opções para verificação de corrupção do banco de dados.
  • SQL Server Integration Services para importar e exportar dados.
    • Entender os conceitos do processo de ETL (Extract Transform Load)
    • Entender os conceitos a  importação a partir de outras fontes de dados como Oracle, Access, Excel,
      etc.
    • Utilizar o SQL Server  Integration Services para  importar massa de dados a partir de um arquivo de
      texto.
    • Criar tabelas, configurar colunas e data types para receber os dados.
    • Configurar agendamentos para pacotes Integration Services
  • Backup e Recover de base de dados.
    • Entender o conceito de Backup e Recover de banco de dados.
    • Entender os tipos de backup e estratégias de recuperação.
    • Criar backup Full, Differential, Log de Transação.
    • Criar backups comprimidos.
    • Entender o que é um Database Snapshot.
    • Criando e restaurando um banco de dados a partir de um Snapshot.
    • Técnicas de Disaster Recover.
    • Restaurar e recuperar bancos de dados em caso de falha.
    • Recuperando bancos de dados corrompidos.
  • Configurar o Resource Governor para gerenciar recursos do servidor.
    • Entender como funciona o Resource Governor.
    • Criar funções de classificação e Workloads Groups.
    • Configurar a alocação de Pool´s utilizados pelos Workloads Groups.
    • Monitorar a utilização de recursos do servidor utilizando o Performance Monitor.
  • Ferramentas para monitoração do SQL Server 2008.
    • Monitorar a atividade do servidor utilizando o Activity Monitor.
    • Monitorar consultas e instruções enviadas ao servidor utilizando o SQL Server Profiler.
    • Utilizar Dynamics Management View´s para consultar metadados de objetos do SQL Server.
  • Gerenciamento baseado em políticas e Compressão de dados.
    • Entender o conceito de Gerenciamento baseado em políticas no SQL Server 2008.
    • Criar políticas e especificar objetos alvos.
    • Entender o conceito de compressão de dados.
    • Utilizar a compressão de dados a nível de página.
    • Utilizar a compressão de dados a nível de linhas.
  • Powershell e SQL Server 2008
    • Como funciona a linguagem de script Powershell e sua integração com o SQL Server.
    • Escrevendo scripts para automatizar tarefas no SQL Server.
    • Visualizando  alguns  exemplos  da  usabilidade  da  linguagem  Powershell  integrada  ao  SQL Server.

É isso ai pessoal, estou ancioso por esse dia e acredito que será muito bom aprender e compartilhar meu humilde conhecimento com os amigos, espero que gostem, até lá 🙂

Usando DMV´s para consultar metadados de uma tabela

Olá Pessoal

Hoje vou falar de um recurso legal que é utilizar as DMV´s para consultar metadados de uma tabela. As vezes criamos uma tabela ou mesmo quando esta já estar criada e queremos saber algumas informações vamos dizer assim: “internas” sobre ela como:

  1. Quantas páginas de dados nossa tabela ocupa?
  2. Qual Data File e filegroup nossa tabela reside?
  3. Quantas partições nossa tabela utiliza?
  4. Quais Unidades de Alocação são utilizadas por nossa tabela?
  5. Quais colunas e seus tipos de dados, se a coluna é identity ou não, qual collation para cada coluna?

Para podermos responder essas perguntas precisamos entender ao menos o básico da estrutura de armazenamento de uma tabela no SQL Server. Para começar vamos analisar a imagem abaixo de qual inlustra este processo.

estrutura table

Considerando a imagem acima podemos observar que uma tabela ou índex no SQL Server pode conter uma ou várias partições com um limite de 1000 partições, com isso quando criamos uma tabela que não seja particionada esta irá ser amazenada em uma única partição. Caso criamos uma tabela particionada para cada partição esta será criada a mesma estrutura de armazenamento como mostrado na imagem acima. O Termo utilizado pelo SQL Server 2005 para representar uma tabela ou índex em uma única partição é o Hobt que significa Heap ou B-Tree.

Cada partição pode conter até tres tipos de linhas de quais são armazenadas em seus próprios conjuntos de página, esses conjuntos de páginas para uma determinada partição são chamados unidades de alocação. As tres unidades de alocação exibidas acima são: IN_ROW_DATA, LOB_DATA e ROW_OVERFLOW_DATA. Cada unidade de alocação armazena linhas de acordo com seus tamanhos, ou seja, dentro da unidade de alocação IN_ROW_DATA são armazenadas as linhas de dados e index para valores comuns que não ultrapassem o limite de 8K, dentro da unidade de alocação LOB_DATA serão armazenados dados do tipo LOB (Large Objects) exemplo são dados do tipo TEXT e na unidade de alocação ROW_OVERFLOW_DATA serão armazenados dados que excedem os 8K como dados do tipo VARCHAR que ultrapassem o limite.

Toda tabela ou índex contém uma entrada na DMV sys.objects de qual armazena informações sobre a tabela, como tipo do objeto que no caso é tabela de usuário ou de sistema, data de criação, data de modificação, etc. A DMV sys.objects se relaciona com a DMV sys.indexes através da coluna Object_ID de qual armazena informações sobre os indexes no objeto. Para uma Heap a informação retornada da DMV sys.indexes na coluna Type_desc é HEAP, se for criado um índex Clustered a coluna Type_Desc retorna CLUSTERED para o objeto informado, pois quando se indexa uma tabela com um índex clustered os dados da tabela são parte do índex.

A DMV sys.indexes se relaciona com a DMV sys.partitions no modelo 1 para N até o limite de 1000 partições, através da coluna Object_ID, com isso podemos visualizar as partições para uma determinada tabela ou index. A DMV sys.partitions se relaciona com a DMV sys.allocation_units de qual lista as unidades de alocação de uma determinada partição.

Agora que entendemos um pouco da estrutura de armazenamento de uma tabela ou index e os relacionamentos das DMV´s responsáveis, vamos criar nosso ambiente de testes para responder nossas perguntas enumeradas acima. Para criar nosso ambiente vamos criar um banco de dados de teste chamado Storage_Engine e criar nossa tabela chamada TB_Storage conforme o script abaixo:

–Criando o banco de dados.
CREATE DATABASE Storage_Engine

–Criando a tabela de teste TB_Storage.
USE Storage_Engine
CREATE TABLE TB_Storage
(
     ID INT IDENTITY(1,1)
    ,NAME CHAR(8000)
)

Criei a tabela TB_Storage com o tipo de dados CHAR(8000) para que a cada linha inserida a tabela ocupe uma página de dados. Com nossa tabela criada vamos consultar a DMV sys.objects e a DMV sys.indexes para verificar algumas informações das quais detalhamos anteriormente. Poderíamos fazer um Inner Join entre as duas DMV´s, mas para visualizar todas as colunas de cada DMV, preferi dividir em dois lotes de consultas.

–Consultando a DMV sys.objects
SELECT *
FROM sys.objects
WHERE Object_id = Object_id(‘TB_Storage’)

–Consultando a DMV sys.indexes
SELECT *
FROM sys.indexes
WHERE Object_id = Object_id(‘TB_Storage’)

result1 

 

 

Com os dados retornados a partir das consulta na DMV sys.objects podemos verificar o nome da tabela, o object_id do objeto, descrição da tabela como User_Table, data de criação, modificação, etc. Com os dados retornados pela DMV sys.indexes podemos verificar algumas informações sobre index como Index_ID que no caso é 0 pois nao contém nenhum index em nossa tabela, o tipo de descrição é HEAP como discutimos anteriormente.

Agora que já listamos informações sobre nossa tabela, vamos começar a tentar responder as perguntas no inicio do artigo. Para responder a primeira pergunta vamos utilizar a query abaixo de qual lista as páginas de dados utilizadas por nossa tabela.

–Inserindo alguns valores para o SQL Server alocar páginas para a tabela
INSERT INTO TB_STORAGE VALUES(‘A’)
INSERT INTO TB_STORAGE VALUES(‘B’)
INSERT INTO TB_STORAGE VALUES(‘C’)

–Listando as páginas de dados usadas e reservadas para a tabela.
SELECT au.*
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE p.object_id = object_id(‘TB_Storage’)

result2 

Através da saída acima podemos observar as colunas total_pages, used_pages, data_pages na coluna Total_pages retorna o total de páginas alocadas para a tabela incluíndo páginas IAM que são para controle do SQL Server, como em nosso exemplo inserimos 3 valores foram criadas 3 páginas de dados. Na coluna Used_Pages como o nome já diz, é retornado todas as páginas de dados usadas pela tabela incluindo a página IAM, é diferente da coluna Total_pages de qual lista as páginas alocadas, que não necessáriamente estão usadas pela tabela, supondo que inserimos mais 6 linhas, nossa tabela teria 9 páginas de dados com isso o SQL Server iria alocar um extend uniforme para a tabela, com isso a coluna Total_pages retornaria 17 páginas (16 páginas de dados alocadas + 1 página IAM) e a coluna Used_pages retornariam 10 páginas (9 páginas de dados + 1 página IAM). Na coluna Data_pages é retornado somente as páginas utilizadas como páginas de dados para a tabela, em nosso exemplo 3 páginas de dados.

Reparem que com essa query respondemos duas perguntas, a pergunta 1 a 4. Como em nosso exemplo somente temos dados comuns, estes são apenas armazenados na unidade de alocação IN_ROW_DATA para exemplificar isto, vamos alterar a estrutura da tabela adicionando duas novas colunas uma do tipo varchar(8000) e outra do tipo de dados text, vamos verificar o que acontece.

–Criando a coluna do tipo varchar(8000)
ALTER TABLE TB_STORAGE
ADD EMAIL VARCHAR(8000)

–Criando a coluna do tipo text
ALTER TABLE TB_STORAGE
ADD ENDERECO TEXT

–Listando as unidades de alocações e suas respectivas páginas para a tabela.
SELECT au.*
FROM sys.allocation_units au
INNER JOIN sys.partitions p
ON au.container_id = p.partition_id
WHERE p.object_id = object_id(‘TB_Storage’)


result3 

O SQL Server criou mais 2 unidades de alocação para a tabela. Observem que com a mesma query respondemos as perguntas 1 e 4. Foi listado as tres unidades de alocação para nossa tabela e suas páginas de dados, como não inserimos dados nas colunas criadas, as unidades de alocação não armazenam nenhuma página de dados.

Para respondermos as pergunta 2 vamos utilizar o comando DBCC IND de qual retorna diversais informações inclusive o ID do arquivo físico de qual nossa tabela reside. Essa informação é retornada através da coluna PageFID que significa Index File ID, para listar o nome lógico e o filegroup associado ao arquivo utilizamos a query abaixo de qual relaciona as DMV´s sys.sysfiles e sys.filegroups.

–Listando o ID do Data File da tabela TB_Storage
DBCC IND(Storage_Engine,TB_Storage,-1)

–Listando o nome lógico e físico do arquivo retornado.
SELECT   f.name
             ,f.filename
             ,fg.name as ‘Filegroup Name’
             ,fg.type_desc
FROM sys.sysfiles f
INNER JOIN sys.filegroups fg
ON f.fileid = fg.data_space_id
WHERE f.fileid = 1

result4

Além da coluna PageFID a saída do comando DBCC IND nos retorna informações úteis sobre a tabela como o ID da Partição, Index ID, Tipo de Página (1= Página de dados, 10 = Página IAM) entre outras informações. Através da coluna Partition ID podemos responder a pergunta 3 pois, a saída do comando DBCC IND já nos lista as partições de nossa tabela, mas para ficar mais claro essas informações vamos utilizar a DMV sys.partitions.

–Listando partições da tabela TB_Storage
SELECT *
FROM sys.partitions
WHERE object_id = object_id(‘TB_Storage’)

result5 

Podemos observar que nossa tabela tem apenas uma partição, o número da partição é 1 e existem 3 linhas de dados nessa partição. Vamos agora responder a nossa quinta pergunta listando as colunas e tipos de dados em nossa tabela, informações sobre collation e propriedade identity. Para pegarmos esses valores devemos relacionar as DMV´s sys.columns e sys.types de quais armazenam as colunas para cada tabela e os tipos de dados de sistema e tipos de dados definidos por usuário. Para visualizarmos os nomes dos tipos de dados para cada coluna precisamos relacionar as DMV´s pois na DMV sys.columns é exibido somente o código do tipo de dados, por isso pegamos esse nome a partir da DMV sys.types conforme o script abaixo.

SELECT    C.object_id
              ,C.name
              ,C.column_id
              ,D.name as ‘Data Type’
              ,C.max_length
              ,C.collation_name
              ,C.is_nullable
              ,C.is_identity
              ,C.is_computed
FROM sys.columns C
INNER JOIN sys.types D
ON C.system_type_id = D.system_type_id
WHERE C.Object_id = Object_id(‘TB_Storage’)
ORDER BY C.column_id

result6

Com essa saída de dados podemos verificar qual a ordem das colunas através da coluna column_id, o tipo de dados, tamanho máximo, nome da collation para cada coluna, se a coluna aceita valores nulos e se é uma coluna computada.

Algumas DMV´s utilizadas para responder algumas perguntas também poderiam ser utilizadas para responder outras ou mais de duas questões, porém utilizei DMV´s diferentes para mostrar de forma clara cada uma e suas informações retornadas. Na verdade esse é um dos principais benefícios em utilizar DMV´s pois os dados estão sempre relacionados e as DMV´s nos mostram uma informação completa, precisa e atualizada dos objetos e estruturas internas do SQL Server.

Espero que o artigo seja útil para a comunidade. Até o próximo!

Com apenas o Recovery Model Bulk-Logged sua importação é minimamente registrada em log?

Olá Pessoal,

Hoje vou falar de uma situação que talvez muitas pessoas tenham dúvidas, ou alguma vez passou pela sua cabeça se apenas alterando o Recovery Model do Banco de dados para Bulk Logged fosse suficiente para que uma carga em massa de dados seja minimamente registrada em log.

Na verdade existem alguns pre-requisitos para serem satisfeitos para garantir esse objetivo, tais como a tabela de destino é um HEAP ? ou tem um Índice Clustered ? tem algum índice NonClustered ? a tabela estava vazia durante a importação? etc. Para visualizarmos de maneira mais clara essas situações iremos utilizar a tabela abaixo:

Table Bulk

Observe que existem alguns fatores na estrutura da tabela de destino que devem ser avaliados antes da importação dos dados. De acordo com a tabela acima em uma tabela vazia independente desta tiver índices ou não a importação será sempre minimamente registrada em log e caso esta já contenha dados e tiver índices a importação não será minimamente registrada em log, ou seja, todos os registros serão inseridos no log de transação por completo (Exceto para o índex NonClustered de qual será minimamente registrada em log a tabela de dados e um registro completo no index).

Para demonstrar essas situações vou criar um banco de dados de teste chamado DB_BULK criar uma tabela e inserir alguns registros para povoa-la, após isso vamos exportar os dados para um arquivo de texto simples e recriar o banco de dados e a tabela para fazermos a importação alterando o Recovery Model do banco de dados para Bulk Logged e verificar se o Transaction Log irá ser preenchido ou não após a importação validando se a importação foi minimamente registrada em log ou não. Para referenciar os scripts utilizados irei colocar um número para identificar cada bloco de script.

Script1: Criando o banco de dados DB_Bulk especificando como tamanho inicial do Transaction Log 200MB
–crecimento automatico de 100MB.

CREATE DATABASE DB_Bulk
ON
(
    NAME = ‘Bulk_DATA’,
    FILENAME = ‘C:BulkBulk_DATA.MDF’,
    SIZE = 100MB,
    FILEGROWTH = 100MB
)
LOG ON
(
    NAME = ‘Bulk_LOG’,
    FILENAME = ‘C:BulkBulk_LOG.LDF’,
    SIZE = 200MB,
    FILEGROWTH = 100MB
)
GO

–Backup Full para o banco de dados sair do modo "Pseudo Simple"
BACKUP DATABASE DB_Bulk TO DISK = ‘C:BACKUPDB_Bulk.BAK’ WITH INIT

–Criando a tabela de teste e gerando os dados.
USE DB_Bulk

CREATE TABLE TB_Bulk
(
     COD INT IDENTITY(1,1)
    ,NOME VARCHAR(100)
    ,DATA DATETIME
    ,ENDERECO VARCHAR(100)
)
–Inserindo 500 Mil linhas na tabela
INSERT INTO TB_Bulk VALUES (‘A’,GETDATE() – 10,‘B’)
GO 500000

–Criando os indices Clustered e NonClustered
CREATE CLUSTERED INDEX [IND_CL] ON TB_Bulk(COD ASC)
CREATE INDEX [IND_NC] ON TB_Bulk(NOME ASC)

Com a tabela criada e os dados gerados vamos exportar os dados para um arquivo de texto utilizando o aplicativo BCP, conforme o script abaixo.

Script 2:

C:Program FilesMicrosoft SQL Server90ToolsBinn> BCP DB_Bulk.dbo.TB_Bulk out IMPORT.txt -t, -U sa –P (senha) -c

Arquivo IMPORT.txt:

import file

 

 

 

 

Ok, com nosso arquivo de texto gerado com os dados para a importação vamos excluir o banco de dados e executar novamente o Script1 para recriar o banco de dados e a tabela de teste. Execute todo o Script 1 exceto a instrução INSERT para povoar os dados, iremos utilizar o arquivo IMPORT.txt para importar os dados. Vamos alterar o Recovery Model do banco de dados para Bulk-Logged e verificar o tamanho do Transaction Log e seu espaço livre.

Script 3:

USE MASTER

DROP DATABASE DB_Bulk

Execute o Script 1 Exceto a instrução INSERT.

–Alterando o Recovery Model para Bulk Logged
ALTER DATABASE DB_Bulk SET RECOVERY BULK_LOGGED

–Visualizando o tamanho e espaco livre do Transaction log para o banco de dados DB_Bulk
DBCC SQLPERF (LOGSPACE)

Log Size

Observe que antes da importação o Transaction Log do nosso banco de dados DB_Bulk tem o tamanho de 200 MB (199.9922 exatamente) e neste momento estar com 0.3425427% de seu espaço utilizado. Agora com o Recovery Model Bulk Logged configurado vamos fazer a importação no banco de dados e verificar os resultados. Para realizar a importação utilize novamente o utilitário BCP conforme script abaixo.

Script 4:

C:Program FilesMicrosoft SQL Server90ToolsBinn> BCP DB_Bulk.dbo.TB_Bulk in IMPORT.txt -t, -U sa –P (senha)-c

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 22853  Average : (21878.97 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

log size 2

Com o resultado do comando DBCC SQLPERF (LOGSPACE) podemos verificar que o tamanho atual do nosso Transaction Log é 399 MB, ou seja, o Transaction Log cresceu durante a importação dos dados, como configuramos na opção FILEGROWTH no momento da criação do banco de dados para o crescimento de 100 em 100 MB, o nosso Log cresceu 2 vezes até atingir os 399 MB. Podemos observar também que a coluna Log Space Used(%) estar indicando que o espaço usado dentro do log é 32.1288% de 399.9922MB cerca de 128MB utilizados.

A saída do utilitário BCP nos mostra que foram importadas 500 mil linhas e que o tempo gasto na importação foi de 22853 milisegundos. Com esses resultados podemos concluir que a importação não foi minimamente registrada em log ou seja, os dados foram completamente logados dentro do Transaction Log. Como podemos observar apenas alterar o Recovery Model do banco de dados para Bulk Logged não assegura que a importação será minimamente registrada em log, para garantirmos isso é preciso utilizar a opção TABLOCK do utilitário BCP, com essa opção estamos especificando o modo de bloqueio exclusivo durante a importação. O TABLOCK é necessário para garantir que a importação será minimamente registrada em log juntamente com os outros fatores relacionados a index que descrevi no inicio do artigo. Além de ser necessário, este fornece uma melhor performance durante a importação pois impede que o SQL Server adquira bloqueios a nível de linha para cada instrução durante a importação.

Outro fator importante durante a importação é a opção para especificar o tamanho do lote, o tamanho do lote é quem irá definir a quantidade de linhas inseridas em cada lote, por exemplo poderíamos definir que a importação iria utilizar 5 lotes de 100 mil linhas cada. Qual a desvantagem disso? Se voltarmos no inicio do artigo falei que o SQL Server trabalha diferente durante a importação para tabelas vazias e tabelas que já contenham dados e esteja indexada, com isso se dividirmos em vários lotes nossa importação durante o primeiro lote o SQL Server irá minimamente registrar em log os registros, mas a partir do segundo lote a importação não será mais minimamente registrada em log, porque a tabela não estará mais vazia, assim as instruções serão registradas em log por completo. Vamos ver um exemplo desta situação mais adiante. Por padrão o utilitário BCP trabalha com o conceito de único lote, as mensagems informadas durante a importação são apenas informativas, para especificar o tamanho do lote utilize a opção b e o número de linhas.

Agora que entendemos o porque que nossa importação não foi minimamente registrada em log vamos refazer os testes utilizando a opção TABLOCK do utilitário BCP e verificar os resultados. Para refazermos os testes siga a sequencia de script listada abaixo para recriar o banco de dados e a tabela de teste.

  • Execute a instrução DROP DATABASE DB_Bulk do Script 3.
  • Execute o Script 1 exceto a instrução INSERT.
  • Execute a instrução ALTER DATABASE do Script 3.

Com o banco de dados recriado e configurado o Recovery Model Bulk Logged execute o script abaixo para importar os dados para a tabela TB_Bulk,

Script 5:

C:Program FilesMicrosoft SQL Server90ToolsBinn>BCP DB_Bulk.dbo.TB_Bulk in IMPORT.txt -t, -U sa –P (senha) -c -h TABLOCK -b 500000

Starting copy…
500000 rows sent to SQL Server. Total sent: 500000

500000 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 5688   Average : (87904.36 rows per sec.)

–Visualizando o tamanho e espaco livre do Transaction log após a importação
DBCC SQLPERF (LOGSPACE)

log size3

Agora podemos observar que a saída do comando DBCC SQLPERF (LOGSPACE) é bastante diferente e por si só já fala muita coisa hein ? Neste caso podemos ver que o tamanho atual do Transaction Log não foi alterado, ou seja, durante a importação são foi preciso estender o log como a importação anterior. O nosso Transaction Log permanece com os mesmos 200 MB de qual foi especificado durante a criação do banco de dados e olhando a coluna Log Space Use(%) podemos perceber também uma grande diferença em relação a importação anterior pois agora apenas 10.4655% do tamanho do Transaction Log é utilizado cerca de 20MB, a primeira importação ocupou cerca de 128MB do tamanho do log.

A saída do utilitário BCP também nos mostra informações importantes como o tempo total da importação que em nosso exemplo foi apenas 5688 milisegundos comparados com a ultima importação é uma diferença muito grande. O Fato da primeira importação precisar estender o Transaction Log contribuiu para o aumento no tempo total da importação. Com estes resultados podemos ver que a importação foi minimamente registrada em log.

Para demostrarmos a diferença entre a tabela indexada vazia e a tabela contendo alguns valores no momento da importação vamos executar os seguintes passos. Execute todo o procedimento listado abaixo para recriar a estrutura e inserir um único valor a tabela TB_Block, após isso vamos realizar novamente a importação e verificar os resultados.

  • Execute a instrução DROP DATABASE DB_Bulk do Script 3.

  • Execute o Script 1, inserindo apenas 1 registro na tabela TB_Bulk.

  • Execute a instrução ALTER DATABASE do Script 3.

  • Execute o script 5 para importar os dados.

    Starting copy…
    500000 rows sent to SQL Server. Total sent: 500000

    500000 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 21290  Average : (23485.20 rows per sec.)

    –Visualizando o tamanho e espaco livre do Transaction log após a importação
    DBCC SQLPERF (LOGSPACE)

    log size4

    Podemos observar que durante esta importação obtemos quase o mesmo resultado como a primeira importação,ou seja, a importação não foi minimamente registrada em log. Isso aconteceu pois com a tabela indexada e que já tenha registro a importação não será minimamente registrada em log como descrito acima e demonstrado a partir da tabela no inicio do artigo.

    Com isso podemos concluir que apenas especificando o Recovery Model Bulk Logged durante a importação de uma massa de dados não é suficiente para esta ser minimamente registrada em log, é preciso satisfazer diversos fatores como existencia de index, a tabela estar vazia ou não, utilizar a opção TABLOCK do utilitário BCP, utilizar um lote único durante a importação e outro ponto a observar que não comentei acima é que a tabela de destino não pode estar participando de uma replicação. É extremamente recomendado realizar importação de dados durante uma janela de manutenção para melhor otimização do processo, diminuindo contenção de recursos do servidor, melhor situação para utilizar a opção TABLOCK de qual adquire um lock exclusivo na tabela de destino e talvez até dropar alguns índices antes da importação dos dados para melhor performance e assegurar que a importação será minimamente registrada em log, feita a importação os índices seriam reconstruídos, sei que não é algo tão simples pois alguns índices são utilizados para garantir a integridade dos dados.

    Espero que este artigo seja útil para a comunidade, até o próximo.

  • Coletando e Aplicando Workloads com Replay Trace

    Olá pessoal,

    Hoje vou falar de um recurso que considero muito interessante disponível com o SQL Server Profiler chamado Replay Trace, de qual consiste de reproduzir um trace gerado, ou seja, com o trace coletado a partir de uma instancia SQL Server é possível reproduzi-lo em outro servidor ou no servidor local de forma que este irá executar todas as instruções armazenadas no arquivo ou tabela trace, simulando conexões e autenticação de usuários, o que permite reproduzir a atividade no momento do rastreamento. Isto é basicamente o conceito de Replay Trace!

    Onde isso seria útil ? Podemos pensar na utilidade do Replay Trace em diversas situações das quais irei listar algumas abaixo:

    • Aplicar uma Workload coletada pelo Replay Trace em um novo Hardware com o objetivo de testar qual será seu desempenho.
    • Testar a compatibilidade do sistema antes de aplicar um novo Service Pack ou Hotfix, assim coletaria uma carga de trabalho com o Replay Trace e aplicaria em um servidor de simulação verificando e analisando os resultados antes de colocar em produção.
    • Testar qual será o desempenho e compatibilidade de uma aplicação diante a uma nova versão do produto.

    São inumeras as situações onde o Replay Trace pode ser útil. Para começar a utilizar o Replay Trace inicialmente precisamos definir algumas colunas que precisam obrigatoriamente constar no arquivo trace para que este possa ser reproduzido. O Profiler disponibiliza um template chamado TSQL_Replay onde todas as colunas e eventos necessários ao Replay Trace já estão selecionados, para a visualização das colunas e eventos necessários clique aqui.

    Antes de colocar a mão na massa vou fazer algumas considerações sobre o uso do Replay Trace em um servidor diferente, de qual será o foco do nosso artigo.

    Para que o Replay Trace tenha sucesso precisamos assegurar que todos os logins e usuários sejam criados no servidor secundário e estejam no banco de dados correspondente, tenham as mesmas permissões, mesmas senhas e estejam configurados para utilizar o mesmo banco de dados como banco de dados padrão. Além dos logins o ID dos bancos de dados devem ser os mesmos,ao menos que a coluna DatabaseName esteja selecionada.

    Agora que conhecemos o conceito e vimos algumas considerações podemos iniciar nossos testes, em nosso exemplo irei criar 4 conexões com o banco de dados e cada conexão irá executar uma instrução SQL aleatória para demonstrar atividade na base de dados, as instruções serão coletadas com o Replay Trace, após isso iremos reproduzir o Replay Trace em uma segunda instancia para analisar os resultados e verificar as conexões criadas na instancia no momento da execução do trace.

    Em nossa instancia primária chamada SERV-SQLPRODUCAO vamos criar 4 conexões. A minha primeira conexão recebeu o SPID 52 (lembre-se dos SPID´s iremos nos referenciar a eles mais adiante), estou logado com o login sa e vou executar o seguinte lote de instruções SQL para criar no banco de dados Northwind e nossa tabela de teste.

    USE Northwind

    –Criando a tabela ReplayTeste.
    CREATE TABLE ReplayTeste
    (
         COD INT IDENTITY(1,1)
        ,TEXT VARCHAR(50) DEFAULT ‘REPLAY’
        ,DATE DATETIME DEFAULT GETDATE()
    )

    Após criar a tabela ainda na mesma conexão insira a instrução SQL abaixo para povoar a tabelas e simular a atividade na base, nao a EXECUTE iremos executa-la no momento do Trace.

    –Lote de registros
    INSERT INTO ReplayTeste VALUES(DEFAULT,DEFAULT)
    GO 50000

    Agora abra mais uma nova conexão,altere o contexto do banco de dados para Northwind e insira a instrução abaixo, mas não a execute agora,note que o spid desta conexão é 53.

    –Comando DBCC Checkdb para simular atividade na base.
    DBCC CHECKDB(‘Northwind’)

    Repita os passos anteriores para criar uma nova conexão, o spid desta conexão é 54.

    –simulando acesso simultaneo a base de dados.
    SELECT     O.OrderID
            ,Od.ProductID
            ,Od.UnitPrice
            ,Od.Quantity
            ,P.ProductID
            ,P.ProductName
    FROM Orders O
    INNER JOIN [Order Details] Od
    ON O.OrderID = Od.OrderID
    INNER JOIN Products P
    ON Od.ProductID = Od.ProductID
    WHERE P.ProductName = ‘Chang’

    Em nossa última conexão vamos inserir a instrução SQL abaixo, o spid desta conexão é 55.

    –Simulando atividade na base.
    UPDATE Employees
    SET BirthDate = GETDATE() – 1

    Pronto agora com as nossas conexões preparadas vamos inciar nosso Replay Trace e executar cada uma, para coletar os dados. Antes de iniciar nosso trace vamos dar uma olhada nas conexoes ativas no momento utilizando a Store Procedure SP_WHO2, conforme a imagem abaixo.

    imagem4

    Podemos observar as 4 conexões ativas através dos SPID´s 52,53,54,55. Para iniciar o Replay Trace iremos abrir o SQL Server Profiler criar um novo Trace especificando o template TSQL_Replay, especifique para salvar o trace em um arquivo como mostrado na imagem abaixo:

    imagem1

    Podemos visualizar após a definição do template TSQL_Replay as colunas e eventos selecionados por padrão, conforme mencionei acima clicando na aba Events Selection como mostrado na figura abaixo:

    imagem2

    Especifique um filtro para o DatabaseName Northwind, no botão Column Filters conforme abaixo.

    imagem3

    Após definir o Filtro clique em Run para iniciar o trace. Com o trace iniciado vamos executar todas as instruções em cada conexão começando pelo SPID 52 para começar a coletar os dados. Durante a execução do Trace a tela do Profile irá ficar conforme a imagem abaixo, espere até o Loop terminar e pare o trace. Para visualizar as outras instruções utilize a opção Find no menu Edit para localizar as instruções dentro da saída.

    imagem5

    Agora que temos o arquivo trace, precisamos importa-lo no segundo servidor para reproduzir o trace. Como mencionei acima para o Replay Trace ter sucesso em sua execução é preciso satisfazer algumas considerações como mesmos logins e senhas, banco de dados padrão para cada login utilizado no Trace, Database ID iguais ou coluna DatabaseName selecionada. Para reproduzir o trace faça um backup do Banco de dados Northwind e restaure o mesmo no segundo servidor, transfira o arquivo trace de qual será importado no SQL Server Profiler do nosso segundo servidor. Neste artigo não irei mostrar os detalhes de Backup e Restore, para uma completa informação veja no post SQL Server 2005 Backup e Recover.

    Com o Banco de dados Northwind restaurado, iremos truncar a tabela ReplayTeste para eliminar os registros inseridos a partir do Loop. Com as outras instruções não precisamos nos preocupar pois o SPID 53 executa o comando DBCC CHECKDB para testar a integridade da base de dados, o SPID 54 executa uma instrução SELECT na tabela Orders, Order Details e Products e o SPID 55 executa um UPDATE na tabela Employees definindo sempre um valor diferente para a coluna Birthdate.

    TRUNCATE TABLE ReplayTeste

    Antes de importar o arquivo trace e reproduzi-lo vamos executar a store procedure SP_WHO2 para visualizarmos as conexões ativas conforme a imagem abaixo.

    imagem6

    Repare que apenas o SPID 52 estar ativo pois foi esta conexão que utilizamos para Restaurar Backup do Banco de dados Northwind, Truncar a tabela ReplayTeste e executar a store procedure SP_WHO2, agora vamos iniciar o SQL Server Profiler e importar o arquivo trace para reproduzir o trace. Após importar o arquivo trace clique no botão Start Trace para iniciar o trace, se conecte ao segundo servidor (no meu exemplo é SERVER02) e é aberto a tela Replay Configuration. Neste momento podemos especificar se queremos salvar o resultado de saída do trace para um arquivo ou uma tabela, se iremos executar o trace na sequencia exata de qual os eventos ocorreram no servidor, esta opção habilita Debugging ou se iremos utilizar multiplas threads para executar os eventos trace de qual otimiza a performance do servidor, podemos também definir quantas threads serão utilizadas e a ultima opção podemos selecionar se será exibido o resultado do trace. Deixei as configurações padrão conforme a imagem abaixo pois quero simular a mesma carga do servidor original.

    imagem7

    Na aba Advanced Replay Options podemos especificar algumas opções interessantes sobre o Replay Trace como definir se serão executados SPID´s do sistema e ainda especificar apenas um SPID em particular para executar, ou seja, podemos especificar que apenas uma conexão de usuário será executada. A imagem abaixo lista os SPID´s envolvidos no trace, reparem que é exatamente as mesmas conexões que abrimos para simular a atividade no banco de dados SPID 52,53,54 e 55. Abaixo temos a opção para limitar a execução do replay de acordo com uma determinada data e hora. Não irei selecionar para executar um SPID em particular para visualizarmos a atividade real com todas as conexões envolvidas. Desmarque a opção Replay one SPID only e clique em OK para iniciar o Replay Trace.

    imagem8

    Durante a execução do Trace a janela do SQL Server Profiler irá ficar mais ou menos conforme a imagem abaixo, notem que é exibido uma porcentagem da conclusão do Replay Trace, o painel é divido exibindo em sua parte superior o bloco de instruções sendo executadas, no meio os resultados.

    imagem10

    Antes de concluir a execução do trace alterne para o SQL Server Management Studio e execute a store procedure SP_WHO2 para visualizar as conexões existentes no momento da execução do trace, reparem na coluna ProgramName de qual indica as conexões através do SQL Server Profiler conforme imagem abaixo.

    imagem9

    Após a conclusão do Replay Trace é exibido os resultados no painel inferior como total de eventos executados, tempo gasto durante o Replay, total de erros, erros internos, etc.imagem11 

    Para verificarmos que o Replay Trace foi executado com sucesso, podemos executar a seguinte instrução SELECT de qual irá retornar o número de linhas armazenadas na tabela ReplayTeste.

    SELECT COUNT(*)
    FROM ReplayTeste

    ———–
    50000

    (1 row(s) affected)

    Pronto ! O Replay Trace foi executado com sucesso, foram inseridos as 50 mil linhas em nossa tabela de teste. Com isso concluo meu artigo sobre Replay Trace, mostrando algumas vantagens em utilizar essa poderosa feature disponível no SQL Server.

    Até o próximo.