microsoft.public.sqlserver.notificationsvcs
[Top] [All Lists]

Re: Set of Subscription Tables to Restore?

Subject: Re: Set of Subscription Tables to Restore?
From: MarkSW
Date: Wed, 31 Aug 2005 20:08:02 -0700
Newsgroups: microsoft.public.sqlserver.notificationsvcs
I confess I left out some relevant information, I thought in the interest of 
clarity but it's actually obscuring the issue.  I have in several instances 
run updates that included event schema changes that reported success but then 
the service would subsequently generate events but not create distributor 
work items from them.  (We are currently attempting to isolate this with MS 
Support.) Each time this has happened I have been able to restore correct 
behavior by deleting and then creating the service -- with the exact same ADF 
and all other config files/components etc.  That is, update mysteriously 
impairs the service, delete and recreate with the same updated ADF (and 
everything else the same) works.

So my thinking is never update, always delete and recreate when there are 
schema changes.  But to do this I will need to restore *all* subscription 
*and* subscriber data.  And I can't simply restore the entire database from 
the backup because then I'll lose my event schema changes.  So I need the set 
of tables that I need to backup and restore to preserve all subscriber and 
subscription data that was in the NS db's before the delete/create.



"Joe Webb" wrote:

> Hi Mark - 
> 
> I've have Shyam's book but not in front of me at the moment. 
> 
> I think you're actually talking about a couple of different scenarios.
> First, if you run NSControl and it fails for some reason, you need to
> be able to return the database to its prior state. You can do this by
> recovering the database from a backup, assuming of course that you
> backed up the database prior to attempting the update. 
> 
> The second scenario is that the update worked, but the subscription
> class metadata has changed. In this case, NSControl update renames the
> existing table to NS<SubscriptionClass>SubscriptionsOld to preserve
> the existing subscription data and creates a new table called
> NS<SubscriptionClass>Subscriptions. So you'll need to migrate the
> subscription data to the newly created table. 
> 
> HTH...
> 
> -- 
> Joe Webb
> SQL Server MVP
> 
> 
> ~~~
> Get up to speed quickly with SQLNS
> http://www.amazon.com/exec/obidos/tg/detail/-/0972688811 
> 
> I support PASS, the Professional Association for SQL Server.
> (www.sqlpass.org)
> 
> 
> On Wed, 31 Aug 2005 07:24:07 -0700, MarkSW
> <[email protected]> wrote:
> 
> >I've read Shyam's book and the section on updates possibly failing (p. 576 
> >on).  It mentions two subscription tables that must be restored if a 
> >subscription schema changes.  And that you should in general backup the NS 
> >DB's before any schema-changing update.  But the restore instructions on p. 
> >582 are for restoring the *entire* DB's from the backup instances, which, 
> >after an update, will then lose the update.  And I don't see a complete 
> >accounting of all tables that hold stateful Subscriber and Subscription data 
> >that must be backed up and restored to insure all Subscrptions are preserved 
> >in case of catastrophe.
> >
> >So, my question. What is the exact set of tables that must be backed up and 
> >retored to guarantee this?
> >
> >My preferred deployment scenario:
> >- Back up all necessary Subscriber/Subscription tables
> >- deploy updated ADF with new schemas
> >- nscontrol update
> >- Restore all necessary Subscriber/Subscription tables
> >
> 

<Prev in Thread] Current Thread [Next in Thread>