| Groups | Blog | Home |
sql server notification services : Set of Subscription Tables to Restore?
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
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 [quoted text, click to view] <MarkSW@discussions.microsoft.com> 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
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. [quoted text, click to view] "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 > <MarkSW@discussions.microsoft.com> 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 > >
What I have done is to copy the data into another table.
The tables that you would be concerned with should be: <Instance><App> DB tables would be: NS<Sub Class>Subscription NOTE: when a schema change is made it will copy the existing table to a table called NS<Sub Class>SubscriptionOld, if you are just performing an update to your application. If you have made more than one change to schema, you must rename or drop the table NS<Sub Class>SubscriptionOld or else the update will not be able to be applied, it gives you an error saying the table already exists. <Instance>NSMain DB tables would be: NSSubscribers NSSubscriberDevices Subscribers and devices should not be affected by schema changes to a subscription class, only if you delete the instance and recreate it. I have not been using NS for very long, but what I have explained is my experience with the product so far. Hope this is helpful. [quoted text, click to view] "MarkSW" <MarkSW@discussions.microsoft.com> wrote in message news:F3858790-BE8B-43FA-8285-E289F65CF2A9@microsoft.com... > 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 > >
I was really saying copy the data do another database on the server, not
another table :). [quoted text, click to view] "Andy" <andyjax@newsgroup.nospam> wrote in message news:OKYWQnurFHA.308@TK2MSFTNGP10.phx.gbl... > What I have done is to copy the data into another table. > > The tables that you would be concerned with should be: > > <Instance><App> DB tables would be: > NS<Sub Class>Subscription > NOTE: when a schema change is made it will copy the existing table to a > table called NS<Sub Class>SubscriptionOld, if you are just performing an > update to your application. If you have made more than one change to > schema, you must rename or drop the table NS<Sub Class>SubscriptionOld or > else the update will not be able to be applied, it gives you an error > saying the table already exists. > > <Instance>NSMain DB tables would be: > NSSubscribers > NSSubscriberDevices > > Subscribers and devices should not be affected by schema changes to a > subscription class, only if you delete the instance and recreate it. > > I have not been using NS for very long, but what I have explained is my > experience with the product so far. Hope this is helpful. > > > "MarkSW" <MarkSW@discussions.microsoft.com> wrote in message > news:F3858790-BE8B-43FA-8285-E289F65CF2A9@microsoft.com... >> 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 >> >> > >
Mark,
Take a look at the end of page 495/top of page 496 in the book. It describes how to copy the subscription data out of the old tables into the new after doing an update. However, I should point out that there's a pretty serious bug in NS2.0. While the subscriber tables are properly renamed to NS<SubscriptionClass>SubscriptionsOld, the related schedules and timezones tables for scheduled subscriptions are not. This means that unless you intervene, subscription schedules are lost on update. We've fixed this problem in the SQL2005 release. However, on NS2.0, you have to back up the NS<SubscriptionClass>Schedules and NS<SubscriptionClass>Timezones tables manually and restore the data in them after the backup. Hope this helps. -shyam -- Learn more about SQL-NS: http://www.amazon.com/exec/obidos/tg/detail/-/0672326647/ --------------------------------------------- This posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm. --------------------------------------------- [quoted text, click to view] "MarkSW" <MarkSW@discussions.microsoft.com> wrote in message news:F3858790-BE8B-43FA-8285-E289F65CF2A9@microsoft.com... > 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 > >
Don't see what you're looking for? Try a search.
|
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |