Chapter 7. Notification Services and the Service Broker

In the last chapter, I explained how you can use a feature called Replication to send data from one system to another. That discussion was framed in regard to high availability, but of course you can use Replication for many other applications that require data interchange.

In this chapter, we examine a more targeted approach for data distribution. Using Notification Services, you can trigger specific datagrams to users based on conditions in your database, from notifying someone that a stock price has changed to alerting that a machine has just gone offline. Any type of event that you track in your database is available for use in Notification Services. Unlike Replication, you can use Notification Services to send data to an e-mail address, a cell phone, or using the Simple Messaging Service (SMS) to any device that conforms to that standard.

There are a couple of ways you can use Notification Services. You can treat it primarily as a task that the maintenance database administrator (DBA) is responsible for or you can enable it for your developers. In this chapter, I focus on the DBA side of things, but you should be aware of its many applications.

You can use SQL Server 2005 as part of a robust service-oriented architecture (SOA). An SOA allows you to build applications in a new way, distributing the load across multiple servers using messages between them. SQL Server 2005 provides the Service Broker as a programmable object that you can use as a store-and-forward mechanism. Although you might not write these programs, as the DBA you will be responsible to set up and manage this part of the framework for an SOA.

In the second part of the chapter, I explain how you can help develop and manage an SOA using the Service Broker. I show you a sample application using the Service Broker in the "Take Away" section at the end of the chapter.

In the early days of computing, users did not get immediate responses from their programs. Developers wrote code in either machine language or something close to it, saved it on a media such as magnetic tape or punch cards, and submitted it to a computer. The computer would run the program and create an output, either more punch cards or a printout. The users would receive the printouts as their results, disconnected from the computer. This is called "batch processing"; and most of us could not imagine working this way any more.

In modern applications, users connect to the database either directly or through a data-access layer in the system. They input data and receive immediate feedback using a video screen. This book was produced with a program using that computing method, and you might even be reading it that way.

But there are times when batch processing or disconnected computing is useful. In some applications, the user is not near the computer when the results are tabulated or created but may still need the data. Other systems might also need access to the data but not all at once. The feedback between the data and the user does not have to be immediate.

The value in this paradigm is that you can spread out the system over large areas and balance the loads between the components. Not needing the immediate feedback lowers the amount of resources a system requires.

You are already using at least one disconnected system in your daily routine. E-mail, for instance, is created on one computer at a certain time, stored on another computer at a later time, and picked up and read on still another computer at a later time. The e-mail server provides the reception, storage, and delivery of the message as a service. In effect, e-mail is a part of an SOA. In an SOA, a server provides interfaces and data that are available to any program that knows how to talk to the service. The service can also be coded to accept input, or perform some other action based on the connection.

SOA systems are not new; in fact, Microsoft provides many SOA mechanisms in programs such as BizTalk or as separate add-ons to SQL Server 2000. In Microsoft SQL Server 2005, Microsoft includes the Service Broker directly integrated into the database engine.

If Microsoft already has SOA systems available in other products, why include it in SQL Server 2005? The reason has to do with the requirements that an SOA has. If you are going to allow systems to be disconnected, your SOA has to guarantee that the traffic between the systems is encapsulated into messages so that the sending program is identified, so it can receive the proper answer from the service. You also have to make sure that the messages are ordered properly. For instance, the program might send the third line of a purchase order before it sends the header. The service needs to be aware of the order and the encapsulation so that it can respond to the purchase order only when it is complete, just as in a database transaction.

To manage all this, SOA systems use a database. In other mechanisms, you need to manage not only the SOA system but a complete database as well. By including the SOA within the database engine, the data, metadata, tracking data, and mechanisms are all contained within the same architecture. There is just one system to learn, implement, and manage.

Another type of SOA is a "push"-oriented data system. I explained a little about this type of data movement in the last chapter when we examined SQL Server's Replication features. In that environment, data is either sent or picked up by another database. Although that is a useful feature, you will often need to send data to a medium other than a database, such as e-mail or an SMS device. In SQL Server 2005, Microsoft includes the Notification Services system that can do just that.

Many people in your technology department are normally involved in creating an SOA, simply because of the vast array of technical skills required to implement and maintain it. As the DBA, you will be asked to manage and maintain the system, working with developers, business analysts, and others.