SQL Server Service Broker——处理 SQL Server 实例之间两阶段提交的建议
我们正在探索在两个不同的 SQL Server 实例之间进行通信的不同方法。所需的工作流程之一是向“远程”端发送某种消息，请求删除记录。当该系统完成删除时，它保持其事务打开并将响应发送回发起者，然后发起者删除其相应的记录，提交其事务，然后将消息发送回“远程”端，最后告诉它：也可以在其一侧提交删除。
这是两阶段提交的穷人近似。该部门正在进行一场关于 SQL Server Service Broker 是否能够合理干净地处理这种类型的场景的争论。任何人都可以阐明是否可以吗？有类似类型工作流程的经验吗？考虑到 SQL Server 实例位于单独的非域计算机上，我是否应该寻找更好的机制来完成此任务？
编辑：澄清一下，我们不能使用分布式事务，因为网络安全既严格又有些任意。我们不允许进行使之成为可能的配置。
We're exploring different approaches for communicating between two different SQL Server instances. One of the desired workflows is to send a message of some sort to the "remote" side requesting, let's say, deletion of a record. When that system completes the deletion, it holds its transaction open and sends a response back to the initiator, who then deletes its corresponding record, commits its transaction, and then sends a message back to the "remote" side, telling it, finally, to commit the deletion on its side as well.
It's a poor man's approximation of two-phase commit. There's a religious debate going on in the department as to whether SQL Server Service Broker can or can't handle this type of scenario reasonably cleanly. Can anyone shed light on whether it can? Any experience in similar types of workflows? Is there a better mechanism I should be looking at to accomplish this, considering that the SQL Server instances are on separate, non-domain machines?
Edit: To clarify, we can't use distributed transactions due to the fact that network security is both tight and somewhat arbitrary. We're not allowed the configuration that would make that possible.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除非我误解了要求，否则我会说这对于 Service Broker 来说是一份完美的工作。 Service Broker 使您无需使用分布式事务和 2PC。使用 Service Broker 所做的就是将问题减少到本地事务和服务器之间的事务消息传递。
在您的特定情况下，其中一台服务器将删除其记录，然后（作为同一事务的一部分）向另一台服务器发送一条消息，请求删除相应的记录。将消息放入队列后，第一台服务器可以提交事务并忘记整个事情，而无需等待与第二台服务器同步。 Service Broker 保证一旦提交消息入队，该消息将以事务方式传递到目标，然后目标可以将其记录作为接收消息的同一事务的一部分删除，从而确保消息处理和数据更改是原子的。
Unless I'm misunderstanding the requirements, I'd say it's a perfect job for Service Broker. Service Broker frees you from the need of using distributed transactions and 2PC. What you do with Service Broker is reduce the problem to local transactions and transactional messaging between the servers.
In your particular case, one of the servers would delete its record and then (as part of the same transaction) send a message to the other server requesting deletion of the corresponding record. After enqueuing the message, the first server can commit the transaction and forget the whole thing without waiting for synchronization with the second server. Service Broker guarantees that once enqueuing of a message is committed, the message will be transactionally delivered to the destination, which can then delete its record as part of the same transaction in which it received the message, thus making sure the message processing and data changes are atomic.
您是否尝试过使用分布式事务？
它将完成您需要的一切，但每个服务器都需要作为链接服务器相互连接。
Have you tried using a distibuted transaction?
It will do everything you need but each server will need to connect to each other as a linked server.