Service Broker整个实践过程

 一、启动Service Broker

1. 检查Service Broker的状态

2. SELECT is_broker_enabled FROM sys.databases WHERE database_id = db_id()

3. 启动Service Broker

4. ALTER DATABASE DMWSNewFunction SET ENABLE_BROKER

   二、执行服务

1. 在设计Servcie Broker应用程序时，你必须考虑到以下设计方案

1. 消息类型(message type)
2. 合同(conract)
3. 队列(queues)
4. 服务(services)
5. 服务编程逻辑services program  logic)

2. 怎样去创造一个合同

1. 合同定义了服务之间的消息传递类型，创造一个合同必须执行以下2个步骤：

a). 创造消息类型

1. 用以下的语法创造消息类型：

   CREATE MESSAGE TYPE message_type_name
   [AUTHORIZATION owner_name]
   [VALIDATION =
    {NONE | EMPTY | WELL_FORMED_XML |
     VALID_XML WITH SCHEMA COLLECTION schema_collection_name}]

   VALIDATION 定义了消息包含的类型

   NONE：消息可以不被验证

   EMPTY：消息必须为空

   WELL_FORMED_XML:消息必须是XML格式

   VALID_XML:消息必须是经过符合XML Schema

   AUTHORIZATION是数据库用户或角色

   例子：

   CREATE MESSAGE TYPE 
   [//Adventure-Works.com/Expenses/ExpenseClaim] 
   VALIDATION = WELL_FORMED_XML

    

   CREATE MESSAGE TYPE 
   [//Adventure-Works.com/Expenses/ClaimResponse] 
   VALIDATION = VALID_XML WITH SCHEMA COLLECTION awschemas

b). 创造合同

1. CREATE CONTRACT contract_name
   [ AUTHORIZATION owner_name ]
    ( message_type_name SENT BY { INITIATOR | TARGET | ANY } 
   [ ,...n] ) 

    

   message_type_name 是先前定义的消息类型，SENT BY 表示在传输过程中消息的方向。

   例子：

   CREATE CONTRACT 
   [//Adventure-Works.com/Expenses/ExpenseSubmission] 
   ( [//Adventure-Works.com/Expenses/ExpenseClaim] 
     SENT BY INITIATOR, 
     [//Adventure-Works.com/Expenses/ClaimResponse] 
     SENT BY TARGET )

   在这个例子中只有发起者可以传输ExpenseClaim消息类型，目标者传输ClaimResponse类型

3.去创造队列：他是临时存储消息的：

1. CREATE QUEUE queue_name
      [ WITH  
        [ STATUS = { ON | OFF }  [ , ] ]
        [ RETENTION = { ON | OFF }  [ , ] ]
        [ ACTIVATION (
         [ STATUS = { ON | OFF } , ] 
         PROCEDURE_NAME = stored_procedure_name ,
         MAX_QUEUE_READERS = max_readers , 
         EXECUTE AS { SELF | USER = 'user_name' }  ) ] ]
      [ ON { filegroup | [ DEFAULT ] } ]

   例子：

   CREATE QUEUE ExpenseQueue
   CREATE QUEUE ExpenseQueueWithActivation
   WITH STATUS = OFF, 
   ACTIVATION ( PROCEDURE_NAME = ProcessExpense,  
        MAX_QUEUE_READERS = 5, 
        EXECUTE AS SELF)

    

   ExpenseQueueWithActivation 队列激活 ProcessExpense存储过程当消息到达的时候。在任何时候最大邮5个存储过程实例存在。队列刚开始是

   不可用的，在接受到消息之前我们必须用ALTER Queue去更改它的状态。

4. 去创造服务：服务连接了服务的消费者和生产者。服务包括了从会话开始到整个会话的结束。

1. 去创造服务你必须按以下步骤：服务的名称；当服务的消费者送达消息时提供存储消息的队列；指定合同；选择在会话期间是否保存消息

   CREATE SERVICE service_name
      [ AUTHORIZATION owner_name ]
      ON QUEUE queue_name
      [ ( contract_name [ ,...n ] ) ]

   例子：

   CREATE SERVICE [//Adventure-Works.com/SubmitExpense] 
   ON QUEUE ExpensesInitiator
   ( [//Adventure-Works.com/Expenses/ProcessExpense] )
    
   CREATE SERVICE [//Adventure-Works.com/ProcessExpense] 
   ON QUEUE ExpensesTarget
   ( [//Adventure-Works.com/Expenses/ProcessExpense] )

    

5. 怎样去发送消息，在创造Service Broker各个组件后，你可以发送消息：按以下步骤发送消息：声明一个可用的对话句柄；

1. 创造一个会话；用会话句柄和消息类型发送消息。

   DECLARE @dialog_handle uniqueidentifier  //声明会话句柄

   BEGIN DIALOG [CONVERSATION] dialog_handle_identifier  //利用BEGIN DIALOG CONVERSATION 开启对话
      FROM SERVICE service_name
      TO SERVICE 'service_name' [ , broker_instance ] 
      ON CONTRACT contract_name
      [ WITH
      [  { RELATED_CONVERSATION = conversation_handle 
         | RELATED_CONVERSATION_GROUP = conversation_group_id } ] 
      [ [ , ] LIFETIME = dialog_lifetime ] 
      [ [ , ] ENCRYPTION = { ON | OFF }  ] ]

   例子：

   BEGIN DIALOG CONVERSATION @dialog_handle
     FROM SERVICE [//Adventure-Works.com/SubmitExpense]
     TO SERVICE '//Adventure-Works.com/ProcessExpense'
    ON CONTRACT [//Adventure-Works.com/Expenses/ProcessExpense]

   发送消息：一旦会话开启，就可以用send语句去send消息了

   SEND ON CONVERSATION conversation_handle
      MESSAGE TYPE message_type_name
      [ ( message_body_expression ) ]

   例子：

   DECLARE @msgString NVARCHAR(MAX)
   SET @msgString = NCHAR(0xFEFF) + N'<root>xml data</root>'
   
   ;SEND ON CONVERSATION @dialog_handle
   MESSAGE TYPE [//Adventure-Works.com/Expenses/ExpenseClaim]
   (@msgString)

6. 怎样去接受消息：一旦你发送消息后，服务就会从消息队列中读取消息并处理。接受消息必须执行以下步骤：声明变量存储消息详细信息；执行Receive语句；检查消息类型并处理消息；如果会话结束，执行END CONVERSATION.

1.  

   DECLARE @conversation UNIQUEIDENTIFIER
   DECLARE @msg NVARCHAR(MAX)
   DECLARE @msgType NVARCHAR(256)

    

   [ WAITFOR ( ]
      RECEIVE [ TOP (n) ] 
         < column_specifier > [ ,...n ]
         FROM queue_name
         [ INTO table_variable ]
         [ WHERE { conversation_handle = conversation_handle
         | conversation_group_id = conversation_group_id } ]
   [ ) ] [ , TIMEOUT timeout ]
   

   例子：

   ;RECEIVE TOP(1) @conversation = conversation_handle,
    @msgType = message_type_name, @msg = message_body
   FROM ExpenseQueue

7. 检查消息类型和处理消息：为了确保你接受到的是合同里的消息类型，你可以用IF语句去检查结果集中的message_type_name列。可能的消息类型有以下几种：

1. The expected message type.
2. An error message of the message type http://schemas.microsoft.com/SQL/ServiceBroker/Error.
3. A dialog timeout message of the message type http://schemas.Microsoft.com/SQL/ServiceBroker/DialogTimer.
4. An end dialog message of the message type http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog.
5. An unknown or unexpected message type.

6. 如果消息不是你想得到的类型，你可以结束对话并且返回一个错误消息给服务。

   例子：

   IF (@msgType = '//Adventure-Works.com/Expenses/ExpenseClaim')
     -- process @msg ...
    
   ELSE IF (@msgType =
   'http://schemas.microsoft.com/SQL/ServiceBroker/Error') OR
    (@msgType =
   'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
    
    END CONVERSATION @conversation
    
   ELSE
     END CONVERSATION @conversation 
     WITH ERROR = 500 DESCRIPTION = 'Invalid message type.'

8. 结束对话：

1.  

   END CONVERSATION conversation_handle
      [ WITH ERROR = failure_code DESCRIPTION = failure_text ]

    

   Answers

   Q.1- What is Service Oriented Architecture?

   Answer.1- SOA is a type of architecture that encourages loosely coupled communication between software services.

    

   Q.2- What are the names of the constructs, which are required to develop a service broker application?

   Answer.2- Dialog, Service instance, Route, and Remote service binding.

    

   Q.3- What type of security is supported by Service broker?

   Answer.3- Transport security and Dialog security.

    

   Q.4- When you design a Service Broker application, which parts of a service-oriented solution you must consider.

   Answer.4- Message types, Contracts, Queues, Services, and Service program logic

    

   源文档 <http://www.programmersheaven.com/2/SQL-server-2005-school-lesson-8-p4>

   http://www.programmersheaven.com/2/SQL-server-2005-school-lesson-8