Blog

Groupe de disponibilité : synchronisation des répliquas secondaires (jobs, logins, etc)

---

Vous avez récemment implémenté une solution de haute disponibilité : un groupe de disponibilité. TUT TUT, c’est la fête !

Bascule automatique ou pas, qu’en est-il le jour où elle est effectuée ? Les bases de données basculent bien, puis quelques minutes plus tard :

– Un premier utilisateur vous fait savoir que pour une raison indéterminée, il ne parvient plus à se connecter à une des bases de données.
– Un second vous indique que son job n’a pas tourné.
– Un troisième vous remonte une erreur quand il tente d’exécuter une procédure stockée qui fait appel à un serveur lié.

Et comme par hasard, ces problèmes surviennent juste après la bascule du groupe de disponibilité. Bref, la reprise de service ne semble pas totale…

Pour résumer, contrairement à un Failover Clustering Instance, AlwaysOn ne couvre pas l’instance mais un groupe de bases de données. Il va donc falloir synchroniser à intervalle régulier les objets suivants sur les répliquas secondaires :

Au niveau de l’instance :
– Logins
– Rôles
– Membres des rôles
– Permissions serveur
– Serveurs liés
– Backup Device

Au niveau de l’agent SQL :
– Catégories d’opérateur
– Opérators
– Catéories d’alerte
– Alertes
– Planifications de job partagées
– Catégories de job
– Jobs

Le module Powershell SqlNinjaSyncAG disponible sur Github et PowerShell Gallery, fera le job. Tout ce qui suit est décrit en détail dans le webcast pour la synchronisation des répliquas secondaires. Sa configuration (fichier json plus bas) est essentielle. Le risque, le cas échéant, est de faire des suppressions d’objets sur vos répliquas secondaires qui n’ont pas lieu d’être. Prenez le temps de visionner la vidéo  et/ou d’aller au bout de la lecture de ce post tenant en 10 étapes pour éviter de scier la branche sur laquelle vous êtes assis.

Déploiement du module SqlNinjaSyncAG

1) Déployez les 3 composants Microsoft SQL Server 2016 Feature Pack SP1 ci-dessous dans cet ordre :

– ENU\x64\SQLSysClrTypes.msi
– ENU\x64\SharedManagementObjects.msi
– ENU\x64\PowerShellTools.msi

Les packages sont téléchargeables ici : https://www.microsoft.com/en-US/download/details.aspx?id=54279

2) Installer le module SqlNinjaSyncAG depuis PSGallery comme suit :

2 bis) Si le module ne peut être téléchargé en direct sur la machine qui exécutera la synchronisation parce qu’elle ne dispose pas de connexion Internet, télécharger le module depuis une machine intermédiaire connectée à Internet :

3) Une fois installé sur la machine cible, charger le module et créer un répertoire de travail où bon vous semble, C:\SyncAG par exemple :

4) Pour la connexion à tous les répliquas lors de la synchronisation, créer de préférence un login SQL admsync, membre du rôle sysadmin, sur TOUS les répliquas.

5) Positionnez-vous sur un répertoire de travail, C:\TEMP par exemple

6) Cryptez le mot de passe du login SQL créé précédemment en passant le nom du login et le mot de passe en paramètre. Un fichier texte contenant le mot de passe crypté sera généré dans le dossier C:\TEMP. Cette valeur devra être reportée dans le fichier de configuration de la synchronisation. Attention : si vous utilisez un autre compte et/ou une autre machine pour exécuter la synchronisation, que ceux qui ont été utilisés pour le cryptage, le mot de passe ne pourra pas être décrypté.

7) Avant de renseigner la configuration pour votre groupe de disponibilité, nous allons faire une copie du template du fichier de configuration, MyConfig.json, dans le répertoire de travail de la synchronisation, à savoir C:\SyncAG :

Configuration de la synchronisation SqlNinjaSyncAG

8) Passons à la configuration de notre synchronisation qui consiste à personnaliser le fichier Json copié précédemment dans le répertoire de travail de la synchronisation C:\SyncAG

5 sections sont présentes dans le fichier de configuration ($InputFile) :

AvaibilityGroup 
– Name : Nom du groupe de disponibilité
– Listener : Nom/IP du listener pour la connexion au répliqua principal
– Login (optionnel si compte Windows utilisé) : Login SQL
– Password (optionnel si compte Windows utilisé) : Mot de passe crypté du login SQL

La section AvaibilityGroup est à personnaliser.

FixOwner

Pour un usage standard (valeur à true), sa deviendra propriétaire des bases de données, jobs, points de terminaison et du groupe de disponibilité pour éviter les erreurs de suppression de logins. En revanche, si le paramètre est à false, il faudra exclure les logins concernées de la synchronisation.

Replicas
– Exclusions * : Répliquas à exclure de la synchronisation, ceux qui ne seront jamais promu principal

Pour un usage standard avec bascule automatique sur tous les répliquas secondaires, aucune exclusion n’est à renseigner.

Jobs
– FirstStepNameCheck : Nom de la première étape de vérification de rôle (primaire) pour poursuivre un job, ex : “AG Check Primary”
– ExcludeIfNoCheck : Si cette étape de vérification n’est pas présente, si la valeur est à true, les jobs concernés seront exclus de la synchronisation

Pour un usage standard, renseigner le nom de l’étape de vérification de rôle sans exclure de la synchronisation les jobs qui ne contiennent pas par cette première étape (ExcludeIfNoCheck = false).

Objects
– ToSync : true (Remplacement : suppression/création) ou false (synchronisation désactivée sur l’ensemble des objets pour un type donné)
– Type : Logins, Roles, LinkedServer, etc
– Exclusions * : Nom des objets à ne pas prendre en compte dans la synchronisation

Pour un usage standard et à l’exception des exclusions, aucune personnalisation n’est nécessaire dans la section ObjectsToSync. 

Certains caractères tels que “\” (ex : exclusion de répliqua avec instance nommée et logins Windows) doivent être échappés pour que le fichier Json reste interprétable, au risque de rencontrer ce type d’erreur : 

Error occurred on line xxx : Unrecognized escape sequence

Gestion des exclusions

Elle est très importante pour plusieurs raisons :
– Ne pas se couper l’herbe sous le pied lors de la synchronisation des logins (tous supprimés puis recréés sur les répliquas secondaires sauf ceux listés dans les exclusions).
– Les exclusions permettent d’éviter de synchroniser à nouveau des objets qui resteraient à compléter (ex : mot de passe à renseigner pour les serveurs liés) pour gérer de la synchronisation OneShot.
– Les exclusions permettent de conserver la définition d’objets spécifiques à une instance donnée, non liés au groupe de disponibilité.
– Les objets systèmes sont automatiquement exclus de la synchronisation (ex : rôles, catégories).
– Les comptes de services, groupes et comptes locaux propres à chaque répliqua sont persistant.

Gestion des jobs

Indépendamment de la synchronisation, chaque job SQL présent sur le répliqua principal et qui implique une base de données dans le groupe de disponibilité, devrait contenir une première étape pour vérifier qu’on se trouve bien sur le répliqua principal avant d’exécuter les tâches suivantes. Le script de synchronisation permet de vérifier que cette première étape est bien présente sous le nom indiqué dans le fichier de configuration (FirstStepNameCheck) et le cas échéant de ne pas synchroniser les jobs qui ne répondent pas à cette contrainte (ExcludeIfNoCheck = true). Pour plus d’information, voici un post détaillé sur la gestion des jobs avec un groupe de disponibilité :

https://glenn-pepper.co.uk/2018/09/26/sql-agent-and-alwayson-availability-groups/

Certains jobs sont à exclure car non pris en charge :

• Les jobs associés à un serveur local, en lien avec la réplication par exemple, doivent être exclu de la synchronisation, ainsi que leur planification partagée au besoin.
• Les jobs associés à des plans de maintenance doivent être exclu de la synchronisation, idem pour leur planification partagée.

Fonctionnement de la synchronisation SqlNinjaSyncAG

Le script se connecte au listener indiqué dans le fichier de configuration Json en entrée ($InputFile) et vérifie qu’il s’agit bien d’un groupe de disponibilité. Une fois cette étape validée, à l’exception des logins, permissions et membres de rôle serveur, la création d’objets liés au serveur et à l’agent SQL est scriptée sur la base de l’assembly SMO à partir du répliqua principal. On récupère la liste des répliquas secondaires pour s’y connecter et générer la suppression de leurs objets respectifs. En dernier lieu, si le paramètre d’entrée $Execute = $True, on exécute le script T-SQL de synchronisation finalisé sur chaque répliqua secondaire (voir plus bas).

Lors de la première exécution, les répertoires log et out sont créés à la volée dans le répertoire de travail indiqué (ex : C:\SyncAG) :

– out : Répertoire hébergeant les scripts T-SQL de synchronisation pour chaque répliqua secondaire
– log : Répertoire hébergeant les logs du module

Quelques précisions :

– Le module ne fait appel à aucun module externe afin de simplifier son déploiement.
– Le module prend aussi en charge une authentification SQL/Windows unique pour tous les répliquas.
– Par sécurité,  déployer et installer le module sur un serveur qui n’est pas un des répliquas
– La synchronisation repose sur un DROP/CREATE pour ne pas avoir à gérer de mise à jour.
– Il y a une gestion des exclusions avec 3 niveaux de granularité : répliqua, type d’objet (ex : jobs) et objet nommé
– Un changement de propriétaire peut être requis sur certains types d’objets pour que la synchronisation des logins soit pleinement fonctionnelle (voir paramètre : FixOwner)

La synchronisation prend 3 paramètres en entrée :

$InputFile : Nom du fichier de configuration Json hébergé dans le répertoire in, à personnaliser selon vos besoins. Il peut y en avoir plusieurs. A titre personnel, je préfère centraliser les paramètres à un endroit plutôt que d’en passer 50 à une fonction Powershell. Le Json est sérialisable sous la forme d’objet, donc manipulable à souhait.

$Execute : $False par défaut, donc pas de synchronisation pour valider la génération de script pour un répliqua secondaire. Si le paramètre est renseigné à $True, la synchronisation des répliquas secondaires aura lieu sur la base des scripts T-SQL sync_NOMREPLIQUASECONDAIRE.sql générés dans le répertoire out.

$LogRetentionDays : Rétention fixée à 3 jours par défaut sur les fichiers de logs générés dans le répertoire log à chaque lancement du script (avec ou ans exécution de la synchronisation).

Compte SQL de la synchronisation

Un login SQL est automatiquement exclu de la synchronisation, raison pour laquelle il est recommandé de privilégier une authentification SQL pour exécuter la synchronisation. Le mot de passe du login SQL devra être obligatoirement crypté. Néanmoins, si vous choisissez d’utiliser une authentification Windows, le compte concerné ne sera pas forcément présent individuellement sur l’instance parce qu’il fait partie d’un groupe local ou AD. Ce groupe doit être sysadmin et exclu impérativement de la synchronisation (Logins\Exclusions).

Etape 1 : Validation de la synchronisation

9) On charge le module, on se positionne sur notre répertoire de travail C:\SyncAG et on lance la génération des scripts de synchronisation des répliquas secondaires en passant en paramètre le fichier de configuration du groupe de disponibilité concerné (InputFile) et sans exécuter la synchronisation (Execute = $False).

Une fois l’exécution du script Powershell terminée, reste à vérifier la cohérence des scripts T-SQL de synchronisation sync_NOMREPLIQUASECONDAIRE.sql disponibles dans le répertoire C:\SyncAG\out. Chaque script est destiné à être exécuté sur le répliqua secondaire correspondant. Cette étape de vérification est primordiale.

Etape 2 : Exécution de la synchronisation

10) Si vous en êtes à cette étape, c’est que vous avez validé le résultat des scripts sync_NOMREPLIQUASECONDAIRE.sql. Attention, l’exécution du script de synchronisation est irréversible, il n’y a pas de retour arrière possible. Cette fois le paramètre Execute est à $True pour l’exécution de la synchronisation.

Erreurs possibles

Voici les erreurs que vous pouvez rencontrer suivant votre contexte :

Base de données inexistante sur l’étape d’un job
Invoke-Sqlcmd : The specified @database_name (‘DATABASE_NAME’) does not exist.

Base de données par défaut inexistante pour un login
Invoke-Sqlcmd : The database ‘DATABASE_NAME’ does not exist. Supply a valid database name. To see available databases…

Tentative de suppression d’un job lié à un plan de maintenance SSIS (à proscrire, voir scripts d’Ola Hallengren)
Invoke-Sqlcmd : The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”. The conflict occurred in database “msdb”, table “dbo.sysmaintplan_subplans”, column ‘job_id’

Tentative de KILL/DROP du compte Windows exécutant le script Powershell de synchronisation
Invoke-Sqlcmd : Cannot use KILL to kill your own process.
Cannot drop the login ‘XXXX’, because it does not exist or you do not have permission.

Tentative de suppression d’une planification partagée liée à un job exclu de la synchronisation
Invoke-Sqlcmd : The schedule was not deleted because it is being used by one or more jobs.

Si FixOwner = false

