Click here to monitor SSC
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 Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 239
Comments posted to this topic are about the item Adding an Article Without a Full Snapshot
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7225 Visits: 2679
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 Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 239
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
SSC Veteran
SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)SSC Veteran (265 reputation)

Group: General Forum Members
Points: 265 Visits: 868
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
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7225 Visits: 2679
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
SSChasing Mays
SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)SSChasing Mays (607 reputation)

Group: General Forum Members
Points: 607 Visits: 984
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 Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 239
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 (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

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



Matt Slocum
Matt Slocum
SSC-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 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 Journeyman
SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)SSC Journeyman (94 reputation)

Group: General Forum Members
Points: 94 Visits: 239
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