sql - 用于分布式应用程序的 SQL Server 服务代理，其会话处于会话状态

Question

我已经对 SSB 有一点经验，但我只是在同一台服务器上的数据库中完成了这项工作。我从来没有这样做过分布式，在不同的机器上使用两个不同的服务器。因此，经过一些研究，我找到了一些 youtube 教程，以及一个很好的教程，其中包含我想要的安全性。

但是我无法将消息从服​​务器 A 传递到服务器 B。问题是；如何找到我缺少的东西？

我知道你们都有工作，不是来审查任何代码的。但是我坚持了几天，我只是想要一些帮助，或者一些敏锐的眼睛来指出我看不到的东西。

我正在阅读这本书名为“Pro SQL Server 2008 Service Broke”，试图找到我的答案。

我在 youtube 上收集了一些视频。

我尝试了此故障排除，但无法使用 ssbdiagnose。我不知道在哪里可以找到它。

---

现在，我要澄清并发布我的环境以及到目前为止我所做的事情。

发起者是 SQL Server 2016 Express

• 视窗 2010 专业版
• 主机：192.168.20.44:1433
• 数据库：市场
• 防火墙：关闭
• 服务器配置：启用 TCP
• SSB：端口 4022（仅通过 telnet 测试）

目标是 SQL Server 2016 Express

• 在192.168.20.44 托管的Hyper-V 上运行的Windows Server 2012 R2 Standard

• 主机：192.168.20.30:1433

• 数据库：市场

• 防火墙：关闭

• 服务器配置：启用 TCP

• SSB：端口 4022（仅通过 telnet 测试）

192.168.20.44 启动器服务器的脚本

--###
--All actions related to Basic Service Broker Objects and Dialog Security 
--will be performed in MarketPlace database of 192.168.20.44
--###
--1 Create the Basic Service Broker Objects
--###
USE MarketPlace
GO

ALTER DATABASE MarketPlace
SET Enable_broker;
GO

--1.1 Create Message Types
CREATE Message Type SenderMessageType validation = NONE;
GO

CREATE Message Type ReceiverMessageType validation = NONE;
GO

--1.2 Create Contract on the above message types
CREATE Contract PointOfSaleContract (
    SenderMessageType SENT BY INITIATOR
    ,ReceiverMessageType SENT BY TARGET
    );
GO

--1.3 Create an Initiator queue
CREATE QUEUE InitiatorQueue
    WITH STATUS = ON;
GO

--1.4  Create a Service on the queue and the contract
CREATE Service MarketPlaceService ON QUEUE InitiatorQueue (PointOfSaleContract);
GO

--###
--2 Set up Dialog Security
--###
--2.1 Create a master key in the local database i.e. the database we are going to use for our application.
CREATE Master KEY ENCRYPTION BY Password = 'gs53&"f"!385'
GO

--2.2 Create a user certificate
CREATE Certificate CertificateUserMarketPlace
    WITH Subject = 'CertificateUserMarketPlace'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO

--2.3 Take a backup of the CertificateUserMarketPlace created and install it into the remote instance
--Copy the certificate to Stage Server Machine
--I Did install the certificates for current user within "automatically select the certificate store" option. Have to?
BACKUP CERTIFICATE CertificateUserMarketPlace TO FILE = 'C:\SSB\CertificateUserMarketPlace.cer';
GO

--2.4 Create a user with the same name as the user who has access rights on the other Database
--I Didn't understand this part. Should I create the very same user on Stage database?
CREATE User UserStage WITHOUT LOGIN
GO

--2.5 Create a user certificate from the user certificate backup file copied from the other server, 
--with authorization to the user created in Step 4
CREATE CERTIFICATE CertificateUserStage AUTHORIZATION UserStage
FROM FILE = 'C:\SSB\CertificateUserStage.cer';
GO

--2.6 Grant connect permissions to the user
GRANT CONNECT
    TO UserStage;
GO

--2.7 Grant send permissions to the user on the local service
GRANT SEND
    ON SERVICE::MarketPlaceService
    TO UserStage;
GO

--2.8 Create a Remote Service Binding with the user created.
CREATE REMOTE SERVICE BINDING ServiceBindingStage TO SERVICE 'StageService'
    WITH USER = UserStage
GO

--###
--3 Set up Transport Security
--All actions related to Transport Security
--will be performed in Master database of 192.168.20.44
--###
USE master
GO

--3.1 Create a master key for master database.
CREATE Master KEY ENCRYPTION BY Password = 'gs53&"f"!385'
GO

--3.2 Create certificate 
CREATE CERTIFICATE EndPointCertificateMarketPlace
    WITH Subject = 'EndPointCertificateMarketPlace'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO

--3.3 Create End Point that support certificate based authentication
CREATE ENDPOINT ServiceBrokerEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER(AUTHENTICATION = CERTIFICATE EndPointCertificateMarketPlace, ENCRYPTION = SUPPORTED);
GO

