add article to replication without snapshotting entire DB.

Question

add article to replication without snapshotting entire DB.

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

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

curious_sqldba SSC-Dedicated Points: 36489 More actions · Answer 1

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 More actions · Answer 2

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: 36489 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

Hi Neeraj,

I did not understand your questions correctly.

Yes, Setting the publication property is needed.

Regards,

Suresh

neerajtri1 SSC Enthusiast Points: 180 More actions · Answer 6

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 More actions · Answer 7

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 More actions · Answer 8

No. Changing the publication property does not trigger snapshot.

You need to manually start the snapshot.

yogigollapudi Valued Member Points: 68 More actions · Answer 9

Thanks , its working.

again should i set the publication options to true ?

Suresh B. SSC-Insane Points: 22986 More actions · Answer 10

You can leave the publication option as "False".

It won't break anything.