January 4, 2015 at 9:37 pm
Comments posted to this topic are about the item Adding an Article Without a Full Snapshot
January 5, 2015 at 11:38 am
Decent article! Some good reading on this also at http://www.replicationanswers.com/TransactionalOptimisation.asp
January 5, 2015 at 12:39 pm
Thanks Andy, that article you linked to is a great read. The context and discussion that Paul put around this was really helpful.
January 5, 2015 at 1:24 pm
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)
January 5, 2015 at 1:37 pm
It changes how long commands are retained in distribution, basically they would get removed after 4 hours.
January 5, 2015 at 2:41 pm
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
January 5, 2015 at 3:00 pm
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.
January 6, 2015 at 3:06 am
Typically I would create another publication rather than add a new article to an existing publication.
January 6, 2015 at 8:10 am
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
January 6, 2015 at 8:54 am
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!
January 6, 2015 at 8:57 am
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.
January 6, 2015 at 10:11 am
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..
January 7, 2015 at 2:31 am
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*/
January 7, 2015 at 1:52 pm
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)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply