A detailed explanation of the session between Service broker completion instances

The first thing to know is what Service Broker is:

Service Broker is part of the database engine, so managing these applications becomes part of the daily management of the database.

Service Broker provides queues and reliable message delivery for SQL Server. Service Broker can be used both for applications that use a single instance of SQL Server, and for applications that distribute work across multiple instances. (Within a single SQL Server instance, Service Broker provides a powerful asynchronous programming model.) Database applications typically use asynchronous programming to shorten interactive response times and increase overall application throughput)

Broker has the basic elements : Message Type,contract,queue,service (few also need storage, routing, certificates, accounts and other support to complete the transmission)

Understanding of the key when transferring between instances:

Suppose there are a,b,c three people, each of these three people have their own certificate, and the key to back up the public key part, so that the other two people will have their own public key, but the private key can not be exported, only can own. When a message needs to be transmitted,a---->b: At this moment, a has its own private key and B's public key, and likewise B has its own private key and A's public key, when A transmits information to B, the information is first encrypted by the public key of B, encrypted and sent to B, At this moment, the encrypted information is only a private key of B can decode, so it is absolutely safe. Similarly, when B--->a, that's the same thing .

Understanding of the queue:

The queue is symbolizing, inserted from the tail, read from the head, the order of the period is not to be mobilized, only can be read in order area. Both queue queues have the ability to send and receive, and can be processed very quickly because there is a queue transmission_queue during the transfer process to store information about the transfer process.

About Certificate Account Understanding:

The so-called certificate can be authenticated, but it must be bound to a user, that is, the certificate must be created at the beginning of the binding to a user (with no login), the binding user can give the relevant permissions.

1: Create the target database

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 (The first thing to do is to create a endpoint, which is the default port number for this endpoint is 4022, and we set it to 4022)Use master; GO IF EXISTS (SELECT * from sys.databases WHERE name = N ' Insttargetdb ') DROP DATABASE insttargetdb; Gocreate DATABASE Insttargetdb; GO use Insttargetdb; GO CREATE MASTER KEY encryption by PASSWORD = N "; GO CREATE USER targetuser without LOGIN; GO(Create the database master key, as well as the user, here to emphasize the key, the so-called master key is a database master key, not the instance level master key, the database master key is derived from the instance level of the master key, when we want to migrate the database, Be sure to remember to import the instance-level key into the new DB instance; CREATE USER targetuser without LOGIN; Go this sentence is to create a user does not log on the user, as the name implies, here to say the role of this user is: Bind the certificate used, bind the certificate, we will bind the public key user, and then give others some kind of permissions problemsCREATE 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 a certificate, and bind the certificate to the new user Targetuser, we have created the certificate to back up to an address:::: In this statement, the certificate here is not a complete certificate, we back up only the public key part, the private key part is not able to back up, Because the process of encryption and decryption is someone else to encrypt the public key to me, I through the private key area to unlock, so as to ensure the security of data transmission,

        CREATE MESSAGE TYPE [//bothdb/2instsample/requestmessage] VALIDATION = well_formed_xml;

        CREATE MESSAGE TYPE [//bothdb/2instsample/replymessage] VALIDATION = well_formed_xml; GO

  Required to create a message type

        CREATE contract [//bothdb/2instsample/simplecontract]   ([//bothdb/2instsample/requestmessage] SENT by INITIATOR,   [//bothdb/2instsample/replymessage] SENT by TARGET); GO

        Creating a contract is also necessary

      CREATE QUEUE Insttargetqueue; CREATE SERVICE [//tgtdb/2instsample/targetservice]   AUTHORIZATION targetuser on QUEUE insttargetqueue ([//bothdb/ 2instsample/simplecontract]); GO

        Creating the target queue and service, this is also necessary, no doubt ah.

2: Create the Initiator database

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 an endpoint first

      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

(Initiator database, master key, and user)

      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 initiator's certificate and back up the public key)

      CREATE MESSAGE TYPE [//bothdb/2instsample/requestmessage] VALIDATION = well_formed_xml;

CREATE MESSAGE TYPE [//bothdb/2instsample/replymessage] VALIDATION = well_formed_xml; GO

CREATE contract [//bothdb/2instsample/simplecontract]   ([//bothdb/2instsample/requestmessage] SENT by INITIATOR,   [//bothdb/2instsample/replymessage] SENT by TARGET); GO

CREATE QUEUE Instinitiatorqueue;

CREATE SERVICE [//instdb/2instsample/initiatorservice] AUTHORIZATION initiatoruser on QUEUE instinitiatorqueue; GO

(Create message type, contract, queue, and database service)

CREATE USER targetuser without LOGIN;

CREATE CERTIFICATE insttargetcertificate AUTHORIZATION targetuser from FILE = N ' C:\storedcerts\ $ampleSSBCerts \ Insttargetcertificate.cer ' GO

(a reference to the target object, here the path of this certificate is the first time we back up the certificate, we will copy it out to the current host above the path, so that we can have the target database of the public key, can encrypt the transmission to another host of the message, Note that the name of the user created here is the same as the name of the user we are using to bind the certificate public key, so that it can be encrypted and transmitted.

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 ', ADDR ESS = N ' LOCAL ';

EXEC (@Cmd); GO

CREATE REMOTE service BINDING targetbinding to SERVICE N '//tgtdb/2instsample/targetservice ' with USER = Targetuser; GO

( create a route, here we have to pay attention to the red part, we can use the host name or host IP is possible)

3: Complete Target Session Object

Use Insttargetdb GO

       CREATE USER initiatoruser without LOGIN;

       CREATE CERTIFICATE instinitiatorcertificate AUTHORIZATION initiatoruser from FILE = N ' C:\storedcerts\ $ampleSSBCerts \ Instinitiatorcertificate.cer '; GO

(Create references, this you know)

       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 = Initiatoru Ser GO

(Create a routing protocol that is the same as the initiator creates)

4: Start session

       Use Instinitiatordb; GO

       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

(Direct operation is possible)

5: Accept the request and send a reply

Use Insttargetdb; GO

DECLARE @RecvReqDlgHandle uniqueidentifier;

       DECLARE @RecvReqMsg NVARCHAR (100);

       DECLARE @RecvReqMsgName sysname;

       BEGIN TRANSACTION;

               WAITFOR (RECEIVE TOP (1) @RecvReqDlgHandle = conversation_handle, @RecvReqMsg = message_body, @RecvReqMsgName = message_ty Pe_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

6: Receive reply and end sessionUse Instinitiatordb; GO

        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.

        S

A detailed explanation of the session between Service broker completion instances