Post reply

Need to ADD to 2 articles to a Publication in Txnl replication

  • February 24, 2006 at 11:01 am

    #365345

    I need to add 2 more articles (tables) to an existing publication.  (transactional replication)  What is the best approach for accomplishing this?   Is it possible to avoid re-genning the entire Snapshot?

    thx in advance-

    BT

  • February 24, 2006 at 11:24 am

    #623287

    yes

    use sp_addarticle in t-sql to add articles.

    The Agents will automatically pick up and make snapshots of only the new articles. and transfer them over.

    I believe you also have to run

    sp_reinitsubscription so the snapshot agent knows to pick it up.

    But hopefully someone else can clarify on this one.

  • February 24, 2006 at 11:29 am

    #623291

    I found this - can anyone confirm:

    Create the table on the subscriber (if not already there)

    DTS the data to the subscriber then -

     

    How to.......add daily tables to my SQL DB and replicate them w/o re-running the Snapshot?

    Yes, assuming the data already exists at the subscriber, this is possible - the sp_addsubscription arguments are straightforward: use @sync_type = 'none' when adding the subscription and manually add the related stored procedures at the subscriber (using sp_scriptpublicationcustomprocs). No new snapshot of existing articles is necessary. 

    EXEC sp_addarticle @publication = N'NorthwindOIncludeDRINonCLustered', @article = N'CategoriesArticle',  @source_object = N'Categories', @destination_table = N'Categories'

    GO

    exec sp_addsubscription  @publication = N'NorthwindOIncludeDRINonCLustered', @article = 'CategoriesArticle', @subscriber = N'HOME-WIN2K', @destination_db = N'Pubs', @sync_type = N'none'

    GO

    http://www.replicationanswers.com/Transactional.asp

     

    BT

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

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