实现远程服务器的Service Broker

为了实现跨越服务器通信，可以通过Windows身份验证或基于证书的身份验证，启用传输安全模式、启用对话安全模式、创建路由、创建远程绑定。

--在实例1上use mastergoif exists(select 1 from sys.databases where name = 'bookstore')drop database bookstoreelsecreate database bookstorego--开启service brokeralter database bookstore set enable_broker--指明 SQL Server 实例是否信任该数据库以及其中的内容。alter database bookstore set trustworthy on use bookstoregocreate message type [//BookConsulting/SendBookOrder]validation = well_formed_xmlgocreate message type [//BookConsulting/BookOrderReceived]validation = well_formed_xmlgocreate contract [//BookConsulting/BookOrderContract]([//BookConsulting/SendBookOrder] sent by initiator,[//BookConsulting/BookOrderReceived] sent by target)gocreate queue BookStoreQueuewith status = oncreate service [//BookConsulting/BookOrderService]on queue dbo.BookStoreQueue([//BookConsulting/BookOrderContract])

--在实例2上use mastergoif exists(select 1 from sys.databases where name = 'bookdistribution')drop database bookdistributionelsecreate database bookdistributiongoalter database bookdistribution set enable_brokeralter database bookdistribution set trustworthy onuse bookdistributiongocreate message type [//BookConsulting/SendBookOrder]validation = well_formed_xmlgocreate message type [//BookConsulting/BookOrderReceived]validation = well_formed_xmlgocreate contract [//BookConsulting/BookOrderContract]([//BookConsulting/SendBookOrder] sent by initiator,[//BookConsulting/BookOrderReceived] sent by target)gocreate queue BookDistributionQueuewith status = oncreate service [//BookConsulting/BookDistributionService]on queue dbo.BookDistributionQueue([//BookConsulting/BookOrderContract])

--实例1use master go--1.删除已经存在的数据库主密钥drop master key--2.创建数据库主密钥create master key encryption by password = '123456!@#'--3.创建证书create certificate bookMasterCertwith subject = 'book Transport Security Service Broker', expiry_date = '2050-12-31' --4.备份证书backup certificate bookMasterCertto file = 'd:\bookMasterCert.cer'goselect * from sys.database_mirroring_endpoints --5.创建端点create endpoint service_broker_book_endpointstate = started--需要提供传输协议（TCP 或 HTTP）特定的信息，设置端点的侦听端口号，以及设置端点身份验证的方法和/或要限制访问端点的 IP 地址列表（如果有的话）。as tcp (listener_port = 4020)--在此部分中，需要定义端点上所支持的负载。--负载可以为以下多种支持类型中的一种：SOAP、Transact-SQL、Service Broker、数据库镜像。for service_broker (authentication = certificate bookMasterCert,--证书encryption = required --密钥                   )--6.创建SQL Server的登录名create login service_broker_loginwith password = 'service_broker_login123'--7.创建数据库用户名create user service_broker_userfor login service_broker_login--8.授予数据库用户可以连接端点grant connect on endpoint::service_broker_book_endpoint               to service_broker_login--9.通过另一个实例复制到本地服务器上的证书文件,来创建证书create certificate bookDistributionMasterCertauthorization service_broker_userfrom file = 'd:\bookDistributionMasterCert.cer'go

--实例2use master go--删除数据库主密钥drop master keycreate master key encryption by password = '123456&^%'create certificate bookDistributionMasterCertwith subject = 'bookDistribution Transport Security Service Broker', expiry_date = '2080-12-31' backup certificate bookDistributionMasterCertto file = 'e:\bookDistributionMasterCert.cer'create endpoint service_broker_bookdistribution_endpointstate = startedas tcp (listener_port = 4021)for service_broker (authentication = certificate bookDistributionMasterCert,encryption = required                   )create login service_broker_loginwith password = 'service_broker_login123'create user service_broker_userfor login service_broker_logingrant connect on endpoint::service_broker_bookdistribution_endpoint               to service_broker_loginuse master gocreate certificate bookMasterCertauthorization service_broker_userfrom file = 'd:\bookMasterCert.cer'go

--实例1use bookstorego--drop master key--1.创建数据库主密钥create master key encryption by password = '123456!@#'  --查看创建的主密钥select * from sys.symmetric_keys--2.创建证书,这里可以给当前数据库用户创建多个证书,不会有影响--当接收到其他服务器传送过来的消息时,可以用这个证书来解密消息create certificate BookStoreCertwith subject = 'BookStore service broker cert',     expiry_date = '2080-12-31'--3.备份证书backup certificate bookstorecertto file = 'd:\bookstorecert.cer'go--4.创建数据库用户,此用户只可以有一个证书create user bookDistributionUserwithout logingo--5.通过从另一个实例复制过来的证书,来创建证书,并指定所有者为此用户create certificate bookDistributionCertauthorization bookDistributionUser        --此用户只能拥有一个证书,                                          --在发送消息时会用这个证书来加密消息from file = 'd:\bookDistributionCert.cer'--6.授予此用户名在某个服务上发送的权限grant send on service::[//BookConsulting/BookOrderService] to bookDistributionUsergo--7.创建路由create route route_bookDistributionwith service_name = '//BookConsulting/BookDistributionService',     address = 'tcp://192.168.5.9:4021'--8.创建远程绑定create remote service binding bookDistributionBindingto service '//BookConsulting/BookDistributionService'with user = bookDistributionUser--9.开始会话,发送消息declare @conversation_handler uniqueidentifierdeclare @order_msg xml;begin dialog conversation @conversation_handlerfrom service [//BookConsulting/BookOrderService]to service '//BookConsulting/BookDistributionService'on contract [//BookConsulting/BookOrderContract]set @order_msg = '<order id="1234" customer="22" orderdate="2012-10-01"><LineItem ItemNumber="1" ISBN="1-12345-123-0" Quantity="1" /></order>';--send语句可以发送消息send on conversation @conversation_handlermessage type [//BookConsulting/SendBookOrder](@order_msg);

--启用对话安全模式--实例2use bookdistributiongocreate master key encryption by password = '123456&^%'  --当接收到对方发送的消息后,用此证书来解密create certificate BookDistributionCertwith subject = 'BookDistribution service broker cert',     expiry_date = '2080-12-31'backup certificate bookDistributioncertto file = 'd:\bookDistributioncert.cer'create user bookStoreUserwithout login--在发送之前,用此证书来加密消息create certificate bookStoreCertauthorization bookStoreUserfrom file = 'd:\bookStoreCert.cer'grant send on service::[//BookConsulting/BookDistributionService] to bookStoreUsercreate route route_bookStorewith service_name = '//BookConsulting/BookOrderService',     address = 'tcp://192.168.5.6:4020'create remote service binding bookStoreBindingto service '//BookConsulting/BookOrderService'with user = bookStoreUser--查询消息SELECT *FROM dbo.bookdistributionqueue