Créer un abonnement pouvant être mis à jour pour une publication transactionnelle

S'applique à : SQL Server

La réplication transactionnelle permet de propager sur le serveur de publication les modifications apportées au niveau d'un Abonné en utilisant des abonnements avec mise à jour immédiate ou mise à jour en file d'attente. Vous pouvez créer par programme un abonnement avec mise à jour en utilisant des procédures stockées de réplication.

Configurez des abonnements pouvant être mis à jour dans la page Abonnements pouvant être mis à jour de l’Assistant Nouvel abonnement. Cette page est disponible seulement si vous avez activé une publication transactionnelle pour les abonnements pouvant être mis à jour. Pour plus d’informations sur l’activation des abonnements pouvant être mis à jour, consultez Activer la mise à jour d’abonnements pour les publications transactionnelles.

Configurer un abonnement pouvant être mis à jour à partir du serveur de publication

1. Connectez-vous au serveur de publication dans Microsoft SQL Server Management Studio, puis développez le nœud du serveur.

2. Développez le dossier Réplication , puis développez le dossier Publications locales .

3. Cliquez avec le bouton droit sur une publication transactionnelle activée pour la mise à jour d’abonnements, puis cliquez sur Nouveaux abonnements.

4. Suivez les pages de l'Assistant pour spécifier les options de l'abonnement, par exemple où l'Agent de distribution doit s'exécuter.

5. Dans la page Abonnements pouvant être mis à jour de l’Assistant Nouvel abonnement, vérifiez que Répliquer est sélectionné.

6. Sélectionnez une option dans la liste déroulante Valider sur le serveur de publication :

   • Pour utiliser des abonnements mis à jour immédiatement, sélectionnez Enregistrer les modifications simultanément. Si vous sélectionnez cette option et que la publication autorise les abonnements mis à jour en attente (l’option par défaut pour les publications créées avec l’Assistant Nouvelle publication), la propriété d’abonnement update_mode a la valeur failover. Ce mode vous permet de passer ultérieurement en mise à jour en attente si nécessaire.

   • Pour utiliser des abonnements mis à jour en attente, sélectionnez Mettre les modifications en file d’attente et valider dès que possible. Si vous sélectionnez cette option et que la publication autorise les abonnements mis à jour immédiatement (l’option par défaut pour les publications créées avec l’Assistant Nouvelle publication), et si l’abonné exécute SQL Server 2005 ou une version ultérieure, la propriété d’abonnement update_mode a la valeur queued failover. Ce mode vous permet de passer ultérieurement en mise à jour immédiate si nécessaire.

   Pour plus d’informations sur le basculement entre les modes de mise à jour, consultez Basculer entre les modes de mise à jour d’un abonnement transactionnel pouvant être mis à jour.

7. La page Nom d’accès aux abonnements pouvant être mis à jour est affichée pour les abonnements qui utilisent la mise à jour immédiate ou dont la propriété update_mode a la valeur queued failover. Dans la page Nom d’accès aux abonnements pouvant être mis à jour, spécifiez un serveur lié via lequel sont effectuées les connexions au serveur de publication pour les abonnements mis à jour immédiatement. Les connexions sont utilisées par les déclencheurs qui s'exécutent pour l'abonné et propagent les modifications sur le serveur de publication. Sélectionnez l’une des options suivantes :

   • Créer un serveur lié qui se connecte par Authentification SQL Server. Sélectionnez cette option si vous n'avez pas défini un serveur distant ou un serveur lié entre l'Abonné et le serveur de publication. La réplication crée un serveur lié pour vous. Le compte que vous spécifiez doit déjà exister sur le serveur de publication.

   • Utiliser un serveur lié ou un serveur distant que vous avez déjà défini. Sélectionnez cette option si vous avez défini un serveur distant ou un serveur lié entre l’abonné et le serveur de publication à l’aide de sp_addserver (Transact-SQL), sp_addlinkedserver (Transact-SQL), SQL Server Management Studio ou d’une autre méthode.

   Pour plus d’informations sur les autorisations requises par le compte du serveur lié, consultez la section Abonnements mis à jour en attente de saisissez la description de lien ici.

8. Suivez les instructions de l’Assistant.

Configurer un abonnement pouvant être mis à jour à partir de l’Abonné

1. Connectez-vous à l’abonné dans SQL Server Management Studio, puis développez le nœud du serveur.

2. Développez le dossier Réplication .

3. Cliquez avec le bouton droit sur le dossier Abonnements locaux , puis cliquez sur Nouveaux abonnements.

4. Dans la page Publication de l’Assistant Nouvel abonnement, sélectionnez Rechercher un serveur de publication SQL Server dans la liste déroulante Serveur de publication.

5. Connectez-vous au serveur de publication dans la boîte de dialogue Se connecter au serveur .

6. Sélectionnez une publication transactionnelle activée pour la mise à jour d’abonnements dans la page Publication.

7. Suivez les pages de l'Assistant pour spécifier les options de l'abonnement, par exemple où l'Agent de distribution doit s'exécuter.

8. Dans la page Abonnements pouvant être mis à jour de l’Assistant Nouvel abonnement, vérifiez que Répliquer est sélectionné.

9. Sélectionnez une option dans la liste déroulante Valider sur le serveur de publication :

   • Pour utiliser des abonnements mis à jour immédiatement, sélectionnez Enregistrer les modifications simultanément. Si vous sélectionnez cette option et que la publication autorise les abonnements mis à jour en attente (l’option par défaut pour les publications créées avec l’Assistant Nouvelle publication), la propriété d’abonnement update_mode a la valeur failover. Ce mode vous permet de passer ultérieurement en mise à jour en attente si nécessaire.

   • Pour utiliser des abonnements mis à jour en attente, sélectionnez Mettre les modifications en file d’attente et valider dès que possible. Si vous sélectionnez cette option et que la publication autorise les abonnements mis à jour immédiatement (l’option par défaut pour les publications créées avec l’Assistant Nouvelle publication), et si l’abonné exécute SQL Server 2005 ou une version ultérieure, la propriété d’abonnement update_mode a la valeur queued failover. Ce mode vous permet de passer ultérieurement en mise à jour immédiate si nécessaire.

   Pour plus d’informations sur le basculement entre les modes de mise à jour, consultez Basculer entre les modes de mise à jour d’un abonnement transactionnel pouvant être mis à jour.

10. La page Nom d’accès aux abonnements pouvant être mis à jour est affichée pour les abonnements qui utilisent la mise à jour immédiate ou dont la propriété update_mode a la valeur queued failover. Dans la page Nom d’accès aux abonnements pouvant être mis à jour, spécifiez un serveur lié via lequel sont effectuées les connexions au serveur de publication pour les abonnements mis à jour immédiatement. Les connexions sont utilisées par les déclencheurs qui s'exécutent pour l'abonné et propagent les modifications sur le serveur de publication. Sélectionnez l’une des options suivantes :

    • Créer un serveur lié qui se connecte par Authentification SQL Server. Sélectionnez cette option si vous n'avez pas défini un serveur distant ou un serveur lié entre l'Abonné et le serveur de publication. La réplication crée un serveur lié pour vous. Le compte que vous spécifiez doit déjà exister sur le serveur de publication.

    • Utiliser un serveur lié ou un serveur distant que vous avez déjà défini. Sélectionnez cette option si vous avez défini un serveur distant ou un serveur lié entre l’abonné et le serveur de publication à l’aide de sp_addserver (Transact-SQL), sp_addlinkedserver (Transact-SQL), SQL Server Management Studio ou d’une autre méthode.

    Pour plus d’informations sur les autorisations requises par le compte du serveur lié, consultez la section Abonnements mis à jour en attente de saisissez la description de lien ici.

11. Suivez les instructions de l’Assistant.

Créer un abonnement par extraction avec mise à jour immédiate

1. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements avec mise à jour immédiate en exécutant sp_helppublication.

   • Si la valeur de allow_sync_tran dans le jeu de résultats est 1, la publication prend en charge les abonnements avec mise à jour immédiate.
   • Si la valeur de allow_sync_tran dans le jeu de résultats est 0, la publication doit être recréée en activant la prise en charge des abonnements avec mise à jour immédiate.

2. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements par extraction en exécutant sp_helppublication.

   • Si la valeur de allow_pull dans le jeu de résultats est 1, la publication prend en charge les abonnements par extraction.
   • Si la valeur de allow_pull est 0, exécutez sp_changepublication, en spécifiant allow_pull pour @property , et true pour @value.

3. Sur l'Abonné, exécutez sp_addpullsubscription. Spécifiez @publisher et @publication, et l’une des valeurs suivantes pour @update_mode:

   • sync tran - active l’abonnement pour la mise à jour immédiate.
   • failover - active l’abonnement pour la mise à jour immédiate avec mise à jour en file d’attente sous forme d’option de basculement.

   Remarque

   failover requiert que la publication soit également activée pour les abonnements avec mise à jour en file d’attente.

4. Sur l'Abonné, exécutez sp_addpullsubscription_agent. Spécifiez les détails suivants :

   • Les paramètres @publisher, de @publisher_dbet @publication .
   • Les informations d’identification Microsoft Windows sous lesquelles l’Agent de distribution est exécuté sur l’abonné pour @job_login et @job_password.

   Remarque

   Les connexions établies à l’aide de l’authentification intégrée Windows utilisent toujours les informations d’identification Windows spécifiées par @job_login et @job_password. L'Agent de distribution établit toujours la connexion locale à l'Abonné à l'aide de l'authentification intégrée Windows. Par défaut, l'agent se connecte au serveur de distribution à l'aide de l'authentification intégrée Windows.

   • (Facultatif) Une valeur de 0 pour @distributor_security_mode et les informations de connexion Microsoft SQL Server pour @distributor_login et @distributor_password, si vous devez utiliser l’authentification SQL Server lors de la connexion du distributeur.
   • Planification du travail de l'Agent de distribution pour cet abonnement.

5. Dans la base de données d’abonnement de l’Abonné, exécutez sp_link_publication. Spécifiez @publisher, de @publication, le nom de la base de données de publication pour @publisher_dbet l'une des valeurs suivantes pour @security_mode:

   • 0 - Utiliser l’authentification SQL Server pour effectuer des mises à jour sur le serveur de publication. Avec cette option, vous devez spécifier une connexion valide sur le serveur de publication pour @login et @password.
   • 1 - Utiliser le contexte de sécurité de l’utilisateur qui apporte des modifications à l’Abonné lors de la connexion au serveur de publication. Consultez sp_link_publication pour connaître les restrictions en rapport avec ce mode de sécurité.
   • 2 - Utiliser une connexion de serveur lié existante, définie par l’utilisateur, créée à l’aide de sp_addlinkedserver.

6. Sur le serveur de publication, exécutez sp_addsubscription en spécifiant @publication, @subscriber, @destination_db, une valeur d’extraction pour @subscription_type, et la valeur spécifiée à l’étape 3 pour @update_mode. L'abonnement par extraction est alors inscrit sur le serveur de publication.

Créer un abonnement par émission de données avec mise à jour immédiate

1. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements avec mise à jour immédiate en exécutant sp_helppublication.

   • Si la valeur de allow_sync_tran dans le jeu de résultats est 1, la publication prend en charge les abonnements avec mise à jour immédiate.
   • Si la valeur de allow_sync_tran dans le jeu de résultats est 0, la publication doit être recréée en activant la prise en charge des abonnements avec mise à jour immédiate.

2. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements par émission de données en exécutant sp_helppublication.

   • Si la valeur de allow_push dans le jeu de résultats est 1, la publication prend en charge les abonnements par émission de données.
   • Si la valeur de allow_push est 0, exécutez sp_changepublication, en spécifiant allow_push pour @property , et true pour @value.

3. Sur le serveur de publication, exécutez sp_addsubscription. Spécifiez @publication, de @subscriber, de @destination_dbet affectez l'une des valeurs suivantes à @update_mode:

   • sync tran - active la prise en charge de la mise à jour immédiate.
   • failover - active la prise en charge de la mise à jour immédiate avec mise à jour en file d’attente comme option de basculement.

   Remarque

   failover requiert que la publication soit également activée pour les abonnements avec mise à jour en file d’attente.

4. Sur le serveur de publication, exécutez sp_addpushsubscription_agent. Spécifiez les paramètres suivants :

   • @subscriber, @subscriber_dbet @publication.

   • Informations d’identification Windows sous lesquelles l’Agent de distribution s’exécute sur le serveur de distribution pour @job_login et @job_password.

   Remarque

   Les connexions établies à l’aide de l’authentification intégrée Windows utilisent toujours les informations d’identification Windows spécifiées par @job_login et @job_password. L'Agent de distribution crée toujours la connexion locale au serveur de distribution à l'aide de l'authentification intégrée Windows. Par défaut, l'agent se connecte à l'Abonné à l'aide de ces informations.

   • (Facultatif) Une valeur de 0 pour @subscriber_security_mode et les informations de connexion SQL Server pour @subscriber_login et @subscriber_password, si vous devez utiliser l’authentification SQL Server lors de la connexion de l’abonné.
   • Planification du travail de l'Agent de distribution pour cet abonnement.

5. Dans la base de données d’abonnement de l’Abonné, exécutez sp_link_publication. Spécifiez @publisher, de @publication, le nom de la base de données de publication pour @publisher_dbet l'une des valeurs suivantes pour @security_mode:

   • 0 - Utiliser l’authentification SQL Server pour effectuer des mises à jour sur le serveur de publication. Avec cette option, vous devez spécifier une connexion valide sur le serveur de publication pour @login et @password.
   • 1 - Utiliser le contexte de sécurité de l’utilisateur qui apporte des modifications à l’Abonné lors de la connexion au serveur de publication. Consultez sp_link_publication pour connaître les restrictions en rapport avec ce mode de sécurité.
   • 2 - Utiliser une connexion de serveur lié existante, définie par l’utilisateur, créée à l’aide de sp_addlinkedserver.

Créer un abonnement par extraction avec mise à jour en file d’attente

1. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements avec mise à jour en file d’attente en exécutant sp_helppublication.

   • Si la valeur de allow_queued_tran dans le jeu de résultats est 1, la publication prend en charge les abonnements avec mise à jour immédiate.
   • Si la valeur de allow_queued_tran dans le jeu de résultats est 0, la publication doit être recréée en activant la prise en charge des abonnements avec mise à jour en file d’attente activée.

2. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements par extraction en exécutant sp_helppublication.

   • Si la valeur de allow_pull dans le jeu de résultats est 1, la publication prend en charge les abonnements par extraction.
   • Si la valeur de allow_pull est 0, exécutez sp_changepublication, en spécifiant allow_pull pour @property , et true pour @value.

3. Sur l'Abonné, exécutez sp_addpullsubscription. Spécifiez @publisher et @publication, et l’une des valeurs suivantes pour @update_mode:

   • queued tran - active l’abonnement pour la mise à jour en attente.
   • queued failover - active la prise en charge de la mise à jour en file d’attente avec mise à jour immédiate comme option de basculement.

   Remarque

   queued failover requiert que la publication soit également activée pour les abonnements avec mise à jour immédiate. Pour basculer sur la mise à jour immédiate, vous devez utiliser sp_link_publication pour définir les informations d’identification sous lesquelles les modifications au niveau de l’Abonné sont répliquées sur le serveur de publication.

4. Sur l'Abonné, exécutez sp_addpullsubscription_agent. Spécifiez les paramètres suivants :

   • @publisher, @publisher_dbet @publication.
   • Les informations d’identification Windows sous lesquelles l’Agent de distribution est exécuté sur l’abonné pour @job_login et @job_password.

   Remarque

   Les connexions établies à l’aide de l’authentification intégrée Windows utilisent toujours les informations d’identification Windows spécifiées par @job_login et @job_password. L'Agent de distribution établit toujours la connexion locale à l'Abonné à l'aide de l'authentification intégrée Windows. Par défaut, l'agent se connecte au serveur de distribution à l'aide de l'authentification intégrée Windows.

   • (Facultatif) Une valeur de 0 pour @distributor_security_mode et les informations de connexion SQL Server pour @distributor_login et @distributor_password, si vous devez utiliser l’authentification SQL Server lors de la connexion du distributeur.
   • Planification du travail de l'Agent de distribution pour cet abonnement.

5. Sur le serveur de publication, exécutez sp_addsubscriber pour enregistrer l’abonné sur le serveur de publication en spécifiant @publication, @subscriber, @destination_db, une valeur d’extraction pour @subscription_type, et la valeur spécifiée à l’étape 3 pour @update_mode. L'abonnement par extraction est alors inscrit sur le serveur de publication.

Créer un abonnement par émission de données avec mise à jour en file d’attente

1. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements avec mise à jour en file d’attente en exécutant sp_helppublication.

   • Si la valeur de allow_queued_tran dans le jeu de résultats est 1, la publication prend en charge les abonnements avec mise à jour immédiate.
   • Si la valeur de allow_queued_tran dans le jeu de résultats est 0, la publication doit être recréée en activant la prise en charge des abonnements avec mise à jour en file d’attente. Pour plus d’informations, voir Procédure : activer les abonnements avec mise à jour pour les publications transactionnelles (programmation Transact-SQL de la réplication).

2. Sur le serveur de publication, vérifiez que la publication prend en charge les abonnements par émission de données en exécutant sp_helppublication.

   • Si la valeur de allow_push dans le jeu de résultats est 1, la publication prend en charge les abonnements par émission de données.
   • Si la valeur de allow_push est 0, exécutez sp_changepublication, en spécifiant allow_push pour @property , et true pour @value.

3. Sur le serveur de publication, exécutez sp_addsubscription. Spécifiez @publication, de @subscriber, de @destination_dbet affectez l'une des valeurs suivantes à @update_mode:

   • queued tran - active l’abonnement pour la mise à jour en attente.
   • queued failover - active la prise en charge de la mise à jour en file d’attente avec mise à jour immédiate comme option de basculement.

   Remarque

   L’option queued failover requiert que la publication soit également activée pour les abonnements avec mise à jour immédiate. Pour basculer sur la mise à jour immédiate, vous devez utiliser sp_link_publication pour définir les informations d’identification sous lesquelles les modifications au niveau de l’Abonné sont répliquées sur le serveur de publication.

4. Sur le serveur de publication, exécutez sp_addpushsubscription_agent. Spécifiez les paramètres suivants :

   • @subscriber, @subscriber_dbet @publication.
   • Informations d’identification Windows sous lesquelles l’Agent de distribution s’exécute sur le serveur de distribution pour @job_login et @job_password.

   Remarque

   Les connexions établies à l’aide de l’authentification intégrée Windows utilisent toujours les informations d’identification Windows spécifiées par @job_login et @job_password. L'Agent de distribution crée toujours la connexion locale au serveur de distribution à l'aide de l'authentification intégrée Windows. Par défaut, l’agent se connecte à l’Abonné à l’aide de ces informations.

   • (Facultatif) Une valeur de 0 pour @subscriber_security_mode et les informations de connexion SQL Server pour @subscriber_login et @subscriber_password, si vous devez utiliser l’authentification SQL Server lors de la connexion de l’abonné.
   • Planification du travail de l'Agent de distribution pour cet abonnement.

Définir les options de résolution des conflits de mise à jour en attente

Définissez les options de résolution des conflits pour les publications qui prennent en charge les abonnements avec mise à jour en attente, sur la page Options d'abonnement de la boîte de dialogue Propriétés de la publication – <Publication>. Pour plus d'informations sur l'accès à cette boîte de dialogue, consultez Afficher et modifier les propriétés d’un serveur de publication.

1. Sur la page Options d'abonnement de la boîte de dialogue Propriétés de la publication – <Publication>, sélectionnez l'une des valeurs ci-après pour l'option Stratégie de résolution de conflit :

   • Conserver la modification apportée au serveur de publication
   • Conserver la modification apportée à l'abonné
   • Réinitialiser l'abonnement

Exemple

Cet exemple crée un abonnement par extraction avec mise à jour immédiate à une publication qui prend en charge les abonnements avec mise à jour immédiate. Les valeurs de connexion et le mot de passe sont fournis lors de l'exécution à l'aide des variables de script sqlcmd.

-- Execute this batch at the Subscriber.
DECLARE @publication AS sysname;
DECLARE @publicationDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS NVARCHAR(512);
SET @publication = N'AdvWorksProductTran';
SET @publicationDB = N'AdventureWorks2022';
SET @publisher = $(PubServer);
SET @login = $(Login);
SET @password = $(Password);

-- At the subscription database, create a pull subscription to a transactional 
-- publication using immediate updating with queued updating as a failover.
EXEC sp_addpullsubscription
    @publisher = @publisher,
    @publication = @publication,
    @publisher_db = @publicationDB,
    @update_mode = N'failover',
    @subscription_type = N'pull';

-- Add an agent job to synchronize the pull subscription, 
-- which uses Windows Authentication when connecting to the Distributor.
EXEC sp_addpullsubscription_agent
    @publisher = @publisher,
    @publisher_db = @publicationDB,
    @publication = @publication,
    @job_login = @login,
    @job_password = @password;

-- Add a Windows Authentication-based linked server that enables the 
-- Subscriber-side triggers to make updates at the Publisher. 
EXEC sp_link_publication
    @publisher = @publisher,
    @publication = @publication,
    @publisher_db = @publicationDB,
    @security_mode = 0,
    @login = @login,
    @password = @password;
GO

USE AdventureWorks2022;
GO

-- Execute this batch at the Publisher.
DECLARE @publication AS sysname;
DECLARE @subscriptionDB AS sysname;
DECLARE @subscriber AS sysname;
SET @publication = N'AdvWorksProductTran';
SET @subscriptionDB = N'AdventureWorks2022Replica';
SET @subscriber = $(SubServer);

-- At the Publisher, register the subscription, using the defaults.
USE AdventureWorks2022;
EXEC sp_addsubscription
    @publication = @publication,
    @subscriber = @subscriber,
    @destination_db = @subscriptionDB,
    @subscription_type = N'pull',
    @update_mode = N'failover';
GO

Contenu connexe

• Updatable Subscriptions for Transactional Replication
• Créer une publication
• Utiliser sqlcmd avec des variables de script