SQL Server 2012 - MDW – Data Collector

Luan Moreno

Maio, 2014

Introdução

Sempre que pensamos em manter nosso ambiente unificado, aonde podemos assim ter os dados reunidos para análise, temos dor de cabeça. Essa coleta de informações sempre é difícil e faz com que nós DBA’s fiquemos com um sentimento de que nem tudo está sendo vigiado ou sob controle.

Para tentarmos solucionar esse problema, utilizavamos T-SQL, Rotinas de coletas de dados, JOBS, Pacotes de SSIS e outros, sempre sendo assim uma rotina muito trabalhosa.

Para tudo isso que acabei de falar temos agora no SQL Server 2008 o Data Collector. Ele é uma nova feature que realiza a coleta de informações de banco de dados, sendo assim ele possui um conjunto de componentes que o ajuda nessas coletas.

Todas essas informações coletadas são colocadas em um banco de dados chamado de Management Data Warehouse (MDW). Esse banco de dados tem como objetivo centralizar as informações de coletas para análise posterior.

(Figura 1 – Ciclo de Vida de um dados dentro do MDW.)

Dentre esse conjunto de soluções, temos que o Data Collector faz parte de uma parte da solução. O Conjunto de "Data Collection Sets", são conjuntos de coletores de informações de queries (Consultas), atividade do servidor, uso dos bancos de dados, tendência de crescimento dos bancos de dados dentre outras informações.

Essas informações históricas são armazenadas no "Management Data Warehouse", que por sua vez é o banco de dados do Data Collector (DC), aonde conseguimos recuperar as informações das coletas realizadas pelos Collection Sets.

Os Componentes que são usados em conjunto com o Data Collector são o banco de dados MDW e os relatórios que ele disponibiliza depois de sua instalação.

As Áreas de Atuação do DC são busca de informações realizadas a consultas e estátisticas, informações de uso de disco dos bancos de dados, monitoramento de cpu, memória e uso de rede dentre outros.

Ferramentas Utilizadas São o SQL Server Integration Services (SSIS) somente o run-time que já vem instalado no SQL Server, o SQL Server Reporting Services (SSRS) e o SQL Server Agent que realiza o agendamento das coletas realizadas para dentro do banco de dados MDW.

Para que fique umm pouco mais fácil de se enternder alguns termos usados pelo DC, segue lista.

- Collection Set - O grupo de itens que possuem vários coletores.

- Collection Item – O tipo de coleta, ou seja os items que se deseja coletar.

- Collection Mode - Cached ou NonCached

• Cached Mode - Coleta das informações contínua, tudo que é coletados está no cache do servidor.
• Noncached Mode – On-Demand ou collection snapshot – realiza a coleta no mesmo instante do acontecimento e coloca em um repostório temporário (Temporary Storage).

- Management Data Warehouse - O Banco de Dados relacional que realiza o armazenamento dos dados da coletas realizadas pelo Data Collector

Arquitetura e Armazenamento

O DC é integrado com o SQL Server Agent e o Integration Services como dito anteriormente. O SQL Agent – realiza os JOBS dos collection types.

O SQL Server Integration Services (SSIS) – é utilizado para coletar e inserir dentro do MDW as informações coletadas pelo os collection sets.

(Figura 2 – Arquitetura e Processamento do MDW.)

O modelo acima mostra o funcionanemto interno do DC. Nesse gráfico vemos que o mesmo é dividido em 4 partes, que são elas:

• Storage - Parte aonde são armazenadas todas as informações coletadas pelos Collection Sets e seus Collection Items.
• Execution - Usado em conjunto com o storage o Data Collection para que assim o pacote do integration services consiga inserir os dados dentro do MDW.
• API- Realiza a interação entre o usuário e o DC.
• Cliente - A interface para a visualização das informações dentro do Management Studio (SSMS).

System Data Collection Sets e Collection Items

Temos 3 coletores por padrão no DC que são: Disk Usage, Query Statistics e Server Activity, dentro de cada coletor temos os items no qual realizam a coleta dentro das instâncias de banco de dados. Aqui veremos todas as informações sobre eles.

• Disk Usage Collection Set

Esse coletor verifica o crescimento dos bancos de dados, tanto dos arquivos de dados (.mdf), como os arquivos de log (.ldf) dando assim uma tendência de crescimento diária em MB dos arquivos analisados.

Collection Itens

Disk Usage – Data Files e

Disk Usage – Log Files

Esses items realizam a coleta das informações usando T-SQL utilizando como consulta:

Snapshot de Sys.partitions e Sys.allocation_units

DBCC SQLPERF (LOGSPACE)

Snapshot de sys.dm_io_virtual_file_stats

Collection set name	Disk Usage
Collection mode	Non-cached
Upload schedule frequency	Every 6 hours
Data retention	730 days
Collection items	Disk Usage – Data Files

• Server Activity Collection Set

Coleta informações de atividades do servidor, estatística, performance, cadeias de lock, informações gerais de memória, CPU e rede.

Collection Itens

Server Activity – DMV Snapshots

Server Activity – Performance Counters

Esse coletores nos deixam visualizar recursos de utilização de servidor, tanto da própria utilização da máquina, como tambêm da utilização da instância, ajudam na procura de gargalos na instância, como visualização de locks, blocks, deadlocks, problemas de contenção e outros…

Collection set name	Server Activity
Collection mode	Cached
Upload schedule frequency	Every 15 minutes
Data retention	14 days
Collection items	Server Activity – DMV Snapshots

• Query Statistics Collection Set

Coleta de estatísticas, planos de execução, texto das consultas realizadas e outras informações a consultas realizadas aos banco de dados.

Collection Itens

Query Statistics – Query Activity

Esse coletor busca informações de todas as consultas realizada contras os bancos de dados. Os dados dessas coletas são apresentadas em um relatório mostrando seu plano de execução, o texto gerado e informações de uso da mesma.

Collection set name	Query Statistics
Collection mode	Cached
Upload schedule frequency	Every 15 minutes
Data retention	14 days
Collection item	Query Statistics – Query Activity

Funcionamento

Ou seja até esse momento podemos dizer que:

• O DC assiste o sistema, temos os coletores realizando a inserção no banco de dados MDW.
• Temos os relatórios divididos por áreas de interesse, gerando assim relatórios históricos e mostrando resolução de problemas quando possível.
• Temos tambêm que ter a consiência que o DC não é utilizado para monitoramente em tempo real, temos uma base que serve para análise de informações históricas e não em real-time.

(Figura 3 – Processamento interno do MDW.)

Temos o funcionamento interno da ferramenta que são divididos em:

1 – Fase:

Temos o serviço do DC rodando juntamento com o sistema operacional, o nome desse serviço se chama DCEXEC.

2 – Fase:

Os Collection Sets e os Collection Items trabalham para realizar a coleta das informações e colocar no que chamamos de Temporary Storage.

3 – Fase:

O pacote do SSIS realiza a busca das informações coletadas no Temporary Storage e realiza a inserção das informações dentro do MDW.

4 –Fase:

Após todas as informações serem coletadas e colocadas no MDW o SSRS monta os relatórios e disponibiliza dentro do Management Studio do SQL Server.

Segurança

O modo de segurança utilizado pelo Data Collector é role-based security modelo utilizado pelo SQL Server, nesse modo é possível que o administrador do banco de dados rode em vários coletores de informações no contexto da segurança tendo somente as permissões necessárias.

Esse modelo de segurança é utilizado também para operações envolvendo tabelas internas que são acessadas por views e stored procedures. Nenhuma permisão é concedida nível tabela, todas as permissões são checadas nível stored procedure e view.

Segurança de Rede – Network Security

Informações sensível serão passadas por instâncias e servidores, sendo assim todos as informações coletadas são protegidas usando o mecanismo padrão de segurança e utilizando protocolo de criptografia do SQL Server.

Permissão do Data Collector

Utilizando a metodologia de acesso do Maior-Privilêgio e Menor-Privilêgio seguem acessos permitidos para o DC.

• Dc_admin
• Dc_operator
• DC_proxy

Todas as regras são armazenadas no banco de sistema msdb. Por padrão nenhum usuário é membro dessas regras, todas as regras citadas teem que ser permitidas explicitamente.

Todos os logins que possuem a fixed server role sysadmintem acesso full a todas as views do DC.

Dc_Admin Role

Usuários que possuem essa regra teem acesso FUL (criação, leitura, Update e Delete) para as configurações do Data Colector e Instância do SQL Server.

As operações que essa regra pode executar são:

• Configurar as propriedades do Collector-Level
• Adicionar novos Collection-Sets
• Instalação de novos Collection Items
• Criação e permissão de executar os JOBS do SQL Server Agent

Dc_Operator Role

Usuários que possuem essa regra tem acesso de leitura e update , essa permissão suporta operações de excutar a configuração dos collections sets. Os membros dessa regra podem executar as seguintes operações:

• Iniciar ou parar os collection sets
• Ver as informações referentes aos collections sets
• Alterar as configurações dos collection sets

Dc_Proxy Role

Usuários que possuem essa regra tem acesso de leitura aos Data Collection Sets, é possível que também que tenham acesso a execução e criação do seu próprio JOB step, se o mesmo tiver uma conta de proxy criada.

Os membros dessa regra podem executar as seguintes operações:

• Visualização das informações do Collection Sets
• Informações dos eventos de run-time dos collection sets

Permissões para configuração do MDW

Dependendo da fixed database role que o usuário possua o mesmo poderá realizar acesso ao MDW. Em ordem do Maior-Privilêgio e do Menor-Privilêgio, temos as seguintes regras:

• Mdw_admin
• Mdw_writer
• Mdw_reader

Todas as regras são armazenadas no banco de sistema msdb. Por padrão nenhum usuário é membro dessas regras, todas as regras citadas teem que ser permitidas explicitamente.

Todos os logins que possuem a fixed server role sysadmintem acesso full a todas as view do DC.

Mdw_admin Role

Os usuários que possuem essa permissão possuem acesso total ao banco de dados, quando há mudança no schema do banco de dados o usuário necessita ter o acesso nível servidor de dc_admin para a instalação de um novo collector type.

Mdw_Writer Role

Acesso de escrita e leitura somente

Mdw_Readre Role

Acesso de leitura.

Configuração, Mdw e Jobs

1 – Configuração

Vamos agora passar por cada passo da configuração do DC.

Selecione o ícone do Data Collection.

Nesse momento será aberto uma tela explicatica de configuração, clique em next

Nessa tela será mostrado duas opções, utilize a primeira, ela fará com que você selecione em qual instância do SQL Server você deseja instalar o MDW.

Realize a criação do banco de dados na instância escolhida.

Depois da criação do banco de dados, agora é necessário realizar as permissões para um Login específico, como dito anteriormente, cada role representa um nível de autoridade para o Data Collector. Como você é quem está realizando a configuração, dê acesso para o seu Login de mdw_admin como mostrado na figura abaixo.

Agora que você realizou a criação do banco de dados e sabe aonde ficará seu repositório fiel e unificado de dados é necessário que seja configurado o Temporary Stage. Esse local é aonde os coletores irão jogar um arquivo temporário para que a o JOB contendo o pacote em SSIS realizará a inserção dos dados dentro do MDW.

Agora escolha no seu servidor qual será a pasta que receberá os arquivos temporários do DC.

Configuração realizada com sucesso, agora o Data Collector está funcionando.

Visualizando os Collection Sets que explicamos anteriormente.

2 – Management Data Warehouse (MDW)

O banco de dados do DC é dividido em dois schemas, o core e o snapshot

No schema core temos toda a parte de configuração que acabamos de realizar como, local aonde ficará o armazenamento temporário, qual o banco de dados escolhido, qual a periodicidade de coleta dos coletores e outras informações de configuração.1

No schema snapshot temos todas as informações que são coletadas dos coletores do DC. Ainda temos toda a parte de logging das informações, podemos descubrir muitas coisas interresantes analisando as tabelas dentro desse schema, sendo que tambêm podemos encontrar informações de logging dos pacotes do SSIS que são gravados no MDW nas funções do banco de dados msdb.

A retenção de todas as tabela que referenciam ao Data Collector, estão diretamente relacionadas ao tempo de retenção de cada collection set.

3 – Jobs do DC

Quando a instalação do DW é concluída, o mesmo realiza a criação de alguns JOB para a coleta das informações. Devemos entender que os Collection Sets possuem seus Collection Items e esse são agendados para serem coletados em um periodo de tempo, sendo assim os JOBS se encarregam de rodar as rotinas do SSIS para que assim ele possa pegar as informações contidas no Temporary Stage e colocar dentro do MDW do DC. Assim toda a parte dos agendamentos e coletas são feitas pelos JOBS.

Temos um JOB chamdo de mdw_purge_data[mdw], esse job faz com que dependendo da retenção de data que você especificar para seu Collection Item ele será executado, temos que ter cuidado com tal rotina, porque a mesma realiza intensas operações de exclusões, sempre execute ela de preferência a noite ou a madrugada.

Curiosidades

• O Consumo no Servidor é de aproximadamente 5% da cpu total, em sistemas com Windows Server 2008, realizando coletas de aproximadamente 250-300 MB por dia.
• Queda de performance entre de 20-30 GB de espaço utilizado, utilize o backup para análise semanal ou mensal.
• Não é necessário a instalação do SSIS, porque o DC utiliza somente o componente de run-time do SSIS.

Melhores Práticas

• Verifique os jobs e tente ajustá-lo conforme seu ambiente.
• Faça uma configuração gradativa das instâncias na qual deseja coletar.
• De preferência, utilize uma máquina separada para realizar as coletas de todas as instâncias que deseja.
• Não esqueça de configurar o Purge of Data, essa rotina é bastante custosa para o SQL Server.
• Realize a análise periódica dos dados realizados pelos coletores.

| Home | Artigos Técnicos | Comunidade