concept Service Broker in category sql

This is an excerpt from Manning's book SQL Server MVP Deep Dives Vol. 2. Login to get full access to this book.

Service Broker has been a part of SQL Server since its 2005 version, but it’s still not widely used. This may be due to the lack of a graphical user interface in SSMS, an overly complex setup, and the need to learn XML programming in SQL Server (all messages are in XML format). This is why I’ll give you a template that you can use for secure communication between two instances of SQL Server.

I’ve successfully used this template in a project that required gathering auditing data from multiple servers to a single central location and another project that used Service Broker for custom replication between two databases in combination with Change Data Capture in SQL Server 2008. Another use for Service Broker is having an order-receiving stored procedure switch to an asynchronous execution in peak time. This involved no change to application code at all. The possibilities are endless.

You’ll have two instances: DataSender (the initiator) and DataReceiver (the target). Data will be sent from the initiator to the target. There are two parts of the Service Broker set up on each instance. The first step is to enable the Service Broker functionality at the instance level (endpoint, certificate-based security, and so forth) in the master database. The second step is to create Service Broker objects (for example, message types, contracts, queues, and routes) and functionality (such as activation stored procedures and programming) at the database level.

Let’s look at what you have to do in the master database on the DataReceiver instance. All the code is run under the SA account. You use two databases: DBSender on the DataSender instance and DBReceiver on the DataReceiver instance. Note that if you have the instances on the same physical server, you need to use different ports for each instance. To make the scripts shorter, I’ve omitted the object existence check and cleanup if they exist. The following listing shows the code that enables Service Broker and sets up transport security on the DataReceiver instance.

Listing 1. Enabling Service Broker and setting up transport security: DataReceiver

USE master -- SET UP TRANSPORT SECURITY -- create a master key for the master database -- DO NOT FORGET THIS PASSWORD AND STORE IT SOMEWHERE SAFE CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Put_Your_Custom_Password_For_DataReceiver_Master_DB_Here' GO -- create certificate for the service broker TCP endpoint for secure --communication between servers CREATE CERTIFICATE CertificateDataTransferReceiver WITH -- BOL: The term subject refers to a field in the metadata of -- the certificate as defined in the X.509 standard SUBJECT = 'CertDataTransferReceiver', -- set the start date yyyyMMdd format START_DATE = '20111001', -- set the expiry date EXPIRY_DATE = '20500101' -- enables the certificate for service broker initiator ACTIVE FOR BEGIN_DIALOG = ON GO -- save certificate to a file and copy it to the DataSender instance -- so we can enable secure connection BACKUP CERTIFICATE CertificateDataTransferReceiver -- this path must be somewhere where we have permissions TO FILE = 'c:\CertificateDataTransferReceiver.cer' GO -- create endpoint which will be used to receive data from DataSender -- instance CREATE ENDPOINT ServiceBrokerEndPoint -- set endpoint to actively listen for connections STATE = STARTED -- set it for TCP traffic only since service broker only supports TCP --protocol -- by convention, 4022 is used but any number between 1024 and 32767 -- is valid. AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( -- authenticate connections with our certificate AUTHENTICATION = CERTIFICATE CertificateDataTransferReceiver, -- default value is REQUIRED encryption but let's just set it --to SUPPORTED -- SUPPORTED means that the data is encrypted only if the -- opposite endpoint specifies either SUPPORTED or REQUIRED. ENCRYPTION = SUPPORTED ) GO -- Finally grant the connect permissions to public. GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint TO PUBLIC

copy

As you can see, not much code is needed to enable Service Broker; comments take up most of it. The most important parts are saving the master key password somewhere safe and copying the certificate to the DataSender instance. You’ll use this certificate on the DataSender instance to authorize the login that will be sending data through Service Broker. This is why the setup in the master database on the DataSender instance includes creating logins and users and applying permissions (see the next listing).

Listing 2. Enabling Service Broker and setting up transport security: DataSender

USE master -- SET UP TRANSPORT SECURITY -- create the login that will be used to send the data through the Endpoint CREATE LOGIN LoginDataTransferSender WITH PASSWORD = 'Login__DataTransferSender_Password' GO -- Create a user for our login CREATE USER UserDataTransferSender FOR LOGIN LoginDataTransferSender GO -- DO NOT FORGET THIS PASSWORD AND STORE IT SOMEWHERE SAFE CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Put_Your_Custom_Password_For_Senders_Master_DB_Here' GO -- create certificate for the service broker TCP endpoint for secure -- communication between servers CREATE CERTIFICATE CertificateDataTransferSender WITH SUBJECT = 'CertDataTransferSender', START_DATE = '20111001', EXPIRY_DATE = '20500101' ACTIVE FOR BEGIN_DIALOG = ON GO -- create a certificate from the file we've copied over from the -- DataReceiver instance to our c: drive -- and use it to authorize the UserDataTransferSender user CREATE CERTIFICATE CertificateDataTransferReceiver AUTHORIZATION UserDataTransferSender FROM FILE = 'c:\CertificateDataTransferReceiver.cer' GO -- create endpoint which will be used to send data to the DataReceiver CREATE ENDPOINT ServiceBrokerEndPoint STATE = STARTED AS TCP (LISTENER_PORT = 4022) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE CertificateDataTransferSender, ENCRYPTION = SUPPORTED ) -- grant the connect permissions to the endpoint to the login we've created GRANT CONNECT ON ENDPOINT::ServiceBrokerEndPoint TO LoginDataTransferSender

copy

Figure 1. Service Broker objects