none
Refresh replication objects

    Question

  • Hi

    I have transnational replication running successfully from one server to another.  However, we have created a few more objects on the source server subsequent to the setup of the initial replication.  What is the most effective method of including these new objects from the source server to the subscription?

    Also, we would like to remove certain objects too.  What would be the best method around this also?

    Thanks.

    Wednesday, April 02, 2014 4:16 PM

Answers

  • Wednesday, April 02, 2014 4:18 PM
  • Dropping article from the publisher doesn't remove the objects from publisher and subscriber. You need to manually drop the object from the subscriber.

    Adding an article to publication requires snapshot to be generated, if you don't want to generate a new snapshot you can use Force_invalidate_snapshot option.Normally, if the number of articles are huge and it might take time to generate the snapshot in only those cases we invalidate the snapshot.

    You can refer the below article

    http://sql-articles.com/articles/high-availability/sql-2005-transaction-replication-adding-new-article-to-an-existing-publication/

    The best and recommended method is to follow the link shared by Tom

    -Prashanth


    Wednesday, April 02, 2014 4:39 PM
  • Considerations must be made when adding or dropping tables and/or making schema changes on the publication database.  These considerations are covered in Add Articles to and Drop Articles from Existing Publications and Make Schema Changes on Publication Databases.

    Adding a new article involves adding the article to the publication, creating a new snapshot, and synchronizing subscriptions to apply the schema and data for the newly added articles.  Reinitialization is not required, but a new snapshot is.

    Dropping an article from a publication involves dropping the articles, creating a new snapshot, and synchronizing subscriptions.  Special considerations must be made for Merge publications with parameterized filters and compatibility level lower than 90RTM.

    To add a new column to a table and include it in an existing publication, you'll need to use ALTER TABLE <Table> ADD <Column> syntax at the publisher.  By default the schema change will be propagated to subscribers, publication property @replicate_ddl must be set to true.

    I hope this helps.


    Brandon Williams (blog | linkedin)

    Wednesday, April 02, 2014 5:06 PM
    Moderator

All replies

  • Wednesday, April 02, 2014 4:18 PM
  • Dropping article from the publisher doesn't remove the objects from publisher and subscriber. You need to manually drop the object from the subscriber.

    Adding an article to publication requires snapshot to be generated, if you don't want to generate a new snapshot you can use Force_invalidate_snapshot option.Normally, if the number of articles are huge and it might take time to generate the snapshot in only those cases we invalidate the snapshot.

    You can refer the below article

    http://sql-articles.com/articles/high-availability/sql-2005-transaction-replication-adding-new-article-to-an-existing-publication/

    The best and recommended method is to follow the link shared by Tom

    -Prashanth


    Wednesday, April 02, 2014 4:39 PM
  • Considerations must be made when adding or dropping tables and/or making schema changes on the publication database.  These considerations are covered in Add Articles to and Drop Articles from Existing Publications and Make Schema Changes on Publication Databases.

    Adding a new article involves adding the article to the publication, creating a new snapshot, and synchronizing subscriptions to apply the schema and data for the newly added articles.  Reinitialization is not required, but a new snapshot is.

    Dropping an article from a publication involves dropping the articles, creating a new snapshot, and synchronizing subscriptions.  Special considerations must be made for Merge publications with parameterized filters and compatibility level lower than 90RTM.

    To add a new column to a table and include it in an existing publication, you'll need to use ALTER TABLE <Table> ADD <Column> syntax at the publisher.  By default the schema change will be propagated to subscribers, publication property @replicate_ddl must be set to true.

    I hope this helps.


    Brandon Williams (blog | linkedin)

    Wednesday, April 02, 2014 5:06 PM
    Moderator