Skip to content

Latest commit

 

History

History
107 lines (68 loc) · 9.9 KB

enable-updating-subscriptions-for-transactional-publications.md

File metadata and controls

107 lines (68 loc) · 9.9 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom helpviewer_keywords
Enable Updatable Subscriptions for Transactional Publications
Learn how to enable Updatable Subscriptions for a Transactional Publication in SQL Server.
MashaMSFT
mathoma
09/25/2024
sql
replication
how-to
updatefrequency5
transactional replication, updatable subscriptions
updatable subscriptions, enabling
subscriptions [SQL Server replication], updatable

Enable Updating Subscriptions for Transactional Publications

[!INCLUDE SQL Server] This topic describes how to enable updating subscriptions for transactional publications in [!INCLUDEssnoversion] by using [!INCLUDEssManStudioFull] or [!INCLUDEtsql].

Note

[!INCLUDEssNoteDepFutureAvoid]

Before You Begin

Security

When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

Using SQL Server Management Studio

Enable updating subscriptions for transactional publications on the Publication Type page of the New Publication Wizard.

To use updating subscriptions, you must also configure options in the New Subscription Wizard.

To enable updating subscriptions

  1. On the Publication Type page of the New Publication Wizard, select Transactional publication with updatable subscriptions.

  2. On the Agent Security page, specify security settings for the Queue Reader Agent in addition to the Snapshot Agent and Log Reader Agent. For more information about the permissions required for the account under which the Queue Reader Agent runs, see Replication Agent Security Model.

    [!NOTE]
    The Queue Reader Agent is configured even if you use only immediate updating subscriptions.

Using Transact-SQL

When creating a transactional publication programmatically using replication stored procedures, you can enable either immediate or queued updating subscriptions.

To create a publication that supports immediate updating subscriptions

  1. If necessary, create a Log Reader Agent job for the publication database.

    • If a Log Reader Agent job already exists for the publication database, proceed to step 2.

    • If you are unsure whether a Log Reader Agent job exists for a published database, execute sp_helplogreader_agent (Transact-SQL) at the Publisher on the publication database. If the result set is empty, a Log Reader Agent job must be created.

    • At the publisher, execute sp_addlogreader_agent (Transact-SQL). Specify the [!INCLUDEmsCoName] Windows credentials under which the agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the [!INCLUDEmsCoName] [!INCLUDEssNoVersion] login information for @publisher_login and @publisher_password.

  2. Execute sp_addpublication (Transact-SQL), specifying a value of true for the parameter @allow_sync_tran.

  3. At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 2 for @publication and the Windows credentials under which the Snapshot Agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the [!INCLUDEssNoVersion] login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

  4. Add articles to the publication. For more information, see Define an Article.

  5. At the Subscriber, create an updating subscription to this publication.

To create a publication that supports queued updating subscriptions

  1. If necessary, create a Log Reader Agent job for the publication database.

    • If a Log Reader Agent job already exists for the publication database, proceed to step 2.

    • If you are unsure whether a Log Reader Agent job exists for a published database, execute sp_helplogreader_agent (Transact-SQL) at the Publisher on the publication database. If the result set is empty, then a Log Reader Agent job must be created.

    • At the publisher, execute sp_addlogreader_agent (Transact-SQL). Specify the Windows credentials under which the agent runs for @job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the [!INCLUDEssNoVersion] login information for @publisher_login and @publisher_password.

  2. If necessary, create a Queue Reader Agent job for the Distributor.

    • If a Queue Reader Agent job already exists for the distribution database, proceed to step 3.

    • If you are unsure whether a Queue Reader Agent job exists for the distribution database, execute sp_helpqreader_agent (Transact-SQL) at the Distributor on the distribution database. If the result set is empty, then a Queue Reader Agent job must be created.

    • At the Distributor, execute sp_addqreader_agent (Transact-SQL). Specify the Windows credentials under which the agent runs for @job_name and @password. These credentials are used when the Queue Reader Agent connects to the Publisher and Subscriber. For more information, see Replication Agent Security Model.

  3. Execute sp_addpublication (Transact-SQL), specifying a value of true for the parameter @allow_queued_tran and a value of pub wins, sub reinit, or sub wins for @conflict_policy.

  4. At the Publisher, execute sp_addpublication_snapshot (Transact-SQL). Specify the publication name used in step 3 for @publication and the Windows credentials under which the Snapshot Agent runs for @snapshot_job_name and @password. If the agent will use SQL Server Authentication when connecting to the Publisher, you must also specify a value of 0 for @publisher_security_mode and the [!INCLUDEssNoVersion] login information for @publisher_login and @publisher_password. This creates a Snapshot Agent job for the publication.

  5. Add articles to the publication. For more information, see Define an Article.

  6. At the Subscriber, create an updating subscription to this publication.

To change the conflict policy for a publication that allows queued updating subscriptions

  1. At the Publisher on the publication database, execute sp_changepublication (Transact-SQL). Specify a value of conflict_policy for @property and the desired conflict policy mode of pub wins, sub reinit, or sub wins for @value.

Example (Transact-SQL)

This example creates a publication that supported both immediate and queued updating pull subscriptions.

:::code language="sql" source="../codesnippet/tsql/enable-updating-subscrip_1.sql":::

Related content