February 6, 2015 at 3:26 am
Hi All,
I've been trying to find some information on the behaviour of this, but haven't really found what I'm looking for, so going to ask here.
I have a table that is replicated, when it was set up only a subset of the columns were included in the article. I now have a requirement to add one of those columns back in to the article, but I'm unsure of the behaviour that will take place when I do this.
What I'm assuming will happen, is that the table will be marked as invalid and reapplied in full when the snapshot agent next runs. Is this correct? If so, is there any way to cheat by adding the column to the replicated table, back populating the data and then having the replication pick up from where it left off?
Thanks in advance,
Matthew
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
February 10, 2015 at 10:57 am
How big is the table?
If it's not too large it would be easier to drop the article and re-add it with the new columns (or all of them). This is dependent on how you set up your publication to begin with though (assuming you have IMMEDIATE_SYNC set to False.)
If it is set to false then it's just a matter of dropping/re-adding the article, then starting the snapshot agent for the publication
Code top remove the article would go something like this: /* DISABLE Immediate Sync - otherwise the snapshot will occur for EVERY table in the publication (bad) */
[ * * * RUN THIS AT THE DISTRIBUTOR * * * ]
EXEC sp_changepublication @publication = 'PUBLICATION_NAME', @property = 'allow_anonymous', @value = 'false'
GO
EXEC sp_changepublication @publication = 'PUBLICATION_NAME', @property = 'immediate_sync', @value = 'false'
GO
/* Remove the article(s) */
[ * * * RUN THIS AT THE PUBLISHER * * * ]
use PUBLISHED_DATABASE
GO
exec sp_dropsubscription @publication= N'PUBLICATION_NAME', @article =N'TABLE_NAME', @subscriber=N'all'
exec sp_droparticle @publication = N'PUBLICATION_NAME', @article = N'TABLE_NAME', @force_invalidate_snapshot = 1
You could then just use the GUI (or TSQL) to add the article back in, and then kick off the snapshot agent...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 10, 2015 at 1:09 pm
Unfortunately it's a pretty large table, about 40gb; and as always, the business can't afford downtime to drop and readd it. So I'm reserving that for a last resort scenario.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
February 10, 2015 at 1:21 pm
You may want to look at the sp_repladdcolumn
There's some discussion on it here:
http://www.sqlservercentral.com/Forums/Topic153870-71-1.aspx
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 10, 2015 at 2:50 pm
OK thanks, will have a good read.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply