One of the most interesting new features in SQL Server 2005 is Service Broker. If you need more details on what it is and why you would want to use it, I will let the expert tell you. But, if you want to get it setup and configured on your machine now then look no further.
Below is a single T/SQL script, which you can cut and paste into a Query Window, that will have you sending messages from one database to the other in less time than it takes to say, “give me Secure-SQL-Server-Service-Broker now” (try and say that 5 times in quick succession).
It's free The reason why I think SQL Service Broker is going to be so interesting is because it enables cheap, easy, and secure deployment of hub-spoke applications. Why? Service Broker comes free with the SQL Server Express edition, and by using certificate based dialog- and transport-encryption, you can setup secure message based communications with many (potentially 1,000s, if you use the message forwarding capabilities to fan in/out) branch locations that report into a central hub.
By using the new public key/private key certificate based encryption and identity in SQL Server 2005, you don’t have to set up complicated cross branch/company/organization infrastructures (Active Directory, SSL, Kerberos, SQL Authentication etc.). NOTE: The hub does have to run a full version of SQL Server, e.g. Standard or Enterprise (The SQL Server Express Service Broker can only send through/to a full SQL Server edition). I have a feeling there are a lot of problems that can be easily solved with the platform we have built here, it will be interesting to see how people capitalize on this.
I couldn’t find a sample anywhere on the web of how to set up Service Broker with Certificates and CREATE USER WITHOUT LOGIN IN. So, in this quick sample, we use CERTIFICATEs, AUTHORIZATION on objects, The Database MASTER KEY, USERs WITHOUT LOGINs, and QUEUEs. Because of this reliance on other features, setting up Service Broker is not quite as simple as doing your first CREATE TABLE. In fact, it is about as easy as the first time you tried to transitively close a four-way join! Still, once you have done it a few times, it seems to make sense.
Service Broker borrows from the world of Service Orientated Architecture (SOA), the concepts of Initiators and Targets (for the Client/Server among us, you might say the Initiator is the Client and the Target is the Server, but that would make the SOA folks cringe, so we won’t say it).
5 stages to glory The 5 Stages to cross-database Secure Service Broker glory using certificate based dialog security:
1. TARGET - Set up the target database, export a certificate (with public key)
2. INITIATOR - Set up the initiator database, export a certificate (with public key) and import the target certificate
3. TARGET - Set up the target database, import the certificate of the initiator user
4. INITIATOR - Create a stored proc to send messages, and send your first message
5. TARGET - Check the message got to the Target database
If you cut and paste all 5 stages into a single Query window (running against an edition other than SQL Server Express), and then Execute them in one go, you will have set-up, sent, and received your first secure SSB message across databases.
-- STAGE 1: SETUP TARGET DATABASE
USE master;
/* Create Target Database */
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Target') DROP DATABASE Target;
CREATE DATABASE Target
GO
-- Activate the Broker in this database (can only be done with ALTER DATABASE)
ALTER DATABASE Target SET ENABLE_BROKER
USE Target;
-- Create the database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Specify a password here>'
GO
/* Set up Target User, and place certificate (without private key) in a file */
-- Create Target User without login
IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'TargetUser') DROP USER TargetUser;
CREATE USER TargetUser WITHOUT LOGIN
-- Create a Cert for the Initiator user
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'TargetCert') DROP CERTIFICATE TargetCert;
CREATE CERTIFICATE TargetCert
AUTHORIZATION TargetUser
WITH SUBJECT = 'Target Certificate', EXPIRY_DATE = '10/31/2009';
-- Backup the cert up so the Target database can import the cert (public key)
BACKUP CERTIFICATE TargetCert TO FILE = 'c:\TargetCert.cer';
GO
/* Set up SSB */
-- First, we need to create a message type. This message type is very simple and allows any type of content
-- (Drop contract because it binds to message)
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'SubmissionContract') DROP CONTRACT SubmissionContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'Message') DROP MESSAGE TYPE Message;
CREATE MESSAGE TYPE Message VALIDATION = NONE
-- Now create a contract that specifies what type types of messages
CREATE CONTRACT SubmissionContract (Message SENT BY INITIATOR)
-- Set up Target receive queue to hold messages
-- (Need to drop service before queue, due to binding)
IF EXISTS (SELECT * FROM sys.services WHERE name = 'TargetService') DROP SERVICE TargetService;
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = 'TargetQueue') DROP QUEUE TargetQueue;
CREATE QUEUE TargetQueue
-- Create the required service and bind to be above created queue
CREATE SERVICE TargetService
AUTHORIZATION TargetUser
ON QUEUE TargetQueue (SubmissionContract)
-- Create a Local Route for the destination TargetService
IF EXISTS (SELECT * FROM sys.routes WHERE name = 'InitiatorRoute') DROP ROUTE InitiatorRoute;
CREATE ROUTE InitiatorRoute WITH SERVICE_NAME = 'InitiatorService', ADDRESS = 'LOCAL'
GO
-- STAGE 2: SETUP INITIATOR DATABASE
USE master;
/* Create Initiator Database */
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'Initiator') DROP DATABASE Initiator;
CREATE DATABASE Initiator;
GO
-- Activate the Broker in this database
ALTER DATABASE Initiator SET ENABLE_BROKER;
USE Initiator;
-- Create the database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Specify a password here>';
GO
/* Set up Initiator User */
-- Create Initiator User without Login
IF EXISTS (SELECT * FROM sys.sysusers WHERE name = 'InitiatorUser01') DROP USER InitiatorUser01;
CREATE USER InitiatorUser01 WITHOUT LOGIN;
-- Create a Cert with InitiatorUser AUTHORIZATION
-- This links the Cert to the User so SSB uses the CERT for Dialog security
IF EXISTS (SELECT * FROM sys.certificates WHERE name = 'InitiatorCert01') DROP CERTIFICATE InitiatorCert01;
CREATE CERTIFICATE InitiatorCert01
AUTHORIZATION InitiatorUser01
WITH SUBJECT = 'Initiator Certificate 01', EXPIRY_DATE = '10/31/2009';
-- Backup the cert up so the Target can import the cert (public key)
BACKUP CERTIFICATE InitiatorCert01 TO FILE = 'c:\InitiatorCert01.cer';
GO
/* Set up SSB */
-- First, we need to create a message type. Note that our message type is
-- very simple and allows any type of content
-- (Need to drop contract before message, due to binding)
IF EXISTS (SELECT * FROM sys.service_contracts WHERE name = 'SubmissionContract') DROP CONTRACT SubmissionContract;
IF EXISTS (SELECT * FROM sys.service_message_types WHERE name = 'Message') DROP MESSAGE TYPE Message;
CREATE MESSAGE TYPE Message VALIDATION = NONE;
-- Once the message type has been created, we need to create a contract