Cross-instance Service Broker message exchange for Azure SQL Managed Instance

Published Mar 17 2021 07:58 AM 1,870 Views
Microsoft

The Service Broker component of Azure SQL Managed Instance allows you to compose  applications from independent services, by providing native support for reliable and secure message exchange between the databases attached to the services.

Thanks to the support for cross-instance message exchange, which is in preview as of March 2021, Azure SQL Managed Instance can host a data tier of applications that distribute work across multiple instances that may reside in different Azure regions.

 

Establishing trust between instances

For two instances to be able to send messages to each other using Service Broker, you must exchange public keys of the certificates linked to their local Service Broker endpoints first. Installing a certificate with public key by authorized person constitutes a statement of trust in the other instance that holds the corresponding private key.

Traditionally, exchanging certificates between the instances of SQL Server was a manual operation that consisted of extracting the public key, providing it to the other instance in a secure way, and installing it on the other instance. With Managed Instance this process has been significantly simplified thanks to the Server Trust Groups.

You establish the trust between the instances by creating server trust group starting from one instance and adding another instance to the group. It’s as simple as that.

 

CreateTrustGroup.png

 

To establish trust among multiple instances, create new server trust group for each pair of instances. During the preview of server trust group feature, maximum number of members of group is limited to two.

 

Allowing network traffic between the instances

If your instances reside in the same virtual network subnet, there are no actions needed to allow the Service Broker traffic between them. If they are placed in different virtual network subnets, you need to enable both the inbound and outbound traffic between the subnets on a set of ports.

Service Broker connections always use redirect connection policy regardless of the connection type configured on the managed instance. Instance initiating the connection reaches to the gateway within the target instance’s subnet to obtain actual IP address and port of the target instance, and subsequent traffic flows directly to the target instance bypassing the gateway.

Gateway listens for incoming Service Broker connections on port 4022 which is default port for Service Broker endpoints. Connection is routed to gateway because the DNS name of managed instance from the Service Broker route resolves to the IP address of the gateway. Gateway then redirects connection to the actual IP address of instance and its automatically assigned port from the range 11000-11999. You cannot change the port used by gateway or instance for service broker traffic. To allow Service Broker traffic between the instances, port 4022 and port range 11000-11999 must be allowed both for the inbound and outbound traffic in both subnets.

 

Creating Service Broker objects

If you are working with Service Broker for the first time, you may be surprised by how many different objects you must create to be able to send a message. No worries, the entire concept is intuitive, and you will quickly adopt it. If you’d like to explore specific aspects of the feature, I’d strongly recommend you to start from here.

In this article we will cover the simplest case of message exchange between two instances that have trust relation already established and network traffic allowed.

 

Let’s start from creating the necessary objects on the first instance sqlmi-ssb-instance1:

 

--Create the message types
CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
       VALIDATION = NONE;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
       VALIDATION = NONE;
GO

--Create the contract
CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
      ([//BothDB/2InstSample/RequestMessage]
         SENT BY INITIATOR,
       [//BothDB/2InstSample/ReplyMessage]
         SENT BY TARGET
      );
GO

--Create the initiator queue and service
CREATE QUEUE SQLMIQueue1;

CREATE SERVICE [//SQLMIDB1/2InstSample/SQLMIService]
       ON QUEUE SQLMIQueue1
       ([//BothDB/2InstSample/SimpleContract]);
GO

--Grant the access to the service to a specific user, or public for quick testing:
GRANT SEND
      ON SERVICE::[//SQLMIDB1/2InstSample/SQLMIService]
      TO [public]
GO

--Create route:
CREATE ROUTE SQLMIRoute1
WITH SERVICE_NAME = N'//SQLMIDB2/2InstSample/SQLMIService',
     ADDRESS = N'TCP://sqlmi-ssb-instance2.50d0d943a5ad.database.windows.net:4022';

 

 

 

 

 

 

Create the corresponding objects on the second instance sqlmi-ssb-instance2:

 

--Create the message types
CREATE MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
       VALIDATION = NONE;
CREATE MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
       VALIDATION = NONE;
GO

--Create the contract
CREATE CONTRACT [//BothDB/2InstSample/SimpleContract]
      ([//BothDB/2InstSample/RequestMessage]
         SENT BY INITIATOR,
       [//BothDB/2InstSample/ReplyMessage]
         SENT BY TARGET
      );
GO

--Create the initiator queue and service
CREATE QUEUE SQLMIQueue2;

CREATE SERVICE [//SQLMIDB2/2InstSample/SQLMIService]
       ON QUEUE SQLMIQueue2
       ([//BothDB/2InstSample/SimpleContract]);
GO

--Grant send to [public] - because there is no remote service binding
GRANT SEND
      ON SERVICE::[//SQLMIDB2/2InstSample/SQLMIService]
      TO [public]
GO

--Create route
CREATE ROUTE SQLMIRoute2
WITH SERVICE_NAME = N'//SQLMIDB1/2InstSample/SQLMIService',
     ADDRESS = N'TCP://sqlmi-ssb-instance1.50d0d943a5ad.database.windows.net:4022';

 

 

 

 

 

 

Sending and receiving messages

Now we can finally send the message from the first instance:

 

--Send message:
DECLARE @InitDlgHandle UNIQUEIDENTIFIER;
DECLARE @RequestMsg NVARCHAR(100);

BEGIN TRANSACTION;

BEGIN DIALOG @InitDlgHandle
     FROM SERVICE [//SQLMIDB1/2InstSample/SQLMIService]
     TO SERVICE N'//SQLMIDB2/2InstSample/SQLMIService'
     ON CONTRACT [//BothDB/2InstSample/SimpleContract]
     WITH
         --ENCRYPTION = ON;
		 ENCRYPTION = OFF;
SELECT @RequestMsg = N'<RequestMsg>Hello other world!</RequestMsg>';

SEND ON CONVERSATION @InitDlgHandle
     MESSAGE TYPE [//BothDB/2InstSample/RequestMessage]
     (@RequestMsg);

SELECT @RequestMsg AS SentRequestMsg;

COMMIT TRANSACTION;
GO

 

 

 

 

Receive message on the second instance and reply to the first instance:

 

DECLARE @RecvReqDlgHandle UNIQUEIDENTIFIER;
DECLARE @RecvReqMsg NVARCHAR(100);
DECLARE @RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR
( RECEIVE TOP(1)
    @RecvReqDlgHandle = conversation_handle,
    @RecvReqMsg = message_body,
    @RecvReqMsgName = message_type_name
  FROM SQLMIQueue2
), TIMEOUT 1000;

SELECT @RecvReqMsg AS ReceivedRequestMsg;

IF @RecvReqMsgName = N'//BothDB/2InstSample/RequestMessage'
BEGIN
     DECLARE @ReplyMsg NVARCHAR(100);
     SELECT @ReplyMsg =
        N'<ReplyMsg>Hello from the other world!</ReplyMsg>';

     SEND ON CONVERSATION @RecvReqDlgHandle
          MESSAGE TYPE [//BothDB/2InstSample/ReplyMessage]
          (@ReplyMsg);
END

SELECT @ReplyMsg AS SentReplyMsg;

COMMIT TRANSACTION;
GO

 

 

 

 

Specificities and limitations of Service Broker in SQL Managed Instance

In addition to the simplified certificate exchange, there are few other differences with Service Broker in Azure SQL Managed Instance compared to SQL Server at this moment:

  • Service Broker endpoint is automatically enabled on every Azure SQL Managed Instance and cannot be disabled.
  • Message forwarding cannot be enabled.
  • Port specified on every route created needs to be 4022
  • Route address cannot specify ‘TRANSPORT’
  • Remote service binding cannot be created

 

Resources

Documents already referenced through the article:

Service broker documentation

Server trust groups

 

Conceptual articles, older but still valid and very useful:

Typical use cases of Service Broker

Advantages of Service Broker

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2217323%22%20slang%3D%22en-US%22%3ECross-instance%20Service%20Broker%20message%20exchange%20for%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2217323%22%20slang%3D%22en-US%22%3E%3CP%3EThe%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fsql-server-service-broker%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EService%20Broker%3C%2FA%3E%26nbsp%3Bcomponent%20of%20Azure%20SQL%20Managed%20Instance%20allows%20you%20to%20compose%20%26nbsp%3Bapplications%20from%20independent%20services%2C%20by%20providing%20native%20support%20for%20reliable%20and%20secure%20message%20exchange%20between%20the%20databases%20attached%20to%20the%20services.%3C%2FP%3E%0A%3CP%3EThanks%20to%20the%20support%20for%20cross-instance%20message%20exchange%2C%20which%20is%20in%20preview%20as%20of%20March%202021%2C%20Azure%20SQL%20Managed%20Instance%20can%20host%20a%20data%20tier%20of%20applications%20that%20distribute%20work%20across%20multiple%20instances%20that%20may%20reside%20in%20different%20Azure%20regions.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--499198546%22%20id%3D%22toc-hId--499197400%22%3EEstablishing%20trust%20between%20instances%3C%2FH2%3E%0A%3CP%3EFor%20two%20instances%20to%20be%20able%20to%20send%20messages%20to%20each%20other%20using%20Service%20Broker%2C%20you%20must%20exchange%20public%20keys%20of%20the%20certificates%20linked%20to%20their%20local%20Service%20Broker%20endpoints%20first.%20Installing%20a%20certificate%20with%20public%20key%20by%20authorized%20person%20constitutes%20a%20statement%20of%20trust%20in%20the%20other%20instance%20that%20holds%20the%20corresponding%20private%20key.%3C%2FP%3E%0A%3CP%3ETraditionally%2C%20exchanging%20certificates%20between%20the%20instances%20of%20SQL%20Server%20was%20a%20manual%20operation%20that%20consisted%20of%20extracting%20the%20public%20key%2C%20providing%20it%20to%20the%20other%20instance%20in%20a%20secure%20way%2C%20and%20installing%20it%20on%20the%20other%20instance.%20With%20Managed%20Instance%20this%20process%20has%20been%20significantly%20simplified%20thanks%20to%20the%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Fserver-trust-group-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EServer%20Trust%20Groups%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EYou%20establish%20the%20trust%20between%20the%20instances%20by%20creating%20server%20trust%20group%20starting%20from%20one%20instance%20and%20adding%20another%20instance%20to%20the%20group.%20It%E2%80%99s%20as%20simple%20as%20that.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22CreateTrustGroup.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F264851i7869ADCF2CAE900E%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22CreateTrustGroup.png%22%20alt%3D%22CreateTrustGroup.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20establish%20trust%20among%20multiple%20instances%2C%20create%20new%20server%20trust%20group%20for%20each%20pair%20of%20instances.%20During%20the%20preview%20of%20server%20trust%20group%20feature%2C%20maximum%20number%20of%20members%20of%20group%20is%20limited%20to%20two.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-1988314287%22%20id%3D%22toc-hId-1988315433%22%3EAllowing%20network%20traffic%20between%20the%20instances%3C%2FH2%3E%0A%3CP%3EIf%20your%20instances%20reside%20in%20the%20same%20virtual%20network%20subnet%2C%20there%20are%20no%20actions%20needed%20to%20allow%20the%20Service%20Broker%20traffic%20between%20them.%20If%20they%20are%20placed%20in%20different%20virtual%20network%20subnets%2C%20you%20need%20to%20enable%20both%20the%20inbound%20and%20outbound%20traffic%20between%20the%20subnets%20on%20a%20set%20of%20ports.%3C%2FP%3E%0A%3CP%3EService%20Broker%20connections%20always%20use%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Fconnection-types-overview%23redirect-connection-type%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Eredirect%20connection%20policy%3C%2FA%3E%20regardless%20of%20the%20connection%20type%20configured%20on%20the%20managed%20instance.%20Instance%20initiating%20the%20connection%20reaches%20to%20the%20gateway%20within%20the%20target%20instance%E2%80%99s%20subnet%20to%20obtain%20actual%20IP%20address%20and%20port%20of%20the%20target%20instance%2C%20and%20subsequent%20traffic%20flows%20directly%20to%20the%20target%20instance%20bypassing%20the%20gateway.%3C%2FP%3E%0A%3CP%3EGateway%20listens%20for%20incoming%20Service%20Broker%20connections%20on%20port%204022%20which%20is%20default%20port%20for%20Service%20Broker%20endpoints.%20Connection%20is%20routed%20to%20gateway%20because%20the%20DNS%20name%20of%20managed%20instance%20from%20the%20Service%20Broker%20route%20resolves%20to%20the%20IP%20address%20of%20the%20gateway.%20Gateway%20then%20redirects%20connection%20to%20the%20actual%20IP%20address%20of%20instance%20and%20its%20automatically%20assigned%20port%20from%20the%20range%2011000-11999.%20You%20cannot%20change%20the%20port%20used%20by%20gateway%20or%20instance%20for%20service%20broker%20traffic.%20To%20allow%20Service%20Broker%20traffic%20between%20the%20instances%2C%20port%204022%20and%20port%20range%2011000-11999%20must%20be%20allowed%20both%20for%20the%20inbound%20and%20outbound%20traffic%20in%20both%20subnets.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-180859824%22%20id%3D%22toc-hId-180860970%22%3ECreating%20Service%20Broker%20objects%3C%2FH2%3E%0A%3CP%3EIf%20you%20are%20working%20with%20Service%20Broker%20for%20the%20first%20time%2C%20you%20may%20be%20surprised%20by%20how%20many%20different%20objects%20you%20must%20create%20to%20be%20able%20to%20send%20a%20message.%20No%20worries%2C%20the%20entire%20concept%20is%20intuitive%2C%20and%20you%20will%20quickly%20adopt%20it.%20If%20you%E2%80%99d%20like%20to%20explore%20specific%20aspects%20of%20the%20feature%2C%20I%E2%80%99d%20strongly%20recommend%20you%20to%20start%20from%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Fsql%2Fsql-server-2008-r2%2Fbb522893(v%3Dsql.105)%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehere%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EIn%20this%20article%20we%20will%20cover%20the%20simplest%20case%20of%20message%20exchange%20between%20two%20instances%20that%20have%20trust%20relation%20already%20established%20and%20network%20traffic%20allowed.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet%E2%80%99s%20start%20from%20creating%20the%20necessary%20objects%20on%20the%20first%20instance%20%3CEM%3Esqlmi-ssb-instance1%3C%2FEM%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--Create%20the%20message%20types%0ACREATE%20MESSAGE%20TYPE%20%5B%2F%2FBothDB%2F2InstSample%2FRequestMessage%5D%0A%20%20%20%20%20%20%20VALIDATION%20%3D%20NONE%3B%0ACREATE%20MESSAGE%20TYPE%20%5B%2F%2FBothDB%2F2InstSample%2FReplyMessage%5D%0A%20%20%20%20%20%20%20VALIDATION%20%3D%20NONE%3B%0AGO%0A%0A--Create%20the%20contract%0ACREATE%20CONTRACT%20%5B%2F%2FBothDB%2F2InstSample%2FSimpleContract%5D%0A%20%20%20%20%20%20(%5B%2F%2FBothDB%2F2InstSample%2FRequestMessage%5D%0A%20%20%20%20%20%20%20%20%20SENT%20BY%20INITIATOR%2C%0A%20%20%20%20%20%20%20%5B%2F%2FBothDB%2F2InstSample%2FReplyMessage%5D%0A%20%20%20%20%20%20%20%20%20SENT%20BY%20TARGET%0A%20%20%20%20%20%20)%3B%0AGO%0A%0A--Create%20the%20initiator%20queue%20and%20service%0ACREATE%20QUEUE%20SQLMIQueue1%3B%0A%0ACREATE%20SERVICE%20%5B%2F%2FSQLMIDB1%2F2InstSample%2FSQLMIService%5D%0A%20%20%20%20%20%20%20ON%20QUEUE%20SQLMIQueue1%0A%20%20%20%20%20%20%20(%5B%2F%2FBothDB%2F2InstSample%2FSimpleContract%5D)%3B%0AGO%0A%0A--Grant%20the%20access%20to%20the%20service%20to%20a%20specific%20user%2C%20or%20public%20for%20quick%20testing%3A%0AGRANT%20SEND%0A%20%20%20%20%20%20ON%20SERVICE%3A%3A%5B%2F%2FSQLMIDB1%2F2InstSample%2FSQLMIService%5D%0A%20%20%20%20%20%20TO%20%5Bpublic%5D%0AGO%0A%0A--Create%20route%3A%0ACREATE%20ROUTE%20SQLMIRoute1%0AWITH%20SERVICE_NAME%20%3D%20N'%2F%2FSQLMIDB2%2F2InstSample%2FSQLMIService'%2C%0A%20%20%20%20%20ADDRESS%20%3D%20N'TCP%3A%2F%2Fsqlmi-ssb-instance2.50d0d943a5ad.database.windows.net%3A4022'%3B%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECreate%20the%20corresponding%20objects%20on%20the%20second%20instance%20%3CEM%3Esqlmi-ssb-instance2%3C%2FEM%3E%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--Create%20the%20message%20types%0ACREATE%20MESSAGE%20TYPE%20%5B%2F%2FBothDB%2F2InstSample%2FRequestMessage%5D%0A%20%20%20%20%20%20%20VALIDATION%20%3D%20NONE%3B%0ACREATE%20MESSAGE%20TYPE%20%5B%2F%2FBothDB%2F2InstSample%2FReplyMessage%5D%0A%20%20%20%20%20%20%20VALIDATION%20%3D%20NONE%3B%0AGO%0A%0A--Create%20the%20contract%0ACREATE%20CONTRACT%20%5B%2F%2FBothDB%2F2InstSample%2FSimpleContract%5D%0A%20%20%20%20%20%20(%5B%2F%2FBothDB%2F2InstSample%2FRequestMessage%5D%0A%20%20%20%20%20%20%20%20%20SENT%20BY%20INITIATOR%2C%0A%20%20%20%20%20%20%20%5B%2F%2FBothDB%2F2InstSample%2FReplyMessage%5D%0A%20%20%20%20%20%20%20%20%20SENT%20BY%20TARGET%0A%20%20%20%20%20%20)%3B%0AGO%0A%0A--Create%20the%20initiator%20queue%20and%20service%0ACREATE%20QUEUE%20SQLMIQueue2%3B%0A%0ACREATE%20SERVICE%20%5B%2F%2FSQLMIDB2%2F2InstSample%2FSQLMIService%5D%0A%20%20%20%20%20%20%20ON%20QUEUE%20SQLMIQueue2%0A%20%20%20%20%20%20%20(%5B%2F%2FBothDB%2F2InstSample%2FSimpleContract%5D)%3B%0AGO%0A%0A--Grant%20send%20to%20%5Bpublic%5D%20-%20because%20there%20is%20no%20remote%20service%20binding%0AGRANT%20SEND%0A%20%20%20%20%20%20ON%20SERVICE%3A%3A%5B%2F%2FSQLMIDB2%2F2InstSample%2FSQLMIService%5D%0A%20%20%20%20%20%20TO%20%5Bpublic%5D%0AGO%0A%0A--Create%20route%0ACREATE%20ROUTE%20SQLMIRoute2%0AWITH%20SERVICE_NAME%20%3D%20N'%2F%2FSQLMIDB1%2F2InstSample%2FSQLMIService'%2C%0A%20%20%20%20%20ADDRESS%20%3D%20N'TCP%3A%2F%2Fsqlmi-ssb-instance1.50d0d943a5ad.database.windows.net%3A4022'%3B%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1626594639%22%20id%3D%22toc-hId--1626593493%22%3ESending%20and%20receiving%20messages%3C%2FH2%3E%0A%3CP%3ENow%20we%20can%20finally%20send%20the%20message%20from%20the%20first%20instance%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E--Send%20message%3A%0ADECLARE%20%40InitDlgHandle%20UNIQUEIDENTIFIER%3B%0ADECLARE%20%40RequestMsg%20NVARCHAR(100)%3B%0A%0ABEGIN%20TRANSACTION%3B%0A%0ABEGIN%20DIALOG%20%40InitDlgHandle%0A%20%20%20%20%20FROM%20SERVICE%20%5B%2F%2FSQLMIDB1%2F2InstSample%2FSQLMIService%5D%0A%20%20%20%20%20TO%20SERVICE%20N'%2F%2FSQLMIDB2%2F2InstSample%2FSQLMIService'%0A%20%20%20%20%20ON%20CONTRACT%20%5B%2F%2FBothDB%2F2InstSample%2FSimpleContract%5D%0A%20%20%20%20%20WITH%0A%20%20%20%20%20%20%20%20%20--ENCRYPTION%20%3D%20ON%3B%0A%20%20%20ENCRYPTION%20%3D%20OFF%3B%0ASELECT%20%40RequestMsg%20%3D%20N'%3CREQUESTMSG%3EHello%20other%20world!%3C%2FREQUESTMSG%3E'%3B%0A%0ASEND%20ON%20CONVERSATION%20%40InitDlgHandle%0A%20%20%20%20%20MESSAGE%20TYPE%20%5B%2F%2FBothDB%2F2InstSample%2FRequestMessage%5D%0A%20%20%20%20%20(%40RequestMsg)%3B%0A%0ASELECT%20%40RequestMsg%20AS%20SentRequestMsg%3B%0A%0ACOMMIT%20TRANSACTION%3B%0AGO%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EReceive%20message%20on%20the%20second%20instance%20and%20reply%20to%20the%20first%20instance%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EDECLARE%20%40RecvReqDlgHandle%20UNIQUEIDENTIFIER%3B%0ADECLARE%20%40RecvReqMsg%20NVARCHAR(100)%3B%0ADECLARE%20%40RecvReqMsgName%20sysname%3B%0A%0ABEGIN%20TRANSACTION%3B%0A%0AWAITFOR%0A(%20RECEIVE%20TOP(1)%0A%20%20%20%20%40RecvReqDlgHandle%20%3D%20conversation_handle%2C%0A%20%20%20%20%40RecvReqMsg%20%3D%20message_body%2C%0A%20%20%20%20%40RecvReqMsgName%20%3D%20message_type_name%0A%20%20FROM%20SQLMIQueue2%0A)%2C%20TIMEOUT%201000%3B%0A%0ASELECT%20%40RecvReqMsg%20AS%20ReceivedRequestMsg%3B%0A%0AIF%20%40RecvReqMsgName%20%3D%20N'%2F%2FBothDB%2F2InstSample%2FRequestMessage'%0ABEGIN%0A%20%20%20%20%20DECLARE%20%40ReplyMsg%20NVARCHAR(100)%3B%0A%20%20%20%20%20SELECT%20%40ReplyMsg%20%3D%0A%20%20%20%20%20%20%20%20N'%3CREPLYMSG%3EHello%20from%20the%20other%20world!%3C%2FREPLYMSG%3E'%3B%0A%0A%20%20%20%20%20SEND%20ON%20CONVERSATION%20%40RecvReqDlgHandle%0A%20%20%20%20%20%20%20%20%20%20MESSAGE%20TYPE%20%5B%2F%2FBothDB%2F2InstSample%2FReplyMessage%5D%0A%20%20%20%20%20%20%20%20%20%20(%40ReplyMsg)%3B%0AEND%0A%0ASELECT%20%40ReplyMsg%20AS%20SentReplyMsg%3B%0A%0ACOMMIT%20TRANSACTION%3B%0AGO%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-860918194%22%20id%3D%22toc-hId-860919340%22%3ESpecificities%20and%20limitations%20of%20Service%20Broker%20in%20SQL%20Managed%20Instance%3C%2FH2%3E%0A%3CP%3EIn%20addition%20to%20the%20simplified%20certificate%20exchange%2C%20there%20are%20few%20other%20differences%20with%20Service%20Broker%20in%20Azure%20SQL%20Managed%20Instance%20compared%20to%20SQL%20Server%20at%20this%20moment%3A%3C%2FP%3E%0A%3CUL%20class%3D%22lia-list-style-type-circle%22%3E%0A%3CLI%3EService%20Broker%20endpoint%20is%20automatically%20enabled%20on%20every%20Azure%20SQL%20Managed%20Instance%20and%20cannot%20be%20disabled.%3C%2FLI%3E%0A%3CLI%3EMessage%20forwarding%20cannot%20be%20enabled.%3C%2FLI%3E%0A%3CLI%3EPort%20specified%20on%20every%20route%20created%20needs%20to%20be%204022%3C%2FLI%3E%0A%3CLI%3ERoute%20address%20cannot%20specify%20%E2%80%98TRANSPORT%E2%80%99%3C%2FLI%3E%0A%3CLI%3ERemote%20service%20binding%20cannot%20be%20created%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--946536269%22%20id%3D%22toc-hId--946535123%22%3EResources%3C%2FH2%3E%0A%3CP%3EDocuments%20already%20referenced%20through%20the%20article%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fsql-server-service-broker%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EService%20broker%20documentation%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Fserver-trust-group-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EServer%20trust%20groups%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EConceptual%20articles%2C%20older%20but%20still%20valid%20and%20very%20useful%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Fsql%2Fsql-server-2008-r2%2Fms166071(v%3Dsql.105)%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ETypical%20use%20cases%20of%20Service%20Broker%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fprevious-versions%2Fsql%2Fsql-server-2008-r2%2Fms166063(v%3Dsql.105)%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAdvantages%20of%20Service%20Broker%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2217323%22%20slang%3D%22en-US%22%3E%3CP%3EThe%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Fdatabase-engine%2Fconfigure-windows%2Fsql-server-service-broker%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EService%20Broker%3C%2FA%3E%26nbsp%3Bcomponent%20of%20Azure%20SQL%20Managed%20Instance%20allows%20you%20to%20compose%20%26nbsp%3Bapplications%20from%20independent%20services%2C%20by%20providing%20native%20support%20for%20reliable%20and%20secure%20message%20exchange%20between%20the%20databases%20attached%20to%20the%20services.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2217323%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Managed%20Instance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Mar 17 2021 10:33 AM
Updated by: