Lesson 2: Creating the Initiator Database

Copy and paste the following code into a Query Editor window. 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

Copy and paste the following code into a Query Editor window. 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'<EnterStrongPassword2Here>';
GO
CREATE USER InitiatorUser WITHOUT LOGIN;
GO

Copy and paste the following code into a Query Editor window. 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

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 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

Copy and paste the following code into a Query Editor window. 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

Copy and paste the following code into a Query Editor window. 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

Copy and paste the following code into a Query Editor window. 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

Copy and paste the following code into a Query Editor window. 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