THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Allen White

Service Broker Basics - Part Deux

Last week I introduced the Service Broker Basics. Today I'd like to cover some of the "plumbing" - the components that allow communication between different servers running Service Broker.

Endpoints. There needs to be a channel for the communications coming in and out of the server, and in the IP world that channel exists in the form of a port. You define the port to be used by defining an Endpoint in the master database.

CREATE ENDPOINT IntEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO

Routes. To get from one place to another Service Broker routes need to be defined. You'll need a route to the remote server defined in the database where your Service Broker application is running, and also one to the local server, and the latter needs to be defined in the msdb database. Defining a remote destination in your application database places the route information in sys.routes, but Service Broker always looks in msdb.sys.routes for any incoming messages to determine where they go.

USE AdventureWorks
GO

CREATE ROUTE DMZRoute
AUTHORIZATION dbo
WITH
SERVICE_NAME = N'//DMZSite/Sync/IntService',
ADDRESS = N'TCP://SQLTBWS:4023'
GO

USE msdb;
GO

CREATE ROUTE IntRoute
AUTHORIZATION dbo
WITH
SERVICE_NAME = N'//IntSite/Sync/IntService',
ADDRESS = N'LOCAL'
GO

One thing I hadn't addressed in my last post was message security. Service Broker allows you to encrypt all messages, preventing network sniffers from discovering the data being sent. To enable this I created certificates at each site, and created a database user without a login to send and receive messages. Here's the code I used to create the local user:

USE AdventureWorks
GO

CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'<enter REALLY secure password string here>';
GO

CREATE USER IntUser WITHOUT LOGIN;
GO
CREATE CERTIFICATE IntCert
AUTHORIZATION IntUser
WITH SUBJECT = 'Int Certificate',
EXPIRY_DATE = N'12/31/2012';

BACKUP CERTIFICATE IntCert
TO FILE = N'E:\Certs\IntCert.cer';
GO

I did the same thing at the destination site (called DMZSite), and to allow the DMZUser to send messages to my site I'll create a local user from the certificate created at that site.

CREATE USER DMZUser WITHOUT LOGIN;

CREATE CERTIFICATE DMZCert
AUTHORIZATION DMZUser
FROM FILE = N'E:\Certs\DMZCert.cer';
GO

Remote Service Binding. Once the users are established and secure, the last component required is the Remote Service Binding. This binds a remote Service Broker service to our local one, defining the security credentials to be used in the conversations.

CREATE REMOTE SERVICE BINDING [DMZBinding] 
AUTHORIZATION dbo
TO SERVICE N'//DMZSite/Sync/IntService'
WITH USER = [DMZUser]
GO

Finally, we'll grant the SEND permission to the DMZUser to allow the remote service to send messages to our site.

GRANT SEND
ON SERVICE::[//IntSite/Sync/IntService]
TO DMZUser;
GO

As I mentioned before, this set of objects make up the "plumbing" that allow separate instances or servers to communicate with each other. In my next post we'll talk about the automated activation process and walk through the steps of message handling.

Allen

Published Tuesday, January 12, 2010 11:40 AM by AllenMWhite

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ron Cartmale said:

Hi Allen,

Like you I fumbled around trying to get to grips with this technology. There we lots of do this do that but nothing explained like how useful message types can be.

Great article series, shame I found them a week late!

April 20, 2011 4:40 AM
 

Ron Cartmale said:

ps  I also meant to say that I am impressed with the technology and I am wondering why it has taken so long for me to get into it.

We will be using it to send notifications / emails from SQL Express versions to our main server for onward transmission.

I have also used Service Broker to write a Job Scheduler for the SQL Express versions giving all the functionality of the SQL Agent.

April 20, 2011 4:44 AM
 

Daniel Folz said:

Allen, I've been working to setup the Service Broker to work in a SQL cluster environment between two SQL Instances.  Your post clued me in to an issue I was having with how I had configured the Remote Service Bindings. Within ten minutes, the first message made it successfully to the destination queue.  Thanks very much!!  

May 29, 2013 4:33 PM
 

N Rapoza said:

Allen, thanks for this post, and the previous post.  Your explanation of routes helped me troubleshoot a problem in our test environment.

January 29, 2014 12:26 AM

Leave a Comment

(required) 
(required) 
Submit

About AllenMWhite

Allen White is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He has worked as a Database Administrator, Architect and Developer for over 30 years, supporting both the Sybase and Microsoft SQL Server platforms over that period.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement