none
Pull transactional replication not picking up new articles

    Question

  • Hello replication experts,

    I have transactional replication set up in my enterprise SQL 2014 server. Publication has IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties set to FALSE, sync_method= concurrent. 

    Environment details:

    1) Publisher: SQL Server 2014 EE SP2

    2) Distributor: SQL Server 2014 EE SP2

    3) Subscriber: SQL Server 2014 EE SP2

    4) Subscription type: Pull

    I added an article using the script below:

    use [Test_Pub]
    GO
    exec sp_addarticle 
    @publication = N'PubRPT', 
    @article = N'artTest',
    @source_owner = N'dbo', 
    @source_object = N'artTest', 
    @type = N'logbased',
    @description = N'', 
    @creation_script = N'', 
    @pre_creation_cmd = N'drop', 
    @schema_option = 0x00000000080010A7, 
    @identityrangemanagementoption = N'manual',
    @destination_table = N'artTest', 
    @destination_owner = N'dbo', 
    @status = 24,
    @vertical_partition = N'false',
    @ins_cmd = N'CALL [dbo].[sp_MSins_dboartTest]', 
    @del_cmd = N'CALL [dbo].[sp_MSdel_dboartTest]', 
    @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboartTest]'
    GO
    EXECUTE sp_refreshsubscriptions @publication = N'PubRPT'
    GO
    EXECUTE sp_startpublication_snapshot @publication = N'PubRPT'
    GO

    I have used this same script several times in the past with no issues. Objects created in the subscriber and snapshot was only run for new articles. This time, even though snapshot ran with no errors for this new article, it did not create the table or objects at the subscriber. There were no errors logged in distribution agent job and was running as usual delivering transactions for rest of the articles. When snapshot job ran it did create files on snapshot location for the newly created article. 

    I am clueless at this point as to why this is not working and the same thing worked in the past without any issues. Please provide your suggestions/insights to fix this issue. 

    Monday, October 8, 2018 7:07 PM

All replies

  • Hi sqlproddba,

    According to your description, it looks like that you are going to add new articles to existing Transactional Replication without initializing old articles. This is not a suggested method, generally, we will need to initialize all articles after adding some articles as mentioned in https://docs.microsoft.com/en-us/sql/relational-databases/replication/synchronize-a-push-subscription?view=sql-server-2017

    In your scenario, have you run sp_helpsubscription on your publisher database to verify the 'subscription status' of the subscribed article? Here is an article which using the SSMS to initialize it, please refer to it: https://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, October 9, 2018 7:53 AM
    Moderator
  • Hello Teige,

    Yes, I am adding a new article to existing Transactional replication. Please note that my existing replication before adding a new article is running fine. So I dont think I need to initialize all articles. Snapshot is only required for newly added article which I am doing by making IMMEDIATE_SYNC and ALLOW_ANONYMOUS properties to FALSE. 

    sp_helpsubscription does show subscribed after adding the article to the publication. But still it is not working for some reason.

    Tuesday, October 9, 2018 4:36 PM
  • There should be no need to reinitialize the whole subscription. A new snapshot followed by the distribution agent job run should be all that would be needed to sync the newly added table. Three suggestions come to my mind. 

    1) After you run the script, go to the subscriber DB--> expand programmability-->Stored procedures--> check if the MSins, MSupd, MSdel stored procs are created for the table that's being added. 

    2) In the past when I did any new article addition via scripts, I would add "@force_invalidate_snapshot=1" to the sp_addarticle stored proc. You can give it a try but before you do, try #3 below.

    3) Use GUI to add the new table followed by running the snapshot (using replication monitor) and distribution agent. See if that makes any difference. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, October 9, 2018 10:50 PM
  • Thanks Mohsin! Point 1, replication stored procs and tables are not getting created on subscriber. Point 2, already tried @force_invalidate_snapshot=1 but it didn't work. Point 3, will have to check.

    Update on point 3:

    Added the article using GUI but it still didn't work. Also did one more test by creating a new publication and then using the script to add the article but this time it worked. Not sure why? Can someone please help me understand why it is behaving like this?

    • Edited by sqlproddba Thursday, October 11, 2018 8:12 PM added more info
    Wednesday, October 10, 2018 1:00 PM
  • Hi sqlproddba,

    Also did one more test by creating a new publication and then using the script to add the article but this time it worked. 

    This means that the snapshot has been generated successfully, the problem occurs during the process initializing the subscriber. Because this method is not a suggested method, as a result, we may meet problem when using it.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, October 19, 2018 6:58 AM
    Moderator
  • You may need to drop the article from the subscription using sp_dropsubscription, then drop it from that table - using sp_droparticle. Then add it back using sp_addarticle and sp_addsubscription.

    There are cases when you have multiple publications - some being deployed via initialize from backup where sp_refreshsubsriptions does not work.

    Friday, October 19, 2018 12:13 PM
    Moderator

  •  Also did one more test by creating a new publication and then using the script to add the article but this time it worked. Not sure why? Can someone please help me understand why it is behaving like this?

    I'd try Hilary's suggestion. Also, to add, I have seen replication behave strangely a few times where things wouldn't work the first time and a rerun (without changing anything) works. 

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Friday, October 19, 2018 1:46 PM
  • The method I used to add new article always worked in the past and I am using the same method since years now. Also there are lot of online blogposts which suggests the same method that I used to add a new article to existing replication. Not sure why in this case it is not working. 
    Tuesday, October 23, 2018 5:12 PM
  • I have already tried dropping and re-adding via script number of times but it didn't work. Also, did check that there are no publications deployed via initialize from backup. All of them were initialized via snapshot method only. The only difference I found was when I add the same article in completely new publication it works but not in existing publication. 
    Tuesday, October 23, 2018 5:18 PM
  • Did try that but no success. :(
    Tuesday, October 23, 2018 5:18 PM
  • Did try that but no success. :(

    Try this in case you haven't tried already.

    1) First, remove the article (if it's already added). 

    2) Run the following on the publisher. 

    use [Test_Pub]
    GO
    exec sp_addarticle 
    @publication = N'PubRPT', 
    @article = N'artTest',
    @source_owner = N'dbo', 
    @source_object = N'artTest', 
    @type = N'logbased',
    @description = N'', 
    @creation_script = N'', 
    @pre_creation_cmd = N'drop', 
    @schema_option = 0x00000000080010A7, 
    @identityrangemanagementoption = N'manual',
    @destination_table = N'artTest', 
    @destination_owner = N'dbo', 
    @status = 24,
    @vertical_partition = N'false',
    @ins_cmd = N'CALL [dbo].[sp_MSins_dboartTest]', 
    @del_cmd = N'CALL [dbo].[sp_MSdel_dboartTest]', 
    @upd_cmd = N'SCALL [dbo].[sp_MSupd_dboartTest]'
    GO

    3) Run the following on publisher. Replace the names per your environment.

    exec sp_addsubscription @publication = N'TEST', 
    @subscriber = N'<SubscriberServer>', 
    @destination_db = N'test', 
    @subscription_type = N'Pull', 
    @sync_type = N'automatic', 
    @article = N'all', 
    @update_mode = N'read only', 
    @subscriber_type = 0

    4) Run the snapshot agent. Make sure the snapshot agent (in the replication monitor) returns "A snapshot of 1 article was generated" upon completion. 

    5) Start the Distribution agent to allow synchronization. 

    Connect to the subscriber and see if the table was added and that the sp_MSIns, sp_MSUpd etc sprocs exist in the subscriber DB. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Tuesday, October 23, 2018 7:06 PM