need to add article(without initialization) in existing publication which has been initialized with snapshot in starting

Question

need to add article(without initialization) in existing publication which has been initialized with snapshot in starting

Saurabh Aggarwal

Ten Centuries

Points: 1073
More actions
July 19, 2009 at 8:58 am

#207103

Hi I have two cases:-
1. I want to add a new artcle in existing publication which has been initialized in starting by snapshot. But i dont want to reinitiaLize the publication. when i see the subscription synchronization status it shows me that the "The initial snapshot for article is not yet available." and i need to restart the snapshot agent but i dont want to reinitialize the publication; Could anybody help how to start the replication without initializing.......

Viewing 6 posts - 1 through 6 (of 6 total)

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

gsimmons31 SSC Journeyman Points: 82 More actions · Answer 1

If you simply add an article to an existing publication you will need to run the snapshot agent. The nice thing is that when you run the snapshot agent after adding the article, the entire publication is not wiped and reinitialized. Only the new article will be initialized. It is much less painful than you might think.

I hope this helps.

George

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

Run the following script before generating new snapshot.

EXEC sp_changepublication

@publication = 'MainPub',

@property = N'allow_anonymous',

@value = 'false'

GO

EXEC sp_changepublication

@publication = 'MainPub',

@property = N'immediate_sync',

@value = 'false'

GOAfter that snapshot of only newly added articles will be generated.

Above script is from http://www.replicationanswers.com/Transactional.asp

Saurabh Aggarwal Ten Centuries Points: 1073 More actions · Answer 3

Hi Thanks for the response and apreciate the ans too.

But suppose my target table is approx 5GB and i dont want to sync the table by snapshot and if its mandatory to sync then i prefer to do bcp out and in....Please advice now how should i proceed to start repl with bcp out/in.

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

As per my knowledge, after adding a new article to a publication, it is mandatory to sync it with snapshot. And it does bulk insert, not single row insert.

BTW, is all the 5GB data relevant. If it is old data, can you archive and purge it?

Saurabh Aggarwal Ten Centuries Points: 1073 More actions · Answer 5

I agree with you that in adding a new article we need to apply the snapshot, but what abt in case the article is already in publication and for time being i need to remove the article (table) from publication, ran some script at source and target to alter the article. After ran script i am sure that the data is accurate at both sides and I dont want to reinitialize now coz data is already available at target and reinitialize will take large time for big table...And now how can i restart the replication without initialize.