Module 4: Using Service Broker

(1)

Contents

Overview 1 Lesson: Service Broker Architecture 2

Lesson: Using Service Broker 15 Lab 4: Using Service Broker 35

Module 4: Using

Service Broker

(2)

domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. The names of manufacturers, products, or URLs are provided for informational purposes only and Microsoft makes no representations and warranties, either expressed, implied, or statutory, regarding these manufacturers or the use of the products with any Microsoft technologies. The inclusion of a manufacturer or product does not imply endorsement of Microsoft of the manufacturer or product. Links are provided to third party sites. Such sites are not under the control of Microsoft and Microsoft is not responsible for the contents of any linked site or any link contained in a linked site, or any changes or updates to such sites. Microsoft is not responsible for webcasting or any other form of transmission received from any linked site. Microsoft is providing these links to you only as a convenience, and the inclusion of any link does not imply endorsement of Microsoft of the site or the products contained therein.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.

Microsoft, JScript, Outlook, PowerPoint, Visual Basic, Visual C#, Visual FoxPro, Visual SourceSafe, Visual Studio, Windows, Windows Media, Windows NT, and Windows Server are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.

The names of actual companies and products mentioned herein may be the trademarks of their respective owners.

(3)

Instructor Notes

Service-oriented architecture (SOA) has become an important concept in the design of large-scale distributed applications. Central to SOA is the ability of services to communicate using a reliable message-based mechanism. This module introduces Microsoft® SQL Server™_{2005 Service Broker, a} message-based platform for building service-oriented database solutions.

After completing this module, students will be able to: ! Describe the Service Broker architecture.

! Use Service Broker for message-based communication. To teach this module, you need the following materials: ! Microsoft Office PowerPoint® file 2734B_04.ppt To prepare for this module:

! Read all the materials for this module. ! Complete the lab.

! Practice the demonstration.

This course has been revised to reflect changes in SQL Server 2005 December 2004 Community Technical Preview build 981.04 (IDW11). Significant changes in this revision are indicated in the instructor version of the materials by underlined text (for new and updated content) and bold

paragraphs (to note deleted content). A full list of changes can be found in the change log provided with this instructor workbook. Revisions are not shown in the student version of the course workbook.

Presentation: 60 minutes Lab: 30 minutes Required materials Preparation tasks Tip

(4)

How to Teach This Module

This section contains information that will help you to teach this module.

Lesson: Service Broker Architecture

This section describes the instructional methods for teaching this lesson. This topic introduces the concept of a service-oriented architecture (SOA). Some students may be familiar with this type of architecture, particularly if they have worked with Web services or message queuing. Ensure that students understand that the main point about SOA is the loose-coupling between autonomous services, and that the terminology introduced in this topic applies to SOA in general, regardless of any specific implementation.

This topic contains a build slide that animates each bullet point. Use this topic to highlight the powerful features that Service Broker provides, such as asynchronous messaging. Emphasize that Service Broker provides these features so that developers can create relatively simple stored procedures that are not concerned with creating the low-level infrastructure.

This topic introduces the main SQL Server objects that students will use when they create Service Broker applications. Use this topic to give the students an overview of what each object does, but do not go into detail about the syntax for creating objects as this will be described later in the module.

This topic introduces additional Service Broker constructs. The Dialog construct plays a key role in all Service Broker communication, so emphasize that the order in which the messages are processed may be significant to the service program. The demonstration and lab exercises use a single instance of SQL Server, but point out to the students that routes are necessary in many applications.

This topic contains a build slide that animates a Service Broker conversation. The example has been kept as simple as possible to get the basic steps across to students. However, emphasize that some Service Broker conversations would require several messages being sent from both services.

This module is intended to introduce students to Service Broker, and a full discussion of Service Broker security is out of scope. Use this topic to make the points that there are two main aspects of Service Broker security to consider (transport security and dialog security), and that authentication and encryption across network connections relies on digital certificates, which can be created by executing the CREATE CERTIFICATE Transact-SQL statement.

Refer students to SQL Server Books Online for more information about Service Broker security.

What Is Service-Oriented Architecture?

Service Broker Features

Service Broker System Architecture Service Broker Conversation Architecture Service Broker Conversation Process

Service Broker Security Architecture

(5)

Lesson: Using Service Broker

This section describes the instructional methods for teaching this lesson. Point out that Service broker is enabled by default in new databases.

This topic lists the different parts of the Service Broker system that students will need to develop. The following topics contain the detail for each item. A demonstration follows at the end of the lesson that covers all aspects of the necessary objects. You can use each section of the demonstration as you discuss each topic.

This topic contains a build slide that animates each step. Describe the examples shown on the slide for the message types and contract. Emphasize that using an XML schema would be the best choice if XML messages might contain invalid data. Also, point out that each message and contract must exist in both the service consumer and service provider databases.

This topic introduces the syntax for creating queues. Emphasize that a queue does not need activation parameters, but if none are provided, no action will occur when the queue receives a message. This requires some form of manual processing from the queue. Discuss what happens to messages if the queue status is set to unavailable (the messages are held until the queue is available). Ensure that you describe each part of the queue syntax because students will have to alter queues in the lab exercises. At least one queue must be created in the service consumer and one in the service provider.

This topic introduces the syntax for creating services. Emphasize that services must exist for both the service consumer and service provider. However, only the service provider must include at least one contract because it will be a target service for the consumer.

This topic contains a build slide that animates each step. Point out to students that this is a simple example of a send operation and that examples that are more complex can include the reusing of conversation handles and the use of transactions.

This topic contains a build slide that animates each step. Point out to students that this is a simple example of a receive operation and that examples that are more complex can include the sending of a response to the initiator and the use of transactions. Explain to students that as part of checking the message type, three preexisting message types are defined within SQL Server as listed in the student notes.

Use the instructions to perform a Service Broker demonstration. Spend as much time as necessary to ensure that students understand what is occurring.

The student lab immediately follows the demonstration. After completing this demonstration, you should execute the

RestartServices.bat batch file on the desktop to minimize the risk of memory leak issues from the prerelease products used in this course.

How to Enable Service Broker in a Database Implementing Services

How to Create Contracts

How to Create Queues

How to Create Services

How to Send a Message

How to Receive a Message

Demonstration: Creating a Service Broker Application

(6)

Lab: Using Service Broker

Warn students that due to the prerelease nature of the software used in this lab, unexpected results can occur if the instructions are not followed exactly.

In Exercise 1 of this lab, students will create several Service Broker objects, such as message types, contracts, queues, and services.

In Exercise 2 of this lab, students will create three stored procedures. The first will act as the client application (or service consumer). The second will act as the service provider, and the third will receive any responses from the service provider.

In Exercise 3 of this lab, students will test the application using a combination of manual and automatic processing.

(7)

Customization Information

This section identifies the lab setup requirements for a module and the configuration changes that occur on student computers during the labs. This information is provided to assist you in replicating or customizing Microsoft Official Curriculum (MOC) courseware.

The lab in this module is also dependent on the classroom configuration that is specified in the Virtual PC Classroom Setup Guide for

Course 2734B, Updating Your Database Development Skills to Microsoft

SQL Server 2005.

Lab Setup

There are no lab setup requirements that affect replication or customization.

Lab Results

Performing the lab in this module introduces the following configuration change:

The following objects are created in the AdventureWorks database:

Type of object Name of object

Message Type AdventureWorks.com/Sales/CustomerDetails

Contract AdventureWorks.com/Sales/SendCustomerDetails Schema CustomerService Schema EmailService Queue EmailService.NewCustomerEmailQueue Queue CustomerService.NewCustomerQueue Service AdventureWorks.com/Sales/CustomerService Service AdventureWorks.com/Sales/EmailService Stored Procedure CustomerService.AddNewCustomer

Stored Procedure EmailService.GetDetailsFromXML Stored Procedure EmailService.ProcessMessages Stored Procedure CustomerService.ProcessMessages Table EmailService.MessageLog Table CustomerService.MessageLog Stored Procedure EmailService.LogMessage

Stored Procedure CustomerService.LogMessage

Additionally, SQLiMail and Service Broker are enabled in the AdventureWorks database.

(8)

(9)

Overview

*****************************ILLEGAL FOR NON-TRAINER USE******************************

Service-oriented architecture (SOA) has become an important concept in the design of large-scale distributed applications. Central to SOA is the ability of services to communicate using a reliable message-based mechanism. This module introduces Microsoft® SQL Server™_{2005 Service Broker, a} message-based platform for building service-oriented database solutions. After completing this module, you will be able to:

! Describe the Service Broker architecture.

! Use Service Broker for message-based communication.

Introduction

(10)

Lesson:

Service Broker Architecture

This lesson introduces SOA and the main features of SQL Server Service Broker. You must understand the overall concepts of an SOA before

understanding how Service Broker implements an SOA for messaging between database applications.

After completing this lesson, you will be able to:

! Explain how the Service Broker system architecture can improve scalability and reduce delays for end users.

! List Service Broker features.

! Describe the purpose of the SQL Server objects involved in Server Broker architecture.

! Describe the Service Broker conversation architecture. ! Explain the Service Broker conversation process. ! Describe the Service Broker security architecture. ! Describe the Service Broker system architecture.

Introduction

(11)

What Is Service-Oriented Architecture?

To improve scalability or to reduce delays for end users, many large-scale systems require a communication approach that is different from traditional communication approaches. SOA provides a communication approach that addresses these needs.

SOA is a type of architecture that encourages loosely coupled communication between software services. A service is a software implementation of a business task that exposes an interface and a contract for communication. Loose coupling

means that the client and the service are not heavily dependent upon each other. For example, you can replace the logic in the service without affecting the client and vice versa.

Software can communicate across the Internet using Web services. Web services are an example of SOA because client code calls a service on an interface exposed by the service provider. This interface calls the internal logic of the Web service, allowing the service provider to change the internal logic without affecting the client. This form of service uses XML and SOAP to communicate.

Services can communicate using messaging technologies such as Message Queuing, which is available in Microsoft Windows NT® 4.0 and later. Messaging provides simple asynchronous communication between the consumer and the provider.

Introduction

Definition

SOA example using Web services

SOA example using messaging

(12)

It is useful to understand the following terms when discussing services and SOA:

Term Description

Service Software that implements a specific task or set of related tasks.

Service provider Software that implements a service specification as defined by a contract. A service provider can also act as a service consumer.

Service consumer Software that calls a service provider. A service consumer can also act as a service provider.

Message A single instance of communication between services. Service providers and consumers communicate using messages. The format of the message varies depending on the SOA implementation; however, a platform-independent message format is advisable so that you do not limit the potential service consumers.

Contract An agreement between two services about the messages each service accepts. The contract allows external developers to understand what a service has to offer from a programmatic perspective.

Conversation An exchange of related messages. Sometimes multiple messages are required to complete a task. The SOA must provide a mechanism to link messages in a conversation. Queue A container for messages that await processing. To allow asynchronous communication, queues are used to store messages temporarily until the service can process the message.

(13)

Service Broker Features

SQL Server 2005 Service Broker provides a concrete implementation of an SOA providing messaging capabilities for your database applications. These capabilities allow you to take advantage of several powerful features to implement service-oriented solutions.

Service Broker uses an identifier to ensure that messages are received only once and in the order in which they were sent. This is part of an ongoing

conversation between two Service Broker endpoints—the service consumer and the service provider. Your service application can expect to handle messages exactly once and in order without any additional code because of this conversation identifier. This greatly reduces the coding effort required while ensuring accuracy in message processing.

To ensure consistency when handling related messages, only one service program instance is able to dequeue and process those messages in the

conversation. However, other service program instances can dequeue messages that are not related.

Service Broker uses an asynchronous message-based approach to

communication so that a client application sends a message but does not have to wait until the service provider returns a result. In fact, the service provider does not even have to be running when the client sends the message. The messages are stored in a queue until the service provider is able to process them. Service Broker allows a service process to dequeue messages when it is convenient for the process, depending on its current load. This can allow overnight batch processing of messages when the server is not too busy, reducing the server load during the day. The client application is unaware of the change because the messages are sent asynchronously.

Introduction

Messages received once and in order Related messages coordinated using conversations Asynchronous delivery Scheduling and processing flexibility

(14)

Service Broker can automatically start a service program upon receipt of a message in a queue. If one instance of the service program cannot handle the volume of messages being received, more instances are started, up to a configurable maximum. When the message rate drops, Service Broker shuts down the service programs. If the system reboots, Service Broker starts service programs if required.

Service Broker integrates messages, queues, and other related items into a database, thus allowing for transactional messaging. You can enclose each iteration of a service program (receiving a message, processing the message, and replying to the client) in a single database transaction. If the transaction fails, the message is queued again so that the service program can make another attempt to process the message.

From an administrator’s point of view, the integration of Service Broker and the database leads to less administration effort. Administrators will automatically back up the Service Broker components as part of their standard SQL Server database backup procedures without any extra configuration.

Service Broker can use digital certificates to encrypt conversations that occur across network connections. Conversation participants can be authenticated using Windows user credentials or a digital certificate.

Automatic service program activation

Database integration

(15)

Service Broker System Architecture

Before you can create a Service Broker application, you must understand the purpose of the five SQL Server objects involved in the Service Broker architecture.

A service consumer sends a message to a service provider. Both parties must agree to the types of message that can be exchanged and how they will be validated.

You must create an identical message type in both the consumer and provider databases.

In Service Broker, a contract object implements the more generic SOA contract. A contract object specifies the message types involved in completing a task and defines who can send each message type. The contract object limits who can send a message type as follows:

! Initiator Only the initiator endpoint can send a message of the specified message type. The initiator is the endpoint that begins the conversation. ! Target Only the target endpoint can send a message of the specified

message type. The target is the endpoint that accepts a conversation from an initiator.

! Any Both endpoints can send a message of the specified message type. You must create an identical contract object in both the consumer and provider databases.

Introduction

Message type

(16)

Service Broker sends messages to services; services store messages in queues. The service consumer can hold the message in a local queue if the service provider server is currently unavailable. Service Broker attempts to resend the message and removes it from the local queue when it is successfully placed in the queue at the provider. If the consumer and provider are located on the same instance of SQL Server, only one queue is used.

In Service Broker, the queue object is implemented as a table with rows representing the messages. The row also contains other information such as a conversation identifier and the contract information. Service Broker removes the message from the queue if you retrieve the message manually.

Alternatively, a queue can automatically start a service program to process a message when the message is received. Service Broker can create more than one instance of the service program, depending on the configuration of the queue, allowing you to scale up to meet increased message-volume demand. The service program processes the messages and provides the logic of the service.

Service Broker can automatically activate the service program when each message arrives. Alternatively, you can schedule an event to activate the service program, or you can execute it manually.

A service program will often need to send a response message to the initiator of the conversation to complete the task. This response will be part of the same conversation so that the initiator can receive the right response.

A service object represents the addressable endpoint for a service. The Service object defines the name of the queue that will hold the messages for the service and for which contracts this service can be a provider. If the service does not specify a contract, the service can only initiate a conversation and cannot be a provider.

Queue

Service program

(17)

Service Broker Conversation Architecture

Service Broker communications use conversations in the exchange of messages. These conversations rely on several constructs to send messages reliably and in the correct order. To develop Service Broker applications, you must understand these constructs.

Dialog conversations involve the sending and receiving of messages between two services, ensuring exactly-once-in-order (EOIO) delivery of each message. EOIO delivery is possible because of the use of a conversation handle and a sequence number. Service Broker uses an automated message receipt acknowledgment so that your service program does not have to check to see whether a message is received.

The following illustration shows how messages flow between the dialog endpoints:

The dialog continues until each service program explicitly ends the dialog or an error occurs. You can use a dialog timeout that guarantees that the dialog cannot live beyond the specified limit.

Introduction

(18)

A service instance identifies a group of related conversations with a unique identifier. Service Broker creates an identifier when it creates the service instance and then adds this identifier to subsequent messages that the client application identifies as a related conversation. The client specifies related conversations using either an existing conversation handle or the known service instance identifier.

Only one service program instance can access messages from a single service instance. This ensures that only one program instance will process all of the messages for related conversations that use the same service instance identifier. Service Broker uses a route to direct messages when conversations take place between different instances of SQL Server. This level of redirection allows you to change the SQL Server instance without affecting the conversations.

The route provides an address for a specified service that includes a network address and an optional service name. If you do not specify a service name, the route is known as the default route. Service Broker uses this route for nonlocal services only when no other route matches.

A remote service binding links a remote service to a local database user account. Service Broker performs authorization using this account and uses the local user’s certificate to encrypt messages when communicating with remote services.

Service instance

Route

(19)

Service Broker Conversation Process

Service Broker applications will typically involve more than sending individual messages to a service provider. Often the service consumer will expect to receive a response from the provider to complete a task. A task may extend across multiple conversations and take hours, or even days, to complete. The slide shows an example conversation between the SubmitExpense service and the ProcessExpense service using a contract named ProcessExpense. The contract specifies two message types, ExpenseClaim and ClaimResponse. The two services are located on different databases within the same instance of SQL Server.

The example conversation follows these steps:

1. An employee submits an expense using a client application such as an ASP.NET Web page. The page uses ADO.NET to call the SubmitExpense

stored procedure.

2. The SubmitExpense stored procedure begins a conversation dialog between the two services and sends an ExpenseClaim message asynchronously to the target endpoint. The SubmitExpense stored procedure ends.

3. The ProcessExpense target service receives the ExpenseClaim message, creates a service instance identifier, and places the message in the Expenses

queue.

4. The arrival of the message in the queue activates the ProcessExpense stored procedure.

5. Processing of the expense begins within the ProcessExpense stored procedure.

6. The ProcessExpense stored procedure sends a ClaimResponse message as part of the same conversation and an EndDialog message to finish the conversation. The ProcessExpense stored procedure ends.

Introduction

(20)

7. The SubmitExpense service receives the ClaimResponse message and places the message in the Expenses queue.

8. The arrival of the ClaimResponse message in the queue activates the

ExpenseResult stored procedure. The procedure checks the contents of the message and looks for an EndDialog message. If it finds an EndDialog, the conversation ends and the stored procedure exits.

(21)

Service Broker Security Architecture

When services conduct conversations across network connections, it is vital that messages be exchanged securely. Service Broker supports both transport

security and dialog security.

Service Broker uses transport security to establish a secure, authenticated connection between two services engaged in a conversation. Services

communicate by sending messages to an HTTP endpoint on the remote server, which can be configured with one of the following authentication options: ! None – Anonymous access to the service is permitted.

! Enabled – Anonymous access is permitted, but authentication is used if the calling service supports or requires it.

! Required – Authentication is required.

The type of authentication used depends on whether the dbo user in the master

database of the calling application has an associated digital certificate. If a certificate exists, certificate-based authentication is used. If no certificate exists, Windows authentication is used.

To use certificate-based authentication, the administrator of each service must create a certificate for the dbo user in the master database, and then export the public key for this certificate and provide it to the administrator of the remote service. Each administrator must then create a user in the master database to represent the dbo user in the remote service and associate the public key provided by the remote administrator with the new user.

To use Windows authentication, the administrator of each service must create a user in the master database for the Windows logon used as the service account by the remote service.

Introduction

(22)

Remote communication between Service Broker instances is

implemented through HTTP endpoints that are created using the CREATE ENDPOINT Transact-SQL statement. For more information about using HTTP endpoints with Service Broker, see SQL Server Books Online.

Service Broker uses dialog security to encrypt messages between remote services and authenticate remote users. Dialog security can be implemented in two ways; full dialog security and anonymous dialog security.

For full dialog security, both the database from which the conversation is initiated and the database in which the called service is implemented must contain two users; one with which to send messages to the remote service, and the other to represent the user in the remote database that will send messages to this service. Each service in the conversation must contain a remote service binding that maps the user account for the remote database user to the remote service. A certificate must be associated with each user, so that messages can be encrypted using the recipient’s public key and decrypted using the

corresponding private key.

Anonymous dialog security works in the same way as full dialog security, but does not require that the target service explicitly permits access to a remote user. When anonymous dialog security is used, the target service allows access through a guest account, and a session key is generated and used to encrypt the messages exchanged by the services.

The Service Broker security model relies heavily on public key infrastructure (PKI) cryptography and digital certificates. You must use the

CREATE CERTIFICATE Transact-SQL statement to create the necessary certificates. For more information about using certificates with Service Broker, see SQL Server Books Online.

Note

Dialog security

(23)

Lesson:

Using Service Broker

This lesson shows you how to create the various objects required when building a simple Service Broker application.

After completing this lesson, you will be able to: ! Enable Service Broker.

! Implement services. ! Create contracts. ! Create queues. ! Create services. ! Send a message. ! Receive a message.

! Use Service Broker for message-based communication.

Introduction Lesson objectives

(24)

How to Enable Service Broker in a Database

Service Broker is enabled in a database by default when the database is created. You can enable or disable Service Broker and check its status by using

Transact-SQL statements. When Service Broker is disabled, all messages are stored in the transmission queue until it is re-enabled.

To check Service Broker status in a database, retrieve the is_broker_enabled

column from the sys.databases catalog view as shown in the following example:

SELECT is_broker_enabled FROM sys.databases

WHERE database_id = db_id()

To enable (or disable) Service Broker in a database, use the ALTER DATABASE Transact-SQL statement as shown in the following example:

ALTER DATABASE AdventureWorks SET ENABLE_BROKER

Introduction

Checking Service Broker status

(25)

Implementing Services

To implement services, you must design several items that will eventually become SQL Server objects in one or more databases. In your Service Broker application, you might be responsible for designing all items described in this topic or only some of them. Some of the items might already exist in your particular scenario.

When you design a Service Broker application, you must consider the following parts of a service-oriented solution:

! Message types ! Contracts ! Queues ! Services

! Service program logic

The remainder of this lesson focuses on how to create each of these parts of the system.

Introduction

(26)

How to Create Contracts

Contracts and message types are the first objects that you must create in your database when building a Service Broker application. The contract defines the list of message types that you will send between services.

To create a contract, you must perform the following steps: 1. Create the message types.

2. Create the contract.

The message type defines the type of data you will send between your services. You specify the message type using the following syntax.

CREATE MESSAGE TYPE message_type_name [AUTHORIZATION owner_name]

[VALIDATION =

{NONE | EMPTY | WELL_FORMED_XML |

VALID_XML WITH SCHEMA COLLECTION schema_collection_name}]

The VALIDATION clause of the syntax specifies the type of data that the message will contain. This type can be one of the following:

! NONE – The message shall not be validated. ! EMPTY – The message must not contain any data.

! WELL_FORMED_XML – The message must contain a well-formed XML string. Service Broker raises an error if the XML is not well formed. ! VALID_XML – The message must contain XML that is valid according to

a schema in the specified XML schema collection.

For more information about creating XML schemas, see Module 3, “Using XML in SQL Server 2005,” in Course 2734, Updating Your Database Development Skills to Microsoft SQL Server 2005.

Introduction

Creating the message types

(27)

The AUTHORIZATION clause of the syntax sets the owner of the message type to the name of a database user or role.

You must create at least one message type in a Service Broker application, and you must recreate each message type in each database that is involved in the conversation.

The following example shows two message types that will contain XML data.

CREATE MESSAGE TYPE

[//Adventure-Works.com/Expenses/ExpenseClaim] VALIDATION = WELL_FORMED_XML

CREATE MESSAGE TYPE

[//Adventure-Works.com/Expenses/ClaimResponse]

VALIDATION = VALID_XML WITH SCHEMA COLLECTION awschemas

A contract defines the message types that you can use in a conversation and the direction in which the message types can be sent. You specify a contract using the following syntax.

CREATE CONTRACT contract_name [ AUTHORIZATION owner_name ]

( message_type_name SENT BY { INITIATOR | TARGET | ANY } [ ,...n] )

The message_type_name part of the syntax specifies the preexisting message type name. SENT BY specifies the direction that the message type can travel in a conversation.

The owner_name part of the syntax sets the owner of the contract to the name of a database user or role.

The following example shows a contract that specifies the previously defined message types. CREATE CONTRACT [//Adventure-Works.com/Expenses/ExpenseSubmission] ( [//Adventure-Works.com/Expenses/ExpenseClaim] SENT BY INITIATOR, [//Adventure-Works.com/Expenses/ClaimResponse] SENT BY TARGET )

In this example, only the initiator can send an ExpenseClaim message and only the target can send a ClaimResponse message.

Important

(28)

How to Create Queues

Queues are an important part of any Service Broker application. The queue defines a location where Service Broker can store messages until a service program becomes available to handle the message.

To create a queue, you must perform the following steps: 1. Create the queue name.

2. Choose the availability of the queue. 3. Specify any activation parameters.

You create a queue using the following syntax.

CREATE QUEUE queue_name

[ WITH [ STATUS = { ON | OFF } [ , ] ] [ RETENTION = { ON | OFF } [ , ] ] [ ACTIVATION ( [ STATUS = { ON | OFF } , ] PROCEDURE_NAME = stored_procedure_name , MAX_QUEUE_READERS = max_readers ,

EXECUTE AS { SELF | 'user_name' | OWNER } ) ] ] [ ON { filegroup | [ DEFAULT ] } ]

Introduction

(29)

The CREATE QUEUE syntax is described in the following table:

Parameter Description

queue_name A name to identify the queue.

STATUS Used to enable or disable the receiving of messages into

the queue. Setting this value to OFF also stops any service program from removing messages from the queue. A queue is ON by default if you do not specify a status value.

You might want to make a queue unavailable for performance reasons, such as reducing the workload of a server during peak periods of the day. You can then enable the queue by using a schedule or manually by using the ALTER QUEUE statement.

RETENTION Allows you to keep all messages in the queue until the

conversation ends. The default setting is OFF, and setting this option ON can lead to reduced performance.

ACTIVATION Allows you to control what happens when a message

arrives in the queue. Specifying a

PROCEDURE_NAME enables Service Broker to activate a stored procedure automatically as the service program. You can temporarily disable the activation feature by setting the activation status to OFF.

MAX_QUEUE_READER S

Controls the maximum number of service program instances that Service Broker will allow to run simultaneously. A smaller setting may mean that messages are held in the queue for a longer period before processing, but the use of database resources will be minimized. You should test different values to optimize the setting in your deployed environment.

EXECUTE AS Specifies the database user account under which the

activated stored procedure runs. You can specify a user_name, the value SELF, which specifies that the procedure will run under the current user account, or OWNER, which specifies that the procedure will run under the account that creates the queue.

ON Allows you to specify on which file group the queue should exist.

(30)

The following example shows the creation of two queues.

CREATE QUEUE ExpenseQueue

CREATE QUEUE ExpenseQueueWithActivation WITH STATUS = OFF,

ACTIVATION ( PROCEDURE_NAME = ProcessExpense,

MAX_QUEUE_READERS = 5,

EXECUTE AS SELF)

In this example, the ExpenseQueue queue does not perform service program activation and is available immediately to store messages. However, a service program must manually read the messages from the queue.

The ExpenseQueueWithActivation queue activates the ProcessExpense

stored procedure as soon as a message arrives. A maximum of five instances of the stored procedure can exist at any one time. The queue is initially

unavailable, and an ALTER QUEUE statement must change the STATUS value before the queue can receive messages. When the procedure does run, it will run as if the user who created the queue were executing the procedure.

If the queue uses activation, the stored procedure must exist in the same database as the queue. The stored procedure must already exist when you create the queue.

(31)

How to Create Services

Services link your service consumer and service provider together. A service is involved from the moment you begin a conversation until you end the

conversation.

To create a service, you must perform the following steps: 1. Create the service name.

2. Choose which queue will store messages that the service consumer sends. 3. Specify the list of contracts for which the service is a provider.

4. Choose whether to retain messages for the duration of a conversation.

You create a service using the following syntax.

CREATE SERVICE service_name

[ AUTHORIZATION owner_name ] ON QUEUE queue_name

[ ( contract_name [ ,...n ] ) ]

The CREATE SERVICE syntax is described in the following table:

service_name A name to identify the service.

AUTHORIZATION Sets the owner of the service to the specified database user

or role.

ON QUEUE Specifies which queue will store incoming messages.

contract_name Specifies zero or more contracts for which the service is a provider. If you do not specify at least one contract, the service can only initiate conversation.

Introduction

(32)

The following example shows the creation of two services that use the same contract while using different queues.

CREATE SERVICE [//Adventure-Works.com/SubmitExpense] ON QUEUE ExpensesInitiator

( [//Adventure-Works.com/Expenses/ProcessExpense] ) CREATE SERVICE [//Adventure-Works.com/ProcessExpense] ON QUEUE ExpensesTarget

(33)

How to Send a Message

Sending a message from a service consumer to a service provider is one of the most common activities you will perform in a Service Broker application. However, you can send a message only after you have created all of the other Service Broker objects, such as contracts, queues, and services.

To send a message, you must perform the following steps: 1. Declare a dialog handle variable.

2. Begin a dialog conversation.

3. Send the message using the dialog handle variable and the message type. Before you can send your message, you must declare a dialog handle identifier variable. You must create this variable as a uniqueidentifier type, as shown in the following example.

DECLARE @dialog_handle uniqueidentifier

Use the BEGIN DIALOG CONVERSATION statement to start a

conversation. BEGIN DIALOG CONVERSATION has the following syntax.

BEGIN DIALOG [CONVERSATION] dialog_handle_identifier

FROM SERVICE service_name

TO SERVICE 'service_name' [ , broker_instance ] ON CONTRACT contract_name [ WITH [ { RELATED_CONVERSATION = conversation_handle | RELATED_CONVERSATION_GROUP = conversation_group_id } ] [ [ , ] LIFETIME = dialog_lifetime ] [ [ , ] ENCRYPTION = { ON | OFF } ] ] Introduction Declaring a dialog handle variable Beginning the conversation

(34)

The BEGIN DIALOG syntax is described in the following table:

dialog_handle_identifier Returns the handle to the newly created dialog identifier. Use your dialog handle variable to store the new identifier.

FROM SERVICE Specifies the service that is sending the message. This

parameter requires a service_name that matches the initiating service name. The queue that the service specifies will receive any messages returned by the target service, such as error or end_dialog messages.

TO SERVICE Specifies the name of the service with which you intend to

begin a conversation. The service_name must be a literal string or a variable that implicitly converts to a string. Note that the comparison is case-sensitive.

broker_instance A unique identifier that allows you to specify a particular database if more than one database hosts an instance of the target service.

ON CONTRACT Specifies the contract name that you intend to use when

communicating with the service.

WITH Allows you to relate the new dialog with an existing

conversation using either RELATED_CONVERSATION or RELATED_CONVERSATION_GROUP identifiers. You can also specify a maximum amount of time in seconds that the conversation will remain open by using the LIFETIME option. If you do not specify the LIFETIME option, both endpoints must end the conversation explicitly.

ENCRYPTION Allows you to encrypt messages sent and received on this

dialog. The default option is that Service Broker encrypts messages between different SQL Server instances. Encryption never occurs during conversations between services on the same SQL Server instance.

The following example shows how to begin a simple dialog conversation.

BEGIN DIALOG CONVERSATION @dialog_handle

FROM SERVICE [//Adventure-Works.com/SubmitExpense] TO SERVICE '//Adventure-Works.com/ProcessExpense'

(35)

Once you have a dialog handle identifier, you can send your message using the

SEND statement. SEND has the following syntax.

SEND ON CONVERSATION conversation_handle

MESSAGE TYPE message_type_name

[ ( message_body_expression ) ]

The SEND syntax is described in the following table:

conversation_handle The linkage to the conversation dialog identifier. Use the uniqueidentifier returned by the BEGIN DIALOG

statement. Alternatively, if you are sending a message from the target back to the initiator as a reply, use the identifier that is available within the incoming message

MESSAGE TYPE Must match a message type that the current endpoint

can send as defined in the contract. If the current endpoint is the initiator, this will include message types marked as SENT BY INITIATOR or SENT BY ANY. If the current endpoint is the target, this will include message types marked as SENT BY TARGET or SENT BY ANY.

message_body_expression The body of the message that you need to send. This must match the appropriate message type.

The following example shows how to send a message.

DECLARE @msgString NVARCHAR(MAX)

SET @msgString = NCHAR(0xFEFF) + N'<root>xml data</root>' ;SEND ON CONVERSATION @dialog_handle

MESSAGE TYPE [//Adventure-Works.com/Expenses/ExpenseClaim] (@msgString)

The example assumes that the message expects an XML body. When you work with XML formatted strings, you must ensure that the string includes a byte order mark preceding the XML data. The NCHAR value 0xFEFF represents a byte order mark.

You must use the semicolon (;) Transact-SQL delimiter if the SEND

statement is not the first statement in a batch or stored procedure.

Sending the message

(36)

How to Receive a Message

Once you have sent a message, a service program needs to read the message from the queue and process the message body. Precisely when the service program reads the message depends on how you chose to set up the queue. However, the basic steps for receiving a message do not change.

To receive a message, you must perform the following steps: 1. Declare variables for storing message details.

2. Call the RECEIVE statement.

3. Check the message type, and process the message accordingly. 4. If the conversation is at an end, call END CONVERSATION.

You need to create local variables so that you can store individual columns from the message. Common columns that you may want to work with include

conversation_handle, message_type_name, and message_body.

The following example shows the declaration of three local variables that will be used later in this topic.

DECLARE @conversation UNIQUEIDENTIFIER DECLARE @msg NVARCHAR(MAX)

DECLARE @msgType NVARCHAR(256)

Introduction

(37)

The RECEIVE statement removes one or more messages from a specified queue so that you can work with the message as you would with any other table-based result set. The statement removes all messages belonging to the same service instance identifier unless you include the TOP parameter in the statement. During this time, no other service program instance can work with the messages you receive or with any other message that has the same service instance identifier. RECEIVE removes messages in order based on the

message_sequence_order value for each message in a conversation.

RECEIVE has the following syntax.

[ WAITFOR ( ]

RECEIVE [ TOP (n) ]

< column_specifier > [ ,...n ] FROM queue_name

[ INTO table_variable ]

[ WHERE { conversation_handle = conversation_handle

| conversation_group_id = conversation_group_id } ] [ ) ] [ , TIMEOUT timeout ]

The RECEIVE syntax is described in the following table:

WAITFOR Blocks processing of the RECEIVE until a message

arrives in the queue. If you specify a TIMEOUT value in milliseconds, the RECEIVE will wait only for the given length of time before returning an empty result set if no message arrives. Omitting this parameter or setting it to -1 cases the RECEIVE statement to wait until a message arrives.

TOP Allows you to specify how many messages to receive from the queue. If you do not specify the TOP parameter, you will receive all of the messages that match a single service instance identifier. Often you will specify the value 1 in order to work with individual messages.

column_specifier Used to list the columns that you intend to use in your message processing logic. For a complete list, see the SQL Server 2005 Books Online.

FROM Specifies the name of the queue where you want to check

for messages.

INTO Allows you to store the result set in a table rather than in

local variables. You should use this approach when you want to work with multiple messages simultaneously.

WHERE Allows you to limit the returned messages to a specific

conversation handle or conversation group identifier. You cannot use any other columns in the WHERE clause.

Calling the RECEIVE statement

(38)

The following example shows how to receive a single message and store three columns from the message in local variables.

;RECEIVE TOP(1) @conversation = conversation_handle, @msgType = message_type_name, @msg = message_body

FROM ExpenseQueue

You should check @@ROWCOUNT after a RECEIVE statement to ensure that a message was found. If @@ROWCOUNT equals 0 (zero), the queue did not contain any messages.

To ensure that you are dealing with the correct type of message, you can check the message_type_name column from the message result set by using a combination of IF statements. Possible message types include:

! The expected message type.

! An error message of the message type

http://schemas.microsoft.com/SQL/ServiceBroker/Error. ! A dialog timeout message of the message type

http://schemas.Microsoft.com/SQL/ServiceBroker/DialogTimer. ! An end dialog message of the message type

http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog. ! An unknown or unexpected message type.

If the message is not of the expected message type, you can end the

conversation and return an error message to the service consumer by using the

END CONVERSATION statement, as shown in the following example.

IF (@msgType = '//Adventure-Works.com/Expenses/ExpenseClaim') -- process @msg ... ELSE IF (@msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error') OR (@msgType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') END CONVERSATION @conversation

ELSE

END CONVERSATION @conversation

WITH ERROR = 500 DESCRIPTION = 'Invalid message type.'

The example also checks to see whether the message is an error message or an end dialog message as predefined by Service Broker. In either case, the example simply ends the conversation. Any other message type results in the end of the conversation and an error message being returned to the service consumer.

Another common action when processing a message is to send a new message either in response to the incoming message or on to another service provider. If the new message should be included as part of the same conversation, use the

RELATED_CONVERSATION parameter in the BEGIN DIALOG

CONVERSATION statement by using the current message’s conversation handle.

Tip

Checking the message type and processing the message

(39)

If the service provider task is complete, you can end the conversation by using the END CONVERSATION statement. END CONVERSATION has the following syntax.

END CONVERSATION conversation_handle

[ [ WITH ERROR = failure_code DESCRIPTION = failure_text ] | [ WITH CLEANUP ] ]

The conversation_handle is the identifier of the conversation that you want to end. You can specify an optional error code and description using the WITH ERROR clause as shown in the preceding syntax.

The WITH CLEANUP clause removes all messages and metadata for this side of the conversation without notifying the other side of the conversation. SQL Server drops the conversation endpoint, all messages for the conversation in the transmission queue, and all messages for the conversation in the service queue.

(40)

Demonstration: Creating a Service Broker Application

In this demonstration, you will see how to create a simple Service Broker application.

!

Create Service Broker objects

1. Click Start, point to All Programs and Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. In the Connect to Server dialog box, enter or confirm the following details and click Connect.

User Interface Element Value

Server type SQL Server

Server name localhost

Authentication Windows Authentication

3. On the File menu, point to Open and click Project/Solution. 4. Openthe ServiceBrokerDemo.ssmssln solution in the

(41)

5. In Solution Explorer, double-click CreateBrokerObjects.sql to view the script in the editor. The script creates the following objects.

Name Object Type Purpose

[//Adventure-Works.com/Expenses/Expense Claim]

Message type Defines an XML message

[//Adventure-Works.com/Expenses/ ProcessExpense]

Contract Defines the single message type and direction

ExpenseQueue Queue Storing expenses

[//Adventure-Works.com/ProcessExpense]

Service Receiving expenses

ExpenseQueueResponse Queue Storing expense responses

[//Adventure-Works.com/SubmitExpense]

Service Receiving expense responses

SubmitExpense Stored procedure

Service consumer for sending expenses

ProcessExpense Stored procedure

Service program for processing expenses ProcessExpenseResponse Stored

procedure

Service program for

processing expense responses

6. On the Query menu, click Execute.

!

Test the Service Broker application

1. In Solution Explorer, double-click RunTests.sql to view the script in the editor.

2. Select the code under the comment submit expense claims, and, on the toolbar, click Execute.

This sends two messages to the [//Adventure-Works.com/SubmitExpense] service, but no processing occurs because ExpenseQueue does not have activation enabled.

3. To confirm that the messages have arrived in ExpenseQueue, select and execute the Transact-SQL code under the comment view the expense claim processing queue.

Two records are displayed from the queue.

4. To process the first message, select and execute the Transact-SQL code under the comment process the first message in the queue.

The Messages pane confirms that processing occurred.

5. Repeat step 3 to confirm that one of the messages has been removed. 6. To confirm that the end dialog message has arrived in the

ExpenseResponseQueue, select and execute the Transact-SQL code under the comment view the response queue.

(42)

7. To process the end dialog message, select and execute the Transact-SQL code under the comment process the first response.

The messages pane confirms that processing occurred.

8. Repeat step 6 to confirm that all the current response messages have been removed.

9. Optionally, to process the remaining message, repeat steps 4 and 7 once more.

(43)

Lab 4: Using Service Broker

After completing this lab, you will be able to: ! Create Service Broker objects.

! Send messages between service programs. ! Test a Service Broker application.

This lab focuses on the concepts in this module and as a result may not comply with Microsoft security recommendations. For instance, this lab does not comply with the recommendation that you perform development tasks using a nonadministrative account.

Before working on this lab, you must have: ! A basic knowledge of Transact-SQL. ! A basic knowledge of XML.

The management at Adventure Works wants to send a welcome e-mail to new customers who register on their website. To minimize the impact to the site’s scalability, you have decided to implement this functionality through a service that will be invoked asynchronously when a new user registers.

Objectives

Note

Prerequisites

Scenario

Estimated time to complete this lab: 30 minutes

(44)

Exercise 0

Lab Setup

Before starting this lab, you must log on as Administrator and restart the SQL Server services.

!

Log on as Administrator

• Log on as Administrator with the password P@ssw0rd.

!

Restart SQL Server services

(45)

Exercise 1

Creating Service Broker Objects

In this exercise, you will use Microsoft® SQL Server™_{Management Studio to} execute Transact-SQL queries that create several Service Broker objects for use in the subsequent exercises.

The C:\Program Files\Microsoft Learning\2734\Labfiles\Lab04\Solution\ CreateServiceBrokerObjects.sql file provides a solution to this exercise if you need to check your Transact-SQL statements.

!

Set up a message type and a contract

1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and click SQL Server Management Studio.

2. In the Connect to Server dialog box, enter or confirm the following details and click Connect:

3. On the standard toolbar, click New Query, and then click New SQL Server Query. When prompted, connect to localhost using Windows

authentication.

Do not execute the script until specifically instructed to do so.

4. Enter the following Transact-SQL statement to enable the service broker in the AdventureWorks database.

USE master

ALTER DATABASE AdventureWorks SET ENABLE_BROKER GO

5. Enter the following Transact-SQL statement to change the database context to AdventureWorks and create two schemas (one for the service used to add customers and another for the service used to send e-mails).

USE AdventureWorks GO

CREATE SCHEMA CustomerService GO

CREATE SCHEMA EmailService GO

Tip

(46)

6. Add Transact-SQL code to create a message type with the following values.

Setting Value

Message type name [//Adventure-Works.com/Sales/CustomerDetails] Validation WELL_FORMED_XML

7. Add Transact-SQL code to create a contract with the following values.

Setting Value

Contract name [//Adventure-Works.com/Sales/SendCustomerDetails] Message type [//Adventure-Works.com/Sales/CustomerDetails] Message direction SENT BY INITIATOR

!

Create the queues

1. Add Transact-SQL code to create a queue named

CustomerService.NewCustomerQueue with no activation values. This service consumer queue will receive any responses from the service provider.

2. Add Transact-SQL code to create a queue named

EmailService.NewCustomerEmailQueue with no activation values. This service provider queue will receive the new customer messages from the service consumer.

!

Create the services

1. Add Transact-SQL code to create a service with the following values:

Setting Value

Service name [//Adventure-Works.com/Sales/CustomerService] On queue CustomerService.NewCustomerQueue

2. Add Transact-SQL code to create a service with the following values:

Setting Value

Service name [//Adventure-Works.com/Sales/EmailService] On queue EmailService.NewCustomerEmailQueue

(47)

Your complete Transact-SQL code should appear as follows:

USE master

ALTER DATABASE AdventureWorks SET ENABLE_BROKER GO

USE AdventureWorks GO

CREATE SCHEMA CustomerService GO

CREATE SCHEMA EmailService GO

CREATE MESSAGE TYPE

[//Adventure-Works.com/Sales/CustomerDetails] VALIDATION = WELL_FORMED_XML CREATE CONTRACT [//Adventure-Works.com/Sales/SendCustomerDetails] ( [//Adventure-Works.com/Sales/CustomerDetails] SENT BY INITIATOR )

CREATE QUEUE CustomerService.NewCustomerQueue CREATE QUEUE EmailService.NewCustomerEmailQueue CREATE SERVICE

[//Adventure-Works.com/Sales/CustomerService] ON QUEUE CustomerService.NewCustomerQueue

CREATE SERVICE [//Adventure-Works.com/Sales/EmailService] ON QUEUE EMailService.NewCustomerEmailQueue

([//Adventure-Works.com/Sales/SendCustomerDetails]) GO

3. On the SQL Editor toolbar, click Execute.

4. Keep SQL Server Management Studio open for the next exercise.

(48)

Exercise 2

Implementing the Customer Service

In this exercise, you will implement the CustomerService service. This service will send customer detail messages to the EmailService service and process the responses it receives.

The C:\Program Files\Microsoft Learning\2734\Labfiles\Lab04\Solution\ ImplementCustomerService.sqlfile provides a solution to this exercise if you need to check your Transact-SQL statements.

!

Implement a stored procedure that sends a message to the Email service

1. On the File menu, point to Open, and click File.

2. In the Open File dialog box, locate C:\Program Files\Microsoft Learning\ 2734\Labfiles\Lab04\Starter\ImplementCustomerService.sqland click

Open.

3. In the Connect to SQL Server dialog box, enter or verify the following details and click Connect:

4. The AddNewCustomer stored procedure acts as a client in the Service Broker application. View and understand the existing part of the stored procedure.

5. Find the comment --##### Add code to send message here #####. 6. Declare a local variable named @message of type NVARCHAR(MAX). 7. After the declaration, add the following Transact-SQL code.

SET @message = NCHAR(0xFEFF) + '<Customer>'

+ '<CustomerName>' + @firstName + ' ' + @lastName + '</CustomerName>' + '<EmailAddress>' + @emailAddress + '</EmailAddress>' + '</Customer>'

8. Declare a local variable named @conversationHandle of type

UNIQUEIDENTIFIER.

9. Begin a dialog conversation using the following values.

Setting Value

Conversation identifier @conversationHandle

From service [//Adventure-Works.com/Sales/CustomerService] To service '//Adventure-Works.com/Sales/EmailService'

On contract [//Adventure-Works.com/Sales/SendCustomerDetails]

(49)

10. Send a message using the following values:

Setting Value

Conversation identifier @conversationHandle

Message type [//Adventure-Works.com/Sales/CustomerDetails] Message @message

11. Your completed Transact-SQL code should look as follows.

DECLARE @message NVARCHAR(MAX) SET @message = NCHAR(0xFEFF) + '<Customer>'

+ '<CustomerName>' + @firstName + ' ' + @lastName + '</CustomerName>' + '<EmailAddress>' + @emailAddress + '</EmailAddress>' + '</Customer>'

DECLARE @conversationHandle UNIQUEIDENTIFIER BEGIN DIALOG CONVERSATION @conversationHandle

FROM SERVICE [//Adventure-Works.com/Sales/CustomerService] TO SERVICE '//Adventure-Works.com/Sales/EmailService' ON CONTRACT

[//Adventure-Works.com/Sales/SendCustomerDetails] ;SEND ON CONVERSATION @conversationHandle

MESSAGE TYPE [//Adventure-Works.com/Sales/CustomerDetails] (@message)

!

Implement a service program for the CustomerService service

1. Find the comment --##### Add code to process messages here #####. 2. After the comment, declare the following local variables.

Variable Name Type

@conversationHandle UNIQUEIDENTIFIER

(50)

3. After the variable declaration, receive a single message from the

CustomerService.NewCustomerQueue queue. Store the following columns in the local variables.

Column Name Variable

conversation_handle @conversationHandle message_type_name @messageTypeName

Your code should look like the following sample.

DECLARE @conversationHandle UNIQUEIDENTIFIER,

@messageTypeName NVARCHAR(256) ;RECEIVE TOP(1) @conversationHandle = conversation_handle, @messageTypeName = message_type_name FROM CustomerService.NewCustomerQueue

4. Check to see whether a message was found by using the following Transact-SQL code.

IF @@ROWCOUNT = 0 BEGIN

EXEC CustomerService.LogMessage

'NewCustomerQueue', 'No further messages found.' RETURN

END

5. After END, end the conversation by using @conversationHandle. 6. Log the message by using the following Transact-SQL code.

EXEC CustomerService.LogMessage 'NewCustomerQueue', @messageTypeName, @conversationHandle

7. On the Query menu, click Execute.

If you encounter any errors, fix the errors and execute the query again. 8. Keep SQL Server Management Studio open for the next exercise.