SQL Server Service Broker Basics Part Two

Service Broker is not a black box. SQL Server Service Broker Basics Part Two was designed to support your messaging needs and make sense of Service Broker.

Continued from SQL Server Service Broker Basics Part One

SQL Server Service Broker Basics Part Two

Originally published on SQLBlog.com.SQL Server Service Broker Basics Part Two

In the previous post, I introduced SQL Server Service Broker Basics Part One.  In this post, 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.

Reprinted with author’s permission from SQLBlog.com.

>> Back to SQL Server Service Broker Basics Part One <<

 

UpSearch

About the Author

Microsoft SQL Server MVP and Practice Leader

Allen White

Allen White is an UpSearch Alum and Microsoft SQL Server MVP.  For over 30 years, Allen has specialized in developing applications that manage the movement of data and maximize data's usefulness. Allen excels at communicating highly technical information using language that results in increased client engagement and understanding, regardless of technical competency.

Allen has been working with relational database systems for over 20 years. He has architected database solutions in application areas like retail point-of-sale (POS), POS audit, loss prevention, logistics, school district information management, purchasing and asset inventory and runtime analytics. Allen thrives on providing comprehensive solutions to information management problems across a great variety of application environments.

About UpSearch

up-social-round

UpSearch is a leading Microsoft Gold Partner for organizations who rely on Microsoft’s Data Platforms, and its mission is to enable every leader to unlock data’s full potential.

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *