Udostępnij za pośrednictwem


Lesson 2: Creating the Initiator Database

New: 15 September 2007

In this lesson, you will learn to create the initiator database and all the initiator Service Broker objects that are used in this tutorial. Run these steps from a copy of Management Studio that is running on the same computer as the initiator instance the Database Engine.

Procedures

Create a Service Broker endpoint

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

Create the initiator database, master key, and user

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

Create the initiator certificate

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

Create the message types

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

Create the contract

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

Create the initiator queue and service

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

Create references to target objects

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

Create routes

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

Next Steps

You have successfully created the initiator databases that will be used for the tutorial. Next, you will finish configuring the target database by creating the target objects that have dependencies on initiator objects. See Lesson 3: Completing the Target Conversation Objects.

See Also

Other Resources

BACKUP CERTIFICATE (Transact-SQL)
CREATE CERTIFICATE (Transact-SQL)
CREATE CONTRACT (Transact-SQL)
CREATE DATABASE (Transact-SQL)
CREATE ENDPOINT (Transact-SQL)
CREATE MASTER KEY (Transact-SQL)
CREATE MESSAGE TYPE (Transact-SQL)
CREATE QUEUE (Transact-SQL)
CREATE REMOTE SERVICE BINDING (Transact-SQL)
CREATE ROUTE (Transact-SQL)
CREATE SERVICE (Transact-SQL)
CREATE USER (Transact-SQL)
EXECUTE (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
Service Broker Dialog Security
Conversation Architecture
Service Architecture

Help and Information

Getting SQL Server 2005 Assistance