Microsoft® SQL Server® 2012 Bible

648

Part V: Enterprise Data Management

FIGURE 25-1

Asynchronous Shipping Notifi cation with Service Broker.

Shipping

Manager

Shipping

Application

Shipping

Notification A

Shipping

Notification B

Shipping

Notification C Initiator Target Queue

Shipping Customer A

Notification A

Shipping

Notification B

Shipping

Notification C

Customer B

Customer C

You can also use Service Broker to pass messages with guaranteed secure delivery between

work queues, which opens a world of possibilities. Delivery of messages can occur within

the same database, between databases hosted on the same instance, or within databases

hosted on different instances. Asynchronous messaging between databases located in dif-

ferent geographical locations is extremely useful especially when they are at different time

zones and work needs to be queued up during maintenance hours.

Because Service Broker is a SQL Server table managed internally, it includes all the cool

transactional and back-up capabilities inherent to SQL Server. This is what sets Service

Broker apart from other queuing technologies, such as MSMQ.

The queue contains a single column for the message body, which is fi ne because the mes-

sage typically contains a single XML fi le or fragment or SOAP message as the payload. The

queue will have other columns of its own that it manages.

Service Broker is not enabled by default, so the fi rst step to working with Service Broker is

to turn it on using the ALTER DATABASE command:

ALTER DATABASE AdventureWorks2012 SET ENABLE_BROKER;

Each database contains a Service Broker identifi er that distinguishes it from all other data-

bases in the network. The service_broker_guid column of the sys.databases catalog

view shows the Service Broker identifi er for each database in the instance. Service Broker

systems can be designed to run multiple copies of a service. Each copy of the service runs

in a separate database. In a system that has multiple copies of a service, use the BROKER_

INSTANCE clause of the CREATE ROUTE statement to create a route to a specifi c copy of

the service.

Service Broker routing uses the Service Broker identifi er to ensure that all messages for

a conversation are delivered to the same database. The BEGIN DIALOG CONVERSATION

c25.indd 648c25.indd 648 7/31/2012 9:21:37 AM7/31/2012 9:21:37 AM

http://www.it-ebooks.info