Padrão do Catálogo SSISDB Melhores Práticas

Introdução

O banco de dados SSISDB (também conhecido como catálogo de Serviços de Integração) foi introduzido no SQL Server 2012 para desclutter o banco de dados MSDB e fornecer uma infra-estrutura interna de registro e relatórios. Em poucas palavras, o SSISDB é uma estrutura SSIS que torna os Serviços de Integração do SQL Server mais robustos e amigáveis para a empresa ao fornecer:

• Cópia de segurança de banco de dados
• Criptografia de banco de dados
• Suporte para ambientes
• Parâmetros de projetos e pacotes de suporte
• Pacote versioning
• Customer SSRS performance reports built into SSMS
• Deployment directly from SSDT

>

A estrutura SSISDB tornou SSIS muito mais capaz, veio com algumas suposições da Microsoft (a.k.a. padrões). Esses padrões estão lá para ajudá-lo a ficar de pé, mas podem não ser ideais quando você começa a correr, e estão longe de ser perfeitos quando você está correndo.

Este artigo aborda os padrões, porque esses padrões podem não ser ideais, e como alterar esses padrões.

Problema

SSISDB Catálogo vem com um processo embutido para limpar as operações e a versão do projeto. Este processo de limpeza depende dos padrões SSISDB que podem tornar seu SSIS inoperante se não for alterado.

Virtualmente cada novo projeto SSIS começa com a construção e execução de apenas alguns pacotes enquanto há uma abundância de espaço livre em disco. Avance rapidamente 3-6 meses e você tem um número de projetos SSIS, dezenas ou centenas de pacotes, e você também pode ter a necessidade de novos dados para relatórios e análises (leia: constantemente rodando pacotes 24 horas por dia). Esses pacotes SSIS podem acumular dados de versão (menos problema) e dados de registro (mais problema) muito além dos padrões de limpeza da Microsoft, o que pode levar ao tamanho do banco de dados SSISDB a balão e crescer em tamanho inesperadamente (leia: consumir todo o espaço em disco disponível).

O único padrão que é responsável por capturar dados relacionados ao tempo de execução do SSIS é o Nível de Registro Padrão do Servidor = Básico. Esta configuração é, em última análise, o que gerencia o tamanho do banco de dados SSISDB e, em última análise, ocupar todo o espaço em disco. Dito isto, o Catálogo SSIS (Fig. 1) fornece outras opções para controlar o tamanho do banco de dados SSISDB:

1. Nível padrão de registro em todo o servidor
2. Padrão de limpeza:
   1. Limpar Logs Periodicamente = Verdadeiro
   2. Retirar periodicamente Versões Antigas = Verdadeiro
   3. Período de Retenção (dias) = 365
   4. Número máximo de Versões por Projeto = 10

Apesar de mudar o Nível de Registro Padrão do Servidor de Básico para Nenhum para recuperar espaço em disco é altamente impraticável e tornará a solução de problemas um pesadelo, alterações em outras configurações do Catálogo SSISDB como Número Máximo de Versões por Projeto e Período de Retenção (dias) podem ter efeito mínimo na solução de problemas, enquanto mantém o banco de dados/disco SSISDB em controle.

Todas essas configurações padrão estão conduzindo o processo de limpeza (a.k.a. SSIS Server Maintenance Job). Enquanto ambos os Logs de Limpeza Periodicamente = True e Periodicamente Remover Versões Antigas = True são virtualmente sempre perfeitos, as configurações para ambos Número Máximo de Versões por Projeto = 10 e Período de Retenção (dias) = 365 podem ser superoptimistas. E aqui está porque.

A principal razão pela qual ambos os Logs Limpos Periodicamente = Verdadeiro e Periodicamente Remover Versões Antigas = Os verdadeiros padrões são perfeitos é que, sem esses padrões (ou efetivamente se ambos mudaram para Falso), o SSISDB crescerá indefinidamente, pois só acumulará dados de log e versionamento sem remover nenhum dado e ficará sem espaço em disco.

A principal razão pela qual tanto o Número Máximo de Versões por Projeto = 10 como a Retenção de dados de registro por 365 dias pode ser super otimista é a decisão da Microsoft de reter os dados com base em um período de tempo e não no tamanho. Isso significa que o número de período fixo não leva em conta a quantidade de dados que serão acumulados. Enquanto o Número Máximo de Versões por Projeto pode crescer e se tornar um problema quando você tiver milhares de pacotes, o Período de Retenção (dias) vai se tornar um enorme problema quando você tiver pacotes que estão rodando a cada poucos minutos. Eu não acho que a Microsoft nunca esperou o acúmulo de dados de log para 1440 rodando por dia (rodando a cada minuto) o que vai requerer uma limpeza mais freqüente do que a cada 365 dias ou vai ficar sem espaço em disco.

Solução

A solução envolveria alterar os padrões para números menores, então o SSISDB mantém menos informação. Baseado na minha experiência pessoal de rodar pacotes a cada minuto (requisito da plataforma de comércio eletrônico) e ter até 10 projetos, diminuindo o Período de Retenção (dias) de 365 (padrão) para 7 dias e o Número Máximo de Versões por Projeto de 10 (padrão) para 5 seria suficiente para controlar o espaço no SSISDB/disco enquanto mantém informações suficientes para fins de depuração e resolução de problemas.

Você tem duas opções para fazer esta alteração:

• Alterar padrões usando o SQL Server Management Studio (a.k.a. “SSMS”), expandindo os Integration Services, clicando com o botão direito do mouse no SSISDB e depois clicando no menu Properties (Fig. 2 – SSMS).

• Alterar padrões usando T-SQL abaixo para atualizar os registros na tabela ssisdb.catalog.catalog_properties diretamente.

Após a alteração estar concluída, você deve ser capaz de confirmar as alterações executando a consulta T-SQL abaixo (Fig 3 – Catálogo SSISDB)

Embora a mudança de padrões possa resolver o problema, pode não resolver o problema de espaço em disco SSISDB existente com rapidez suficiente. Você pode acelerar o processo de limpeza modificando o código e mudando @delete_batch_size de 1000 para 10000 alterando ssisdb.internal.cleanup_server_retention_window stored procedure (Fig 4 – Procedimento de Retenção).

Sumário

Este artigo destacou um problema potencial com os padrões do Catálogo SSISDB que poderia levar ao esgotamento do espaço em disco e forneceu uma solução que forçaria o SSISDB a reter menos informação.