Post reply

Bizarre Transaction Replication Issue Subscription Status 3(!?!)

  • January 26, 2015 at 9:51 pm

    #314066

    Hello.

    Right, I really hope there is someone out there can explain this to me. First, the details...

    SQL Server 2012 ENT SP2 on the 3 servers (Pub, Dist, Sub) involved.

    Transaction Replication push subscription.

    All I wanted to do was add a new table to a database (done),

    Add it to my publication as an article (done via the GUI, with allow schema changes set to TRUE),

    Have it appear in the existing subscription. The issue.

    I appear to be forced into dropping the subscription, recreating it, reinitialising, creating a new snapshot (takes hours and hours) for the new table to be applied to the subscription. Thing is, it takes a snapshot all the articles, and I want to avoid this. Its somewhat time consuming.

    So I read an article (no actual need to read it unless you want to)...,

    http://www.mssqltips.com/sqlservertip/2502/limit-snapshot-size-when-adding-new-article-to-sql-server-replication/

    ...very simple, but its not working for me, I should be able to generate a snapshot that only contains the changes. My one table.

    I have all the correct settings, but the snapshot will always take the full DB (all the articles).

    There are supposed to be only 3 Subscription status available when running sp_helpsubscriptions (my new table shows up...

    Subscription status:

    0 = Inactive

    1 = Subscribed

    2 = Active

    ...but when I run this, all the subscription status' are 3(?!?), I have double checked on the MSDN site, and there is no 3.

    I just don't get it, and I should be able to generate a snapshot that only contains the changes. I do not know if it matters or makes a difference, but when Transaction Replication was set up, we did not initialise the subscriber as the subscriber database was a backup (along with the subscriber) and was up to date.

    Any help would be so appreciated, especially in regards to the subscription status.

    Kind regards,

    D.

  • January 27, 2015 at 5:20 pm

    #1773541

    I can't speak to the subscription status issue but the below link is from the Microsoft REPLTalk blog. see option 2. I found this by doing an internet search for "add article to a subscription"

    http://blogs.msdn.com/b/repltalk/archive/2011/03/15/how-to-add-an-article-to-an-existing-transactional-subscription-initialized-through-backup.aspx

  • January 29, 2015 at 3:42 pm

    #1774200

    Hello,

    I tried this but inserts into the table at the publisher are not replication to the subscriber. In my view this does not work.

    Regards,

    D.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply