Comment configurer la réplication MS SQL Server

Microsoft SQL Server est un logiciel de gestion de bases de données pouvant être installé sur les systèmes d’exploitation Windows Server. Les bases de données sont utilisées par les entreprises de tous les secteurs d’activité, et de nombreuses solutions logicielles s’appuient sur des bases de données, qu’elles soient centralisées ou distribuées. La disponibilité des bases de données et la cohérence des données sont essentielles pour les entreprises, ce qui rend indispensables la sauvegarde et la réplication des bases de données.
Découvrez les types de réplication SQL Server, le fonctionnement de la réplication dans SQL Server et comment effectuer une réplication SQL Server.

NAKIVO pour la sauvegarde sous Windows

Sauvegarde rapide des serveurs et postes de travail Windows sur site, hors site et dans le cloud. Récupération complète des machines et des objets en quelques minutes pour des délais de reprise (RTO) réduits et une disponibilité maximale.

DÉCOUVREZ LA SOLUTION

Qu’est-ce que la réplication SQL Server ?

La réplication MS SQL Server est le processus consistant à copier des données d’une base de données vers une autre, y compris des objets de base de données spécifiques, et à maintenir une copie synchronisée de ces données entre la base de données Source et la base de données Cible. Grâce à la réplication dans SQL Server, vous pouvez créer une copie identique de votre base de données principale et synchroniser les modifications entre les deux bases de données tout en préservant la cohérence et l’intégrité des données.

Terminologie utilisée pour la réplication MS SQL Server

Avant d’aborder la configuration et la mise en place de la réplication MS SQL Server, passons d’abord brièvement en revue les principaux termes et les modèles de réplication.
Les articles constituent les unités de base à répliquer, telles que les tables, les procédures, les fonctions et les vues. Les articles peuvent être mis à l’échelle verticalement ou horizontalement par l’intermédiaire de filtres. Plusieurs articles peuvent être créés pour un même objet.
Une publication est un ensemble logique d’articles. Il s’agit de l’ensemble final d’entités de la base de données désignées pour la réplication.
Un filtre est un ensemble de conditions applicables à un article. La réplication MS SQL Server vous permet d’utiliser des filtres et de sélectionner des entités personnalisées à répliquer, ce qui réduit ainsi le trafic, la redondance et la quantité de données stockées dans une réplique de base de données. Par exemple, vous pouvez sélectionner uniquement les tables et les champs les plus critiques à l’aide de filtres, puis ne répliquer que ces données.
Les agents sont des composants de MS SQL Server pouvant agir en tant que services d’arrière-plan pour les systèmes de gestion de bases de données relationnelles ; ils sont utilisés pour planifier l’exécution automatisée de tâches, telles que la sauvegarde et la réplication de bases de données MS SQL. Il existe cinq types d’agents : l’agent d’instantané, l’agent de lecture des journaux, l’agent de distribution, l’agent de fusion et l’agent de lecture de file d’attente.
Les métadonnées sont les données utilisées pour décrire les entités de la base de données. Il existe un large éventail de fonctions de métadonnées intégrées qui vous permettent de récupérer des informations sur l’instance MS SQL Server, les instances de base de données et les entités de base de données.

Rôles dans la réplication de bases de données SQL

Il existe trois rôles principaux dans la réplication de bases de données MS SQL : le distributeur, l’éditeur et l’abonné.

• Un distributeur est une instance de base de données MS SQL configurée pour collecter les transactions provenant des publications et pour les distribuer aux abonnés. Un distributeur fait office de base de données pour le stockage des transactions répliquées.

  Une base de données distributrice peut être considérée à la fois comme l’éditeur et le distributeur. Dans le modèle de distributeur local, une seule instance de MS SQL Server héberge à la fois l’éditeur et le distributeur. Un modèle de distributeur distant peut être utilisé lorsque vous souhaitez que les abonnés soient configurés pour utiliser une seule instance de MS SQL Server afin d’obtenir différentes publications (distribution centralisée). Dans ce modèle, l’éditeur et le distributeur s’exécutent sur des serveurs différents.

• Un éditeur est la copie principale de la base de données sur laquelle la publication est configurée, mettant les données à la disposition d’autres serveurs MS SQL Server configurés pour être utilisés dans le processus de réplication. L’éditeur peut disposer de plusieurs publications.
• Un abonné est une base de données qui reçoit les données répliquées à partir d’une publication. Un abonné peut recevoir des données provenant de plusieurs éditeurs et publications. Un modèle à abonné unique est utilisé lorsqu’il n’y a qu’un seul abonné. Un modèle à abonnés multiples est utilisé lorsque plusieurs abonnés sont connectés à une même publication.

  L’abonnement est une demande de copie d’une publication qui doit être transmise à l’abonné. L’abonnement sert à définir les données de la publication à recevoir, ainsi que le lieu et le moment de cette réception. Il existe deux types d’abonnements :

  • L’abonnement « push » : les données modifiées sont transmises de manière forcée depuis un distributeur vers la base de données d’un abonné. Aucune requête de la part de l’abonné n’est nécessaire.
  • Abonnement « pull » : les données modifiées sur l’éditeur sont demandées par un abonné. L’agent s’exécute côté abonné.

  Une base de données d’abonnement est une base de données cible dans le modèle de réplication MS SQL.

  

Dans le modèle « plusieurs éditeurs – plusieurs abonnés » , l’éditeur peut agir en tant qu’abonné sur l’un des serveurs MS SQL. Veillez à éviter tout conflit de mise à jour potentiel lorsque vous utilisez ce modèle de réplication MS SQL Server.

Types de réplication MS SQL Server

La réplication MS SQL Server est une technologie permettant de copier et de synchroniser des données entre des bases de données de manière continue ou régulière, à des intervalles planifiés. En ce qui concerne le sens de la réplication, celle-ci peut être unidirectionnelle, un-à-plusieurs, bidirectionnelle ou plusieurs-à-un. Il existe quatre types de réplication MS SQL Server : la réplication par instantané, la réplication transactionnelle, la réplication pair-à-pair et la réplication par fusion.

La réplication par instantané

La réplication par instantané sert à répliquer les données exactement telles qu’elles apparaissent au moment de la création de l’instantané de la base de données. Ce type de réplication convient aux données qui ne changent pas fréquemment, lorsque le fait que la réplique de la base de données soit plus ancienne que la base de données maître ne pose pas de problème majeur, ou lorsqu’un volume important de modifications est effectué en peu de temps. Le suivi des modifications n’est pas utilisé avec la réplication par instantané.
Par exemple, la réplication par instantané peut être utilisée lorsque les taux de change ou les listes de prix sont mis à jour une fois par jour et doivent être distribués depuis le serveur principal vers les serveurs des succursales.

Réplication transactionnelle

La réplication transactionnelle est une réplication périodique automatisée au cours de laquelle les données sont distribuées depuis une base de données maître vers une réplique en temps réel (ou quasi-temps réel). La réplication transactionnelle est plus complexe que la réplication par instantané. Toutes les transactions effectuées ainsi que l’état final de la base de données sont répliqués, ce qui permet de suivre l’historique complet des transactions sur la réplique.
Au début du processus de réplication transactionnelle, un instantané est appliqué à l’abonné, puis les données sont transférées en continu depuis la base de données maître vers une réplique de base de données à mesure que des modifications sont apportées à ces données. La réplication transactionnelle est largement utilisée comme réplication unidirectionnelle.

Cas d’utilisation de la réplication transactionnelle :

• Création d’un serveur de base de données avec une réplique de base de données à utiliser pour le basculement en cas de défaillance du serveur de base de données principal.
• Réception de rapports sur les opérations effectuées dans les succursales par l’intermédiaire de plusieurs éditeurs dans les succursales et d’un abonné au siège.
• Réplication des modifications dès qu’elles se produisent.
• Les données de la base de données source changent fréquemment.

Réplication pair-à-pair

Réplication pair-à-pair est utilisée pour répliquer les données d’une base de données vers plusieurs abonnés simultanément. Ce type de réplication MS SQL Server peut être utilisé lorsque vos serveurs de bases de données sont répartis à travers le monde. Les modifications peuvent être effectuées sur n’importe quel serveur de base de données. Elles sont ensuite propagées vers tous les serveurs de bases de données. La réplication peer-to-peer peut aider à améliorer l’évolutivité d’une application qui utilise une base de données. Son principe de fonctionnement repose principalement sur la réplication transactionnelle.

Vous pouvez voir ci-dessous comment la réplication peer-to-peer de MS SQL Server peut être utilisée entre des serveurs de bases de données répartis à travers le monde.

La réplication par fusion

La réplication par fusion est un type de réplication bidirectionnelle généralement utilisé dans les environnements serveur-client pour synchroniser les données entre des serveurs de base de données lorsqu’il n’est pas possible de les connecter en permanence. Lorsque la connexion réseau est établie entre les deux serveurs de base de données, les agents de réplication par fusion détectent les modifications apportées aux deux bases de données et modifient celles-ci afin de synchroniser et de mettre à jour leur état. La réplication par fusion est similaire à la réplication transactionnelle, mais les données sont répliquées de l’éditeur vers l’abonné et inversement.

Ce type de réplication de base de données est le plus complexe de tous les types de réplication MS SQL Server et est rarement utilisé. Par exemple, la réplication par fusion peut être utilisée par plusieurs magasins homologues travaillant avec un entrepôt partagé. Chaque magasin est autorisé à modifier les informations contenues dans la base de données de l’entrepôt et, parallèlement, tous les magasins doivent disposer de l’état mis à jour de leurs bases de données après l’expédition des marchandises ou la livraison des fournitures à l’entrepôt. La réplication par fusion peut être utilisée dans les cas où les informations mises à jour doivent être disponibles simultanément pour la base de données principale (ou centrale) et les bases de données des succursales.

Conditions à remplir pour la réplication MS SQL Server

Les ports suivants doivent être ouverts pour le trafic entrant :

• TCP 1433, 1434, 2383, 2382, 135, 80, 443
• UDP 1434

Veillez à configurer le pare-feu Windows et à activer les ports appropriés pour le trafic entrant sur chaque hôte avant d’installer MS SQL Server. Les hôtes participant à la réplication MS SQL doivent se résoudre mutuellement par un nom d’hôte.
Avant de configurer la réplication MS SQL Server, les logiciels suivants doivent être installés pour MS SQL Server :

• .NET Framework – un ensemble de bibliothèques
• MS SQL Server – le logiciel de serveur de base de données
• MS SQL Server Management Studio (SSMS) – logiciel de gestion des bases de données MS SQL via une interface graphique (GUI).

REMARQUE : MS SQL Server 2016 est utilisé pour la configuration dans cet article. Vous pouvez appliquer le même principe pour configurer la réplication dans les versions plus récentes de SQL Server.
N’oubliez pas que si vous installez MS SQL Server 2016 sur la première machine où se trouve la base de données source, vous devez également installer MS SQL Server 2016 sur la deuxième machine pour que la base de données fonctionne correctement. Par exemple, si vous souhaitez configurer la réplication transactionnelle MS SQL, vous pouvez utiliser le deuxième serveur de base de données (sur lequel l’abonné est configuré) dont la version se situe dans une fourchette de deux versions par rapport à celle du serveur de base de données Source sur lequel l’éditeur est configuré. Si la version de l’éditeur sur MS SQL Server est 2016, le distributeur peut être configuré sur les versions 2016, 2017, 2019 et 2022, et l’abonné peut être configuré sur MS SQL Server 2012, 2014, 2016, 2017 et 2019. La version du distributeur ne peut pas être antérieure à celle de l’éditeur. La réplication ne fonctionnera pas si vous installez, par exemple, MS SQL Server 2008 sur la deuxième machine.

Recommandations de base pour la réplication de bases de données MS SQL

Avant de configurer l’environnement pour MS SQL Server, voici quelques facteurs à prendre en compte :

• Il existe des limitations concernant les champs d’identité et les déclencheurs.
• Les publications ne peuvent contenir que des tables dotées d’une clé primaire.
• Il est recommandé de ne pas planifier la création d’instantanés pour les bases de données volumineuses afin d’éviter de mobiliser d’importantes ressources informatiques.
• Faites preuve de prudence lorsque vous modifiez des données dans la réplique de la base de données résidant chez l’abonné. Lorsqu’une transaction modifiant des données est en cours et que ces données ont été modifiées ou supprimées, la réplication peut s’arrêter jusqu’à ce que ce problème soit résolu.

Configuration de l’environnement

Lorsque vous configurez la réplication MS SQL pour la première fois, il est recommandé de le faire d’abord dans un environnement de test. Par exemple, nous configurons la réplication sur des serveurs SQL s’exécutant sur des machines virtuelles. Deux hôtes exécutant Windows Server 2016 et MS SQL Server 2016 sont utilisés dans ce tutoriel pour expliquer la réplication MS SQL Server.
Examinons la configuration de l’environnement de test utilisé pour la rédaction de cet article de blog afin de mieux comprendre la configuration de la réplication MS SQL Server.
Hôte 1

• Adresse IP : 192.168.101.101
• Nom d’hôte : MSSQL01
• ID d’instance MS SQL Server : MSSQLSERVER1

Hôte 2

• Adresse IP : 192.168.101.102
• Nom d’hôte : MSSQL02
• ID d’instance MS SQL Server : MSSQLSERVER2

Les deux machines disposent d’un disque C: et d’un disque D: dans leur configuration de disques.
Vous pouvez désactiver temporairement le pare-feu Windows lors de l’installation de MS SQL Server afin de vous entraîner à configurer la réplication MS SQL Server. Cet article de blog n’aborde pas la procédure d’installation de MS SQL Server, car ce tutoriel se concentre sur la configuration de la réplication MS SQL Server. Dans cet exemple, les deux serveurs MS SQL Server sont installés sans PolyBase.
Vérifiez que vous avez bien installé les fonctionnalités requises pour la réplication MS SQL Server une fois l’installation de MS SQL Server terminée. Notez que les services du moteur de base de données, tels que la réplication SQL Server et les R-Services, doivent être sélectionnés lors de l’installation de MS SQL Server. Le chemin d’installation par défaut est utilisé dans cet exemple (C:Program FilesMicrosoft SQL Server).

Autres paramètres :

• Mode d’authentification mixte (authentification Windows et authentification MS SQL Server)
• Répertoire racine des données : D:MSSQL_Server
• Répertoire de la base de données système : D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Répertoire de la base de données utilisateur : D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Répertoire du journal de la base de données utilisateur : D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Répertoire de sauvegarde : D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup

Une fois MS SQL Server 2016 et SQL Server Management Studio installés sur les machines, vous pouvez préparer vos serveurs MS SQL pour la réplication de bases de données.

Préparation à la réplication MS SQL Server

Vous devez configurer les serveurs avant de pouvoir démarrer la réplication de bases de données. Dans notre exemple, un seul compte Windows sera utilisé pour les agents de réplication MS SQL Server.

1. Créez l’utilisateur mssql sur les deux serveurs et définissez le même mot de passe.
2. Dans cet exemple, l’utilisateur mssql est membre des groupes suivants :
   • Administrateurs (administrateurs locaux sur les machines locales, et non administrateurs de domaine)
   • SQLRUserGroupMSSQLSERVER1
   • SQLServer2005SQLBrowserUser$MSSQL01
3. Vous pouvez modifier les utilisateurs et les groupes en appuyant sur Win+R , en ouvrant CMD , puis en exécutant la lusrmgr.msc commande.

Les deux machines Windows Server utilisées dans cet exemple ne font pas partie d’Active Directory. Si vous utilisez Active Directory, vous pouvez créer l’utilisateur mssql sur le contrôleur de domaine.

Connexion à MS SQL Server

1. Lancez SQL Server Management Studio.
2. Connectez-vous (voir la capture d’écran) en tant que sa par l’authentification SQL Server.
   • MSSQL01MSSQLSERVER1 correspond au nom d’hôte et au nom de l’instance MS SQL sur le premier serveur.
   • MSSQL02MSSQLSERVER2 correspond au nom d’hôte et au nom de l’instance MS SQL sur le deuxième serveur.

   

De la même manière, vous pouvez vous connecter, depuis le deuxième serveur (MSSQL02), à la deuxième instance MS SQL (MSSQLSERVER2). Vous pouvez également vous connecter à la deuxième instance de MS SQL Server (MSSQLSERVER2) depuis le premier serveur MS SQL Server (MSSQL01) en saisissant les identifiants de connexion dans SQL Server Management Studio. Vous pouvez vous connecter aux deux instances de MS SQL Server (MSSQL01 et MSSQL02) dans une seule instance de SQL Server Management Studio.
Pour ce faire, dans l’Explorateur d’objets, cliquez sur Se connecter > Moteur de base de données . Dans ce tutoriel, nous allons nous connecter à MSSQLSERVER1 depuis MSSQL01 et à MSSQLSERVER2 depuis MSSQL02 par l’intermédiaire de SQL Server Management Studio pour configurer les serveurs MS SQL.

Démarrage de l’Agent

Une fois connecté à l’instance MS SQL Server, vous constaterez que l’Agent n’est pas en cours d’exécution. Par défaut, l’Agent SQL Server ne démarre pas automatiquement. Vous pouvez démarrer ce service manuellement, mais il est préférable de le configurer pour qu’il démarre automatiquement après l’amorçage de Windows.

Pour configurer le service Agent afin qu’il démarre automatiquement :

1. Appuyez sur Win+R , exécutez cmd, puis exécutez la services.msc commande.
2. Ouvrez les propriétés du service SQL Server Agent et définissez le type de démarrage sur Automatique .

   

Configuration des utilisateurs pour MS SQL Server

Après vous être connecté à l’instance MSSQLSERVER1 dans SQL Server Management Studio, vous devez configurer les utilisateurs :

1. Accédez à Explorateur d’objets et ouvrez Sécurité > Connexions .
2. Cliquez avec le bouton droit sur Connexions et sélectionnez Nouvelle connexion . Sélectionnez Authentification Windows .
3. Saisissez le nom d’utilisateur mssql dans la section Général .
4. Cliquez sur Rechercher , puis sur Vérifier les noms pour confirmer, et cliquez deux fois sur OK pour enregistrer les paramètres.

   

5. L’utilisateur Windows MSSQL01mssql est désormais ajouté à la liste des utilisateurs autorisés à se connecter à la base de données (de la même manière, ajoutez l’utilisateur mssql aux identifiants de connexion sur la deuxième machine MSSQL02 dans SQL Server Management Studio).
6. Ajoutez l’utilisateur mssql aux rôles serveur sysadmins dans la configuration Sécurité de la base de données dans SQL Server Management Studio.
7. Accédez à MSSQL01MSSQLSERVER1 > Rôles du serveur , cliquez avec le bouton droit sur sysadmin , puis ouvrez Propriétés .
8. Dans la page Membres , cliquez sur Ajouter , saisissez le nom de votre utilisateur mssql, puis cliquez sur Vérifier les noms .
9. Cochez la case correspondant au nom d’utilisateur MSSQL01mssql puis cliquez sur OK .

   

10. Effectuez la même configuration sur votre deuxième machine (MSSQL02 dans ce cas).
11. Redémarrez les deux machines.

    Vous pouvez désormais vous connecter en utilisant l’authentification Windows sur les deux serveurs.

    

Importation d’une base de données à partir d’une sauvegarde

Importons une base de données d’exemple à partir d’une sauvegarde, puis répliquons cette base de données de la première machine vers la seconde. La base de données AdventureWorks2016 est utilisée comme exemple dans cet exercice.

1. Copiez le fichier de sauvegarde de la base de données AdventureWorks2016.bak dans votre répertoire de sauvegarde MSSQL. Dans notre cas, ce répertoire se trouve sur le premier serveur à l’emplacement D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLSauvegardes
2. Importez une base de données d’exemple. Sur la première machine, dans SQL Server Management Studio, accédez à MSSQL01MSSQLSERVER1 , cliquez avec le bouton droit sur Bases de données, puis sélectionnez Restaurer la base de données dans le menu contextuel.

   

3. Dans la fenêtre Restaurer la base de données , sélectionnez les paramètres requis :
   • Source : Périphérique .
   • Cliquez sur les trois points pour parcourir le fichier de sauvegarde de la base de données.
     • Dans la fenêtre Sélectionner les appliances de sauvegarde , sélectionnez le type de support de sauvegarde : fichier .
     • Cliquez sur Ajouter .
   • Sélectionnez le fichier .bak requis – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Cliquez sur OK , puis cliquez à nouveau sur OK .
4. La base de données AdventureWorks2016 a été restaurée avec succès.

   

Vous pouvez importer la base de données à partir d’une sauvegarde sur le deuxième serveur, où la réplique de la base de données sera exécutée. Cette approche vous permet de réduire le trafic réseau, car la réplication commencera par copier les modifications intervenues depuis la création de la sauvegarde, sans copier l’intégralité des données de la base de données vers une base de données vide.
Restaurez la base de données à partir d’une sauvegarde sur le deuxième serveur et renommez la base de données en AdventureWorks2016r , où « r » signifie « réplique ».
Au final, nous obtenons :

Après l’importation de la base de données, vous devez effectuer quelques réglages pour préparer vos serveurs MS SQL

1. Sur la machine MSSQL01 , accédez à MSSQL01MSSQLSERVER1 > Sécurité > Connexions , sélectionnez MSSQL01mssql . Cliquez avec le bouton droit (ou double-cliquez) sur mssql et sélectionnez Propriétés .
2. Dans Rôles du serveur , cochez la case en regard du rôle dbcreator .

   

3. Sur la page Mappage des utilisateurs , sélectionnez les utilisateurs mappés à cette connexion et cochez la case base de données AdventureWorks2016 (sélectionnez AdventureWorks2016r sur le deuxième serveur en conséquence).
4. Dans la section « Appartenance aux rôles de la base de données » , cochez la case « db_owner » .

   

5. Cliquez sur « OK » pour enregistrer les paramètres.

Effectuez la même configuration sur la machine MSSQL02. Vous pouvez ensuite configurer les composants MS SQL Server nécessaires à la réplication de la base de données.

Trouvez la solution adaptée à votre environnement

Découvrez les différentes éditions et les modèles d'octroi de licences flexibles de NAKIVO, conçus pour offrir une protection des données de niveau entreprise à un coût adapté à votre budget.

AFFICHER LES TARIFS

Configuration de la réplication de base de données

La configuration de la réplication en mode graphique est la méthode la plus pratique. La configuration suivante s’effectue dans SQL Server Management Studio. Cet exemple porte sur la réplication transactionnelle de base de données, car il s’agit de l’un des types de réplication les plus utilisés dans MS SQL Server.
La vue sur le serveur de base de données principal (MSSQL01MSSQLSERVER1) et celle sur le deuxième serveur (MSSQL02MSSQLSERVER2) dans SQL Server Management Studio sont illustrées dans la capture d’écran ci-dessous.

Configuration de la distribution

La distribution peut être utilisée pour plusieurs éditeurs et abonnés. Dans cet exemple, la distribution est configurée sur le serveur principal sur lequel la base de données source est stockée. Sur le serveur principal (MSSQL01MSSQLSERVER1), cliquez avec le bouton droit sur Réplication et, dans le menu contextuel, sélectionnez Configurer la distribution .

L’assistant de configuration de la distribution s’ouvre.

1. Distributeur . Sélectionnez l’instance de base de données en cours d’exécution sur le serveur principal (MSSQL01MSSQLSERVER1) pour qu’elle fasse office de distributeur dans cet exemple. Cliquez sur Suivant fois pour passer à l’étape suivante de l’assistant.
2. Démarrage de SQL Server Agent . Si vous n’avez pas configuré SQL Server Agent pour qu’il démarre automatiquement, comme expliqué ci-dessus, le message suivant s’affichera. Sélectionnez Oui, configurer le service SQL Server Agent pour qu’il démarre automatiquement .

   

3. Dossier des instantanés . Vous pouvez conserver le chemin d’accès par défaut ici. Un instantané est nécessaire pour initialiser la réplication. Assurez-vous qu’il y a suffisamment d’espace libre sur le disque à l’emplacement où se trouve votre répertoire d’instantanés. L’espace libre doit correspondre au moins à la taille de la base de données répliquée.
4. Base de données de distribution . Saisissez le nom de la base de données de distribution. Vous pouvez conserver le nom par défaut ( distribution ) ainsi que les dossiers pour le fichier de la base de données de distribution et le fichier journal.

   

5. Éditeurs . Définissez les éditeurs de réplication MS SQL Server pouvant accéder au distributeur. Cochez la case située à côté du nom de la base de données de distribution sur l’instance principale de MS SQL Server (qui héberge une base de données source qui sera répliquée). Dans cet exemple, il s’agit de l’instance MSSQL01MSSQLSERVER1, et le nom de la base de données de distribution est distribution .
6. Actions de l’assistant . Sélectionner Configurer la distribution pour configurer la distribution lors de l’étape finale de l’assistant. Dans cet exemple, nous n’allons pas générer un fichier de script à exécuter plus tard.

   

7. Terminer l’assistant . Vérifiez le résumé de la configuration de la distribution et cliquez sur Terminer pour créer le distributeur.

   

8. Le Succès doit apparaître si le distributeur a été créé et configuré avec succès.

   

Si vous voyez qu’une erreur s’est produite lors de la configuration de l’Agent SQL Server pour qu’il démarre automatiquement, allez à la configuration des services et vérifiez le mode de démarrage de l’Agent SQL Server (voir comment configurer le démarrage de l’agent plus haut dans cet article de blog).
Vous pouvez également ouvrir les propriétés de l’Agent SQL Server dans SQL Server Management Studio et vérifier l’état du service et les options de redémarrage. Faites un clic droit sur Agent SQL Server à la fin de la liste dans Explorateur d’objets et appuyez sur Propriétés pour afficher ou modifier les propriétés de l’agent.

Configurer le publieur

Une fois la distribution configurée, vous pouvez configurer le publieur. Le publieur doit être configuré sur le serveur principal (MSSQL01MSSQLSERVER1) où la base de données maîtresse à répliquer est stockée. Sélectionner Réplication , faites un clic droit sur Publications locales et, dans le menu contextuel, sélectionnez Nouvelle publication .

L’ Assistant de nouvelle publication s’ouvre.

1. Base de données de publication . Sélectionnez la base de données que vous souhaitez répliquer ( AdventureWorks2016 dans ce cas). Appuyez sur Suivant à chaque étape de l’assistant pour continuer.

   

2. Type de publication . Pour cette étape, vous pouvez sélectionner des types de réplication MS SQL Server pour une base de données. Sélectionnons une publication transactionnelle, qui est un type de réplication couramment utilisé.
3. Articles . Sélectionnez les objets nécessaires, tels que les tables, les procédures, les vues, les vues indexées et les fonctions définies par l’utilisateur pour les publier en tant qu’articles. Il est possible de sélectionner la réplication des champs personnalisés dans les tables et de sélectionner les propriétés des articles si nécessaire. Dans cet exemple, certaines tables sont sélectionnées.

   

4. Filtrer les lignes de tables . Aucun filtre n’est ajouté dans cet exemple (c’est la configuration par défaut des filtres). Vous pouvez ajouter des filtres si nécessaire.
5. Agent d’instantané . Spécifiez quand exécuter l’Agent d’instantané. Configurons l’agent pour qu’il s’exécute immédiatement. Sélectionnez Créer un instantané immédiatement et conserver l’instantané disponible pour initialiser les abonnements .

   

6. Sécurité de l’agent . Sélectionnez Utiliser les paramètres de sécurité de l’agent d’instantanés . Cliquez sur le bouton Paramètres de sécurité pour sélectionner le compte sous lequel l’agent s’exécutera.

   Dans la fenêtre Sécurité de l’agent d’instantanés qui s’ouvre, saisissez les identifiants de connexion de l’utilisateur Windows mssql que vous avez créé précédemment. Sélectionnez « Se connecter à l’éditeur » par usurpation de l’identité du compte de processus . Cliquez sur OK pour enregistrer les paramètres et revenir à l’assistant.

   

   Après avoir défini l’utilisateur requis, vous pouvez le voir dans les sections « Snapshot Agent » et « Log Reader Agent » .

   

7. Actions de l’assistant . Cochez la case du haut pour créer la publication lors de la dernière étape de l’assistant.
8. Terminez l’assistant . Vérifiez la configuration de votre publication, puis cliquez sur Terminer pour créer une nouvelle publication.

   

Dans la fenêtre Création d’une publication , vous pouvez suivre la progression de la création d’une nouvelle publication. Patientez quelques instants ; si tout s’est déroulé correctement, le statut « Réussite » devrait s’afficher.

La publication est désormais créée et vous pouvez la consulter dans l’Explorateur d’objets en accédant à Réplication > Publications locales .

Configuration de l’abonné

Comme vous vous en souvenez, la réplication MS SQL Server peut être de type « pull » ou « push ». Si vous configurez une réplication « push », vous devez configurer l’abonné pour qu’il exécute des agents sur le serveur de base de données principal (MSSQL01 dans ce cas). Si vous configurez une réplication « pull », l’abonné doit être configuré pour exécuter des agents sur la deuxième machine (MSSQL02), c’est-à-dire la machine sur laquelle la réplique de la base de données sera créée.
Configurons la réplication « push » et créons un nouvel abonnement sur le premier serveur MS SQL Server (MSSQL01MSSQLSERVER1) où réside la base de données « master ».
Dans l’Explorateur d’objets, accédez à Réplication , cliquez avec le bouton droit sur Abonnements locaux et, dans le menu contextuel, sélectionnez Nouveaux abonnements .

L’assistant Nouvel abonnement s’ouvre.

1. Publication . Sélectionnez la publication pour laquelle vous souhaitez créer un nouvel abonnement. Dans notre exemple, le nom de l’éditeur est MSSQL01MSSQLSERVER1 et le nom de la publication (créée précédemment) est AdvWorks_Pub . Cliquez sur Suivant à chaque étape de l’assistant pour continuer.
2. Emplacement de l’agent de distribution . Sélectionnez le type de réplication en choisissant soit un abonnement « push », soit un abonnement « pull ». Dans notre exemple, nous souhaitons que tous les agents s’exécutent côté serveur source ; par conséquent, la première option est sélectionnée pour créer un abonnement « push ». Cela vous permet de gérer la réplication MS SQL Server de manière centralisée.

   

3. Abonnés . Par défaut, le serveur sur lequel vous exécutez l’assistant (MSSQL01MSSQLSERVER1 dans ce cas) s’affiche comme abonné, et la base de données d’abonnement n’est pas définie. Ajoutons un nouvel abonné et sélectionnons une base de données d’abonnement située sur le deuxième serveur de base de données (MSSQL01MSSQLSERVER2). Cliquez sur Ajouter un abonné et, dans le menu contextuel, sélectionnez Ajouter un abonné SQL Server .
   • Dans la fenêtre contextuelle, saisissez les identifiants de connexion de la deuxième instance de MSSQL Server (MSSQL01MSSQLSERVER2 dans notre cas) et cliquez sur Se connecter .

     

   • Cochez la case correspondant à votre deuxième serveur sur lequel votre réplique de base de données sera stockée (MSSQL02MSSQLSERVER2) et, dans le Base de données d’abonnement menu déroulant, sélectionnez une nouvelle base de données ou une base de données existante restaurée à partir d’une sauvegarde pour l’utiliser comme réplique de base de données.

     Dans notre exemple, la base de données AdventureWorks2016r a été créée sur le deuxième serveur en restaurant la base de données principale (source) AdventureWorks2016 à partir d’une sauvegarde afin de lancer la réplication. La réplication est lancée en répliquant uniquement les nouvelles données, et non en copiant l’intégralité de la base de données après le démarrage du processus de réplication. Ainsi, AdventureWorks2016r est sélectionnée comme base de données d’abonnement dans l’exemple actuel.

     

4. Sécurité de l’agent de distribution . Cliquez sur le bouton avec les trois points (…) et sélectionnez l’utilisateur ainsi que les autres options de sécurité pour l’agent de distribution.

   Dans la fenêtre Sécurité de l’agent de distribution qui s’ouvre, configurez l’agent de distribution pour qu’il s’exécute sur l’hôte MSSQL01 sous le compte utilisateur mssql . Saisissez le mot de passe de l’utilisateur Windows mssql . Sélectionnez Se connecter au distributeur en usurpant l’identité du compte de processus puis sélectionnez Se connecter à l’abonné en usurpant l’identité du compte de processus . Cliquez sur OK pour enregistrer les paramètres.

   

   Vos propriétés d’abonnement sont désormais configurées.

   

5. Planification de la synchronisation . Sélectionnez l’agent situé sur le distributeur pour Exécuter en continu pour l’abonné actuel.
6. Initialiser les abonnements . Cochez la case Initialiser et, dans le menu déroulant, sélectionnez Immédiatement pour définir le moment de l’initialisation de l’abonnement. Vous pouvez également sélectionner l’option Optimisé pour la mémoire si nécessaire.

   

7. Actions de l’assistant . Cochez la case du haut pour créer le ou les abonnements à la fin de l’assistant.
8. Terminer l’assistant . Vous pouvez vérifier les paramètres de votre abonnement et cliquer sur Terminer pour créer l’abonnement.

   

9. Attendez que l’abonnement soit créé. Si vous voyez le Succès statut, cela signifie que l’abonnement a été créé avec succès.

   

10. Une fois la réplication configurée dans SQL Server, trois tâches s’affichent dans l’Explorateur d’objets ; vous pouvez les consulter en accédant à Agent SQL Server > Tâches .

    

Finalisation de la configuration de la réplication

Une fois que vous avez configuré le distributeur, l’éditeur et l’abonné, vous pouvez vérifier l’état de la réplication MS SQL Server.

1. Sur le premier serveur (MSSQL01MSSQLSERVER1), lancez le moniteur de réplication pour consulter le statut de la réplication MS SQL Server. Dans SQL Server Management Studio, sélectionnez votre instance MS SQL Server (MSSQLSERVER1), accédez à Réplication , cliquez avec le bouton droit sur Publications locales et, dans le menu contextuel, sélectionnez Lancer le moniteur de réplication .

   

2. Dans notre cas, une erreur Agent de lecture des journaux est survenue. Pour consulter les détails de l’erreur, sélectionnez la base de données source (l’éditeur) dans le volet de gauche, sélectionnez l’onglet Agents dans le volet de droite, puis double-cliquez sur le nom de l’erreur.

   

3. Dans la fenêtre qui s’ouvre, vous pouvez consulter l’historique de l’agent et les messages d’erreur. Les messages d’erreur sont les suivants :
   • Le processus n’a pas pu exécuter sp_replcmds sur MSSQL01MSSQLSERVER1. Source : MSSQL_REPL. Numéro d’erreur : MSSQL_REPL20011).
   • Impossible d’exécuter en tant que principal de la base de données car le principal « dbo » n’existe pas, ce type de principal ne peut pas être usurpé ou vous n’avez pas la permission. (Source: MSSQLServer, numéro d’erreur: 15517).

   

   Le deuxième message d’erreur suggère qu’un certain type de permission est manquant. Résolvons cette erreur.

4. Créez une nouvelle requête dans MS SQL Management Studio et exécutez cette requête. Dans la fenêtre principale, cliquez sur le bouton Nouvelle requête .
5. Dans la section de requête SQL de la fenêtre principale, entrez la requête suivante:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Cliquez sur le bouton Exécuter .

   

   Commande(s) exécutée(s) avec succès.

6. Ensuite, allez à MSSQL01MSSQLSERVER1 > Réplication > Publications Locales > [AdventureWorks2016]: AdvWorks_Pub . Faites un clic droit sur le nom de la publication et, dans le menu contextuel, sélectionnez Voir le statut de l’agent instantané . Vous pouvez cliquer sur Action > Actualiser pour actualiser le statut et Réinitialiser toutes les abonnements pour appliquer un instantané à chaque abonné.

   Maintenant tout est résolu, aucune erreur n’est affichée et la réplication de MS SQL Server devrait fonctionner.

   

Vérification comment fonctionne la réplication

Voyons la réplication de MS SQL Server en action. Affichez le contenu d’une table de la base de données AdventureWorks2016 stockée sur le premier serveur MS SQL ( MSSQL01MSQLSERVER1 ). Dans notre exemple, nous allons sélectionner toutes les données de la table Person.AddressType . Pour ce faire, exécutez la requête:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
Le résultat de l’exécution de la requête est affiché dans la capture d’écran ci-dessous:

Exécutez une requête similaire sur le deuxième serveur pour afficher toutes les données du Person.AddressType de la base de données AdventureWorks2016r stockée sur MSSQL02MSSQLSERVER2.
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Si vous comparez les captures d’écran ci-dessus et ci-dessous, le contenu du Person.AddressType est identique sur les deux bases de données (une base source sur le premier serveur et la base cible qui est un réplique de base sur le deuxième serveur).

Supprimons une ligne dans la table PersonAddressType de la base de données AdventureWorks2016 (source) sur le premier serveur (MSSQL01MSSQLSERVER1). Exécutez la requête suivante pour supprimer une ligne qui contient « Billing » dans le nom, puis affichez le contenu de la table après cette opération :
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;

Comme vous pouvez le constater, la première ligne avec l’ AddressTypeID 1 et le nom « Billing » a été supprimée de la table Person.AddressType dans la base de données AdventureWorks2016 sur la machine MSSQL01 .
La réplication transactionnelle est en cours. Vérifions le contenu de la table Person.AddressType de la base de données AdventureWorks2016r sur la machine MSSQL02 . Exécutez à nouveau une requête similaire à celle ci-dessus pour afficher le contenu de la table :
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
À la suite de la réplication, la première ligne a également été supprimée de la table Person.AddressType de la base de données secondaire qui sert de réplique ( AdventureWorks2016r ). Vous pouvez voir les résultats sur la capture d’écran ci-dessous.

La réplication de base de données dans SQL Server travaille correctement.

Conclusion

Il existe quatre types de réplication MS SQL Server : la réplication par instantané, la réplication transactionnelle, la réplication pair-à-pair et la réplication par fusion. La réplication transactionnelle étant largement utilisée, nous avons configuré ce type de réplication MS SQL Server dans cet article de blog. Le distributeur, l’éditeur et l’abonné doivent être configurés pour que la réplication de la base de données fonctionne. L’abonné peut être configuré sur un serveur source (réplication « push ») ou sur un serveur cible (réplication « pull »).
Cependant, vous devriez envisager d’utiliser à la fois la réplication et à sauvegarder les bases de données MS SQL pour augmenter les chances de réussite de récupération des données d’une base de données.

Essayez NAKIVO Backup & Replication

Profitez d'un essai gratuit pour découvrir toutes les fonctionnalités de protection des données de la solution. 15 jours gratuits. Aucune limitation en termes de fonctionnalités ou de capacité. Aucune carte bancaire requise.

ESSAYEZ-LE GRATUITEMENT