Maintenance Techniques


A SQL-NS instance will probably undergo a number of changes during its lifetime. Operational problems may require adjustments to configuration settings or the applications' code. External systems (on which the applications depend) may evolve. Deployment hardware is sometimes replaced or upgraded. The applications themselves might need to change in response to new requirements or feature requests. Maintenance is the art of keeping the instance running in the face of such changes, allowing it to grow and evolve over time.

Maintenance also involves some tasks that need to be done regularly to ensure smooth operation. For example, old data in chronicle tables may need to be removed to free up disk space and improve query performance. This section describes the basic techniques you use when maintaining SQL-NS instances.

Enabling and Disabling Components

As you saw in previous chapters, a SQL-NS instance can be enabled and disabled via Management Studio or the nscontrol enable and nscontrol disable commands. In most of this book's examples, we used these tools to enable or disable a whole instance at once. However, both Management Studio and the nscontrol commands also provide the capability of enabling or disabling individual components within an instance or application. This can be useful when maintenance tasks require one component to be taken down (for repairs or changes to be made) while leaving others running.

Enabling and Disabling Components with Management Studio

In Management Studio, the components of an instance can be enabled and disabled from the instance's properties dialog box. To open the instance properties dialog box, right-click an instance in the Notification Services folder in the Object Explorer tree and choose Properties.

The Instance Properties dialog box has four pages. You select a page to view using the page selector in the pane on the dialog box's left side. The Applications page provides controls for enabling and disabling components of an application, and the Subscribers page has controls for enabling and disabling subscriber management for the whole instance.

In the Applications page of the Instance Properties dialog box, the first drop-down list allows you to select among the applications in the instance. When you select an application, the components grid beneath the drop-down list is populated with that application's components.

To enable a component, check its box in the Enable column (the first column in the grid). To disable a component, uncheck its box in the Enable column. Note that the first component, labeled Application, displayed in the components grid represents the application as a whole. Checking or unchecking the Enable box in the Application component's row is a convenient way to enable or disable all components in the application. This is also the only way to enable an application's vacuum, which is not listed as a separate component.

To enable or disable subscriber management for an instance, go to the Subscribers page of the Instance Properties dialog box. The Subscribers page shows the current state of subscriber management and provides a check box you can use to change it.

Click the OK button in the Instance Properties dialog box to apply any changes made to the component states. These changes take effect as described in the section "Applying Component State Changes" (p. 495).

Enabling and Disabling Components with nscontrol

The command-line help for nscontrol enable shows that it takes the following arguments:

 Usage: enable [-nologo] [-help]     [<component>] -name <instanceName>     [-server <databaseServer>] [-application <applicationName>]     [-sqlusername <sqlUserName> -sqlpassword <sqlPassword>]     where <component> can be one or more of the following:         -events [<systemName>]         -generator         -distributor [<systemName>]         -subscriptions         -subscribers 


The -name argument (or -n for short, as we have used it in this book's examples) is always required to specify the instance name. Specifying only this argument causes the whole instance to be enabled. You can restrict the scope of the enable command by using additional arguments.

The <component> argument can be used to specify a particular component (or functional area) to enable. For example, you can enable the generator by supplying the -generator argument, or you can enable the subscription management functions by supplying the -subscriptions argument.

The -events and -distributor arguments each take an optional <systemName> qualifier. This is used to restrict the components affected by the command to just those configured to run on a particular machine. For example, to enable only the distributor on a machine called DistributionServer1, you would specify -distributor DistributionServer1. Similarly, when the -events argument is used, supplying a <systemName> qualifier makes the command apply only to hosted event providers on the given system. When the <systemName> qualifier is omitted, the command is applied to hosted event providers on all systems.

Caution

In their ADF declarations, standalone event providers are not associated with any particular machine. When nscontrol enable is invoked with the -events argument, standalone event providers are always enabled, whether or not a <systemName> qualifier is used (and regardless of the value given).


The -application argument is used to restrict the components affected by the command to those in a particular application within the specified instance. The -application argument can be used in conjunction with the other arguments that identify application subcomponents, or it can be used alone. For example, specifying -application SongAlerts -generator makes the command enable only the generator in the SongAlerts application (leaving the state of the generators in all other applications in the instance unmodified). When used without another argument to restrict the command to an application subcomponent, the -application argument causes the command to apply to all components in the application. Omitting the -application argument causes the command to apply to all the applications in the instance.

Tip

Unfortunately, there isn't a -vacuum argument that can be used to specifically apply the enable command to the vacuum component. Using the -application argument, without any other subcomponent arguments, is the only way to change the state of the vacuum in an application.


Note

The arguments to nscontrol disable are the same as those listed for nscontrol enable, and they work the same way. Using arguments as described in this section, you can restrict the scope of the disable command to particular components or applications.


Applying Component State Changes

It's important to understand that the enabling and disabling components with Management Studio or the nscontrol commands does not manipulate the running components in the SQL-NS engine directly. These commands set flags in various instance and application database tables to indicate that the state of the components should change. The SQL-NS engine periodically polls these flags, and when it sees changes, it responds by enabling or disabling the relevant components. Because of this polling scheme, there may be a delay between the time that you issue an enable or disable command and when the engine actually implements the changes to the states of the running components. Usually, this delay is no more than 30 seconds.

The only exceptions are the enabling and disabling of subscribers, subscriptions, and standalone event providers. For these, state changes take effect immediately because the state flags in the database are checked by the subscription management and event submission APIs on every invocation.

Note

If the SQL-NS engine is not running, changes to component states made via Management Studio, nscontrol enable, or nscontrol disable will affect which hosted components will run the next time the engine starts.


Cleaning Up Old Data

As described in the "Vacuuming Old Data" section (p. 467) in Chapter 13, "Deploying a SQL-NS Instance," the data that accumulates in SQL-NS application database tables must be cleaned out regularly. The SQL-NS vacuum can be configured to run automatically, on a configurable schedule, to remove the data related to old events and notifications.

This section covers two additional aspects of the cleanup problem: invoking vacuuming manually and cleaning out data that is outside the scope of the vacuum. Using the techniques explained in this section to perform complete and regular cleanup is a critical part of application maintenance.

Invoking Vacuuming Manually

SQL-NS allows you to invoke the vacuum manually, if you need to remove old data at a time when the vacuum is not normally scheduled to run. You might need to do this if the scheduled vacuuming runs fail to remove all the old data (because the scheduled intervals are too short or because of errors during vacuum execution).

To invoke vacuuming manually, you execute the NSVacuum stored procedure, which the compiler creates in the application schema. NSVacuum takes one required argument: the maximum number of seconds it should run. This argument allows you to specify a time limit for the vacuum's execution. NSVacuum uses the same logic that the regular, scheduled vacuum process uses: It removes all processed data that is older than the application's configured retention age. NSVacuum runs until the specified time limit is exceeded or until there are no remaining items to vacuum, whichever happens sooner.

Note

The argument passed to NSVacuum is often mistaken for a retention age. Remember that the NSVacuum argument specifies only the number of seconds that the vacuuming process should run, not the age of the items it should vacuum. The retention age specified in the <RetentionAge> element in the ADF still applies, even when vacuuming is invoked manually. To change the retention age, edit the <RetentionAge> element in the ADF and then update the instance.


In the music store database, you can invoke NSVacuum as follows:

 USE [MusicStore] GO EXEC [SongAlerts].[NSVacuum] 600 


The argument in this example specifies a maximum run time of 600 seconds, or 10 minutes. If you run this code snippet against the music store database on your system, NSVacuum will most likely return immediately because the database in its current state probably doesn't contain any items older than the retention age to vacuum.

NSVacuum returns a resultset containing a single row with three columns:

  • StatusIndicates the status of the vacuuming operation. A value of 2 indicates that vacuuming completed successfully, and a value of 3 indicates that the time limit was exceeded while there were still remaining items to vacuum.

  • QuantumsVacuumedIndicates the number of quantums for which data was removed.

  • QuantumsRemainingIndicates the number of quantums eligible for vacuuming that were not vacuumed because NSVacuum ran out of time.

Note

The vacuuming process is based on quantums, which is why the amount of data vacuumed and the amount of data remaining are both expressed as a number of quantums. The vacuum considers quantums older than the retention age (as measured by the quantum end time) and marks them as eligible for vacuuming if all the event data submitted during the quantum and all notification data generated during the quantum has been completely processed. It then proceeds down the list of marked quantums and deletes the event, notification, and other associated data.


Cleaning Up Chronicle Tables

The data in chronicle tables is outside the scope of the SQL-NS vacuum. SQL-NS does not control the schema or semantics of chronicle tables, so it cannot manage their cleanup automatically. When the vacuum runs, the chronicle tables are not affected.

If your application accumulates data in chronicle tables, you must implement your own cleanup mechanism to remove old data that is no longer needed. The retention policy you use to determine when data can be cleaned up depends on the semantics of the chronicle table and the purpose it serves in your application's logic.

For example, an event chronicle table that keeps event history will probably be cleaned up differently from a subscription chronicle that keeps state for each subscription. The event chronicle can be cleaned up based on the age of the event data, but rows should be removed from the subscription chronicle only when the corresponding subscriptions are deleted.

You should write a SQL cleanup script for each chronicle table in your application. In each script, you can implement the cleanup policy appropriate for the particular chronicle table. You can run these cleanup scripts manually at any time, or you can set up SQL Server Agent jobs to run them automatically on a regular basis. For more information on using SQL Server Agent to set up scheduled tasks, consult the SQL Server Books Online.

Tip

It's a good idea to create a separate cleanup script for each chronicle table rather than lump the cleanup logic for multiple chronicle tables into one script. Using separate scripts allows you to easily clean up chronicles selectively, one at a time.


This chapter's scripts directory, C:\SQL-NS\Chapters\14\Scripts, contains SQL scripts that clean up the two chronicle tables defined in the music store sample application. The script CleanupSongAddedLog.sql removes old data from the SongAddedLog event chronicle table, and the script CleanupNewSongByGenreSubscriptionProcessingTimes.sql removes data from the NewSongByGenreSubscriptionProcessingTimes subscription chronicle. These scripts can be invoked manually or called from SQL Server Agent jobs.

Note

For a review of the music store application's chronicle tables and their semantics, see the "Event Chronicles" section (p. 163) and the "Subscription State" section (p. 170) in Chapter 6, "Completing the Application Prototype: Scheduled Subscriptions and Application State."


Listing 14.2 shows the code in the CleanupSongAddedLog.sql script.

Listing 14.2. Cleanup Script for the SongAddedLog Chronicle Table

 USE [MusicStore] GO DECLARE @oldestProcessingTime DATETIME DECLARE @oldestCreationTime DATETIME DECLARE @cutoffTime DATETIME --Determine the oldest processing time in the subscription chronicle. SELECT @oldestProcessingTime=MIN(LastProcessingTime) FROM   [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] --Determine the creation time of the oldest subscription not in the --subscription chronicle. SELECT  @oldestCreationTime=MIN(subscriptions.Created) FROM    [SongAlerts].[NSNewSongByGenreSubscriptions] subscriptions WHERE   NOT EXISTS (     SELECT chron.SubscriptionId     FROM   [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] chron     WHERE  chron.SubscriptionId = subscriptions.SubscriptionId ) --The cutoff time is the earlier of the oldest processing time and --the oldest creation time. IF @oldestProcessingTime < @oldestCreationTime     SELECT @cutoffTime = @oldestProcessingTime ELSE     SELECT @cutoffTime = @oldestCreationTime --Delete items from the event chronicle older than the cutoff time. DELETE FROM [SongAlerts].[SongAddedLog] WHERE TimeAdded <= @cutoffTime 

Remember that the event chronicle's purpose is to provide an event history against which the scheduled subscriptions are evaluated. We cannot simply delete rows from the event chronicle based on a fixed retention age because some of those rows may still be needed for scheduled subscriptions that have not yet been evaluated. The cleanup script works by using the subscription chronicle and the subscription data to determine a cutoff time for the events that should be left in the event chronicle. Events older than the cutoff time are deleted, and the rest are left behind. As long as the cutoff time is calculated correctly, we can be sure that events that are still needed in the event history do not get deleted.

The cutoff time is determined by looking at the oldest processing time recorded in the subscription chronicle and the creation time of the oldest subscription that has not yet been evaluated. The earlier of these two times becomes the cutoff. This works because of the way the scheduled subscription-matching logic scopes the data in the event chronicle. All subscriptions that have been evaluated at least once have already seen events older than their processing times recorded in the subscription chronicle. The oldest processing time in the subscription chronicle gives the cutoff time for the oldest event that all evaluated subscriptions have seen.

Subscriptions that have not yet been evaluated will never be evaluated against events older than their creation times (because the last processing time in the subscription chronicle is initialized to the creation time of the subscription on first evaluation). Thus, the creation time of the oldest subscription without an entry in the subscription chronicle provides the cutoff time for events that will never be seen by the yet unprocessed subscriptions. The earlier of the two cutoff times determines which events can safely be deleted.

Caution

Although a strategy like this guarantees that the required event history will be kept for all scheduled subscriptions, it may not clean up the chronicle fast enough from a maintenance perspective. A single scheduled subscription with an infrequent schedule can cause data to be left in the chronicle for extended periods of time. For example, a scheduled subscription due to be next evaluated six months into the future would require all chronicle data that arrived after its creation time to remain in the chronicle until it is evaluated.

A compromise would be to declare a maximum age for chronicle data and have the cleanup script remove all data older than this age, regardless of whether any outstanding scheduled subscriptions will ever be evaluated against it. By using a relatively large maximum age, you can handle the vast majority of subscriptions correctly.


Listing 14.3 shows the code in the CleanupNewSongByGenreSubscriptionProcessingTimes.sql script, which cleans up the subscription chronicle.

Listing 14.3. Cleanup Script for the NewSongByGenreSubscriptionProcessingTimes Chronicle Table

 USE [MusicStore] GO DELETE FROM [SongAlerts].[NewSongByGenreSubscriptionProcessingTimes] WHERE SubscriptionId NOT IN (     SELECT SubscriptionId     FROM   [SongAlerts].[NSNewSongByGenreView] ) 

This script is much simpler than the one used to clean up the event chronicle. It simply deletes rows from the subscription chronicle that do not have corresponding rows in the view of all subscriptions. Such rows correspond to subscriptions that were evaluated at least once but were since deleted.

Note

As an alternative to the asynchronous cleanup strategy described here, the subscription chronicle could be maintained by the SMI's logic. Whenever a subscription is removed, the corresponding chronicle entries could be removed, too.


Making Changes to Deployed SQL-NS Instances

To make changes to a deployed SQL-NS instance, you use essentially the same techniques that you used when you made incremental changes during development. First, you edit the ICF or ADFs and make the necessary modifications. Then you disable the instance and run either nscontrol update or the Update Instance command in Management Studio.

Although the procedure is the same as in the development stage, you must be a lot more careful when performing updates against a deployed instance. Because SQL-NS requires an instance to be disabled before it can be updated, you must schedule the update so that the downtime has the least impact on users. While an instance is disabled, users will not be able to enter subscriptions, event providers will not be able to submit events, and no notifications will be generated or delivered. During the downtime, you may want to take the SMI offline to prevent users from trying to add or manage subscriptions.

In addition to scheduling the update properly, you also need to be concerned with potential data loss and corruption of the instance. The update process is atomic and the SQL-NS compiler will always leave the instance in a consistent state after it completes an update, but problems can still occur. For example, some changes introduced in an update are difficult to reverse. If these changes don't have the effects you intended, you may be left with an inoperable instance. Also, some updates rebuild tables, causing existing data in them to be lost. This section describes the steps you should take, before and after performing an update, to protect your instance against data loss and other problems.

Note

Despite the warnings about the potential pitfalls of the update process, the SQL-NS update tools (nscontrol update and the Update Instance command in Management Studio) are still the only means you should ever use to make changes to your instance. You should never update the instance or application database objects directly, even for seemingly trivial changes. The warnings given here are intended only to alert you to potential dangers so that you can take the appropriate precautionary measures.


Tip

To minimize the chances of problems, you should do updates incrementally. Instead of making several changes to an ADF at once and then performing a single, large update operation, it's better to make the changes individually and use several update operations to apply them one at a time. The more complex the changes introduced in an update, the greater the chance that something could go wrong.


Preparing to Perform an Update

The safest approach to performing an update is to first do a full backup of the databases containing the instance and application objects. This allows you to restore them to their original states should a bad update leave them in an unrecoverable condition. The section "Backing Up a SQL-NS Instance" (p. 502) later in this chapter provides information on how to perform backups.

Performing an update (even if it completes successfully) may result in data loss in the following cases:

  • If the changes in the ADF require the compiler to rebuild the event class or notification class tables, data in those tables will be lost after the update completes. Changing the schemas of these classes, including the <IndexSqlSchema> elements, usually causes these tables to be rebuilt.

  • If the compiler detects changes to the chronicle statements, they will be rerun. If these statements are written so that they drop and re-create existing tables, any data in the tables will be lost. For an example of such chronicle statements, see Listing 6.1 (p. 164) in Chapter 6.

  • If you make changes to the subscription class schema declarations (including <IndexSqlSchema> elements), the subscription tables will be rebuilt. Before rebuilding the tables, the compiler renames the existing tables to preserve the data in them (this mechanism is described in the "Testing the Effects of the New Indexes" section, p. 432, in Chapter 12).

If you will need any of the data that will be lost during the update, be sure to make a copy of it before performing the update.

Performing an Update

After backing up the required data, invoke nscontrol update or the Update Instance command in Management Studio to compile your ICF and ADF changes. For details on both commands, see the section "Making Updates to an Instance and Its Applications," (p. 131) in Chapter 5, "Designing and Prototyping an Application." After examining the modified ICF and ADFs, the update commands display the list of changes they will make to the databases and then ask for your confirmation before proceeding. Although the displayed list of changes is sometimes difficult to read, it's worth scanning to ensure that the update will do what you expect.

If the update tools encounter an error while applying changes to the databases, they roll back all changes made up to that point. This leaves the databases in their original state. Usually, you can fix whatever part of the ICF or ADF code resulted in the error and run the update command again. You may need to repeat this process several times until the update completes successfully.

Post-Update Steps

After an update has completed successfully, you may need to take additional steps before you can get the instance running again. Specifically, you must restore any lost data you require, either from the database backup or from other copies that you made.

If your changes resulted in subscription tables being recompiled, make sure that you copy any old subscription data from the old renamed subscription tables into the new ones. It's particularly important to copy back the subscription schedule data in the case of scheduled subscriptions. The section "Testing the Effects of the New Indexes" (p. 432) in Chapter 12 explains this process in detail and includes information on a sample script you can look at for an example of how subscription and schedule data should be restored.

After copying the old data into the new tables, you should drop the old tables. If you leave these tables around, any subsequent updates that require renaming of the subscription tables will fail because the update tools will try to reuse the existing old table names, generating a conflict.

Finally, after restoring all data, it's important that you test the instance before making it available to users again. Just because the update command succeeded, there is no guarantee that the changes will work as you expect. It's always important to verify, in the production environment, any changes you make.




Microsoft SQL Server 2005 Notification Services
Microsoft SQL Server 2005 Notification Services
ISBN: 0672327791
EAN: 2147483647
Year: 2006
Pages: 166
Authors: Shyam Pather

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net