Réplication SQL Serveur

La réplication SQL Serveur consiste en un transfert des données d'un serveur à un ou plusieurs autres serveurs SQL, de manière transparente pour l'utilisateur de SQL et ainsi d'assurer une persistance des données ou la synchronisation d'informations entre plusieurs serveurs.

Le réplication nécessite 3 éléments :

• Un serveur de publication : serveur qui rend disponible ses données pour la réplication
• Un serveur de distribution : serveur qui distribue les données à répliquer (obvious...)
• Un serveur de destination : les abonnés au service de réplication

La réplication peut prendre 3 aspects :

• Réplication de capture instantanée : réplique à un instant t la totalité des données
• Réplication transactionnelle : réplique uniquement les modifications effectuées aux bases à répliquer
• Réplication de fusion : permet de disposer d'une base localement (pour des utilisateurs sans connexion par exemple) et qui autorise la fusion de cette base locale avec celle du serveur de publication.

Ces réplications peuvent se réaliser immédiatement ou bien peuvent être mises en attente jusqu'à la prochaine réplication.

Prérequis

La mise en place de la réplication SQL Serveur requiert des prérequis :

• Un contrôleur de domaine Windows indépendant des serveurs SQL (best practice)
• Deux hôtes Windows intégrés au domaine avec SQL Serveur installé
• Des instances SQL qui n'ont pas plus de deux versions d'écart
• L'accès à un compte de niveau sysadmin sur les serveurs SQL de publication et d'abonnement
• Créer des utilisateurs de gestion des différents services liés à la réplication SQL

Sur le contrôleur de domaine, il est recommandé de mettre en place un compte de service qui sera utilisé pour la gestion des différents services liés à SQL Server, et notamment pour la future réplication que nous allons mettre en place. Celle-ci faisant appel à un compte de domaine, il est plus simple de générer ce compte que d'utiliser les informations fournies automatiquement par SQL Serveur lors de son installation. Pour faire simple, nous nous limiterons à la création de deux utilisateurs :

• sqlsrv : utilisé pour la gestion du moteur SQL
• sqlagt : utilisé pour la gestion de l'agent SQL et des jobs associés

Cela peut se faire avec les commandes de gestion du Domain Service ou bien en Powershell ou bien depuis la GUI. Si l'on fait avec les bonnes vieilles commandes DS, cela donne

dsadd ou "OU=SQLSERVEUR,DC=DOMAINE,DC=LOCAL"
dsadd user "CN=SQLSRV,OU=SQLSERVEUR,DC=DOMAINE,DC=LOCAL" -pwd <mot_de_passe_fort> -pwdneverexpires yes -disabled no
dsadd user "CN=SQLAGT,OU=SQLSERVEUR,DC=DOMAINE,DC=LOCAL" -pwd <mot_de_passe_fort> -pwdneverexpires yes -disabled no
         

La mise en place d'un serveur SQL est à faire sur un serveur qui est membre d'un domaine Windows. La mise en place de SQL Serveur sur un serveur disposant déjà du rôle de contrôleur de domaine Windows n'est pas considéré comme une bonne pratique et ne peut s'envisager que dans le cadre d'un labo de test de configuration, pas en production.

Installation de SQL Serveur

Démarrez sur l'ISO de SQL et démarrez l'installation de l'application. Sur l'assistant d'installation, faites simplement Suivant s'il n'y a pas d'action spécifique prévue sur l'étape.

Licence : Si vous disposez d'une licence, saisissez-la maintenant. Sinon choisissez l'option Gratuite. Faites Suivant. Acceptez les termes du contrat de licence Microsoft et Faites Suivant. Utilisez les mises à jour Microsoft pour maintenir à jour votre service SQL.

Règles d'installation : l'assistant d'installation vous affiche les différents problèmes ou avertissements à prendre en compte avant d'avancer dans les étapes d'installation ultérieures. Il y a potentiellement deux avertissements qui sont susceptibles d'arriver :

• Ordinateur contrôleur de domaine : pour rappel, il est fortement déconseillé d'installer le service SQL Serveur sur un ordinateur contrôleur de domaine. Des problèmes de performances et de fonctionnement général au niveau du partage des ressources sont à craindre. Réservez l'usage de SQL sur un contrôleur de domaine pour des applications non critiques dans un réseau qui n'est pas critique non plus.
• Pare-feu Windows : pour communiquer, SQL Serveur utilise par défaut les ports 1433/tcp pour le moteur SQL et 1434/tcp + 1434/udp pour le service SQLBrowser (permet de trouver une instance sur un port dynamique). Il faut bien penser à ajouter les règles dans le firewall du serveur Windows afin de permettre les accès depuis les applications utilisant le service SQL.

Pour autoriser les ports de SQL Server dans le pare-feu de Windows, vous pouvez passer par l'interface ou bien ce genre de lignes de commande :

netsh advfirewall firewall add rule name="SQL_PORT1433" dir=in action=allow protocol=TCP localport=1433 profile=domain
         netsh advfirewall firewall add rule name="SQL_PORT1434" dir=in action=allow protocol=TCP localport=1434 profile=domain
         netsh advfirewall firewall add rule name="SQL_PORT1434_UDP" dir=in action=allow protocol=UDP localport=1434 profile=domain
         

Vous retrouvez ces règles dans l'interface sous cette forme Dans les fonctionnalités à installer, assurez-vous que Réplication SQL Server soit bien cochée ainsi que l'option Connectivité des outils clients (dans les options partagées). Pour les autres options c'est à vous de voir en fonction des éventuels besoins d'usage. Si c'est votre première ou unique instance sur le serveur, Microsoft propose par défaut le nom MSSQLSERVER. Laissez-le tel quel sauf si vous avez absolument besoin de donner un nom à votre instance. Lors de la configuration des comptes de service à utiliser pour la gestion de SQL Serveur, par défaut vous obtenez des comptes système locaux mais qui peuvent nous ennuyer dans la mise en place de la réplication, notamment pour le passage de jetons de sécurité. Il est conseillé de les remplacer par ceux que nous avons créés dans l'AD précédemment. Ne pas oublier de mettre le type de démarrage à automatique sur l'Agent SQL (pour les travaux de gestion et d'entretien des bases présentes dans l'instance SQL). En ce qui concerne la case d'attribution de droits pour pouvoir effectuer des tâches de maintenance en volume, je vous invite à lire cette page https://learn.microsoft.com/fr-fr/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15 Je vous conseille de cocher cette case... Un point important ici est de bien installer le moteur SQL en mode mixte, c'est-à-dire pour lui permettre d'autoriser les connexions à partir de comptes SQL mais également de comptes Windows (du domaine). N'oubliez donc pas d'ajouter votre utilisateur actuel en tant qu'administrateur du serveur SQL (c'est toujours utile). Après le rappel de la configuration qui va être appliquée, il suffit de valider et d'attendre...

Création des comptes Windows

Pour la mise en place de la réplication, nous avons besoin de comptes pour les différents agents. Ces comptes seront créés dans le domaine.