Microsoft SQL Server 2005 Developer’s Guide- P7

Chia sẻ: Cong Thanh | Ngày: | Loại File: PDF | Số trang:20

Thêm vào BST

Báo xấu

70
lượt xem 15
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Microsoft SQL Server 2005 Developer’s Guide- P7:This book is the successor to the SQL Server 2000 Developer’s Guide, which was extremely successful thanks to all of the supportive SQL Server developers who bought that edition of the book. Our first thanks go to all of the people who encouraged us to write another book about Microsoft’s incredible new relational database server: SQL Server 2005.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Microsoft SQL Server 2005 Developer’s Guide- P7

Chapter 4: SQL Server Service Broker 119 the SQL Server Service Broker subsystem to enable functionality in several other areas of SQL Server 2005, including Notification Services, Reporting Services, and asynchronous query notifications. The SQL Server Service Broker is completely integrated with the SQL Server 2005 engine and is fully transactional. Transactions can incorporate queued events and can be both committed and rolled back. In addition, the new SQL Server Service Broker also supports reliable delivery of messages to remote queues. This means that information sent via SQL Server Service Broker can span multiple SQL Server systems and still provide guaranteed in-order, one-time-only message delivery— even to remote queues that must be reached across multiple routing steps. The SQL Server Service Broker will take care of the mechanics required to break the large messages into smaller chunks that are sent across the network and then reassemble them at the other end. You can see an overview of the SQL Server Service Broker architecture in Figure 4-1. Messages Messages are the core bits of information that are sent by a SQL Server Service Broker application. These messages can be text messages or consist of binary data or XML. For XML messages, SQL Server can validate that the messages are well formed and that they comply with a predefined schema. You create a SQL Server Service Broker message by running the CREATE MESSAGE TYPE command, which is where you specify the type of content that the message will have. The messages that are sent across the queues can be very large—up to 2GB. SQL Server Database SQL Server Database Service Service Dialog Queue Queue Message Message Application Application Figure 4-1 SQL Service Broker Architecture
120 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Queues SQL Server Service Broker queues contain a collection of related messages. Each queue is associated with a service. When a SQL Server Service Broker application sends a message, that message must first be placed in a queue. Likewise, when that message is received by the target system, it is received into a queue. Messages are validated when they are received by the target queue. If a message is not valid, then the service returns an error to the sender. Then the application can read the queue and process the message. You create a SQL Server Service Broker queue by running the CREATE QUEUE command. Contracts Contracts essentially define which messages can be used by a given queue. In order to be processed, a contract must first be created between a SQL Server Service Broker message and a queue or, more specifically, the queue’s service. The contract provides information to the service about the type of messages it will process. The contract also prevents errant messages from being sent to and used by an unintended target application. You create a SQL Server Service Broker message by running the CREATE CONTRACT command. Services A SQL Server Service Broker service is a specific Service Broker task or set of tasks. Each queue has an associated service. Conversations occur between services. The contracts associated with the service define the specific messages that will be processed by the service. Dialogs Dialogs are an essential component of Microsoft’s new SQL Server Service Broker. Essentially, dialogs provide two-way messaging between two SQL Server Service Broker services. Dialogs can be used for interserver communications for services running on different servers or instances, or they can be used for intraserver communications linking two applications running on the same server. Figure 4-2 illustrates the SQL Server Services Broker’s dialog. The main purpose of a SQL Server Service Broker dialog is to provide an ordered message delivery. In other words, dialogs enable queued messages to always be read in the same order that they are put into the queue. SQL Server Service Broker
Chapter 4: SQL Server Service Broker 121 Service Service Queue Dialog Queue Figure 4-2 SQL Service Broker dialog dialogs maintain reliable event ordering across servers even if network, application, or other failures temporarily disrupt the communications between dialog endpoints. When the communications are restored, the events will continue to be processed in order from the point of the last processed queued entry. Dialogs can be set up to process messages in either full-duplex mode or half-duplex mode. Message Transport The SQL Server Service Broker message transport protocol enables messages to be sent across the network. It is based on TCP/IP, and the overall architecture of the SQL Server Service Broker message transport is a bit like the architecture used by TCP/IP and FTP. By default the SQL Service Broker uses TCP/IP port 4022. The SQL Server Service Broker message transport is composed of two protocols: the Adjacent Broker Protocol, which is a lower-level protocol like TCP, and the Dialog Protocol, which is a higher-level protocol like FTP that rides on top of the lower- level Adjacent Broker Protocol. Adjacent Broker Protocol The Adjacent Broker Protocol is a highly efficient low- level TCP/IP protocol that provides the basic message transport. It is a bidirectional and multiplexed protocol and so can handle the message transport for multiple SQL Server Service Broker dialogs. It doesn’t worry about message order or confirming message delivery. That’s all handled by the Dialog Protocol. Instead, the Adjacent Broker Protocol simply sends messages across the network as quickly as it can. Dialog Protocol The Dialog Protocol is a higher-level protocol that utilizes the services of the Adjacent Broker Protocol to handle end-to-end communications for a SQL Server Service Broker dialog. It is designed to provide one-time-only, in- order delivery of messages, handling the sending and acknowledgment of messages. It also provides symmetric failure handling where both end nodes are notified of any message delivery failures. In addition, the Dialog Protocol is responsible for authentication and encryption of messages.
122 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Developing SQL Service Broker Applications As you saw in the first part of this chapter, the SQL Server Service Broker is a subsystem that enables the development of asynchronous database-oriented messaging applications. The first part of this chapter provided you with an overview of the primary components of the SQL Service Broker subsystem and gave you an idea of the functions and interactions of those components. This section will present the new T-SQL commands that you can employ to create and use SQL Server Service Broker objects; it will then present a sample SQL Server Service Broker application. SQL Server Service Broker DDL and DML SQL Server 2005 utilizes a new set of T-SQL commands to describe the database objects used in a Service Broker application as well as new commands that enable you to access those objects in your applications. T-SQL DDL T-SQL has been enhanced with several new statements that enable the native integration of SQL Server Service Broker messaging with traditional database procedures. Table 4-1 summarizes the new T-SQL DDL statements that are used to create SQL Server Service Broker objects. T-SQL DML In addition to the new T-SQL DDL statements that are used to create the new SQL Server Service Broker objects, there are also a group of new T-SQL statements that enable your applications to set up conversations and work with the messages in a SQL Server Service Broker application. Table 4-2 lists the new SQL Server Service Broker–related T-SQL DML statements. Enabling SQL Server Broker Before you can begin to build SQL Server Service Broker applications, you must first enable the SQL Server Service Broker subsystem. Like the new SQL Server 2005 CLR support, to enhance out-of-the-box security, SQL Server 2005 ships with
Chapter 4: SQL Server Service Broker 123 T-SQL DDL Description CREATE MESSAGE TYPE Creates a new message type. Message types can be text, binary, or XML. CREATE CONTRACT Creates a new contract associating a message type and service. CREATE QUEUE Creates a new queue in a database. CREATE ROUTE Creates a new route in a database. CREATE SERVICE Creates a new service in a database. ALTER MESSAGE TYPE Changes a message type. ALTER CONTRACT Changes a contract. ALTER QUEUE Changes a queue. ALTER ROUTE Changes a route. ALTER SERVICE Changes a service. DROP MESSAGE TYPE Deletes a message type from a database. DROP CONTRACT Deletes a contract from a database. DROP QUEUE Deletes a queue from a database. DROP ROUTE Deletes a route from a database. DROP SERVICE Deletes a service from a database. Table 4-1 The New T-SQL DDL Statements Used to Create SQL Server Service Broker Objects T-SQL DML Description BEGIN DIALOG CONVERSATION Opens a new dialog between two endpoints. END CONVERSATION Ends a conversation used by a dialog. MOVE CONVERSATION Moves a conversation to a new dialog. GET CONVERSATION GROUP Retrieves a conversation group identifier for the next message to be received. RECEIVE Receives a message from a queue. SEND Sends a message to a queue. BEGIN DIALOG TIMER Opens a timed dialog. A message is placed on the dialog when the timer expires. Table 4-2 The New SQL Server Service Broker–Related T-SQL DML Statements
124 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e the SQL Server Service Broker disabled. The following code illustrates how to enable the SQL Server Service Broker for the AdventureWorks database: IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'AdventureWorks' AND is_broker_enabled = 1) BEGIN ALTER DATABASE AdventureWorks SET ENABLE_BROKER ; END ; This checks the is_broker_enabled property of the AdventureWorks database. If the is_broker_enabled value is not 1—if, in other words, Service Broker is not enabled—then the ALTER DATABASE SET ENABLE BROKER command is used to enable the Service Broker. This command sets the is_broker_enabled value to 1. As you might have noticed, the SQL Server Service Broker is enabled on a per- database basis. Using Queues While the idea of queuing in applications may be a bit foreign to most relational database designers, queues are common in highly scalable applications. Among the most well known of these types of applications are the airline reservation systems used by all major airlines like United, Delta, and American, as well as online travel brokers like Expedia and CheapTickets.com. To get an idea of how queuing is used in one of these applications, you can refer to Figure 4-3, where you can see the design of a sample queued application. Figure 4-3 presents a high-level overview of an example airline reservation system. Here you can see that the application’s presentation layer is delivered to the end user’s browser by an application running on a web farm. That application could be written using ASP.NET or some other web development language. The front-end application will then interact with the actual reservation system, which is normally running on another computer system. Because applications like these must support thousands of simultaneous users, they can’t afford to lock rows while a given user waits to decide on the final details of a flight or even starts a reservation and then goes to lunch, planning to finish later. Row locking in this type of scenario would seriously inhibit the application’s scalability and even the application’s usability. Queuing solves this problem by enabling the application to make an asynchronous request for a reservation, sending the request to the back-end reservation system
Chapter 4: SQL Server Service Broker 125 Back-End Reservation Web Front End System Database Reservation Request Reservation Queue Web Application Batch Update Process Reservation Database Reservation Response Queue Batch Confirmation Reply Process Figure 4-3 Queued Application Design and immediately freeing the front-end application for other work. At no point in the process of placing the reservation have any locks been placed on the database tables. The back-end reservation system, which is essentially operating in batch mode, will take the reservation request off the queue and then perform the update to the database. Since the update is being done in a batch-style mode, it happens very quickly with no user interaction, and minimal time is needed to lock rows while the update is performed. If the request is successful, the end user’s reservation is confirmed. Otherwise, if the request is denied because all seats were booked or for some other reason, then the reservation will not be accepted and the user will be contacted with the status. Sample SQL Server Service Broker Application This section will now dive into the code and show you how to create a sample SQL Server Service Broker application. First you’ll see how to create the required SQL Server Service Broker objects, and then you’ll see how to use those objects. The sample application is a simple messaging system that places a simple XML message on an input queue and then reads that message off the queue.
126 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Creating the SQL Server Service Broker Objects The code that’s used to create the required SQL Server Service Broker objects is shown in the following listing: -- Create the XML SBSampleMessage message type CREATE MESSAGE TYPE SBSampleMessage VALIDATION = WELL_FORMED_XML ; GO -- Create the SBSampleContract contract CREATE CONTRACT SBSampleContract ( SBSampleMessage SENT BY INITIATOR); GO -- Create the queue CREATE QUEUE [dbo].[ReceiverQueue]; GO -- Create the queue for the Sender service CREATE QUEUE [dbo].[SenderQueue]; GO -- Create the service CREATE SERVICE SenderService ON QUEUE [dbo].[SenderQueue]; GO -- Create the target service CREATE SERVICE ReceiverService ON QUEUE [dbo].[ReceiverQueue] (SBSampleContract); GO The first step to creating a SQL Server Service Broker application is the creation of the required message types, which describe the messages that will be sent. The first statement shows the creation of the message type named SBSampleMessage. The VALIDATION keyword indicates that this message will be an XML message, and SQL Server will check to make sure the XML is well formed. Next, a contract is created. The contract describes all of the messages that can be received using a particular service. The first argument is used to name the contract. Here the contract is named SBSampleContract. The SENT BY clause specifies
Chapter 4: SQL Server Service Broker 127 which endpoint can send a message of the indicated message type. INITIATOR indicates that only the initiator of the conversation can send messages of the SBSampleMessage type. Then the queues must be created. This example shows the creation of two queues: the ReceiverQueue and the SenderQueue. As their names suggest, the SenderQueue will be used to send messages and ReceiverQueue will be used to receive messages of the SBSampleMessage type. After the queues are created, you can display the contents of the queues by using the SELECT statement exactly as if the queue were a standard database table. The following line of code shows how you can display the contents of the Request queue: SELECT * FROM ReceiverQueue At this point, however, since there are no messages in the queues, the result set will be empty. However, running SELECT statements on the queue is a great way to check out functionality of the SQL Server Service Broker applications you are developing. After the queues have been created, the next step is to create the services for the queues using the CREATE SERVICE statement. The first parameter names the service. The ON QUEUE clause identifies the queue associated with the service, and then the contracts that are associated with the service are listed. In the preceding listing, you can see two services being created: the SenderService and the ReceiverService. The SenderService handles messages in the SenderQueue, while the ReceiverService handles messages in the ReceiverQueue. If one of the services were located on a remote system, you would also need to create a route. The CREATE ROUTE statement supplies the SQL Server Service Broker with the system address where the remote service is found. In this case, since both services reside on the same system, no route is needed. Sending Messages to a Queue After the necessary SQL Service Broker objects have been created, you’re ready to use them in your queuing applications. The following code listing shows how you can add a message to the ResRequestQueue queue: USE AdventureWorks ; GO -- Begin a transaction BEGIN TRANSACTION ; GO
128 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e -- Declare a variable for the message DECLARE @SBmessage XML ; SET @SBmessage = N'Service Broker is Cool' ; -- Declare a variable for the conversation ID DECLARE @conversationID UNIQUEIDENTIFIER ; -- Begin a dialog between the services BEGIN DIALOG CONVERSATION @conversationID FROM SERVICE SenderService TO SERVICE 'ReceiverService' ON CONTRACT SBSampleContract ; -- Put the message on the queue SEND ON CONVERSATION @conversationID MESSAGE TYPE SBSampleMessage (@SBmessage) ; -- End the conversation END CONVERSATION @conversationID ; GO -- Commit the transaction to send the message COMMIT TRANSACTION ; GO At the start of this listing you can see where a transaction is started. Using transactions enables all of the actions that are performed by the SQL Server Service Broker to commit and, optionally, to roll back any changes that are made within the context of the transaction. Next, a variable named SBMessage is declared that contains the message that will be sent by SQL Service Broker. Then the conversationID variable is created that contains a unique identifier that will be used by a SQL Server Service Broker dialog. Then the BEGIN DIALOG COVERSATION statement is used to open up a new conversation. When you declare a dialog, you always need to specify two endpoints. The FROM SERVICE identifies the sender of the messages, while the TO SERVICE keyword identifies the target endpoint. Here, the sender is named SenderService and the target is named ReceiverService. While this example uses simple names, Microsoft BOL recommends that you use a URL name to uniquely identify the SQL Server Service Broker objects. For example, to ensure uniqueness in
Chapter 4: SQL Server Service Broker 129 the network, they recommend using names like [//AdventureWorks.com/MySample/ SenderService]. The ON CONTRACT keyword specifies the contract that’s used for the dialog. The Contract specifies the contract that will be used. Then a SEND operation is executed to send a message on the conservation that was started. Finally, the transaction is committed. The target service will receive the message and add it to the queue that is associated with that service. At this point you can see the message on the ReceiverQueue by running the following SELECT command: USE AdventureWorks ; GO SELECT * FROM ReceiverQueue This shows two entries in the ReceiverQueue. The first entry on the queue is for the message that was placed on the queue by the sample application, and the second entry was created by the END CONVERSATION command. A partial view of the result set showing the contents of the ReceiverQueue is shown here: status priority queuing_order conversation_group_id 1 0 0 82C5F460-3305-DA11-8D17-005056C00008 1 0 1 82C5F460-3305-DA11-8D17-005056C00008 (2 row(s) affected) In order to see the contents of the message, you need to cast the contents of the message_body column in the results set to a varchar, as is shown in the following listing: USE AdventureWorks ; GO SELECT CAST(message_body as nvarchar(MAX)) from ReceiverQueue The result set showing the contents of the message is listed here: Service Broker is Cool NULL (2 row(s) affected)
130 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Retrieving Messages from a Queue Now that you’ve seen how to add a message to a queue, the next example will illustrate how to retrieve the messages off the queue. You can see the T-SQL code in the following listing: use Adventureworks GO DECLARE @conversationID UNIQUEIDENTIFIER DECLARE @message_type_id int DECLARE @message_body NVARCHAR(1000) DECLARE @message NVARCHAR(1000) while(1=1) BEGIN BEGIN TRANSACTION WAITFOR (RECEIVE top(1) @message_type_id = message_type_id, @message_body = message_body, @conversationID = conversation_handle FROM ReceiverQueue), TIMEOUT 200; IF @@ROWCOUNT = 0 OR @@ERROR 0 BREAK; IF @message_type_id =2 BEGIN Print 'Conversation Ended' END CONVERSATION @conversationID ; END ; SELECT @message = 'Received: ' + @message_body; PRINT CONVERT(nvarchar(100), @message) COMMIT TRANSACTION END COMMIT TRANSACTION A variable that will contain the receiver dialog identification is declared at the top of this listing, followed by three variables that will be used to pull back information from the queue that’s being read. Then a loop is initiated to read all of the entries
Chapter 4: SQL Server Service Broker 131 from the queue. Within the loop a transaction is started and the RECEIVE statement is used to receive a message. In this example, the TOP(1) clause is used to limit the procedure to receiving only a single message at a time. If the TOP clause were omitted, you could receive all of the messages that were present on the queue. The RECEIVE statement populates the three variables. The message_type_id identifies the type of message, which is typically either a user-defined message or an EndDialog message. The @message_body variable contains the contents of the actual message, while the @ReceiverQueue variable contains a handle that identifies the sending dialog. Then the result set is checked to ensure that a message was actually received. If no rows were received or an error is encountered, then the procedure is ended. Otherwise, the contents will be processed. If the message_type_id is a 2 (meaning the message was an EndDialog message), then the dialog conversation is stopped. Otherwise, the Select statement is used to access the message contents. The received message is concatenated with the string “Received:”, the message is printed, and the transaction is committed. You can see the sample text results in the following listing: (1 row(s) affected) Received: Service Broker is Cool (1 row(s) affected) Conversation Ended (0 row(s) affected) SQL Server Service Broker Activation SQL Server Service Broker activation is another unique feature of the SQL Server Service Broker subsystem. Activation enables you to create a stored procedure that is associated with a given input queue. The purpose of the stored procedure is to automatically process messages from that queue. As each new message comes in, the associated stored procedure is automatically executed to handle the incoming messages. If the stored procedure encounters an error, it can throw an exception and be automatically recycled. Periodically, the SQL Server Service Broker checks the status of the input queue to find out if the stored procedure is keeping up with the incoming messages on the input queue. If the SQL Server Service Broker determines that there are waiting messages
132 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e on the queue, then it will automatically start up another instance of the queue reader to process the additional messages. This process of automatically starting additional queue readers can continue until the preset MAX_QUEUE_READERS value is reached. Likewise, when the SQL Server Service Broker determines that there are no remaining messages on the queue, it will begin to automatically reduce the number of active queue readers. SQL Server Service Broker queues don’t necessarily need to be associated with just stored procedures. Messages that require more complex processing can also be associated with external middle-tier procedures. Since these middle-tier processes are external to the database, they need to be activated differently. To enable the automatic activation of external processes, the SQL Server Service Broker also supports firing a SQL Server event. These events can be subscribed to using WMI (Windows Management Instrumentation). Dialog Security When dialogs are created, they can optionally be secured using the WITH ENCRYPTION clause. When a dialog is created using the WITH ENCRYPTION clause, a session key is created that’s used to encrypt the messages sent using the dialog. One important point about dialog security is the fact that it is an end-to- end security. In other words, the message is encrypted when it is first sent from a dialog, and it is not decrypted until the message reaches its endpoint. The message contents remain encrypted as the message is forwarded across any intermediate hops. To implement dialog security, the SQL Service Broker uses certificate-based authentication, where the certificate of the sending user is sent along with the message. Because of the asynchronous nature of SQL Service Broker, the security information is stored in the message headers and retrieved by the receiving service when the message is retrieved. This enables SQL Service Broker applications to avoid the need to establish a connection to authenticate messages. System Views SQL Server 2005 supplies several new system views that enable you to retrieve information about SQL Service Broker objects and its current status. Table 4-3 lists the new system views.
Chapter 4: SQL Server Service Broker 133 System View Description sys.service_message_types Lists all the message types that have been created. System message types are listed at the top, while user-defined message types are listed at the end of the display. sys.service_contracts Lists all of the contracts that have been created. sys.service_contract_message_usages Lists the relationships between contracts and message types. Relationships can be one-to-one or one-to-many. sys.services Lists the created services. sys.service_contract_usages Lists the relationships between contracts and services. Relationships can be one-to-one or one-to-many. sys.service_instances Lists the services that are active at the current time. sys.conversation_endpoints Lists the conversation endpoints that are currently active. sys.routes Lists the created routes. sys.remote_service_bindings Lists the relationship of the services and the users that will execute them. sys.transmission_queue Lists all of the messages that are queued to be sent. sys.service_queues Lists the queues that have been created. Table 4-3 SQL Server 2005 New System Views Summary SQL Server Service Broker is an all new subsystem that enables you to create highly scalable asynchronous applications. In this chapter you learned about the new SQL Server Service Broker architecture and you saw how to create and use the objects that make up a SQL Server Service Broker application.
This page intentionally left blank
CHAPTER Developing with 5 Notification Services IN THIS CHAPTER Notification Services Overview Developing Notification Services Applications Notification Services Application Sample Updating Notification Services Applications Building a .NET Subscription/Event Application 135 Copyright © 2006 by The McGraw-Hill Companies. Click here for terms of use.
136 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e N otification Services is a new subsystem that Microsoft has added to SQL Server 2005. First introduced as a Web download for SQL Server 2000, Notification Services provides a framework that enables you to develop custom notification applications that monitor for specific data events and then push customized notification information concerning those events to multiple subscribers and devices. Notification Services is used in a number of well-known scenarios. Microsoft’s MSN Messenger uses Notification Services to alert your cell phone of traffic. NASDAQ’s Nasdaq.com site and The New York Times’ NYTimes.com are two other high-profile Notification Services users. The Nasdaq.com site allows subscribers to receive personalized notifications about changes in financial data. Here subscribers can ask to be alerted about specific changes in market prices. The NYTimes.com site uses Notification Services to push new real estate listings in the East Coast market to subscribers. In this scenario, renters or buyers specify the property characteristics that they are interested in, and they receive notifications whenever a property matching their criteria is listed in The New York Times real estate classified section. In this chapter you’ll learn how to create a Notification Services application. In the first part of this chapter you’ll get an overview showing you how the new subsystem works. In the second part of the chapter you’ll see how to build a Notification Services application. Later in the chapter you’ll learn how to update a Notification Services application, as well as how to build a .NET subscription/event application. Notification Services Overview A Notification Services application is a software layer that sits between an information source and the intended recipient of that information. The Notification Services application monitors certain predefined events and can intelligently filter and route the information about those events to a variety of different target devices using a personalized delivery schedule. Notification Services applications consist of three basic components: events, subscriptions, and notifications. Figure 5-1 provides a very high-level overview of a Notification Services application. Events In a Notification Services application, events are just what they sound like—things happening that you want to be informed about. In the case of the NASDAQ, an event might be a given stock price rising to a certain level. In a typical database application
Chapter 5: Developing with Notification Services 137 Notification Services Application Subscribers Events Subscriber Notifications Data Devices External Changes Subscriptions Delivery Event provider Generator Distributor File System X SMTP Watcher XSLT S Event SQL CF L File SQL Server Provider Match T Provider Host Rule HTTP Custom CF Custom DP Custom EP Figure 5-1 Notification Services overview an event could be associated with the value of a given column. Here the event would be fired if the column’s value passed a certain predefined threshold. Event Providers A Notification Services application monitors for events using an event provider. There are three types of Notification Services event providers: hosted, non-hosted, and standard event providers. Hosted Providers Hosted event providers are directly executed by Notification Services. When Notification Services starts, it automatically initializes and runs enabled hosted event providers. Non-Hosted Providers Non-hosted event providers are external applications that do not run within the Notification Services process. Non-hosted event providers post event data to a Notification Services application using the EventCollector class; the EventLoader class; or the NseventBeginBatch, NSEventWrite, or NSEventFlushBatch stored procedures.
138 M i c r o s o f t S Q L S e r v e r 2 0 0 5 D e v e l o p e r ’s G u i d e Standard Providers SQL Server 2005 ships with a base set of standard event providers that you can readily use to build Notification Services applications. Notification Services provides the following event providers: File System Watcher The File System Watcher event provider monitors the ﬁle system and is triggered when a ﬁle is added to the monitored directory. It reads the directory contents into memory and then writes event information to the event table. SQL Server The SQL Server event provider uses a T-SQL query to specify database data that will be monitored. It then uses Notiﬁcation Services– provided stored procedures to create events based on this new or updated data and then write these events to the event table. Analysis Services The Analysis Services event provider uses a static or dynamic MDX query to gather data from an Analysis Services cube and submit the data as events to an application. Subscriptions Subscriptions correlate users and the types of events that they are interested in. For example, with the NASDAQ example, a user might create a subscription to get a notification when a given stock price drops below $50 per share. SQL Server 2005’s Notification Services stores subscriptions, like events, as rows in a table. Notiﬁcations The notification is essentially a message that will be sent to the end user that contains the information regarding the event that the user subscribed to. Notifications can be delivered in various formats to a variety of different target devices, including XML, HTML, e-mail, WAP, and other formats. Notification Engine The Notification Services engine receives external events from the event provider and looks for matches between events and registered subscriptions. When an event matches a subscription, the Notification Services engine sends a notification to the end user. The scalability of a Notification Services application depends in a large part on how well the Notification Services engine matches events to subscriptions. Microsoft has designed the underlying Notification Services framework to be scalable at an Internet level, meaning that with the appropriate platform, SQL Server 2005’s Notification Services can scale upward to handle millions of events, subscriptions, and notifications. To do that, Notification Services takes advantage of SQL Server