实现远程服务器的Service Broker

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

--在实例1上
use master
go

if exists(select 1 from sys.databases where name = 'bookstore')
	drop database bookstore
else
	create database bookstore
go

--开启service broker
alter database bookstore set enable_broker
--指明 SQL Server 实例是否信任该数据库以及其中的内容。
alter database bookstore set trustworthy on 


use bookstore
go

create message type [//BookConsulting/SendBookOrder]
validation = well_formed_xml
go

create message type [//BookConsulting/BookOrderReceived]
validation = well_formed_xml
go


create contract [//BookConsulting/BookOrderContract]
(
	[//BookConsulting/SendBookOrder] sent by initiator,
	[//BookConsulting/BookOrderReceived] sent by target
)
go



create queue BookStoreQueue
with status = on


create service [//BookConsulting/BookOrderService]
on queue dbo.BookStoreQueue
(
	[//BookConsulting/BookOrderContract]
)

--在实例2上
use master
go

if exists(select 1 from sys.databases where name = 'bookdistribution')
	drop database bookdistribution
else
	create database bookdistribution
go


alter database bookdistribution set enable_broker

alter database bookdistribution set trustworthy on


use bookdistribution
go

create message type [//BookConsulting/SendBookOrder]
validation = well_formed_xml
go

create message type [//BookConsulting/BookOrderReceived]
validation = well_formed_xml
go


create contract [//BookConsulting/BookOrderContract]
(
	[//BookConsulting/SendBookOrder] sent by initiator,
	[//BookConsulting/BookOrderReceived] sent by target
)
go



create queue BookDistributionQueue
with status = on


create service [//BookConsulting/BookDistributionService]
on queue dbo.BookDistributionQueue
(
	[//BookConsulting/BookOrderContract]
)

--实例1
use master 
go


--1.删除已经存在的数据库主密钥
drop master key


--2.创建数据库主密钥
create master key encryption by password = '123456!@#'


--3.创建证书
create certificate bookMasterCert
with subject = 'book Transport Security Service Broker',
	 expiry_date = '2050-12-31' 


--4.备份证书
backup certificate bookMasterCert
to file = 'd:\bookMasterCert.cer'
go

select * from sys.database_mirroring_endpoints 

--5.创建端点
create endpoint service_broker_book_endpoint
state = 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_login
with password = 'service_broker_login123'


--7.创建数据库用户名
create user service_broker_user
for login service_broker_login


--8.授予数据库用户可以连接端点
grant connect on endpoint::service_broker_book_endpoint 
              to service_broker_login


--9.通过另一个实例复制到本地服务器上的证书文件,来创建证书
create certificate bookDistributionMasterCert
authorization service_broker_user
from file = 'd:\bookDistributionMasterCert.cer'
go

--实例2
use master 
go


--删除数据库主密钥
drop master key

create master key encryption by password = '123456&^%'


create certificate bookDistributionMasterCert
with subject = 'bookDistribution Transport Security Service Broker',
	 expiry_date = '2080-12-31' 


backup certificate bookDistributionMasterCert
to file = 'e:\bookDistributionMasterCert.cer'


create endpoint service_broker_bookdistribution_endpoint
state = started
as tcp (listener_port = 4021)
for service_broker (
						authentication = certificate bookDistributionMasterCert,
						encryption = required
                   )


create login service_broker_login
with password = 'service_broker_login123'


create user service_broker_user
for login service_broker_login


grant connect on endpoint::service_broker_bookdistribution_endpoint 
              to service_broker_login


use master 
go
create certificate bookMasterCert
authorization service_broker_user
from file = 'd:\bookMasterCert.cer'
go

--实例1
use bookstore
go

--drop master key
--1.创建数据库主密钥
create master key encryption by password = '123456!@#'  
--查看创建的主密钥
select * from sys.symmetric_keys

--2.创建证书,这里可以给当前数据库用户创建多个证书,不会有影响
--当接收到其他服务器传送过来的消息时,可以用这个证书来解密消息
create certificate BookStoreCert
with subject = 'BookStore service broker cert',
     expiry_date = '2080-12-31'


--3.备份证书
backup certificate bookstorecert
to file = 'd:\bookstorecert.cer'
go


--4.创建数据库用户,此用户只可以有一个证书
create user bookDistributionUser
without login
go


--5.通过从另一个实例复制过来的证书,来创建证书,并指定所有者为此用户
create certificate bookDistributionCert
authorization bookDistributionUser        --此用户只能拥有一个证书,
                                          --在发送消息时会用这个证书来加密消息
from file = 'd:\bookDistributionCert.cer'


--6.授予此用户名在某个服务上发送的权限
grant send on service::[//BookConsulting/BookOrderService] to bookDistributionUser
go


--7.创建路由
create route route_bookDistribution
with service_name = '//BookConsulting/BookDistributionService',
     address = 'tcp://192.168.5.9:4021'


--8.创建远程绑定
create remote service binding bookDistributionBinding
to service '//BookConsulting/BookDistributionService'
with user = bookDistributionUser


--9.开始会话,发送消息
declare @conversation_handler uniqueidentifier
declare @order_msg xml;


begin dialog conversation @conversation_handler
from 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_handler
message type [//BookConsulting/SendBookOrder]
(@order_msg);

--启用对话安全模式
--实例2
use bookdistribution
go

create master key encryption by password = '123456&^%'  


--当接收到对方发送的消息后,用此证书来解密
create certificate BookDistributionCert
with subject = 'BookDistribution service broker cert',
     expiry_date = '2080-12-31'


backup certificate bookDistributioncert
to file = 'd:\bookDistributioncert.cer'


create user bookStoreUser
without login


--在发送之前,用此证书来加密消息
create certificate bookStoreCert
authorization bookStoreUser
from file = 'd:\bookStoreCert.cer'


grant send on service::[//BookConsulting/BookDistributionService] to bookStoreUser


create route route_bookStore
with service_name = '//BookConsulting/BookOrderService',
     address = 'tcp://192.168.5.6:4020'


create remote service binding bookStoreBinding
to service '//BookConsulting/BookOrderService'
with user = bookStoreUser


--查询消息
SELECT *
FROM dbo.bookdistributionqueue