-- http://msdn.microsoft.com/en-us/library/bb839483.aspx
/* ************* Lesson 1: Creating the Target Database ************* */
-- Create a Service Broker endpoint
--Then, run it to create a Service Broker endpoint for this instance of the Database Engine. A Service Broker endpoint establishes the network address to which Service Broker messages are sent. This endpoint uses the Service Broker default of TCP port 4022, and establishes that the remote instances of the Database Engine will use Windows Authentication connections to send messages.
--Windows Authentication works when both computers are in the same domain or trusted domains. If the computers are not in trusted domains, use certificate security for the endpoints. For more information, see How to: Create Certificates for Service Broker Transport Security (Transact-SQL).
USE master;
GO
IF EXISTS (SELECT * FROM master.sys.endpoints
WHERE name = N'InstTargetEndpoint')
DROP ENDPOINT InstTargetEndpoint;
GO
CREATE ENDPOINT InstTargetEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
-- Create the target database, master key, and user
--Change the password on the CREATE MASTER KEY statement. Then, run the code to create the target database used for this tutorial. By default, new databases have the ENABLE_BROKER option set to on. The code also creates the master key and user that will be used to support encryption and remote connections.
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases
WHERE name = N'InstTargetDB')
DROP DATABASE InstTargetDB;
GO
CREATE DATABASE InstTargetDB;
GO
USE InstTargetDB;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'';
GO
CREATE USER TargetUser WITHOUT LOGIN;
GO
-- Create the target certificate
--Change the file name that is specified in the BACKUP CERTIFICATE statement to refer to a folder on your system. Then, run the code to create the target certificate that is used to encrypt messages. The folder that you specify should have permissions that prevent access from accounts other than your Windows account and the Windows account the instance of the Database Engine is running under. For Lesson 2, you must manually copy the InstTargetCertificate.cer file to a folder that can be accessed from the initiator instance.
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
WITH SUBJECT = 'Target Certificate',
EXPIRY_DATE = N'12/31/2010';
BACKUP CERTIFICATE InstTargetCertificate
TO FILE =
N'C:\storedcerts\$ampleSSBCerts\InstTargetCertificate.cer';
GO
-- Create the message types
--Copy and paste the following code into a Query Editor window then run it to create the message types for the conversation. The message type names and properties specified here must be identical to the ones that you will create in the InstInitiatorDB in the next lesson.
CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GO
-- Create the contract
--Then, run it to create the contract for the conversation. The contract name and properties that are specified here must be identical to the contract that you will create in the InstInitiatorDB in the next lesson.
CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
([//BothDB/2InstSample/RequestMessage]
SENT BY INITIATOR,
[//BothDB/2InstSample/ReplyMessage]
SENT BY TARGET
);
GO
-- Create the target queue and service
--Then, run it to create the queue and service that is used for the target. The CREATE SERVICE statement associates the service with the InstTargetQueue, so that all messages sent to the service will be received into the InstTargetQueue. The CREATE SERVICE also specifies that only conversations that use the //BothDB/ 2InstSample/SimpleContract that was created earlier can use the service as a target service.
CREATE QUEUE InstTargetQueue;
CREATE SERVICE [//TgtDB/2InstSample/TargetService]
AUTHORIZATION TargetUser
ON QUEUE InstTargetQueue
([//BothDB/2InstSample/SimpleContract]);
GO
/* ************* Lesson 2: Creating the Initiator Database ************* */
-- Create a Service Broker endpoint
--Then, run it to create a Service Broker endpoint for this instance of the Database Engine. A Service Broker endpoint specifies the network address to which Service Broker messages are sent. This endpoint uses the Service Broker default of TCP port 4022, and specifies that remote instances of the Database Engine will use Windows Authentication connections to send messages.
-- Windows Authentication works when both computers are in the same domain, or are in trusted domains. If the computers are not in trusted domains, use certificate security for the endpoints. For more information, see How to: Create Certificates for Service Broker Transport Security (Transact-SQL).
USE master;
GO
IF EXISTS (SELECT * FROM sys.endpoints
WHERE name = N'InstInitiatorEndpoint')
DROP ENDPOINT InstInitiatorEndpoint;
GO
CREATE ENDPOINT InstInitiatorEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
-- Create the initiator database, master key, and user
--Change the password on the CREATE MASTER KEY statement. Then, run the code to create the target database that is used for this tutorial. By default, new databases have the ENABLE_BROKER option set to on. The code also creates the master key and user that will be used to support encryption and remote connections.
USE master;
GO
IF EXISTS (SELECT * FROM sys.databases
WHERE name = N'InstInitiatorDB')
DROP DATABASE InstInitiatorDB;
GO
CREATE DATABASE InstInitiatorDB;
GO
USE InstInitiatorDB;
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'';
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO
-- Create the initiator certificate
--Change the file name that is specified in the BACKUP CERTIFICATE statement to refer to a folder on your system. Then, run the code to create the initiator certificate that is used to encrypt messages. The folder that you specify should have permissions that prevent access from accounts other than your Windows account and the Windows account the instance of the Database Engine is running under. For Lesson 3, you must manually copy the InstInitiatorCertificate.cer file to a folder that can be accessed from the target instance.
CREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
WITH SUBJECT = N'Initiator Certificate',
EXPIRY_DATE = N'12/31/2010';
BACKUP CERTIFICATE InstInitiatorCertificate
TO FILE =
N'C:\storedcerts\$ampleSSBCerts\InstInitiatorCertificate.cer';
GO
-- Create the message types
--Then, run it to create the message types for the conversation. The message type names and properties specified here must be identical to the ones that were created in the InstTargetDB in the previous lesson.
CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
VALIDATION = WELL_FORMED_XML;
GO
-- Create the contract
--Then, run it to create the contract for the conversation. The contract name and properties that are specified here must be identical to the contract that you will create in the InstInitiatorDB during the next lesson.
CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
([//BothDB/2InstSample/RequestMessage]
SENT BY INITIATOR,
[//BothDB/2InstSample/ReplyMessage]
SENT BY TARGET
);
GO
-- Create the initiator queue and service
--Then, run it to create the queue and service that is used for the target. The CREATE SERVICE statement associates the service with the InstInitiatorQueue. Therefore, all messages that are sent to the service will be received into the InstInitiatorQueue. The CREATE SERVICE also specifies that only conversations that use the //BothDB/ 2InstSample/SimpleContract that was created earlier can use the service as a target service.
CREATE QUEUE InstInitiatorQueue;
CREATE SERVICE [//InstDB/2InstSample/InitiatorService]
AUTHORIZATION InitiatorUser
ON QUEUE InstInitiatorQueue;
GO
-- Create references to target objects
--Change the FROM FILE clause to reference the folder to which you copied the InstTargetCertficate.cer file from step 3 in Lesson 1. Then, run the code to create a target user and pull in the target certificate.
CREATE USER TargetUser WITHOUT LOGIN;
CREATE CERTIFICATE InstTargetCertificate
AUTHORIZATION TargetUser
FROM FILE =
N'C:\storedcerts\$ampleSSBCerts\InstTargetCertificate.cer'
GO
-- Create routes
--Change the string MyTargetComputer to the name of the computer that is running your target instance. Then, run the code to create routes to the target service and initiator service, and a remote service binding that associates the TargetUser with the target service route.
--The following CREATE ROUTE statements assume that there are no duplicate service names in the target instance. If multiple databases on the target instance have services with the same name, use the BROKER_INSTANCE clause to specify the database on which you want to open a conversation.
DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE InstInitiatorDB;
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
N''//TgtDB/2InstSample/TargetService'',
ADDRESS = N''TCP://MyTargetComputer:4022'';';
EXEC (@Cmd);
SET @Cmd = N'USE msdb
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
N''//InstDB/2InstSample/InitiatorService'',
ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GO
CREATE REMOTE SERVICE BINDING TargetBinding
TO SERVICE
N'//TgtDB/2InstSample/TargetService'
WITH USER = TargetUser;
GO
/* ************* Lesson 3: Completing the Target Conversation Objects ************* */
-- Create references to initiator objects
--Change the FROM FILE clause to reference the folder to which you copied the InstInitiatorCertficate.cer file from step 4 in Lesson 2. Then, run the code to create an initiator user and pull in the initiator certificate.
USE InstTargetDB
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
CREATE CERTIFICATE InstInitiatorCertificate
AUTHORIZATION InitiatorUser
FROM FILE =
N'C:\storedcerts\$ampleSSBCerts\InstInitiatorCertificate.cer';
GO
-- Create routes
--Change the string MyInitiatorComputer to the name of the computer that is running your initiator instance. Then, run the code to create routes to the target service and initiator service, and a remote service binding that associates the InitiatorUser with the initiator service route.
-- The following CREATE ROUTE statements assume that there are no duplicate service names in the target instance. If multiple databases on the target instance contain services that have the same name, use the BROKER_INSTANCE clause to specify the database on which you want to open a conversation.
DECLARE @Cmd NVARCHAR(4000);
SET @Cmd = N'USE InstTargetDB;
CREATE ROUTE InstInitiatorRoute
WITH SERVICE_NAME =
N''//InstDB/2InstSample/InitiatorService'',
ADDRESS = N''TCP://MyInitiatorComputer:4022'';';
EXEC (@Cmd);
SET @Cmd = N'USE msdb
CREATE ROUTE InstTargetRoute
WITH SERVICE_NAME =
N''//TgtDB/2InstSample/TargetService'',
ADDRESS = N''LOCAL''';
EXEC (@Cmd);
GO
GRANT SEND
ON SERVICE::[//TgtDB/2InstSample/TargetService]
TO InitiatorUser;
GO
CREATE REMOTE SERVICE BINDING InitiatorBinding
TO SERVICE N'//InstDB/2InstSample/InitiatorService'
WITH USER = InitiatorUser;
GO
/* ************* Lesson 4: Beginning the Conversation ************* */
-- Switch to the InitiatorDB database
--Then, run it to switch context to the InstInitiatorDB database where you will initiate the conversation.
USE InstInitiatorDB;
GO
-- Start a conversation and send a request message
--Then, run it to begin a conversation and send a request message to the //TgtDB/2InstSample/TargetService in the InstTargetDB. The code must be run in one block because a variable is used to pass a dialog handle from BEGIN DIALOG to the SEND statement. The batch runs the BEGIN DIALOG statement to begin the conversation, and then builds a request message. Then, it uses the dialog handle in a SEND statement to send the request message on that conversation. The last SELECT statement just displays the text of the message that was sent.
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);
BEGIN TRANSACTION;
BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//InstDB/2InstSample/InitiatorService]
TO SERVICE N'//TgtDB/2InstSample/TargetService'
ON CONTRACT [//BothDB/2InstSample/SimpleContract]
WITH
ENCRYPTION = ON;
SELECT @RequestMsg = N'Message for Target service.';
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
(@RequestMsg);
SELECT @RequestMsg AS SentRequestMsg;
COMMIT TRANSACTION;
GO
/* ************* Lesson 5: Receiving a Request and Sending a Reply ************* */
-- Switch to the TargetDB database
--Then, run it to switch context to the InstTargetDB database where you will receive the request message and send a reply message back to the InstInitiatorDB.
USE InstTargetDB;
GO
-- Receive the request and send a reply
--Then, run it to receive the reply message from the InstTargetQueue and send a reply message back to the initiator. The RECEIVE statement retrieves the request message. Then, the following SELECT statement displays the text so that you can verify that it is the same message that was sent in the previous step. The IF statement tests whether the received message is a request message type, and if a SEND statement is used to send a reply message back to the initiator. The END CONVERSATION statement is used to end the target side of the conversation. The final SELECT statement displays the text of the reply message.
DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName SYSNAME;
BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1)
@RecvReqDlgHandle = conversation_handle,
@RecvReqMsg = message_body,
@RecvReqMsgName = message_type_name
FROM InstTargetQueue
), TIMEOUT 1000;
SELECT @RecvReqMsg AS ReceivedRequestMsg;
IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'
BEGIN
DECLARE @ReplyMsg NVARCHAR(100);
SELECT @ReplyMsg =
N'Message for Initiator service.';
SEND ON CONVERSATION @RecvReqDlgHandle
MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
(@ReplyMsg);
END CONVERSATION @RecvReqDlgHandle;
END
SELECT @ReplyMsg AS SentReplyMsg;
COMMIT TRANSACTION;
GO
/* ************* Lesson 6: Receiving the Reply and Ending the Conversation ************* */
-- Switch to the InitiatorDB database
--Then, run it to switch context back to the InstInitiatorDB database where you will receive the reply message and end the conversation.
USE InstInitiatorDB;
GO
-- Receive the reply and end the conversation
--Then, run it to receive the reply message and end the conversation. The RECEIVE statement retrieves the reply message from the InstInitiatorQueue. The END CONVERSATION statement ends the initiator side of the conversation. The last SELECT statement displays the text of the reply message so that you can confirm it is the same as what was sent in the last step.
DECLARE @RecvReplyMsg NVARCHAR(100);
DECLARE @RecvReplyDlgHandle UNIQUEIDENTIFIER;
BEGIN TRANSACTION;
WAITFOR
( RECEIVE TOP(1)
@RecvReplyDlgHandle = conversation_handle,
@RecvReplyMsg = message_body
FROM InstInitiatorQueue
), TIMEOUT 1000;
END CONVERSATION @RecvReplyDlgHandle;
-- Display recieved request.
SELECT @RecvReplyMsg AS ReceivedReplyMsg;
COMMIT TRANSACTION;
GO