6 Fevereiro 2018

Package Deployment Model vs Project, diferenças a nível de Logging

Introdução - Sysssislog (tradicional) VS SSIS Catalog

Esta investigação tem como propósito comparar 2 modelos de log distintos. O primeiro framework faz parte da framework BI4ALL e usamos com o package deployment model. O segundo modelo é usado com o project deployment model e é o que vamos analisar no contexto deste artigo.

Posto isto, a nossa equipa decidiu investigar as potencialidades das Views de SSIS Catalog e verificar, face ao sysssislog vigente, qual dos dois é a melhor abordagem, ou até se são complementares.

Para chegar a essa conclusão a equipa do Cliente delegou-nos essa tarefa, e como tal, cabe-nos a nós fazer essa análise para saber qual das duas metedologias de logging é a melhor.

Sysssislog:

No tradicional Sysssislog temos informação sobre o evento, o computador que correu o package, o objeto que correu naquele momento, quem correu o package, hora de início e hora de fim da tarefa e mensagens de erro (ver na imagem mais abaixo a estrutura da tabela de sysssislog).

Limitações desta visão:
- Não conseguimos ver quantas linhas passaram de uma tabela para a outra diretamente. Para conseguir obter essa informação temos que ativar mais eventos.
- Não conseguimos saber quais são os packages que estão a consumir mais memória do servidor.
- Mais difícil de chegar ao nome do package face ao SISS Catalog.
- Mais difícil de saber se o package correu com sucesso ou não do que no SISS Catalog.
- Visão mais confusa face às views dos SISS Catalog.

SYSSSISLOG:

 

O modelo Relacional das tabelas do SISS Catalog:

 

Como podemos verificar pela imagem acima, existem muitas views de SSIS Catalog e as ligações entre si são um pouco complexas. O nosso objetivo é tentar explicar de forma sucinta, como essas ligações funcionam e, destas tabelas, quais têm informação útil para este artigo.

Querys às tabelas de SISSDB (Catalog tables):

 

Fazendo um select * a todas as views com o schema Catalog na SISSDB (base de dados que armazena informação em forma de views com o schema Catalog), podemos analisar qual é a informação que está disponível e dessa informação disponível quais são as tabelas relevantes para a nossa investigação.

Descobrindo as tabelas mais relevantes passamos a descobrir quais são os campos das mesmas tabelas que são relevantes para o objetivo deste estudo.

Views OnError:

Neste projeto, decidimos criar views para conseguir ver mais rapidamente a informação carregada.

O objetivo da view (OnError) é ver a informação de todos os packages que não correram como era suposto. Neste caso de erro, ao correr esta view nós conseguimos obter a informação de qual foi a execução, qual o nome do package, qual a hora a que esse package correu e qual a mensagem de erro que deu.

 

Através desta query, conseguimos criar uma view que dá os resultados quando os packages dão erro. Só aparecem resultados de erro pois foi aplicado o filtro de message_type = 120 (que é o código de onerror eventos).

 

Aqui podemos ver o output da View OnError.

View OnWarning:

Criámos esta view com o objetivo de ver os avisos que não fazem o package parar a sua execução, mas que poderão ter influência no futuro. São pequenas coisas que devem ser corrigidas.

Esta view terá a mesma estrutura que a View OnError mas com a diferença do tipo de mensagem que em vez de ser o 120, neste caso é o 110.

 

Tal como referido, a única diferença entre as duas view é apenas o tipo de mensagem.

 

Como podemos observar, os outputs dados entre as duas views são muito idênticos. A única diferença é o conteúdo da mensagem.

View PostExecute com (verbose):

Depois de termos uma visão sobre os packages que deram erro ou que estão em sobre aviso, decidimos criar views para os packages que correram com sucesso.

Esta view é ligeiramente diferente das anteriores, porque usámos uma tabela nova “Catalog.execution_data_statistics”. Através desta tabela, conseguimos obter o número de linhas que foram transferidas para esses packages. No entanto, existe um trade-off. Esta tabela é especial pois só tem dados quando executamos o package em modo verbose. Também conseguimos calcular a data de início e a data de fim do package. Com essas informações conseguimos calcular o tempo que o mesmo demorou a correr quer em minutos quer em segundos.

 

Como podemos analisar na imagem, a query acima é ligeiramente diferente das anteriores.

 

Também o output é ligeiramente diferente dos anteriores. Podemos ver o número de linhas passado em cada execução de package com sucesso. Podemos ver o tempo que demorou a correr, a que horas começou a correr e quando terminou de correr.

Verbose:

Numa primeira visão, pensávamos que não conseguíamos obter o número de linhas que passavam da Source para a Destination, portanto, após alguma investigação encontrámos o verbose que é o nível que dá o número de linhas que passam desde a Source até à Destination.

Existem vários modos de execução de um package num servidor:

http://www.mattmasson.com/2011/12/what-events-are-included-in-the-ssis-catalog-log-levels/.

Temos o basic, que é o mais utilizado, e também o verbose. Para conseguirmos saber o número de linhas transferido em cada package temos que usar o verbose. Conseguirmos ainda obter dados na tabela“Catalog.execution_data_statistics”.

 

Para verificar se existia uma diferença substancial entre o verbose e o basic, decidimos correr vários packages em modo basic e em modo verbose. Verificámos que não existem grandes diferenças de tempos, apesar do verbose ser sempre mais lento pois oferece-nos mais informação que o modo basic.

No entanto, isto acarreta alguns problemas. Fazendo uma breve análise à tabela “Catalog.execution_data_statistics”, podemos reparar que através dos prints executados, apenas 7 vezes, obtivemos cerca de 28000 registos (ver na imagem abaixo). O crescimento desta tabela é preocupante pois só corremos 7 packages e o nosso projeto no Cliente tem mais de 300 packages. Concluímos que devido à elevada volumetria de dados, o verbose deixou de ser uma opção viável.

 

 

Modo Customer:

Para colmatar as lacunas do modo Verbose, optámos por investigar um pouco mais e descobrir o modo Customer.

O Modo Customer é um modo como o próprio nome indica, customizável, ou seja, podemos escolher os eventos que têm a informação pretendida sem estarmos limitados aos modos default da Microsoft.

 

No entanto, esta funcionalidade só está disponível no SQL Server 2016, e no Cliente apenas temos a versão 2014, logo, não conseguimos usar este modo customizável.

Modo Basic:

Depois de uma investigação mais aprofundada, verificámos que o modo basic tinha um evento chamado “onInformation”, que é o mesmo evento que passa o número de linhas no Sysssislog. Assim, conseguimos corrigir o nosso problema.

 

View PostExecute sem (verbose):

Depois de descobrir o evento que tinha a informação sobre o número de linhas, tivemos de adaptar a view à informação que está disponível.

Face ao postExecute do verbose, conseguimos ter informação acerca de todos os packages que foram executados e não só os packages que foram executados em modo verbose. Esta view será muito mais rápida que a anterior pois vai fazer filtro a uma view com muito menos registos e com menor taxa de crescimento que a View anterior, ou seja, vamos ganhar um pouco mais de performance face à anterior maneira de modulação.

No entanto, temos um trade off. Vamos ter ruído na informação apresentada. Obtemos o número de linhas através da mensagem do evento, no entanto, essa mensagem tem mais informação que só o número de linhas.

View Status:

Esta view vai ser importante para sabermos o status do package corrido, pois as views de siss catalog só tem o código do status (número). Decidimos criar uma view de modo a obter o código “status” e o seu descritivo. A imagem mais acima (O modelo Relacional das tabelas do SISS Catalog) mostra o modelo relacional das views. Conseguimos saber qual o código e o seu descritivo. Assim, quem for ver a view de status e as views de execuções, consegue perceber melhor se um package correu ou não com sucesso.

 

Como podemos observar através da query acima, usámos uma maneira simples, mas eficaz para criar a tabela.

Abaixo listamos o resultado da query acima.

 

View Execution:

Esta view foi criada com o objetivo de conseguirmos analisar se os packages executaram como previsto ou se houve algum erro. Caso haja algum erro, podemos ir a view OnError para perceber melhor o porquê desse erro ter acontecido.

Em baixo, mostramos como criámos a view e o formato em que vai aparecer a informação dada pela View Execution.

 

 

 

View Memory:

Esta view foi criada com o objetivo de conseguirmos analisar quais são as execuções mais pesadas (que usam mais memória do servidor), o porquê dessas execuções serem pesadas, o que podemos fazer para melhorar essas execuções e também conseguirmos perceber as características do servidor onde estamos a executar os packages.

Em baixo mostramos a query que usámos para criar a view e o seu output.

 

 

 

Master package vs project deployment model:

A framework da BI4ALL é orientada para o package deployment model e, como tal, o master da sua framework também está orientado para esse modelo.

No entanto, no project deployment model, os packages em diferentes projetos não conseguem comunicar entre si. Deste modo, utilizando o master da framework BI4ALL, não conseguimos replicar, pois, na solução aplicada no Cliente, temos diferentes projetos. Conseguíamos replicar caso tivéssemos a solução da framework da BI4ALL. No entanto, só funciona com o project deployment model se estiver tudo dentro do mesmo projeto.

Para resolver essa situação, tivemos que criar o nosso master através de scripts task que têm tabelas configuradas que permitem configurar a ordem dos packages a correr e quantos packages a correr ao mesmo tempo. Caso um package dê erro, podemos continuar a execução.

 

 

 

Syssislog VS SISS Catalog (conclusões)

Após tudo o que foi mencionado, concluímos que o SISS Catalog é mais completo e “user friendly“ que o Sysssislog, assim, poderá ser a solução de auditoria a usar. No entanto, não nos podemos esquecer que os dados nas tabelas de SISS Catalog crescem mais depressa (em termos de volumetria) do que na Sysssislog.

Podemos contornar esse crescimento através de dois tipos de views, uma com histórico em que terá toda a informação carregada desde que o data warehouse começou a funcionar até ao dia atual e outra com informação do mês em vigor. Nesta segunda view será mais leve e rápido o acesso à informação da mesma.

Face ao projeto do Cliente, proponho usar este tipo de solução (SISS Catalog) com as duas views, uma com uma visão mensal e outra com uma visão de histórico.