Microsoft Certified Solutions Associate & Expert – SQL Server 2016

Pessoal, recentemente finalizei a carreira de certificações (por enquanto) em SQL Server. Gostaria de através desse post compartilhar com vocês como está atualmente a carreira de certificações da Microsoft, com relação à plataforma de dados, e também dar algumas dicas de como vocês podem também alcançar um ou vários dos titulo de Microsoft Certified Professional.


Carreira de certificações atual (MCSA SQL 2016)

No que diz respeito ao SQL Server 2012/2014, a carreira de certificações se resumia a apenas uma onde, para conseguir o título de Microsoft Certified Solutions Associate, era necessário realizar 3 provas onde, em cada uma delas, eram medidos conhecimentos em três áreas diferentes: Desenvolvimento (Exame 70-461), Administração (Exame 70-462) e Desenvolvimento de Ambientes de BI (Exame 70-463).

Conseguir adquirir essa certificação não era tarefa fácil pois demandava do candidato conhecimentos profundos de 3 assuntos totalmente distintos.

Atualmente, no SQL Server 2016, a Microsoft mudou um pouco a maneira de pensar com relação à carreira de certificações e dividiu o MCSA em três trilhas diferentes. São elas:

  • MCSA SQL 2016 | Database Development – Esse título é adquirido pelo candidato que for bem sucedido nos exames 70-761 – Querying Data with Transact-SQL e 70-762 – Developing SQL Databases.
  • MCSA SQL 2016 | Database Administration – Esse título é adquirido pelo candidato que for bem sucedido nos exames 70-764 – Administering a SQL Database Infrastructure e 70-765 – Provisioning SQL Databases.
  • MCSA SQL 2016 | BI Development – Esse título é adquirido pelo candidato que for bem sucedido nos exames 70-767 – Implementing a SQL Data Warehouse e 70-768 – Developing SQL Data Models.

Impressões sobre as provas (MCSA SQL 2016)

            

Quando iniciei a jornada de certificações da plataforma do SQL Server 2016 eu já possuía o título de SQL Server MCSA 2012/2014 e, por isso, não houve a necessidade de que eu realizasse os 6 exames para conquistar cada um dos 3 títulos do MCSA do SQL Server 2016 pois, cada uma das 3 provas substitui a primeira prova (70-761, 70-764 e 70-767) do novo ciclo.

Essa substituição ocorre pelo simples fato de que o conteúdo estudado para as 3 provas do SQL Server 2014 é praticamente o mesmo que para a primeira prova de cada uma das novas carreiras. com base nisso, leiam:

Agora, quanto aos outros 3 exames, seguem abaixo minhas impressões:

  • Exame 70-762 – Developing SQL Databases – Esse exame é o segundo da trilha para MCSA em Desenvolvimento. Realizei essa prova quando estava em período BETA. foram em torno de 63 questões (Atualmente não deve passar de 45) onde os principais assuntos cobrados estão relacionados à identificação e solução de problemas relacionados tratamento de exceções em queryes, identificação e tratamento de Locks e Deadlocks e os demais assuntos relacionados à segurança de objetos, utilização de triggers e views para abstração de banco de dados físico na aplicação dentre outros.
  • Exame 70-765 – Provisioning SQL Databases – Esse exame é o segundo da trilha para MCSA em Administração de ambientes de bancos de dados em SQL Server. Também realizei esse exame em caráter BETA e, não se enganem, AZURE é o tema dessa prova. Dentre os assuntos mais cobrados estão: Identificar qual o melhor caminho para criação de uma infraestrutura no AZURE (Infrastructure as a Services [IaaS] ou Platform as a Services [PaaS]), como identificar o melhor tipo de Tier para Azure SQL Database com base nas possibilidades disponíveis em cada um deles, Como realizar Backup e Restore no ambiente Cloud, configuração do ambiente de dados do SQL Server em VMs no AZURE dentre outros.
  • Exame 70-768 – Developing SQL Data Models – Esse exame é o segundo da trilha para MCSA em Business Intelligence com a plataforma do SQL Server 2016. Esse exame é unica e exclusivamente relacionado ao SQL Server Analysis Services. Dentre outros assuntos, os mais cobrados são: Qual modelo (Multidimensional ou Tabular) usar dependendo dos requisitos fornecidos, Como desenvolver KPIs e Campos Calculados com MDX e DAX, Como ocultar dos usuários finais colunas e tabelas em ambos os modelos, Como otimizar cubos multidimensionais através de relacionamentos entre atributos, qual tipo de armazenamento utilizar em ambos os modelos (MOLAP/ROLAP x InMemory/DirectQuery) dentre outros.

    Carreira de certificações atual (MCSE SQL 2016)

Anteriormente existiam duas carreiras para se tornar um Expert nas soluções da plataforma de dados do SQL Server. Era possível se tornar Solutions Expert em Data Platform (Exame 70-464 e Exame 70-465) ou em Business Intelligence (Exame 70-466 e Exame 70-467).

Atualmente, essas duas certificações foram descontinuadas. Existe epenas um título que pode ser adquirido. Ele se chama Microsoft Certified Solutions Expert in Data Management and Analytics. Para adquirir essa certificação basta que você possua uma das certificações em MCSA SQL 2016 (Desenvolvimento, Administração ou BI) e seja bem sucedido em um dos exames abaixo:

  1. Exame 70-473 – Designing and Implementing Cloud Data Platform Solutions – “Este exame foi criado para candidatos que estiverem interessados em confirmar suas habilidades em projetar e implementar soluções de plataforma de dados da Microsoft. Os candidatos deste exame devem ter experiência relevante de trabalho em soluções de plataforma locais e baseadas em nuvem.”
  2. Exame 70-475 – Designing and Implementing Big Data Analytics Solutions – “Este exame de certificação destina-se a profissionais de gerenciamento de dados, arquitetos de dados, cientistas de dados e desenvolvedores de dados que criam soluções de análise para Big Data no Microsoft Azure. Os candidatos deste exame devem ter experiência relevante de trabalho em soluções de análise de Big Data.”
  3. Exame 70-464 – Developing Microsoft SQL Server Databases – “Este exame se destina a profissionais de banco de dados que compilam e implementam bancos de dados em várias organizações e garantem níveis elevados de disponibilidade de dados. Entre suas responsabilidades estão criar arquivos de banco de dados, tipos de dados e tabelas; planejar, criar e otimizar índices; garantir integridade de dados; implementar exibições, procedimentos armazenados e funções; e gerenciar transações e bloqueios.”
  4. Exame 70-465 – Designing Database Solutions for Microsoft SQL Server – “Este exame é destinado a profissionais de banco de dados que projetam e compilam soluções de banco de dados na organização. Eles são responsáveis pela criação de planos e projetos para estrutura, armazenamento, objetos e servidores de banco de dados. Eles criam o plano para o ambiente em que a solução de banco de dados é executada.”
  5. Exame 70-466 – Implementing Data Models and Reports with Microsoft SQL Server – “Este exame é destinado a desenvolvedores de Business Intelligence (BI) com foco na criação de soluções de BI que exigem implementar modelos de dados multidimensionais, implementar e manter cubos OLAP e criar exibições de informações usadas na tomada de decisão de negócios.”
  6. Exame 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server – “Este exame é destinado a arquitetos de Business Intelligence (BI), que são responsáveis pelo projeto geral de uma infraestrutura de BI e pelo modo como esta se relaciona com outros sistemas de dados em uso.”

    Impressões sobre as provas (MCSE SQL 2016)

Para adquirir a certificação de MCSE em SQL Server 2016 realizei primeiramente o Exame 70-466 e em seguida o exame 70-767 ( não que esse fosse necessário mas decidi o fazer pois os dois exames eram necessários para a carreira de MCSE de Business Intelligence).

  • Exame 70-466 – Implementing Data Models and Reports with Microsoft SQL Server – Esse exame é muito parecido com a segunda prova necessária para o MCSA em BI do SQL Server 2016 (Exame 70-768). A diferença entre eles é que, neste, conhecimentos em Reporting Services são necessários. Dentre eles: saber que componentes usar dependendo do tipo de análise solicitada, saber quais permissões usar exatamente dependendo da necessidade, como realizar migrações de ambiente em situações de disaster/recovery, dentre outros.
  • Exame 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server – Esse exame foi o MAIS DIFÍCIL que já realizei. Ele não exige simplesmente que saiba-se construir, desde modelos Dimensionais (Kimball x Immon) em SQL Server, Projetos de Integração de Dados com Integration Services, Projetos de Cubos Tabulares e/ou Multidimensionais e projetos de Reporting Services em modo Nativo e/ou integrados com sharepoint, mas também o que é MELHOR dentre as várias opções disponibilizadas. Outros assuntos abordados também são cubos dentro do Power Pivot (e como importá-los para um modelo Tabular dentro do SSAS) e análises dentro do Power View (Ainda não encontramos aqui Mobile Report e Power BI).

Como se preparar – Material para Estudo

Essa área do post se dedica às pessoas que querem aprender o conteúdo necessário para se tornarem Microsoft Certified Solutions Associate / Solutions Expert em SQL Server 2016. Abaixo seguem os livros e materiais que utilizei para conseguir ser bem sucedido em todos esses exames os quais listei.

  • Para os exames relacionados a desenvolvimento de SQL Server (70-461, 70-761, 70-762 e 70-464) aconselho o estudo APROFUNDADO do livro Querying Microsoft® SQL Server® 2012 Exam 70-461 Training Kit.
  • Para os exames relacionados a administração de ambiente em SQL Server (70-462, 70-764 e 70-465) aconselho também o estudo aprofundado dos livros Training Kit Exam 70-462: Administering Microsoft® SQL Sever® 2012 DatabasesProfessional Microsoft SQL Server 2014 Administration. Em se tratando de alta disponibilidade com AlwaysOn FCI aconselho o livro SQL Server 2014 – Alta Disponibilidade na Prática Com Alwayson Failover Cluster Instances e para AlwaysOn AG a documentação no link.
  • Para estudos relacionados a AZURE (70-765 e 70-473) aconselho o estudo através das documentações (Não há muito conteúdo em livros ainda sobre o assunto).
  • Para os exames relacionados à Business Intelligence (70-463, 70-767, 70-768, 70-466 e 70-767) não existe melhor fonte, na minha opinião, que estudar profundamente os livros: The Data Warehouse Toolkit (Para modelagem), Professional Microsoft SQL Server 2014 Integration Services (Para Integração de Dados), Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX (Envolve todo o conteúdo OLAP da plataforma do SQL Server + dicas avançadas de performance e troubleshoot + conteúdo para desenvolvimento de códigos em Multidimensional Expression [MDX] e Data Analysis Expression [DAX]) e o Professional Microsoft SQL Server 2016 Reporting Services and Mobile Reports (Todo o conteúdo de Reporting Services com o Adicional do Mobile Report [Ainda não cobrado para os exames]).

    Espero poder conseguir ajudá-los e desejo que todos tenham sucesso nos exames.

Anúncios

Facebook Page – Data’s Light

Pessoal, venho através deste post comunicar a vocês a criação da minha página no Facebook.

Nela pretendo manter informações, posts e vídeos sobre eventos, novidades e novos posts sobre conhecimentos técnicos.

Peço a vocês que deem o Like e Sigam para que assim consigamos continuar aprendendo e trocando informações juntos.

Grande abraço!

Atenciosamente,

Arthur Luz | Data’s Light.

Usando Expressions dentro do Foreach Loop Container para Captura de Dados de arquivos específicos em um diretório

Na semana passada alguns alunos de um curso de Integration Services que ministrei me trouxeram um questionamento bem interessante sobre um problema com relação à criação de processos de ETL.

Eles trabalham na Caixa Econômica Federal e lá possuem uma situação onde necessitam extrair de um diretório de rede – onde vários arquivos são armazenados de vários processamentos distintos – dados de apenas um arquivo relacionado ao dia útil anterior de um processo específico.

Achei o caso bem interessante e através desse post vou demonstrar como resolver a issue.


Criei uma série de arquivos de para simular o ambiente dos alunos. Nesse diretório teremos dois tipos de arquivos distintos pelo nome que representam dois processos diferentes. são eles [NPD010253.DOC2794.031016007.ARC6576] e [JJX010253.DOC2794.031016007.ARC1076]. Os arquivos são formados pela sigla do processo e uma numeração específica do processo [NPD010253] + o número do documento [DOC2794] + a data dos dados acompanhada de um outro código [031016007] + um outro código e numeração [ARC6576] (toda a nomeclatura do arquivos é meramente demonstrativa).

02

Entendido isso, a problemática é a seguinte: é necessário que desse diretório onde se encontram arquivos de todos os dias úteis do mês de Outubro para ambos os processos (processo [NPD] e processo [JJX]) sejam extraídos somente os dados do arquivo do processo [NPD] porém somente os dados do último dia útil do mês (Lembrando que esse processo hipotético somente será executado de segunda a sexta feira).


Use o Script abaixo para criação do banco de dados de teste que será usado no artigo e acesse o Link para realizar o download do pacote e arquivos usados na demonstração. Para que não ocorram erros, ponham a pasta de arquivos baixados dentro do diretório C:\.

--------------------------------------------
---------- CRIA AMBIENTE DE TESTE ----------
--------------------------------------------
USE master
GO

CREATE DATABASE test_SSIS
GO 

CREATE TABLE tbCliente (
 CodCliente INT,
 NomCLiente VARCHAR(255),
 TipoCliente VARCHAR(55),
 EmailCliente VARCHAR(255),
 TipoTelefone VARCHAR(55),
 TelefoneCliente VARCHAR(55),
 TipoEndereco VARCHAR (55),
 EnderecoCliente VARCHAR (255),
 CodigoPostal VARCHAR (50),
 DatCriacaoRegistro DATETIME
 )

o Pacote nada mais é do que um fluxo de ETL para carga dinâmica de vários arquivos distintos usando o Foreach Loop Container e a parametrização da ConnectionString da conexão com o Flat File Source usando a variável que recebe o nome do arquivos dentro do loop. 

Se ao ler este artigo você ainda não estiver familiarizado com o uso do Foreach Loop Container para carga em loop de vários arquivos em um mesmo destino podem me mandar email que explico como funciona pontualmente já que explicar essa configuração não é a ideia do artigo.

01

A segunda etapa de configuração que será demonstrada abaixo ainda não existe no pacote que está disponível para download. Isto é proposital para que você possa seguir o passo-a-passo de configuração do post.


Para que o ETL carregue apenas os dados do arquivo do último dia útil do mês (sempre o dia útil anterior ao dia atual) e apenas de um dos processos disponíveis no diretório é necessário que, dentro do Foreach Loop Container haja uma parametrização da opção Files: dentro da Aba de configuração Collection.

Para isso, será necessário que você utilize uma Expression. Essa expressão será mapeada no campo Expressions que fica abaixo da seleção de Enumerador do foreach [Enumerator].

03

Dentro da janela de propriedades você irá selecionar em Property a opção FileSpec, na opção Expression, você irá preencher com a expressão abaixo (é possível também, dentro de Property, usar a opção Directory para mapear o diretório do arquivo dinamicamente):

"NPD*" + (
 LEN((DT_WSTR, 2) DAY((DT_DATE) "2016-10-24")) == 1 ? 
 "0" + ((DT_WSTR, 2)(
 DATEPART("DW",(DT_DATE)"2016-10-24") == 2 ? 
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24") -3) :
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24"))
 )
 ) : (
 (DT_WSTR, 2)(DATEPART("DW",(DT_DATE)"2016-10-24") == 2 ? 
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24") -3) :
 (DT_WSTR, 2)(DAY((DT_DATE)"2016-10-24"))
 )
 )
+ (LEN((DT_WSTR, 2) (MONTH((DT_DATE) "2016-10-24"))) == 1 ? 
 "0" + (DT_WSTR, 2) (MONTH((DT_DATE) "2016-10-24")) : 
 (DT_WSTR, 2) (MONTH((DT_DATE) "2016-10-24"))) 
 + (RIGHT((DT_WSTR, 4) (YEAR((DT_DATE) "2016-10-24")),2)) 
 )+ "*.*"

05

Essa expressão irá criar a busca dinâmica pelo nome do arquivo que possua o identificador do processo [NPD] e que tenha data igual ao dia de hoje – 1 (último dia útil).

08

Obs – A expressão acima usa a data ’24-10-2016′ de maneira não dinâmica. Isso está dessa maneira porque o último arquivo do diretório possui a data ’21-10-2016′. Para que em ambiente de produção isso ocorra dinamicamente substitua a data ’24-10-2016′ pela Function GETDATE() conforme a Expression abaixo:

"NPD*" + (
 LEN((DT_WSTR, 2) (DAY(GETDATE()))) == 1 ? 
 "0" + ((DT_WSTR, 2)(
 DATEPART("DW",(GETDATE())) == 2 ? 
 (DT_WSTR, 2)((DAY(GETDATE())) -3) :
 (DT_WSTR, 2)((DAY(GETDATE())))
 )
 ) : (
 (DT_WSTR, 2)(DATEPART("DW",(GETDATE())) == 2 ? 
 (DT_WSTR, 2)(((DAY(GETDATE()))) -3) :
 (DT_WSTR, 2)((DAY(GETDATE())))
 )
 )
+ (LEN((DT_WSTR, 2) (MONTH(GETDATE()))) == 1 ? 
 "0" + (DT_WSTR, 2) (MONTH(GETDATE())) : 
 (DT_WSTR, 2) (MONTH(GETDATE()))) 
 + (RIGHT((DT_WSTR, 4) (YEAR(GETDATE())),2)) 
 )+ "*.*"

Agora, ao executarmos o pacote com o breakpoint habilitado para cada interação do loop poderemos observar que a variável nomeArquivo que, dentro do Flat File Connection, irá se tornar a Connection String para o ETL irá está mapeando o arquivo de nome [NPD010253.DOC2794.211016008.ARC6590.txt] cujo processo corresponde ao processo mapeado na Expression e a data diz respeito à data anterior à data mapeada.

06

Se continuada a execução do pacote, não haverão mais interações de Loop e o pacote irá terminar o processo de carga.

Se você for até o Management Studio e executar a query abaixo, verá que a única data existente dentro da tabela é 2016-10-21.

SELECT DISTINCT CAST(DatCriacaoRegistro 
 AS DATE) AS DatCriacaoRegistro
FROM tbCliente

07


É claro que a Expression deverá ser adaptada para cada ambiente e cada nomeclatura de arquivos segundo as necessidades do processo de carga de ETL.

Espero poder ajudá-los!

Últimos Eventos e Participações – #SQLSat570 [São Paulo]

Pessoal, nas duas semanas anteriores tive a oportunidade de passar por alguns eventos como comunidade Microsoft e gostaria de compartilhar com vocês minhas impressões, Slides e Arquivos de testes dos conteúdos ministrados.

Primeiramente, agradeço de todo coração à coordenação da faculdade Projeção por me permitirem participar no dia 06/10 do início das comemorações do dia do profissional de TI no polo de Ceilândia.

foto03

Pude falar sobre o tema Por Detrás dos Relatórios de Business Intelligence para os alunos de 1º a 3º semestre das turmas de Sistema de Informação e Tecnologia em Análise e Desenvolvimento de Sistemas.

Foram 2 horas de palestra sobre o assunto onde pude despertar nos alunos a curiosidade sobre essa área tão amada por mim e tão requisitada pelo mercado de trabalho.

Falei de assuntos como Modelagem Dimensional focando nas principais diferença entre os modelos de Kimball x Inmon, principais conceitos relacionados à fatos e dimensões e também sobre as ferramentas da suite Microsoft que podem auxiliar na construção de um sistema de Business Intelligence.

foto02

O evento foi tema no site da Faculdade no seguinte link


Ainda no mesmo dia estive também no Centro Universitário IESB [Asa Sul] ministrando um minicurso de 3 horas para os alunos de TI da universidade.

Deixo meus sinceros agradecimentos também ao professor Gabriel pelo convite de poder ser um dois instrutores dos minicursos. 

foto03

Falei exatamente sobre o mesmo tema usado na palestra da manhã [Por Detrás dos Relatórios de Business Intelligence], porém, pude interagir um pouco mais com os ouvintes trabalhando com eles a construção de um pequeno projeto de ETL para carga de dados de um mini Data Warehouse.

Para os alunos [Projeção ou IESB] que quiserem realizar o download da apresentação usada, basta clicar na imagem abaixo. 

capture

Já para os alunos que participaram do minicurso da IESB para realizar o download do material prático, basta clicar no seguinte link


No Sábado, dia 08/10, tive mais uma vez o imenso prazer de participar de mais um edição do SQL Saturday em São Paulo [#SQLSat570].

Dessa vez falei a cerca de alguns conceitos relacionados ao mundo de Business Intelligence (BI) e Master Data Management (MDM) e também sobre uma ferramenta pouco utilizada da suite do Microsoft SQL Server usada para qualidade de dados – o Data Quality Services (DQS)

img_7908

O evento foi fantástico com mais de 1150 inscritos e mais de 500 participantes presenciais dos quais mais de 400 permaneceram até o final do evento.

Agradeço de maneira gigantesca ao Diego Nogare pela possibilidade de poder continuar passando pra frente informações sobre tudo isso que tanto amo e também o parabenizo pela grandiosidade e organização do evento.

A quem diga que esse evento do PASS aqui no Brasil tenha sido o maior em número de palestrantes. foram mais de 50 palestrantes em mais de 40 sessões

img_8006

Para quem tiver interesse, segue abaixo o conteúdo ministrado na sessão de São Paulo sobre o DQS.

Como fora dito, quem tiver interesse em conhecer mais sobre a ferramenta, existe uma série com 8 artigos no blog do Luan Moreno no seguinte Link. Ele é uma referência no assunto.

Apesar de ainda não ter nada escrito sobre a ferramenta no meu blog me ponho inteiramente a disposição para quaisquer dúvidas.

capture01

Seguem abaixo algumas outras fotos sobre o evento: 

img_7852 img_7864 img_7880 img_7892 img_7936132

img_7981 img_7982 img_7986 img_7994 img_7995 img_8004 img_8011 img_8013 img_8014 img_8018 img_8024

CDC – Parte 06

Nesta etapa da série será possível aprender acerca das melhores práticas e pontos importantes para habilitação e manutenção do Change Data Capture em ambientes de Produção.


  1. Uma ótima prática de utilização do Change Data Capture é evitar habilitar a feature em colunas de uma tabela cujo não existe importância em armazenar o histórico de seus dados. Tabelas com grandes quantidades de colunas tendem a aumentar de maneira significativa a latência nas capturas das alterações;
  2. Somente habilite o suporte a Net Changes caso realmente seja necessário o seu uso. O trabalho que o CDC executa por detrás para realizar o controle dessas mudanças também prejudica a performance na utilização da feature;
  3. O Job de Cleanup dos dados históricos não possui um desempenho favorável quando executado em paralelo com o workload. Se possível, o job de Cleanup dos dados de alteração deve ser programado para horários com baixa ou nenhuma carga de trabalho;
  4. Uma das coisas mais importantes a que deve-se ter atenção no que diz respeito à captura de dados de alteração é o subsistema de log de transações. O arquivo de Log cresce significativamente quando o Change Data Capture é habilitado em um banco de dados. Além disso, registros de log permanecem ativos até o CDC processá-los. Isto significa que, especialmente em ambientes onde uma grande latência se acumula, o arquivo de Log pode crescer significativamente, porque o espaço de log não pode ser reutilizado até que o CDC execute o trabalho de carga dos dados ainda processados dentro do Log, mesmo no Recovery Mode Simple, ou mesmo depois de um backup de log no Recovery Mode Full;
  5. O Change Data Capture funciona em todos os Recovery Modes. Mas quando o CDC é habilitado, as operações que normalmente seriam minimamente logadas nos modos de recuperação Simple ou Bulk-logged são totalmente logadas para permitir ao CDC a capturar de todas as alterações;
  6. Esteja ciente que quando o disco de Log ficar cheio, você não poderá reduzí-lo fazendo um backup de log manualmente ou mesmo realizando um Shrink no arquivo até que o CDC tenha processado todas as transações;
  7. O Change Data Capture não pode processar as transações quando o disco de Log está cheio, porque a captura de dados de alteração escreve no arquivo de Log o que é realizado nas tabelas de histórico. Neste caso, a maneira mais fácil de resolver essa situação é adicionar temporariamente outro arquivo de Log em um disco diferente e deletá-lo após o CDC concluir a carga das tabelas de histórico;
  8. Planeje o crescimento do arquivo de Log. Esteja ciente que o Log File pode crescer de 200-300% a partir do tamanho original quando o Change Data Capture está habilitado na base de dados;
  9. Uma boa prática para manter o filegroup PRIMARY pequeno e para que possa-se ter uma clara distinção do que são dados transacionais e o que são dados históricos, é habilitar o CDC em um filegroup diferente;

No próximo post da série você começará a aprender sobre os componentes do MS Integration Services que são utilizados para trabalhar com o Change Data Capture, utilizando dessa maneira os dados históricos como fonte para cargas de Data Warehouses (DWs) e Operational Data Stores (ODSs).

Referência para o post – Tuning the Performance of Change Data Capture in SQL Server 2008.

CDC – Parte 05

Nesta etapa da série será possível entender o funcionamento das TVFs (Table Valued Function) denominadas [cdc].[fn_cdc_get_all_changes_…][cdc].[fn_cdc_get_net_changes_…] e também da IF (Inline Table-Valued Function) denominada [sys].[fn_cdc_map_time_to_lsn] usada para transformar o conteúdo de data em LSN (Log Serial Number). Essas funções são usadas para manipulação dos dados contidos nas tabelas de CDC.

Um outro ponto importante com relação ao entendimento dessas funções é que elas são usadas internamente pelo Integration Services dentro do componente de origem de dados do CDC. Ou seja, seus conceitos são um ponto muito importante mais tarde, quando estivermos gerando as cargas de ETL.


Como foi explanado na Parte 3 do conjunto de posts, o CDC cria dentro das tabelas de histórico algumas colunas onde, através delas, é possível realizar o controle de versão dos registros com base nos LSNs do Log de Transação.

Trabalhar com base no log é complexo, dessa forma, é possível que realizemos a conversão de LSN em data através da IF [sys].[fn_cdc_map_time_to_lsn]. Essa função captura a data desejada com base em algums parâmetros e transforma internamento esse range de período no LSN correto a ser interpretado pela função [cdc].[fn_cdc_get_all_changes_…] ou [cdc].[fn_cdc_get_net_changes_…].

Para trabalhar com a função [sys].[fn_cdc_map_time_to_lsn] é necessário entender acerca de sues parâmetros que são:

@relacional_operator – É usado para identificar um valor de LSN distinto dentro da tabela cdc.lsn_time_mapping com um tran_end_time associado que satisfaça a relação quando comparado ao valor @tracking_time (BOL). Dentro desse parâmetro é possível usar os seguintes valores:

‘smallest greater than’ ou ‘smallest greater than or equal’, onde:

smallest

‘largest less than’ ou ‘largest less than or equal’, onde:

largest

@tracking_time – É o valor de data e hora ao qual corresponder. tracking_time é datetime (BOL).


Agora que compreendida a Função [sys].[fn_cdc_map_time_to_lsn] você irá iniciar o entendimento dos parâmetros das funções [cdc].[fn_cdc_get_all_changes_…][cdc].[fn_cdc_get_net_changes_…], onde cada uma delas possui os seguintes parâmetros:

@from_lsn – LSN de início da transação;

@to_lsn – LSN de fim da transação;

@row_filter_option – Rege o conteúdo das colunas de metadados, bem como as linhas retornadas no conjunto de resultados.

Para trabalhar com ambas as functions para manipulação de registros é necessário usar o código abaixo. Lembrando que, conforme foi dito na Parte 02 do conjunto de posts,  o suporte a Net Changes somente é habilitado caso o parâmetro @supports_net_changes seja preenchido como um na hora de habilitar o CDC para tabela. Caso não, a função [cdc].[fn_cdc_get_net_changes_…] não será criada.


-- ===========================
-- checking get_all funtion --
-- ===========================

USE AdventureWorks2014
GO

-- function all change - Used for capture all changes in tables with cdc excluding the last value in update modification
DECLARE @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'smallest greater than', @begin_time);
-- the parameter <relational_operator> may have two values: SMALLEST GREATER THAN OR EQUAL or SMALLEST GREATER THAN

SET @to_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'largest less than or equal', @end_time);
-- the parameter <relational_operator> may have two values: LARGEST LESS THAN OR EQUAL or LARGEST LESS THAN

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, /*<row_filter_options>*/'all update old');
-- the parameter <row_filter_options> may have three values: ALL and ALL UPDATE OLD

-- ===========================
-- checking get_net funtion --
-- ===========================

USE AdventureWorks2014
GO
-- function net change - Used for capture the last value of the data, dispensing information of the update and delete statements
DECLARE @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10);

SET @begin_time = GETDATE() -1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'smallest greater than', @begin_time);
-- the parameter <relational_operator> may have two values: SMALLEST GREATER THAN OR EQUAL or SMALLEST GREATER THAN

SET @to_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'largest less than or equal', @end_time);
-- the parameter <relational_operator> may have two values: LARGEST LESS THAN OR EQUAL or LARGEST LESS THAN

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, /*<row_filter_options>*/'all with mask');
-- the parameter <row_filter_options> may have three values: ALL, ALL WITH MASK (return the final LSN of the row) and ALL WITH MERGE

Primeiramente execute os comandos abaixo que irão Inserir e Deletar alguns registros na tabela Person.Person e, também alguns Updates nos seus dados:

USE AdventureWorks2014
GO

-- ====================================
-- ----- insert operation test --------
-- ====================================

INSERT INTO [Person].[BusinessEntity]
 ([rowguid]
 ,[ModifiedDate])
VALUES ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50011',GETDATE()), 
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50081',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50082',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50083',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50084',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50085',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50086',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50090',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50088',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50089',GETDATE())
GO

INSERT INTO [Person].[Person]
 ([BusinessEntityID]
 ,[PersonType]
 ,[NameStyle]
 ,[Title]
 ,[FirstName]
 ,[MiddleName]
 ,[LastName]
 ,[Suffix]
 ,[EmailPromotion]
 ,[AdditionalContactInfo]
 ,[Demographics]
 ,[rowguid]
 ,[ModifiedDate])
VALUES (20778,'IN',0,NULL,'Arthur','J','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50011',GETDATE()),
 (20779,'IN',0,NULL,'Heitor','D','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50081',GETDATE()),
 (20780,'IN',0,NULL,'Ulisses','L','Silva Neto',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50082',GETDATE()),
 (20781,'IN',0,NULL,'Raquel','F','Nascimento',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50083',GETDATE()),
 (20782,'IN',0,NULL,'Miguel','C','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50084',GETDATE()),
 (20783,'IN',0,NULL,'Rafael','M','Guedes',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50085',GETDATE()),
 (20784,'IN',0,NULL,'Marcelo','F','Moreira',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50086',GETDATE()),
 (20785,'IN',0,NULL,'Ana','L','Dos Santos',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50090',GETDATE()),
 (20786,'IN',0,NULL,'Leticia','B','Mendes',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50088',GETDATE()),
 (20787,'IN',0,NULL,'Lucas','A','Cruz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50089',GETDATE())
GO

-- ====================================
-- ----- delete operation test --------
-- ====================================

DELETE FROM [Person].[Person]
WHERE [BusinessEntityID] IN (
 20785,
 20786,
 20787
)
GO

-- ====================================
-- ----- update operation test --------
-- ====================================

UPDATE [Person].[Person] 
SET [FirstName] = 'William',
 [LastName] = 'Golçalves',
 [MiddleName] = 'D'
WHERE [BusinessEntityID] = 20783
GO

UPDATE [Person].[Person] 
SET [Title] = 'Sr.'
WHERE [BusinessEntityID] = 20778
GO

UPDATE [Person].[Person] 
SET [PersonType] = 'SC'
WHERE [BusinessEntityID] = 20782
GO

UPDATE [Person].[Person]
SET [PersonType] = 'SC',
    [Title] = 'Sr.'
WHERE [BusinessEntityID] IN (1,2,3,4)
GO

Para facilitar o entendimento, para cada uma das opções de @row_filter_option de cada uma das TVFs, você usará um dos códigos abaixo (partindo do pressuposto de que o ambiente já foi criado seguindo os passos anteriores do conjunto de posts):

[fn_cdc_get_all_changes_…] – Value ALL

Usado para retornar todos registros da tabela de histórico do CDC eliminando os registros que possuem o valor 3 no campo [__$operation] pois estes registros são os valores anteriores ao valor atual do update (vide Parte 3 do conjunto de posts).

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, 'all'); 
GO

01

[fn_cdc_get_all_changes_…] – Value ALL UPDATE OLD

Usado para retornar todos registros da tabela de histórico do CDC incluindo os registros que possuem o valor 3 no campo [__$operation].

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all update old
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, 'all update old'); 
GO

02


[fn_cdc_get_net_changes_…] – Value ALL

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico. Por exemplo, caso um registro tenha sido inserido, sofrido um update e depois deletado, a query abaixo irá mostrar apenas o registro deletado.

A coluna [__$update_mask] aparece com o valor NULL.

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all'); 
GO

03

[fn_cdc_get_net_changes_…] – Value ALL WITH MASK

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico assim como na situação anterior, porém, nesse caso, quando a coluna [__$operation] retornar o valor 4 (novo valor de um update), temos a coluna [__$update_mask] preenchida com a máscara da ultima atualização.

Você entenderá melhor sobre a utilização dessa coluna mais abaixo neste post.

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all with mask
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all with mask'); 
GO

04

[fn_cdc_get_net_changes_…] – Value ALL WITH MERGE

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico, porém realiza um merge entre os registros Inseridos e Atualizados mostrando, neste caso, para ambas as situações, a coluna [__$operation] com valor igual a 5. O valor igual a 1 é usado pra deleção.

USE AdventureWorks2014
GO

--delete registry existing before cdc initialization
ALTER TABLE [Person].[EmailAddress]
DROP CONSTRAINT [FK_EmailAddress_Person_BusinessEntityID]
GO

ALTER TABLE [HumanResources].[Employee]
DROP CONSTRAINT [FK_Employee_Person_BusinessEntityID]
GO

ALTER TABLE [Person].[Password]
DROP CONSTRAINT [FK_Password_Person_BusinessEntityID]
GO

ALTER TABLE [Person].[PersonPhone]
DROP CONSTRAINT [FK_PersonPhone_Person_BusinessEntityID]
GO

DELETE FROM [Person].[Person]
WHERE [BusinessEntityID] IN (10,11,12)
GO
-- get_all_changes with row filter options equal all with merge
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all with merge'); 
GO

Capture

Observer que para os registros de [BusinessEntityID] igual a 20785, 20786 e 20787 da tabela [Person].[Person] que foram inseridos hoje e também deletados hoje, ao realizar a operação de merge de acordo com o Range de data que foi passado (de ontem para hoje), o CDC desconsidera a operação de INSERT e nem o apresenta no retorno do SELECT. É como se esse registro não houvesse sido inserido.

Porém, para os registros 10, 11 e 12 que já faziam parte da tabela, ele mostra no select para a coluna [__$operation] o valor 1.


A coluna [__$update_mask] é responsável por “armazena uma máscara de bits com base nos ordinais de coluna da tabela de alteração que identificam as colunas que foram alteradas” (BOL).

Em outras palavras, através dela é possível descobrir quais colunas foram alteradas dentro de uma instrução de UPDATE. Caso a instrução seja de DELETE ou INSERT todas as colunas serão apresentadas.

No código será possível verificar quais registros, de acordo com o range de data fornecido, sofreram alterações de UPDATE ([__$operation] = 4) na coluna [Title] da tabela [Person].[Person]:

-- =======================================
-- understand the column __$update_mask --
-- =======================================

DECLARE @StartDate datetime = GETDATE()-1;
DECLARE @EndDate datetime = GETDATE();
DECLARE @begin_lsn BINARY(10); 
DECLARE @end_lsn BINARY(10);

-- Set the LSN values
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @StartDate);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @EndDate);

-- get all updated rows where Title was updated
SELECT *
FROM cdc.fn_cdc_get_all_changes_Person_Person(@begin_lsn, @end_lsn, 'all')
WHERE __$operation = 4 
 AND sys.fn_cdc_is_bit_set (
 sys.fn_cdc_get_column_ordinal ('Person_Person','Title'),__$update_mask
 ) = 1
GO

Capture2

Já usando o código abaixo será possivel identificar quais colunas foram alteradas para cada um dos registros da tabela de histórico do CDC capturando somente aqueles que possuem valor de [__$operation] = 4 (todos os valores de UPDATE).

-- =======================================
-- understand the column __$update_mask --
-- =======================================

-- capture all columns changed in each transaction
SELECT ( SELECT CC.column_name + ','
 FROM cdc.captured_columns CC
 INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
 WHERE capture_instance = 'Person_Person'
 AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
 PD.__$update_mask) = 1
 FOR
 XML PATH('')
 ) AS ChangedColumns,
 *
FROM cdc.Person_Person_CT PD
WHERE PD.__$operation = 4
GO

Capture3


No proximo post da série será possível aprender sobre as melhores práticas e observações para a utilização do Change Data Capture. Após isso, iniciaremos a etapa de construção de ETLs para consumo da feature em cargas de dados de Data Warehouse (DWs) e/ou Operational Data Stores (ODSs).

CDC – Parte 04

Neste post da série você aprenderá sobre a procedure de sistema  sys.sp_cdc_change_job e seus parâmetros usada para alterar os jobs de captura e limpeza dos dados nas tabelas de histórico do Change Data Capture.


A procedure sys.sp_cdc_change_job é usada para configurar os jobs [_capture] [_cleanup] que são responsáveis pelas rotinas de captura e cleanup das tabelas de CDC. essa sp possui alguns parâmetros. São eles:

@job_type – Identifica qual o job que será alterado/configurado. Parâmetro obrigatório;


Para o Job de Captura de dados existem os seguintes parâmetros de configuração:

@maxtrans – Fornece o número máximo de transações a processar em cada ciclo de scan. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é de 500 transações por ciclo;

@maxscans – Fornece o número máximo de ciclos de scan a serem executados para captura de todos os registros do log de transação. Esse parâmetro só pode ser utilizado para o job de captura O valor inicial é de 10 ciclos de scan;

@pollinginterval Quantidade de segundos entre cada ciclo de scan. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é de 5 segundos de espera entre cada ciclo;

@continuous – Indica se o job de captura de dados do Log terá execução continua ou não.

Quando definido como 1, o job examina o log e processa até (max_trans * max_scans) transações. Em seguida, aguarda o número de segundos especificados no polling_interval antes de começar um novo ciclo de verificações.

Se definido como 0, a execução do job ocorrerá (max_trans * max_scans) e depois sairá do job. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é 1;

A arquitetura do job de captura funciona conforme figura abaixo:

Capturar

É importante ao utilizar o Change Data Capture que você faça uma verificação, tanto de Hardware quanto de Volumetria de dados de alteração para poder otimizar o seu ambiente. A latência de captura das alterações e a quantidade de transações capturadas por segundo são dois pontos de suma importância a serem observados.

Sobre isso, deixo como abaixo um dos exemplos realizados Steffen Krause em um post escrito para o TechNet em 2008 onde uma comparação é realizada entre dois tipos de configuração do job de captura.

Imagem1
Padrão – @continuous = 1, @maxscans = 10, @maxtrans = 500 e @pollinginterval = 5
Imagem2
Modificado – @continuous = 1, @maxscans = 100, @maxtrans = 5000 e @pollinginterval = 5
Imagem3
Outras possibilidades de configuração e observações realizadas nos testes.

Para maiores informações sobre configurações avançadas do Change Data Capture, leia o post Tuning the Performance of Change Data Capture in SQL Server 2008 do Steffen Krause na íntegra. 

As informações demonstradas acima estão relacionadas ao hardware usado no artigo.


Para o Job de Cleanup existem os seguintes parâmetros de configuração:

@retention Número em minutos de armazenamento dos dados de alteração nas tabelas de histórico do CDC antes que a limpeza desses dados ocorra. Esse parâmetro só pode ser utilizado para o job de Cleanup. O valor inicial é 4320 minutos (3 dias);

@threshold Número máximo de exclusões que podem ser efetuadas para cada operação de cleanup de dados. Esse parâmetro só pode ser utilizado para o job de Cleanup. O valor inicial é 5000 deleções;


Para consultar as configurações atuais dos Jobs [_capture] e de [_cleanup] de dados use a tabela msdb.dbo.cdc_jobs.

Abaixo um exemplo de execução da procedure sys.sp_cdc_change_job:


-- ===================================
-- changes in the CDC configuration --
-- ===================================
USE AdventureWorks2014
GO

-- Used for to verify the current configuration inside the jobs
SELECT *
FROM msdb.dbo.cdc_jobs

/* Case the change data capture is enabled in database 
 with transactional replication, the jobs of the replication are used */

-- Jobs
EXEC sys.sp_cdc_change_job @job_type = 'capture'
 ,@maxtrans = 5000 -- number of transactions read between each cicle
 ,@maxscans = 10 -- number of cycles between each polling interval 
 ,@continuous = 1 -- enable the continuous execution of the work of the capture 
 ,@pollinginterval = 1 -- Interval in seconds between each cycle of the log read

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'
 ,@retention = 10080 -- time in minutes of retention of the dates in cdc tables (default is 3 days)

No próximo post da série você aprenderá sobre as functions [_get_all_changes] e [_get_net_changes] para manipular os dados salvos dentro das tabelas de histórico do Change Data Capture.