Mail to a Friend Receive updates via our RSS feed

An Overview of SQL Server Notification Services

Get up to speed fast with SQL Server 2000 Notification Services (version 2.0). Notification Services allows you to build high-performance notification applications that automatically notify subcribers (computers and devices) about changed data.

Today, there is more information readily available to us than ever before. The World Wide Web provides a seemingly endless supply of web sites to which we can direct our browsers and instantly retrieve a staggering amount of information about almost any subject imaginable. Think of the way you frequently use the internet today. On any given day, you likely visit the internet to search for updated device drivers for your computer, to check the starting time of that movie you have been planning to see, to see if the local team won last night, or to double check whether your child's school is closing due to inclement weather conditions. It is a great place to seek out information when you need it.

The problem is that the onus is on us, the consumers of the information. We request information and get a response. But the crux is that we have to know when to go look for information and we have to know where to find it. Many times, this is not an issue. When we are in the market for a new laptop computer, we will turn to the internet to do a little comparative shopping online.

But what about instances where the internet may have a wealth of information but we just don't know when to go look for it? For example, your car may have one or more recall notices outstanding on it. Perhaps the manufacturer has issued a recall notice because the brakes on your car won't work when going above 80 mph. That's important to know! But how do you know when to go look? As important as that is, you are pretty unlikely to visit the manufacturer's web site daily just to check for recall announcements.

There must be a better way.

What if there was an application in place that would act as an agent on our behalf? In this application, we could define our interests, explicitly selecting specific criteria that the information must meet in order for it to be considered of interest to us. Then the application could notify us whenever pertinent information becomes available. That places the burden on the technology, not on the consumers of the information.

Consider the car safety recall notice again. What if we could sign up on the vehicle manufacturer's web site to be notified of recall announcements? Of course, we do not want to be notified for recalls on every make, model, and year, so we explicitly define our interests to limit the notifications that we will receive. When a recall is issued that affects our specific vehicle, we will receive an email similar to the one in Figure 1.1.

What is SQL Server Notification Services?
Sometimes it is helpful when attempting to define something to first state what it is not. SQL Server Notification Services is not :

• A Search Engine - SQL Server Notification Services is not a search engine to help direct your users to the best places on the internet or to the appropriate page within your web site. There are plenty of good search engines out there for that. SQL Server Notification Services applications are associated with specific web sites, not search engines.
• A Content Filter - SQL Server Notification Services is not designed to help your users sift through the mountains of content that are available on the internet and cull the best and most relevant information for further perusal. It is designed to help automatically deliver specific information to your users in a timely and efficient manner.
• A Tool for Sending Unsolicited Emails - SQL Server Notification Services is designed to generate and send electronic notifications to millions of recipients in a very short period of time; but it is not intended to be a tool that enables unprincipled companies to send unsolicited bulk emails.

  

So, what exactly is SQL Server Notification Services? Simply put, it is a framework based on Microsoft SQL Server, Transact-SQL, XML, and the .NET Framework for developing and hosting notification applications. As an add-on product to Microsoft SQL Server, it is currently freely available for download from the Microsoft web site. Visit http ://www.microsoft.com/sql/ns for licensing and additional information.

Notification Applications
Notification applications are designed to provide a vehicle for communications between an organization and those interested parties who have subscribed to the application. The subscribers may either be inside the organization, such as production engineers or the sales staff, or they may be external to the organization, such as a company's customers or suppliers.

For example, consider the following scenarios where a notification application may be appreciated.

• A School Closing Application - An application where parents can ask to receive an email notifying them when their child's school has closed due to weather conditions.
• A Vehicle Maintenance Application - An application where drivers can ask to receive an email every three months reminding them to have their vehicle serviced.
• A Press Release Application - An organization can offer a service whereby members of the press can sign up to automatically receive an email every time a new press release is issued by the organization.
• A Software Update Application - An Independent Software Vendor could notify its customers when new software patches or security hot fi xes are available.
• A Real Estate Application - Realtors could offer an application to their clients that would allow prospective home buyers to automatically receive emails whenever new listings are published that meet their interests.

Terminology
SQL Server Notification Services applications have specific terminologies that warrant a bit of discussion. For the purposes of this discussion, let us consider an example to help illustrate the use of the terms. Let's continue with the vehicle safety recall notification example with a company called ABC Motorworks.

• Notification Services Instance - A Notification Services Instance is a single, named configuration of SQL Server Notification Services. Each Notification Services instance has its own registry entries and Windows service, allowing multiple Notification Services Instances to coexist on a server. Each Notification Services Instance can host one or more notification applications and may be deployed on a single server or across multiple servers. A Notification Services Instance is not the same as a SQL Server Instance.
• Notification Application - A Notification Application is a solution designed to recognize external events and match those events with subscriptions, producing and delivering formatted notifications to subscribers. In the ABC Motorworks example, a safety recall application notifies customers when manufacturer recalls are issued for the customer's vehicle. Each notification application is hosted by a Notification Services Instance.
• Events - An event is an occurrence of something from outside the notification application that contains information of interest to subscribers. Events can be in real-time, such as receiving sports scores at the end of each period, or they can be scheduled, such as receiving a digested list of all sports scores at the end of the day. In our example, an event is generated in the notification application whenever a recall is issued for the ABC Motorworks Model 6 vehicle. Event providers and events are covered in Chapters 6 and 7, respectively.
• Notifications - A notification is the actual message sent from the notification application to the subscriber when an event of interest occurs. For our example, the notification would be the email message in Figure 1.1. Chapters 9 and 10 discuss generating and distributing notifications.
• Subscribers - These are the people who are interested in the information that we offer. In our example, these are likely to be the owners of ABC Motorworks vehicles, but they could also be members of the press, dealerships, or car repair centers.
• Subscriber Devices - Subscribers are people. We need to define one or more ways to contact them electronically. Subscriber Devices define the delivery destination for subscribers to which notifications are sent. The devices can be email addresses, Short Message Service (SMS)-capable cellular telephones, or even Microsoft Windows Messenger accounts. In our example, most people would probably prefer to receive an email indicating that a safety recall has been issued for their vehicle.
• Subscriptions - When subscribers ask to be notified about a certain event, a subscription is created. A subscription associates a subscriber, subscriber device, and the information that is of interest to the subscriber. In our example, a subscription would indicate that the subscriber known as Joe Webb wants to be notified via his email address (joew@webbtechsolutions.com ) when a 2001 ABC Motorworks Model 6 has a safety recall issued. Subscription management is discussed in Chapter 8.
• NSControl - NSControl is a command utility for administering SQL Server Notification Services instances and applications.

