实现目标：从一台服务器（1.23）发出消息，通过中转服务器（1.30），ID为单数的进入到单服务器（1.27、1.28），ID为双数的进入到双服务器（1.32、1.33）。
前提条件：创建一个域用户，并赋予相应的权限（[IDC1234\DBAdmins]）
实验环境：主服务器为1.23 ，转发服务器为：1.30，从属服务器为：1.27、1.28（单号），1.32,1.33（双号）；服务器配置均为：windows2003+sp3+Sqlserver2005+sp2；都为域服务器。
use master
go
--创建基于域用户的ENDPOINT
CREATE ENDPOINT InitiatorSSBEndPoint
STATE = STARTED
AS TCP(LISTENER_PORT = 4022)
FOR SERVICE_BROKER(AUTHENTICATION = windows , ENCRYPTION = REQUIRED)
--连接权限
GRANT CONNECT ON ENDPOINT::InitiatorSSBEndPoint TO [IDC1234\DBAdmins]
USE Master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'InitiatorDB')
DROP DATABASE InitiatorDB
GO
CREATE DATABASE InitiatorDB on(name='InitiatorDB',filename='d:\SBTestDB\InitiatorDB.mdf')
log on(name='InitiatorDB_Log',filename='d:\SBTestDB\InitiatorDB_Log.ldf')
GO
ALTER DATABASE [InitiatorDB] ADD FILEGROUP [QueueGroup]
GO
ALTER DATABASE [InitiatorDB] ADD FILE ( NAME = N'QueueList', FILENAME = N'd:\SBTestDB\QueueList.mdf' ,
SIZE = 10240KB , FILEGROWTH = 1024KB ) TO FILEGROUP [QueueGroup]
---------------检测是否开启Service Broker------------------
USE InitiatorDB
GO
SELECT Service_Broker_Guid, is_broker_enabled, is_trustworthy_on
FROM sys.databases WHERE database_id = DB_ID()
USE InitiatorDB
GO
-----记录消息处理情况------------
CREATE TABLE dbo.ProcessHistory
(
RowID int IDENTITY(1,1) NOT NULL
,Process varchar(60) NOT NULL
,StatusDate datetime NOT NULL
,Status varchar(50) NULL
,MessageText xml NULL
,SPID int NULL
,UserName varchar(60) NULL
CONSTRAINT PK_ProcessHistory PRIMARY KEY CLUSTERED(RowID)
)
--------记录处理过的队列信息-------
create table QueueErrorInfo
( id int identity(1,1) not null,
conversation_id UNIQUEIDENTIFIER,
errorinfo varchar(3000),
indate datetime
)
--------service broker 测试表----------------
create table tablesb
( id int,
sbname varchar(100)
)
CREATE MESSAGE TYPE [/MTSql/GetSqlInfo]
VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [/MTSql/GetSqlInfoResult]
VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [/MTExec/GetExecInfo]
VALIDATION = WELL_FORMED_XML
GO
CREATE MESSAGE TYPE [/MTExec/GetExecInfoResult]
VALIDATION = WELL_FORMED_XML
GO
CREATE CONTRACT [/CTGetInfo/SqlServer]
(
[/MTSql/GetSqlInfo] SENT BY INITIATOR
,[/MTExec/GetExecInfo] SENT BY INITIATOR
,[/MTSql/GetSqlInfoResult] SENT BY TARGET
,[/MTExec/GetExecInfoResult] SENT BY TARGET
)
GO
IF OBJECT_ID('dbo.CheckResponseFromTarget') IS NOT NULL
DROP PROC dbo.CheckResponseFromTarget
GO
Create PROCEDURE dbo.CheckResponseFromTarget
AS
DECLARE
@message_type_name nvarchar(256)
,@xmlmessage_body xml
,@MessageFromTarget varchar(255)
,@ProcedureName varchar (60)
,@conversation_handle UNIQUEIDENTIFIER
,@ErrorNumber int
,@ErrorMessage nvarchar(4000)
,@ErrSeverity int
,@ErrorLine int
,@comment nvarchar(4000)
,@Id_list nvarchar(max)
SET @ProcedureName = OBJECT_NAME(@@PROCID)
BEGIN TRY
WHILE (1 = 1)
BEGIN
-- Receive the next available messages
BEGIN TRANSACTION
WAITFOR
(
RECEIVE TOP(1) -- just handle one message at a time
@xmlmessage_body = CASE WHEN validation = 'X'
THEN CAST(message_body AS XML)
ELSE CAST(N'<none/>' AS XML)
END
,@message_type_name = message_type_name
,@conversation_handle = conversation_handle
FROM dbo.ReceiveAckQueue -- This is the name of the queue from which you want to pick up the messages.
), TIMEOUT 3000 -- if the queue is empty for 3 second, give up and go away.
----------------------------------------------------------------------------------
-- If we didn't get anything or if there is any error, bail out
IF (@@ROWCOUNT = 0 OR @@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
ELSE
BEGIN
IF @message_type_name = '/MTSql/GetSqlInfoResult'
BEGIN
select @Id_list=CAST(@xmlmessage_body.query('/message/text()')AS NVARCHAR(MAX))
-----------------------------------------------------------------
--- Your code go here to process the received message.
-----------------------------------------------------------------
--exec ('insert into BizOffer_queue SELECT * FROM BizOffer with(nolock) where id in ('+@Id_list+')');
---Let's insert what we got into processhistory table.
INSERT dbo.ProcessHistory(Process, StatusDate, Status, MessageText, SPID, UserName)
SELECT @ProcedureName, GETDATE(), 'Success', @xmlmessage_body, @@SPID, SUSER_NAME()
END
ELSE
IF @message_type_name = '/MTExec/GetExecInfoResult'
BEGIN
-----------------------------------------------------------------
--- Your code go here to process the received message.
-----------------------------------------------------------------
---Let's insert what we got into processhistory table.
INSERT dbo.ProcessHistory(Process, StatusDate,Status, MessageText, SPID, UserName)
SELECT @ProcedureName, GETDATE(), 'Success', @xmlmessage_body, @@SPID, SUSER_NAME()
END
ELSE
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
-- an end dialog message, end the conversation.
BEGIN
END CONVERSATION @conversation_handle
---Let's insert what we got into processhistory table.
INSERT dbo.ProcessHistory(Process, StatusDate, Status, MessageText, SPID, UserName)
SELECT @ProcedureName, GETDATE(), 'END DIALOG', @xmlmessage_body, @@SPID, SUSER_NAME()
END
ELSE
IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error')
-- If the message_type_name indicates that the message is an error
BEGIN
-----------------------------------------------------------------
--- Your code go here to handle the error and do some type of notification (email etc)
-----------------------------------------------------------------
---Let's insert what we got into processhistory table.
INSERT dbo.ProcessHistory(Process, StatusDate, Status, MessageText, SPID, UserName)
SELECT @ProcedureName, GETDATE(), 'Error', @xmlmessage_body, @@SPID, SUSER_NAME()
END
END
COMMIT TRAN
END --WHILE END here.
END TRY
BEGIN CATCH
ROLLBACK TRAN
SELECT @ErrorNumber = ERROR_NUMBER(), @ErrorMessage = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY(), @ErrorLine = ERROR_LINE()
SET @comment = 'Error Number: '+ CAST(@ErrorNumber AS varchar(25)) + ', Error Message: ' + @ErrorMessage +
' Error Severity: ' + CAST(@ErrSeverity AS varchar(25)) + ' Line Number: ' + CAST(@ErrorLine AS varchar(25))
---Let's insert what we got into processhistory table.
INSERT dbo.ProcessHistory(Process, StatusDate, Status, MessageText, SPID, UserName)
SELECT @ProcedureName, GETDATE(), 'Error', @comment, @@SPID, SUSER_NAME()
exec DealWithErrorMsg @conversation_handle,@comment;
END CATCH
GO
------------清除垃圾数据并激活队列--------------
Create proc DealWithErrorMsg
(@conversation_handle UNIQUEIDENTIFIER,
@ErrorInfo varchar(3000))
as
Begin
END CONVERSATION @conversation_handle
insert into QueueErrorInfo(conversation_id,errorinfo,indate)
values(@conversation_handle,@ErrorInfo,getdate())
--激活队列
ALTER QUEUE ReceiveAckQueue WITH STATUS = ON,RETENTION = OFF,ACTIVATION(STATUS = ON)
End
Create QUEUE dbo.ReceiveAckQueue WITH STATUS = ON,RETENTION = OFF,ACTIVATION
(STATUS = ON,PROCEDURE_NAME = dbo.CheckResponseFromTarget
,MAX_QUEUE_READERS = 10,EXECUTE AS SELF) ON 'QueueGroup'
GO
---add queue----------------------------
Create QUEUE dbo.ReceiveAckQueue_Sub WITH STATUS = ON,RETENTION = OFF,ACTIVATION
(STATUS = ON,PROCEDURE_NAME = dbo.CheckResponseFromTarget
,MAX_QUEUE_READERS = 10,EXECUTE AS SELF) ON 'QueueGroup'
GO
---------创建SERVICE--queue---CONTRACT---------------------
CREATE SERVICE InitiatorService
ON QUEUE dbo.ReceiveAckQueue ([/CTGetInfo/SqlServer])
---add SERVICE-------------------------------
CREATE SERVICE InitiatorService_Sub
ON QUEUE dbo.ReceiveAckQueue_Sub ([/CTGetInfo/SqlServer])
GRANT SEND ON SERVICE::InitiatorService TO PUBLIC
GRANT SEND ON SERVICE::InitiatorService_Sub TO PUBLIC
CREATE ROUTE RouteToTargetServer30
WITH
SERVICE_NAME = 'TargetService30'
,ADDRESS = 'TCP://192.168.1.30:4022'
到这里主服务器（1.23）的配置已经完成。
