SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Adding an Article Without a Full Snapshot


Adding an Article Without a Full Snapshot

Author
Message
nick.dale.burns
nick.dale.burns
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 248
Comments posted to this topic are about the item Adding an Article Without a Full Snapshot
Andy Warren
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11495 Visits: 2730
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
nick.dale.burns
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 248
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
jeffrey yao
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 882
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
Andy Warren
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: Moderators
Points: 11495 Visits: 2730
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
Misha_SQL
SSC Eights!
SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)SSC Eights! (964 reputation)

Group: General Forum Members
Points: 964 Visits: 1004
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_name SYSNAME = '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
nick.dale.burns
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 248
Nice Smile - 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
paulhayes
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 3
Typically I would create another publication rather than add a new article to an existing publication.



Matt Slocum
Matt Slocum
Mr or Mrs. 500
Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)Mr or Mrs. 500 (534 reputation)

Group: General Forum Members
Points: 534 Visits: 275
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
nick.dale.burns
SSC Veteran
SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)SSC Veteran (210 reputation)

Group: General Forum Members
Points: 210 Visits: 248
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search