Thiago Zavaschi R2 www.zavaschi.com

24Sep/141

CDC – Change Data Capture – O que é? Como Usar?

Olá pessoal,

Publiquei um artigo sobre Change Tracking (CT) e comentei que existe um “irmão” chamado Change Data Capture (CDC). Ambos presentes desde o SQL Server 2008.

O CDC serve para fazer capturas das operações de INSERT, UPDATE e DELETE realizadas em uma tabela, porém, diferentemente do CT, ele consegue armazenar o dado. Isso leva a um overhead maior do que o que o CT causa, devido a necessidade de armazenar os dados modificados.

Os dados capturados são armazenados em uma tabela SQL Server, o que facilita o consumo dos mesmos. E sim, é uma solução muito mais elegante e eficiente do que utilizar TRIGGERs sobre as tabelas.

Já vi cenários onde o CDC é utilizado como mecanismo de auditoria, e, ainda que ele consiga cumprir com esse papel (dados algumas premissas), já vimos aqui no blog sobre o recurso que seria mais recomendado nestes casos: SQL Audit.

Vamos entender um pouco melhor como configurar e como este recurso de captura funciona.

Como Funciona

Olhe a seguinte imagem (extraída de http://msdn.microsoft.com/en-us/library/cc645937.aspx):

Change data capture data flow 

O SQL Server Change Data Capture, captura as alterações diretamente do arquivo de log da database. Ou seja, os processos DML que ocorrem na database não são condicionados ao CDC, como seriam no caso de triggers.

É importante ressaltar a diferença de metodologia do Change Data Capture para o Change Tracking. O CDC é assíncrono e captura as informações do log. Já o CT é um processo leve, porém síncrono (pois as informações das operações são armazenadas no momento da operação). Observe também que o CDC é uma feature enterprise e o CT não é (estando assim disponívél em outras SKUs).

Uma vez o log lido, as informações pertinentes às modificações são aplicadas a tabelas de modificações (que podem crescer rapidamente, de acordo com a alteração dos dados na tabela com o tracking ativo).

O CDC fornece funções que operam sobre estas tabelas para retornar os valores em um formato filtrado para atender em geral aos processos de ETL. Estes processos por sua vez se beneficiam muito pois não precisam comparar os dados da tabela com os dados de um DW por exemplo para determinar se houve mudança ou não. Isso pode baixar consideravelmente o tempo de carga nos seus pacotes de ETL.

Maiores informações (em inglês): http://msdn.microsoft.com/en-us/library/cc645937.aspx

Como Configurar

Para utilizar o CDC é necessário habilitá-lo para a database através da stored procedure: sys.sp_cdc_enable_db. E na sequência habilitar nas tabelas desejadas com sys.sp_cdc_enable_table.

Imagine que temos uma database chamada MonitorarCDC e que gostaríamos de monitorar algumas de suas tabelas. A começar com a tabela chamada Empregados.

Primeiramente habilitamos o CDC na database MonitorarCDC:

USE MonitorarCDC
GO
EXECUTE sys.sp_cdc_enable_db;

Se atente que o nome da databe não é passada como parâmetro, é utilizado o contexto atual.

Perceba também que isso irá criar na sua database um schema chamado cdc, bem como algumas tabelas de sistema para gerenciar seu CDC.

image

A função de cada uma destas tabelas você pode ver abaixo:

cdc.captured_columns – Retorna a lista de colunas capturadas.
cdc.change_tables – Armazena a lista de todas as tabelas habilitadas para captura.
cdc.ddl_history – Contém toda a modificação de estrutura (DDL) desde que a captura foi habilitada.
cdc.index_columns – Contém os índices associados às tabelas capturadas.
cdc.lsn_time_mapping – Esta tabela mapeia o número LSN e o tempo.

Agora vamos criar uma tabela para monitorar e vamos inserir alguns valores:

CREATE TABLE Empregados
( matricula int primary key,
  nome varchar(255) not null,
  nascimento datetime not null,
  cargo varchar(255) null,
  ativo bit not null
)

INSERT INTO Empregados
VALUES
(1000, 'Thiago', '1980-01-01', 'Gerente Geral', 1),
(1001, 'Maria', '1987-01-01', 'Auxiliar Administrativo', 1),
(1002
, 'Pedro', '1980-01-01', 'Operação Fiscal', 1),
(1003, 'Jorge', '1980-01-01', 'Analista Financeiro', 1)

Agora vamos habilitar o CDC para a tabela Empregados. O exemplo não cobre, mas existem outros parâmetros na procedure sp_cdc_enable_table, como por exemplo a possibilidade de especificar o monitoramento de apenas algumas colunas. Para maiores informações desta procedure segue a documentação: http://technet.microsoft.com/en-us/library/bb522475(v=sql.120).aspx

EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name   = N'Empregados',
@role_name     = NULL
GO

O parametro “role_name” serve para atribuir acesso a esta tabela somente aos pertecentes a esta role passada, caso a role não exista ela é criada. Em caso de NULL, como no exemplo, todos possuem acesso a tabela de tracking.

Existem alguns erros que você pode receber caso tenha instalado o SQL Server e depois renomeado a maquina (servidor). No meu caso isso aconteceu e eu recebi o seguinte erro:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 645
Could not update the metadata that indicates table [dbo].[Empregados] is enabled for Change Data Capture. The failure occurred when executing the command '[sys].[sp_cdc_add_job] @job_type = N'capture''. The error returned was 22836: 'Could not update the metadata for database MonitorarCDC to indicate that a Change Data Capture job has been added. The failure occurred when executing the command 'sp_add_jobstep_internal'. The error returned was 14234: 'The specified '@server' is invalid (valid values are returned by sp_helpserver).'. Use the action and error to determine the cause of the failure and resubmit the request.'. Use the action and error to determine the cause of the failure and resubmit the request.

Como descobri isso?

O retorno da variável @@SERVERNAME foi “ZAVA-SP3” sendo que atualmente é “THIAGOZ-MS”. Isso significa que os metadados do SQL Server estão desatualizados.

Para resolver siga os passos presentes aqui:

http://msdn.microsoft.com/en-us/library/ms143799.aspx

image

Basicamente no meu caso eu precisei utilizar o seguinte comando:

sp_dropserver 'ZAVA-SP3';
GO
sp_addserver 'thiagoz-ms', local;
GO

Perceba que a sp_helpserver retorna o valor atualizado, porém o @@SERVERNAME me retorna o valor antigo. Para completar o procedimento é necessária a reinicialização da instância.

Agora ao executar a proc sys.sp_cdc_enable_table vemos que funciona e a mensagem de retorno é:

Job 'cdc.MonitorarCDC_capture' started successfully.
Job 'cdc.MonitorarCDC_cleanup' started successfully.

O CDC, como vimos, é assíncrono e (caso não tenha replicação) irá utilizar jobs para fazer a leitura do log de transações, cuja principal função é a execução das procedures sys.sp_MScdc_capture_job e sys.sp_MScdc_cleanup_job.

Quando há a replicação para não haver competição entre o capture e o transactional logreader da replicação, ambos passam a utilizar o transactional logreader.

image

Além destes jobs no SQL Agent, há a criação de uma tabela de sistema com o nome e estrutura parecidas, com o seguinte padrão de nomenclatura cdc.<schema>_<nome_da_tabela>_CT. No meu caso: cdc.dbo_Empregados_CT.

 image

A estrutura da tabela é semelhante, porém com 5 colunas a mais. Veja na imagem abaixo:

image

__$start_lsn: LSN associado com o commit da transação de mudança. Todas as mudanças feitas em uma mesma transação compartilham o mesmo valor de __$start_lsn. Se houve deleção de 10 linhas, as 10 linhas possuirão o mesmo valor.

__$end_lsn: Para propósitos informacionais apenas. Não suportado e a compatibilidade futura não é garantida (atualmente insere o valor NULL para todos os valores).

__$seqval: Valor usado para ordenar as operações dentro de uma transação.

__$operation: Identifica a operação (DML) realizada. 1 – DELETE, 2 – INSERT, 3 - UPDATE (valores antigos), 4 – UPDATE (novos valores).

__$update_mask: Mostra os valores alterados no update (1 para as posições alteradas). No caso de DELETE e INSERT onde todas as colunas são afetadas, virá uma máscara com 1’s em todas as posições.

Maiores informações sobre esta tabela criada pode ser vista na documentação: http://msdn.microsoft.com/en-us/library/bb500305.aspx.

Como Obter os Valores

Bom, agora que já temos o CDC implementado e já conhecemos as estruturas criadas, vamos realizar algumas alterações e ver como consumir estas informações.

Vamos executar algumas operações:

INSERT INTO Empregados
VALUES
(1010, 'Mariano', '1975-01-01', 'Atendente', 1),
(1011, 'Paulo', '1990-01-01', 'Atendente', 1)

DELETE FROM Empregados WHERE matricula IN (1002, 1010)

UPDATE Empregados SET cargo = 'Diretor Financeiro' WHERE matricula = 1003

Vamos verificar o que há na tabela de tracking (as imagens correspondem às mesmas linhas):

image

image 

Ok, consigo ver as operações que foram realizadas e os dados, mas como sei quando aconteceu?

Bom, o LSN é sequencial e corresponde a um tempo específico, esta informação fica armazenada na tabela cdc.lsn_time_mapping criada na database monitorada no momento em que você habilitou o CDC.

Para facilitar o consumo destas tabelas, sem termos que executar uma série de joins e análises, o CDC criou para nós algumas funções que nos auxiliam neste consumo, tais como: sys.fn_cdc_map_time_to_lsn e cdc.fn_cdc_get_all_changes_<schema>_<tabela>.

Veja abaixo um exemplo do consumo de dados utilizando estas funções no nosso exemplo para trazer as modificações das últimas 24 horas:

DECLARE
    @begin_time DATETIME,
    @end_time DATETIME,
    @begin_lsn BINARY(10),
    @end_lsn BINARY(10)

SELECT @begin_time = GETDATE()-1, @end_time = GETDATE();
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @begin_time);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);
SELECT *
FROM cdc.fn_cdc_get_all_changes_dbo_Empregados(@begin_lsn,@end_lsn,'all')
GO

A função fn_cdc_map_time_to_lsn pode receber um dos seguintes parâmetros (auto explicativos):

  • largest less than
  • largest less than or equal
  • smallest greater than
  • smallest greater than or equal

Essa tabela de tracking pode crescer muito?

Se as suas tabelas monitoradas são altamente transacionais, você pode esperar um rápido crescimento das mesmas. O job de cleanup é o responsável pela limpeza destas tabelas.

Por padrão há a limpeza a cada 3 dias, porém esse período de retenção é configurável. Na realidade este valor é configurado em minutos, neste caso: 4320 minutos.

Para alterar o período de retenção utilize a procedure abaixo. Há um limite de tempo máximo para a retenção que é de 52494800 minutos (100 anos), e caso seja especificado deve ser um valor inteiro e positivo. Maiores informações na documentação (em inglês): http://technet.microsoft.com/en-us/library/bb510626(v=sql.110).aspx.

sp_cdc_change_job @job_type='cleanup', @retention=minutes


Bom pessoal, e era isso o que eu queria mostrar hoje. Espero que seja útil!

Abraços,
Zava

11Aug/140

SQL Saturday #325 – Estarei presente!

Olá pessoal,

Com grande alegria comunico que estarei presente ao SQL Saturday #325 que será realizado dia 27 de setembro de 2014 em São Paulo.

Irei realizar duas palestras:

Junte, manipule e conecte dados com o Power Query
Conheça essa poderosa ferramenta para recuperar dados de diferentes fontes e prepará-los para análise. Parte da suíte de Power BI da Microsoft. Em muitos casos pode ser considerada uma ferramenta de "self-service ETL", podendo trazer e manipular dados de Hadoop, Facebook, fontes relacionais, entre muitas outras.

Analysis Services Internals
Se você quer conhecer mais sobre o Analysis Services (SSAS) internamente, arquitetura e como ele lida com memória, arquivos e threads essa é a sua sessão!

 

Não sabe o que é o SQL Saturday (SQLSat)?

Veja a descrição do próprio site do evento:

"SQLSaturday é um evento de capacitação para profissionais de SQL Server, Business Intelligence e aqueles que querem aprender sobre o universo da Plataforma de dados da Microsoft. Este evento será realizado em 27 de setembro de 2014, na UNIP Tatuapé, Rua Antônio Macedo, 505 - Parque São Jorge, Tatuapé - São Paulo - SP, São Paulo, 03087-040, Brasil. A entrada ao evento é gratuita, todos os custos são cobertos por doações e patrocínios. Os lugares são limitados, registre-se para garantir sua vaga, e compartilhe com os outros para que todos possam comparecer."

O link para as inscrições é o seguinte (já está praticamente lotado):

http://www.sqlsaturday.com/325/eventhome.aspx

Obrigado e aguardo vocês lá!

30May/142

Change Tracking – O que é? Como Usar?

Olá pessoal,

Hoje comentarei sobre um recurso velho no SQL Server (desde o 2008) mas que vejo pouco utilizado e que muitas vezes os desenvolvedores fazem algumas coisas “mirabolantes” para ter resultados similares.

O Change Tracking (CT) e o Change Data Capture (CDC) são ferramentas de suporte a sincronização. O CDC abordarei no próximo post.

Os cenários em que o CT se aplica são aqueles cenários onde você trabalha com aplicações offline, aplicações ocasionalmente conectadas ou aplicações que não necessitam conhecer em tempo real que houve atualização nos dados.

O Change tracking lhe garante a informação sobre qual linha foi modificada (linha inserida, coluna atualizada, deletada, etc.). O seu “irmão” Change Data Capture armazena todo o histórico do dado modificado (por essa razão pode ser vista como uma solução mais custosa).

Em cenários de DW ambas tecnologias podem ajudar a identificar as linhas que sofreram modificação para que seja extraído do sistema transacional somente as linhas que foram modificadas e assim diminuir a carga sobre os sistemas transacionais fontes (CT e CDC são features do SQL Server, no SQL Server 2012 há a possibilidade de utilizar o CDC para Oracle – Instalador externo presente na mídia do SQL Server 2012).

26Apr/140

SQL Saturday #284 – Porto Alegre

Olá pessoal,

Hoje estamos tendo a edição #284 do SQL Saturday (Porto Alegre).
Fiz uma apresentação sobre MDX (ainda há muito chão para ele mesmo com o DAX), e aqui disponibilizo os materiais.

Materiais download

Obrigado a todos os presentes! :)

Tagged as: , , No Comments
23Oct/133

Whitepaper de Performance Tuning para o Modelo Tabular do SSAS 2012

Olá pessoal,

Este post é apenas para informar que já foi lançado o Performance Guide para o modelo tabular do Analysis Service 2012. Recebi já muitas perguntas sobre ele e aqui está.

Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services

http://msdn.microsoft.com/en-us/library/dn393915.aspx

Enjoy!

PS: Esse post ficou algum tempo nos drafts, o guide já saiu há alguns meses e em breve teremos atualização para cobrir servidores NUMA.

13May/131

Data Explorer Release de Maio

Olá a todos!

Sexta-feira passada foi lançada publicamente mais uma build (May Release) do Data Explorer.

O Data Explorer é um add-in para o Excel para você realizar transformações nos dados de uma forma facilitada (self-service) e passível também de integração com o PowerPivot para análise.

Essa versão de maio contém diversas novidades que podem ser acompanhadas no blog do time de produto do Data Explorer:

http://blogs.msdn.com/b/dataexplorer/archive/2013/05/13/data-explorer-may-update-is-available-now.aspx
Essa ferramenta tem muito potencial, principalmente nos cenários em que a utilização do Integration Services (SSIS) poderia aumentar muito a complexidade da solução.

Fica a dica ;)
Thiago Zavaschi

10May/131

Analysis Services Internals – Formula e Storage Engines (e como funciona o cache?)

Olá pessoal!

O tópico de hoje é para servir de base a todos que desejam conhecer um pouco mais de como o servidor do Analysis Services (SSAS) trabalha por trás dos panos.

Existem diversos mecanismos internos que são responsáveis / interagem no processamento de uma query que é enviada ao servidor de análise (o processo detalhado de execução de uma query vou abordar em um tópico futuro específico): XMLA Listener, Formula Engine, Storage Engine e assim por diante.

Esse post abordará sobre o que é e para que servem a Formula e a Storage Engine.

22Apr/132

Sharepoint e Reporting Services – O que é suportado?

Olá pessoal, depois de um longo e tenebroso inverno (leia-se: muito trabalho aqui no time de engenharia) estou de volta e pretendo postar sobre os casos com que lido e continuar algumas séries de posts antigas aqui do blog.

O ponto que quero abordar hoje é sobre a suportabilidade da integração do SharePoint com o Reporting Services.

É de senso comum de quem trabalha com as tecnologias de Business Intelligence da Microsoft saber que o Reporting Services pode ser configurado para trabalhar de modo autônomo (modo nativo) ou em modo integrado ao SharePoint. No entanto nem sempre fica claro quais são as restrições de versões para essa compatibilidade.

Por exemplo: Posso usar o Reporting Services do SQL Server 2008 R2 com o SharePoint 2010? E com o SharePoint 2013? E se for o SQL Server 2008? E a versão dos Add-ins?

Este post serve para elucidar estes casos. Este post é baseado no seguinte pedaço do books online (em inglês): http://msdn.microsoft.com/en-us/library/dc6a3372-db26-43f0-b7aa-f725acc635c2

27Jul/123

SSIS Package Configuration (XML File) – Trocando a Connection String entre Servidores

Olá!

Uma dúvida comum que sempre surge quando vou atender um caso de SQL Server Integration Services (SSIS) é:

Ok Thiago desenvolvi 100 (leia-se muitos) pacotes no SSIS, porém toda a vez que vou fazer o deploy destes pacotes em homologação e produção eu tenho que ficar mudando as connection strings. Como proceder?”

O SSIS tem um mecanismo para ajudar neste sentido chamado SSIS Package Configuration, onde é possível salvar a suas configurações em um arquivo XML, variável de ambiente ou até mesmo em uma tabela do SQL Server que contenha os valores.

No artigo de hoje vou abordar apenas a configuração através de arquivo XML.

Mas então, como fazer?

Desenvolva seu pacote normalmente. Utilizando os Data Sources de desenvolvimento e associando os respectivos Connection Managers a eles da forma que for necessária ao seu fluxo.

Agora o que precisamos fazer é criar um arquivo de configuração XML que conterá os dados que os data sources utilizarão ao invés do que você configurou previamente.

Esta configuração você fará para o primeiro pacote e depois reutilizará o mesmo arquivo de configuração para os demais pacotes.

5Jul/121

SQL Server Analysis Services – Query Log 2000 x 2005+

Olá pessoal,

Estando no time de suporte premier da Microsoft recebo muitos chamados de clientes que desejam migrar seu Analysis Services do 2000 para versões mais recentes como o 2008 R2 e 2012.

Uma dúvida comum é sobre as diferenças entre a versão 2000 e as demais.

Hoje falaremos das principais diferenças do query log do SSAS 2000 para os SSAS posteriores.

O SSAS possui capacidade de logar informações sobre as queries que são disparadas contra o servidor (não estou falando do Profiler (traces) e nem do Flight Recorder (que também é um trace)) conhecido como query log.

O SSAS não armazena evetivamente a query executada, porém armazena informações sobre quais atributos e measures foram utilizadas, que mais tarde pode ser utilizada pelo wizard de otimização baseado em uso.

O SSAS 2005 (e os posteriores) não loga as queries na tabela de log de queries por padrão. Para fazer o log você deve explicitamente ativar esse recurso nas propriedades do SSAS.

A partir do SSAS 2005 não é possível utilizar o Access como repositório para o log de queries. Obrigatoriamente você terá de usar uma database do SQL Server para isso.

O SQL Server não precisa residir no mesmo computados que o SSAS.

O Analysis Services não utiliza mais o registro do windows para armazenar suas propriedades. Todas as propriedades do servidor SSAS que controlam o comportamento do query log são acessíveis através do Management Studio ou através da modificação do arquivo de configuração do SSAS.

O formato (campos e tipos de dados) da tabela de log mudou. Se você utiliza para algum outro fim, você precisará ajustar seus scripts personalizados.

Para maiores informações sobre como configurar o query log do SSAS segue o link (em inglês):

http://technet.microsoft.com/en-us/library/cc917676.aspx

Obrigado!