add article to replication without snapshotting entire DB.

  • curious_sqldba

    SSC-Dedicated

    Points: 36310

    Suresh B. (2/29/2012)


    sqldba_newbie (2/27/2012)


    ...Apart from getting new articles, would it also capture schema changes for an existing article?

    I believe it creates "snapshot" of only the new articles.

    I believe it does NOT consider any "schema changes" for existing articles.

    Schema changes are anyway replicated by transactional replication. Check your publication options. If it is disabled for some reason, enable it.

    Suresh i tried different options to get the snapshot ONLY for new articles but didn't work for me. Sounds like this did work for you. Would you be able to share the exact steps you did to achieve this? Thanks

  • Suresh B.

    SSC-Insane

    Points: 22986

    1) Run the following (with your database and publication name):

    USE <Database>

    GO

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'allow_anonymous',

    @value = 'false'

    GO

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'immediate_sync',

    @value = 'false'

    GO

    2) Add new article to the publication using UI.

    3) Right click on the publication. Select "View Snapshot Agent Status".

    Click on "Start" button.

    This will generate snapshot for only the newly added articles.

    I have done this many times in different production and test environments. It should work in your environement as well.

  • curious_sqldba

    SSC-Dedicated

    Points: 36310

    Suresh B. (3/1/2012)


    1) Run the following (with your database and publication name):

    USE <Database>

    GO

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'allow_anonymous',

    @value = 'false'

    GO

    EXEC sp_changepublication

    @publication = 'MainPub',

    @property = N'immediate_sync',

    @value = 'false'

    GO

    2) Add new article to the publication using UI.

    3) Right click on the publication. Select "View Snapshot Agent Status".

    Click on "Start" button.

    This will generate snapshot for only the newly added articles.

    I have done this many times in different production and test environments. It should work in your environement as well.

    This is awesome. This works !!!!. Thanks again.

  • neerajtri1

    SSC Enthusiast

    Points: 180

    Hi Suresh,

    Please confirm, post adding the article and starting the agent snapshot status. Do we need to set the publication properties as previously defined and also confirm post setting does it create snapshot again ???

    Regards,

    Neeraj

  • Suresh B.

    SSC-Insane

    Points: 22986

    Hi Neeraj,

    I did not understand your questions correctly.

    Yes, Setting the publication property is needed.

    Regards,

    Suresh

  • neerajtri1

    SSC Enthusiast

    Points: 180

    Hi Suresh,

    Thanks for answering.

    I wanted to know post setting the publisher properties does it generate the snapshot again to sync with subscriber.

    Regards,

    Neeraj

  • neerajtri1

    SSC Enthusiast

    Points: 180

    Hi Suresh,

    Thanks for the answer. I wanted to know whether the snapshot will be generated post setting the publisher properties to sync both the publisher and subscriber.

    Regards,

    Neeraj

  • Suresh B.

    SSC-Insane

    Points: 22986

    No. Changing the publication property does not trigger snapshot.

    You need to manually start the snapshot.

  • yogigollapudi

    Valued Member

    Points: 68

    Thanks , its working.

    again should i set the publication options to true ?

  • Suresh B.

    SSC-Insane

    Points: 22986

    You can leave the publication option as "False".

    It won't break anything.

Viewing 10 posts - 16 through 25 (of 25 total)

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