sql server service broker 测试 脚本

CREATE DATABASE logindb;




ALTER DATABASE logindb SET ENABLE_BROKER;
go


ALTER DATABASE logindb  SET TRUSTWORTHY ON;




go


CREATE DATABASE arealogindb;
go


ALTER DATABASE arealogindb  SET TRUSTWORTHY ON;


GO


##CREATE MASTER key


USE LOGINdb;
GO


CREATE MASTER KEY  
ENCRYPTION BY PASSWORD = 'heavstar' 
GO


SELECT name,is_master_key_encrypted_by_server FROM sys.databases where database_id=db_id();






USE areaLOGINdb;
GO


CREATE MASTER KEY  
ENCRYPTION BY PASSWORD = 'tonyyang' 
GO


SELECT name,is_master_key_encrypted_by_server FROM sys.databases where database_id=db_id();




Use logindb  
GO  
CREATE PROCEDURE[dbo].PrSync_UserInfoSynch 
AS
RETURN 0;
go


CREATE MESSAGE TYPE [//dream.com/UserInfo/UserMessageType] AUTHORIZATION [dbo] 
VALIDATION = WELL_FORMED_XML 
GO


CREATE CONTRACT [//dream.com/UserInfo/UserMessageContract] AUTHORIZATION [dbo] 
([//dream.com/UserInfo/UserMessageType] SENT BY ANY)
GO


CREATE QUEUE [dbo].[UserInfoQueue] WITH STATUS=ON,RETENTION=OFF,ACTIVATION(
STATUS=ON,PROCEDURE_NAME=PrSync_UserInfoSynch,MAX_QUEUE_READERS = 2,
EXECUTE AS N'dbo')
go


CREATE SERVICE [//dream.com/UserCenterDB/UserInfoServices] AUTHORIZATION dbo
ON QUEUE[dbo].[UserInfoQueue]
([//dream.com/UserInfo/UserMessageContract])




go
USE arealogindb
GO
CREATE TABLE[dbo].[UserInfo](
[LogID] INT NOT NULL Identity(1,1),
[ActionType] [varchar](32)NOT NULL,
[UserID] [int]  NOT NULL,
[UserName] [varchar](32)NOT NULL,
    [SEX] [tinyint],
[Time] [datetime] NOT NULL DEFAULT(getdate()),
[Msg] [xml] NOT NULL
)


go




CREATE PROCEDURE[dbo].[PrSync_UserInfoSynch] 
AS
DECLARE @message_body AS XML;
DECLARE @response     AS XML;
DECLARE @message_type AS sysname;
DECLARE @dialog       AS UNIQUEIDENTIFIER;
DECLARE @hDoc         AS INT;
DECLARE @rc           AS INT;


-- 定义用户信息的变量
DECLARE @ActionType NVARCHAR(128),
@UserID int,
@UserName varchar(32),
@Sex tinyint,
@ChvMsg NVARCHAR(128)


--  This procedure will just sit in a loop processing event messages in the queue until the queue is empty
WHILE(1 = 1)
BEGIN
BEGIN TRANSACTION
-- Receive the next available message
WAITFOR(
RECEIVE top(1)-- just handle one message at a time
         @message_type = message_type_name,
         @message_body = message_body,
         @dialog       =conversation_handle
FROM [dbo].UserInfoQueue
),TIMEOUT 2000  


-- If we didn't get anything, bail out
IF(@@ROWCOUNT= 0)
BEGIN
ROLLBACK TRANSACTION
BREAK;
END


/*--------------------------------------------------------------
-- Message handling logic based on the message type received
---------------------------------------------------------------*/
-- Handle End Conversation messages by ending our conversation also
IF(@message_type ='http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
PRINT'End Dialog for dialog # '+cast(@dialog AS nvarchar(40));
END CONVERSATION @dialog;
END
-- For error messages, just end the conversation.  In a real app, we
-- would log the error and do any required cleanup.
ELSE IF(@message_type ='http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
PRINT'Dialog ERROR dialog # '+cast(@dialog AS nvarchar(40));
END CONVERSATION @dialog;
END
ELSE
BEGIN
BEGIN TRY
SELECT
@ActionType=gameUser.item.value('(ActionType/text())[1]','nvarchar(128)'),
@UserID   = gameUser.item.value('(UserID/text())[1]','int'),
@UserName = gameUser.item.value('(UserName/text())[1]','nvarchar(32)'),
@Sex      = gameUser.item.value('(Sex/text())[1]','tinyint')
FROM @message_body.nodes('/GameUser[1]')AS gameUser(item);
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
-- Create a new transaction to send the response
BEGIN TRANSACTION
SET @response  =ERROR_MESSAGE();
-- ToDo - log the error
-- ToDo - poison message handling
END CATCH;
INSERT [UserInfo](ActionType,UserID,UserName,Sex, Msg)VALUES(@ActionType,@UserID,@UserName,@Sex,@message_body);


END-- IF message type
END CONVERSATION @dialog;
COMMIT TRANSACTION
END -- while
GO
CREATE MESSAGE TYPE [//dream.com/UserInfo/UserMessageType] AUTHORIZATION [dbo] 
VALIDATION = WELL_FORMED_XML 
GO


CREATE CONTRACT [//dream.com/UserInfo/UserMessageContract] AUTHORIZATION [dbo] 
([//dream.com/UserInfo/UserMessageType] SENT BY ANY)
GO


CREATE QUEUE[dbo].[UserInfoQueue] WITH STATUS=ON,RETENTION=OFF,ACTIVATION(
STATUS=ON,PROCEDURE_NAME=PrSync_UserInfoSynch,MAX_QUEUE_READERS = 2,
EXECUTE AS N'dbo')
go


CREATE SERVICE [//dream.com/GameAreaDB/UserInfoServices] AUTHORIZATION dbo
ON QUEUE[dbo].[UserInfoQueue]
([//dream.com/UserInfo/UserMessageContract])








USE logindb
go




DECLARE      @message_Text       AS NVARCHAR(MAX);
DECLARE      @message_body       AS XML;
DECLARE      @conversationHandle AS UNIQUEIDENTIFIER;
DECLARE      @partno             AS UNIQUEIDENTIFIER;
DECLARE @ServiceName        Nvarchar(128)
Set @ServiceName=N'//dream.com/GameAreaDB/UserInfoServices';
SET @message_Text =N'<GameUser>
<ActionType>register</ActionType>
<UserID>333302</UserID>
<UserName>testuser</UserName>
<Sex>1</Sex>'
SET @message_Text = @message_Text + N'<UserName>testaaa324</UserName>'
SET @message_Text = @message_Text + N'</GameUser>';
Set @message_body=@message_Text
BEGIN try
BEGIN DIALOG  @conversationHandle
FROM SERVICE    [//dream.com/UserCenterDB/UserInfoServices]
TO SERVICE      @ServiceName
ON CONTRACT     [//dream.com/UserInfo/UserMessageContract]
WITH
-- RELATED_CONVERSATION = @groupid,
-- RELATED_CONVERSATION_GROUP = @groupid,
ENCRYPTION=ON,--此处一定要注意是OFF,如果会话是加密的,则用ON
LIFETIME = 86400;


-- Send message
SEND ON CONVERSATION @conversationHandle 
MESSAGE TYPE [//dream.com/UserInfo/UserMessageType] (@message_body);


END CONVERSATION @conversationHandle;
END try
Begin Catch
End    Catch


SELECT * FROM sys.transmission_queue 


select * FROM dbo.UserInfoQueue


USE logindb
go


alter trigger  TR_USERINFO_INSERT ON dbo.UserInfo
AFTER INSERT
AS 
begin
SET NOCOUNT ON
DECLARE      @message_Text       AS NVARCHAR(MAX);
DECLARE      @message_body       AS XML;
DECLARE      @conversationHandle AS UNIQUEIDENTIFIER;
DECLARE      @partno             AS UNIQUEIDENTIFIER;
DECLARE @ServiceName        Nvarchar(128)
Set @ServiceName=N'//dream.com/GameAreaDB/UserInfoServices';
SELECT @message_Text =N'<GameUser>
<ActionType>'+ActionType+'</ActionType>
<UserID>'+CONVERT(VARCHAR(10),UserID)+'</UserID>
<UserName>'+UserName+'</UserName>
<Sex>'+CONVERT(VARCHAR(10),Sex)+'</Sex>'+ N'<UserName>'+UserName+'</UserName>' from INSERTED




--SET @message_Text = @message_Text + N'<UserName>testaaa324</UserName>'
SET @message_Text = @message_Text + N'</GameUser>';
Set @message_body=@message_Text
BEGIN try
BEGIN DIALOG  @conversationHandle
FROM SERVICE    [//dream.com/UserCenterDB/UserInfoServices]
TO SERVICE      @ServiceName
ON CONTRACT     [//dream.com/UserInfo/UserMessageContract]
WITH
-- RELATED_CONVERSATION = @groupid,
-- RELATED_CONVERSATION_GROUP = @groupid,
ENCRYPTION=OFF,--此处一定要注意是OFF,如果会话是加密的,则用ON
LIFETIME = 86400;


-- Send message
SEND ON CONVERSATION @conversationHandle 
MESSAGE TYPE [//dream.com/UserInfo/UserMessageType] (@message_body);


END CONVERSATION @conversationHandle;
END try
Begin Catch
End    Catch




end
-----------------------传输安全






USE master 
go
Select *from sys.symmetric_keys


SELECT name,is_master_key_encrypted_by_server FROM sys.databases WHERE database_id=DB_ID()


create MASTER KEY ENCRYPTION by password='heavstar'




CREATE CERTIFICATE SendHost_Master_Cert
With Subject='发送服务器Master端点证书',
START_DATE='2010-01-01',
EXPIRY_DATE='2078-01-01'




BACKUP CERTIFICATE SendHost_Master_Cert TO FILE='D:\work\Cert\SendHost_Master_Cert.cer'




CREATE ENDPOINT SendHost_EndPoint
STATE=STARTED AS TCP(LISTENER_PORT=32969,LISTENER_IP=ALL)
FOR SERVICE_BROKER(AUTHENTICATION=CERTIFICATE SendHost_Master_Cert)




--接收端
USE master 
go
Select *from sys.symmetric_keys
create MASTER KEY ENCRYPTION by password='heavstar'




SELECT name,is_master_key_encrypted_by_server FROM sys.databases WHERE database_id=DB_ID()




CREATE CERTIFICATE ReviceHost_Master_Cert
With Subject='接收服务器Master端点证书',
START_DATE='2010-01-01',
EXPIRY_DATE='2078-01-01'




BACKUP CERTIFICATE ReviceHost_Master_Cert TO FILE='C:\Cert\ReviceHost_Master_Cert.cer'




CREATE ENDPOINT ReviceHost_EndPoint
STATE=STARTED AS TCP(LISTENER_PORT=32969,LISTENER_IP=ALL)
FOR SERVICE_BROKER(AUTHENTICATION=CERTIFICATE ReviceHost_Master_Cert)




CREATE LOGIN ReviceHostLogin WITH PASSWORD='heavstar'
CREATE USER ReviceHostUser FOR LOGIN ReviceHostLogin


--导入接收服（ReviceHost）的MASTER证书
CREATE CERTIFICATE ReviceHost_Master_Cert
AUTHORIZATION ReviceHostUser
FROM FILE='D:\work\cert\ReviceHost_Master_Cert.cer';


E。授权接收服(ReviceHost)登陆名对本地MASTER端点的连接权限
GRANT CONNECT ON ENDPOINT:: SendHost_EndPoint TO ReviceHostLogin


F:创建路由表


Use loginDB
GO
CREATE ROUTE GameArea_Route WITH
SERVICE_NAME='//dream.com/GameAreaDB/UserInfoServices',
ADDRESS='TCP://10.143.133.219:32969'




--证书复制


CREATE LOGIN SendHostLogin WITH PASSWORD='heavstar'
CREATE USER SendHostUser FOR LOGIN SendHostLogin
go


CREATE CERTIFICATE SendHost_Master_Cert
AUTHORIZATION SendHostUser
FROM FILE='C:\Cert\SendHost_Master_Cert.cer'




GRANT CONNECT ON ENDPOINT:: ReviceHost_EndPoint TO SendHostLogin






Use arealogindb
GO
CREATE ROUTE SendHost_Route WITH
SERVICE_NAME='//dream.com/UserCenterDB/UserInfoServices',
ADDRESS='TCP://10.143.133.86:32969'




GRANT SEND ON SERVICE::[//dream.com/GameAreaDB/UserInfoServices] TO PUBLIC














------------------------------------------
--证书


USE logindb


go




CREATE USER CenterHost_OwnerCert_User without LOGIN




ALTER DATABASE logindb SET TRUSTWORTHY OFF


CREATE CERTIFICATE CenterHost_UserCenter_Cert AUTHORIZATION CenterHost_OwnerCert_User
WITH SUBJECT='发送服会话证书',
START_DATE='2013-01-01',
EXPIRY_DATE='2028-01-01'


--备份证书
BACKUP CERTIFICATE CenterHost_UserCenter_Cert TO FILE='D:\work\Cert\Send_UserCenter_Cert.cer'


--将Services服务置为用户的拥有者
ALTER  AUTHORIZATION ON SERVICE::[//dream.com/UserCenterDB/UserInfoServices] TO CenterHost_OwnerCert_User








CREATE USER ReviceHost_GameAreaDB_CertUser without Login


--G。导入接收服务器(ReviceHost)的证书，且授权此用户为证书的拥有者
CREATE CERTIFICATE ReviceHost_GameAreaDB_Cert AUTHORIZATION ReviceHost_GameAreaDB_CertUser
FROM FILE='D:\work\Cert\ReviceHost_GameAreaDB_Cert.cer'


--H。创建远程服务绑定
CREATE REMOTE SERVICE BINDING TO_GameAreaDB_BIND
TO SERVICE'//dream.com/GameAreaDB/UserInfoServices' WITH USER=ReviceHost_GameAreaDB_CertUser




----接收服务器


SELECT *FROM Sys.Symmetric_keys




CREATE USER ReviceHost_GameAreaDB_CertUser without Login


ALTER DATABASE arealogindb Set TRUSTWORTHY OFF
GO






CREATE CERTIFICATE ReviceHost_GameAreaDB_Cert AUTHORIZATION ReviceHost_GameAreaDB_CertUser
WITH SUBJECT='接收服务器会话证书',
START_DATE='2010-01-01',
EXPIRY_DATE='2078-01-01'
--备份证书
BACKUP CERTIFICATE ReviceHost_GameAreaDB_Cert TO FILE='C:\Cert\ReviceHost_GameAreaDB_Cert.cer'
GO




ALTER AUTHORIZATION ON SERVICE::[//dream.com/GameAreaDB/UserInfoServices] TO ReviceHost_GameAreaDB_CertUser
GO
CREATE USER SendHost_UserCenterDB_CertUser without LOGIN






CREATE CERTIFICATE SendHost_UserCenterDB_Cert AUTHORIZATION SendHost_UserCenterDB_CertUser
FROM FILE='C:\Cert\Send_UserCenter_Cert.cer'
go




GRANT SEND ON SERVICE::[//dream.com/GameAreaDB/UserInfoServices] TO SendHost_UserCenterDB_CertUser