January 17, 2012 at 5:57 am
How can I avoid recreating (& applying) an entire snapshot in the future when I simply want to change 1 article's filter?
Scenario: In 1 publication, we're publishing 50 articles from SQL 2008 R2 to SQL 2008 R2. I was asked to change 1 filter in 1 article. In doing so, SQL Server 2008 R2 prompted me with: This requires a new snapshot and REINITof all subscriptions for this publication.
We only had 1 subscription for this PUBL but this publication consisted of 500 GB data being replicated and I wound up waiting 12 hours for the snapshot / apply to complete -- encompassing every article in the publication.
Is there a way to avoid this and simlply allow the 1 changed article to re-snap -- instead of all 50?
thanks.
January 17, 2012 at 6:17 am
Not that I'm aware of.
Snapshot publications take a "photograph" of the data in the publication at a given time. There is no way AFAIK to apply changes to the snapshots you already have created.
-- Gianluca Sartori
January 17, 2012 at 6:22 am
this is actually a Transactional replication. Would dropping the article THEN re-ADDing the article w/ the new predicate avoid regenerating the ENTIRE Publicaitons snapshot?
From Microsofts instructions: http://msdn.microsoft.com/en-us/library/ms152571.aspx
To delete an article
1. On the Articles page of the Publication Properties - <Publication> dialog box, clear the check box next to each article you want to delete.
2. Click OK.
followed by:
To add an article after a publication is created
1. On the Articles page of the Publication Properties - <Publication> dialog box, clear the Show only checked objects in the list check box. This allows you to see the unpublished objects in the publication database.
2. Select the check box next to each article you want to add.
3. Click OK.
January 17, 2012 at 12:13 pm
I have experimented with adding new articles to an existing publication and successfully taken snapshot for just that one article. That way I can avoid taking snapshot for the entire set of articles.
Few things that you need to check are
1) Use yourDB
select immediate_sync , allow_anonymous from syspublications
If either value comes up as TRUE then you can not avoid taking a full snapshot. So change it now and avoid taking a full snapshot in the future.
EXEC sp_changepublication @publication = 'yourpublication', @property =
N'allow_anonymous', @value='False'
Go
EXEC sp_changepublication @publication = 'yourpublication', @property =
N'immediate_sync', @value='false'
Go
2) If you are using the command sp_articlefilter to add filter to an existing article then according to Microsoft Technet
http://technet.microsoft.com/en-us/library/ms178558.aspx
Those subscriptions have to re-initialzed.
But check the option @force_invalidate_snapshot=1 in that command and see if you can avoid taking a full snapshot.
3) Check the option @reserved='Internal' in the sp_addsubscription command and use that to avoid taking a full snapshot.
4) Lastly, do not use the replication monitor to generate a new snapshot. Run the job via the job activity monitor to run the snapshot.
As I said earlier I have tested this for adding new articles so not sure if this will work for adding/modifying filters. But the steps are documented here so let us know if this works.
http://saveadba.blogspot.com/2011/08/adding-new-article-without-generating.html
Blog
http://saveadba.blogspot.com/
January 17, 2012 at 12:57 pm
thx Trees -- I've added your suggestions to my toolbox of trix! appreciated!
January 17, 2012 at 1:21 pm
Let us know if this helps. I would like to try that if I had to add filtering next time.
Blog
http://saveadba.blogspot.com/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply