Chapter 14. Administering a SQL-NS Instance
IN THIS CHAPTER
This chapter explains some of the common tasks involved in administering a SQL-NS instance in a production environment. Administration tasks typically fall into two buckets: monitoring and maintenance. Monitoring
Before taking a SQL-NS deployment live, it's a good idea to practice some of the administration tasks described in this chapter. Although most SQL-NS applications tend to run trouble-free after a few initial problems are worked out, learning the proper administration techniques is still important. Careful administration will alert you to potential problems early and enable you to respond to them effectively.
Monitoring and Reporting
This section describes ways you can monitor a running SQL-NS instance and obtain
SQL-NS provides three main sources of information about the behavior of a running instance:
These information sources are described in detail in this section. You'll probably use all of them in practice.
Using the Application Event Log
Windows provides aset of standard event logs that services and applications (including SQL-NS) use to record events. The events in the event logs provide diagnostic information, error messages, and warnings. The Event Viewer tool (part of the Administrative tools on Windows operating systems) allows you to examine the event logs on your system.
The SQL-NS engine
When you click the Application log in the Event Viewer's left pane, the right pane shows a list of events. Double-click any event listed to see its properties. The Source column in the Event Viewer's right pane indicates which service or application logged each event. Events generated by the SQL-NS engine have a source value of NotificationServices . On your machine, there many not be any NotificationServices events if the SQL-NS engine did not encounter any errors.
Entries in the event logs are
Entries in the event log are categorized as informational messages, warnings, or errors. These types are distinguished by the icon and label in the Type column. By default, the SQL-NS engine writes errors and warnings when it encounters problems in its execution. As described in the "Setting the Logging Level" section (p. 486), you can adjust the engine's configuration to make it log informational messages as well. These messages can provide detailed information about the behavior of the components in the engine, at various stages of processing. Although these messages can be useful, writing them involves a lot of overhead, so they are turned off by default.
SQL-NS Event Log Entries
When the SQL-NS engine encounters an error, or a situation that
Figure 14.1. The Event Viewer showing the properties of a SQL-NS event.
Every event has an Event ID, an identifier that indicates the type of the event. For example, in NotificationServices events, an Event ID of 4045 indicates that the event represents a rule firing failure.
events, the Description section of the Event Properties window always contains text that provides additional information about the occurrence that resulted in the event being logged. Usually, the description contains a specific error or warning message, along with the
The link pointing to additional information is usually at the end of the event description, so you may have to scroll down to see it.
This book does not provide a complete reference to all the SQL-NS events that you might see in the Application Event Log. Instead, Chapter 15, "Troubleshooting SQL-NS Applications," describes the common problem situations and how to diagnose them based on events in the Application Event Log.
Setting the Logging Level
You can adjust the SQL-NS engine's logging level to change the amount of information it writes to the event log. If you're having trouble diagnosing a problem, turning up the engine's logging level can provide additional information that might be helpful.
If the engine runs as a Windows service (as it usually does), you can adjust its logging level by editing the NSService.exe.config file that provides the service with its startup parameters. When the engine is hosted in a custom application process (instead of the service), you can control the logging level with settings in the hosting application's configuration file, as explained in Chapter 17, "Hosting the SQL-NS Execution Engine."
file is always located in the same directory as the service executable,
. You'll find this in the SQL-NS binaries directory,
NSService.exe.config is an XML document, and you can edit it with any XML or text editor. Although this file can be used to pass a number of configuration options to the SQL-NS service, it is most often used to specify logging levels by means of a set of log switches . Listing 14.1 shows the contents of the NSService.exe.config file as installed, before any changes are made.
Listing 14.1. Log Switch Settings in the NSService.exe.config File
Each log switch controls the logging for a particular component in the SQL-NS engine. For example, the
switch controls generator logging. The value set for each switch determines the amount of information the corresponding component writes to the log. All switches are initially set to the value 2, which results in errors and warnings being logged. The log switches can be set to any value between 0 and 4, as
To obtain more or less information in the event log, you can adjust the log switch values for the relevant components. After changing the settings in the NSService.exe.config file, you must restart the service for the changes to take effect.
The log switch values that you set apply to the services for all SQL-NS instances on the machine. There is no way to control the logging levels independently for just one particular instance.
Do not confuse the logging described in this section with the distributor logging described in the "Distributor Logging Options" section (p. 445) in Chapter 12, "Performance Tuning." This section describes log messages written to the Application Event Log by the SQL-NS engine (and the components in it). The distributor logging described in Chapter 12 refers to information the distributor writes to the database as it processes notifications.
SQL-NS Performance Counters
Registering a SQL-NS instance results in the installation of a set of performance counters you can use to obtain
SQL-NS performance counters are grouped into
. Each performance object contains a set of related counters that measure various performance indicators
For example, the events performance object (one of the application-level objects) contains counters that measure the following:
Monitoring these counters gives you a picture of the event activity in the system. You can monitor performance counters using the built-in perfmon tool (part of Windows), a third-party monitoring tool, or programmatically, in monitoring applications of your own.
More than one instance of some counters may be installed, based on the entities in the applications. For example, one instance of each event counter is installed for each event class.
The SQL-NS Books Online provides complete documentation for all the SQL-NS performance objects and the individual counters they contain. Consult the "Notification Services Performance Objects" topic in SQL-NS Books Online for a complete reference. The information presented in this book is intended to be a guide on how to use the performance counters and does not duplicate the detailed counter reference.
To see the SQL-NS performance counters in use, make sure you have the music store instance created, registered, and enabled, and then carry out the following instructions:
Selecting an individual counter in the list causes its last, average, minimum, and maximum values to be displayed in the text boxes below the graph.
Figure 14.2 shows a screenshot of
monitoring the events counters. If you carried out the
Figure 14.2. perfmon is used to monitor the event performance counters.
The values of some performance counters are obtained by doing calculations over the data in the database. These calculations are performed by a performance-monitoring thread in the SQL-NS engine, on a periodic interval. By default, the performance monitor thread calculates and updates the counter values every 1 minute. Between these periodic updates, the counter values do not change. This is the reason for the delay mentioned in step 10 of the previous instructions.
You can adjust the interval on which the performance monitoring thread recalculates the counter values. To do this, use the <PerformanceQueryInterval> element in the ADF. (This element is not used in any of this book's examplessee the SQL-NS Books Online for information on this element.) Be aware that increasing the frequency of performance counter updates increases the load on the SQL Server and may therefore reduce the performance of your applications.
SQL-NS Reporting Stored Procedures
SQL-NS provides stored procedures that generate reports about instances, applications, and their subcomponents. The SQL-NS compiler creates these stored procedures in the instance and application schemas when an instance is compiled. You can invoke these stored procedures directly or from reporting tools to get information about the current state and history of your SQL-NS instance.
The reports produced by the SQL-NS reporting stored procedures fall into three categories:
This section provides some examples of the various reports available. You should browse the "Notification Services Stored Procedures" topic in the SQL-NS Books Online to learn about all the other reports you can use. The SQL-NS Books Online provides detailed reference information for each report.
A Snapshot Report Example
To see an example of a snapshot report, you can invoke the NSSnapshotEvents stored procedure. This produces a report of the current event activity in an instance. For the music store instance, you can invoke it by running the following commands in Management Studio:
USE [MusicStore] GO EXEC [NSInstance].[NSSnapshotEvents]
Like NSSnapshotEvents , many of the other reporting stored procedures are defined in the instance schema, even though they provide information about applications.
The resultset contains information about the enabled state of event submission and the time the last event batch was submitted for each event class in each application. This information allows you to quickly assess the state of the event submission activities. For example, an unusual increase in the
A Diagnostic Report Example
The diagnostic reports can help you troubleshoot problems by providing detailed information about the operation of individual components. For example, the NSDiagnosticEventClass stored procedure produces a report of event-processing activities for a particular event class. In the music store instance, it can be invoked as follows:
USE [MusicStore] GO EXEC [NSInstance].[NSDiagnosticEventClass] N'SongAlerts', N'SongAdded'
The arguments to NSDiagnosticEventClass specify an application name and an event class name. The stored procedure also takes other optional arguments that can be used to specify a time interval for which the report should be generated. The resultset returned summarizes the event batches submitted for the given event class and provides information on how they were processed.
In a running SQL-NS instance, it's better to use the reporting stored procedures to obtain information from the database rather than run queries directly against the tables. The reporting stored procedures are designed and implemented so as to avoid
An Analysis Report Example
An example of a reporting stored procedure that produces an analysis report is NSQuantumPerformance . The data in this report summarizes execution times and other data related to processing of quantums in an application. NSQuantumPerformance is invoked from the application schema. To run it against the SongAlerts application, execute the following statements:
USE [MusicStore] GO EXEC [SongAlerts].[NSQuantumPerformance]
The resultset shows data about the quantums processed by the application. This gives you an indication of how well the generator is keeping up with its workload.