Sauvegarder, restaurer et déplacer le catalogue SSIS

Article
05/07/2013

SQL Server 2012 Integration Services (SSIS) comprend la base de données SSISDB. Interrogez les vues de la base de données SSISDB pour inspecter les objets, les paramètres et les données opérationnelles stockés dans le catalogue SSISDB. Cette rubrique fournit des instructions sur la sauvegarde et la restauration de la base de données.

Le catalogue SSISDB stocke les packages que vous avez déployés sur le serveur Integration Services. Pour plus d'informations sur le catalogue, consultez Catalogue SSIS.

Pour sauvegarder la base de données SSIS

Ouvrez SQL Server Management Studio et connectez-vous à une instance de SQL Server.
Sauvegardez la clé principale de la base de données SSISDB, à l'aide de l'instruction Transact-SQL BACKUP MASTER KEY. La clé est stockée dans un fichier que vous spécifiez. Utilisez un mot de passe pour chiffrer la clé principale dans le fichier.

Pour plus d'informations sur cette instruction, consultez BACKUP MASTER KEY (Transact-SQL).

Dans l'exemple suivant, la clé principale est exportée vers le fichier c:\temp directory\RCTestInstKey. Le mot de passe LS2Setup! est utilisé pour chiffrer la clé principale.
```
backup master key to file = 'c:\temp\RCTestInstKey'
       encryption by password = 'LS2Setup!'
```
Sauvegardez la base de données SSISDB à l'aide de la boîte de dialogue Sauvegarder la base de données dans SQL Server Management Studio. Pour plus d'informations, consultez Procédure : sauvegarder une base de données (SQL Server Management Studio).
Générez le script CREATE LOGIN pour ##MS_SSISServerCleanupJobLogin## en procédant comme suit. Pour plus d'informations, consultez CREATE LOGIN (Transact-SQL).
1. Dans l'Explorateur d'objets de SQL Server Management Studio, développez le nœud Sécurité, puis le nœud Connexions.
2. Cliquez avec le bouton droit sur ##MS_SSISServerCleanupJobLogin##, puis cliquez sur Générer un script de la connexion en tant que > CREATE To > Nouvelle fenêtre d'éditeur de requête.
Vous vous devez restaurer la base de données SSISDB sur une instance SQL Server où le catalogue SSISDB n'a jamais été créé, générez le script CREATE PROCEDURE pour sp_ssis_startup, en effectuant les opérations suivantes. Pour plus d'informations, consultez CREATE PROCEDURE (Transact-SQL).
1. Dans l'Explorateur d'objets, développez le nœud Bases de données, puis le nœud Clé principale > Programmabilité > Procédures stockées.
2. Cliquez avec le bouton droit sur dbo.sp_ssis_startup, puis cliquez sur Générer un script de la procédure stockée en tant que > CREATE To > Nouvelle fenêtre d'éditeur de requête.
Assurez-vous que Le SQL Server Agent a démarré.
Vous vous devez restaurer la base de données SSISDB sur une instance SQL Server où le catalogue SSISDB n'a jamais été créé, générez un script pour la tâche de maintenance de serveur SSIS en effectuant les opérations suivantes. Le script est créé automatiquement dans l'Agent SQL Server lorsque le catalogue SSISDB est créé. Le travail permet de nettoyer les journaux d'opérations de nettoyage en dehors de la période de conservation et de supprimer les versions antérieures des projets.
1. Dans l'Explorateur d'objets, développez le nœud SQL Server Agent, puis le nœud Travaux.
2. Cliquez avec le bouton droit sur le travail de maintenance de serveur SSIS, puis sélectionnez Générer un script du travail en tant que > CREATE To > Nouvelle fenêtre d'éditeur de requête.

Pour restaurer la base de données SSIS

Si vous restaurez la base de données SSISDB sur une instance SQL Server où le catalogue SSISDB n'a jamais été créé, activez le CLR en exécutant la procédure stockée sp_configure. Pour plus d'informations, consultez sp_configure (Transact-SQL) et Option clr enabled.
```
use master 
       sp_configure 'clr enabled', 1
       reconfigure
```
Vous restaurez la base de données SSISDB sur une instance SQL Server où le catalogue SSISDB n'a jamais été créé, créez la clé asymétrique et la connexion à partir de la clé asymétrique et accordez l'autorisation UNSAFE à la connexion.
```
Create Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey
       FROM Executable File = 'C:\Program Files\Microsoft SQL Server\110\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll' 
       
```
Les procédures stockées CLR Integration Services requièrent l'octroi d'autorisations UNSAFE à la connexion, car cette dernière nécessite un accès supplémentaire aux ressources restreintes, par exemple l'API Win32 de Microsoft. Pour plus d'informations sur l'autorisation de code UNSAFE, consultez Création d'un assembly.
```
Create Login MS_SQLEnableSystemAssemblyLoadingUser
       FROM Asymmetric key MS_SQLEnableSystemAssemblyLoadingKey 
 
       Grant unsafe Assembly to MS_SQLEnableSystemAssemblyLoadingUser
```
Restaurez la base de données SSISDB à partir de la sauvegarde, à l'aide de la boîte de dialogue Restaurer la base de données dans SQL Server Management Studio. Pour plus d'informations, consultez les rubriques ci-dessous.
Exécutez les scripts que vous avez créés dans la procédure de sauvegarde du catalogue SSISDB pour ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup et le travail de maintenance de serveur SSIS. Assurez-vous que SQL Server Agent a démarré.
Exécutez l'instruction suivante afin de définir la procédure sp_ssis_startup pour l'exécution automatique. Pour plus d'informations, consultez sp_procoption (Transact-SQL).
```
EXEC sp_procoption N'sp_ssis_startup','startup','on'
```
Mappez l'utilisateur SSISDB ##MS_SSISServerCleanupJobUser## (base de données SSISDB) à ##MS_SSISServerCleanupJobLogin##, à l'aide de la boîte de dialogue Propriétés de la connexion dans SQL Server Management Studio.
Restaurez la clé principale à l'aide de l'une des méthodes suivantes. Pour plus d'informations sur le chiffrement, consultez Hiérarchie de chiffrement.
- Méthode 1
  
  Utilisez cette méthode si vous avez déjà effectué une sauvegarde de la clé principale de base de données et si vous disposez du mot de passe de chiffrement de la clé principale.
```
       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
```
  [!REMARQUE]
  
  Assurez-vous que le compte de service SQL Server dispose des autorisations nécessaires pour lire le fichier de clé de sauvegarde.
  
  [!REMARQUE]
  
  Le message d'avertissement suivant s'affiche dans SQL Server Management Studio si la clé principale de base de données n'a pas encore été chiffrée par la clé principale du service. Ignorez le message d'avertissement.
  
  Impossible de déchiffrer la clé principale active. Cette erreur a été ignorée parce que l'option FORCE a été spécifiée.
  
  L'argument FORCE spécifie que le processus de restauration doit continuer même si la clé principale de base de données actuelle n'est pas ouverte. Pour le catalogue SSISDB, comme la clé principale de base de données n'a pas été ouverte sur l'instance où vous restaurez la base de données, vous voyez s'afficher ce message.
- Méthode 2
  
  Utilisez cette méthode si vous disposez du mot de passe d'origine utilisé pour créer SSISDB.
```
open master key decryption by password = 'LS1Setup!' --'Password used when creating SSISDB'
       Alter Master Key Add encryption by Service Master Key
```
Déterminez si le schéma de catalogue SSISDB et les binaires Integration Services (assembly SQLCLR et ISServerExec) sont compatibles en exécutant catalog.check_schema_version.
Pour vérifier que la base de données SSISDB a été restaurée correctement, effectuez des opérations sur le catalogue SSISDB, par exemple exécutez des packages déployés sur le serveur Integration Services. Pour plus d'informations, consultez Exécuter un package sur le serveur SSIS à l'aide de SQL Server Management Studio.

Pour déplacer la base de données SSIS

Suivez les instructions pour déplacer les bases de données utilisateur. Pour plus d'informations, consultez Déplacer des bases de données utilisateur.

Veillez à sauvegarder la clé principale de la base de données SSISDB et à protéger le fichier de sauvegarde. Pour plus d'informations, consultez Pour sauvegarder le catalogue SSISDB.

Assurez-vous que les objets Integration Services (SSIS) appropriés sont créés dans la nouvelle instance SQL Server où le catalogue SSISDB n'a pas encore été créé.

Sauvegarder, restaurer et déplacer le catalogue SSIS

Pour sauvegarder la base de données SSIS

Pour restaurer la base de données SSIS

Pour déplacer la base de données SSIS

Ressources supplémentaires