SQLServer Broker Service 配置步骤

最新推荐文章于 2018-05-29 08:42:27 发布
最新推荐文章于 2018-05-29 08:42:27 发布
阅读量2.6k 收藏
点赞数
文章标签： service sqlserver server database encryption authentication
介绍了两个 服务器上SQLServer实例之间配置Service Broker的方法.

 1. ENABLE OUTBOUND CONNECTIONS ON THE FIRST SERVER.

1.1.         Create the SQL service master key.

On the master database, create the SQL service master key. This can be used to initial transparent security.

USEMASTER;

GO

--MAKE SURE MASTER HAD MASTER KEY

CREATEMASTERKEY

ENCRYPTIONBYPASSWORD='???????';

GO

1.2.         Make a certificate for this server instance.

Create a certificate in master, the public key of this cert will be used for authentication purposes it is a self-signed cert.

CREATECERTIFICATETRPCERT_SERVER1

WITHSUBJECT='TRPCERT_SERVER1',

START_DATE='10/30/2006',

EXPIRY_DATE='10/30/2016'--SHOULD BE LATER THAN START DATE.

GO

1.3.         Backup the certificate and copy it to the second server.

Backup the public key of the certificate to a file. This file will then be exchanged with the other instance , The file needs to be copied to the second server.

BACKUPCERTIFICATETRPCERT_SERVER1

TOFILE='C:\DD11\TRPCERT_SERVER1.CER';

GO

1.4.         Create a mirroring endpoint for server broker using the certificate.

You need to create an endpoint in order to enable communication outside of this instance.if you configure SQL Server 2005, you can use the same step.

CREATEENDPOINTSERVER1_WH_SSB

STATE=STARTED

ASTCP

( LISTENER_PORT=4022)

FORSERVICE_BROKER

(

 AUTHENTICATION=CERTIFICATETRPCERT_SERVER1,

 ENCRYPTION=REQUIRED

);

GO

2.            ENABLE OUTBOUND CONNECTIONS ON THE SECOND SERVER

2.1.         On the master database, create the database master key.

USEMASTER;

GO

--MAKE SURE MASTER HAD MASTER KEY

CREATEMASTERKEY

ENCRYPTIONBYPASSWORD='???????';

GO

2.2.         New one certificate on the Mirror server instance.

Create a certificate in master database, the public key of this certificate will be used for authentication purposes when the remote instance tries to connect to this server.

CREATECERTIFICATETRPCERT_SERVER2

WITHSUBJECT='TRANSPORT CERTIFICATE FOR SERVER2',

START_DATE='10/1/2006',

EXPIRY_DATE='10/1/2016'

GO

2.3.         Backup the certificate and copy it to other server.

Backup the public key of the certificate to a file, this file will then be exchanged with the other instance. The file needs to be copied to first server.

BACKUPCERTIFICATETRPCERT_SERVER2

TOFILE='C:\DD11\TRPCERT_SERVER2.CER';

GO

2.4.         Create the mirroring endpoint for the server broker instance.

You need to create an endpoint in order to enable communication outside of this instance.

CREATEENDPOINTSERVER2_WH_SSB

STATE=STARTED

ASTCP

( LISTENER_PORT=4022)

FORSERVICE_BROKER

(

 AUTHENTICATION=CERTIFICATETRPCERT_SERVER2,

 ENCRYPTION=REQUIRED

);

GO

 

3.            ENABLE INBOUND CONNECTIONS ON THE FIRST SERVER

 3.1.        Create a login on first server. 

Create a login and a user which you will assign a public key from the certificate in the remote master database.

USEMASTER;

GO

IF NOTEXISTS(SELECT*FROMSYS.SERVER_PRINCIPALSWHERENAME=N'WH10DBO')

BEGIN

  CREATELOGINWH10DBO

  WITHPASSWORD=N'???????',DEFAULT_DATABASE=MASTER,DEFAULT_LANGUAGE=[US_ENGLISH],CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF

END

GO

3.2.         Create a user for the login.

CREATEUSERWH10DBOFROMLOGINWH10DBO

GO

