一、启动Service Broker
- 检查Service Broker的状态
SELECT is_broker_enabled FROM sys.databases WHERE database_id = db_id()
- 启动Service Broker
ALTER DATABASE DMWSNewFunction SET ENABLE_BROKER
二、执行服务
1. 在设计Servcie Broker应用程序时,你必须考虑到以下设计方案
- 消息类型(message type)
- 合同(conract)
- 队列(queues)
- 服务(services)
- 服务编程逻辑services program logic)
2. 怎样去创造一个合同
合同定义了服务之间的消息传递类型,创造一个合同必须执行以下2个步骤:
a). 创造消息类型
用以下的语法创造消息类型:
CREATE MESSAGE TYPE message_type_name
[AUTHORIZATION owner_name]
[VALIDATION =
{NONE | EMPTY | WELL_FORMED_XML |
VALID_XML WITH SCHEMA COLLECTION schema_collection_name}]VALIDATION 定义了消息包含的类型
NONE:消息可以不被验证
EMPTY:消息必须为空
WELL_FORMED_XML:消息必须是XML格式
VALID_XML:消息必须是经过符合XML Schema
AUTHORIZATION是数据库用户或角色
例子:
CREATE MESSAGE TYPE
[//Adventure-Works.com/Expenses/ExpenseClaim]
VALIDATION = WELL_FORMED_XMLCREATE MESSAGE TYPE
[//Adventure-Works.com/Expenses/ClaimResponse]
VALIDATION = VALID_XML WITH SCHEMA COLLECTION awschemas
b). 创造合同
CREATE CONTRACT contract_name
[ AUTHORIZATION owner_name ]
( message_type_name SENT BY { INITIATOR | TARGET | ANY }
[ ,...n] )message_type_name 是先前定义的消息类型,SENT BY 表示在传输过程中消息的方向。
例子:
CREATE CONTRACT
[//Adventure-Works.com/Expenses/ExpenseSubmission]
( [//Adventure-Works.com/Expenses/ExpenseClaim]
SENT BY INITIATOR,
[//Adventure-Works.com/Expenses/ClaimResponse]
SENT BY TARGET )在这个例子中只有发起者可以传输ExpenseClaim消息类型,目标者传输ClaimResponse类型
3.去创造队列:他是临时存储消息的:
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 = 'user_name' } ) ] ]
[ ON { filegroup | [ DEFAULT ] } ]例子:
CREATE QUEUE ExpenseQueue
CREATE QUEUE ExpenseQueueWithActivation
WITH STATUS = OFF,
ACTIVATION ( PROCEDURE_NAME = ProcessExpense,
MAX_QUEUE_READERS = 5,
EXECUTE AS SELF)ExpenseQueueWithActivation 队列激活 ProcessExpense存储过程当消息到达的时候。在任何时候最大邮5个存储过程实例存在。队列刚开始是
不可用的,在接受到消息之前我们必须用ALTER Queue去更改它的状态。
4. 去创造服务:服务连接了服务的消费者和生产者。服务包括了从会话开始到整个会话的结束。
去创造服务你必须按以下步骤:服务的名称;当服务的消费者送达消息时提供存储消息的队列;指定合同;选择在会话期间是否保存消息
CREATE SERVICE service_name
[ AUTHORIZATION owner_name ]
ON QUEUE queue_name
[ ( contract_name [ ,...n ] ) ]例子:
CREATE SERVICE [//Adventure-Works.com/SubmitExpense]
ON QUEUE ExpensesInitiator
( [//Adventure-Works.com/Expenses/ProcessExpense] )
CREATE SERVICE [//Adventure-Works.com/ProcessExpense]
ON QUEUE ExpensesTarget
( [//Adventure-Works.com/Expenses/ProcessExpense] )
5. 怎样去发送消息,在创造Service Broker各个组件后,你可以发送消息:按以下步骤发送消息:声明一个可用的对话句柄;
创造一个会话;用会话句柄和消息类型发送消息。
DECLARE @dialog_handle uniqueidentifier //声明会话句柄
BEGIN DIALOG [CONVERSATION] dialog_handle_identifier //利用BEGIN DIALOG CONVERSATION 开启对话
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 } ] ]例子:
BEGIN DIALOG CONVERSATION @dialog_handle
FROM SERVICE [//Adventure-Works.com/SubmitExpense]
TO SERVICE '//Adventure-Works.com/ProcessExpense'
ON CONTRACT [//Adventure-Works.com/Expenses/ProcessExpense]发送消息:一旦会话开启,就可以用send语句去send消息了
SEND ON CONVERSATION conversation_handle
MESSAGE TYPE message_type_name
[ ( message_body_expression ) ]例子:
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)
6. 怎样去接受消息:一旦你发送消息后,服务就会从消息队列中读取消息并处理。接受消息必须执行以下步骤:声明变量存储消息详细信息;执行Receive语句;检查消息类型并处理消息;如果会话结束,执行END CONVERSATION.
DECLARE @conversation UNIQUEIDENTIFIER
DECLARE @msg NVARCHAR(MAX)
DECLARE @msgType NVARCHAR(256)[ 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 ]例子:
;RECEIVE TOP(1) @conversation = conversation_handle,
@msgType = message_type_name, @msg = message_body
FROM ExpenseQueue
7. 检查消息类型和处理消息:为了确保你接受到的是合同里的消息类型,你可以用IF语句去检查结果集中的message_type_name列。可能的消息类型有以下几种:
- 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 (@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.'
8. 结束对话:
END CONVERSATION conversation_handle
[ WITH ERROR = failure_code DESCRIPTION = failure_text ]Answers
Q.1- What is Service Oriented Architecture?
Answer.1- SOA is a type of architecture that encourages loosely coupled communication between software services.
Q.2- What are the names of the constructs, which are required to develop a service broker application?
Answer.2- Dialog, Service instance, Route, and Remote service binding.
Q.3- What type of security is supported by Service broker?
Answer.3- Transport security and Dialog security.
Q.4- When you design a Service Broker application, which parts of a service-oriented solution you must consider.
Answer.4- Message types, Contracts, Queues, Services, and Service program logic
源文档 <http://www.programmersheaven.com/2/SQL-server-2005-school-lesson-8-p4>
http://www.programmersheaven.com/2/SQL-server-2005-school-lesson-8