Tentative de suppression d’un login propriétaire d’un groupe de disponibilité
Server principal has granted one or more permission(s). Revoke the permission(s) before dropping the server principal.

Tentative de suppression d’un login propriétaire d’un point de terminaison
Drop failed for Login ‘x’. The server principal owns one or more endpoint(s) and cannot be dropped.

Tentative de suppression d’un login propriétaire d’une base de données
Login ‘x’ owns one or more database(s). Change the owner of the database(s) before dropping the login.

Si FixOwner = true

Sur SQL Server 2012, le changement de propriétaire de la base de données ne se propage pas aux répliquas secondaires, ce qui est dû à un bug référencé ci-dessous :

https://connect.microsoft.com/SQLServer/feedback/details/723879/sql-2012-always-on-availability-group-change-or-alter-db-owner-does-not-replicate-to-secondary

Pour corriger le problème, de deux choses l’une, soit :
– Vous basculez et changer le propriétaire de la base sur le répliqua principal nouvellement promu et basculer à nouveau au besoin
– Vous supprimez la base de donnée du répliqua secondaire et restaurer une sauvegarde avec le bon propriétaire
– C’est historique et j’ai presque envie de vous dire, bah c’était le destin, vous laissez vivre tel quel

Voir : http://www.dbafire.com/2018/01/19/fix-a-replica-read-only-db-owner-availability-group

Non pris en charge ou partiellement

Serveurs liés

Concernant les serveurs liés, ils sont partiellement pris en charge. La définition est bien créée sur les répliquas secondaires mais le mot de passe reste à initialiser en cas d’authentification SQL. Le mot de passe est certes disponible dans une table système et accessible en mode DAC, seulement l’algorithme de cryptage varie d’une version à l’autre de SQL Server. Voici un post plus détaillé sur le sujet :

https://blog.netspi.com/decrypting-mssql-database-link-server-passwords/

Pour les plus chevronnés d’entre vous, la méthode d’extraction des mots de passe sur des versions plus récentes que SQL Server 2005, 2008, 2012, 2008 Express, and 2012 Express, est disponible dans les scripts DBATools. Mais on ne va pas se mentir, les serveurs liés, c’est un peu comme les curseurs, dans 99,9999999999999999% des cas, ils ont été créés pour de mauvaises raisons. Avant d’envisager de les synchroniser, posez-vous la question de leur légitimité (VS ETL par exemple).

Credentials & Proxy

Ces deux types d’objet sont partiellement pris en charge. Pour rappel, un credential fait référence à un compte Windows rattaché à un proxy pour exécuter des tâches spécifiques depuis l’agent SQL (ex : SSIS, Powershell). Pour faire simple c’est le “Exécuter ce job en tant que bidulette”. Concernant les credentials, on ne peut pas générer de script à partir de SMO (la méthode script() est inexistante). A l’image des serveurs liés, le mot de passe de chaque credential synchronisé reste à renseigner. Par ailleurs la suppression d’un credential, n’entraîne pas celle du proxy auquel il est associé. Or, la création du proxy ne sera pas scriptée s’il n’est pas rattaché à un credential.

Plans de maintenance

Non supportés.

Groupes distribués

Non supportés.

Linux

A tester… Un petit lien sur l’implémentation de SMO sur cet environnement :

http://www.maxtblog.com/2017/06/using-linux-sql-server-smo-in-powershell-core

Autres outils de synchronisation

Il existe bien évidemment d’autres méthodes pour synchroniser des répliquas secondaires telles que :

– Boite à outil Powershell DBATools : https://dbatools.io

C’est une solution que j’ai finalement renoncé à intégrer entre autres pour ces raisons :
– Nativement SMO permet déjà de faire énormément de choses
– Faciliter le déploiement du script principal en limitant le nombre de modules externes
– Le contexte lié au groupe de disponibilité est particulier, il ne s’agit pas de faire du clonage pour une migration
– Le but était d’obtenir un script de synchronisation en sortie sans nécessairement l’exécuter

– Add-In Availability Group : https://www.sqlskills.com/free-tools/ssms-availability-group-add-in/

A l’heure actuelle, cet outil n’est disponible que sur SQL Server Management Studio 2012 and 2014. Voici les types d’objets qu’il permet de synchroniser :
– Instance : Rôles
– Instance : Logins
– Instance : Permissions
– Instance : Serveurs liés
– Agent SQL : Operators
– Agent SQL : Alerts
– Agent SQL : Jobs