今天帮一个朋友查下关于消息队列的资料,看后认为不错,给大家贴出来!
引用
Server Broker实现消息的发送
(一)同一实例同一数据库下的消息发送
1.准备工作:
1.1创建数据库ssbDemo
use master
go
if exists( select top 1 1
from sys.sysdatabases with(nolock)
where name=N'ssbDemo'
)
begin
drop database ssbDemo
end
create database ssbDemo
go
1.2 允许Server Broker
--check database broker states
declare @is_broker_enabled int
select top 1 @is_broker_enabled=is_broker_enabled
from sys.databases with (nolock)
where database_id=db_id(N'ssbDemo')
if @is_broker_enabled<>1
begin
--Enable SSB
alter database ssbDemo
set enable_broker
alter database ssbDemo
set trustworthy on
end
1.3创建Master Key
use ssbDemo
go
create master key
encryption by password = 'Cw24@$g'
2.创建消息
use ssbDemo
go
--Message
if exists (select top 1 1
from sys.service_message_types with(nolock)
where name='HelloWorldRequest')
begin
drop message type HelloWorldRequest;
end
go
create message type [HelloWorldRequest] VALIDATION=NONE
if exists (select top 1 1
from sys.service_message_types with(nolock)
where name='HelloWorldRespone')
begin
drop message type HelloWorldRespone;
end
go
create message type [HelloWorldRespone] VALIDATION=NONE
3.创建合约
--Contract
if exists (select top 1 1
from sys.service_contracts with(nolock)
where name='HelloWorldContract')
begin
drop contract HelloWorldContract;
end
go
create contract [HelloWorldContract]
(
[HelloWorldRequest] sent by initiator
,[HelloWorldRespone] sent by target
)
4.创建队列和基于队列的服务
--Queue
if object_id('dbo.HelloWorldTargerQueue') is not null and
exists(select top 1 1
from sys.objects with (nolock)
where object_id=object_id('dbo.HelloWorldTargerQueue')
and type='SQ')
begin
drop queue dbo.HelloWorldTargerQueue
end
go
create queue dbo.[HelloWorldTargetQueue]
if exists (select top 1 1
from sys.services with(nolock)
where name='HelloWorldRequestService')
begin
drop service HelloWorldRequestService;
end
go
create service [HelloWorldRequestService] on queue [HelloWorldTargetQueue]
(
[HelloWorldContract]
)
if object_id('dbo.HelloWorldInitiatorQueue') is not null and
exists(select top 1 1
from sys.objects with (nolock)
where object_id=object_id('dbo.HelloWorldInitiatorQueue')
and type='SQ'
)
begin
drop queue dbo.HelloWorldInitiatorQueue
end
go
Create queue dbo.[HelloWorldInitiatorQueue]
if exists (select top 1 1
from sys.services with(nolock)
where name='HelloWorldResponseService')
begin
drop service HelloWorldResponseService;
end
go
create service [HelloWorldResponeService] on queue [HelloWorldInitiatorQueue]
(
[HelloWorldContract]
)
5.发送消息(Server Broker的消息发送,必须在事务中实现)
--send message
use ssbDemo
go
set nocount on
declare @conversionHandle uniqueidentifier
begin try
begin tran
--begin dialog to server
begin dialog @conversionHandle
from service [HelloWorldResponeService]
to service N'HelloWorldRequestService'
on contract [HelloWorldContract]
with encryption=off,lifetime=600;
--Send message
send on conversation @conversionHandle
message type [HelloWorldRequest] (N'Hello world')
commit
end try
begin catch
--get the the error info
select error_message()
end catch
6.接受消息(同样消息的接受也必须在事务中)
--receive message from raget queue
set nocount on
declare @conversionHandle uniqueidentifier
declare @message_body nvarchar(max)
declare @message_type_name sysname
--begin transaction
begin transaction
waitfor (receive top (1)--only a piece of message
@message_type_name = message_type_name--Receiving message type
,@conversionHandle=conversation_Handle--Dialog identifier
,@message_body=message_body
from [HelloWorldTargetQueue])
--it is a HelloWorldRequset
if @message_type_name=N'HelloWorldRequest'
begin
send on conversation @conversionHandle
message type HelloWorldRespone
(N'Hello from '+@@servername)
end conversation @conversionHandle
end
--commit
commit tran
go
7.检验
7.1检查目标队列
use ssbDemo
go
--check the target queue
select * from [HelloWorldTargetQueue] with (nolock)
--go
----convert the message to nvarchar
select cast(message_body as nvarchar(max))
from [HelloWorldTargetQueue]
go
7.2检查消息接受情况
--Check receiving whether sucessfully or fail
select cast(message_body as nvarchar(max))
from [HelloWorldTargetQueue]
7.3检查消息的反应情况
--Checking response message from initiator queue
select cast(message_body as nvarchar(max))
from [HelloWorldInitiatorQueue]