Developing Applications
SQL Server Notification Services applications are based on a combination of commonly used Microsoft technologies and open standards. Using the .NET Framework, Microsoft SQL Server, Transact-SQL, and Extensible Markup Language (XML), developers can quickly develop, build, and test notification applications. Using internet development tools such as Web Forms in Visual Studio .NET, developers can create end-user web applications that allow subscribers to sign up for their notification application. While a complete discussion of developing ASP .NET Web Applications is beyond the scope of this book, Chapter 11 explores the Subscription Management Objects of SQL Server Notification Services - an API and set of classes that may be used to create and test an end-user Subscription Management application.

Using the SQL Server Notification Services development framework to rapidly develop a notification application involves the following steps:

• Copying a Sample Application - Notification Services comes with four sample applications. Each offers a slightly different perspective on developing a notification application and each may be used as a starting point for developing your own application.
• Creating the Core Files - A significant portion of the development activity for creating a notification application involves creating XML-based configuration files. Chapter 3 introduces the core files used to create a SQL Server Notification Services application. Configuring these files is the topic of Part II of this book.
• Building the Application - Once the core files have been created, the application must be built. Chapter 11 examines the build process and its results.
• Testing the Application - Once the application is in place, it is time to develop a simple subscription management application that can be used to test the application. Creating a test application is the subject of Chapter 12.

Why Use SQL Server Notification Services?
Many people, when they first consider SQL Server Notification Services, ponder only the superficial aspects of the services and neglect to fully evaluate all that they do and manage for them. This frequently prompts the sentiment "I can develop an application that can do everything a SQL Server Notification Services application can do. Why should I use the product?"

It is true that many developers with sufficient experience could certainly develop—from scratch—a notification application without using SQL Server Notification Services. However, doing so would very likely mean a significantly longer development cycle, higher maintenance costs, and limited extensibility. It would probably produce an application that is much less scalable than a Notification Services application.
Consider the following benefits of SQL Server Notification Services:

• Rapid Application Development - SQL Server Notification Services offers a framework to rapidly develop notification applications. It is based on technologies that many developers already know: XML, the .NET Framework, Microsoft SQL Server, and Transact-SQL.
• Jumpstart on Development - The core components of a notification application are already built into the Notification Services framework, freeing you from having to create these components yourself. If you were to develop something from scratch, you would have to intentionally handle circumstances such as multiple time zones, obsolete events, and notification retries after failed notification attempts.
• Scalable Architecture - Notification Services is designed to leverage SQL Server's inherent ability to work with sets of data rather than specific items. Since it is set-based, notification services applications scale very, very well. It is designed to handle millions of incoming events that generate millions of notifications. The architecture of Notification Services is examined in Chapter 2.
• Flexible Deployment Options - Notification Services applications are easily scaled. Since they are based on SQL Server running on Windows-based servers, Notification Services applications will be able to take full advantage of all of the memory and multi-processor support inherent in the Windows Server operating system. Additionally, Notification Services applications can be easily configured to scale out to additional servers as needed to help distribute the load. Bonus Chapter 13 discusses options for deployment.

Editions
SQL Server Notification Services is available in two editions: Standard and Enterprise. The Standard Edition of SQL Server Notification Service is designed for small to medium implementations and runs on the Standard Edition of Microsoft SQL Server. The Enterprise Edition is, of course, designed for larger implementations and works with the corresponding edition of Microsoft SQL Server. Most of the additional features included in the Enterprise Edition are designed to improve efficiency and scalability.

• Standard Edition - Includes the following features and characteristics:
  • Single Server Deployment Model
  • One Generator Thread per Instance
  • Up to three Distributor Threads per Instance
• Enterprise Edition - Includes the following features and characteristics:
  • Multiple Deployment Options
  • Up to 25 Generator Threads per Instance
  • Unlimited Number of Distributor Threads per Instance
  • Digest and Multicast Delivery Options
  • Configurable Notification Batch Sizes

Summary
Microsoft SQL Server Notification Services is an innovative development and hosting platform that provides the basis for rapidly producing highly scalable notification solutions. Since its foundation rests squarely on technologies that many application developers already know (the .NET Framework, Microsoft SQL Server, Transact-SQL, and XML), developers are not required to learn a completely new and proprietary technology before getting started. Instead, developers can leverage their existing skill set and simply learn to apply it to the Notification Services framework, making the learning curve rather small.

The rest of this book is dedicated to providing you with a good, solid overview of SQL Server Notification Services. It is designed to quickly and efficiently give you the insight you need to get started. Once you have this foundation, you can explore the intricacies of the technology in more depth.

Copyright © 2004 by Mann Publishing Group.

Oracle Magazine contains technology strategy articles, sample code, tips, Oracle and partner news, how to articles for developers and DBAs, and more. Oracle (NASDAQ: ORCL) is the world's largest enterprise software company. Subscribe Free!