April 14, 2008 at 10:47 am
Hi all,
SQL Server 2000, transactional replication, pull subscription.
If you wish to add an existing, currently non-replicated column to an existing vertically-filtered article using sp_articlecolumn (and then do an sp_articleview), a new snapshot of the entire publication is generated once you start the snapshot agent, and (sometimes) only the changed article is distributed to the subscriber. (Following the sp_articlecolumn, the distribution agent actually states "the initial snapshot for is not available").
Sometimes, however, you can follow the same procedure and the distribution agent does not state this or any other message, and indeed it does not distribute the changed article at all. In other words, adding the column to the article has not reinitialized the snapshot (at any level, article or publication). You can then of course choose to reinitialize the subscription but then the whole publication will be distributed. Not ideal for large publications.
In both examples, @force_invalidate_snapshot = 1 and @force_reinit_subscription = 1 are specified in sp_articlecolumn.
Q. Is this because sp_articlecolumn does not reinitialize the subscription if the snapshot files no longer exist? (That's what my tests seem to suggest, but I am none the wiser from reading up on the subject).
Q. On that note, when do snapshot files get removed; is this related to a subscription's expiration?
Many thanks.
James
April 17, 2008 at 7:23 am
It looks like the kind of behavior I want - consistently reinitializing only articles that have changed as opposed to all articles - would come about if I make my pull subscription named instead of anonymous, which would allow me to turn off immediate synchronization - the option that determines if synchronization files are recreated each time the snapshot agent runs.
Does anybody happen to know if there are any considerations to be taken into account regarding moving from anonymous to named, other than making sure the relevant account is in the Publication Access List? As the same domain account is running sql server and sql server agent across our estate I think this is taken care of anyway. I wonder if there are any other things I should be wary of.
Any opinions gratefully received, including if I am wrong on any of my assertions/assumptions in this post!
Many thanks,
James M
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply