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
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 270
Comments posted to this topic are about the item Adding an Article Without a Full Snapshot
Andy Warren
Andy Warren
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24795 Visits: 2746
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
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 270
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 Visits: 895
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
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: Moderators
Points: 24795 Visits: 2746
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
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1622 Visits: 1010
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
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 270
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
SSC Rookie
SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)SSC Rookie (26 reputation)

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



Matt Slocum
Matt Slocum
SSChasing Mays
SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)SSChasing Mays (660 reputation)

Group: General Forum Members
Points: 660 Visits: 277
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
Mr or Mrs. 500
Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)Mr or Mrs. 500 (579 reputation)

Group: General Forum Members
Points: 579 Visits: 270
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