Catálogo do SSIS

  • Artigo

Aplica-se a:SQL Server SSIS Integration Runtime no Azure Data Factory

O catálogo do SSISDB é o ponto central para trabalhar com os projetos do SSIS (Integration Services) que você implantou no servidor Integration Services. Por exemplo, você define parâmetros de projeto e pacote, configura ambientes para especificar valores de runtime para pacotes, executa e soluciona problemas de pacotes, e gerencia as operações de servidor do Integration Services .

Observação

Este artigo descreve o Catálogo do SSIS em geral e o Catálogo do SSIS em execução localmente. Também é possível criar o Catálogo do SSIS no Banco de Dados SQL do Azure e implantar e executar pacotes do SSIS no Azure. Para obter mais informações, consulte Migrar cargas de trabalho do SQL Server Integration Services por lift-and-shift para a nuvem.

Embora também seja possível executar pacotes do SSIS no Linux, não há suporte ao Catálogo do SSIS no Linux. Para obter mais informações, consulte Extrair, transformar e carregar dados no Linux com o SSIS.

Os objetos armazenados no catálogo SSISDB incluem projetos, pacotes, parâmetros, ambientes e histórico operacional.

Você inspeciona objetos, configurações e dados operacionais que são armazenados no catálogo do SSISDB , consultando as exibições no banco de dados SSISDB . Você gerencia os objetos chamando procedimentos armazenados no banco de dados SSISDB ou usando a interface de usuário do catálogo SSISDB . Em muitos casos, a mesma tarefa pode ser executada na interface de usuário ou chamando um procedimento armazenado.

Para manter o banco de dados SSISDB , é recomendado que você aplique políticas empresariais padrão para gerenciar os bancos de dados de usuários. Para obter informações sobre como criar planos de manutenção, consulte Maintenance Plans.

O catálogo SSISDB e o banco de dados SSISDB dão suporte ao Windows PowerShell. Para obter mais informações sobre como usar o SQL Server com Windows PowerShell, consulte SQL Server PowerShell. Para obter exemplos de como usar o Windows PowerShell para concluir tarefas como implantar um projeto, consultar a entrada de blog, SSIS e PowerShell no SQL Server 2012em blogs.msdn.com.

Para obter mais informações sobre como exibir dados de operações, consulte Monitorar pacote em execução e outras operações.

Você acessa o catálogo SSISDB no SQL Server Management Studio conectando-se ao Mecanismo de Banco de Dados do SQL Server e expandindo o nó Catálogos do Integration Services no Pesquisador de Objetos. Você acessa o banco de dados SSISDB no SQL Server Management Studio expandindo o nó Bancos de Dados no Pesquisador de Objetos.

Observação

Não é possível renomear o banco de dados SSISDB .

Observação

Se a instância do SQL Server à qual o banco de dados SSISDB está anexado para ou não responde, o processo ISServerExec.exe termina. Uma mensagem é gravada em um log de Eventos do Windows.

Se os recursos do SQL Server fizerem failover como parte de um failover de cluster, os pacotes em execução não serão reiniciados. Você pode usar pontos de verificação para reiniciar pacotes. Para saber mais, confira Restart Packages by Using Checkpoints.

Recursos e funcionalidades

Identificadores do objeto de catálogo

Quando você cria um novo objeto no catálogo, atribua um nome ao objeto. O nome do objeto é um identificador. OSQL Server define regras que estabelecem que os caracteres podem ser usados em um identificador. Os nomes destes objetos devem seguir as regras de identificador.

  • Pasta

  • Project

  • Ambiente

  • Parâmetro

  • Variável de ambiente

Pasta, projeto, ambiente

Considere as seguintes regras ao renomear uma pasta, um projeto ou um ambiente.

  • Os caracteres inválidos incluem caracteres ASCII/Unicode de 1 a 31, aspas ("), menor que (<), maior que (>), barra vertical (|), Backspace (\b), nulo (\0) e Tab (\t).

  • O nome não pode conter espaços à esquerda ou à direita.

  • @ não é permitido como primeiro caractere, mas os caracteres subsequentes podem usar @.

  • O comprimento do nome deve ser maior ou igual a 0 e menor ou igual a 128.

Parâmetro

Considere as seguintes regras ao nomear um parâmetro.

  • O primeiro caractere do nome deve ser uma letra, conforme definido no Unicode Standard 2.0, ou um caractere de sublinhado (_).

  • Os caracteres subsequentes podem ser letras ou números, conforme definido no Unicode Standard 2.0, ou um caractere de sublinhado (_).

Variável de ambiente

Considere as seguintes regras ao nomear uma variável de ambiente.

  • Os caracteres inválidos incluem caracteres ASCII/Unicode de 1 a 31, aspas ("), menor que (<), maior que (>), barra vertical (|), Backspace (\b), nulo (\0) e Tab (\t).

  • O nome não pode conter espaços à esquerda ou à direita.

  • @ não é permitido como primeiro caractere, mas os caracteres subsequentes podem usar @.

  • O comprimento do nome deve ser maior ou igual a 0 e menor ou igual a 128.

  • O primeiro caractere do nome deve ser uma letra, conforme definido no Unicode Standard 2.0, ou um caractere de sublinhado (_).

  • Os caracteres subsequentes podem ser letras ou números, conforme definido no Unicode Standard 2.0, ou um caractere de sublinhado (_).

Configuração do catálogo

Você ajusta como o catálogo se comporta ajustando as propriedades do catálogo. As propriedades do catálogo definem como os dados confidenciais serão criptografados, e como as operações e os dados de controle de versão de projeto serão retidos. Para definir as propriedades do catálogo, use a caixa de diálogo Propriedades do Catálogo ou chame o procedimento armazenado catalog.configure_catalog (Banco de dados SSISDB). Para exibir as propriedades, use a caixa de diálogo ou consulte catalog.catalog_properties (Banco de dados SSISDB). Acesse a caixa de diálogo clicando com o botão direito do mouse em SSISDB no Pesquisador de Objetos.

Operações e limpeza de versão do projeto

Os dados de status de muitas operações no catálogo são armazenados nas tabelas de banco de dados internas. Por exemplo, o catálogo rastreia o status das execuções de pacote e das implantações de projeto. Para manter o tamanho dos dados de operações, o Trabalho de Manutenção do Servidor SSIS no SQL Server Management Studio é usado para remover dados antigos. Este trabalho do SQL Server Agent é criado quando Integration Services é instalado.

Você pode atualizar ou reimplantar um projeto do Integration Services implantando-o com o mesmo nome na mesma pasta do catálogo. Por padrão, cada vez que você reimplanta um projeto, o catálogo SSISDB retém a versão anterior do projeto. Para manter o tamanho dos dados de operações, o Trabalho de Manutenção do Servidor SSIS é usado para remover versões antigas de projetos.

Para executar o Trabalho de Manutenção do Servidor SSIS, o SSIS cria o logon do SQL Server ##MS_SSISServerCleanupJobLogin## . Esse logon é apenas para uso interno do SSIS.

As propriedades de catálogo SSISDB a seguir definem como este trabalho do SQL Server Agent se comporta. Você pode exibir e modificar as propriedades usando a caixa de diálogo Propriedades do Catálogo ou usando catalog.catalog_properties (Banco de dados SSISDB) e catalog.configure_catalog (Banco de dados SSISDB).

Limpar Logs Periodicamente
A etapa de trabalho para limpeza de operações é executada quando esta propriedade é definida como True.

Período de Retenção (dias)
Define a idade máxima dos dados de operações permitidos (em dias). Os dados mais antigos são removidos.

O valor mínimo é um dia. O valor máximo só é limitado pelo valor máximo dos dados SQL Server int data. Para obter informações sobre esse tipo de dados, confira int, bigint, smallint e tinyint (Transact-SQL).

Remover Periodicamente Versões Antigas
A etapa de trabalho para limpeza de versão de projeto é executada quando esta propriedade é definida como True.

Número Máximo de Versões por Projeto
Define quantas versões de um projeto são armazenadas no catálogo. As versões de projetos mais antigas são removidas.

Algoritmo de Criptografia

A propriedade Algoritmo de Criptografia especifica o tipo de criptografia usado para criptografar valores de parâmetro confidenciais. Você pode escolher entre os seguintes tipos de criptografia.

  • AES_256 (padrão)

  • AES_192

  • AES_128

  • DESX

  • TRIPLE_DES_3KEY

  • TRIPLE_DES

  • DES

Quando você implantar um projeto do Integration Services para o servidor do Integration Services , o catálogo criptografará automaticamente os dados do pacote e os valores confidenciais. O catálogo também descriptografa automaticamente os dados quando você recupera-os. O catálogo SSISDB usa o nível de proteção ServerStorage . Para obter mais informações, consulte Access Control for Sensitive Data in Packages.

Alterar o algoritmo de criptografia é uma operação demorada. Primeiro, o servidor tem que usar o algoritmo previamente especificado para descriptografar todos os valores de configuração. Em seguida, o servidor tem que usar o novo algoritmo para criptografar novamente os valores. Durante este momento, não pode haver outras operações do Integration Services no servidor. Assim, para permitir que operações do Integration Services continuem ininterruptas, o algoritmo de criptografia deverá ser um valor somente leitura na caixa de diálogo do Management Studio.

Para alterar a configuração da propriedade Algoritmo de Criptografia , defina o banco de dados SSISDB como modo de usuário único e chame o procedimento armazenado catalog.configure_catalog. Use ENCRYPTION_ALGORITHM para o argumento property_name . Para os valores de propriedade com suporte, confira catalog.catalog_properties (Banco de dados SSISDB). Para obter mais informações sobre o procedimento armazenado, veja catalog.configure_catalog (Banco de dados SSISDB).

Para obter mais informações sobre o modo de usuário único, veja Definir um banco de dados como modo de usuário único. Para obter informações sobre criptografia e algoritmos de criptografia no SQL Server, consulte os tópicos na seção Criptografia do SQL Server.

Uma chave mestra de banco de dados é usada para a criptografia. A chave é criada quando você cria o catálogo.

A tabela a seguir lista os nomes de propriedade mostrados na caixa de diálogo Propriedades do Catálogo e as propriedades correspondentes na exibição de banco de dados.

Nome da Propriedade (caixa de diálogoPropriedades do Catálogo ) Nome da Propriedade (exibição de banco de dados)
Nome do Algoritmo de Criptografia ENCRYPTION_ALGORITHM
Limpar Logs Periodicamente OPERATION_CLEANUP_ENABLED​
Período de Retenção (dias) RETENTION_WINDOW
Remover Periodicamente Versões Antigas VERSION_CLEANUP_ENABLED
Número Máximo de Versões por Projeto MAX_PROJECT_VERSIONS
Nível de Log Padrão em Todo o Servidor SERVER_LOGGING_LEVEL

Permissões

Os projetos, ambientes e pacotes são armazenados em pastas, que são objetos protegíveis. Você pode conceder permissões a uma pasta, incluindo a permissão MANAGE_OBJECT_PERMISSIONS. MANAGE_OBJECT_PERMISSIONS permite delegar a administração do conteúdo da pasta a um usuário sem precisar conceder a associação do usuário à função ssis_admin. Você também pode conceder permissões para projetos, ambientes e operações. As operações incluem a inicialização do Integration Services, a implantação de projetos, a criando e a inicialização de execuções, a validação de projetos e pacotes, e a configuração do catálogo SSISDB .

Para obter mais informações sobre as funções de banco de dados, veja Funções no nível de banco de dados.

O catálogo SSISDB usa um gatilho DDL, ddl_cleanup_object_permissions, para impor a integridade das informações de permissões para elementos protegíveis do SSIS. O gatilho é acionado quando uma entidade de segurança de banco de dados, como um usuário de banco de dados, função de banco de dados ou função de aplicativo de banco de dados, é removida do banco de dados SSISDB.

Se a entidade de segurança tiver concedido ou negado permissões a outras entidades de segurança, revogue as permissões dadas pelo concessor, para que a entidade de segurança possa ser removida. Caso contrário, uma mensagem de erro será retornada quando o sistema tentar remover a entidade de segurança. O gatilho removerá todos os registros de permissão em que a entidade de segurança de banco de dados é um usuário autorizado.

É recomendável que o gatilho não seja desabilitado porque ele assegura que não haverá nenhum registro de permissão órfão depois que uma entidade de segurança de banco de dados for removida do banco de dados SSISDB .

Gerenciando permissões

Você pode gerenciar permissões usando a interface do usuário do SQL Server Management Studio , procedimentos armazenados e o namespace Microsoft.SqlServer.Management.IntegrationServices .

Para gerenciar permissões usando a interface do usuário do SQL Server Management Studio, use as seguintes caixas de diálogo:

Para gerenciar permissões usando o Transact-SQL, chame catalog.grant_permission (Banco de dados SSISDB), catalog.deny_permission (Banco de dados SSISDB) e catalog.revoke_permission (Banco de dados SSISDB). Para exibir as permissões em vigor da entidade de segurança atual para todos os objetos, confira catalog.effective_object_permissions (Banco de dados SSISDB). Este tópico fornece descrições dos diferentes tipos de permissões. Para exibir as permissões atribuídas explicitamente ao usuário, confira catalog.explicit_object_permissions (Banco de dados SSISDB).

Pastas

Uma pasta contém um ou mais projetos e ambientes no catálogo SSISDB . Você pode usar a exibição catalog.folders (Banco de dados SSISDB) para acessar informações sobre pastas no catálogo. Use os seguintes procedimentos armazenados para gerenciar pastas:

Projetos e pacotes

Cada projeto pode conter vários pacotes. Os projetos e pacotes podem conter parâmetros e referências a ambientes. Você pode acessar os parâmetros e referências de ambiente usando a Configure Dialog Box.

Realize outras tarefas do projeto chamando os seguintes procedimentos armazenados:

Estas exibições fornecem detalhes sobre pacotes, projetos e versões de projeto.

Parâmetros

Use os parâmetros para atribuir valores às propriedades de pacote no tempo de execução do pacote. Para definir o valor de um pacote ou parâmetro de projeto e limpar o valor, chame catalog.set_object_parameter_value (Banco de dados SSISDB) e catalog.clear_object_parameter_value (Banco de dados SSISDB). Para definir o valor de um parâmetro para uma instância de execução, chame catalog.set_execution_parameter_value (Banco de dados SSISDB). Você pode recuperar valores de parâmetro padrão chamando catalog.get_parameter_values (Banco de dados SSISDB).

Estas exibições mostram os parâmetros de todos os pacotes e projetos, e os valores de parâmetro usados para uma instância de execução.

Ambientes de servidor, variáveis de servidor e referências de ambiente de servidor

Os ambientes de servidor contêm variáveis de servidor. Os valores variáveis podem ser usados quando um pacote é executado ou validado no servidor do Integration Services .

Os procedimentos armazenados a seguir permitem executar muitas outras tarefas de gerenciamento para ambientes e variáveis.

Ao chamar o procedimento armazenado catalog.set_environment_variable_protection (Banco de dados SSISDB), você pode definir o bit de sensibilidade de uma variável.

Para usar o valor de uma variável de servidor, especifique a referência entre o projeto e o ambiente de servidor. Você pode usar os procedimentos armazenados para criar e excluir referências. Você também pode indicar se o ambiente pode estar localizado na mesma pasta que o projeto ou em uma pasta diferente.

Para obter mais detalhes sobre ambientes e variáveis, consulte estas exibições.

Execuções e validações

Uma execução é uma instância de uma execução de pacote. Chame catalog.create_execution (Banco de dados SSISDB) e catalog.start_execution (Banco de dados SSISDB) para criar e iniciar uma execução. Para interromper a execução ou uma validação de pacote/projeto, chame catalog.stop_operation (Banco de dados SSISDB).

Para fazer com que um pacote em execução pause ou crie um arquivo de despejo, chame o procedimento armazenado catalog.create_execution_dump. Um arquivo de despejo fornece informações sobre a execução de um pacote que pode ajudar a solucionar problemas de execução. Para obter mais informações sobre como gerar e configurar arquivos de despejo, consulte Generating Dump Files for Package Execution.

Para obter detalhes sobre execuções, validações, mensagens que são registradas em log durante operações, e informações contextuais relacionadas a erros, consulte estas exibições.

Você pode validar projetos e pacotes chamando os procedimentos armazenados catalog.validate_project (Banco de dados SSISDB) e catalog.validate_package (Banco de dados SSISDB). A exibição catalog.validations (Banco de dados SSISDB) fornece detalhes sobre validações, como as referências de ambiente de servidor consideradas na validação, se é uma validação de dependência ou uma validação completa e se o runtime de 32 ou 64 bits é usado para executar o pacote.

Criar o catálogo do SSIS

Depois de criar e testar pacotes no SQL Server Data Tools, você pode implantar os projetos que contêm os pacotes em um servidor do Integration Services . Para poder implantar os projetos no servidor do Integration Services , o servidor deve conter o catálogo do SSISDB . O programa de instalação do SQL Server 2012 (11.x) não cria o catálogo automaticamente; você precisará criar o catálogo manualmente por meio das instruções a seguir.

Você pode criar o catálogo do SSISDB no SQL Server Management Studio. Você também pode criar o catálogo programaticamente usando o Windows PowerShell.

Para criar o catálogo SSISDB no SQL Server Management Studio

  1. Abra o SQL Server Management Studio.

  2. Conecte-se ao Mecanismo de Banco de Dados do SQL Server .

  3. No Pesquisador de Objetos, expanda o nó de servidor, clique com o botão direito do mouse no nó Catálogos do Integration Services e clique em Criar Catálogo.

  4. Clique em Habilitar Integração CLR.

    Esse catálogo usa procedimentos armazenados CLR.

  5. Clique em Habilitar a execução automática de procedimento armazenado do Integration Services na inicialização do SQL Server para habilitar o procedimento armazenado catalog.startup a ser executado toda vez que a instância do servidor SSIS for reiniciada.

    O procedimento armazenado executa a manutenção do estado das operações para o catálogo SSISDB. Ele corrigirá o status dos pacotes que estavam em execução se a instância do servidor do SSIS ficar inativa.

  6. Digite uma senha e clique em Ok.

    A senha protege a chave mestra do banco de dados que é usada para criptografar os dados do catálogo. Salve a senha em um local seguro. É recomendado que você também faça backup da chave mestra do banco de dados. Para obter mais informações, consulte Back Up a Database Master Key.

Para criar o catálogo do SSISDB programaticamente

  1. Execute o seguinte script do PowerShell:

    # Load the IntegrationServices Assembly  
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")  

# Store the IntegrationServices Assembly namespace to avoid typing it every time  
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"  

Write-Host "Connecting to server ..."  

# Create a connection to the server  
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"  
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString  

# Create the Integration Services object  
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection  

# Provision a new SSIS Catalog  
$catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "P@assword1")  
$catalog.Create()

    Para obter mais exemplos de como usar o Windows PowerShell e o namespace Microsoft.SqlServer.Management.IntegrationServices, confira a entrada de blog SSIS e o PowerShell no SQL Server 2012 em blogs.msdn.com. Para obter uma visão geral do namespace e dos exemplos de códigos, consulte a entrada do blog Prévia do modelo do objeto gerenciado do catálogo do SSISem blogs.msdn.com.

Caixa de diálogo Propriedades do Catálogo

Use a caixa de diálogo de Propriedades do Catálogo para configurar o catálogo do SSISDB. As propriedades do catálogo definem como dados confidenciais são criptografados, como dados de operações e de controle de versão de projeto são retidos, e quando o tempo limite de operações de validação expira. O catálogo do SSISDB é um armazenamento e ponto de administração central para projetos, pacotes, parâmetros e ambientes do Integration Services .

Também é possível exibir as propriedades do catálogo na exibição catalog.catalog_properties e definir as propriedades usando o procedimento armazenado catalog.configure_catalog. Para obter mais informações, consulte catalog.catalog_properties (banco de dados SSISDB) e catalog.configure_catalog (banco de dados SSISDB).

O que você deseja fazer?

Abrir a caixa de diálogo Propriedades do Catálogo

  1. Abra o SQL Server Management Studio.

  2. Conecte-se ao Mecanismo de Banco de Dados do Microsoft SQL Server.

  3. No Pesquisador de Objetos, expanda o nó Integration Services , clique com o botão direito do mouse em SSISDBe clique em Propriedades.

Configurar as opções

Opções

A tabela a seguir descreve determinadas propriedades na caixa de diálogo e as propriedades correspondentes na exibição catalog.catalog_properties.

Nome da Propriedade (caixa de diálogo Propriedades do Catálogo) Nome da propriedade (exibição catalog.catalog_property) Descrição
Nome do Algoritmo de Criptografia ENCRYPTION_ALGORITHM Especifica o tipo de criptografia usado para criptografar os valores dos parâmetros confidenciais no catálogo. O valores possíveis são os seguintes:

DES

TRIPLE_DES

TRIPLE_DES_3KEY

DESPX

AES_128

AES_192

AES_256 (padrão)
Número Máximo de Versões por Projeto MAX_PROJECT_VERSIONS Especifique quantas versões de um projeto são armazenadas no catálogo. Versões mais antigas de projetos que excedem o máximo são removidas quando o trabalho de limpeza de versões de projeto é executado.
Limpar Logs Periodicamente OPERATION_CLEANUP_ENABLED Defina a propriedade como True para indicar que o trabalho do SQL Server Agent, limpeza de operações, é executada. Caso contrário, defina a propriedade como False.
Período de Retenção (dias) RETENTION_WINDOW Especifique a idade máxima dos dados de operações permitidos (em dias). Dados mais antigos do que o número de dias especificado são removidos pelo trabalho do SQL Agent, limpeza de operações.

Fazer backup, restaurar e mover o catálogo do SSIS

Aplica-se a SQL Server 2016 (13.x) e versões posteriores Not supported. Azure SQL Database Not supported. Azure Synapse Analytics Not supported. PDW (Analytics Platform System)

SSIS (SQL Server 2019 Integration Services) inclui o banco de dados SSISDB. Você consulta exibições no banco de dados SSISDB para inspecionar objetos, configurações e dados operacionais que são armazenados no catálogo do SSISDB . Este tópico fornece instruções para fazer backup do banco de dados e restaurá-lo.

O catálogo do SSISDB armazena os pacotes que você implantou no servidor Integration Services. Para obter mais informações sobre o catálogo, consulte Catálogo do SSIS.

Para fazer o backup do banco de dados SSIS

  1. Abra o SQL Server Management Studio e conecte-se a uma instância do SQL Server.

  2. Faça backup da chave mestra para o banco de dados do SSISDB, usando a instrução Transact-SQL BACKUP MASTER KEY. A chave é armazenada em um arquivo que você especifica. Use a senha para criptografar a chave mestra no arquivo.

    Para obter mais informações sobre a instrução, confira BACKUP MASTER KEY (Transact-SQL).

    No exemplo a seguir, a chave mestra é exportada para o arquivo c:\temp directory\RCTestInstKey . A senha LS2Setup! é usada para criptografar a chave mestra.

    backup master key to file = 'c:\temp\RCTestInstKey'  
       encryption by password = 'LS2Setup!'

  3. Faça backup do banco de dados do SSISDB usando a caixa de diálogo Backup de Banco de Dados no SQL Server Management Studio. Para obter mais informações, confira Como executar backup de um banco de dados (SQL Server Management Studio).

  4. Gere o script de CREATE LOGIN para ##MS_SSISServerCleanupJobLogin## realizando o procedimento a seguir. Para obter mais informações, confira CRIAR LOGON (Transact-SQL).

    1. No Pesquisador de Objetos do SQL Server Management Studio, expanda o nó Segurança e expanda o nó Logons .

    2. Clique com o botão direito do mouse em ##MS_SSISServerCleanupJobLogin## e clique em Script de Logon como>CREATE To>Nova Janela do Editor de Consultas.

  5. Se estiver restaurando o banco de dados SSISDB para uma instância do SQL Server na qual o catálogo do SSISDB nunca foi criado, gere o script de CREATE PROCEDURE para sp_ssis_startup realizando o procedimento a seguir. Para obter mais informações, veja CREATE PROCEDURE (Transact-SQL).

    1. No Pesquisador de Objetos, expanda o nó Bancos de Dados e, em seguida, expanda o nó mestre>Programação>Procedimentos Armazenados .

    2. Clique com o botão direito do mouse em dbo.sp_ssis_startup e, em seguida, clique em Gerar Script de Procedimento Armazenado como>CRIAR para>Nova Janela do Editor de Consultas.

  6. Confirme que o SQL Server Agent foi iniciado

  7. Se estiver restaurando o banco de dados SSISDB para uma instância do SQL Server na qual o catálogo do SSISDB nunca foi criado, gere um script para o Trabalho de Manutenção do Servidor do SSIS realizando o procedimento a seguir. O script é criado automaticamente no SQL Server Agent quando o catálogo do SSISDB é criado. O trabalho ajuda a limpar os logs da operação de limpeza fora da janela de retenção e remove versões anteriores de projetos.

    1. No Pesquisador de Objetos, expanda o nó SQL Server Agent e, em seguida, expanda o nó Trabalhos .

    2. Clique com o botão direito do mouse em Trabalho de Manutenção do Servidor do SSIS e, em seguida, clique em Gerar Script de Trabalho como>CRIAR para>Nova Janela do Editor de Consultas.

Para restaurar o banco de dados SSIS

  1. Se você estiver restaurando o banco de dados SSISDB para uma instância do SQL Server em que o catálogo do SSISDB nunca foi criado, habilite o CLR (Common Language Runtime) executando o procedimento armazenado sp_configure. Para obter mais informações, confira sp_configure (Transact-SQL) e Opção clr habilitada.

    use master   
       sp_configure 'clr enabled', 1  
       reconfigure

  2. Se você estiver restaurando o banco de dados SSISDB para uma instância do SQL Server onde o catálogo do SSISDB nunca foi criado, crie a chave assimétrica e o logon da chave assimétrica e conceda permissão UNSAFE para o logon.

    Create Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey  
       FROM Executable File = 'C:\Program Files\Microsoft SQL Server\YourSQLServerDefaultCompatibilityLevel\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'

    Encontre o valor de YourSQLServerDefaultCompatibilityLevel em uma lista de níveis de compatibilidade padrão do SQL Server.

    Integration Services Os procedimentos armazenados CLR exigem que permissões de UNSAFE sejam concedidas ao logon porque o logon exige acesso adicional a recursos restritos, como a API do Microsoft Win32. Para obter mais informações sobre a permissão de código UNSAFE, consulte Criando um assembly.

    Create Login ##MS_SQLEnableSystemAssemblyLoadingUser## FROM Asymmetric Key MS_SQLEnableSystemAssemblyLoadingKey   
Grant Unsafe Assembly to ##MS_SQLEnableSystemAssemblyLoadingUser##

  3. Restaure o banco de dados SSISDB do backup usando a caixa de diálogo Restaurar Banco de Dados no SQL Server Management Studio. Para obter mais informações, consulte estes tópicos:

  4. Execute os scripts que você criou no procedimento Para fazer backup do banco de dados SSIS para ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup e Trabalho de Manutenção do Servidor SSIS. Confirme que o SQL Server Agent foi iniciado.

  5. Execute a instrução a seguir para definir o procedimento sp_ssis_startup para execução automática. Para obter mais informações, veja sp_procoption (Transact-SQL).

    EXEC sp_procoption N'sp_ssis_startup','startup','on'

  6. Mapeie o usuário do SSISDB ##MS_SSISServerCleanupJobUser## (banco de dados do SSISDB) para ##MS_SSISServerCleanupJobLogin## usando a caixa de diálogo Propriedades de Logon no SQL Server Management Studio.

  7. Restaure a chave mestra usando um dos seguintes métodos. Para obter mais informações sobre criptografia, consulte Hierarquia de criptografia.

    • Método 1

      Use esse método se você já executou um backup da chave mestra do banco de dados e tem a senha usada para criptografar a chave mestra.

             Restore master key from file = 'c:\temp\RCTestInstKey'  
       Decryption by password = 'LS2Setup!' -- 'Password used to encrypt the master key during SSISDB backup'  
       Encryption by password = 'LS3Setup!' -- 'New Password'  
       Force

      Observação

      Confirme que a conta de serviço do SQL Server tem permissões para ler o arquivo da chave de backup.

      Observação

      Você verá a mensagem de aviso a seguir exibida no SQL Server Management Studio se a chave mestra de banco de dados ainda não tiver sido criptografada pela chave mestra de serviço. Ignore a mensagem de aviso.

      A chave mestra atual não pode ser descriptografada. O erro foi ignorado porque a opção FORCE foi especificada.

      O argumento FORCE especifica que o processo de restauração deve continuar mesmo se a chave mestra de banco de dados atual não estiver aberta. Para o catálogo do SSISDB, como a chave mestra de banco de dados não foi aberta na instância em que o banco de dados está sendo restaurado, você verá essa mensagem.

    • Método 2

      Use este método se você tiver a senha original que foi usada para criar o SSISDB.

      open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'  
       Alter Master Key Add encryption by Service Master Key

  8. Determine se o esquema de catálogo SSISDB e os binários Integration Services (ISServerExec e SQLCLR assembly) são compatíveis executando catalog.check_schema_version.

  9. Para confirmar que o banco de dados do SSISDB foi restaurado com êxito, execute operações no catálogo d SSISDB, por exemplo, executar pacotes que foram implantados no servidor do Integration Services . Para obter mais informações, confira Executar pacotes do SSIS (Integration Services).

Para mover o banco de dados SSIS

  • Siga as instruções para mover bancos de dados de usuários. Para obter mais informações, veja Mover bancos de dados de usuário.

    Assegure-se de fazer backup da chave mestra do banco de dados SSISDB e proteger o arquivo de backup. Para obter mais informações, consulte Para fazer o backup do banco de dados SSIS.

    Verifique se os objetos pertinentes do Integration Services (SSIS) estão criados na nova instância do SQL Server na qual o catálogo do SSISDB ainda não foi criado.

Atualizar o Catálogo SSIS (SSISDB)

Execute o Assistente de atualização do SSISDB para atualizar o banco de dados do catálogo do SSIS, SSISDB, quando o banco de dados for mais antigo que a versão atual da instância do SQL Server. O banco de dados poderá ser mais antigo quando uma das condições a seguir for verdadeira.

  • Você restaurou o banco de dados de uma versão anterior do SQL Server.

  • Você não removeu o banco de dados de um Grupo de Disponibilidade AlwaysOn antes de atualizar a instância do SQL Server. Essa condição impede o upgrade automático do banco de dados. Para obter mais informações, consulte Upgrading SSISDB in an availability group.

O assistente só pode atualizar o banco de dados em uma instância de servidor local.

Atualizar o Catálogo SSIS (SSISDB) executando o Assistente de Atualização do SSISDB

  1. Fazer backup do banco de dados do Catálogo do SSIS, SSISDB.

  2. No SQL Server Management Studio, expanda o servidor local e expanda Catálogos do Integration Services.

  3. Clique com o botão direito do mouse em SSISDBe selecione Atualização do Banco de Dados para iniciar o Assistente de Atualização do SSISDB. Ou inicie o Assistente de Atualização do SSISDB executando C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe com permissões elevadas no servidor local.

    Launch the SSISDB upgrade wizard

  4. Na página Selecionar Instância , escolha uma instância do SQL Server no servidor local.

    Importante

    O assistente só pode atualizar o banco de dados em uma instância de servidor local.

    Marque a caixa de seleção para indicar que você fez backup do banco de dados SSISDB antes de executar o assistente.

    Select the server in the SSISDB Upgrade Wizard

  5. Escolha Atualizar para atualizar o banco de dados do Catálogo SSIS.

  6. Na página Resultado , examine os resultados.

    Review the results in the SSISDB Upgrade Wizard

Always On para o Catálogo do SSIS (SSISDB)

O recurso Grupos de Disponibilidade AlwaysOn é uma solução de alta disponibilidade e recuperação de desastres que fornece uma alternativa de nível corporativo para espelhamento de banco de dados. Um grupo de disponibilidade permite um ambiente de failover para um conjunto discreto de bancos de dados de usuário, conhecidos como bancos de dados de disponibilidade, que fazem failover juntos. Para obter mais informações, confira AlwaysOn em grupos de disponibilidade.

Para fornecer a alta disponibilidade ao catálogo do SSIS (SSISDB) e seu conteúdo (projetos, pacotes, logs de execução, etc.), você pode adicionar o banco de dados do SSISDB (da mesma forma que qualquer outro banco de dados de usuário) a um Grupo de Disponibilidade AlwaysOn. Quando ocorre um failover, um dos nós secundários automaticamente se torna o novo nó primário.

Observação

Os grupos de disponibilidade contidos, que foram introduzidos no SQL Server 2022, ainda não têm suporte.

Importante

Quando ocorre um failover, os pacotes que estavam em execução não reiniciam ou retomam.

Nesta seção:

  1. Pré-requisitos

  2. Configurar o suporte do SSIS para Always On

  3. Atualizando o SSISDB em um grupo de disponibilidade

Pré-requisitos

Execute as etapas de pré-requisito a seguir antes de habilitar o suporte do Always On no banco de dados do SSISDB.

  1. Configurar um cluster de failover do Windows. Confira a postagem do blog Installing the Failover Cluster Feature and Tools for Windows Server 2012 (Instalando o recurso Cluster de Failover e as Ferramentas para o Windows Server 2012) para obter instruções. Instale o recurso e as ferramentas em todos os nós de cluster.

  2. Instalar o recurso SSIS (SQL Server 2016 com Integration Services) em cada nó do cluster.

  3. Habilite Grupos de Disponibilidade Always On para cada instância do SQL Server. Confira Habilitar Grupos de Disponibilidade Always On para obter detalhes.

Configurar o suporte do SSIS para Always On

Importante

  • Você deve executar estas etapas no nó primário do grupo de disponibilidade.
  • Você deve habilitar o suporte do SSIS para Always Ondepois de adicionar o SSISDB a um Grupo de Disponibilidade Always On.

Etapa 1: Criar o catálogo do Integration Services

  1. Inicie o SQL Server Management Studio e o conecte a uma instância do SQL Server no cluster que você deseja definir como o nó primário do grupo de alta disponibilidade do Always On para SSISDB.

  2. No Pesquisador de Objetos, expanda o nó de servidor, clique com o botão direito do mouse no nó Catálogos do Integration Services e clique em Criar Catálogo.

  3. Clique em Habilitar Integração CLR. Esse catálogo usa procedimentos armazenados CLR.

  4. Clique em Habilitar a execução automática de procedimento armazenado do Integration Services na inicialização do SQL Server para habilitar o procedimento armazenado catalog.startup a ser executado toda vez que a instância do servidor SSIS for reiniciada. O procedimento armazenado executa a manutenção do estado das operações para o catálogo SSISDB. Ele corrigirá o status de todos os pacotes que estavam sendo executados se e quando a instância do servidor SSIS ficar inoperante.

  5. Digite uma senhae clique em OK. A senha protege a chave mestra do banco de dados que é usada para criptografar os dados do catálogo. Salve a senha em um local seguro. É recomendado que você também faça backup da chave mestra do banco de dados. Para obter mais informações, consulte Back Up a Database Master Key.

Etapa 2: Adicionar o SSISDB a um Grupo de Disponibilidade Always On

Adicionar o banco de dados do SSISDB a um Grupo de Disponibilidade Always On é quase igual a adicionar qualquer outro banco de dados de usuário em um grupo de disponibilidade. Confira Use the Availability Group Wizard(Usar o assistente do Grupo de Disponibilidade).

Forneça a senha especificada ao criar o Catálogo do SSIS na página Selecionar Bancos de Dados do assistente Novo Grupo de Disponibilidade.

New Availability Group

Importante

Para evitar problemas com a chave mestra após um failover, use o método Banco de dados completo e backup de log para adicionar o banco de dados SSISDB ao Grupo de Disponibilidade Always On.

Etapa 3: Habilitar o suporte do SSIS para o Always On

Depois de criar o Catálogo do Serviço de integração, clique com o botão direito do mouse no nó Catálogos do Serviço de Integração e clique em Habilitar Suporte do Always On. Você verá a seguinte caixa de diálogo: Habilitar Suporte para Always On. Se esse item de menu estiver desabilitado, verifique se você tem todos os pré-requisitos instalados e clique em Atualizar.

Enable Support for Always On

Aviso

O failover automático do banco de dados do SSISDB só será permitido quando você habilitar o Suporte do SSIS para o Always On.

As réplicas secundárias recém-adicionadas do grupo de disponibilidade Always On são mostradas na tabela. Clique no botão Conectar... de cada réplica na lista e insira as credenciais de autenticação para conectar-se à réplica. A conta de usuário deve ser membro do grupo sysadmin em cada réplica para habilitar o suporte do SSIS para Always On. Depois de se conectar com êxito a cada réplica, clique em OK para habilitar o suporte do SSIS para Always On.

Se a opção Habilitar suporte do Always On no menu de contexto parecer estar desabilitada depois que você concluir os pré-requisitos, tente fazer o seguinte:

  1. Atualize o menu de contexto clicando na opção Atualizar.
  2. Verifique se você está se conectando ao nó primário. Você precisa habilitar o suporte do Always On no nó primário.
  3. Verifique se a versão do SQL Server é 13.0 ou superior. O SSIS dá suporte a Always On apenas no SQL Server 2016 e versões posteriores.

Atualizando o SSISDB em um grupo de disponibilidade

Se estiver atualizando o SQL Server de uma versão anterior e o SSISDB estiver em um grupo de disponibilidade Always On, sua atualização poderá ser bloqueada pela regra "Verificação do SSISDB no Grupo de Disponibilidade Always On". Esse bloqueio ocorre porque a atualização é executada no modo de usuário único, enquanto um banco de dados de disponibilidade deve ser um banco de dados de multiusuário. Portanto, durante a atualização ou a aplicação de patch, todos os bancos de dados de disponibilidade, incluindo o SSISDB, são colocados no modo offline e não são atualizados nem corrigidos. Para permitir que o upgrade continue, primeiro remova o SSISDB do grupo de disponibilidade, faça upgrade ou aplique patch a cada nó e, em seguida, adicione o SSISDB novamente ao grupo de disponibilidade.

Se você estiver bloqueado pela regra "Verificação do SSISDB no Grupo de Disponibilidade Always On", siga estas etapas para atualizar o SQL Server.

  1. Remova o banco de dados do SSISDB do grupo de disponibilidade. Para obter mais informações, veja Remover um banco de dados secundário de um grupo de disponibilidade (SQL Server) e Remover um banco de dados primário de um grupo de disponibilidade (SQL Server).

  2. Clique em Executar novamente no assistente de atualização. A regra "Verificação do SSISDB no Grupo de Disponibilidade Always On" é aprovada.

  3. Clique em Avançar para continuar a atualização.

  4. Depois de atualizar todos os nós, adicione o banco de dados do SSISDB de volta ao grupo de disponibilidade Always On. Para obter mais informações, confira Adicionar um banco de dados a um grupo de disponibilidade (SQL Server).

Se não estiver bloqueado ao fazer upgrade do SQL Server e o SSISDB estiver em um grupo de disponibilidade Always On, faça upgrade do SSISDB separadamente depois de fazer upgrade do mecanismo de banco de dados do SQL Server. Use o Assistente de Atualização do SSIS para atualizar o SSISDB, conforme descrito no procedimento a seguir.

  1. Mova o banco de dados do SSISDB para fora do grupo de disponibilidade ou exclua o grupo de disponibilidade se o SSISDB for o único banco de dados no grupo de disponibilidade. Inicie o SQL Server Management Studio no nó primário do grupo de disponibilidade para executar essa tarefa.

  2. Remova o banco de dados do SSISDB de todos os nós de réplica.

  3. Atualize o banco de dados do SSISDB no nó primário. NoPesquisador de Objetos do SQL Server Management Studio, expanda Catálogos do Integration Services, clique com o botão direito do mouse em SSISDBe selecione Atualização do Banco de Dados. Siga as instruções do Assistente de Atualização do SSISDB para atualizar o banco de dados. Inicie o Assistente de Atualização do SSISDB localmente no nó primário.

  4. Siga as instruções descritas na Etapa 2: Adicionar o SSISDB a um grupo de disponibilidade Always On para adicionar o SSISDB de volta um grupo de disponibilidade.

  5. Siga as instruções descritas na Etapa 3: Habilitar o suporte do SSIS para o Always On.

Catálogo e delegação do SSISDB em cenários de salto duplo

Por padrão, a invocação remota de pacotes do SSIS armazenados no catálogo do SSISDB não é compatível com a delegação de credenciais, às vezes chamada de salto duplo.

Imagine um cenário no qual um usuário faz logon no computador cliente A e inicia o SSMS (SQL Server Management Studio). No SSMS, o usuário se conecta a um SQL Server que está hospedado no computador B, que tem o catálogo do SSISDB. O pacote do SSIS é armazenado nesse catálogo do SSISDB e o pacote, por sua vez, se conecta a um serviço SQL Server que está sendo executado no computador C (o pacote também pode estar acessando qualquer outro serviço). Quando o usuário invoca a execução do pacote do SSIS do computador A, o SSMS primeiro passa com sucesso as credenciais do usuário do computador A para o computador B (onde o processo de runtime do SSIS está executando o pacote). O processo de runtime de execução do SSIS (ISServerExec. exe) agora é obrigado a delegar as credenciais de usuário do computador B para o computador C a fim de que a execução seja concluída com sucesso. No entanto, a delegação de credenciais não é habilitada por padrão.

Um usuário pode habilitar a delegação de credenciais concedendo o direito Confiar neste usuário para delegação a qualquer serviço (somente para Kerberos) à conta de serviço do SQL Server (no computador B), que inicia ISServerExec.exe como um processo filho. Esse processo é conhecido como configuração de delegação irrestrita ou delegação aberta para uma conta de serviço do SQL Server. Antes de conceder esse direito, considere se ele atende aos requisitos de segurança de sua organização.

O SSISDB não é compatível com a delegação restrita. Em um ambiente de salto duplo, se a conta de serviço do SQL Server que hospeda o catálogo do SSISDB (computador B em nosso exemplo) estiver configurada para delegação restrita, o ISServerExec.exe não poderá delegar as credenciais para o terceiro computador (computador C). Isso se aplica a cenários nos quais o Windows Credential Guard está habilitado, o que exige obrigatoriamente a configuração da delegação restrita.

Conteúdo relacionado