[SQL Server] Réplication : mise-en-place de la réplication de snapshot avec SQL Server 2008 (R2)

Ce billet présente comment mettre-en-place la réplication de snapshot avec SQL Server 2008 (R2) – même mode opératoire que pour les versions 2012 et 2014 -, via SQL Server Management Studio (SSMS). Pour comprendre les différents concepts associés à la réplication de snapshot, vous pouvez aller ici, et pour les prérequis, vous pouvez aller .


A noter qu’avant de commencer, il faudra configurer un distributeur comme vu ici.

Contexte de nos besoins

Pour notre exemple de mise-en-place, nous allons configurer un mécanisme de réplication de snapshot :

  • Réplication entre une base MaBase1 et une autre base MaBase4 en ne prenant en compte que l’ensemble des tables, vues et procédures de ces 2 bases.
  • Exemple de besoin dans le monde réel : rafraîchissement chaque nuit d’une base de données (de test, par exemple) à partir d’une autre.
  • Planification des agents de réplication souhaitée pour notre exemple :
    • Snapshot Agent : lancement une fois par jour, à 2h du matin.
    • Distribution Agent : lancement une fois par jour, à 3 h du matin.
Le choix des planifications des agents des différents types de réplication sont, ici, bien sûr des exemples. En temps normal, il faudra les adapter en fonction de divers facteurs : les besoins en termes de synchronisation, la charge sur les serveurs, etc…

Du côté des structures de données :

  • MaBase1 possède 4 tables MaTable1, MaTable2, MaTable3 et MaTable4, 3 vues MaVue1, MaVue2 et MaVue3, 3 procédures stockées usp_SP1, usp_SP2 et usp_SP3.
  • MaBase4 est identique à MaBase1.

Du côté de la localisation des bases de données :

  • MaBase1 est placée sur l’instance SRV-SQL01 qui fera figure d’éditeur (et de distributeur).
  • MaBase4 est sur SRV-SQL02.
Notons que :

  • Le mode de récupération choisi pour la réplication de snapshot sera push (les agents de réplication seront placés sur le distributeur de façon à ce que les répliques soient « poussées » vers l’abonné).
  • Le distributeur de chaque réplication sera placé sur la même instance que l’éditeur concerné.

Et supposons que :

  • Les bases MaBase1 et MaBase4 ont déjà été créées (même s’il n’est fondamentalement pas obligatoire de le faire pour la base abonnée, l’Assistant de configuration pouvant très bien s’en charger) et MaBase1 remplie.
  • Chaque abonné n’est pas sur une instance identique à celle de son éditeur..
  • La réplication sera unidirectionnelle (de l’éditeur vers l’abonné).

Modus operandi de mise-en-œuvre de la réplication de snapshot

Armés des informations de la section précédente, démarrons maintenant la conception de la réplication de snapshot sur l’instance du futur éditeur (SRV-SQL01) :

  • Lancement de l’assistant de création de la publication :


Une fenêtre de bienvenue s’ouvrira :


  • Sélection de la base MaBase1 comme base de publication :


  • Choix du type de réplication : publication instantanée (snapshot) :


  • Sélection des articles de la base de publication à répliquer :


    Vous pouvez éventuellement définir les propriétés de chaque article (ou d’un ensemble d’articles). Exemple pour la procédure stockée usp_SP1 (les valeurs sont celles par défaut) :


    Un clic sur Définir les propriétés de l’article Procédure stockée en surbrillance ouvre la boîte modale suivante :


    En règle générale, les valeurs par défaut sont largement suffisantes. Mais nous allons choisir de copier les propriétés étendues pour chaque procédure stockée en laissant les autres valeurs de propriétés intactes. Et pour les vues, on choisit de copier les triggers et propriétés étendues, tout en laissant les autres propriétés intactes :


    Si, par ailleurs, vous accédez aux propriétés de l’ensemble des tables à répliquer, vous obtiendrez cet exemple de fenêtre modale :


    Choisissez de copier les indexes non-cluster, les clés étrangères, les spécifications de valeurs par défaut, les triggers et contraintes de validation.


    Notons toutefois qu’en règle générale, les propriétés par défaut sont largement suffisantes dans de très nombreux cas de réplication. D’autant plus que chaque copie nécessitant un snapshot, cela reviendrait comme à recréer manuellement les structures à copier dans les bases abonnées.

Une bonne pratique générale consiste à créer manuellement ces structures de données (via génération de scripts sous SSMS sur l’éditeur, par exemple) sur les bases abonnées concernées de façon à ne pas avoir à lancer un nouveau snapshot si celui-ci s’avère long et coûteux.

On peut également copier les autres types d’indexes (filtrés, XML, spatiaux,…) et éventuellement (mais ce n’est pas le cas dans notre exemple) les autorisations d’accès aux objets (assurez-vous, dans ce cas, que les bons logins et utilisateurs existent sur les abonnés concernés).

  • Lisez l’avertissement de l’assistant qui parle du référencement des articles et des contraintes :


    Ce qu’il faut savoir c’est que s’il existe un article référençant un objet (table, vue, index…) non sélectionné dans la liste des articles à répliquer, alors ledit objet doit obligatoirement être également sélectionné en tant qu’article (ce qui nécessitera un retour à l’étape précédente). Dans notre exemple précis, seules les vues référencent d’autres objets.

  • Filtre des lignes des tables :


    Vu que l’on ne compte pas filtrer les lignes de nos tables ou vues, on peut ignorer cette étape.

  • Planification de l’agent de snapshot :


    On clique sur Modifier… pour planifier la Snapshot Agent plutôt que d’utiliser sa planification par défaut (qui est à toutes les heures) :


    Comme le montre la figure ci-dessus, on opte pour une planification d’un snapshot tous les jours, à 2h du matin. On en profite également pour indiquer à l’assistant le lancement du snapshot après la configuration (ce qui est facultatif) :


  • Configuration de la sécurité du Snapshot Agent :


    On choisit de laisser le Snapshot Agent s’exécuter avec le compte d’exécution du SQL Agent. En outre, on spécifie que le login SQL toto (préalablement créé sur l’instance hébergeant la base de publication) sera utilisé pour se connecter à l’éditeur (ou serveur de publication).


  • Choix du comportement de l’assistant post-configuration :


    On n’oublie pas de choisir de générer le script de configuration de l’éditeur après configuration par l’assistant. Un tel script pourra être utile pour toute reconfiguration de l’éditeur en cas de sinistre ou réutilisation ailleurs.

  • Spécification du chemin d’accès du fichier de configuration de l’éditeur :


    Le chemin d’accès choisi et le nom du script sont, bien sûr, arbitraires. Il faudra conventionnellement placer le fichier de configuration de la publication dans un dossier dédié.

  • Spécification du nom de la publication, validation et lancement :


    Appelons notre publication MaBase1_Snapshot.

  • Confirmation du bon déroulement de la configuration :


Au sein de l’explorateur d’objets de SSMS, on peut noter la présence de la publication fraîchement configurée pour l’instance SRV-SQL01 dans la liste des publications locales :


Toujours au sein de l’instance SRV-SQL01, on peut également constater la présence du job de lancement du Snapshot Agent :


Il reste désormais à créer l’abonné MaBase4. Pour ce faire, toujours au sein de l’instance de publication SRV-SQL01 :

  • Lancement de l’assistant de configuration de nouveaux abonnements :


Une fenêtre de bienvenue s’ouvrira :


  • Sélection de la publication MaBase1_Snapshot préalablement créée sur le serveur de publication :


  • Choix du mode de récupération :


    On opte pour le mode push.

  • Sélection de l’abonné (serveur de souscription et base abonnée MaBase4) :


    Après ajout de l’abonné (instance SRV-SQL02) et sélection de la bonne base MaBase4 :


  • Configuration de la sécurité du Distribution Agent :

On choisit de l’exécuter dans le contexte du compte d’exécution du SQL Agent de l’instance où la base de distribution est hébergée (SRV-SQL01). En outre, on spécifie un login SQL de connexion vers l’instance où se trouve l’abonné MaBase4 (SRV-SQL02).


La configuration de la sécurité de l’agent donne l’aperçu suivant :


  • Planification du lancement du Distribution Agent :


    On choisit de planifier son exécuter à toutes les jours, à 3h du matin (environ 1h après le dernier snapshot) :


    On valide ensuite la planification :


  • Initialisation de l’abonnement :


    On peut choisir d’initialiser l’abonnement immédiatement ou lors de la prochaine synchronisation (i.e., lancement du Distribution Agent). On rappelle que plus haut, un snapshot a été configuré.

  • Choix du comportement de l’assistant post-configuration de l’abonné :


    On n’oublie pas de préciser à l’assistant de générer le script de configuration de l’abonné. Un tel script pourra être utile pour toute reconfiguration de l’abonné en cas de sinistre ou réutilisation ailleurs.

  • Spécification du chemin d’accès du fichier de configuration de l’éditeur :


  • Validation de la configuration :


  • Confirmation du bon déroulement de la configuration :


Au terme de nos configurations, au sein de SSMS, on peut souligner :

  • Sur l’instance SRV-SQL01 (éditeur) la présence de jobs dédiés au Snapshot Agent et au Distribution Agent :


    Le job SRV-SQL01-MaBase1-MaBase1_Snapshot-SRV-SQL02-3 a été automatiquement créé. Notons bien que :

    • SRV-SQL01-MaBase1-MaBase1_Snapshot-1 effectue un snapshot pour l’ensemble des abonnés de la publication MaBase1_Snapshot. Il s’agit du job d’exécution du Snapshot Agent.
    • SRV-SQL01-MaBase1-MaBase1_Snapshot-SRV-SQL02-3 effectue une distribution des données vers l’abonné MaBase4 de l’instance SRV-SQL02 qui est membre de la publication MaBase1_Snapshot. Il s’agit du job d’exécution du Distribution Agent.
  • Sur l’instance SRV-SQL02 (abonné), la présence de notre abonné :


Pour aller plus loin…

Nous avons pu voir que la mise-en-place de la réplication de snapshot avec SQL Server 2008 (R2) n’était pas insurmontable. Nous aborderons, très prochainement, quelques éléments d’audit et opérations avancées sur la réplication.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s