Catalogue SSIS

  • Article

S’applique à : SQL Server SSIS Integration Runtime dans Azure Data Factory

Le catalogue SSISDB est l’élément central pour l’utilisation des projets Integration Services (SSIS) que vous avez déployés sur le serveur Integration Services. Ainsi, c'est dans ce catalogue que vous définissez les paramètres de projet et de package, configurez les environnements pour spécifier des valeurs d'exécution pour les packages, exécutez et résolvez les problèmes relatifs aux packages, et gérez les opérations du serveur Integration Services .

Notes

Cet article décrit le catalogue SSIS de manière générale et le catalogue SSIS en cours d’exécution au niveau local. Vous pouvez également créer le catalogue SSIS dans Azure SQL Database, et déployer et exécuter des packages SSIS dans Azure. Pour plus d’informations, consultez Effectuer un « lift-and-shift » des charges de travail SQL Server Integration Services vers le cloud.

Bien que vous puissiez également exécuter des packages SSIS sur Linux, le catalogue SSIS n’est pas pris en charge sur Linux. Pour plus d’informations, consultez Extraire, transformer et charger des données sur Linux avec SSIS.

Les objets stockés dans le catalogue SSISDB sont les projets, les packages, les paramètres, les environnements et l'historique opérationnel.

Vous inspectez les objets, les paramètres et les données opérationnelles stockés dans le catalogue SSISDB en interrogeant les vues de la base de données SSISDB . Vous gérez des objets en appelant des procédures stockées situées dans la base de données SSISDB ou à l'aide de l'interface utilisateur du catalogue SSISDB . Dans de nombreux cas, la même tâche peut être effectuée dans l'interface utilisateur ou en appelant une procédure stockée.

Pour maintenir la base de données SSISDB , il est recommandé d'appliquer des stratégies d'entreprise standard pour la gestion des bases de données utilisateur. Pour plus d'informations sur la création de plans de maintenance, consultez Maintenance Plans.

Le catalogue SSISDB et la base de données SSISDB prennent en charge Windows PowerShell. Pour plus d'informations sur l'utilisation de SQL Server avec Windows PowerShell, consultez SQL Server PowerShell. Pour des exemples d'utilisation de Windows PowerShell pour exécuter des tâches telles que le déploiement d'un projet, consultez l'entrée de blog SSIS et PowerShell dans SQL Server 2012, sur blogs.msdn.com.

Pour plus d’informations sur l’affichage des données opérationnelles, consultez Surveiller les packages en cours d’exécution et autres opérations.

Vous accédez au catalogue SSISDB de SQL Server Management Studio en vous connectant au moteur de base de données de SQL Server , puis en développant le nœud Catalogues Integration Services dans l'Explorateur d'objets. Vous accédez à la base de données SSISDB de SQL Server Management Studio en développant le nœud Bases de données dans l'Explorateur d'objets.

Notes

Vous ne pouvez pas renommer la base de données SSISDB .

Notes

Si l'instance SQL Server à laque la base de données SSISDB est rattachée s'arrête ou ne répond pas, le processus ISServerExec.exe prend fin. Un message est écrit dans un journal des événements Windows.

Si les ressources SQL Server basculent dans le cadre d’un basculement de cluster, les packages en cours d’exécution ne redémarrent pas. Vous pouvez utiliser les points de contrôle pour redémarrer les packages. Pour plus d'informations, consultez Redémarrer des packages à l'aide de points de contrôle.

Fonctionnalités et capacités

Identificateurs d'objets de catalogue

Lorsque vous créez un objet dans le catalogue, attribuez-lui un nom. Ce nom constitue l'identificateur de l'objet. SQL Server définit des règles quant aux caractères pouvant être utilisés dans un identificateur. Les noms des objets suivants doivent respecter les règles liées aux identificateurs.

  • Dossier

  • Projet

  • Environnement

  • Paramètre

  • Variable d’environnement

Dossier, projet, environnement

Lorsque vous renommez un dossier, un projet ou un environnement, respectez les règles suivantes.

  • Les caractères non valides sont les caractères ASCII/Unicode de 1 à 31, les guillemets ("), le signe inférieur à (<), le signe supérieur à (>), la barre verticale (|), le retour arrière (\b), la valeur null (\0) et la tabulation (\t).

  • Le nom ne peut pas contenir d'espaces de début ni de fin.

  • @ ne doit pas être utilisé comme premier caractère, mais il peut l'être par la suite.

  • La longueur du nom doit être supérieure à 0 et inférieure ou égale à 128.

Paramètre

Lorsque vous affectez un nom à un paramètre, respectez les règles suivantes :

  • Le premier caractère du nom doit être une lettre, ainsi que défini dans la norme Unicode 2.0, ou un trait de soulignement (_).

  • Les caractères suivants peuvent être des lettres ou des nombres conformément à la norme Unicode 2.0, ou un trait de soulignement (_).

Variable d’environnement

Lorsque vous attribuez un nom à une variable d'environnement, respectez les règles suivantes :

  • Les caractères non valides sont les caractères ASCII/Unicode de 1 à 31, les guillemets ("), le signe inférieur à (<), le signe supérieur à (>), la barre verticale (|), le retour arrière (\b), la valeur null (\0) et la tabulation (\t).

  • Le nom ne peut pas contenir d'espaces de début ni de fin.

  • @ ne doit pas être utilisé comme premier caractère, mais il peut l'être par la suite.

  • La longueur du nom doit être supérieure à 0 et inférieure ou égale à 128.

  • Le premier caractère du nom doit être une lettre, ainsi que défini dans la norme Unicode 2.0, ou un trait de soulignement (_).

  • Les caractères suivants peuvent être des lettres ou des nombres conformément à la norme Unicode 2.0, ou un trait de soulignement (_).

Configuration du catalogue

Pour définir précisément le comportement du catalogue, vous devez ajuster ses propriétés. Les propriétés du catalogue définissent la façon dont les données sensibles sont chiffrées, ainsi que la façon dont les opérations et les données du contrôle de version des projets sont conservées. Pour définir les propriétés du catalogue, utilisez la boîte de dialogue Propriétés du catalogue ou appelez la procédure stockée catalog.configure_catalog (base de données SSISDB). Pour voir les propriétés, utilisez la boîte de dialogue ou interrogez catalog.configure_catalog (base de données SSISDB). Vous pouvez accéder à cette boîte de dialogue en cliquant avec le bouton droit sur SSISDB dans l’Explorateur d’objets.

Nettoyage des opérations et des versions de projet

Les données d'état de nombreuses opérations du catalogue sont stockées dans des tables de base de données internes. Ainsi, le catalogue effectue le suivi de l'état des exécutions de packages et des déploiements de projets. Pour limiter la taille des données opérationnelles, le travail de maintenance de serveur SSIS dans SQL Server Management Studio est utilisé pour supprimer les anciennes données. Ce travail de l'Agent SQL Server est créé lors de l'installation de Integration Services .

Vous pouvez mettre à jour ou redéployer un projet Integration Services en le déployant avec le même nom dans le même dossier du catalogue. Par défaut, chaque fois que vous redéployez un projet, le catalogue SSISDB en conserve la version précédente. Pour limiter la taille des données opérationnelles, le travail de maintenance de serveur SSIS est utilisé pour supprimer les anciennes versions des projets.

Pour exécuter le travail de maintenance du serveur SSIS, SSIS crée la connexion SQL Server ##MS_SSISServerCleanupJobLogin## . Cette connexion est réservée à un usage interne par SSIS.

Les propriétés de catalogue SSISDB suivantes définissent le comportement de ce travail de l'Agent SQL Server . Vous pouvez voir et modifier les propriétés en utilisant la boîte de dialogue Propriétés du catalogue ou catalog.catalog_properties (base de données SSISDB) et catalog.configure_catalog (base de données SSISDB).

Nettoyer les journaux régulièrement
L’étape de travail de nettoyage des opérations s’exécute quand cette propriété a la valeur True.

Période de rétention (jours)
Définit l'âge maximal des données opérationnelles autorisées (en jours). Les données plus anciennes sont supprimées.

La valeur minimale est de un jour. La valeur maximale est limitée uniquement par la valeur maximale des données SQL Server int. Pour plus d’informations sur ce type de données, consultez int, bigint, smallint et tinyint (Transact-SQL).

Supprimer régulièrement les anciennes versions
L’étape de travail de nettoyage des versions de projet s’exécute quand cette propriété a la valeur True.

Nombre maximal de versions par projet
Définit le nombre de versions d’un projet stockées dans le catalogue. Les versions antérieures des projets sont supprimées.

Algorithme de chiffrement

La propriété Algorithme de chiffrement spécifie le type de chiffrement utilisé pour chiffrer les valeurs des paramètres sensibles. Vous pouvez faire votre choix parmi les types de chiffrement suivants.

  • AES_256 (par défaut)

  • AES_192

  • AES_128

  • DESX

  • TRIPLE_DES_3KEY

  • TRIPLE_DES

  • DES

Lorsque vous déployez un projet Integration Services sur le serveur Integration Services , le catalogue chiffre automatiquement les données du package et les valeurs sensibles. Le catalogue déchiffre automatiquement les données lorsque vous les récupérez. Le catalogue SSISDB utilise le niveau de protection ServerStorage . Pour plus d'informations, consultez Access Control for Sensitive Data in Packages.

La modification de l'algorithme de chiffrement est une opération qui prend du temps. Tout d'abord, le serveur doit utiliser l'algorithme précédemment spécifié pour déchiffrer toutes les valeurs de configuration. Le serveur doit ensuite utiliser le nouvel algorithme pour ré-chiffrer les valeurs. Pendant ce temps, aucune autre opération Integration Services ne peut être effectuée sur le serveur. Ainsi, pour permettre aux opérations Integration Services de continuer de façon ininterrompue, l’algorithme de chiffrement est une valeur en lecture seule dans la boîte de dialogue de Management Studio.

Pour modifier le paramètre de la propriété Algorithme de chiffrement , définissez la base de données SSISDB en mode mono-utilisateur, puis appelez la procédure stockée catalog.configure_catalog. Utilisez ENCRYPTION_ALGORITHM pour l’argument property_name . Pour connaître les valeurs de propriétés prises en charge, consultez catalog.catalog_properties (base de données SSISDB). Pour plus d’informations sur la procédure stockée, consultez catalog.configure_catalog (base de données SSISDB).

Pour plus d’informations sur le mode mono-utilisateur, consultez Définir une base de données en mode mono-utilisateur. Pour plus d’informations sur le chiffrement et les algorithmes de chiffrement dans SQL Server, consultez les rubriques de la section Chiffrement SQL Server.

Une clé principale de base de données est utilisée pour le chiffrement. La clé est générée lorsque vous créez le catalogue.

Le tableau suivant répertorie les noms des propriétés apparaissant dans la boîte de dialogue Propriétés du catalogue et les propriétés correspondantes en vue de base de données.

Nom de la propriété (boîte de dialoguePropriétés du catalogue ) Nom de la propriété (vue de base de données)
Nom de l'algorithme de chiffrement ENCRYPTION_ALGORITHM
Nettoyer les journaux régulièrement OPERATION_CLEANUP_ENABLED​
Période de rétention (jours) RETENTION_WINDOW
Supprimer régulièrement les anciennes versions VERSION_CLEANUP_ENABLED
Nombre maximal de versions par projet MAX_PROJECT_VERSIONS
Niveau d'enregistrement par défaut au niveau du serveur SERVER_LOGGING_LEVEL

Autorisations

Les projets, les environnements et les packages sont contenus dans des dossiers qui sont des objets sécurisables. Vous pouvez accorder des autorisations à un dossier, notamment l'autorisation de MANAGE_OBJECT_PERMISSIONS. MANAGE_OBJECT_PERMISSIONS vous permet de déléguer l'administration du contenu du dossier à un utilisateur sans avoir à accorder à celui-ci l'appartenance au rôle ssis_admin. Vous pouvez également accorder des autorisations relatives à des projets, des environnements et des opérations. Les opérations incluent l'initialisation de Integration Services, le déploiement de projets, la création et le démarrage d'exécutions, la validation de projets et de packages, ainsi que la configuration du catalogue SSISDB .

Pour plus d’informations sur les rôles de base de données, consultez Rôles au niveau de la base de données.

Le catalogue SSISDB utilise un déclencheur DDL, ddl_cleanup_object_permissions, pour appliquer l'intégrité des informations d'autorisations sur les éléments sécurisables SSIS. Le déclencheur est activé lorsqu'un principal de base de données, tel qu'un utilisateur de base de données, un rôle de base de données ou un rôle d'application de base de données, est supprimé de la base de données SSISDB.

Si le principal a accordé ou refusé des autorisations à d'autres principaux, révoquez les autorisations données par le fournisseur d'autorisations, avant que le principal puisse être supprimé. Sinon, un message d'erreur est retourné lorsque le système essaie de supprimer le principal. Le déclencheur supprime tous les enregistrements d'autorisation dans lesquels le principal de la base de données est un bénéficiaire.

Il est recommandé de ne pas désactiver le déclencheur car il garantit qu'il n'existe aucun enregistrement d'autorisation orphelin après la suppression d'un principal de la base de données SSISDB .

Gestion des autorisations

Vous pouvez gérer les autorisations à l’aide de l’interface utilisateur de SQL Server Management Studio , des procédures stockées et de l’espace de noms Microsoft.SqlServer.Management.IntegrationServices .

Pour gérer les autorisations à l’aide de l’IU de SQL Server Management Studio, servez-vous des boîtes de dialogue suivantes :

Pour gérer les autorisations en utilisant Transact-SQL, appelez catalog.grant_permission (base de données SSISDB), catalog.deny_permission (base de données SSISDB) et catalog.revoke_permission (base de données SSISDB). Pour voir les autorisations effectives du principal actuel pour tous les objets, interrogez catalog.effective_object_permissions (base de données SSISDB). Cette rubrique fournit les descriptions des différents types d'autorisations. Pour voir les autorisations attribuées explicitement à l’utilisateur, interrogez catalog.explicit_object_permissions (base de données SSISDB).

Dossiers

Un dossier contient un ou plusieurs projets et environnements du catalogue SSISDB . Vous pouvez utiliser la vue catalog.folders (base de données SSISDB) pour accéder aux informations sur les dossiers du catalogue. Vous pouvez utiliser les procédures stockées suivantes pour gérer les dossiers :

Projets et packages

Chaque projet peut contenir plusieurs packages. Les projets et les packages peuvent contenir des paramètres et des références aux environnements. Vous pouvez accéder aux paramètres et aux références d'environnement à l'aide de la Configure Dialog Box.

Vous pouvez effectuer d’autres tâches de projet en appelant les procédures stockées suivantes :

Ces vues fournissent des détails sur les packages, les projets et les versions des projets.

Paramètres

Vous utilisez des paramètres pour affecter des valeurs aux propriétés des packages au moment de l'exécution de ces packages. Pour définir la valeur d’un package ou d’un paramètre de projet, et pour effacer la valeur, appelez catalog.set_object_parameter_value (base de données SSISDB) et catalog.clear_object_parameter_value (base de données SSISDB). Pour définir la valeur d’un paramètre d’une instance d’exécution, appelez catalog.set_execution_parameter_value (base de données SSISDB). Vous pouvez récupérer les valeurs des paramètres par défaut en appelant catalog.get_parameter_values (base de données SSISDB).

Ces vues affichent les paramètres de tous les packages et projets, ainsi que les valeurs de paramètre utilisées pour une instance d'exécution.

Environnements serveur, variables de serveur et références d'environnement serveur

Les environnements serveur contiennent des variables de serveur. Les valeurs des variables peuvent être utilisées lorsqu'un package est exécuté ou validé sur le serveur Integration Services .

Les procédures stockées suivantes vous permettent d'exécuter de nombreuses autres tâches de gestion sur les environnements et les variables.

En appelant la procédure stockée catalog.set_environment_variable_protection (base de données SSISDB), vous pouvez définir le bit de sensibilité d’une variable.

Pour utiliser la valeur d'une variable de serveur, spécifiez la référence entre le projet et l'environnement serveur. Vous pouvez utiliser les procédures stockées suivantes pour créer et supprimer des références. Vous pouvez également indiquer si l'environnement peut se trouver dans le même dossier que le projet ou dans un dossier différent.

Pour plus de détails sur les environnements et les variables, interrogez ces vues.

Exécutions et validations

Une exécution est une instance d'une exécution de package. Appelez catalog.create_execution (base de données SSISDB) et catalog.start_execution (base de données SSISDB) pour créer et démarrer une exécution. Pour arrêter une exécution ou une validation de package/projet, appelez catalog.stop_operation (base de données SSISDB).

Pour suspendre un package en cours d'exécution et créer un fichier de vidage, appelez la procédure stockée catalog.create_execution_dump. Le fichier de vidage fournit des informations sur l'exécution d'un package, ce qui peut vous aider à résoudre les problèmes d'exécution. Pour plus d'informations sur la génération et la configuration de fichiers de vidage, consultez Generating Dump Files for Package Execution.

Pour plus de détails sur les exécutions, les validations et les messages enregistrés pendant les opérations, ainsi que pour obtenir des informations contextuelles sur les erreurs, interrogez ces vues.

Vous pouvez valider des projets et des packages en appelant les procédures stockées catalog.validate_project (base de données SSISDB) et catalog.validate_package (base de données SSISDB). La vue catalog.validations (base de données SSISDB) fournit des informations sur les validations, comme les références d’environnement de serveur prises en compte dans la validation, s’il s’agit d’une validation des dépendances ou d’une validation complète, et si le runtime 32 bits ou 64 bits est utilisé pour exécuter le package.

Créer le catalogue SSIS

Après avoir conçu et testé des packages dans SQL Server Data Tools, vous pouvez déployer les projets qui contiennent les packages sur un serveur Integration Services . Avant cela, le serveur Integration Services doit contenir le catalogue SSISDB . Le programme d'installation de SQL Server 2012 (11.x) ne crée pas automatiquement le catalogue ; vous devez le créer manuellement à l'aide des instructions suivantes.

Vous pouvez créer le catalogue SSISDB dans SQL Server Management Studio. Vous pouvez également créer le catalogue par programmation en utilisant Windows PowerShell.

Pour créer le catalogue SSISDB dans SQL Server Management Studio

  1. Ouvrez SQL Server Management Studio.

  2. Connectez-vous au moteur de base de données SQL Server .

  3. Dans l’Explorateur d’objets, développez le nœud du serveur, cliquez avec le bouton droit sur le nœud Catalogues Integration Services , puis cliquez sur Créer un catalogue.

  4. Cliquez sur Activer l'intégration du CLR.

    Le catalogue utilise des procédures stockées du CLR.

  5. Cliquez sur Activer l’exécution automatique des procédures stockées Integration Services au démarrage de SQL Server pour permettre à la procédure stockée catalog.startup de s’exécuter à chaque redémarrage de l’instance de serveur SSIS .

    La procédure stockée effectue la maintenance de l'état des opérations pour le catalogue SSISDB. Elle corrige l’état des packages en cours d’exécution si l’instance de serveur SSIS s’arrête.

  6. Entrez un mot de passe, puis cliquez sur OK.

    Le mot de passe protège la clé principale de la base de données utilisée pour le chiffrement des données du catalogue. Enregistrez le mot de passe dans un emplacement sécurisé. Il est également recommandé de sauvegarder la clé principale de base de données. Pour plus d'informations, consultez Back Up a Database Master Key.

Pour créer le catalogue SSISDB par programmation

  1. Exécutez le script PowerShell suivant :

    # 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()

    Vous trouverez d’autres exemples d’utilisation de Windows PowerShell et de l’espace de noms Microsoft.SqlServer.Management.IntegrationServices dans l’entrée de blog SSIS et PowerShell dans SQL Server 2012, sur blogs.msdn.com. Pour obtenir une vue d'ensemble de l'espace de noms et des exemples de code, consultez l'entrée de blog, A Glimpse of the SSIS Catalog Managed Object Model, sur blogs.msdn.com.

Boîte de dialogue Propriétés du catalogue

Utilisez la boîte de dialogue Propriétés du catalogue pour configurer le catalogue SSISDB. Les propriétés de catalogue définissent la façon dont les données sensibles sont chiffrées, la façon dont les opérations et les données du contrôle de version du projet sont conservées et le délai d'attente des opérations de validation. Le catalogue SSISDB est une base de données qui représente le point d’administration et de stockage central pour les projets, les packages, les paramètres et les environnements Integration Services .

Vous pouvez également consulter les propriétés de catalogue dans la vue catalog.catalog_properties et les définir à l’aide de la procédure stockée catalog.configure_catalog. Pour plus d’informations, consultez catalog.catalog_properties (base de données SSISDB) et catalog.configure_catalog (base de données SSISDB).

Que voulez-vous faire ?

Ouvrez la boîte de dialogue Propriétés du catalogue.

  1. Ouvrez SQL Server Management Studio.

  2. Connectez-vous au moteur de base de données Microsoft SQL Server.

  3. Dans l’Explorateur d’objets, développez le nœud Integration Services , cliquez avec le bouton droit sur SSISDB, puis cliquez sur Propriétés.

Configurer les options

Options

Le tableau décrit certaines propriétés de la boîte de dialogue et les propriétés correspondantes dans la vue catalog.catalog_properties.

Nom de la propriété (boîte de dialogue Propriétés du catalogue) Nom de la propriété (vue catalog.catalog_properties) Description
Nom de l'algorithme de chiffrement ENCRYPTION_ALGORITHM Spécifie le type de chiffrement utilisé pour chiffrer les valeurs des paramètres sensibles dans le catalogue. Les valeurs possibles sont les suivantes :

DES

TRIPLE_DES

TRIPLE_DES_3KEY

DESPX

AES_128

AES_192

AES_256 (par défaut)
Nombre maximal de versions par projet MAX_PROJECT_VERSIONS Indiquez combien de versions d’un projet sont stockées dans le catalogue. Les versions antérieures des projets qui dépassent la limite maximale autorisée sont supprimées lors de l’exécution du travail de nettoyage des versions de projets.
Nettoyer les journaux régulièrement OPERATION_CLEANUP_ENABLED Définissez la propriété sur True pour indiquer que le travail de l'Agent SQL Server, à savoir le nettoyage des opérations, doit être exécuté. Sinon, définissez la propriété sur False.
Période de rétention (jours) RETENTION_WINDOW Spécifiez l'âge maximal des données opérationnelles autorisées (en jours). Les données plus anciennes que le nombre de jours spécifié sont supprimées par le travail de l’Agent SQL, à savoir le nettoyage des opérations.

Sauvegarder, restaurer et déplacer le catalogue SSIS

S’applique à : SQL Server 2016 (13.x) et versions ultérieures Non pris en charge. Base de données Azure SQL Non pris en charge. Azure Synapse Analytics Non pris en charge. Analytics Platform System (PDW)

SQL Server 2019 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

  1. Ouvrez SQL Server Management Studio et connectez-vous à une instance de SQL Server.

  2. 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 l’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!'

  3. 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).

  4. Générez le script CREATE LOGIN pour ##MS_SSISServerCleanupJobLogin## en effectuant les actions suivantes. 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.

  5. Si vous restaurez 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 actions 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.

  6. Assurez-vous que Le SQL Server Agent a démarré.

  7. Si vous restaurez 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 SQL Server Agent quand 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 cliquez sur 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

  1. 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 l’option clr enabled.

    use master   
       sp_configure 'clr enabled', 1  
       reconfigure

  2. 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\YourSQLServerDefaultCompatibilityLevel\DTS\Binn\Microsoft.SqlServer.IntegrationServices.Server.dll'

    Vous pouvez trouver la valeur de YourSQLServerDefaultCompatibilityLevel dans une liste des niveaux de compatibilité par défaut de SQL Server.

    Integration Services Les procédures stockées CLR exigent 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##

  3. 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, voir les rubriques suivantes :

  4. Exécutez les scripts que vous avez créés dans la procédure Pour sauvegarder la base de données SSIS pour ##MS_SSISServerCleanupJobLogin##, sp_ssis_startup et le travail de maintenance de serveur SSIS. Assurez-vous que SQL Server Agent a démarré.

  5. Exécutez l’instruction suivante pour définir la procédure sp_ssis_startup d’exécution automatique. Pour plus d’informations, consultez sp_procoption (Transact-SQL).

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

  6. 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.

  7. 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

      Notes

      Assurez-vous que le compte de service SQL Server dispose des autorisations nécessaires pour lire le fichier de clé de sauvegarde.

      Notes

      Le message d’avertissement suivant s’affiche dans SQL Server Management Studio si la clé principale de la 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 la base de données n’a pas été ouverte sur l’instance où est restaurée la base de données, ce message s’affiche.

    • 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

  8. 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.

  9. 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 la page Exécuter des packages Integration Services (SSIS).

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 la base de données SSIS.

    Vérifiez 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éé.

Mettre à niveau le catalogue SSIS (SSISDB)

Exécutez l’Assistant Mise à niveau de SSISDB pour mettre à niveau la base de données du catalogue SSIS, SSISDB, quand celle-ci est plus ancienne que la version actuelle de l’instance SQL Server. La base de données peut être plus ancienne quand l’une des conditions suivantes est remplie.

  • Vous avez restauré la base de données à partir d’une ancienne version de SQL Server.

  • Vous n’avez pas supprimé la base de données d’un groupe de disponibilité Always On avant la mise à niveau de l’instance SQL Server. Cette condition empêche la mise à niveau automatique de la base de données. Pour plus d’informations, consultez Upgrading SSISDB in an availability group.

L’assistant peut uniquement mettre à niveau la base de données sur une instance de serveur local.

Mettre à niveau le catalogue SSIS (SSISDB) en exécutant l’Assistant Mise à niveau de SSISDB

  1. Sauvegardez la base de données de catalogues SSIS, SSISDB.

  2. Dans SQL Server Management Studio, développez le serveur local puis développez Catalogues Integration Services.

  3. Cliquez avec le bouton droit sur SSISDB, puis sélectionnez Mise à niveau de base de données pour lancer l’Assistant Mise à niveau de SSISDB. Vous pouvez aussi lancer l’Assistant Mise à niveau de SSISDB en exécutant C:\Program Files\Microsoft SQL Server\140\DTS\Binn\ISDBUpgradeWizard.exe avec des autorisations élevées sur le serveur local.

    Lancer l’Assistant Mise à niveau de SSISDB

  4. Sur la page Sélectionner une instance , sélectionnez une instance de SQL Server sur le serveur local.

    Important

    L’assistant peut uniquement mettre à niveau la base de données sur une instance de serveur local.

    Sélectionnez la case à cocher pour indiquer que vous avez sauvegardé la base de données SSISDB avant d’exécuter l’assistant.

    Sélectionner le serveur dans l’Assistant Mise à niveau de SSISDB

  5. Sélectionnez Mettre à niveau pour mettre à niveau la base de données du catalogue SSIS.

  6. Sur la page Résultat , passez en revue les résultats.

    Passer en revue les résultats de l’Assistant Mise à niveau de SSISDB

Always On pour le catalogue SSIS (SSISDB)

La fonctionnalité des groupes de disponibilité AlwaysOn est une solution de haute disponibilité et de récupération d’urgence qui offre une alternative au niveau de l’entreprise à la mise en miroir de bases de données. Un groupe de disponibilité prend en charge un environnement de basculement pour un ensemble discret de bases de données utilisateur, appelées bases de données de disponibilité, qui basculent de concert. Pour plus d’informations, consultez Groupes de disponibilité AlwaysOn.

Pour assurer la haute disponibilité du catalogue SSIS (base de données SSISDB) et de son contenu (projets, packages, journaux d’exécution, etc.), vous pouvez ajouter la base de données SSISDB (de la même façon que toute autre base de données utilisateur) à un groupe de disponibilité AlwaysOn. Quand un basculement se produit, le nœud secondaire devient automatiquement le nouveau nœud primaire.

Remarque

Les groupes de disponibilité contenus, qui ont été introduits dans SQL Server 2022, ne sont pas encore pris en charge.

Important

Quand un basculement se produit, les packages en cours d’exécution ne redémarrent pas ou ne reprennent pas.

Dans cette section :

  1. Composants requis

  2. Configurer la prise en charge de SSIS pour AlwaysOn

  3. Mise à niveau de la base de données SSISDB dans un groupe de disponibilité

Prérequis

Avant d’activer la prise en charge d’Always On pour la base de données SSISDB, effectuez les étapes suivantes.

  1. Configurez un cluster de basculement Windows. Pour obtenir des instructions, voir le billet de blog Installing the Failover Cluster Feature and Tools for Windows Server 2012 (Installation de la fonctionnalité de cluster de basculement et des outils pour Windows Server 2012). Installez la fonctionnalité et les outils sur tous les nœuds de cluster.

  2. Installez SQL Server 2016 avec la fonctionnalité Integration Services (SSIS) sur chaque nœud du cluster.

  3. Activez les groupes de disponibilité Always On pour chaque instance SQL Server. Pour plus d’informations, consultez Activer et désactiver les groupes de disponibilité AlwaysOn (SQL Server) .

Configurer la prise en charge de SSIS pour AlwaysOn

Important

  • Vous devez exécuter les étapes suivantes sur le nœud primaire du groupe de disponibilité.
  • Vous devez activer la prise en charge d’Always On par SSIS après avoir ajouté la base de données SSISDB à un groupe de disponibilité Always On.

Étape 1 : créer un catalogue Integration Services

  1. Lancez SQL Server Management Studio , puis connectez-vous à une instance SQL Server dans le cluster que vous voulez définir comme nœud primaire du groupe de disponibilité AlwaysOn pour la base de données SSISDB.

  2. Dans l’Explorateur d’objets, développez le nœud du serveur, cliquez avec le bouton droit sur le nœud Catalogues Integration Services , puis cliquez sur Créer un catalogue.

  3. Cliquez sur Activer l'intégration du CLR. Le catalogue utilise des procédures stockées du CLR.

  4. Cliquez sur Activer l’exécution automatique des procédures stockées Integration Services au démarrage de SQL Server pour permettre à la procédure stockée catalog.startup de s’exécuter à chaque redémarrage de l’instance de serveur SSIS . La procédure stockée effectue la maintenance de l'état des opérations pour le catalogue SSISDB. Elle résout l’état de tous les packages en cours d’exécution si et quand l’instance de serveur SSIS s’arrête.

  5. Entrez un mot de passe, puis cliquez sur OK. Le mot de passe protège la clé principale de la base de données utilisée pour le chiffrement des données du catalogue. Enregistrez le mot de passe dans un emplacement sécurisé. Il est également recommandé de sauvegarder la clé principale de base de données. Pour plus d'informations, consultez Back Up a Database Master Key.

Étape 2 : ajouter la base de données SSISDB à un groupe de disponibilité AlwaysOn

La procédure à suivre pour ajouter la base de données SSISDB à un groupe de disponibilité AlwaysOn est presque identique à celle qui permet d’ajouter n’importe quelle autre base de données utilisateur à un groupe de disponibilité. Voir Utiliser l’Assistant groupe de disponibilité.

Indiquez le mot de passe que vous avez spécifié durant la création du catalogue SSIS sur la page Sélectionner les bases de données de l’Assistant Nouveau groupe de disponibilité.

Nouveau groupe de disponibilité

Important

Pour éviter les problèmes liés à la clé principale après un basculement, utilisez la méthode Sauvegarde complète de la base de données et des journaux pour ajouter la base de données SSISDB au groupe de disponibilité Always On.

Étape 3 : activer la prise en charge de SSIS pour AlwaysOn

Après avoir créé le catalogue de services d’intégration, cliquez avec le bouton droit sur le nœud Catalogues de services d’intégration, puis cliquez sur Activer la prise en charge d’Always On. Vous devez voir la boîte de dialogue Activer la prise en charge d’Always On suivante. Si cette option de menu est désactivée, vérifiez que vous disposez de tous les composants requis, puis cliquez sur Actualiser.

Activer la prise en charge d’Always On

Avertissement

Le basculement automatique de la base de données SSISDB n’est pas pris en charge tant que vous n’activez pas la prise en charge de SSIS pour AlwaysOn.

Les réplicas secondaires récemment ajoutés à partir du groupe de disponibilité Always On apparaissent dans le tableau. Cliquez sur le bouton de connexion pour chaque réplica figurant dans la liste, puis entrez les informations d’identification d’authentification pour la connexion au réplica. Le compte d’utilisateur doit être membre du groupe sysadmin sur chaque réplica pour pouvoir activer la prise en charge d’Always On par SSIS. Une fois connecté à chaque réplica, cliquez sur OK pour activer la prise en charge de SSIS pour AlwaysOn.

Si l’option Activer la prise en charge d’Always On du menu contextuel semble désactivée une fois que vous avez rempli les autres prérequis, essayez d’effectuer les actions suivantes :

  1. Actualisez le menu contextuel en cliquant sur l’option Actualiser.
  2. Vérifiez que vous vous connectez au nœud principal. Vous devez activer la prise en charge d’Always On sur le nœud principal.
  3. Vérifiez que la version de SQL Server est au moins égale à 13.0. SSIS prend en charge Always On uniquement sur SQL Server 2016 et les versions ultérieures.

Mise à niveau de la base de données SSISDB dans un groupe de disponibilité

Si vous mettez à niveau SQL Server à partir d’une version précédente et si la base de données SSISDB se trouve dans un groupe de disponibilité AlwaysOn, la mise à niveau peut être bloquée par la règle « Vérification : SSISDB est dans des groupes de disponibilité AlwaysOn ». Ce blocage se produit parce que la mise à niveau s’exécute en mode mono-utilisateur, alors qu’une base de données de disponibilité doit être une base de données multi-utilisateurs. Par conséquent, durant une mise à niveau ou une mise à jour corrective, toutes les bases de données de disponibilité, y compris la base de données SSISDB, sont mises hors connexion, et ne sont pas mises à niveau ou corrigées. Pour permettre la poursuite de la mise à niveau, supprimez la base de données SSISDB du groupe de disponibilité, mettez à niveau ou corrigez chaque nœud, puis rajoutez la base de données SSISDB au groupe de disponibilité.

Si la règle « Vérification : SSISDB dans un groupe de disponibilité Always On » vous bloque, mettez à niveau SQL Server en effectuant les étapes suivantes.

  1. Supprimez la base de données SSISDB du groupe de disponibilité. Pour plus d’informations, consultez Supprimer une base de données secondaire d’un groupe de disponibilité (SQL Server) et Supprimer une base de données primaire d’un groupe de disponibilité (SQL Server).

  2. Cliquez sur Réexécuter dans l’Assistant Mise à niveau. La règle « Vérification : SSISDB dans un groupe de disponibilité Always On » ne bloque plus.

  3. Cliquez sur Suivant pour continuer la mise à niveau.

  4. Une fois tous les nœuds mis à niveau, ajoutez la base de données SSISDB au groupe de disponibilité AlwaysOn. Pour plus d’informations, consultez Ajouter une base de données à un groupe de disponibilité (SQL Server).

Si rien ne vous bloque pendant la mise à niveau de SQL Server et que la base de données SSISDB se trouve dans un groupe de disponibilité Always On, mettez à niveau la base de données SSISDB séparément après avoir mis à niveau le moteur de base de données SQL Server. Utilisez l’Assistant Mise à niveau de SSIS pour mettre à niveau la base de données SSISDB comme décrit dans la procédure suivante.

  1. Déplacez la base de données SSISDB hors du groupe de disponibilité, ou supprimez le groupe de disponibilité si la base de données SSISDB est la seule base de données figurant dans le groupe de disponibilité. Pour effectuer cette tâche, lancez SQL Server Management Studio sur le nœud principal du groupe de disponibilité.

  2. Supprimez la base de données SSISDB de tous les nœuds de réplica.

  3. Mettez à niveau la base de données SSISDB sur le nœud primaire. Dansl’Explorateur d’objets de SQL Server Management Studio, développez Catalogues Integration Services, cliquez avec le bouton droit sur SSISDB, puis sélectionnez Mise à niveau de la base de données. Suivez les instructions de l’ Assistant Mise à niveau de SSISDB pour mettre à niveau la base de données. Lancez l’Assistant Mise à niveau de SSIDB localement sur le nœud principal.

  4. Suivez les instructions de l’étape 2 : Ajouter la base de données SSISDB à un groupe de disponibilité Always On pour rajouter la base de données SSISDB à un groupe de disponibilité.

  5. Suivez les instructions de l’étape 3 : Activer la prise en charge de SSIS pour Always On.

Catalogue et délégation SSISDB dans les scénarios à double tronçon

Par défaut, l’appel à distance des packages SSIS stockés dans le catalogue SSISDB ne prend pas en charge la délégation des informations d’identification, parfois appelée double tronçon.

Imaginez un scénario dans lequel un utilisateur se connecte à la machine cliente A et lance SQL Server Management Studio (SSMS). À partir de SSMS, l'utilisateur se connecte à un serveur SQL Server hébergé sur la machine B, qui a le catalogue SSISDB. Le package SSIS est stocké sous ce catalogue SSISDB et le package se connecte à son tour à un service SQL Server qui s’exécute sur la machine C (le package peut également accéder à d’autres services). Quand l’utilisateur appelle l’exécution du package SSIS à partir de la machine A, SSMS passe d’abord les informations d’identification de l’utilisateur de la machine A à la machine B (où le processus de runtime SSIS exécute le package). Le processus de runtime d’exécution SSIS (ISServerExec.exe) est maintenant nécessaire pour déléguer les informations d’identification de l’utilisateur de la machine B à la machine C pour que l’exécution s’effectue correctement. Cependant, la délégation des informations d’identification n’est pas activée par défaut.

Un utilisateur peut activer la délégation des informations d’identification en octroyant le droit Approuver cet utilisateur pour la délégation à tous les services (Kerberos uniquement) au compte de service SQL Server (sur la machine B), qui lance ISServerExec.exe en tant que processus enfant. Ce processus est appelé « configuration d’une délégation non contrainte » ou « délégation ouverte » pour un compte de service SQL Server. Avant d’octroyer ce droit, demandez-vous s’il est conforme aux exigences de sécurité de votre organisation.

SSISDB ne prend pas en charge la délégation contrainte. Dans un environnement à double tronçon, si le compte de service du serveur SQL Server qui héberge le catalogue SSISDB (la machine B dans notre exemple) est configuré pour la délégation contrainte, ISServerExec.exe ne peut pas déléguer les informations d'identification à la troisième machine (la machine C). Ceci s’applique aux scénarios dans lesquels Windows Credential Guard est activé, qui nécessite impérativement la configuration de la délégation contrainte.

Contenu associé