AI 助理
备案控制台
开发者社区 云原生 云消息队列 文章 正文

SQL Server Service Broker创建单个数据库会话（消息队列）

2014-12-04 1170
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：
2018-01-08 18:27 by pursuer.chen, 287 阅读, 1 评论, 收藏, 编辑

概述  

SQL Server Service Broker 用来创建用于交换消息的会话。消息在目标和发起方这两个端点之间进行交换。消息用于传输数据和触发消息收到时的处理过程。目标和发起方既可以在同一数据库引擎实例的同一数据库或不同数据库中，也可以在不同数据库引擎实例的同一数据库或不同数据库中。

 

每个 Service Broker 会话都有两个端点：会话发起方和目标。您将执行下列任务：

  • 为目标创建一个服务和队列，并为发起方创建一个服务和队列。
  • 创建请求消息类型和答复消息类型。
  • 创建约定，指定请求消息从发起方传递到目标并且答复消息从目标传递到发起方。

然后执行一个简单会话：

  • 启动会话。
  • 从发起方向目标发送一个请求。
  • 在目标处接收请求并将答复发送到发起方。
  • 在发起方处接收答复。
  • 结束会话。

对于其两端在同一 数据库引擎 实例中的会话，其消息不通过网络传输。数据库引擎 安全性和权限将限制对授权主体的访问。此方案不需要网络加密。

一、创建会话对象

1.启用Service Broker

复制代码 
----创建数据库
IF NOT EXISTS(SELECT * FROM SYS.DATABASES WHERE name='Dsend')
BEGIN
     CREATE DATABASE Dsend;

END

USE master;
GO
---开启数据库BROKER
ALTER DATABASE Dsend  SET ENABLE_BROKER;
GO

SELECT is_broker_enabled FROM SYS.DATABASES WHERE NAME='Dsend'

USE Dsend;
GO
复制代码

2.创建消息类型

由于经常在多个数据库引擎实例间引用 Service Broker 对象，因而大多数 Service Broker 对象的名称都是 URI 格式的。这有助于确保它们在多台计算机上是唯一的。这两种消息类型都指定 Service Broker 将只验证消息是否是格式正确的 XML 文档，并且指定 Service Broker 将不按照特定架构验证 XML。

复制代码 
CREATE MESSAGE TYPE
       [//Dsend/test/RequestMessage]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//Dsend/test/ReplyMessage]
       VALIDATION = WELL_FORMED_XML;
复制代码

创建请求消息和答复消息，并且消息的格式为XML格式。

注意：Service Broker 验证传入消息。如果消息包含的消息正文与指定的验证类型不符，则 Service Broker 将放弃此无效消息，并向发送此消息的服务返回一条错误消息。会话双方必须定义相同的消息类型名称。为便于排除故障，尽管 Service Broker 不要求会话双方使用相同的验证，但通常会话双方还是会为消息类型指定相同的验证。消息类型不能是临时对象。允许使用以 # 开头的消息类型名称，但它们是永久对象。

3.创建约定

约定用于定义在 Service Broker 会话中所使用的消息类型，还用于确定会话的哪一端可以发送该类型的消息。每个会话都要遵循一个约定。当会话开始时，启动服务为会话指定约定。目标服务指定该目标服务将接受其会话的约定。 

复制代码 
/*
SENT BY INITIATOR    ----指示只有会话的发起方才能发送指定消息类型的消息。启动会话的服务称为会话的“发起方”
SENT BY TARGET       ----指示只有会话的目标才能发送指定消息类型的消息。接受由另一个服务启动的会话的服务称为会话的目标。
SENT BY ANY          ----指示发起方和目标都可以发送此类型的消息。
*/

CREATE CONTRACT [//Dsend/test/RequestContract]
      ([//Dsend/test/RequestMessage]    
       SENT BY INITIATOR,             ---约定只有发起方才能使用//Dsend/test/RequestMessage消息类型
       [//Dsend/test/ReplyMessage]
       SENT BY TARGET                 ---约定只有答复方才能使用//Dsend/test/ReplyMessage消息类型
      );
复制代码

4.创建队列

队列可以存储消息。当一条针对某项服务的消息到达时，Service Broker 会将该消息放入与该服务关联的队列中。

创建发起方和答复方的队列。

CREATE QUEUE RequestQueue WITH STATUS=ON;
CREATE QUEUE ReplyQueue WITH STATUS=ON;

注意：

1.队列可以通过SELECT 语句查询，但是不能使用INSERT、UPDATE、DELETE 或 TRUNCATE 语句来操作。只能使用在 Service Broker 会话中运行的语句（如 SEND、RECEIVE 和 END CONVERSATION）来修改队列的内容。

2.队列可能不是临时对象。因此，以 # 开头的队列名称无效。

3.通过以不可用状态创建队列，可以先准备好服务的基础结构，然后再允许在队列中接收消息。

4.如果队列中没有消息，则 Service Broker 不会停止激活存储过程。如果队列中在短时间内没有可用消息，应退出激活存储过程。

5.在 Service Broker 启动存储过程时将检查激活存储过程的权限，而不是在创建队列时检查。CREATE QUEUE 语句不验证 EXECUTE AS 子句中指定的用户是否有权限执行 PROCEDURE NAME 子句中指定的存储过程。

6.队列不可用时，Service Broker 将在数据库的传输队列中保存使用该队列的服务的消息。sys.transmission_queue 目录视图提供传输队列的视图。

 

例：创建具有多个参数的队列

以下示例在 DEFAULT 文件组中创建一个队列。该队列不可用。消息被保留在队列中，直到消息所属的会话结束。通过 ALTER QUEUE 启用队列后，该队列将启动存储过程 2008R2.dbo.expense_procedure 来处理消息。此存储过程以运行 CREATE QUEUE 语句的用户的身份执行。该队列最多启动存储过程的 10 个实例。

复制代码 
CREATE QUEUE ExpenseQueue
    WITH STATUS = OFF,
      RETENTION = ON,
      ACTIVATION (
          PROCEDURE_NAME = AdventureWorks2008R2.dbo.expense_procedure,
          MAX_QUEUE_READERS = 10,
          EXECUTE AS SELF )
    ON [DEFAULT] ;
复制代码

5.创建服务

Service Broker 使用服务的名称路由消息、将消息传递到数据库中的正确队列，以及强制执行会话的约定。一个服务可以同时绑定多个约束。

复制代码 
--1.创建要用于发起方的队列和服务。由于未指定约定名称，因而其他服务不可将此服务用作目标服务，此服务只能启动会话。
CREATE SERVICE
       [//Dsend/test/RequestService]
       ON QUEUE RequestQueue     
GO
---2.创建接答复服务
CREATE SERVICE
       [//Dsend/test/ReplyService]
       ON QUEUE ReplyQueue
       ([//Dsend/test/RequestContract]
       )
       ;
GO
复制代码

注意：服务公开与其关联的约定提供的功能，以便其他服务可使用该功能。CREATE SERVICE 语句指定针对此服务的约定。一个服务只能是使用该服务指定的约定会话的目标。未指定约定的服务不会向其他服务公开任何功能。

从此服务启动的会话可使用任何约定。如果服务仅启动会话，则创建服务时可不指定约定。Service Broker 从远程服务接受新会话时，目标服务的名称决定了 Broker 在会话中放入消息的队列。

 

例:创建具有多个约定的服务

CREATE SERVICE [//Adventure-Works.com/Expenses] ON QUEUE ExpenseQueue
    ([//Adventure-Works.com/Expenses/ExpenseSubmission],
     [//Adventure-Works.com/Expenses/ExpenseProcessing]) ;

5.开启回话

复制代码 
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE
      [//Dsend/test/RequestService] ---指定的服务是用于答复消息的返回地址
     TO SERVICE
      N'//Dsend/test/ReplyService'  ---指定的服务是消息发送到的地址。
     ON CONTRACT
      [//Dsend/test/RequestContract]
     WITH
         ENCRYPTION = OFF;

SELECT @InitDlgHandle;

SELECT @RequestMsg =
       N'<RequestMsg>3</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE 
     [//Dsend/test/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO
复制代码

 

6.接收方接收消息并返回消息给发送方

复制代码 
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;
DECLARE @Message NVARCHAR(100)

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM ReplyQueue
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;
SELECT @RecvReqMsgName;


BEGIN

     -----返回接收消息确认结果到发起方

     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
     N'<ReplyMsg>Reply Message</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE 
          [//Dsend/test/ReplyMessage]
          (@ReplyMsg);

     SELECT @RecvReqDlgHandle;
      
     ---正常的流程不是在这里结束会话
     END CONVERSATION @RecvReqDlgHandle;
END

IF @RecvReqMsgName =N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
         END CONVERSATION @RecvReqDlgHandle;
    END

COMMIT TRANSACTION;
GO
复制代码

例：接收数据同时插入表变量

DECLARE @conversation_group_id UNIQUEIDENTIFIER ;

DECLARE @procTable TABLE(
     service_instance_id UNIQUEIDENTIFIER,
     handle UNIQUEIDENTIFIER,
     message_sequence_number BIGINT,
     service_name NVARCHAR(512),
     service_contract_name NVARCHAR(256),
     message_type_name NVARCHAR(256),
     validation NCHAR,
     message_body VARBINARY(MAX)) ;

SET @conversation_group_id = <retrieve conversation group ID from database> ;

RECEIVE TOP (1)
    conversation_group_id,
    conversation_handle,
    message_sequence_number,
    service_name,
    service_contract_name,
    message_type_name,
    validation,
    message_body
FROM ExpenseQueue
INTO @procTable
WHERE conversation_group_id = @conversation_group_id ;
View Code

 

7.发送方收到消息终止会话

复制代码 
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReplyDlgHandle = conversation_handle,
    @RecvReplyMsg = message_body,
    @RecvReqMsgName=message_type_name
  FROM RequestQueue
), TIMEOUT 1000;

IF @RecvReqMsgName =N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
    BEGIN
         END CONVERSATION @RecvReqDlgHandle;
    END

SELECT @RecvReplyMsg AS ReceivedReplyMsg;

COMMIT TRANSACTION;

GO
复制代码

三、查询

复制代码 
SELECT state_desc,*
FROM sys.conversation_endpoints

SELECT message_type_name, CAST(message_body as xml) message,*
FROM dbo.RequestQueue

SELECT message_type_name, CAST(message_body as xml) message,*
FROM dbo.ReplyQueue

SELECT CAST(message_body as xml) message,* FROM sys.transmission_queue

--END CONVERSATION '236AF2C5-57F4-E711-A9E6-005056C00008';
复制代码

四、删除会话对象

复制代码 
IF EXISTS (SELECT * FROM sys.services
           WHERE name =
           N'//AWDB/1DBSample/TargetService')
     DROP SERVICE
     [//AWDB/1DBSample/TargetService];

IF EXISTS (SELECT * FROM sys.service_queues
           WHERE name = N'TargetQueue1DB')
     DROP QUEUE TargetQueue1DB;

-- Drop the intitator queue and service if they already exist.
IF EXISTS (SELECT * FROM sys.services
           WHERE name =
           N'//AWDB/1DBSample/InitiatorService')
     DROP SERVICE
     [//AWDB/1DBSample/InitiatorService];

IF EXISTS (SELECT * FROM sys.service_queues
           WHERE name = N'InitiatorQueue1DB')
     DROP QUEUE InitiatorQueue1DB;

IF EXISTS (SELECT * FROM sys.service_contracts
           WHERE name =
           N'//AWDB/1DBSample/SampleContract')
     DROP CONTRACT
     [//AWDB/1DBSample/SampleContract];

IF EXISTS (SELECT * FROM sys.service_message_types
           WHERE name =
           N'//AWDB/1DBSample/RequestMessage')
     DROP MESSAGE TYPE
     [//AWDB/1DBSample/RequestMessage];

IF EXISTS (SELECT * FROM sys.service_message_types
           WHERE name =
           N'//AWDB/1DBSample/ReplyMessage')
     DROP MESSAGE TYPE
     [//AWDB/1DBSample/ReplyMessage];
GO
复制代码

 

参考：http://www.cnblogs.com/downmoon/archive/2011/04/05/2005900.html

参考：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-message-type-transact-sql

总结

使用单数据库会话可以处理一般的队列发送和读写消息的场景，其中sys.conversation_endpoints系统视图需要重点关注。

 

 





本文转自pursuer.chen(陈敏华)博客园博客，原文链接：http://www.cnblogs.com/chenmh/p/8204765.html，如需转载请自行联系原作者

文章标签：
云数据库 RDS SQL Server 版
数据库
SQL
Go
存储
数据格式
消息中间件
数据安全/隐私保护
XML
关键词：
SQL数据库
数据库sql
sqlserver数据库
云数据库RDS SQLServer版数据库
service数据库
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中，使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库，该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品：云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能，强力支撑各种企业应用，同时也包含了微软的License费用，减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
长征6号
目录
相关文章
1436047922066202
|
16天前
|
SQL 数据库
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
SQL Server附加数据库出现错误823，附加数据库失败。数据库没有备份，无法通过备份恢复数据库。 SQL Server数据库出现823错误的可能原因有：数据库物理页面损坏、数据库物理页面校验值损坏导致无法识别该页面、断电或者文件系统问题导致页面丢失。
1436047922066202
82 12
数据库数据恢复—SQL Server数据库报错“错误823”的数据恢复案例
html的七十二变
|
2天前
|
SQL 存储 移动开发
HTML5 Web SQL 数据库详解
Web SQL 数据库是 HTML5 中的一种本地存储技术，允许在浏览器中使用 SQL 语言操作本地数据，支持离线访问和事务处理，适用于缓存数据和小型应用。然而，其存储容量有限且仅部分现代浏览器支持，标准已不再积极维护，未来可能被 IndexedDB 和 localStorage 等技术取代。使用时需谨慎考虑兼容性和发展前景。
html的七十二变
23 6
蓝易云
|
11天前
|
SQL 关系型数据库 MySQL
创建包含MySQL和SQLServer数据库所有字段类型的表的方法
创建一个既包含MySQL又包含SQL Server所有字段类型的表是一个复杂的任务，需要仔细地比较和转换数据类型。通过上述方法，可以在两个数据库系统之间建立起相互兼容的数据结构，为数据迁移和同步提供便利。这一过程不仅要考虑数据类型的直接对应，还要注意特定数据类型在不同系统中的表现差异，确保数据的一致性和完整性。
蓝易云
22 4
sunrr
|
25天前
|
SQL 存储 数据管理
SQL Server数据库
SQL Server数据库
sunrr
41 11
蓝易云
|
1月前
|
SQL 安全 数据库
基于SQL Server事务日志的数据库恢复技术及实战代码详解
基于事务日志的数据库恢复技术是SQL Server中一个非常强大的功能，它能够帮助数据库管理员在数据丢失或损坏的情况下，有效地恢复数据。通过定期备份数据库和事务日志，并在需要时按照正确的步骤恢复，可以最大限度地减少数据丢失的风险。需要注意的是，恢复数据是一个需要谨慎操作的过程，建议在执行恢复操作之前，详细了解相关的操作步骤和注意事项，以确保数据的安全和完整。
蓝易云
59 0
土木林森
|
2月前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用：以MVVM为核心，手把手教你重构代码结构，实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中，合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例，详细解析了这些模式的实际应用，特别是MVVM模式如何通过分离UI逻辑与业务逻辑，实现视图与模型的松耦合，从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程，帮助读者更好地理解并应用这些模式。
土木林森
58 0
wljslmz
|
2月前
|
SQL 数据处理 数据库
SQL 函数全解析：掌握数据库操作的关键工具
【8月更文挑战第31天】
wljslmz
34 0
wljslmz
|
2月前
|
SQL 存储 调度
SQL Server 中的计划任务：高效数据库管理的利器
【8月更文挑战第31天】
wljslmz
69 0
wljslmz
|
2月前
|
SQL 安全 数据库
深入了解 SQL Server Agent：数据库管理的得力助手
【8月更文挑战第31天】
wljslmz
41 0
土木林森
|
2月前
|
Java 数据库连接 数据库
告别繁琐 SQL！Hibernate 入门指南带你轻松玩转 ORM，解锁高效数据库操作新姿势
【8月更文挑战第31天】Hibernate 是一款流行的 Java 持久层框架，简化了对象关系映射（ORM）过程，使开发者能以面向对象的方式进行数据持久化操作而无需直接编写 SQL 语句。本文提供 Hibernate 入门指南，介绍核心概念及示例代码，涵盖依赖引入、配置文件设置、实体类定义、工具类构建及基本 CRUD 操作。通过学习，你将掌握使用 Hibernate 简化数据持久化的技巧，为实际项目应用打下基础。
土木林森
66 0
云原生

云消息队列

热门文章

最新文章

  • 1
    Redis笔记（七）Java实现Redis消息队列
  • 2
    后端必备——数据通信知识(RPC、消息队列)一站式总结
  • 3
    消息队列ActiveMQ的使用详解
  • 4
    消息队列MQ新增3把武器
  • 5
    C#消息队列(MQ)零基础从入门到实战演练
  • 6
    aurora - 跨平台 Beanstalk 消息队列服务器管理工具
  • 7
    android异步图片加载之handler+线程池+消息队列模式+缓存
  • 8
    消息队列常见的 5 个应用场景
  • 9
    细说UI线程和Windows消息队列
  • 10
    消息队列在测试开发中的应用思路
  • 1
    RocketMQ体验测评
    44
  • 2
    关键成果与技术应用
    62
  • 3
    AutoMQ 开源可观测性方案：夜莺 Flashcat
    56
  • 4
    消息队列 MQ使用问题之如何使用DefaultMQPushConsumer来消费消息
    84
  • 5
    消息队列 MQ使用问题之遇到消费速度是固定的并且导致了堆积，该怎么办
    118
  • 6
    消息队列 MQ使用问题之如何在grpc客户端中设置负载均衡器
    35
  • 7
    消息队列 MQ使用问题之如何配置生产环境
    69
  • 8
    消息队列 MQ使用问题之在使用C++客户端和GBase的ESQL进行编译时出现core dump，该怎么办
    112
  • 9
    消息队列 MQ使用问题之如何将RocketMQ中某个集群的topic迁移到另一个集群
    48
  • 10
    消息队列 MQ操作报错合集之建立连接时发生了超时错误，该如何解决
    242

    • 相关课程

    更多
  • 数据库的前世今生
  • 数据库核心概念
  • 从传统数据库到云数据库演进
  • 数据库常见问题排查
  • 数据库及SQL/MySQL基础
  • 高校精品课-西安交通大学 -数据库理论与技术

    • 相关电子书

    更多
  • DTCC 2022大会集锦《云原生一站式数据库技术与实践》
  • 阿里云瑶池数据库精要2022版
  • 2022 DTCC-阿里云一站式数据库上云最佳实践

    • 相关实验场景

    更多
  • 数据库实验室挑战任务-高级任务
  • 数据库实验室挑战任务-中级任务
  • 数据库实验室挑战任务-初级任务
  • 阿里云数据库产品实验室
  • MongoDB数据库入门
  • 如何开通SQL Server的专属集群
    • 下一篇
    无影云桌面