Trouble adding articles to an Publication/Subscription that are part of Availability Groups


  • I was adding a few articles (a delta of 8 tables and 2 views) to an existing publication that is part of an AG (so is subscriber).

    e.g. -- Oct 16, 2018 - new tables
    exec sp_addarticle @Publication = N'$(PublicationName)', @Filter_Clause = '$(FilterClause)', @article = N'InventoryPools',...
    exec sp_addarticle @Publication = N'$(PublicationName)', @Filter_Clause = '$(FilterClause)', @article = N'InventoryPoolAdjustments', ...


    I think my problem began when I used the actual SQLserverName as @Subscriber portion of this statement which I think normally forces a Snapshot of the Delta.  I think I should have used the AG Subscriber Name here.

    exec sp_addsubscription 
    @Publication = N'$(PublicationName)', 
    @subscriber = N'$(SubscriberServerName)', -- I THINK I SHOULD HAVE USED AGSubscriberListenerName 
    @destination_db = N'$(SubscriberDatabaseName)',
    @subscription_type = N'Push',
    @sync_type = N'automatic', -- , N'none',
    @article = N'All', 
    @update_mode = N'read only', 
    @subscriber_type = 0

    Does this assumption that this is the source of my problem make sense?

    What this resulted in was a Second Subscription being created to the publication to the SAME subscriber database.   A second distribution job was created.

    The snapshot agent started taking a full Snapshot instead of a delta which we want to avoid against our busy OLTP database.  Full snapshot was bad news as we have to do a lot to this DB downstream of a snapshot to get it ready for our needs.

    I could see I had 2 subscriptions to the same publication both targeting the same subscription DB.

    I stopped the snapshot job and I removed that new subscription. 

    Sadly it clobbered ALL the MS repl procs in the subscriber DB.  It also removed the new distribution agent but the older one was still there and trying to update the DB (my light at the end of the tunnel).  But it was complaining that the MSIns proc was missing.

    So I re-generated all the Procs and applied them to the subscriber DB; but now I am getting some duplicate Key errors on Inserts.

    Feels like I am close but I am not sure how it got so confused as to what xact it should be applying.

    If I can't save this situation by end of day I will be forced to re-build from scratch but that is a tough job that I would like to avoid.

    Any suggestions for quickly determining what xact to skip and get that distributor on the right track again without loosing a beat?

    Thanks very much in advance



    Wednesday, October 17, 2018 5:01 PM

All replies

  • Hi ...Ray,

    According to your description, my understanding is that you want to add new articles to an existing publication which is part of an AG and the subscriber is in the AG too. If anything is misunderstood, please tell me.

    Your assumption is right. If the existing subscription is using the name of the availability group listener of the subscriber and sp_helppublication returns a value of 0 in the immediate_sync column, you should use the name of the AG listener to call sp_addsubscription to add the articles to each existing subscription. But you used the actual SQL Server Name which caused that a Second Subscription is created. I think you should delete the Subscription created by SQL Server Name and reinitialize the former Subscription. For more information, please refer to the documents from Reinitialize a Subscription and Add Articles to and Drop Articles from Existing Publications.

    Best Regards
    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

    Thursday, October 18, 2018 6:34 AM