At this point, we have merely gathered the requirements and started to envision how those requirements should be implemented. We are just at the beginning.
You decided to use Notification Services for developing this application. Notification Services will help in accelerating development of the application, and it will offer a platform for evolving the application if this is eventually needed. Therefore, you need to understand how the requirements map onto Notification Services elements, as well as the process of building, deploying, and testing a Notification Services application.
The next sections of this chapter will provide information on:
Understanding the components of a Notification Services application
Understanding how the requirements for our Birding example can be converted to Notification Services elements
Creating the development infrastructure, which consists of the application database and the development project
Creating the Notification Services application foundation
Performing the initial deployment
Progressively adding functionality to the application to complete the expected requirements
Components of a Notification Application
Now that you have some initial knowledge of the data the application will manage, you need to think about how the data processing should work. In addition to having a schema, every application needs an engine that keeps everything running and does the real work with the data. The following are expected to be implemented as part of the notification solution engine:
A SQL Server 2005 server Hosts our database (both the schema and the data) and the Notification Services components.
A subscribing application Provides the interface for end users to enter and edit their subscriptions and device information. In our example, we will not build that application. Instead, you will use a couple of scripts to enter subscribers, subscriber devices, and subscription data into the application. Sometimes this interface is not actually an interface but a trigger from other applications, such as a customer management or human resources application.
A mechanism to receive events generated in the outside world Notification Services reacts to events, but how does it become aware of an event? Notification Services includes some event providers that allow events to be sent to Notification Services. The included event providers are an Event object API (object model), the XML Loader, and the SQL Server API for loading events by means of stored procedures. You will find more information about these providers in the Notification Services documentation. In the Birdinia scenario, we will use another script to enter events directly in the appropriate places. Those events will trigger the notification generation and delivery process.
An engine to match events with subscriptions The engine itself is provided by Notification Services. However, you will need to inform Notification Services what the relation is between the events and the subscription through something known as a matching rule. A matching rule is a simple SQL statement that selects those subscriptions that should receive a specific event. In our scenario, the matching rule matches events in a specific region to subscribers holding subscriptions to that region. Thus, if an event is produced for a particular region, all subscribers holding subscriptions to that region should receive a notification. The SQL statement we need should return the list of subscriptions that correspond to a particular region.
A physical notification generation engine Once the application knows which notifications it should send to which subscriber devices, it is necessary to build the notification content with all textual information and formatting. Notification Services uses XSLT templates to generate the final notification. Notification Services functionality includes options for sending notifications in different languages (known as cultures in Notification Services terminology). If you decide not to use the multi-language functionality, Notification Services still requires passing a culture to be used during the process. This engine will need XSLT templates for HTML e-mails and for SMS text messages. You need to instruct Notification Services when to use which template.
A delivery engine for actually sending the notifications to the individual target devices Once the notification information is in place and the XSLT templates are ready, Notification Services sends the notification to the delivery server or servers that will perform the final delivery to the subscriber device. Notification Services supplies you with this engine, too. It is optimized for performance and scalability. The configuration data necessary for Notification Services to connect to the delivery servers, such as server names, IP addresses, and authentication information, must be provided by you.
In our scenario, in order to make the setup process easier, you will use the File delivery channel instead of the SMTP or SMS server. The Notification Services documentation includes information about how to set up the SMTP delivery channel. For SMS, you will need to write the code yourself or use third-party software. Although this may look overwhelming at first, remember the possibility of using Web Services-based third-party services and how easy it is to write a Web Service client with .NET.
How Notification Services Expects Its Instructions
How do you give Notification Services the information listed in the previous section?
Notification Services uses two XML files as configuration files: the Instance Configuration File (ICF) and the Application Definition File (ADF). The ADF includes most of the application-related configuration settings, but what is the ICF?
Notification Services instances
Notification Services is a component designed to serve several notification applications on the same server hardware and software. This means that you can deploy several notification applications on the same server. Notification Services knows that it is quite possible that, in such a case, you will reuse Subscriber and Subscriber Device data in more than one solution or application; therefore, it stores that information independently of specific notification solution data. The Subscriber data is stored in the instance database, while the Subscriber Device data resides in the application database. Both databases receive their names according to some internal rules, as you will see shortly.
Additionally, Notification Services will create a Windows service to keep track of all events related to the applications the instance is serving. Each instance is backed by a unique Windows service. Thus, you may think of the instance as a Windows service instance that controls several Notification Services applications.
Notification Services application
The database for the application you are trying to build will store some specific information and, although reuse is on our minds, it is clear that a stock exchange notification application, a football match result notification application, and a bird-sighting notification application will all store different sorts of data. Therefore, the schema associated with each one of those applications will differ in major or minor ways.
The purpose of Notification Services is to manage the application data on your behalf, adding tables, views, stored procedures, triggers, and any tool Notification Services needs to perform its job. As a result, Notification Services requests to receive the schema information in a way it can understand and manage by itself. That "understandable way" is actually an XML file that adheres to a specific XML schema (XSD). This file will look like a database script for creating tables, although it is written in XML.
Notification Services internal databases
Unless instructed otherwise, when deploying the configuration files to a server, the deployment process creates at least two databases: one for the instance data, and one for each application the instance is serving. The name of those databases will follow this pattern:
The instance database is named <instance name>NSMain. In our scenario, this name is SQL2005StepByStepNSMain.
The application database is named <instance name><application name>. In our scenario, this name should be SQL2005StepByStepBirding.
In SQL Server 2005, you may use any database you like to store Notification Services element items, both instance and application related. Notification Services generates and manages many SQL Server elements; thus, you should state the schema name where these items will be grouped.
Because the number of items generated is significant, you should use common databases only in specific circumstances. In our scenario, a common database is suitable because the additional information you are using may not warrant having a database of its own. Hence, you may create the application database and instruct Notification Services to expand it with its own objects instead of creating a new database.
Requirements Mapped to Notification Services Elements
Our requirements checklist contains the following items:
Mapping the Elements to Notification Services Elements
By now, you know that a Notification Services application should include the following items:
The application-specific database, including all data not strictly related to Notification Services:
The ICF, or instance configuration files
The ADF, or application definition file, including:
The matching rule
For our convenience, the development (SQL Server Management Studio) project, including:
You will notice that nothing is mentioned about Subscriber and Subscriber device schemas. Notification Services will manage them and dictates their schema. The schema provided will meet our needs. However, if this were not the case, you would have to add the additional data to the application-specific database and use the SubscriberID field in the Notification Services schema as a logical join between both sources.
The Development Infrastructure
Now let's start building our sample application. SQL Server Management Studio does not include a template for Notification Services projects, although you can create a project to host the Notification Services-related files.
Instead of starting from scratch, we will use the Tutorial project to build our Birding notification application. This application will give us the scripts we need to populate the databases and to enter some events.
Installing the SQL Server 2005 Samples
The rest of this chapter uses the Tutorial sample included as one of the Notification Services samples. Although you don't have to install the samples to follow along with the chapter content, the Notification Services samples packaged with SQL Server 2005 will help you better understand Notification Services as a whole by comparing several business scenarios and their solutions. To install the SQL Server 2005 samples, see the SQL Server Books Online topic "Installing Samples."
Additionally, you can install the samples and follow the Notification Services Tutorial topics as a complement to this chapter.
The "Notification Services Samples" section in SQL Server Books Online includes a topic called "Troubleshooting the Samples" that might help you if unexpected errors occur while following the steps detailed in the rest of this chapter.
Starting a New Project for Notification Services
Although Notification Services will handle all notification-related elements, it will not manage the application-specific data. In the Birding scenario, this means that it will not maintain the information about available Regions or Observation categories.
Similarly, Notification Services will include minimal data about subscribers themselves, holding just an ID for each subscriber. If you need to keep track of additional subscriber information, you should consider developing a standard application or using a membership solution. The ID maintained by Notification Services is the link between Notification Services and your subscriber tracking solution.
In our scenario, you will build a database to store all information not specific to Notification Services. This database is very simple, but useful as an example.
Creating the Application-Specific Database
In the Start menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.
From the File menu, select Open | File. Locate the BirdingDatabaseCreation.sql script in the My Documents\Microsoft Press\SQLAppliedTechSBS\Chapter13 folder.
Edit the database file paths to point to the folder in which you want to create the database.
Execute the script to create the database, its tables, and its contents.
Creating the Project and the SQL Server 2005 Management Studio Solution
Open SQL Server Management Studio and connect to the database server.
Select New | Project from the File menu. Choose SQL Server Scripts as the project template. This template is used because SQL Server Management Studio does not include Notification Services as a project template. Name the project SQL2005StepByStep_NS. Choose the location of your preference. Unselect the Create Directory For Solution checkbox. Click the OK button to create the project.
Using Windows Explorer, copy the following files from the My Documents\MicrosoftPress\SQLAppliedTechSBS\Chapter13 folder to the folder where you have stored the project:
In the Solution Explorer Window, right-click SQL2005StepByStep_NS and select Add | Existing Item from the context menu. In the Add Existing Item dialog box, select All Files from the Files Of Type dropdown list. Add the above two files to the project. Both of them will go to the Miscellaneous folder, as shown below.
Right-click EmptyADF.xml and select Rename from the context menu. Rename the file as BirdingADF.xml
In Windows Explorer, under the project folder, create a new folder named Notifications. This folder will contain files resulting from the notification delivery process.
In another part of the process, you will need to choose a user account to act on behalf of the Notification Services instance. This user account must have the necessary permissions to create and edit files in the destination folders.
The Notification Services Application Foundation
The application requires a few fundamental definitions before we can continue.
Defining the Schemas
The ADF includes several sections used to describe the schema of the tables that contain event, subscription, and notification data. As previously mentioned, the ADF is an XML file that must comply with a predefined XSD schema.
In Notification Services terminology, the distinct type of element to create is called a class, as in the Events class or the Subscription class. Each class will eventually include more than one table. The classes might even be split in several tables and rejoined by means of one or more views, depending on the class type.
The SQL commands that will perform matching or updating operations are called rules, as in the event rule, the event chronicle rule, or the scheduled rules.
In the following sections, you will build the ADF, including all the mentioned object schemas.
Defining the Event Class
Open the BirdingADF.xml file for editing by double-clicking it in the Solution Explorer window.
Review its content and locate the XML comments that serve as placeholders for the content to add.
The schema for the events must include the following fields: In place of the <!-- Replace with EventClasses XML --> comment, write or paste the following XML fragment:
<!-- Event Classes --> <EventClasses> <EventClass> <EventClassName>SightData</EventClassName> <Schema> <Field> <FieldName>RegionID</FieldName> <FieldType>varchar(5)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>Date</FieldName> <FieldType>datetime</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>Observation</FieldName> <FieldType>nvarchar(500)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>Category</FieldName> <FieldType>char(1)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> </Schema> <IndexSqlSchema> <SqlStatement> CREATE INDEX myIndex ON SightData ( Date ); </SqlStatement> </IndexSqlSchema> </EventClass> </EventClasses>
Note the structure of the content. It looks like a regular database schema definition, but expressed in XML syntax. You declare the Event class name rather than a table name, then the fields, the datatypes, the modifiers for null acceptance, and so on.
Additionally, to declare the fields that will form the SightData event table, you can include the information about indexes you think will be useful to apply when the matching rule accesses this table. Thus, if you know that the matching rule will use the date or the RegionID as filters by itself or in a JOIN clause, you can instruct Notification Services to create the corresponding indexes. This will be useful if event data comes in big batches; this is not the case in the Birdinia scenario and therefore indexes are not necessary. The IndexSqlSchema element is shown simply as a reminder. See Chapter 6, "Improving Query Performance," for more information about using indexes to enhance query performance.
More options exists to define schemas in the ADF. For more information about available elements, see the SQL Server Books Online topic "Application Definition File Reference."
Defining the Subscription class
Write or paste the following XML fragment below the <!-- Subscription Classes --> comment:
<!-- Subscription Classes --> <SubscriptionClasses> <SubscriptionClass> <SubscriptionClassName>SightRegionSubs</SubscriptionClassName> <Schema> <Field> <FieldName>DeviceName</FieldName> <FieldType>nvarchar(255)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>SubscriberLocale</FieldName> <FieldType>nvarchar(10)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> <Field> <FieldName>RegionID</FieldName> <FieldType>varchar(5)</FieldType> <FieldTypeMods>not null</FieldTypeMods> </Field> </Schema> <EventRules> <EventRule> <RuleName>SightRegionEventRule</RuleName> <EventClassName>SightData</EventClassName> <Action> INSERT INTO SightAlerts(SubscriberId, DeviceName, SubscriberLocale, Region, Date, Observation, Category) SELECT s.SubscriberId, s.DeviceName, s.SubscriberLocale, e.RegionID, e.Date, e.Observation, e.Category FROM SightData e, SightRegionSubs s WHERE e.RegionID = s.RegionID; </Action> </EventRule> </EventRules> </SubscriptionClass> </SubscriptionClasses>
The declaration of subscription schemas is similar to that of the Event classes in the previous section. As before, you state the Subscription class name rather than a table name, then the fields, and so on.
The subscription definition includes an important item: the event matching rule. The rule shows a name followed by the Event class to which it relates and then the T-SQL statement that constitutes the actual rule.
To understand the rule, you will need to make a small jump ahead. The purpose of the RULE statement is to insert records in the notification table, or class, that you have not yet defined. Therefore, neither the name of the destination table, nor the fields it contains are familiar to you. They will be defined in the Notification class definition below.
The RULE statement gathers the necessary columns from two origin classes: the Event and the Subscription class and, most importantly, includes the key to understanding how both classes relate. In the code above, the matching rule matches the Region ID from the Event class with that of the Subscription class. Thus, subscribers will receive notifications when a sighting is produced in a region stored in the subscriber's subscription record.
Defining the Notification Class
Write or paste the following XML fragment below the <!-- Notification Classes --> comment:
<!-- Notification Classes --> <NotificationClasses> <NotificationClass> <NotificationClassName> SightAlerts </NotificationClassName> <Schema> <Fields> <Field> <FieldName>Region</FieldName> <FieldType>nvarchar(35)</FieldType> </Field> <Field> <FieldName>Date</FieldName> <FieldType>datetime</FieldType> </Field> <Field> <FieldName>Observation</FieldName> <FieldType>nvarchar(500)</FieldType> </Field> <Field> <FieldName>Category</FieldName> <FieldType>char(1)</FieldType> </Field> </Fields> </Schema> <ContentFormatter> <ClassName>XsltFormatter</ClassName> <Arguments> <Argument> <Name>XsltBaseDirectoryPath</Name> <Value>%_AppPath_%</Value> </Argument> <Argument> <Name>XsltFileName</Name> <Value>BirdingTransform.xslt</Value> </Argument> </Arguments> </ContentFormatter> <Protocols> <Protocol> <ProtocolName>File</ProtocolName> </Protocol> </Protocols> </NotificationClass> </NotificationClasses>
Again, the content of the Notification class schema is quite straightforward, but it does include something unexpected. In this case, the schema is accompanied by a ContentFormatter element. The ContentFormatter element indicates which component will be in charge of generating the notification in its final form. Details about it will be provided later in the section titled "How Do You Build the Notification Message?"
Defining the Provider Class
Write or paste the following XML fragment over the <!Replace with Providers --> comment:
<!-- Providers XML --> <Providers> <NonHostedProvider> <ProviderName>BirdingSPEventProvider</ProviderName> </NonHostedProvider> </Providers>
This XML fragment indicates that the event will arrive through a route not managed by Notification Services itself. Click the Save button in the toolbar to save the BirdingADF.xml file, so the changes will not be lost.
Editing the Instance Configuration
The next step is to edit the ICF. This file is the starting point through which Notification Services will parse the configuration files and implement all necessary steps. Therefore, it contains some basic information along with data specific to the instance.
Open the SQL2005StepByStepICF.xml file by double-clicking it in the Solution Explorer window. You will need to modify its contents to suit your actual machine configuration.
The ICF starts by defining some parameters that will be used in various places in the file. The _DBEngineInstance_ and _ServerName_ parameters will be reused when defining the database engine and the server that will support the Notification Services instance. Unless your configuration is different, leave these values as they are.
The _InstancePath_ parameter indicates in which folder Notification Services will look for the ICFs. Edit it to point to the place where you saved the Notification Services project.
Note the InstanceName element value, which in this scenario is SQL2005StepByStep.
The SqlServerSystem is the server to be used. Note that it simply specifies the parameter defined above.
The Applications element includes the list and details of all applications depending on the instance you are currently defining.
Each Application element includes the name of the application, the directory where the corresponding ADF resides, and the name of the ADF itself. Observe that the current file is leveraging the parameters defined at the beginning of the file.
The DeliveryChannels element declares the available channels and their details. In this scenario, for the sake of simplicity, only one file channel is specified. This file channel requires an argument stating the physical path to use. Edit this path to match the location of the Notifications folder you created in step 6 under "Creating the Project and the SQL Server 2005 Management Studio Solution" procedure earlier in the chapter.
Do not forget to edit the _InstancePath_ parameter and Delivery channel FileName argument to match the file paths on your system. If you don't, the Notification Services instance will not run properly.
Click the Save button to save the ICF so the changes will not be lost.
More options are available for defining items in the ICF. For more information about available elements, see the SQL Server Books Online topic "Instance Configuration File Reference."
You must follow a specific procedure to deploy and activate a notification application. Pay attention to these mechanics because a Notification Services instance must go through several states before it is operative. You must understand these state-steps if you want to put your solution to work. The process is summarized here:
Create the instance according to the settings stored in the ICF. This step will create all infrastructure items.
Register the instance on the operating system. This step will configure the Windows service, the processing user, and so on.
Enable the instance for receiving requests and events. This is equivalent to starting the application, although enabling creates a non-processing state similar to being paused.
Start the instance, or the Windows service associated with the instance. This allows the application to receive notice of outside world events and generate and deliver notifications.
Eventually, you will perform an additional step: updating the instance. This will happen each time you need to change something in the application configuration.
Creating the Instance
To create a new Notification Services instance (solution), do the following:
Open SQL Server Management Studio and connect to your database server.
In the Object Explorer window, right-click the Notification Services node and choose New Notification Services Instance from the context menu, as shown below.
The New Notification Services Instance dialog box appears. Specify the ICF you just created by typing the full path, by pasting it, or by clicking the Browse button to point to the file.
Notification Services parses the ICF to extract the parameters in it. The parameters it finds appear in the Parameters section of the dialog box. Verify that your parameters are correct. In the following figure, the parameter values reflect a particular case; your dialog box will display different folder and computer name values.
When you click the OK button, Notification Services processes the ICF's contents. The Creating New Notification Services Instance progress box, shown below, displays each step and will indicate whether the creation was successful.
If the instance is created successfully, click the Close button to close the progress window. If it fails, review the error message and make necessary adjustments to correct for the given error. After resolving the error, repeat steps 2 through 5 above.
In SQL Server Management Studio, look at the Object Explorer window. You should notice some new elements in it, as shown below.
- A new instance under the Notification Services node, with the name SQL2005StepByStep.
- A couple of new databases under the Databases node called SQL2005StepByStepNSMain and SQL2005StepByStepBirding. If you browse their objects, you will see how many elements (tables, views, and stored procedures) Notification Services has created and is managing for you.
Registering the Instance
The following steps will generate the Windows service that waits for incoming events or requests for the Notification Services instance.
In SQL Server Management Studio, expand the Notification Services node in Object Explorer. Right-click on the SQL2005StepByStep instance and select Tasks | Register from the context menu.
In the Register Instance dialog box, shown below, select the Create Windows Service checkbox and provide the appropriate account credentials that will be used to run the service. If you leave the credentials blank, the service will run under the internal Network-Service account, whose privileges are low. Alternatively, you can edit the service directly and change the logon account to LocalSystem or another account. However, using a domain account is recommended because that way you can isolate the specific permissions you grant to the account instead of changing the permissions of built-in accounts. Once you have entered your settings, click the OK button to register the instance.
To find more information about accounts and security in Notification Services, see the SQL Server Books Online topic "Configuring Windows Accounts for an Instance of Notification Services."
Again, a progress dialog box will confirm the success or failure of the registration process. If it fails, the most likely problem is a permissions issue. Check your settings and try registering again. Once you have succeeded in registering the instance, you can verify that the Windows service exists in the Services administration tool. You will find a service named NS$SQL2005StepByStep. The Services tool can be found by selecting Control Panel | Administrative Tools | Services from the Start menu.
Although the Notification Services instance has been created, it is not yet serving requests. To have it do so, you must enable and start the instance.
Enabling the Instance
In SQL Server Management Studio, locate the instance under the Notification Services node in Object Explorer. Right-click on the instance and select Enable from the context menu.
The Enable Instance Confirmation dialog box will ask you to double-check your command. Click the Yes button to enable the instance.
Starting the Instance
Right-click on the instance name in the Object Explorer window, and select Start from the context menu.
The Start Instance Confirmation dialog box asks you to double-check your command. Click the Yes button to start the instance.
Checking the Status of an Instance
At this point, everything should be in place to run smoothly. You can look at the instance's properties to confirm that every component is enabled and/or started. To open the Instance Properties dialog box, shown below, right-click the instance and select Properties from the context menu. (Select the Windows Services page to check whether services are started.)
To test the application, you will need to fire an event and check that the appropriate notification is delivered.
Updating and Upgrading the Instance
Each time you change the content of an ADF, the live instance of the Notification Services application must be updated to reflect the changes you want to introduce.
Just as there is a process for setting up an instance to run, you must follow a set procedure for making updates. The update process is the inverse of the setup process.
Use the following steps to update your instance:
Right-click the instance within Object Explorer and select Stop from the context menu. Click the Yes button in the Stop Instance Confirmation dialog box.
Disable the instance by right-clicking the instance and selecting Disable from the context menu. Click the Yes button in the Disable Instance Confirmation dialog box.
Launch the update process by right-clicking the instance and selecting Tasks | Update from the context menu
The Update Instance dialog box opens. Point to the ICF, just as you did when setting the instance for the first time. Then click the OK button.
A new progress dialog box will appear, as shown below.
The first step in this process consists of comparing the updated ADF against the existing instance and application data in order to find out which changes should be performed. After examining the files, the process presents its conclusions to you in the Update Summary dialog box, shown on the following page.
If correct, accept the proposed changes by clicking the Update button. The process tries to apply the changes to the databases. Eventually, the process will either succeed or stop if an error arises. In the following figure, you can see the Updating Instance dialog box showing an error. You can click on the message to see the full details or hover over it to look at a tip about the error content.
If the process fails due to an error, fix it and try the process again. When it succeeds, restart the instance by enabling and starting the instance again.