Adding an Article Without a Full Snapshot

Question

Post reply

Adding an Article Without a Full Snapshot

nick.dale.burns

SSCrazy

Points: 2226
More actions
January 4, 2015 at 9:37 pm

#296405

Comments posted to this topic are about the item Adding an Article Without a Full Snapshot

Viewing 14 posts - 1 through 13 (of 13 total)

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Decent article! Some good reading on this also at http://www.replicationanswers.com/TransactionalOptimisation.asp

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

nick.dale.burns SSCrazy Points: 2226 More actions · Answer 2

Thanks Andy, that article you linked to is a great read. The context and discussion that Paul put around this was really helpful.

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 3

So after you have modified the two properties, allow_anonymous and immediate_sync, you do not change them back to what they were before? Does this have any potential impact to your subscription architecture? (which I think so)

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

It changes how long commands are retained in distribution, basically they would get removed after 4 hours.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 5

I was facing the same challenge. I am pasting abbreviated version of the code I use. It does seem to avoid generating full snapshot.

--1. Declare some variables

DECLARE @found_out INT,

@publication_name SYSNAME = 'your_publication_name',

@article_nameSYSNAME = 'your_table_name',

--2. This part should only run if there is already existing article and we need to change it

BEGIN

-- Check if subscription exists and, if so, drop it first

EXEC sp_helpsubscription @publication = @publication_name, @article = @article_name, @found = @found_out OUTPUT

IF @found_out = 1

BEGIN

EXEC sp_dropsubscription @publication = @publication_name, @article = @article_name, @subscriber= 'all'

END

EXEC sp_droparticle @publication = @publication_name, @article = @article_name, @force_invalidate_snapshot = 1

END

--3. Add article back (or create it for the first time)

EXEC sp_addarticle @publication = @publication_name,

@article = @article_name,

--- list any other desired options (this proc has a ton of parameters) ----

@force_invalidate_snapshot = 1

--4. Add subscription for our newly added article

EXEC sp_addsubscription

@publication = @publication_name,

@subscriber = 'Subscriber_Server_Name',

@destination_db = 'Subscriber_DB_Name',

@subscription_type = N'Push',

@article = @article_name,

@sync_type = 'automatic',

@update_mode = 'read only',

@subscriber_type = 0,

@reserved = 'Internal'

--5. Kick-off snapshot

EXEC sp_startpublication_snapshot @publication = @publication_name

nick.dale.burns SSCrazy Points: 2226 More actions · Answer 6

Nice - thank you for this! I worked with the sprocs for a while, but didn't seem to get it right. I will look forward to using your method.

paulhayes SSC Rookie Points: 36 More actions · Answer 7

Typically I would create another publication rather than add a new article to an existing publication.

Matt Slocum Default port Points: 1497 More actions · Answer 8

The GUI will run the SPROC sp_refreshsubscriptions after adding articles. This SPROC updates the subscription metadata to include the newly added table.

To run this manually, it's a simple matter of including the following in your script:

EXEC sp_refreshsubscriptions @publication = 'PublicationName'

Update PublicationName as appropriate for your publication.

Reference: http://msdn.microsoft.com/en-us/library/ms181680.aspx

nick.dale.burns SSCrazy Points: 2226 More actions · Answer 9

Hi Matt, thank you for this. I had a feeling that there was something missing when I was trying to use the sprocs... but I couldn't quite figure it out. I will add in your suggestion later this week when I run another test - it could be a real life saver in some of our sites!

nick.dale.burns SSCrazy Points: 2226 More actions · Answer 10

Hi Paul, we actually thought about doing the same - just adding a new publication. It would have been a nice simple way to go. But in the end, the publish-subscribe configuration is quite specific in the environment we were in and adding a new publication wouldn't have fit the requirements.

Not all bad though, prompted us to really look at how we went about this.

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 11

Matt Slocum (1/6/2015)
The GUI will run the SPROC sp_refreshsubscriptions after adding articles. This SPROC updates the subscription metadata to include the newly added table.
To run this manually, it's a simple matter of including the following in your script:
EXEC sp_refreshsubscriptions @publication = 'PublicationName'

Exactly that's how I add a new article to an existing publication without changing any publication property..

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 12

Its been a while, but I would avoid using the GUI completely when adding anything to replication. SSMS overly cautious with replication.

Briefly, my way which I believe to be the most simple (and safe) way of doing this is to run these two sprocs then run the snapshot agent manually.

exec sp_addarticle --(once per new article)

exec sp_addsubscription @article='all' --(once per subscriber)

/*run snapshot now*/

jeffrey yao SSCarpal Tunnel Points: 4365 More actions · Answer 13

MysteryJimbo (1/7/2015)
Its been a while, but I would avoid using the GUI completely when adding anything to replication. SSMS overly cautious with replication.
Briefly, my way which I believe to be the most simple (and safe) way of doing this is to run these two sprocs then run the snapshot agent manually.
exec sp_addarticle --(once per new article)
exec sp_addsubscription @article='all' --(once per subscriber)
/*run snapshot now*/

Actually, the right way (at least what I have been using for long time) should be:

exec sp_addarticle --(once per new article)

exec sp_refreshsubscriptions @publication='<your publication name>';

exec sp_startpublication_snapshot @publication='<your publication name>';--this starts the snapshot for the new articles

This applies to transactional replication with push subscription. I guess I can write a blog to detail this (together with pull subscription scenario)

Adding an Article Without a Full Snapshot

Cookies on SQLServerCentral