Share via

An interesting issue with Peer to Peer Replication

  • Article

I recently ran into an interesting issue when setting up Peer 2 Peer Replication across 3 instances.

The primary instance was SM-UTSQL, where we configured a Peer-to-Peer publication named "PUBLISH1" on the database "DDS-TRANS". Next, we proceeded to configure Peer-to-Peer topology (right-click on publication, click on "Configure Peer-To-Peer topology"), and added the other 2 instances to the topology. After this, we clicked on the primary node and selected "Connect to all displayed nodes" :

 

image

We then went ahead through the UI and configured the replication. However, when we checked in object explorer, we saw that on the Primary instance (SM-UTSQL), under replication->Publish1, we could see both the Peer nodes SO-UTSQL and ST-UTSQL as subscribers, but on SO-UTSQL and ST-UTSQL, we could see on SM-UTSQL as the subscriber for the publication i.e. SO-UTSQL and ST-UTSQL did not recognize each other as subscribers.

We tried to add the missing subscriber through the new subscriptions wizard, but got the following error:

TITLE: New Subscription Wizard

------------------------------

SQL Server could not create a subscription for Subscriber 'ST-UTSQL'.

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Peer-to-peer publications only support a '@sync_type' parameter value of 'replication support only', 'initialize with backup' or 'initialize from lsn'.

The subscription could not be found.

Changed database context to 'DDS_TRANS'. (Microsoft SQL Server, Error: 21679)

For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000&EvtSrc=MSSQLServer&EvtID=21679&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

 

The resolution? Here are the steps:

  1. Navigate to the replication tab in object explorer on the Primary instance (where you can see both subscriptions under the publication, SM-UTSQL in our case).

  2. Right click on the publication and select generate scripts, and select the "To Create or enable the components" radio button.

  3. In the resulting script, navigate to the bottom. Here, you will see 2 sets of "sp_addsubscription" and "sp_addpushsubscription_agent" calls:

    -- Adding the transactional subscriptions

    use [DDS_TRANS]

    exec sp_addsubscription @publication = N'PUBLISH1', @subscriber = N'SO-UTSQL', @destination_db = N'DDS_TRANS', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

    exec sp_addpushsubscription_agent @publication = N'PUBLISH1', @subscriber = N'SO-UTSQL', @subscriber_db = N'DDS_TRANS', @job_login = N'dds\dtsql.admin', @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'

    GO

     

    use [DDS_TRANS]

    exec sp_addsubscription @publication = N'PUBLISH1', @subscriber = N'ST-UTSQL', @destination_db = N'DDS_TRANS', @subscription_type = N'Push', @sync_type = N'replication support only', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

    exec sp_addpushsubscription_agent @publication = N'PUBLISH1', @subscriber = N'ST-UTSQL', @subscriber_db = N'DDS_TRANS', @job_login = N'dds\dtsql.admin', @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'

    GO

  4. Copy these commands over, provide the value for the @job_password parameter (password for the login used to configure replication, reflected in the @job_login parameter), and run the appropriate set on the 2 subscribers. For example, we ran the first set of commands (@subscriber=N'SO-UTSQL') on ST-UTSQL instance, and the second set(@subscriber=N'ST-UTSQL') on the SO-UTSQL instance.

And voila, the subscriptions were created and syncing.

 

Hope this helped you. Comments and feedback are welcome.

Comments

  • Anonymous
    September 30, 2013
    nice post Harsh .. thanks a lot for sharing

  • Anonymous
    September 30, 2013
    Thanks a lot for appreciating Gurpreet ...!!!