Procédure : configurer la réplication transactionnelle d'égal à égal (programmation Transact-SQL de la programmation)
Cette rubrique décrit comment configurer et gérer une topologie de réplication d'égal à égal en utilisant des procédures stockées. Elle explique tout d'abord comment créer une topologie à trois nœuds, puis comment ajouter et connecter un nouveau nœud. Des procédures distinctes sont présentées pour l'ajout d'un nœud qui exécute SQL Server 2005 et pour l'ajout d'un nœud qui exécute SQL Server 2008 ou une version ultérieure de SQL Server. Cette rubrique ne contient pas d'informations sur les paramètres utilisés pour détecter les conflits dans la réplication d'égal à égal. Pour plus d'informations sur la détection des conflits, consultez Procédure : configurer la détection de conflit pour la réplication transactionnelle d'égal à égal (programmation Transact-SQL de la programmation).
La configuration de cette topologie est très semblable à la configuration d'une série de publications et d'abonnements transactionnels standard. Le principal élément à noter est la méthode d'initialisation des nœuds et les valeurs qui sont spécifiées pour le paramètre @sync_type de sp_addsubscription :
Le premier nœud d'une topologie contient la base de données de publication d'origine ; il n'est donc pas nécessaire de l'initialiser avec un schéma et des données. Par conséquent, vous devez affecter la valeur replication support only (prise en charge de la réplication uniquement) pour les abonnements qui répliquent des données d'autres nœuds vers le premier nœud. Cette option garantit que les objets requis par la réplication sont copiés sur le premier nœud.
Une fois le premier nœud configuré, les autres nœuds sont généralement initialisés à partir d'une sauvegarde du premier. Par conséquent, vous devez affecter la valeur initialize with backup ou replication support only (initialisation avec sauvegarde) pour les abonnements qui répliquent des données du premier nœud vers les autres nœuds. La valeur replication support only peut être utilisée si le système ne présente aucune activité, mais la valeur de initialize with backup est requise si le système est actif. La valeur initialize with backup garantit que toutes les transactions pertinentes sont répliquées vers un nouveau nœud.
Une fois un nœud initialisé, il n'est pas nécessaire que les abonnements qui répliquent des données d'autres nœuds initialisent une nouvelle fois le nœud. Par conséquent, vous devez affecter la valeur replication support only ou initialize from lsn (initialiser à partir du numéro séquentiel dans le journal). La valeur replication support only peut être utilisée si le système ne présente aucune activité, mais la valeur de initialize from lsn est requise si le système est actif. La valeur initialize from lsn garantit que toutes les transactions pertinentes sont répliquées vers un nouveau nœud.
SQL Server 2005 ne prend pas en charge initialize from lsn. Si une topologie inclut des nœuds SQL Server 2005, vous pouvez connecter un nouveau nœud à un nœud existant, mais vous devez suspendre le système pour le connecter à plusieurs nœuds. Suspendre un système implique d'interrompre toute activité sur les tables publiées de tous les nœuds et de vérifier que chaque nœud a reçu toutes les modifications des autres nœuds. Ce processus est décrit en détail dans la dernière section de cette rubrique.
Procédure
Si vous configurez une topologie pour des bases de données qui sont actives, utilisez la procédure suivante pour ajouter le premier et le deuxième nœud (Nœud A et Nœud B). Utilisez ensuite la seconde procédure pour le Nœud C et tout nœud supplémentaire. Dans cette procédure, la valeur initialize from lsn est affectée à @sync_type. Cette option vous permet de configurer une topologie alors que le système est actif sans manquer aucune transaction.
Pour configurer une réplication d'égal à égal entre trois nœuds
Configurez chaque nœud en tant que serveur de publication et associez-le à un serveur de distribution locale ou distant. Si vous utilisez un serveur de distribution distant, nous vous conseillons de ne pas utiliser le même pour tous les nœuds dans la mesure où il représenterait un point de défaillance unique potentiel. Pour plus d'informations, consultez Procédure : configurer la publication et la distribution (programmation Transact-SQL de la réplication).
Au niveau du Nœud A, exécutez sp_addpublication. Affectez la valeur true à @enabled_for_p2p, la valeur active à @status et la valeur true à @allow_initialize_from_backup. Pour ajouter des articles à la publication, exécutez sp_addarticle.
Les données initiales doivent être présentes sur chaque nœud avant configuration de la topologie. Utilisez les fonctionnalités de sauvegarde et de restauration de SQL Server pour initialiser les données de la publication sur chaque nœud de la topologie. La sauvegarde doit venir du premier nœud configuré ; dans le cas présent, il s'agit du Nœud A. La sauvegarde doit être réalisée après création de la publication sur le Nœud A et son activation pour la réplication d'égal à égal. Cette rubrique suppose qu'aucune activité ne se produit sur aucun des nouveaux nœuds avant que tous les nœuds ne soient ajoutés ; par conséquent, vous pouvez utiliser la même sauvegarde pour initialiser chaque nœud.
Si une activité se produit sur un des nouveaux nœuds avant que tous les nœuds ne soient ajoutés, vous devez réaliser une nouvelle sauvegarde après ajout de chaque nœud et sa synchronisation au moins une fois avec le Nœud A. Cela garantit que la sauvegarde du Nœud A contient les métadonnées de tous les autres nœuds. Par exemple, si vous ajoutez le Nœud B et le Nœud C avec une activité sur ces nœuds : initialisez le Nœud B avec une sauvegarde du Nœud A, configurez et synchronisez le Nœud B, réalisez une nouvelle sauvegarde du Nœud A, initialisez le Nœud C avec la nouvelle sauvegarde, puis configurez et synchronisez le Nœud C.
Pour plus d'informations sur la sauvegarde et la restauration de bases de données, consultez Sauvegarde et restauration de bases de données dans SQL Server.
Important
Lorsque vous restaurez la base de données, ne spécifiez pas l'option KEEP_REPLICATION (pour Transact-SQL) ni l'option Conserver les paramètres de réplication (pour SQL Server Management Studio). La réplication configurera la base de données comme il se doit lorsque vous exécuterez l'Assistant Configurer la topologie d'égal à égal.
Dans la mesure où une sauvegarde contient la base de données complète, chaque base de données d'homologues contient une copie complète de la base de données de publication lors de son initialisation. La sauvegarde peut contenir des tables qui ne sont pas spécifiées comme articles pour la publication. C'est à l'administrateur ou à l'application que revient la tâche de supprimer tous les objets ou données inutiles après restauration de la sauvegarde. Lors des synchronisations ultérieures, les modifications de données sont uniquement répliquées si elles s'appliquent aux tables spécifiées comme articles.
Au niveau du Nœud A, exécutez sp_addsubscription. Spécifiez le nom de la publication créée au niveau du Nœud A pour @publication, le nom de Nœud B pour @subscriber, le nom de la base de données cible sur le Nœud B pour @destination_db, affectez la valeur initialize with backup à @sync_type et affectez les valeurs appropriées aux paramètres @backupdevicetype et @backupdevicename.
Au niveau du Nœud A, exécutez de nouveau sp_addsubscription. Cette fois, spécifiez le nom de la publication pour @publication, le nom du Nœud C pour @subscriber, le nom de la base de données cible sur le Nœud C pour @destination_db, affectez la valeur initialize with backup à @sync_type et affectez les valeurs appropriées aux paramètres @backupdevicetype et @backupdevicename.
Au niveau du Nœud B, exécutez sp_addpublication. Spécifiez le nom de la publication pour @publication, affectez la valeur true à @enabled_for_p2p, la valeur active à @status et la valeur true à @allow_initialize_from_backup. Pour ajouter des articles à la publication, exécutez sp_addarticle.
Au niveau du Nœud B, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud A pour @subscriber, le nom de la base de données cible sur le Nœud A pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud B, exécutez de nouveau sp_addsubscription. Cette fois, spécifiez le nom de la publication pour @publication, le nom du Nœud C pour @subscriber, le nom de la base de données cible sur le Nœud C pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud C, exécutez sp_addpublication. Spécifiez le nom de la publication pour @publication, affectez la valeur true à @enabled_for_p2p, la valeur active à @status et la valeur true à @allow_initialize_from_backup. Pour ajouter des articles à la publication, exécutez sp_addarticle.
Au niveau du Nœud C, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud A pour @subscriber, le nom de la base de données cible sur le Nœud A pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud C, exécutez de nouveau sp_addsubscription. Cette fois, spécifiez le nom de la publication pour @publication, le nom du Nœud B pour @subscriber, le nom de la base de données cible sur le Nœud B pour @destination_db et affectez la valeur replication support only à @sync_type.
Si les tables publiées contiennent des colonnes d'identité, après l'opération de restauration, la plage d'identité que vous avez affectée aux tables du Nœud A sera également utilisée dans les tables du Nœud B et du Nœud C. Vous devez utiliser DBCC CHECKIDENT pour réattribuer les tables du Nœud B et du Nœud C et ainsi garantir qu'une plage différente est utilisée pour chacun.
Pour plus d'informations sur la gestion des plages d'identité, consultez la section « Affectation de plages pour la gestion de plages d'identité manuelle » dans la rubrique Replicating Identity Columns.
Utilisez la procédure suivante pour ajouter un ou plusieurs nœuds à une topologie composée de nœuds qui exécutent SQL Server 2008 ou des versions ultérieures de SQL Server.
Pour ajouter un nœud qui exécute SQL Server 2008 ou une version ultérieure à la topologie
Configurez le Nœud D comme serveur de publication et associez-le à un serveur de distribution local ou distant.
Restaurez une sauvegarde du Nœud A sur le Nœud D.
Au niveau du Nœud D, exécutez sp_addpublication. Spécifiez le nom de la publication pour @publication, affectez la valeur true à @enabled_for_p2p, la valeur active à @status et la valeur true à @allow_initialize_from_backup. Pour ajouter des articles à la publication, exécutez sp_addarticle.
Au niveau du Nœud D, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud A pour @subscriber, le nom de la base de données cible sur le Nœud A pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud A, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud D pour @subscriber, le nom de la base de données cible sur le Nœud D pour @destination_db, affectez la valeur initialize with backup pour @sync_type et affectez les valeurs appropriées pour les paramètres @backupdevicetype et @backupdevicename.
Le Nœud D a potentiellement reçu des transactions du Nœud B et du Nœud C par le biais du Nœud A. Ces transactions sont analysées dans l'étape suivante.
Au niveau du Nœud D, interrogez la table MSpeer_lsns. Utilisez les colonnes originator et originator_lsn pour déterminer le NSE (numéro séquentiel dans le journal) de la transaction la plus récente que le Nœud D a reçu du Nœud B.
Au niveau du Nœud D, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud B pour @subscriber, le nom de la base de données cible sur le Nœud B pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud B, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud D pour @subscriber, le nom de la base de données cible sur le Nœud D pour @destination_db, affectez la valeur initialize from lsn à @sync_type et affectez le NSE récupéré pour le Nœud B à @subscriptionlsn.
Au niveau du Nœud D, interrogez la table MSpeer_lsns. Utilisez les colonnes originator et originator_lsn pour déterminer le NSE de la transaction la plus récente que le Nœud D a reçu du Nœud C.
Au niveau du Nœud D, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud C pour @subscriber, le nom de la base de données cible sur le Nœud C pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud C, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud D pour @subscriber, le nom de la base de données cible sur le Nœud D pour @destination_db, affectez la valeur initialize from lsn à @sync_type et affectez le NSE récupéré pour le Nœud C à @subscriptionlsn.
Si les tables publiées contiennent des colonnes d'identité, après l'opération de restauration, la plage d'identité que vous avez affectée aux tables du Nœud A sera également utilisée dans les tables du Nœud D. Vous devez utiliser DBCC CHECKIDENT pour réattribuer les tables du Nœud D et ainsi garantir qu'une plage différente est utilisée pour chacun.
Pour plus d'informations sur la gestion des plages d'identité, consultez la section « Attribution de plages pour la gestion manuelle des plages d'identité » dans la rubrique Réplication de colonnes d'identité.
Comme mentionné dans l'introduction de cette rubrique, la différence principale entre l'ajout d'un nœud SQL Server 2005 et l'ajout d'un nœud qui exécute une version ultérieure de SQL Server est que SQL Server 2005 exige que vous suspendiez le système pour connecter le nouveau nœud à tous les nœuds existants. La procédure qui suit montre comment ajouter un nœud SQL Server 2005 à la topologie existante en plusieurs phases :
La première phase regroupe les étapes 1 à 5. Dans cette phase, le Nœud D est partiellement connecté à la topologie en créant des abonnements entre le Nœud A et le Nœud D. Des modifications peuvent ainsi continuer à être apportées sur les Nœuds A, B et C. Des modifications peuvent commencer à être apportées au niveau du Nœud D dès que des abonnements ont été créés entre le Nœud A et le Nœud D. Les modifications des Nœuds B et C sont répliquées sur le Nœud D par le biais du Nœud A.
La seconde phase regroupe les étapes 6 à 9. Dans cette phase, le Nœud D est entièrement connecté à la topologie en créant des abonnements entre le Nœud B et le Nœud D, et entre le Nœud C et le Nœud D. Pour cette phase, vous devez suspendre le système.
Cette phase n'est pas obligatoire, mais elle offre une meilleure tolérance de panne qu'une seule connexion entre le Nœud A et le Nœud D.
Pour ajouter un nœud SQL Server 2005 à la topologie
Configurez le Nœud D comme serveur de publication et associez-le avec un serveur de distribution local ou distant.
Restaurez une sauvegarde du Nœud A sur le Nœud D.
Au niveau du Nœud D, exécutez sp_addpublication. Spécifiez le nom de la publication pour @publication, affectez la valeur true à @enabled_for_p2p, la valeur active à @status et la valeur true à @allow_initialize_from_backup. Pour ajouter des articles à la publication, exécutez sp_addarticle.
Au niveau du Nœud D, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud A pour @subscriber, le nom de la base de données cible sur le Nœud A pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud A, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud D pour @subscriber, le nom de la base de données cible sur le Nœud D pour @destination_db, affectez la valeur initialize with backup à @sync_type et affectez les valeurs appropriées aux paramètres @backupdevicetype et @backupdevicename.
Suspendez la topologie en procédant comme suit :
Arrêtez toute activité sur toutes les tables publiées dans la topologie d'égal à égal.
Exécutez sp_requestpeerresponse sur la base de données du serveur A, du serveur B, du serveur C et du serveur D, puis récupérez le paramètre de sortie @request_id.
Par défaut, l'Agent de distribution est configuré pour s'exécuter en continu ; par conséquent, les jetons doivent être envoyés automatiquement à tous les nœuds. Si l'Agent de distribution ne s'exécute pas en mode continu, exécutez l'agent. Pour plus d'informations, consultez Concepts des exécutables de l'agent de réplication ou Procédure : démarrer et arrêter un Agent de réplication (SQL Server Management Studio).
Exécutez sp_helppeerresponses, en fournissant la valeur de @request_id récupérée à l'étape 2. Attendez que tous les nœuds indiquent qu'ils ont reçu la demande de l'homologue.
Affectez de nouvelles plages d'identité au niveau du Nœud D si besoin. Vous pouvez désormais connecter complètement la topologie en ajoutant le reste des abonnements.
Au niveau du Nœud D, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud B pour @subscriber, le nom de la base de données cible sur le Nœud B pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud D, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud C pour @subscriber, le nom de la base de données cible sur le Nœud C pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud B, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud D pour @subscriber, le nom de la base de données cible sur le Nœud D pour @destination_db et affectez la valeur replication support only à @sync_type.
Au niveau du Nœud C, exécutez sp_addsubscription. Spécifiez le nom de la publication pour @publication, le nom du Nœud D pour @subscriber, le nom de la base de données cible sur le Nœud D pour @destination_db et affectez la valeur replication support only à @sync_type.