La base SSISDB est une base qui stocke tous les projets Integration services, les packages et leur configuration, les paramètres, les environnements et l’historique des exécutions.

Dans cet article, tous les aspects liés à cette base seront abordés dans le cadre d’un cluster Always On 2 nœuds sur Azure.

 

1. Pré-Requis

• Cluster Windows installé et configuré correctement entre les 2 nœuds
• Instances SQL Server installées et configurées sur les 2 nœuds
• Installation SQL Server Integration Services sur les 2 nœuds
• Groupe de disponibilité défini (New Availability Group)

 

2. Installation catalogue SSIS

L’installation SSIS doit être faite sur les 2 nœuds et le catalogue SSIS doit être configuré sur les 2 nœuds.

Sur la première instance SQL Server (nœud primaire node1) suivre les étapes suivantes :

	Depuis SSMS, aller sur « Integration Services Catalog »   Ensuite : « create catalog »
	– Enable CLR intégrations – affiche le nom du catalogue : SSISDB Il possible de modifier le nom de cette base – renseigner le mot de passe pour l’encryption de clé base de données (Database Master Key), qui protège les données sensibles Clic : OK
	La création du catalogue SSIS et de la base SSISDB sont OK
	Clic droit sur le catalog SSIS On peut voir l’algorithme de cryptage : AES_256
	Rajouter la base SSISDB dans le groupe de disponibilité (Availability Group) existant
	Sélectionner la base SSISDB Renseigner le mot de passe défini précédemment lors de la création du catalogue SSIS et faire un refresh Le statut change à «  Meets prerequisites »
	Renseigner le réplica et se connecter
	Ensuite, il faut configurer le mode de synchronisation de la base de données – Automatic seeding : crée automatiquement une copie de la SSISDB sur le nœud secondaire et synchronise avec le primaire. – Full and Log backup : renseigne le chemin partage réseau pour que SQL Server fasse un backup et le restaure automatiquement. – Join Only : Un backup/restore est déjà fait manuellement, il faut juste mapper les bases dans l’AG La plupart du temps, « Automatic seeding est choisi », c’est mieux de choisir « Full database and log backup » Vous comprendrez pourquoi dans la suite de l’article
	Se connecter sur le premier réplica et activer le support AlwaysOn
SSIS Failover Monitor Job SSIS Server Maintenance Job	Ce qui permet de créer les 2 jobs ci-contre

 

3. Utilisation de SSIS sur le cluster Always On

Maintenant que la base SSIS est dans un groupe de disponibilité Always On, elle est en haute disponibilité et devient donc up et running tout le temps.

Il est possible de déployer les packages SSIS sur le catalogue du nœud primaire (node1) et les voir répliqués sur le nœud secondaire (node2).

Et si on faisait un test de bascule : je perds le nœud primaire (node1), le nœud secondaire (node2) prend le relais et devient à son tour primaire.

Lors du lancement des packages, il est possible de rencontrer l’erreur suivante :

« Please create a master key in the database or open the master key in the session before performing this operation”.

Voyons voir quelques définitions liées au problème rencontré.

3.1. C’est quoi la DMK (Database Master Key) ?

La clé d’encryption de base (DMK : Database Master Key) est utilisée pour protéger certaines colonnes dans la base de données SSISDB (tels que les mots de passes des chaines de connexions). Cette DMK est protégée par la clé d’encryption serveur (SMK : Server Master Key).

La DMK est ouverte à chaque fois tant que le service SQL tourne avec la même SMK sur les 2 nœuds.

Pour permettre ce décryptage automatique de la DMK, une copie de la DMK est encryptée en utilisant la SMK, cette copie est stockée dans les 2 bases utilisées et dans la base master (la clé stockée dans la master est misz à jour de manière silencieuse à chaque mise à jour de la DMK).

Cependant, si la DMK n’est pas encryptée par la DMS, il sera nécessaire de l’ouvrir en utilisant la commande « OPEN MASTER KEY » et le mot de passe.

Ceci implique d’avoir la même SMK sur les 2 nœuds pour que le failover soit transparent (ceci équivaut à avoir le même SID des logins sur tous les réplicas Always On).

La DMK est nécessaire aussi lors de la restauration de la base SSISDB sur un autre serveur.

 

3.2. C’est quoi le SMK (Service Master Key) ?

Il est automatiquement généré quand il est nécessaire afin de protéger la DMK. Le SMK est encrypté en utilisant la clé locale de la machine ou bien API (Windows Data Protection). Cette API utilise la clé dérivée du compte de service de SQL Server (compte Windows).

SQL Server 2012 et supérieur utilisent l’algorithme d’encryption AES pour protéger la SMK et la DMK.

 

3.3. Explication de la problématique

Pour utiliser SSIS dans Always On, il faut rajouter le catalogue SSIS dans les groupes de disponibilités et déployer ensuite les packages SSIS sur ce catalogue SSISDB.

Quand un failover se produit, le nœud secondaire devient automatiquement le nœud primaire.

L’exécution des packages SSIS doit continuer le plus normalement possible, la bascule doit être transparente dans le meilleur des mondes.

Mais on n’est pas à l’abri de surprises, raison pour laquelle des tests failover sont planifiés tous les ans, pour ne pas avoir ces surprises lors d’un vrai failover.

L’une des surprises est donc ce fameux message d’erreur «Please create a master key in the database or open the master key in the session before performing this operation”.

 

3.4. Résolution – solution de contournement

Pour résoudre l’erreur remontée, la Database Master Key a besoin d’être ré-encryptée par le service Master Key après le failover.

Il faut donc re encrypter la Database Master Key en exécutant la commande suivante :

USE SSISDB

OPEN master Key decryption by password = 'x' -- Password used when creating SSISDB

ALTER Master Key ADD encryption by Service Master Key

Une fois la Database Master Key est ré-encryptée, le nouveau nœud primaire est capable d’exécuter les packages SSIS.

 

Qu’en est-il si la personne qui a installé le SSIS n’est plus là et n’a pas laissé les informations nécessaires pour la clé d’encryption ou bien tout simplement ne l’a pas enregistré quelque part ?

Dans ce cas, on peut faire un backup de la clé du nœud primaire (devenu secondaire après le failover) en utiliser la commande suivante :

backup master key to file = 'C:\temp\masterkey' encryption by password = xxxxxxx

Copier cette clé sur le serveur devenu primaire et exécuter la commande suivante :

Restore master key from file ='C:\temp\masterkey'  Decryption by password = xxxxxxx Encryption by password = xxxxxxxx force

La clé protège le backup.

 

Il y a la possibilité aussi de réinitialiser le mot de passe de la clé d’encryption, mais il faut les droits admins sur l’instance, en exécutant la commande suivante :

USE SSISDB

ALTER MASTER KEY REGENERATE WITh ENCRYPTION BY PASSWORD 'xxxxxxx'

Cependant, exécuter ces scripts à chaque failover du cluster n’est vraiment idéal.

 

3.5. Résolution – solution définitive

Il existe une solution pérenne pour résoudre ce problème de Database Master Key à ré-encrypter :

Lors du rajout de la base SSISDB dans le groupe de disponibilité AlwaysOn,

Lors de l’étape : « Select Initial Data Synchronization »

Il faut choisir « Full database and log backup » comme mode de synchronisation au lieu du « Automatic Seeding »

	Commencer à synchroniser la base SSISDB Renseigner le mot de passe
	Se connecter au réplica
	Choisir « Full database and log backup » comme mode de synchronisation Renseigner un partage dans lequel un backup de la base node1 est fait, ce backup sera restauré sur le node2
	Validation des étapes de synchronisation
	Un sommaire est résumé
	Le résultat de toutes les étapes utilisées pour la synchronisation de la base SSISDB

 

Conclusion

Dans cet article, nous avons configuré le catalogue SSIS sur un cluster Always On dans un groupe de disponibilité et résolu des erreurs liées au Database Master Key.

Maintenant, les deux nœuds sont fonctionnels et la bascule se fait automatiquement tout en permettant une exécution iso des packages SSIS en toute transparence.