none
Pull Subscription Expiring When Adding New Articles RRS feed

  • Question

  • I have a very curious and inconvenient problem happening on a production transactional replication stream which is critical to our department workflow.  This replication stream normally works very reliably and I don't have to touch it but recently I had to had 5 new articles to it and while doing so the subscription expired (twice).  Data comes across this stream of 116 tables very quickly, so although I was able to recreate the subscription, it was quite a bit of work to backfill the missing data in the tables.  Because the tables are large and numerous I have configured this subscription not to use the SnapShot Agent. Also, I double checked the properties on the publication and subscriptions are set to never expire.  

    I would like to know why my subscription is expiring when I add articles and what I can do to prevent it.

    Thx v much,

    Kathy

    -- Executed on the publisher

    exec sp_addsubscription @publication = N'ecms_to_dss', @subscriber = N'Sidewinder', @destination_db = N'sched_mem', @sync_type = N'replication support only', @subscription_type = N'pull', @update_mode = N'read only'

    -- Executed on the subscriber

    exec sp_addpullsubscription @publisher = N'DBREPV01', @publication = N'ecms_to_dss', @publisher_db = N'smdb', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0
    go

    exec sp_addpullsubscription_agent @publisher = N'DBREPV01', @publisher_db = N'smdb', @publication = N'ecms_to_dss', @distributor = N'DBREPV01', @distributor_security_mode = 0, @distributor_login = N'replicator', @distributor_password = N'???????', @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20110517, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
    GO

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

    Here are the relevant physical configuration details:

    Publisher and Distributor on same VM: Windows Server 2008R2 and SQL Server Standard 2008R2

    Subscriber VM:  Windows Server 2016 and SQL Server 2016 Enterprise (this is the only subscription to this publication).

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

    Here's what happened!

    Task:  Add 5 new articles (tables) to the existing replication stream.

    Wednesday:  As a test, I created the simplest table on the subscriber and backfilled it with data (only 3 rows), then enabled the article on the publisher and inserted a sample row into the table, watched it appear on the subscriber and then deleted it on the publisher and verified that it was deleted.  All was good or so I thought!

    Thursday: That night we patched the OS on the subscriber (which meant it was rebooted and SQL Server was restarted).Replication was running just fine after that.

    Friday:  I met with our SQL developer to add the other 4 articles (tables) as a paired learning exercise.  I wanted to do this several times with her so I just started with 1 table out of the 4. 

    1. I created the table on our side, then went to publisher to enable the article. 
    2. When I did that, the distributor agent on the subscriber reported that the subscription had expired and I had to recreate the subscription. 
    3. I verified once again that the replication stream was working (using tracer tokens from the replication monitor).  Then I gave up using the situation as a teaching lesson and went back to the publisher to enable the other 4 tables (after creating and backfilling them on the subscriber). 
    4. I went back to the subscriber and the distributor agent reported AGAIN that the subscription had expired and I had to recreate it all over again.  So this pull subscription expired twice in the time span of 30 minutes.

    Weekend:  We worked to backfill the data we lost due to recreating the expired subscriptions and everything looks like it's working just fine, but we don't want this to happen each time we add new articles to the subscription.  Any insight on this would be helpful!


    Tuesday, July 30, 2019 2:44 PM

Answers

  • There are rules about what breaks the publication and what does not. I do not recall these rules. 

    There are two settings to pay attention to on your replication stored procedures.

    @force_invalidate_snapshot
    @force_reinit_subscription

    If these are set to 0 (the default) the procedure call will fail if you will need to reinitialize or regenerate your snapshot. Setting these parameters to 1 will allow these stored procedures to 1.

    This may help you.

    • Marked as answer by KathyGibson Wednesday, July 31, 2019 2:20 AM
    Tuesday, July 30, 2019 8:53 PM
    Moderator

All replies

  • Can you check the immediate_sync is off and that you did a sp_refreshsubscriptions after adding the article to the publication?
    Tuesday, July 30, 2019 3:20 PM
    Moderator
  • immediate_sync = 0 and allow_anonymouse = 0

    No I didn't call sp_refreshsubscriptions but then I didn't do it on Wednesday either when I successfully added the first table.

    I assumed because I enabled the article using SSMS that the GUI would take care of that for me.  Once I added the article on Friday the subscription immediately expired on the subscriber.  It didn't seem like there was any time for me to take corrective action.

    Tuesday, July 30, 2019 8:18 PM
  • The wizard "expires" the subscription when you make changes to it using the wizard.

    You need to make these additions using script. Then you won't get these "expirations". Really what is happening is the the subscription is invalidated and the snapshot agent has to be run again. Possibly to push down a new full subscription.

    Tuesday, July 30, 2019 8:36 PM
    Moderator
  • thx very much for the insight.

    But do you have any thoughts about why adding the 1st article on Wednesday was successful?  It seems like sometimes the GUI is safe and sometimes it is problematic.

    You are saying the best solution is to just call sp_addarticle() on the publisher for each new table and then sp_refreshsubscriptions after all the tables are added.

    I've never done this from script so does this seem right?



    Tuesday, July 30, 2019 8:46 PM
  • There are rules about what breaks the publication and what does not. I do not recall these rules. 

    There are two settings to pay attention to on your replication stored procedures.

    @force_invalidate_snapshot
    @force_reinit_subscription

    If these are set to 0 (the default) the procedure call will fail if you will need to reinitialize or regenerate your snapshot. Setting these parameters to 1 will allow these stored procedures to 1.

    This may help you.

    • Marked as answer by KathyGibson Wednesday, July 31, 2019 2:20 AM
    Tuesday, July 30, 2019 8:53 PM
    Moderator