Service Broker整个实践过程

最新推荐文章于 2023-08-04 10:22:32 发布

最新推荐文章于 2023-08-04 10:22:32 发布 · 123 阅读

文章标签：

#数据库

本文介绍如何在 SQL Server 中使用 Service Broker 进行消息传递和服务交互，涵盖启动 Service Broker、创建合同、队列、服务及消息发送接收等关键步骤。

一、启动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_XML

CREATE 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>