--3.4 Take a backup of the certificate created and install it into the remote instance
--Copy the certificate to Stage
BACKUP CERTIFICATE EndPointCertificateMarketPlace TO FILE = 'C:\SSB\EndPointCertificateMarketPlace.cer';
GO

--3.5 Create certificate from the certificate backup file copied from the Target server
CREATE CERTIFICATE EndPointCertificateStage
FROM FILE = 'C:\SSB\EndPointCertificateStage.cer';
GO

--3.6 Create login from the certificate created in Step 3.5
CREATE LOGIN SSBLogin
FROM CERTIFICATE EndPointCertificateStage;
GO

--3.7 Grant the login, connect permissions on the end point.
GRANT CONNECT
    ON ENDPOINT::ServiceBrokerEndPoint
    TO SSBLogin
GO

SELECT *
FROM sys.service_broker_endpoints
GO

--###
--4 Create a Route
--###
USE MarketPlace
GO

--4.1 Get the UID from Stage database on 192.168.20.30 to use on the Route
SELECT service_broker_guid
FROM sys.databases
WHERE NAME = 'Stage';

--4.2 Use the UID from 4.1
CREATE Route RouteToStageService
    WITH SERVICE_NAME = 'StageService'
        ,BROKER_INSTANCE = 'A88B9743-EAFF-42FA-9404-0D551D4B29DB' -- Guid From Stage
        ,ADDRESS = 'TCP://192.168.20.30:4022'
GO

目标服务器 192.168.20.30 的脚本

--###
--All actions related to Basic Service Broker Objects and Dialog Security 
--will be performed in Stage database of 192.168.20.30
--###
--1 Create the basic Service Broker Objects
--###
USE Stage
GO

ALTER DATABASE Stage
SET Enable_broker;
GO

--1.1 Create Message Types
CREATE Message Type SenderMessageType validation = NONE;
GO

CREATE Message Type ReceiverMessageType validation = NONE;
GO

--1.2 Create Contract on the above message types
CREATE Contract PointOfSaleContract (
    SenderMessageType SENT BY INITIATOR
    ,ReceiverMessageType SENT BY TARGET
    );
GO

--1.3 Create an Target queue
CREATE QUEUE TargetQueue
    WITH STATUS = ON;
GO

--1.4  Create a Service on the queue and the contract
CREATE Service StageService ON QUEUE TargetQueue (PointOfSaleContract);
GO

--###
--2 Set up Dialog Security
--###
--2.1 Create a master key in the local database i.e. the database we are going to use for our application.
CREATE Master KEY ENCRYPTION BY Password = '45Gme*3^&fwu'
GO

--2.2 Create a user certificate
CREATE Certificate CertificateUserStage
    WITH SUBJECT = 'CertificateUserStage'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO
--2.3 Take a backup of the user certificate created and install it into the remote instance
--Copy the certificate to MarketPlace Server Machine
BACKUP CERTIFICATE CertificateUserStage TO FILE = 'C:\SSB\CertificateUserStage.cer';
GO

--2.4 Create a user with the same name as the user who has access rights on the other Database
CREATE User UserMarketPlace WITHOUT LOGIN
GO

--2.5 Create a user certificate from the user certificate backup file copied from the other server, 
--with authorization to the user created in Step 4
CREATE CERTIFICATE CertificateUserMarketPlace AUTHORIZATION UserMarketPlace
FROM FILE = 'C:\SSB\CertificateUserMarketPlace.cer';
GO

--2.6 Grant connect permissions to the user
GRANT CONNECT
    TO UserMarketPlace;
GO

--2.7 Grant send permissions to the user on the local service
GRANT SEND
    ON SERVICE::StageService
    TO UserMarketPlace;
GO

--2.8 Create a Remote Service Binding with the user created.
CREATE REMOTE SERVICE BINDING ServiceBindingMarketPlace TO SERVICE 'MarketPlaceService'
    WITH USER = UserMarketPlace
GO

--###
--3 Set up Transport Security
--All actions related to Transport Security
--will be performed in Master database of 192.168.20.30
--###
USE master
GO

--3.1 Create a master key for master database.
CREATE Master KEY ENCRYPTION BY Password = '45Gme*3^&fwu';
GO

--3.2 Create certificate and End Point that support certificate based authentication 
CREATE Certificate EndPointCertificateStage
    WITH Subject = 'EndPointCertificateStage'
        ,START_DATE = '2018-01-01'
        ,EXPIRY_DATE = '2020-12-31' ACTIVE
FOR BEGIN_DIALOG = ON;
GO

--3.3 Create End Point that support certificate based authentication
CREATE ENDPOINT ServiceBrokerEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4022)
FOR SERVICE_BROKER(AUTHENTICATION = CERTIFICATE EndPointCertificateStage, ENCRYPTION = SUPPORTED);
GO

--3.4 Take a backup of the certificate created and install it into the remote instance.
--Copy the certificate to MarketPlace
BACKUP CERTIFICATE EndPointCertificateStage TO FILE = 'C:\SSB\EndPointCertificateStage.cer';
GO

--3.5 Create certificate from the certificate backup file copied from the other server
CREATE Certificate EndPointCertificateMarketPlace
FROM FILE = 'C:\SSB\EndPointCertificateMarketPlace.cer';
GO

--3.6 Create login from the certificate created in Step 3.5
CREATE LOGIN SSBLogin
FROM CERTIFICATE EndPointCertificateMarketPlace;
GO

--3.7 Grant the login, connect permissions on the end point.
GRANT CONNECT
    ON ENDPOINT::ServiceBrokerEndPoint
    TO SSBLogin
GO

SELECT *
FROM sys.service_broker_endpoints
GO

--###
--4 Create a Route
--###
USE Stage
GO

--4.1 Get the UID from MarketPlace database on 192.168.20.44 to use on the Route
SELECT service_broker_guid
FROM sys.databases
WHERE NAME = 'Stage';

--4.2 Use the UID from 4.1
CREATE Route RouteToMarketPlaceService
    WITH SERVICE_NAME = 'MarketPlaceService'
        ,BROKER_INSTANCE = 'A18B5078-EB73-42D4-ACF9-4AF6549921A0' -- From MarketPlace
        ,ADDRESS = 'TCP://192.168.20.44:4022'
GO

现在，当我在 Initiator Server 上运行它时，消息卡住了：

USE MarketPlace
GO

SELECT conversation_handle, to_service_name, enqueue_time, cast(message_body AS XML)
FROM sys.transmission_queue;

DECLARE @ConversationHandle uniqueidentifier;
BEGIN TRANSACTION
  BEGIN DIALOG @ConversationHandle
  FROM SERVICE MarketPlaceService
  TO SERVICE 'StageService'
  ON CONTRACT PointOfSaleContract
  WITH ENCRYPTION = OFF;
  SEND
  ON CONVERSATION @ConversationHandle
  MESSAGE TYPE SenderMessageType
  ('<test>Test 001</test>')
COMMIT

SELECT conversation_handle, to_service_name, enqueue_time, cast(message_body AS XML)
FROM sys.transmission_queue;

SELECT conversation_handle, is_initiator, state_desc, far_service
FROM MarketPlace.sys.conversation_endpoints;

我在目标服务器上看不到我的消息：

--###
--6 Sent Messages from MarketPlace
--###
USE Stage
GO

SELECT cast(message_body AS XML)
FROM TargetQueue;
GO

如果你读到这里。感谢您的关注。

标签： sqlsql-serverservice-broker

Answer 1

来自“SQL Server 2005 Express Edition 概述”：

SQL Server Express 只能与其他 SQL Server 2005 版本结合使用 Service Broker。如果 SQL Server Express 从另一个 Express 实例接收到 Broker 消息，并且另一个 SQL Server 2005 版本尚未处理该消息，则该消息将被丢弃。因此，消息可以源自 Express 实例并以 1 结尾，但如果是这种情况，则必须通过非 Express 实例进行路由。您可以检查可从 Profiler 访问的 Message Drop 跟踪事件，或使用跟踪存储过程来跟踪此类事件。与丢弃的消息相关的错误消息包括这样的措辞：“由于许可限制，此消息已被丢弃。”

我知道这是针对旧版本的方式，但我找不到 2016 年同样冗长的声明。但是，由于“SQL Server 2016 的版本和支持的功能”仍然列出了有关 Service Broker 的限制（“否（仅限客户端）”）我相信这在 2016 年仍然有效。

不久前，我确实尝试过使用非常相似的设置（也是 2016 Express），但没有找到让它直接工作的方法。

我发现的一种解决方法是使用链接服务器。我BEGIN DIALOG CONVERSATION ... SEND ON CONVERSATION ...通过链接将代码发送到远程服务器并使用它在那里执行sp_executesql（内部消息代理在 Express Editions 中运行良好，它只是无法与其他 Express Edition 服务器交换消息）。这样做我遇到了 DTC 的一个错误（显然），它阻止了远程调用最初的工作。相反，它告诉我 DTC 在远程服务器上不可用。但是，这可以通过将remote proc transaction promotion选项设置为 来解决false。

EXEC master.dbo.sp_serveroption @server = N'<the server link>', @optname = N'remote proc transaction promotion', @optvalue = N'true';

在相当长的一段时间内，这一切都运作良好。但是 SQL Server 的一些最新更新可能会破坏某些东西。至少我的 Service Broker 设置遇到了一些奇怪的问题，类似于你的设置，它们似乎是在 SQL Server 更新的那天开始的。但由于这不是一件很重要的事情，我还没有时间更详细地调查这个问题并找到解决方案。所以我现在不能给你一个提示。（有问题的更新似乎是从今年 5 月左右开始的。抱歉，我现在手头没有 KB 数字。）

如果您仅将其用于开发，则另一种选择可能是“升级”到开发人员版。这声称功能齐全（相当于企业版 AFAIK）。这是 2017 版本的链接。但我相信还有一个2016年，如果你坚持要留在2016年。