3.3.         Grant connect permission on the endpoint to the login.

You must grant connect permission, otherwise you will get error when you are using the profiler90.

GRANTCONNECTONENDPOINT::SERVER1_WH_SSBTOWH10DBO

GO

3.4.         Associate the mirror server certificate with the user.

 

CREATECERTIFICATETRPCERT_SERVER2

AUTHORIZATIONWH10DBO

FROMFILE='C:\DD11\TRPCERT_SERVER2.CER';

GO

4.            ENABLE INBOUND CONNECTIONS ON THE SECOND SERVER

4.1.         Create a login for the first server.

Create a login and a user which you will assign a public key from the certificate in the remote master database to it.

USEMASTER;

GO

CREATELOGINSSBDBOWITHPASSWORD='??????';

GO

4.2.         Create a user from the login.

 CREATEUSER  SSBDBOFROMLOGIN  SSBDBO;

GO

4.3.         Grant connection permission to the user.

 GRANTCONNECTONENDPOINT::SERVER2_WH_SSB

TOSSBDBO--CAN ONLY GRANT TO USER OR GROUP.CAN’T BE A LOGIN.THIS IS WHY WE CREATE USER ABOVE.

GO

4.4.           Associate a certificate with the user we created.

Create a certificate from the public certificate of first server. The certificate you backed is only the public key.

CREATECERTIFICATETRPCERT_SERVER1

AUTHORIZATIONSSBDBO

FROMFILE='C:\DD11\TRPCERT_SERVER1.CER';

GO

5.            SET THE DATABASE OUTBOUND FOR THE FIRST SERVER

5.1.         Create a database which will be configured as service broker database.

CREATEDATABASESSB

GO

5.2.         Create database master key for encrypting our certificate.

USESSB;

GO

--SET MASTER KEY

CREATEMASTERKEYENCRYPTIONBYPASSWORD='??????*';

GO

5.3.         Create certificate for transport security.

Here we create a self-signed certificate. 

CREATECERTIFICATEDLGCERT_SERVER1

WITHSUBJECT='DLGCERT_CENTER',START_DATE='10/30/2006',

EXPIRY_DATE='10/30/2016'ACTIVEFORBEGIN_DIALOG=ON;

GO

5.4.         Backup the certificate.

 Backup the public key of the certificate to a file, and then copy it to second server.

BACKUPCERTIFICATEDLGCERT_SERVER1

TOFILE='C:\DD11\DLGCERT_SERVER1.CER';

GO

6.            SET THE DATABASE OUTBOUND  FOR SECOND SERVER.

6.1.         Create the database.

 CREATEDATABASESSB

GO

6.2.         Create the database master key.

This key then will be used to encryption our certificate.

USESSB;

GO

--SET MASTER KEY

CREATEMASTERKEY

ENCRYPTIONBYPASSWORD='???????*';

GO

6.3.         Create certificate for transport security.

Here you create a self-signed certificate, which will be encrypted by the above key.

CREATECERTIFICATEDLGCERT_SERVER2

WITHSUBJECT='DLGCERT_SERVER2',START_DATE='10/1/2006',

EXPIRY_DATE='10/1/2016'ACTIVEFORBEGIN_DIALOG=ON;

GO

6.4.         Backup the Certificate and copy to the first server.

Backup the public key of the certificate to a file this will then be exchanged with the other instance make sure that the path you define below can be accessed by sql server. The file needs to be copied to server 2

BACKUPCERTIFICATEDLGCERT_SERVER2

TOFILE='C:\DD11\DLGCERT_SERVER2.CER';

GO

7.            SET THE DATABASE INBOUND FOR THE FIRST SERVER.

7.1.         Create a user to assign a public key.

Create a user which you   will assign a public key from the certificate in the remote database.

CREATEUSERWH10DBOFROMLOGINWH10DBO;

GO

7.2.         Associate the second server db’s public cert to the use.

You create a certificate from server 2's public certificate and assign it to the user created above 

CREATECERTIFICATEDLGCERT_SERVER2

AUTHORIZATIONWH10DBO

FROMFILE='C:\DD11\DLGCERT_SERVER2.CER';

GO

8.            SET THE DATABASE INBOUND FOR SERVER2

8.1.         Create a user that will own the public key from second server db.

Create a user which you eventually will assign a public key from the cert in the remote db

CREATEUSERSSBDBO FROMLOGINSSBDBO;

GO

8.2.         Associate the related public key with the user.

CREATECERTIFICATEDLGCERT_SERVER1

AUTHORIZATIONSSBDBO

FROMFILE='C:\DD11\DLGCERT_SERVER1.CER';

9.            SET THE DATABASE SERVICE BROKER METADATA FOR SERVER1

9.1.         Create two message types.

USESSB;

GO

-- WE NEED TWO MESSAGE TYPES

CREATEMESSAGETYPE[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]

VALIDATION=WELL_FORMED_XML;

CREATEMESSAGETYPE[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType]

VALIDATION=WELL_FORMED_XML;

GO

9.2.         Create the message contract.

CREATECONTRACT[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]

(

  [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType] SENTBYINITIATOR,

  [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType]   SENTBYTARGET

);

GO

9.3.         Create the queue.

Note: At this stage we do not care about activation, so the queue can’t auto process.

CREATEQUEUE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]

WITHSTATUS=ON;

GO

9.4.         Create a service.

CREATESERVICE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService]

ONQUEUE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]

(

  [http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]

);

GO

9.5.         Create a route to the remote service.

CREATEROUTE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllRoute]

WITH SERVICE_NAME='http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService',

ADDRESS='TCP://SERVER2:4022';

GO

9.6.         Create a remote service binding.

We'll do  encrypted dialogs we need a remote service binding

CREATEREMOTESERVICEBINDING[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllServiceRemoteServiceBinding]

TOSERVICE'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService'

WITH USER=WH10DBO,

ANONYMOUS=OFF

GO

9.7.         Grant the user send rights on the service.

GRANTSENDONSERVICE::[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService]TOWH10DBO;

GO

10.          SET THE DATABASE SERVICE BROKER METADATA FOR SERVER2

10.1.      Create two message types.

USESSB;

GO

CREATEMESSAGETYPE[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]

VALIDATION=WELL_FORMED_XML;

 

CREATEMESSAGETYPE[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType]

VALIDATION=WELL_FORMED_XML;

GO

10.2.      Create the message contract.

CREATECONTRACT[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]

([http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType] SENTBYINITIATOR,

 [http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType]  SENTBYTARGET

);

GO

10.3.      Create the queue.

Create the queue, at this stage we do not care about activation.

CREATEQUEUE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]

WITHSTATUS=ON;

GO

10.4.      Create the Service.

CREATESERVICE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService]

ONQUEUE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue]

([http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]);

GO

10.5.      Create a route to the remote service.

Create a route to the remote service.

CREATEROUTE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllRoute]

WITH

SERVICE_NAME='http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService',

ADDRESS='TCP://SERVER1:4022';

GO

10.6.      Create a remote service binding.

As we'll be doing encrypted dialogs we need a remote service binding

CREATEREMOTESERVICEBINDING[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllServiceRemoteServiceBinding]

TOSERVICE'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService'

WITH USER=SSBDBO,

ANONYMOUS=OFF

GO

10.7.      Grant the user send rights on the service.

GRANTSENDONSERVICE::[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService]TOSSBDBO;

GO

11.          CONFIGURE THE LOG TABLE ON BOTH SERVERS

CREATETABLE[DBO].[SUCCESS_RECORD](

      [CONVERSATION_HANDLE] [UNIQUEIDENTIFIER]NOTNULL,

      [MESSAGE_TYPE_NAME] [SYSNAME]COLLATE       SQL_LATIN1_GENERAL_CP1_CI_ASNOTNULL,

      [MESSAGE_BODY] [XML]NULL,

      [INDATE] [DATETIME]NULLDEFAULT(GETDATE()),

      [MESSAGENAMESPACE] [VARCHAR](100)COLLATESQL_LATIN1_GENERAL_CP1_CI_ASNULL,

      [MESSAGEACTION] [VARCHAR](20)COLLATESQL_LATIN1_GENERAL_CP1_CI_ASNULL,

      [PROCESSTYPE] [CHAR](1)COLLATESQL_LATIN1_GENERAL_CP1_CI_ASNULLDEFAULT('N'),

PRIMARYKEYCLUSTERED

(

      [CONVERSATION_HANDLE]ASC

)WITH(PAD_INDEX =OFF,IGNORE_DUP_KEY=OFF)ON[PRIMARY]

)ON[PRIMARY]

 GO

CREATETABLE[DBO].[EXCEPTION_RECORD](

      [MESSAGE_BODY] [XML]NULL,

      [INDATE] [DATETIME]NOTNULLCONSTRAINT[DF_EXCEPTION_RECORD_INDATE] DEFAULT(GETDATE())

)ON[PRIMARY]

GO

CREATETABLE[DBO].[SENT_RECORD](

      [MESSAGE_BODY] [XML]NULL,

      [INDATE] [DATETIME]NULLCONSTRAINT[DF_SENT_RECORD_INDATE] DEFAULT(GETDATE())

)ON[PRIMARY]

GO

12.          SET THE DATABASE SERVICE BROKER CONVESATION 

12.1.      Create Send message on server1.

USESSB

GO

CREATEPROC[DBO].[UP_SSB_SENDMSG]

      @SERVICENAMESYSNAME,

      @MSGXML

AS

BEGIN

     --START THE DIALOG AND SEND A MESSAGE

     --UNCOMMENT FROM HERE UNTIL THE FOLLOWING GO STATEMENT AND RUN

     DECLARE@HUNIQUEIDENTIFIER--CONVERSATION HANDLE

     --DECLARE @MSG XML; --WILL HOLD THE MESSAGE

     BEGINTRY

     BEGINDIALOGCONVERSATION@H

     FROMSERVICE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse00/AllSSBService]

     -- TO SERVICE 'S_RECSERVICE'

     --TO SERVICE 'S_RECSERVICE_S8SQL01'

     TOSERVICE@SERVICENAME

     ONCONTRACT[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]

     --WITH ENCRYPTION=OFF;

     ;

     --SET @MSG = '<HELLO00/>';

     SENDONCONVERSATION@H

     MESSAGETYPE[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]

     (@MSG);

       

ENDTRY

     BEGINCATCH

           BEGIN

                 INSERTINTOSSB.DBO.DISPATH_ERRORRECORD(MESSAGE_BODY)VALUES(@MSG)

                 --SELECT [MESSAGE]='MESSAGE CHANNEL NOT DEFINED IN E3BZT.SSB_CENTER'         

           END

     ENDCATCH

END

 

12.2.      Receive message on server2.

CREATEPROC[DBO].[UP_SSB_SENDMSG]

      @SERVICENAMESYSNAME,

      @MSGXML

AS

BEGIN

     --START THE DIALOG AND SEND A MESSAGE

     --UNCOMMENT FROM HERE UNTIL THE FOLLOWING GO STATEMENT AND RUN

     DECLARE@HUNIQUEIDENTIFIER--CONVERSATION HANDLE

     --DECLARE @MSG XML; --WILL HOLD THE MESSAGE

     BEGINTRY

     BEGINDIALOGCONVERSATION@H

     FROMSERVICE[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService]

     -- TO SERVICE 'S_RECSERVICE'

     --TO SERVICE 'S_RECSERVICE_S8SQL01'

     TOSERVICE@SERVICENAME

     ONCONTRACT[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllContract]

     --WITH ENCRYPTION=OFF;

     ;

     --SET @MSG = '<HELLO00/>';

     SENDONCONVERSATION@H

     MESSAGETYPE[http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllRequestMessageType]

     (@MSG);

     

     --CHECK IF THE TRANSMISSION_STATUS COLUMN IN SYS.TRANSMISSION_QUEUE

     --HAS ANY MESSAGES, IF SO SOMETHING IS NOT ENTIRELY CORRECT

ENDTRY

BEGINCATCH

     BEGIN

           INSERTINTOSSB.DBO.DISPATH_ERRORRECORD(MESSAGE_BODY)VALUES(@MSG)

           --SELECT [MESSAGE]='MESSAGE CHANNEL NOT DEFINED IN E3BZT.SSB_CENTER'         

     END

ENDCATCH

END

 

13.          RECEIVE STORE PROCEDURE

CREATEPROCEDURE[DBO].[UP_SSB_PROCESSINCOMINGMSG] 

AS 

BEGIN 

 SETNOCOUNTON 

 DECLARE@MESSAGETYPESYSNAME, 

          @CONVERSATIONHANDLEUNIQUEIDENTIFIER, 

          @MESSAGEBODYXML, 

          @MESSAGENAMESPACEVARCHAR(100), 

          @MESSAGEACTION VARCHAR(20) 

 WHILE(1=1) 

  BEGIN  

     BEGINTRY

            WAITFOR(RECEIVETOP(1)  

               @MESSAGETYPE=MESSAGE_TYPE_NAME, 

               @MESSAGEBODY=MESSAGE_BODY, 

               @CONVERSATIONHANDLE=CONVERSATION_HANDLE 

              --,@MESSAGENAMESPACE=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/NAMESPACE)[1]','VARCHAR(100)')

              --,@MESSAGEACTION=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/ACTION)[1]','VARCHAR(20)')

            FROM[http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse/AllQueue] 

            ),TIMEOUT1000 

               

            IF(@@ROWCOUNT=0) 

               BEGIN 

                   BREAK 

               END

 

            --IF WE DON'T GET ANYTHING FROM THE QUEUE AFTER 1 SECOND,THE QUEUE IS EMPTY,SO BAIL OUT 

            ELSE    

    --CHECK THE END DIALOG  

            IF(@MESSAGETYPE='http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog') 

                BEGIN 

    ---WHEN RECEIVE END DIALOG,WE NEED TO END ALSO 

                   ENDCONVERSATION@CONVERSATIONHANDLE 

                 END 

            ELSE 

            --CHECK FOR ERROR MESSAGE 

            IF(@MESSAGETYPE='http://schemas.microsoft.com/SQL/ServiceBroker/Error') 

                BEGIN 

    --HANDLE THE ERROR HERE, RECORD TO ONE TABLE. 

                    INSERTERROR_RECORD 

                                      (CONVERSATION_HANDLE, 

                                          MESSAGE_TYPE_NAME, 

                                          MESSAGE_BODY, 

                                          ERROR_DATE) 

                   VALUES(@CONVERSATIONHANDLE, 

                                       @MESSAGETYPE, 

                                       @MESSAGEBODY, 

                                      GETDATE() ) 

                  ENDCONVERSATION@CONVERSATIONHANDLE  

                END 

             ELSE 

            --CHECK FOR THE APPLICATION MESSAGE 

             IF(@MESSAGETYPE='http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/WAREHOUSE/ALLREQUESTMESSAGETYPE') 

                BEGIN 

                    --SELECT  @MESSAGENAMESPACE=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/NAMESPACE)[1]','VARCHAR(100)')

                    --,@MESSAGEACTION=@MESSAGEBODY.VALUE('(/ROOT/NODE/MESSAGEHEAD/ACTION)[1]','VARCHAR(20)') ;

                    --PROCESS HERE 

                   INSERTSUCESS_RECORD 

                                (CONVERSATION_HANDLE, 

                                    MESSAGE_TYPE_NAME, 

                                    MESSAGE_BODY, 

                                    INDATE, 

                                    MESSAGENAMESPACE, 

                                    MESSAGEACTION) 

                             VALUES(@CONVERSATIONHANDLE, 

                                     @MESSAGETYPE, 

                                     @MESSAGEBODY, 

                                     GETDATE(), 

                                     @MESSAGENAMESPACE, 

                                     @MESSAGEACTION)

                   ENDCONVERSATION@CONVERSATIONHANDLE           

                 END 

         ELSE 

            --CHECK FOR THE APPLICATION MESSAGE 

        IF(@MESSAGETYPE='http://soa.microsoft.com/SOA/SOA/USA/InfrastructureService/V10/Types/Warehouse/AllReponseMessageType') 

                BEGIN 

                    --PROCESS HERE 

                   ENDCONVERSATION@CONVERSATIONHANDLE               

                END 

         ELSE 

                BEGIN 

    --PROCESS THE OTHER INFORMATION 

    --HANDLE THE ERROR HERE, RECORD TO ONE TABLE. 

                    INSERTERROR_RECORD 

                                (CONVERSATION_HANDLE, 

                                    MESSAGE_TYPE_NAME, 

                                    MESSAGE_BODY, 

                                    ERROR_DATE) 

                             VALUES(@CONVERSATIONHANDLE, 

                                     @MESSAGETYPE, 

                                     @MESSAGEBODY, 

                                     GETDATE()) 

                    ENDCONVERSATION@CONVERSATIONHANDLE  

                END 

ENDTRY

BEGINCATCH

              INSERTERROR_RECORD 

                   (CONVERSATION_HANDLE, 

                    MESSAGE_TYPE_NAME, 

                    MESSAGE_BODY, 

                    ERROR_DATE) 

              VALUES(@CONVERSATIONHANDLE, 

                     @MESSAGETYPE, 

                     @MESSAGEBODY, 

                    GETDATE()) 

         CONTINUE 

 

 ENDCATCH 

 

 

      END--END LOOP 

 

END 

 

14.          SEND SAMPLE

EXEC[UP_SSB_SENDMSG]'http://soa.microsoft.com/SOA/USA/InfrastructureService/V10/Warehouse10/AllSSBService','<test/>'

zhuanghe_xing
关注
SQLServer -ServiceBroker
时光
08-13 2975
1.ServiceBroker简介 直接把文档原话copy过来吧： SQL Service Broker is one of the best recent features in SQL Server 2005 because it makes it possible to write queuing and message-based applications. With Service...
SQL Server 2008中Service Broker基础应用
11-28
下面将详细阐述Service Broker的基础应用及其在SQL Server 2008中的实现步骤。 首先，Service Broker的核心功能是提供消息队列，它支持数据库之间的异步通信。这意味着在主业务流程中，发送方可以立即返回确认，而...
SQL Server 2008中Service Broker基础应用(上）
dfdfadsf3443的博客
04-05 258
SQL Server 2008中SQL应用系列--目录索引 导读：本文主要涉及Service Broker的基本概念及建立一个Service Broker应用程序的基本步骤。 一、前言： Service Broker为SQL Server提供消息队列，这提供了从数据库中发送异步事务性消息队列的方法。Service Broker消息可以保证以适当的顺序或原始的发送顺序不重复...
SQL Server 2005 Service Broker 初探
weixin_33845477的博客
12-28 118
引言 SQL Server 2005 的一个主要成就是可以实现可靠、可扩展且功能完善的数据库应用程序。 与 .NET Framework 2.0 公共语言运行库 (CLR) 的集成使开发人员可以将重要的业务逻辑与存储过程合并，而 T-SQL 和 XML 中的新增功能扩展了数据操作的可用范围以及开发人员可用的存储功能。另一个重要功能是 SQL Server Service Broker，它为数据库应...
SQL Server 2008中Service Broker基础应用(下 ）
邀月周记
04-12 6116
导读：在上篇《SQL Server 2008中Service Broker基础应用(上）》中，简要介绍了Service Broker的一般步骤，本文继续介绍Service Broker的设置会话优先级，存储过程中实现。
SQL Server Service Broker – 消息队列
weixin_33774883的博客
03-10 450
SQL Server Service Broker – 消息队列   Service Broker 是集成在SQL Server 2005/2008内的消息服务，允许数据库应用程序单向（one-way）或双向（two-way）轻松交换消息。Service Broker 两个重要的优点是： （1）异步消息（Asynchronous messages） （2）确保到达目的地（Guarant...
Pro SQL Server 2008 Service Broker 数据库
07-12
- **配置与设置**：学习如何在SQL Server中启用和配置Service Broker，包括创建服务、定义消息类型和协议等步骤。 - **消息路由**：掌握如何设定消息路由策略，确保消息能到达正确的目的地。 - **安全性配置**：学习...
Pro SQL Server 2008 Service Broker
12-29
### Pro SQL Server 2008 Service Broker：深入解析与应用 #### 一、SQL Server 2008 Service Broker简介 SQL Server 2008 Service Broker 是一种基于消息的平台，它允许应用程序在数据库之间异步地传递消息。这种...
SQL Server 2005 ServiceBroker详解(二)
11-27
在提供的资源中，`20060828--SQL Server 2005 ServiceBroker详解(二).pdf` 文件很可能包含了关于如何设置、配置和使用Service Broker 的详细教程，包括实例演示和最佳实践，这对于理解和掌握这一特性非常有帮助。...
SQL Server Service Broker基础知识
07-26
下面是一份关于 SQL Server Service Broker 基础知识介绍的PPT 文档，可以下载。 演讲者：EntLib.com MSN & Email：entlib@hotmail.com 如有任何技术问题或者培训需求，欢迎和我们联系或者留言。
SQL Server Service Broker 疑难杂症汇总
07-26
Service Broker 是 Microsoft SQL Server 2005/2008/2012 中的新技术，它可帮助数据库开发人员生成安全、可靠且可伸缩的应用程序。本文档由 EntLib.com team 编写。
sql server broker基本用法
04-28
sql server broker基本用法
用SQL语句解决Service Broker未启动的问题
06-28
有时候会遇到这样的问题：未启用当前数据库的 SQL Server Service Broker，因此查询通知不受支持。如果希望使用通知，请为此数据库启用 Service Broker。
SQL Server Service Broker 简单例子 （转）
weixin_33941350的博客
07-22 303
SQL Server Service Broker服务体系结构 消息类型 — 定义应用程序间交换的消息的名称。还可以选择是否验证消息。约定 — 指定给定会话中的消息方向和消息类型。队列 — 存储消息。此存储机制使服务间可以进行异步通信。Service Broker 队列还有其他优点，比如自动锁定同一个会话组中的消息。服务 — 是可寻址的会话端点。Service Broker 消息从一个服务发送到...
SQL Server Service Broker
我的地盘
08-20 1227
http://msdn.microsoft.com/zh-cn/library/bb522893.aspx SQL Server Service Broker 为 SQL Server 数据库引擎 中的消息和队列应用程序提供本机支持。 这使开发人员可以更轻松地创建使用数据库引擎组件在完全不同的数据库之间进行通信的复杂应用程序。 开发人员可以使用 Service Broker 轻松生成
启用数据库的 SQL Server Service Broker
weixin_30379531的博客
05-15 105
ALTER DATABASE DevAssistant SET NEW_BROKER WITH ROLLBACK IMMEDIATE; ALTER DATABASE DevAssistantSET ENABLE_BROKER; 转载于:https://www.cnblogs.com/pgboy/p/5496578.html
sqlserver 使用 service broker 代替触发器
xkf1119的专栏
05-29 591
轉發自: https://blog.csdn.net/superwfei/article/details/78327376我们在 sqlserver触发器根据columns_updated内容生成动态更新列 中，进行了初步探讨，但是在实际应用中发现一个很尴尬的问题，那就是批量更新数据时，效率感人比如表A，有20个字段，500万行数据，其中有nvarchar(max)字段5个，当执行 update ...
启动数据库SQL Server Service Broker
learn_2010的专栏
04-10 2345
1. 启动数据库SQL Server Service Broker（未启用当前数据库的 SQL Server Service Broker,因此查询通知不受支持） sql语句： --查询是否启用 1启用 SELECT is_broker_enabled FROM sys.databases WHERE name = '【数据库名称】' --启用数据库Broker ALTER
SQL Server 2008 Service Broker基础教程：异步消息处理
"SQL Server 2008中Service Broker基础应用" SQL Server 2008中的Service Broker是一项强大的特性，它为数据库间的异步通信提供了可靠且高效的方法。Service Broker通过消息队列实现数据的传输，确保消息的一致性、...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值