SQL 服务代理——一个中央 SQL 和多个卫星 SQL [英] SQL Service Broker -- one central SQL and more satellite SQL

问题描述

该系统由一台中央 SQL 服务器和两台或多台卫星服务器组成.卫星服务器收集测量数据并将其发送到中央服务器.看图:

The system consists of one central SQL server and two or more satellite servers. The satellite servers collect the measured data and send them to the central server. See the picture:

(图片来自官方Service Broker 通信协议 文章和修改.)

(The picture was taken from the official Service Broker Communication Protocols article and modified.)

我需要使添加另一个卫星 SQL 的操作尽可能简单.我的意思是，设置新添加的卫星 SQL 应该可能与其他卫星 SQL 机器的设置相同.有可能吗?

I need to make the act of adding another satelite SQL as simple as possible. I mean, setting the newly added satelite SQL should possibly be the same as of the other satelite SQL machines. Is it possible at all?

所有 SQL 服务器都位于同一个域中，不需要基于证书的加密——至少现在不需要.部署的简便性和速度是现在的首要任务.后期可以提高安全性.

All SQL servers are located in the same domain, no certificate-based encryption is neccessary -- at least not now. The ease and speed of deployment is the priority now. The security can be improved in the later phase.

换句话说，卫星 SQL 是否可以使用相同的消息类型、相同的合约创建代码、相同的端点设置，...

In other words, can the satelite SQL use the same message types, the same contract-creation code, the same end-point setting,...

我对路由和目标绑定有点困惑.你能对此发表评论吗?

I am a bit confused with routing and target binding. Can you comment on that?

推荐答案

最简单的最简单的部署是以下配置:

The absolute simplest of the simplest to deploy is the following config:

1. 在任何地方使用相同的消息类型和契约.这在任何情况下都是必须的，所以不用说.
2. 不要使用对话框安全.只需 GRANT SEND ON SERVICE::[] TO [public] 无处不在.这消除了对数据库证书和远程服务绑定的需要.
3. 不要为端点使用证书，但不要交换它们(导出、导入、创建登录等).有一个技巧:GRANT CONNECT ON ENDPOINT::[] TO [public] 允许两个端点使用 SSL(证书)连接甚至不交换证书.
4. 使用传输路由(意味着启用特殊的TRANSPORT 路由并使用[tcp://hostname:port/servicename] 约定命名您的服务.

1. Use the same message types and contracts every where. This is a must in any situations, so it kind of goes without saying.
2. Do no use dialog security. simply GRANT SEND ON SERVICE::[<servicename>] TO [public] everywhere. This eliminates the need for database certificates and remote service bindings.
3. Do use certificates for endpoints, but do not exchange them (export, import, create login etc). There is a trick: GRANT CONNECT ON ENDPOINT::[<brokerendpointname>] TO [public] allows two endpoints to connect using SSL (certificates) even w/o exchanging certificates.
4. Use transport routing (meaning enable the special TRANSPORT route and name your services using the [tcp://hostname:port/servicename] convention.

让我解释一下我推荐这种设置的原因:

Let me explain why I'm recommending this setup:

• 移除对话安全性可将部署简化 10 倍之类的.对话安全性允许服务对每条消息的发件人进行身份验证和授权，但在相对受控的环境(内联网)中，您可以基于信任进行部署:服务收到的任何消息都被信任来自授权发件人.
• 为端点使用证书通常被视为复杂，因为需要交换证书以允许连接，但在代理端点上授予连接到 public 的技巧消除了交换证书的要求.使用此技巧的所有机器都可以无需任何事先设置相互通信，这甚至比在端点上使用 Windows 身份验证更好(需要授权连接到 domain\machine$ ro 需要使用特定域帐户部署 SQL Server 实例).同样，您失去了对连接说不"的能力，您将接受来自 Intranet 中任何 SQL 实例的连接.
• 通过 TRANSPORT 路由，任何加入派对"的 SQL Server 实例都准备好了:因为服务名称包含主机名，所有其他机器已经知道如何与这台机器通信，并且不需要添加显式路由.

• Removing dialog security simplifies deployment by something like 10x. Dialog security allows a service to authenticate and autorize the sender of each message, but in relatively controlled environments (intranet) you can deploy based on trust: any message received by a service is trusted to come from an authorized sender.
• Using certificates for endpoints is generally seen as complex because of the need to exchange the certificates to allow connectivity, but the trick of granting connect to public on the broker endpoint removes the requirement to exchange certificates. All machines using this trick can talk to each other w/o any prior setup which is better even than using Windows authentication on the endpoints (that require grant connect to domain\machine$ ro requires deployment of SQL Server instances using specific domain accounts). Again, you loos the ability to say 'No' on a connection, you will accept connection from any SQL instance in your intranet.
• With TRANSPORT routing any SQL Server instance that joins the 'party' is ready to rock: because the service name contains the host name, all the other machines already know how to talk with this machine and do no require explicit routes to be added.

这种配置非常接近即插即用".新机器可以立即加入与任何现有 SQL Server SSB 服务的通信而无需对其他现有机器进行任何配置更改.

This configuration is really as close as you get to 'plug-and-play'. New machines can join the communication with any existing SQL Server SSB services immediately without requiring any configuration changes on the other existing machines.

以下是如何为此类部署配置机器的示例.假设您想首先在 MACHINE1 上部署中央服务器:

Here is an example of how to configure a machines for such a deployment. Say you want to start by deploying the central server on MACHINE1:

use master;
go

create database master key...
create certificate [MACHINE1] with subject 'MACHINE1';
create endpoint BROKER as tcp (listener_port 4022) for service_broker 
  (authentication certificate [MACHINE1]);
grant connect on endpoint::BROKER to [public];
go

use db1;
create message type...
create contract ...
create queue ...
create service [tcp://MACHINE1:4022/CentralService] 
   on ...
   ([...]);
grant send on service::[tcp://MACHINE1:4022/CentralService] to [public];
create route transport with address = 'TRANSPORT';
go

就是这样.现在让一个节点，比如在主机 MACHINE2 上:

That's it. Now let ad a node, say on host MACHINE2:

use master;
go

create database master key...
create certificate [MACHINE2] with subject 'MACHINE2';
create endpoint BROKER as tcp (listener_port 4022) for service_broker 
  (authentication certificate [MACHINE2]);
grant connect on endpoint::BROKER to [public];
go

use db2;
create message type...
create contract ...
create queue ...
create service [tcp://MACHINE2:4022/Satellite] 
   on ...
   ([...]);
grant send on service::[tcp://MACHINE2:4022/Satellite] to [public];
create route transport with address = 'TRANSPORT';
go

就是这样.现在发生了两件事:

That's it. Now two things happen:

• 因为 MACHINE1 和 MACHINE2 上的两个端点都使用基于证书的身份验证并已授予公共连接权限，因此它们可以按原样连接和交换消息，而无需交换(导出和导入)它们的端点证书
• 因为两个数据库都创建了特殊的 TRANSPORT 路由并且服务名称具有特殊的 [tcp://machine:port/service] 语法，所以两个服务可以立即交换消息 as-是，没有任何显式路由.

• because both endpoints on MACHINE1 and MACHINE2 use certificate based authentication and have granted connect to public, they can connect and exchange messages as is, without the need to exchange (export and import) their endpoint certificates
• because both databases have created the special TRANSPORT route and the service names have the special [tcp://machine:port/service] syntax the two services can immediately exchange messages as-is, without any explicit routing.

最好的事情是你如何添加一个新节点，比如 MACHINE3:

The best thing is how you add a new node, say MACHINE3:

use master;
go

create database master key...
create certificate [MACHINE3] with subject 'MACHINE3';
create endpoint BROKER as tcp (listener_port 4022) for service_broker 
  (authentication certificate [MACHINE3]);
grant connect on endpoint::BROKER to [public];
go

use db2;
create message type...
create contract ...
create queue ...
create service [tcp://MACHINE3:4022/Satellite] 
   on ...
   ([...]);
grant send on service::[tcp://MACHINE3:4022/Satellite] to [public];
create route transport with address = 'TRANSPORT';
go

现在，删除对 MACHINE1 和 MACHINE2 的任何单一更改，新节点 MACHINE3 可以与中央服务交换消息，如果需要，实际上也可以与 MACHINE2 的卫星交换消息.端点接受任何人连接，因此欢迎使用 MACHINE3，并且使用的服务名称由特殊的 TRANSPORT 路由机制自动路由.这就是这种配置的美妙之处，即插即用:添加一个新节点需要在其他节点上进行 0 配置.

Now, whiteout any single change to MACHINE1 nor to MACHINE2, the new node MACHINE3 can exchange messages with the central service, and actually with the Satellite of MACHINE2 too, if needed. The endpoints are accepting anybody to connect so MACHINE3 is welcome, and the service names used are auto-routed by the special TRANSPORT routing mechanism. This is the beauty of this configuration, the plug-and-play: adding a new node requires 0 config on the other nodes.

那是什么?最大的问题是安全性.任何员工都可以在其桌面上下载 SQL Server Express，设置未经授权的卫星节点并开始与中央服务交换消息.真的没有什么可以阻止他，你已经明确地打开了所有的大门.一个更微妙的问题是服务何时移动.当服务 [tcp://MACHINE3:4022/Satellite] 移动(例如通过数据库备份/恢复)到 MACHINE4 时，服务的名称仍然有效TRANSPORT 路由语法名称，但不正确.根据保留现有对话的重要性，您可以选择取消服务并创建一个名为 [tcp://MACHINE4:4022/Satellite] 和派对的新服务(您不能重命名服务，您必须删除并创建一个新服务).如果维护现有对话很重要，那么有一些变通方法，因为在中央服务数据库上为它添加一个显式路由将优先于最后的传输路由，消息将被正确重定向.重要的是有解决方案:)

So what gives? The biggest problem is security. Any employee can download SQL Server Express on his desktop, set up an unauthorized Satellite node and start exchanging messages with the Central service. There really isn't anything to stop him, you have explicitly open all gates. A more subtle problem is when a service moves. When the service [tcp://MACHINE3:4022/Satellite] is moved (eg. via database backup/restore) to MACHINE4 the name of the service is still a valid TRANSPORT route syntax name, but is incorrect. Depending on how important is to preserve existing conversation, you can choose to nuke the service and create a new one, named [tcp://MACHINE4:4022/Satellite] and party (you cannot rename a service, you must drop and create a new one). If maintaining existing conversation is critical then there are workarounds, as adding an explicit route for it on the Central service database will take precedence over the last-resort TRANSPORT route and messages will be redirected correctly. The important thing is that there are solutions :)

这篇关于SQL 服务代理——一个中央 SQL 和多个卫星 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！