两个服务器上SQLServer实例之间配置Service Broker示例
1. ENABLE OUTBOUND CONNECTIONS ON THE FIRST SERVER.
1.1. Create the SQL service master key.
On the master database, create the SQL service master key. This can be used to initial transparent security.
USE MASTER;
GO
--MAKE SURE MASTER HAD MASTER KEY
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '???????';
GO
1.2. Make a certificate for this server instance.
Create a certificate in master, the public key of this cert will be used for authentication purposes it is a self-signed cert.
CREATE CERTIFICATE TRPCERT_SERVER1
WITH SUBJECT = 'TRPCERT_SERVER1',
START_DATE = '10/30/2006',
EXPIRY_DATE = '10/30/2016' --SHOULD BE LATER THAN START DATE.
GO
1.3. Backup the certificate and copy it to the second server.
Backup the public key of the certificate to a file. This file will then be exchanged with the other instance , The file needs to be copied to the second server.
BACKUP CERTIFICATE TRPCERT_SERVER1
TO FILE = 'C:\DD11\TRPCERT_SERVER1.CER';
GO
1.4. Create a mirroring endpoint for server broker using the certificate.
You need to create an endpoint in order to enable communication outside of this instance.if you configure SQL Server 2005, you can use the same step.
CREATE ENDPOINT SERVER1_WH_SSB
STATE = STARTED
AS TCP
( LISTENER_PORT = 4022)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TRPCERT_SERVER1,
ENCRYPTION = REQUIRED
);
GO
2. ENABLE OUTBOUND CONNECTIONS ON THE SECOND SERVER
2.1. On the master database, create the database master key.
USE MASTER;
GO
--MAKE SURE MASTER HAD MASTER KEY
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '???????';
GO
2.2. New one certificate on the Mirror server instance.
Create a certificate in master database, the public key of this certificate will be used for authentication purposes when the remote instance tries to connect to this server.
CREATE CERTIFICATE TRPCERT_SERVER2
WITH SUBJECT = 'TRANSPORT CERTIFICATE FOR SERVER2',
START_DATE = '10/1/2006',
EXPIRY_DATE='10/1/2016'
GO
2.3. Backup the certificate and copy it to other server.
Backup the public key of the certificate to a file, this file will then be exchanged with the other instance. The file needs to be copied to first server.
BACKUP CERTIFICATE TRPCERT_SERVER2
TO FILE = 'C:\DD11\TRPCERT_SERVER2.CER';
GO
2.4. Create the mirroring endpoint for the server broker instance.
You need to create an endpoint in order to enable communication outside of this instance.
CREATE ENDPOINT SERVER2_WH_SSB
STATE = STARTED
AS TCP
( LISTENER_PORT = 4022)
FOR SERVICE_BROKER
(
AUTHENTICATION = CERTIFICATE TRPCERT_SERVER2,
ENCRYPTION = REQUIRED
);
GO
3. ENABLE INBOUND CONNECTIONS ON THE FIRST SERVER
3.1. Create a login on first server.
Create a login and a user which you will assign a public key from the certificate in the remote master database.
USE MASTER;
GO
IF NOT EXISTS (SELECT * FROM SYS.SERVER_PRINCIPALS WHERE NAME = N'WH10DBO')
BEGIN
CREATE LOGIN WH10DBO
WITH PASSWORD=N'???????',DEFAULT_DATABASE=MASTER, DEFAULT_LANGUAGE=[US_ENGLISH], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
END
GO
3.2. Create a user for the login.
CREATE USER WH10DBO FROM LOGIN WH10DBO
GO
3.3. Grant connect permission on the endpoint to the login.
You must grant connect permission, otherwise you will get error when you are using the profiler90.
GRANT CONNECT ON ENDPOINT::SERVER1_WH_SSB TO WH10DBO
GO
3.4. Associate the mirror server certificate with the user.
CREATE CERTIFICATE TRPCERT_SERVER2
AUTHORIZATION WH10DBO
FROM FILE = 'C:\DD11\TRPCERT_SERVER2.CER';
GO
4. ENABLE INBOUND CONNECTIONS ON THE SECOND SERVER
4.1. Create a login for the first server.
Create a login and a user which you will assign a public key from the certificate in the remote master database to it.
USE MASTER;
GO
CREATE LOGIN SSBDBO WITH PASSWORD = '??????';
GO
4.2. Create a user from the login.
CREATE USER SSBDBO FROM LOGIN SSBDBO;
GO
4.3. Grant connection permission to the user.
GRANT CONNECT ON ENDPOINT::SERVER2_WH_SSB
TO SSBDBO --CAN ONLY GRANT TO USER OR GROUP.CAN’T BE A LOGIN.THIS IS WHY WE CREATE USER ABOVE.
GO
4.4. Associate a certificate with the user we created.
Create a certificate from the public certificate of first server. The certificate you backed is only the public key.
CREATE CERTIFICATE TRPCERT_SERVER1
AUTHORIZATION SSBDBO
FROM FILE = 'C:\DD11\TRPCERT_SERVER1.CER';
GO
5. SET THE DATABASE OUTBOUND FOR THE FIRST SERVER
5.1. Create a database which will be configured as service broker database.
CREATE DATABASE SSB
GO
5.2. Create database master key for encrypting our certificate.
USE SSB;
GO
--SET MASTER KEY
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '??????*';
GO
5.3. Create certificate for transport security.
Here we create a self-signed certificate.
CREATE CERTIFICATE DLGCERT_SERVER1
WITH SUBJECT = 'DLGCERT_CENTER',START_DATE = '10/30/2006',
EXPIRY_DATE='10/30/2016' ACTIVE FOR BEGIN_DIALOG = ON;
GO
5.4. Backup the certificate.
Backup the public key of the certificate to a file, and then copy it to second server.
BACKUP CERTIFICATE DLGCERT_SERVER1
TO FILE = 'C:\DD11\DLGCERT_SERVER1.CER';
GO
6. SET THE DATABASE OUTBOUND FOR SECOND SERVER.
6.1. Create the database.
CREATE DATABASE SSB
GO
6.2. Create the database master key.
This key then will be used to encryption our certificate.
USE SSB;
GO
--SET MASTER KEY
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '???????*';
GO
6.3. Create certificate for transport security.
Here you create a self-signed certificate, which will be encrypted by the above key.
CREATE CERTIFICATE DLGCERT_SERVER2
WITH SUBJECT = 'DLGCERT_SERVER2',START_DATE = '10/1/2006',
EXPIRY_DATE='10/1/2016' ACTIVE FOR BEGIN_DIALOG = ON;
GO
6.4. Backup the Certificate and copy to the first server.
Backup the public key of the certificate to a file this will then be exchanged with the other instance make sure that the path you define below can be accessed by sql server. The file needs to be copied to server 2
BACKUP CERTIFICATE DLGCERT_SERVER2
TO FILE = 'C:\DD11\DLGCERT_SERVER2.CER';
GO
7. SET THE DATABASE INBOUND FOR THE FIRST SERVER.
7.1. Create a user to assign a public key.
Create a user which you will assign a public key from the certificate in the remote database.
CREATE USER WH10DBO FROM LOGIN WH10DBO;
GO
7.2. Associate the second server db’s public cert to the use.
You create a certificate from server 2's public certificate and assign it to the user created above
CREATE CERTIFICATE DLGCERT_SERVER2
AUTHORIZATION WH10DBO
FROM FILE = 'C:\DD11\DLGCERT_SERVER2.CER';
GO
8. SET THE DATABASE INBOUND FOR SERVER2
8.1. Create a user that will own the public key from second server db.
Create a user which you eventually will assign a public key from the cert in the remote db
CREATE USER SSBDBO FROM LOGIN SSBDBO;
GO
8.2. Associate the related public key with the user.
CREATE CERTIFICATE DLGCERT_SERVER1
AUTHORIZATION SSBDBO
FROM FILE = 'C:\DD11\DLGCERT_SERVER1.CER';
9. SET THE DATABASE SERVICE BROKER METADATA FOR SERVER1
9.1. Create two message types.
USE SSB ;
GO
-- WE NEED TWO MESSAGE TYPES
CREATE MESSAGE TYPE [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType]
VALIDATION = WELL_FORMED_XML;
GO
9.2. Create the message contract.
CREATE CONTRACT [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]
(
[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType] SENT BY INITIATOR,
[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType] SENT BY TARGET
);
GO
9.3. Create the queue.
Note: At this stage we do not care about activation, so the queue can’t auto process.
CREATE QUEUE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]
WITH STATUS = ON;
GO
9.4. Create a service.
CREATE SERVICE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService]
ON QUEUE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]
(
[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]
);
GO
9.5. Create a route to the remote service.
CREATE ROUTE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllRoute]
WITH SERVICE_NAME = 'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService',
ADDRESS = 'TCP://SERVER2:4022';
GO
9.6. Create a remote service binding.
We'll do encrypted dialogs we need a remote service binding
CREATE REMOTE SERVICE BINDING [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllServiceRemoteServiceBinding]
TO SERVICE 'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService'
WITH USER = WH10DBO,
ANONYMOUS=OFF
GO
9.7. Grant the user send rights on the service.
GRANT SEND ON SERVICE::[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService] TO WH10DBO;
GO
10. SET THE DATABASE SERVICE BROKER METADATA FOR SERVER2
10.1. Create two message types.
USE SSB ;
GO
CREATE MESSAGE TYPE [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType]
VALIDATION = WELL_FORMED_XML;
GO
10.2. Create the message contract.
CREATE CONTRACT [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]
([http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType] SENT BY INITIATOR,
[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType] SENT BY TARGET
);
GO
10.3. Create the queue.
Create the queue, at this stage we do not care about activation.
CREATE QUEUE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]
WITH STATUS = ON;
GO
10.4. Create the Service.
CREATE SERVICE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService]
ON QUEUE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]
([http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]);
GO
10.5. Create a route to the remote service.
Create a route to the remote service.
CREATE ROUTE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllRoute]
WITH
SERVICE_NAME = 'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService',
ADDRESS = 'TCP://SERVER1:4022';
GO
10.6. Create a remote service binding.
As we'll be doing encrypted dialogs we need a remote service binding
CREATE REMOTE SERVICE BINDING [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllServiceRemoteServiceBinding]
TO SERVICE 'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService'
WITH USER = SSBDBO,
ANONYMOUS=OFF
GO
10.7. Grant the user send rights on the service.
GRANT SEND ON SERVICE::[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService] TO SSBDBO;
GO
11. CONFIGURE THE LOG TABLE ON BOTH SERVERS
CREATE TABLE [DBO].[SUCCESS_RECORD](
[CONVERSATION_HANDLE] [UNIQUEIDENTIFIER] NOT NULL,
[MESSAGE_TYPE_NAME] [SYSNAME] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NOT NULL,
[MESSAGE_BODY] [XML] NULL,
[INDATE] [DATETIME] NULL DEFAULT (GETDATE()),
[MESSAGENAMESPACE] [VARCHAR](100) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NULL,
[MESSAGEACTION] [VARCHAR](20) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NULL,
[PROCESSTYPE] [CHAR](1) COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS NULL DEFAULT ('N'),
PRIMARY KEY CLUSTERED
(
[CONVERSATION_HANDLE] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [DBO].[EXCEPTION_RECORD](
[MESSAGE_BODY] [XML] NULL,
[INDATE] [DATETIME] NOT NULL CONSTRAINT [DF_EXCEPTION_RECORD_INDATE] DEFAULT (GETDATE())
) ON [PRIMARY]
GO
CREATE TABLE [DBO].[SENT_RECORD](
[MESSAGE_BODY] [XML] NULL,
[INDATE] [DATETIME] NULL CONSTRAINT [DF_SENT_RECORD_INDATE] DEFAULT (GETDATE())
) ON [PRIMARY]
GO
12. SET THE DATABASE SERVICE BROKER CONVESATION
12.1. Create Send message on server1.
USE SSB
GO
CREATE PROC [DBO].[UP_SSB_SENDMSG]
@SERVICENAME SYSNAME,
@MSG XML
AS
BEGIN
--START THE DIALOG AND SEND A MESSAGE
--UNCOMMENT FROM HERE UNTIL THE FOLLOWING GO STATEMENT AND RUN
DECLARE @H UNIQUEIDENTIFIER --CONVERSATION HANDLE
--DECLARE @MSG XML; --WILL HOLD THE MESSAGE
BEGIN TRY
BEGIN DIALOG CONVERSATION @H
FROM SERVICE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService]
-- TO SERVICE 'S_RECSERVICE'
--TO SERVICE 'S_RECSERVICE_S8SQL01'
TO SERVICE @SERVICENAME
ON CONTRACT [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]
--WITH ENCRYPTION=OFF;
;
--SET @MSG = '<HELLO00/>';
SEND ON CONVERSATION @H
MESSAGE TYPE [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]
(@MSG);
END TRY
BEGIN CATCH
BEGIN
INSERT INTO SSB.DBO.DISPATH_ERRORRECORD (MESSAGE_BODY) VALUES (@MSG)
--SELECT [MESSAGE]='MESSAGE CHANNEL NOT DEFINED IN E3BZT.SSB_CENTER'
END
END CATCH
END
12.2. Receive message on server2.
CREATE PROC [DBO].[UP_SSB_SENDMSG]
@SERVICENAME SYSNAME,
@MSG XML
AS
BEGIN
--START THE DIALOG AND SEND A MESSAGE
--UNCOMMENT FROM HERE UNTIL THE FOLLOWING GO STATEMENT AND RUN
DECLARE @H UNIQUEIDENTIFIER --CONVERSATION HANDLE
--DECLARE @MSG XML; --WILL HOLD THE MESSAGE
BEGIN TRY
BEGIN DIALOG CONVERSATION @H
FROM SERVICE [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService]
-- TO SERVICE 'S_RECSERVICE'
--TO SERVICE 'S_RECSERVICE_S8SQL01'
TO SERVICE @SERVICENAME
ON CONTRACT [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]
--WITH ENCRYPTION=OFF;
;
--SET @MSG = '<HELLO00/>';
SEND ON CONVERSATION @H
MESSAGE TYPE [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]
(@MSG);
--CHECK IF THE TRANSMISSION_STATUS COLUMN IN SYS.TRANSMISSION_QUEUE
--HAS ANY MESSAGES, IF SO SOMETHING IS NOT ENTIRELY CORRECT
END TRY
BEGIN CATCH
BEGIN
INSERT INTO SSB.DBO.DISPATH_ERRORRECORD (MESSAGE_BODY) VALUES (@MSG)
--SELECT [MESSAGE]='MESSAGE CHANNEL NOT DEFINED IN E3BZT.SSB_CENTER'
END
END CATCH
END
13. RECEIVE STORE PROCEDURE
CREATE PROCEDURE [DBO].[UP_SSB_PROCESSINCOMINGMSG]
AS
BEGIN
SET NOCOUNT ON
DECLARE @MESSAGETYPE SYSNAME,
@CONVERSATIONHANDLE UNIQUEIDENTIFIER,
@MESSAGEBODY XML,
@MESSAGENAMESPACE VARCHAR(100),
@MESSAGEACTION VARCHAR(20)
WHILE(1=1)
BEGIN
BEGIN TRY
WAITFOR( RECEIVE TOP(1)
@MESSAGETYPE=MESSAGE_TYPE_NAME,
@MESSAGEBODY=MESSAGE_BODY,
@CONVERSATIONHANDLE=CONVERSATION_HANDLE
--,@MESSAGENAMESPACE=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/NAMESPACE)[1]','VARCHAR(100)')
--,@MESSAGEACTION=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/ACTION)[1]','VARCHAR(20)')
FROM [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]
),TIMEOUT 1000
IF(@@ROWCOUNT=0)
BEGIN
BREAK
END
--IF WE DON'T GET ANYTHING FROM THE QUEUE AFTER 1 SECOND,THE QUEUE IS EMPTY,SO BAIL OUT
ELSE
--CHECK THE END DIALOG
IF(@MESSAGETYPE='http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
BEGIN
---WHEN RECEIVE END DIALOG,WE NEED TO END ALSO
END CONVERSATION @CONVERSATIONHANDLE
END
ELSE
--CHECK FOR ERROR MESSAGE
IF(@MESSAGETYPE='http://schemas.microsoft.com/SQL/ServiceBroker/Error')
BEGIN
--HANDLE THE ERROR HERE, RECORD TO ONE TABLE.
INSERT ERROR_RECORD
( CONVERSATION_HANDLE ,
MESSAGE_TYPE_NAME ,
MESSAGE_BODY,
ERROR_DATE )
VALUES(@CONVERSATIONHANDLE,
@MESSAGETYPE,
@MESSAGEBODY,
GETDATE() )
END CONVERSATION @CONVERSATIONHANDLE
END
ELSE
--CHECK FOR THE APPLICATION MESSAGE
IF(@MESSAGETYPE='http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/WAREHOUSE/ALLREQUESTMESSAGETYPE' )
BEGIN
--SELECT @MESSAGENAMESPACE=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/NAMESPACE)[1]','VARCHAR(100)')
--,@MESSAGEACTION=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/ACTION)[1]','VARCHAR(20)') ;
--PROCESS HERE
INSERT SUCESS_RECORD
(CONVERSATION_HANDLE ,
MESSAGE_TYPE_NAME ,
MESSAGE_BODY,
INDATE,
MESSAGENAMESPACE,
MESSAGEACTION)
VALUES(@CONVERSATIONHANDLE,
@MESSAGETYPE,
@MESSAGEBODY,
GETDATE(),
@MESSAGENAMESPACE,
@MESSAGEACTION)
END CONVERSATION @CONVERSATIONHANDLE
END
ELSE
--CHECK FOR THE APPLICATION MESSAGE
IF(@MESSAGETYPE='http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType' )
BEGIN
--PROCESS HERE
END CONVERSATION @CONVERSATIONHANDLE
END
ELSE
BEGIN
--PROCESS THE OTHER INFORMATION
--HANDLE THE ERROR HERE, RECORD TO ONE TABLE.
INSERT ERROR_RECORD
(CONVERSATION_HANDLE ,
MESSAGE_TYPE_NAME ,
MESSAGE_BODY,
ERROR_DATE)
VALUES(@CONVERSATIONHANDLE,
@MESSAGETYPE,
@MESSAGEBODY,
GETDATE())
END CONVERSATION @CONVERSATIONHANDLE
END
END TRY
BEGIN CATCH
INSERT ERROR_RECORD
(CONVERSATION_HANDLE ,
MESSAGE_TYPE_NAME ,
MESSAGE_BODY,
ERROR_DATE)
VALUES(@CONVERSATIONHANDLE,
@MESSAGETYPE,
@MESSAGEBODY,
GETDATE() )
CONTINUE
END CATCH
END --END LOOP
END
14. SEND SAMPLE
EXEC [UP_SSB_SENDMSG]'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService','<test/>'
