Overview of the Notification Services Tools
This section looks at the tools available for deploying and administering instances of Notification Services. It starts with the NSControl command line utility because it was used as the underlying framework for the Notification Services tools in SQL Server Management Studio. It then looks at the equivalent commands in SQL Server Management Studio, the NMO API, and then the stored procedures and performance counters you can use to monitor performance.
NSControl Command Line Utility
In the previous release of Notification Services, the NSControl command line utility was the only toolset for deploying and managing instances of Notification Services. This utility still exists in SQL Server 2005 Notification Services, and is the tool of choice for those who like to script deployment and management tasks.
Notification Services Command Prompt
To make it easier to run NSControl commands, SQL Server 2005 installs a Notification Services command prompt. This command prompt opens to the location where the Notification Services binaries are installed. To open this command prompt window, click the Start menu, All Programs, Microsoft SQL Server 2005, Configuration Tools, and then click Notification Services Command Prompt.
Basic NSControl Syntax
The basic syntax of an NSControl command is as follows:
nscontrol command command_options
Full syntax, permission requirements, and examples for each command are provided in SQL Server 2005 Books Online.
As an example, let's look at the nscontrol create command, which creates database objects for a new instance of Notification Services. It has the following syntax:
[-sqlusername name -sqlpassword password]
The options are as follows:
The nologo and help options are standard to all NSControl commands. The nologo option suppresses verbose product information in the command line output. The help option displays the syntax for the command.
The in option specifies the path and filename of the instance configuration file (ICF) used to compile the instance and its applications. Because many paths and filenames contain special characters, you should enclose the instance_config_file value in quotation marks.
The sqlusername and sqlpassword options are used only if you are using SQL Server Authentication to connect to the instance of SQL Server. The username and password must be able to create databases or database objects in the existing databases, depending on the database settings defined for the instance and its applications.
The argumentkey option is used to encrypt event provider and delivery channel argument values before storing them in the database. (These parameters sometimes contain sensitive information, such as passwords.)
And finally, the param=values option enables you to provide values for parameters specified in the ICF. You can think of these parameters as a way of doing a search-and-replace before processing the XML.
To learn how the NSControl utility works, it helps to see an example. To create database objects for the instance and its applications, you use the nscontrol create command. This command validates an XML instance configuration file (ICF), validates referenced application definition files (ADFs), and then creates instance and application database objects.
The following example shows how to create database objects using an ICF named MyICF.xml:
nscontrol create -in "C:\NS\MyNSInstance\MyICF.xml"
This command gets the MyICF.xml file located in C:\NS\MyNSInstance, processes the file, and then processes referenced application definition files (ADFs). Because the sqlusername and sqlpassword options are not used, nscontrol create uses Windows Authentication to connect to SQL Server and create the database objects. Also, because no argumentkey value is provided, event provider and delivery protocol arguments are stored as clear text.
In SQL Server 2005, NSControl has 13 commands, adding the nscontrol export and nscontrol repair commands in this release. Table 15-1 lists the commands, the purpose for each command, and the security requirements for running the command.
Table 15-1. NSControl Commands
Creates databases or database objects for a new instance of Notification Services.
Permission to create databases or add database objects to the specified databases.
Deletes databases or database objects for an instance of Notification Services (after asking for confirmation).
Permission to drop databases or database objects. (If Notification Services created the databases, nscontrol delete drops them; otherwise, nscontrol delete drops just the database objects.)
Disables the specified Notification Services components (instance, application, event provider, generator, or distributor).
Membership in the NSAdmin database role (or higher) for updating database tables.
Displays the key used to encrypt delivery channel and event provider arguments
Membership in the Administrators or Power Users groups in Windows (to read the Registry), or must be the same account as used to run the engine.
Enables the specified Notification Services components (instance, application, event provider, generator, or distributor).
Membership in the NSAdmin database role (or higher) for updating database tables.
Exports metadata from the instance and application objects and creates one XML instance configuration file (ICF) and one XML applicationdefinition file (ADF) per application.
Membership in the NSReader database role (or higher) for database reading database tables.
Displays information about the installed versions and registered instances of Notification Services.
No special security requirements.
Creates or updates local Registry entries and performance counters for an instance of Notification Services, and optionally creates a Windows service.
If creating a Windows service, the account must be a member of the local Administrators group.
Updates metadata for the instance of Notification Services. When you upgrade an instance of Notification Services 2.0 to SQL Server 2005, you must run this command to add metadata to the msdb database. If you move an instance of Notification Services or perform any action that removes or alters instance metadata from msdb, run this command to repair the metadata.
Members of the db_owner database role and the sysadmin and dbcreator fixed server roles can run nscontrol repair. If you are not using the server argument, the account that you use to run nscontrol repair must be able to read the Registry.
Displays the current enabled or disabled status of instances and applications and the current state of associated Windows services.
Membership in of one of the following database roles: NSAnalysis, NSDistributor, NSEvent Provider, NSGenerator, NSReader, NSRunService, NSSubscriberAdmin, or NSVacuum.
ns control unregister
Removes the Registry information and performance counters for the instance, as well as the local Windows service, if it exists.
Membership in the local Administrators group, which allows nscontrol unregister to access the Notification Services binary files, to update the Registry, and to remove a Windows service.
Compares the metadata in the ICF and the ADFs to the metadata in the instance and application databases, and then optionally up dates the databases for any differences that it finds.
Membership in the db_owner database role in all instance and application databases affected by the nscontrol update command, or membership in the sysadmin server role.
Upgrades an instance of Notification Services that was created with an older version of the product to the current version or from a lower edition to a higher edition.
Membership in the db_owner fixed database role or the sysadmin server role.
SQL Server Management Studio
In SQL Server 2005, Notification Services added the capability to perform deployment and management tasks in SQL Server Management Studio. For example, rather than provide between one and six options to the nscontrol create command, you can simply right-click Object Explorer's Notification Services folder, select New, and then complete fields in the resulting dialog box.
The Notification Services client components are installed with SQL Server Management Studio, meaning you can do many deployment and management tasks from any installation of SQL Server Management Studio. However, the Notification Services engine components are not installed. You must install the Notification Services engine components to locally register and run an instance of Notification Services.
Notification Services Commands
In SQL Server Management Studio, instances of Notification Services appear in the Notification Services folder of Object Explorer. The instances shown have their data in the current instance of SQL Server and have metadata about the instance in the msdb database. If instances do not appear, you may need to run nscontrol repair or you may need to refresh Object Explorer.
Notification Services commands in SQL Server Management Studio are divided into server-level commands and Notification Services instance-level commands. Figure 15-3 shows the server-level commands, which you access by right-clicking the Notification Services folder.
Figure 15-3. Server-level commands.
As mentioned earlier, most SQL Server Management Studio commands for Notification Services are equivalent to NSControl commands. Table 15-2 lists the NSControl equivalents for the server-level commands.
Table 15-2. Notification Services Commands
Management Studio Command
New Notification Services Instance
None. However, if deployed instances of Notification Services do not appear, you may need to use nscontrol repair to fix instance metadata.
After you create an instance of Notification Services, the instance appears in the Notification Services folder. When you right-click an instance of Notification Services, a shortcut menu appears with commands relevant to the instance (see Figure 15-4).
Figure 15-4. Instance-level commands.
Table 15-3 lists the NSControl equivalents to these commands.
Table 15-3. Instance Commands
Management Studio Command
nscontrol enable. For more granular control, use the Properties dialog.
nscontrol disable. For more granular control, use the Properties dialog.
None. If using Windows services to run the instance of Notification Services, this starts all services associated with the instance.For more granular control, use the Properties dialog.
None. If using Windows services to run the instance of Notification Services, this stops all services associated with the instance. For more granular control, use the Properties dialog.
nscontrol status. This command also provides more granular control over Enable, Disable, Start, and Stop.
Export to Editor
nscontrol export. The nscontrol export command always exports XML files. The Export to Editor command always exports XML to the SQL Server Management Studio XML Editor.
What Happens Where?
It is important to note that most Notification Services actions performed within SQL Server Management Studio happen on the instance of SQL Server to which you are connected, but registration commands happen on the local computer.
For example, let's say you have installed Management Studio on Client01, and a default instance of SQL Server on Server01. On Client01, you start SQL Server Management Studio and then connect to Server01. In Object Explorer, right-click Notification Services and select New Notification Services Instance. The databases or database objects are created on Server01.
However, if you right-click your new instance, point to Tasks, and then select Register, the instance is registered on Client01, not Server01. In other words, register (and unregister) are local computer operations, and create (along with enable, disable, update, upgrade, and delete) is a database operation.
Start and stop are Windows service operations, but they use instance and application metadata stored in databases to determine where each Windows service is running.
Notification Services Stored Procedures and Views
SQL Server Management Studio provides basic information about instances of Notification Services. But what if you need more information? For example, what if you want to see what event batches were recently processed, and what notification batches were generated as a result? Or what if you want to look at event, subscription, or notification data for troubleshooting purposes?
Notification Services provides stored procedures and views to help you monitor and troubleshoot instances and applications. Although it is debatable whether stored procedures and views are officially "tools," the stored procedures and views are good resources that every Notification Services developer and administrator should know about. Tables 15-4 and 15-5 list the stored procedures and views that are most useful for monitoring and troubleshooting.
Table 15-4. Notification Services Stored Procedures
Returns information about all applications hosted by the instance, including the events, notifications, and subscriptions processed duringa specified time interval.
Contains a list of failed notification delivery attempts. Each row in the report includes the notification and subscription classes that produced the notification, the subscriber to which the notification would have been sent, and information about a delivery attempt.
Returns detailed information about an event batch in an application. One resultset contains general information about the event batch, such as the event provider name and the time that the event batch was collected. A second resultset displays the events that were submitted in the event batch.
Lists the event batches submitted by an event provider to an event class.
Contains information about a specified notification batch, such as the rule firing that produced the batch and the notifications contained in the batch. Use this stored procedure to troubleshoot notification generation and to analyze the progression of notifications through the application.
Lists the notification batches generated for a notification class.
Returns information about the subscriptions that are scheduled to be evaluated within a given time period. This stored procedure works only for scheduled subscriptions.
Contains information about all scheduled subscriptions for a given subscriber.
Provides information about all applications hosted by an instance of Notification Services.
Provides information about the current state of the delivery channels configured for an instance of Notification Services.
Produces the events snapshot report. You can use this report to determine how long it has been since an event class has received events from an event provider.
Produces the event providers snapshot report, which contains information about each event provider configured for an instance.
Produces the subscriptions snapshot report, which contains information about the most recent subscription addition for all subscription classes.
Returns the query used to evaluate a user-defined subscription.
Manually removes older events, notifications, and related data from the application database.
Table 15-5. Notification Services Views
Contains the current set of events to be processed. In SQL Server 2005, this view is always available, and you can insert events into it.
Contains the current set of raw notification data, which you can use to review the notifications being processed by the application.
Lists the event batches for all event classes in the application.
Lists the Notification Services time zones.
Lists all delivery attempts for notifications of the associated notification class.
Lists subscriber and subscriber device information for the instance. You can use this view to manage subscriber devices.
List the subscribers to the instance of Notification Services. You can use this view to manage subscribers.
Lists all subscriptions for the associated subscription class. You can use this view to manage subscription data. However, if your application uses the new condition-based subscriptions or scheduled subscriptions, you cannot add subscriptions with this view.
Other stored procedures you might find yourself using to troubleshoot applications include a set of diagnostic stored procedures (NSDiagnostic*) that provide more detail than the snapshot stored procedures, and a set of quantum-related stored procedures (NSQuantum*) that provide detailed information about the generator, which produces notifications.
Using SQL Server Management Studio Projects
For each instance of Notification Services, you may find that there is a specific set of queries or stored procedures that you frequently run. To make accessing these queries and stored procedures easier, you may want to create a SQL Server Management Studio solution for Notification Services, and within that solution create a project for each Notification Services instance. Each project can contain saved queries, XML files, and other files related to that project. For more information about SQL Server Management Studio solutions, see Chapter 4.
Finally, you can develop your own tools with the Notification Services APIs. Examples of using the API are not included in this chapter, but SQL Server Books Online contains reference content and sample applications.
The NMO API
The Notification Services Management Objects (NMO) is a managed code API that enables you to define, deploy, and manage Notification Services instances and applications programmatically.
If you develop a Notification Services application with the NMO API, you can deploy that application with the NMO API. Otherwise, use the SQL Server Management Studio or NSControl tools.
However, you can manage an instance of Notification Services programmatically, no matter how you deployed the application. Table 15-6 lists the classes and methods used to manage Notification Services For more information about the classes in the NMO API, and the properties and methods that enable you to manage Notification Services, see the Microsoft.SqlServer.Management.Nmo namespace in SQL Server Books Online.
Table 15-6. NMO Equivalents to Deployment and Management Tools
Deployment and Management Methods
Create, Disable, DisableSubscribers, DisableSubscriptions, Drop, Enable, EnableSubscribers, EnableSubscriptions, Export, RegisterLocal, Repair, UnregisterLocal, Update
Disable, DisableSubscriptions, Enable, EnableSubscriptions
Distributor, Generator, and Hosted EventProvider Classes
Notification Services Core API
The core Notification Services API is in the Microsoft.SqlServer.NotificationServices namespace. This API is primarily for developing custom event providers, content formatters, delivery protocols, and subscription management interfaces. However, this API also contains the NSInstance class, which enables you to start or stop an instance of Notification Services without using the NS$instance_name Windows service.
It is important to note that either the Windows service or a custom application can host an instance of Notification Services at any one time. In other words, if you are running an instance of Notification Services using the NS$instance_name Windows service, you must use the Windows service, not NSInstance class, to stop the instance.
For more information about the NSInstance class, see the Microsoft.SqlServer.NotificationServices namespace in SQL Server Books Online.