dcsimg

Writing SSBS Applications Across Instances - Getting Environments Ready

January 21, 2011

SQL Server Service Broker (SSBS) allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself. Arshad Ali shows you how to write an SSBS application when the Initiator and Target are in two different databases on two different SQL Server instances.

Introduction

SQL Server Service Broker (SSBS) is a new architecture (introduced with SQL Server 2005 and enhanced further in SQL Server 2008 and SQL Server 2008 R2) which allows you to write asynchronous, decoupled, distributed, persistent, reliable, scalable and secure queuing/message based applications within the database itself.

In my previous couple of articles, I introduced you SQL Server Service Broker, what it is, how it works, its different components and how are they related to each other. Then I talked about writing SSBS application when both Initiator (Sender) and Target (Receiver) are in same database and SSBS application when both Initiator (Sender) and Target (Receiver) are in different databases on the same SQL Server instance. Now let’s move on and see how to write an SSBS application when the Initiator and Target are in two different databases on two different SQL Server instances (machines).

Problem Statement

In this example, once again I will take the same problem statement as my last articles but this time the Initiator and Target will be in different databases on two different SQL Server instances (machines).

There are two applications, one is the Order application (uses SSBSInitiatorDB database) and another one is the Inventory application (uses SSBSTargetDB database). Before accepting any order from the users, the Order application needs to make sure that the product, which is being ordered is available in the store but for that, the Order application does not want to wait. The Order application will request to check the product stock availability status asynchronously and will continue doing its other work. On the other side, the Inventory application will listen to the request coming into the queue, process it and respond back to the Order application, again asynchronously.

When Initiator and Target are in different databases on the different SQL Server instances

As I said before, depending on the arrangement of Initiator and Target, the architecture can be categorized into three categories:

  1. Initiator and Target in same database
  2. Initiator in one database and Target in another database on same instance
  3. Initiator and Target in separate databases on separate SQL Server instances - This is what we are going to discuss in this article.

The behavior in scenario A and B above is almost same. In these cases, SSBS optimizes performance by writing messages directly to the Target Queue. Although if while writing a message to the Target Queue, it encounters any problem (for example the Target service is not available, Target Queue is disabled, etc.) it keeps that message in the sys.transmission_queue table temporarily so that it can push the message to Target Queue once it is available. Also, in these cases, no network is involved and hence no network encryption is required for these scenarios. However, this is not the case in third scenario; you need to do encryption when communicating across instances or network, more detail follows in this article later.

In this example, I will demonstrate how you will be creating an SSBS application when Initiator and Target are in different databases on two different SQL Server instances (machines).

Although we can enable Service Broker for an existing database and create SSBS objects in it, for simplicity I will be creating new databases, on the two different SQL Server instances for this demonstration.

What I am doing in the script below is creating a database, which will act like an Initiator. Then I am enabling Service Broker on it. Before that, I need to create an endpoint so that SSBS can communicate outside of the SQL Server instance. An endpoint, a SQL Server object, is required so that SSBS can establish/communicate with network addresses when sending/receiving messages across SQL Server instances. By default, there is no endpoint available in SQL Server, hence you need to create one explicitly to communicate outside of theSQL Server instance. This endpoint supports TCP communication protocol and listens on a specific port number (default is 4022). For more information about endpoint, click here.

These endpoints must be in STARTED state so that SSBS can send and receive messages over the network. If for some reason, you want to temporarily pause the communication you can set the state to STOPPED, with ALTER ENDPOINT command, in which case SSBS would not be able to receive messages from outside the current instance or send message to outside the current instance.

Next, I creating a master key and a user, which will be used by the certificate for encryption and making remote connections. Then I create certificate, which will be used for encrypting messages. The certificate needs to backed up and restored (or created from an existing partner’s certificate) at the other communicating partner so that the messages can be decrypted by the receiver of the message. The location, which you specify for backing up/restoring the certificate must be accessible by the account under which the database engine is running.

Finally I create two message types and a contract and then I create an Initiator queue, which will hold the responses (messages) sent back by the Target to the Initiator and Initiator service which will be tied to this queue and will act like an initiator endpoint.

Across instances - Setting up Initiator – Part 1

USE master;
GO
--An endpoint is required so that SSBS can establise/communicate 
--with network addresses when sending messages across SQL Server instances
--These endpoints must be in STARTED mode so that SSBS can send and 
--receive message over the network.
IF EXISTS (SELECT * FROM sys.endpoints WHERE name = N'SSBSInitiatorEP')
     DROP ENDPOINT SSBSInitiatorEP;
GO
--You can notice I have specified Windows authentication and hence
--SSBS will uses Windows authentication connections to send messages 
CREATE ENDPOINT SSBSInitiatorEP
       STATE = STARTED
       AS TCP ( LISTENER_PORT = 4022 )
       FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
--Create Initiator database for this learning session, it will help you to do 
--clean up easily, you can create SSBS objects in any existing database
--also but you need to drop all objects individually if you want to do
--clean up of these objects than dropping a single database
IF EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSBSInitiatorDB')
       DROP DATABASE SSBSInitiatorDB
GO
CREATE DATABASE SSBSInitiatorDB
GO
--By default a database will have service broker enabled, which you can verify 
--with is_broker_enabled column of the below resultset
SELECT name, service_broker_guid, is_broker_enabled, is_honor_broker_priority_on 
FROM sys.databases WHERE name = 'SSBSInitiatorDB'
GO
--If your database is not enabled for Service Broker becuase you have 
--changed the default setting in Model database, even then you can enable
--service broker for a database with this statement
ALTER DATABASE SSBSInitiatorDB
      SET ENABLE_BROKER;
      --WITH ROLLBACK IMMEDIATE
GO
----To disable service broker for a database
--ALTER DATABASE SSBSInitiatorDB
--      SET DISABLE_BROKER;
--GO
--You need to mark database TRUSTWORTHY so that it can 
--access resource beyond the scope of this database
ALTER DATABASE SSBSInitiatorDB SET TRUSTWORTHY ON
GO
USE SSBSInitiatorDB;
GO
--Creating master key and an user which will be used by certificate 
--for encryption and making remote connections
--Change the strong password for your master key as appropriate
--You can query sys.symmetric_keys to get information 
--abou the database database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'abcd!@#$5678';
GO
CREATE USER SSBSInitiatorUser WITHOUT LOGIN;
GO
--Creating certificate which will be used for encrypting messages. 
CREATE CERTIFICATE SSBSInitiatorCert
       AUTHORIZATION SSBSInitiatorUser
       WITH SUBJECT = N'Certificate For Initiator',
       EXPIRY_DATE = N'12/31/2012';
GO
--The certificate needs to backed up and restored at the other communicating 
--partner so that the messages can be decrypted by the receiver of the message. 
--The location which you specify for backing up/restoring certificate must be 
--accessible by the account under which database engine is running.
BACKUP CERTIFICATE SSBSInitiatorCert
       TO FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSInitiatorCert.cer';
GO
--Create message types which will allow valid xml messages to be sent
--and received, SSBS validates whether a message is well formed XML 
--or not by loading it into XML parser
--Please use XML validation only when required as it has performance overhead 
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckRequest]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckResponse]
       VALIDATION = WELL_FORMED_XML;
GO
--Create a contract which will be used by Service to validate
--what message types are allowed for Initiator and for Target.
--As because communication starts from Initiator hence 
--SENT BY INITIATOR or SENT BY ANY is mandatory
CREATE CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
      ([//SSBSLearning/ProductStockStatusCheckRequest]
       SENT BY INITIATOR,
       [//SSBSLearning/ProductStockStatusCheckResponse]
       SENT BY TARGET
      );
GO
--A Target can also send messages back to Initiator and hence
--you can create a queue for Initiator also
CREATE QUEUE dbo.SSBSLearningInitiatorQueue WITH STATUS = ON;
GO
--Likewsie you would need to create a service which will sit 
--on top of Initiator queue and used by Target to send messages
--back to Initiator
CREATE SERVICE [//SSBSLearning/ProductStockStatusCheck/InitiatorService]
       AUTHORIZATION SSBSInitiatorUser
       ON QUEUE dbo.SSBSLearningInitiatorQueue
       ([//SSBSLearning/ProductStockStatusCheckContract])
GO

Before creating a database, which will act like a Target and enabling Service Broker on it, create an endpoint on the target (as we did with Initiator side) so that the target SSBS can communicate outside of the SQL Server instance. This endpoint must be in STARTED state so that SSBS can send and receive messages over the network.

Next, create a master key and a user, which will be used by the certificate for encryption and making remote connections. Then create a target certificate, which will be used for encrypting messages while sending response messages back to Initiator. The target certificate needs to backed up and restored (or created from the existing partner’s certificate) at the other communicating partner (Initiator), so that the messages can be decrypted by the receiver (Initiator) of the message. The location, which you specify for backing up/restoring the certificate must be accessible by the account under which the database engine is running.

Finally, create the Target queue, which will hold the request (messages) sent by the Initiator to the Target and the Target service, which will be tied to this queue and will act like a target endpoint.

Please note, because this message type and contract will be shared by both the Initiator and Target, they will have the same definitions at both places.

Across instances - Setting up Target – Part 1

USE master;
GO
--An endpoint is required so that SSBS can establise/communicate 
--with network addresses when sending messages across SQL Server instances
--These endpoints must be in STARTED mode so that SSBS can send and 
--receive message over the network.
IF EXISTS (SELECT * FROM master.sys.endpoints WHERE name = N'SSBSTargetEP')
     DROP ENDPOINT SSBSTargetEP;
GO
--You can notice I have specified Windows authentication and hence
--SSBS will uses Windows authentication connections to send messages 
CREATE ENDPOINT SSBSTargetEP
       STATE = STARTED
       AS TCP ( LISTENER_PORT = 4022 )
       FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
--Create target database for this learning session, it will help you to do 
--clean up easily, you can create SSBS objects in any existing database
--also but you need to drop all objects individually if you want to do
--clean up of these objects than dropping a single database
IF EXISTS(SELECT TOP 1 1 FROM sys.databases WHERE name = 'SSBSTargetDB')
       DROP DATABASE SSBSTargetDB
GO
CREATE DATABASE SSBSTargetDB
GO
--By default a database will have service broker enabled, which you can verify 
--with is_broker_enabled column of the below resultset
SELECT name, service_broker_guid, is_broker_enabled, is_honor_broker_priority_on 
FROM sys.databases WHERE name = 'SSBSTargetDB'
GO
--If your database is not enabled for Service Broker becuase you have 
--changed the default setting in Model database, even then you can enable
--service broker for a database with this statement
ALTER DATABASE SSBSTargetDB
      SET ENABLE_BROKER;
      --WITH ROLLBACK IMMEDIATE
GO
----To disable service broker for a database
--ALTER DATABASE SSBSTargetDB
--      SET DISABLE_BROKER;
--GO
--You need to mark database TRUSTWORTHY so that it can 
--resource beyond the scope of this database
ALTER DATABASE SSBSTargetDB SET TRUSTWORTHY ON
GO
USE SSBSTargetDB;
GO
--Creating master key and an user which will be used by certificate 
--for encryption and making remote connections
--Change the strong password for your master key as appropriate
--You can query sys.symmetric_keys to get information 
--abou the database database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = N'abcd!@#$5678';
GO
CREATE USER SSBSTargetUser WITHOUT LOGIN;
GO
--Creating certificate which will be used for encrypting messages. 
CREATE CERTIFICATE SSBSTargetCert 
       AUTHORIZATION SSBSTargetUser
       WITH SUBJECT = 'Certificate For Target',
       EXPIRY_DATE = N'12/31/2012';
GO
--The certificate needs to backed up and restored at the other communicating 
--partner so that the messages can be decrypted by the receiver of the message. 
--The location which you specify for backing up/restoring certificate must be 
--accessible by the account under which database engine is running.
BACKUP CERTIFICATE SSBSTargetCert
       TO FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSTargetCert.cer';
GO
--Create message types which will allow valid xml messages to be sent
--and received, SSBS validates whether a message is well formed XML 
--or not by loading it into XML parser 
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckRequest]
       VALIDATION = WELL_FORMED_XML;
CREATE MESSAGE TYPE
       [//SSBSLearning/ProductStockStatusCheckResponse]
       VALIDATION = WELL_FORMED_XML;
GO
--Create a contract which will be used by Service to validate
--what message types are allowed for Initiator and for Target.
--As because communication starts from Initiator hence 
--SENT BY INITIATOR or SENT BY ANY is mandatory
CREATE CONTRACT [//SSBSLearning/ProductStockStatusCheckContract]
      ([//SSBSLearning/ProductStockStatusCheckRequest]
       SENT BY INITIATOR,
       [//SSBSLearning/ProductStockStatusCheckResponse]
       SENT BY TARGET
      );
GO
--Create a queue which is an internal physical table to hold 
--the messages passed to the service, by default it will be 
--created in default file group, if you want to create it in 
--another file group you need to specify the ON clause with 
--this statement. You can use SELECT statement to query this 
--queue or special table but you can not use other DML statement
--like INSERT, UPDATE and DELETE. You need to use SEND and RECEIVE
--commands to send messages to queue and receive from it
CREATE QUEUE dbo.SSBSLearningTargetQueue WITH STATUS = ON;
GO
--Create a service, which is a logical endpoint which sits on top 
--of a queue on which either message is sent or received. With 
--Service creation you all specify the contract which will be
--used to validate message sent on that service
CREATE SERVICE [//SSBSLearning/ProductStockStatusCheck/TargetService]
       AUTHORIZATION SSBSTargetUser
       ON QUEUE dbo.SSBSLearningTargetQueue
       ([//SSBSLearning/ProductStockStatusCheckContract]);
GO

Next, on the Initiator, create a user and import the target certificate so that encrypted messages (response) from target can be decrypted by the Initiator. Next, create routes. A route is a mapping or a means to locate the target service while sending messages and to locate the initiating service while sending the response back. You can create a route using the CREATE ROUTE command and bind it with the remote service.

When the initiator and target services are in the same instance of SQL Server, no remote service binding is necessary but as in this case, we have the initiator and target services in two different instances and hence we need Remote Service Binding. Remote service binding defines the security credentials to use to communicate with a target/remote service.


Across instances - Setting up Initiator – Part 2 USE SSBSInitiatorDB; GO CREATE USER SSBSTargetUser WITHOUT LOGIN; GO --Resotring target certifacts so that message received --from target can be decrypted CREATE CERTIFICATE SSBSTargetCert AUTHORIZATION SSBSTargetUser FROM FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSTargetCert.cer' GO --A route is a mapping or a means to locate the target service while sending messages --Creating a route to locate the target instance service IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSTargetRoute') DROP ROUTE SSBSTargetRoute; GO CREATE ROUTE SSBSTargetRoute WITH SERVICE_NAME = N'//SSBSLearning/ProductStockStatusCheck/TargetService', ADDRESS = N'TCP://MKTARALIW2K8R2:4022'; GO USE msdb GO IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSInitiatorRoute') DROP ROUTE SSBSInitiatorRoute; GO --Creating a route to locate the local instance service CREATE ROUTE SSBSInitiatorRoute WITH SERVICE_NAME = N'//SSBSLearning/ProductStockStatusCheck/InitiatorService', ADDRESS = N'LOCAL'; GO USE SSBSInitiatorDB GO --Creating remote service binding that associates the SSBSTargetUser --with the target service route CREATE REMOTE SERVICE BINDING SSBSRemoteServiceBindingForTarget TO SERVICE N'//SSBSLearning/ProductStockStatusCheck/TargetService' WITH USER = SSBSTargetUser; GO

Now, on the Target, create a user and import the Initiator certificate so that encrypted messages (request) from Initiator can be decrypted by the Target. Next, create routes. A route is a mapping or a means to locate the Initiator service while sending response messages back. You can create a route using the CREATE ROUTE command and bind it with the remote Initiator service.

When the initiator and target services are in the same instance of SQL Server, no remote service binding is necessary but as in this case, we have initiator and target services in two different instances and hence we need Remote Service Binding. Remote service binding defines the security credentials to use to communicate with a target/remote service. I am also granting SEND permission to SSBSInitiatorUser user to send the message to Target service.


Across instances - Setting up Target – Part 2 USE SSBSTargetDB GO CREATE USER SSBSInitiatorUser WITHOUT LOGIN; GO --Resotring initiator certifacts so that message received --from initiator can be decrypted CREATE CERTIFICATE SSBSInitiatorCert AUTHORIZATION SSBSInitiatorUser FROM FILE = N'\\MKTARALIW2K8R2\Shared\SQLCertificates\SSBSInitiatorCert.cer'; GO --A route is a mapping or a means to locate the target service while sending messages --Creating a route to locate the target instance service IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSInitiatorRoute') DROP ROUTE SSBSInitiatorRoute; GO CREATE ROUTE SSBSInitiatorRoute WITH SERVICE_NAME = N'//SSBSLearning/ProductStockStatusCheck/InitiatorService', ADDRESS = N'TCP://ARSHAD-LAPPY:4022'; GO USE msdb GO --Creating a route to locate the local instance service IF EXISTS (SELECT * FROM sys.routes WHERE name = N'SSBSTargetRoute') DROP ROUTE SSBSTargetRoute; GO CREATE ROUTE SSBSTargetRoute WITH SERVICE_NAME = N'//SSBSLearning/ProductStockStatusCheck/TargetService', ADDRESS = N'LOCAL'; GO USE SSBSTargetDB GO --Grant SEND permission to the user created above to send message --to target service GRANT SEND ON SERVICE::[//SSBSLearning/ProductStockStatusCheck/TargetService] TO SSBSInitiatorUser; GO --Creating remote service binding that associates the SSBSTargetUser --with the target service route CREATE REMOTE SERVICE BINDING SSBSRemoteServiceBindingForInitiator TO SERVICE N'//SSBSLearning/ProductStockStatusCheck/InitiatorService' WITH USER = SSBSInitiatorUser; GO

Conclusion

In this first article of this series of writing SQL Server Service Broker (SSBS) Applications across Instances I step by step demonstrated how you can set up Initiator, Target and create routes between them. In the next article I will demonstrate how to verify the configuration when both Initiator and Target are in different SQL Server instances, how to communicate between them and how to monitor the conversation status between them.

Resources

MSDN SQL Server Service Broker
MSDN Creating Service Broker Objects
MSDN Creating Service Broker Message Types
MSDN CREATE QUEUE (Transact-SQL)
MSDN Contracts

» See All Articles by Columnist Arshad Ali








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers