Cet article technique décrit comment construire une solution haute disponibilité (HA) pour SQL Server Data Quality Services (DQS) en s’appuyant sur les groupes de disponibilité Always On (AG) pour SQL Server 2017.

Cette procédure doit pouvoir s’appliquer à toutes les version SQL Server depuis SQL Server 2012. La plateforme utilisée pour valider cet article est SQL Server 2017.

SQL Server Data Quality n’est pas traité dans l’article, pour en apprendre plus sur SQL Server Data Quality :

• Introduction to Data Quality Services

Introduction

La fonctionnalité Always On Availability Groups (AG) optimisent la disponibilité des bases de données utilisateur. Cette solution assure qu’a tout instant les données sont répliquées sur les réplicas secondaires. En cas de défaillance du nœud physique primaire, un des réplicas secondaires prend le relais et devient primaire.

Cette solution est extrêmement souple et simple à mettre en œuvre. Cependant, contrairement à un cluster de basculement d’instance (FCI) ou l’unité de basculement est l’instance SQL entière, ici la haute disponibilité est de niveau bases de données utilisateur. Pour cette raison, ajouter les bases de données de SQL Server Data Quality (DQS) à un groupe de disponibilité Always On (AG) ne suffit pas à assurer la haute disponibilité de DQS :

• Les bases de données DQS_MAIN et DQS_PROJECT ne contiennent pas uniquement des objet de bases de données simples (tables et autres procédures stockées), mais aussi des assemblies et autres objets complexes.
• Lors de l’installation de DQS des nombreux objets serveurs sont créée.

Plus de détails :

• Windows Server Failover Clustering with SQL Server
• Always On Failover Cluster Instances (FCI)
• Always On availability groups

L’ajout des bases de données de DQS à un AG nécessite des configurations supplémentaires pour assurer la haute disponibilité du service DQS. Cet article décrit l’installation et la configuration nécessaire pour rendre DQS hautement disponible et près pour une récupération après sinistre.

Limitations

Si les bases de données de DQS sont rendues hautement disponibles avec l’utilisation des AG, incluant les bases de connaissance – knowledge base (KB) – et les projets – project –, le basculement des tâches actives n’est pas pris en charge. Lors du basculement, toutes les tâches de knowledge discovery, cleansing, ou de matching échoueront. Elles devront être relancées.

Prérequis

Cet article nécessite des connaissances avancées concernant la haute disponibilité, la récupération après sinistre et les concepts associés. Il ne décrit pas non plus l’installation et la configuration d’un cluster de basculement, ni d’une instance SQL Server pour supporter les groupes de disponibilités Always On.

Ces opérations sont décrites dans les articles :

• Overview of Always On Availability Groups (SQL Server)
• Prerequisites, Restrictions, and Recommendations for Always On availability groups
• Getting Started with Always On Availability Groups
• AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups

Il suppose aussi de bonnes connaissances sur SQL Server Data Quality :

• Data Quality Services
• Install Data Quality Services

Configuration de SQL server pour Always On Availability Groups

Installation et configuration d’un cluster de basculement

Pour la prise en charge des groupes de disponibilité Always On, il faut d’abord installer et configurer un cluster de basculement Windows Server – Windows Server Failover Cluster (WSFC) –. Les recommandations de Microsoft sont de 3 nœuds physiques sur 2 sites : un primaire et un disaster recovery.

Un cluster de basculement Windows de 2 nœuds physiques

Pour les détails, consulter :

• Failover Clustering Overview
• Windows Server Failover Clustering with SQL Server

Activation et configuration des groupes de disponibilité Always On

Un groupe de disponibilité doit être disponible et configuré afin d’accueillir les bases de données de DQS.

Tableau de bord d’un groupe de disponibilité Always On

L’article ne décrit pas cette configuration. Il assume qu’un groupe de disponibilité existe pour accueillir les bases de données de DQS.

Ces opérations sont décrites dans les articles :

• Configuring an Instance of SQL Server to Support Always On Availability Groups
• Creating and Configuring a New Availability Group

Configuration de DQS pour les groupes de disponibilité Always On

Installation de SQL Server Data Quality Services sur chaque nœud du cluster

Nous avons vu que les groupes de disponibilité Always On fournissent la haute disponibilité au niveau des bases de données utilisateur et que DQS s’appuie sur des objets serveurs. SQL Server Data Quality doit donc être installé sur tous les nœuds physiques participant à l’AG.

L’installation de SQL Server Data Quality est standard et décrite dans le document :

• Install Data Quality Services

Une fois l’installation effectuée sur l’ensemble des nœuds du groupe de disponibilité, toutes les instance SQL server possèdent les objets server nécessaire au fonctionnement de DQS. A ce stade, il y a autant de serveur DQS que de nœud dans le groupe de disponibilité. Ils sont tous accessibles indépendamment les uns des autres. Ils se comportent comme autant de serveurs DQS.

Préparation de DQS pour les groupes de disponibilité Always On

Préparation du nœud primaire

Sur le nœud primaire des opérations préalables sur les bases de données : DQS_MAIN, DQS_PROJECTS, et DQS_STAGING_DATA, créées lors de l’installation DQS pour pouvoir partie de l’AG. Les prérequis pour ajouter une base de données à un AG sont :

• Le mode de recovery doit être sur « Full »
• Un backup full doit exister

Lors de l’installation de DQS les bases de données DQS sont créées avec le mode de recovery « Simple ». Il doit donc être modifié à « Full » pour les 3 bases de données : DQS_MAIN, DQS_PROJECTS, et DQS_STAGING_DATA.

Modification du mode de recovery des bases de données de DQS

Par script :

	USE [master]
	GO
	ALTER DATABASE [DQS_MAIN] SET RECOVERY FULL WITH NO_WAIT
	ALTER DATABASE [DQS_PROJECT] SET RECOVERY FULL WITH NO_WAIT
	ALTER DATABASE [DQS_STAGING_DATA] SET RECOVERY FULL WITH NO_WAIT
	GO

Une fois cette configuration terminée, il est nécessaire d’effectuer une sauvegarde complète des 3 base de données : DQS_MAIN, DQS_PROJECTS, et DQS_STAGING_DATA.

Préparation des nœuds secondaires

Sur tous les autres nœuds les 3 bases de données créées lors de l’installation de DQS doivent être supprimées. L’installation a créée et configurée tous les objets nécessaires. Il n’est pas nécessaire de les conserver, elles seront restaurées depuis le nœud primaire en tant que membre du groupe de disponibilité.

Les bases doivent être supprimées sur tous les nœuds secondaires du AG :

• DQS_MAIN
• DQS_PROJECTS
• DQS_STAGING_DATA

Ajout des bases de données DQS à un AG

Cet article ne décrit pas la création et la configuration de groupe de disponibilité Always On. Ce point est documenté plus haut. A ce stade :

DQS est installé et configuré sur tous les nœuds prenant part au AG

• Sur le nœud primaire, le mode de recovery est configuré à « Full » pour les 3 bases de données de DQS : DQS_MAIN, DQS_PROJECTS, et DQS_STAGING_DATA suivi d’un backup Full
• Sur les nœuds secondaires, les 3 base de données créées lors de l’installation de DQS sont supprimées
• Un groupe de disponibilité Always On existe pour accueillir les 3 bases de données de DQS

Il faut maintenant ajouter ces 3 bases de données à un AG. La procédure est similaire à celle qui permet d’ajouter n’importe quelle base de données à un groupe de disponibilité. La seule subtilité concerne la base de données DQS_MAIN, chiffrée par un mot de passe. La suite des opérations doit s’effectuer sur le nœud primaire du groupe de disponibilité, actuellement le seul à posséder une copie des bases de données de DQS.

1_ Dans SQL Server Management Studio, lancer l’assistant d’ajout de bases de données :

2_ Pour pouvoir ajouter la base de données DQS_MAIN, il faut au préalable saisir le mot de passe et valider en cliquant sur « refresh »

3_ Les 3 base de données peuvent alors être sélectionnées

4_ La fenêtre suivante réclame de se connecter à chaque nœud secondaire

5_ Sélectionner la méthode de synchronisation initiale conformément aux procédures et usages en vigueur dans votre organisation

6_ Le pre-flight check ne doit pas remonter d’erreur

7_ Il suffit ensuite de valider les étapes suivantes pour terminer l’ajout des bases de données au AG

La configuration est maintenant terminée les bases de données bénéficient des fonctionnalités de haute disponibilité fournies par les groupes de disponibilités Always On de SQL Server.

Exemple de groupe de disponibilité Always On pour SQL Server Data Quality Services

Test de connexion au server DQS

Après avoir configuré le AG pour DQS, il est maintenant possible de se connecter au serveur DQS en indiquant comme nom de server le nom du availability group listener avec le client DQS. Il faut utiliser le nom du listener pour s’assurer que la connexion est correctement routée vers le nœud primaire actuel. Dans mon cas, j’utilise un alias, qui pointe sur le listener, ce qui est une bonne pratique. Cela permet de limiter au maximum l’adhérence à la configuration de l’infrastructure sous-jacente.

Test de connexion au serveur DQS

On peut aussi constater que lors de la bascule, tout se passe bien pour les bases de données mais le serveur DQS n’est pas fonctionnel pour autant.

Gestion manuel du basculement

Lorsque qu’un basculement se produit, le serveur cible devient le serveur primaire. Dès lors le serveur DQS ne fonctionne plus :

Message d’erreur après la bascule

Plus haut, nous avons vu que DQS utilisait entre autres des objets serveur. C’est notamment le cas login SQL. Pour assurez la haute disponibilité du serveur DQS, il est nécessaire à chaque basculement de réaligner correctement les logins SQL et les owners des bases données de DQS.

Pour ce faire, il suffit d’exécuter le code sur l’instance SQL Server qui porte maintenant le rôle de primaire :

	ALTER AUTHORIZATION ON DATABASE::[DQS_MAIN] TO [##MS_dqs_db_owner_login##]
	ALTER AUTHORIZATION ON DATABASE::[DQS_PROJECTS] TO [##MS_dqs_db_owner_login##]
	ALTER AUTHORIZATION ON DATABASE::[DQS_STAGING_DATA] TO [##MS_dqs_db_owner_login##]

	USE DQS_MAIN
	ALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]
 
	USE DQS_PROJECTS
	ALTER USER dqs_service WITH LOGIN=[##MS_dqs_service_login##]

Le serveur DQS est de nouveau accessible.

A ce stade-là, il ne semble pas que l’on puisse parler de haute disponibilité. L’exécution manuelle d’un script pour rétablir l’accès au serveur DQS entrave gravement la haute disponibilité dans le cas d’un basculement imprévu.

Pourtant, nous avons bel et bien terminé notre configuration. En effet, une fois le premier basculement et la configuration nécessaire appliquée, les bascules suivantes s’effectuent de façon transparente sans action supplémentaire.

Nous avons maintenant un serveur DQS haute disponibilité et près pour une récupération après sinistre.

Mise à jour SQL server et mise à niveau des bases de données DQS

Certaines mises à jour de SQL Server peuvent nécessiter la mise à niveau des schémas des bases de données de DQS. Cette mise à niveau s’effectue avec le même utilitaire que l’installation, avec la ligne de commande :

dqsinstaller.exe –upgrade

Pour plus de détail sur la mise à niveau du shémas des bases de données de DQS :

• Upgrade DQS Databases Schema After Installing SQL Server Update

Nous l’avons vu précédemment, les base de données de DQS contiennent, en plus des objets classiques, des assemblies et d’autre objets complexes. La mise à niveau, comme l’installation requière des manipulations supplémentaires dans ce contexte de serveur DQS en haute disponibilité.

Le processus de mise à jour de SQL Server et la mise à niveau des bases de données de DQS n’ont pas d’impact sur les données stockées dans les bases de données de DQS. Il est cependant conseillé d’effectuer une sauvegarde des bases de données de DQS, afin de prévenir tout problème de perte de données durant la mise à niveau des 3 bases de données de DQS.

Les détails sur la sauvegarde et la restauration des bases de données de DQS :

• Backing Up and Restoring DQS Databases

Mise à jour du serveur primaire et mise à niveau des bases de données de DQS

Les opérations commencent par la mise à jour du noeud primaire. Après s’être assurer qu’aucun client n’est connecté au server DQS et avant d’installer les mises à jour SQL Server, il faut retirer les bases de données de DQS du AG sur le nœud primaire.

Les mises à jour de SQL Server peuvent maintenant être appliquées. Une fois l’installation terminée avec succès, il faut préparer les bases de données de DQS pour la mise à niveau :

1_ Permettre l’enregistrement des assemblies unsafe lors de la mise à niveau :

	ALTER DATABASE [DQS_MAIN] SET TRUSTWORTHY ON

2_ Restaurer la master key de la base de données :

	USE DQS_MAIN
	OPEN MASTER KEY DECRYPTION BY PASSWORD = '<DB_MASTER_KEY>'
	ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

3_ La commande de mise à niveau peut être exécutée :

	dqsinstaller.exe –upgrade

Une fois la mise à niveau du nœud primaire effectuée, mettre à jours tous les réplicas secondaires.

Mise à jour des réplicas secondaires

Sur tous les réplicas secondaires, il faut appliquer les mêmes mises à jour de SQL server que sur le nœud primaire. Une fois les mises à jour appliquées, il est nécessaire supprimer les 3 bases de données de DQS : DQS_MAIN, DQS_PROJECTS, et DQS_STAGING_DATA.

Ces bases de données peuvent être supprimées en toutes sécurité et n’ont pas besoin d’être mises à niveau. Les bases de données mises à niveau seront restaurées depuis le nœud principal lors de leur ajout au AG.

Ces opérations sont à effectuer sur tous les réplicas secondaires appartenant au AG.

Ajout des bases de données au groupe de disponibilité

Les mises à jour sont appliquées avec succès sur tous les réplicas du groupe de disponibilité et la mise à niveau du schéma des bases de données terminée sur le nœud primaire est achevé. Pour que le serveur DQS soit hautement disponible, il faut maintenant ajouter ses bases de données au groupe de disponibilité.