Share via

Architecting Service Broker applications (part 3)

  • Article

Architecting Service Broker applications (part 3)

This is the final installment (I promise) of my Architecting SQL Service Broker (SSB) applications series. If you haven’t read the first two parts: https://blogs.msdn.com/rogerwolterblog/archive/2006/09/08/Roger.aspx

https://blogs.msdn.com/rogerwolterblog/archive/2006/09/15/Roger_Wolter.aspx

, I would recommend reading them before tackling this one. In this post I will cover some of the infrastructure aspects of a Service Broker solution.

One of the design points of Service Broker is that the application should know as little as possible about how it is to be deployed. For example, the level of security can be determined and changed by a DBA without making changes to the application code. For this reason, it’s possible to talk about SSB infrastructure issues in isolation from application design issues for the most part.

Because this is an architecture discussion, I won’t cover the commands for setting up the infrastructure but I will point out the infrastructure issues you should consider when deploying a Service Broker application. Please refer to SQL Server books online or one of the Service Broker books for the detailed information to do the configuration and deployment.

Security Issues

Any application that sends messages on a network connection must have a security infrastructure. Service Broker has multiple layers of security with options at each layer so you can tune the SSB security to what you need for your network and your data.

One security option is Dialog Security. When a secure dialog is established, Asymmetric (public and private) keys are used to authenticate the dialog connection, SQL Server permissions are verified, a session key is established for the dialog, and all messages are signed and encrypted. This ensures that the messages will be delivered unaltered and unread. Dialog security is established between the two endpoints of a dialog so if messages are forwarded through intermediate brokers, they won’t be decrypted. This is both more secure and more efficient than SSL encryption on the wire. Dialog security should be used if message must be sent over unsecured networks.

If you feel the message traffic for a particular dialog is so sensitive that it should be encrypted over any network, you should let the ENCRYPTION parameter in the BEGIN DIALOG command default to ON. If the data can be sent unencrypted over some networks, you should set the ENCRYPTION parameter to OFF. Setting this to OFF doesn’t mean that the data won’t be encrypted. It means that if the DBA sets up dialog security it will be encrypted but otherwise it will not be. If the ENCRYPTION parameter is set to ON (the default) then no messages will be sent outside the local instance unless dialog security is configured.

Service Broker also has security at the TCP/IP connection level. All Service Broker connections require Authentication, Authorization, and digital signatures to ensure that the connection is authorized and that messages can not be changed on the network. The TCP/IP connection can optionally be encrypted. If the messages are already encrypted by dialog security, they will not be double encrypted so dialog security and transport security are complimentary.

The application deployment plan must define which dialogs and connections should be encrypted. If dialog security is used, a plan for handling certificate expiration must be included.

Availability Issues

Service Broker is often used in highly available systems so availability must be taken into account when designing the SSB infrastructure. Because Service Broker is part of the database engine, you make Service Broker highly available by making the database highly available. Much has been written about SQL Server availability so I won’t try to cover it here. The one unique availability feature that Serviced Broker offers is that it is tightly integrated with database mirroring. If a Service Broker opens a connection to a database that is mirrored, it will also open a connection to the secondary database on the mirror so that when the primary fails over to the secondary; Service Broker will detect the change and automatically start sending messages to the new primary. Because SSB messages are transactional and stored in the database, any in-process messages and any unprocessed messages will still be on the queue after the failover so everything continues with not loss of data or uncompleted work.

Routing Issues

Service Broker dialogs are opened between Services – a FROM service and a TO service specified in the BEGIN DIALOG command. A Service Broker service is basically a name for a dialog endpoint. The dialog endpoint is a SSB queue in a SQL Server database. This indirection from the service to the queue means you can write your application to communicate between logic services and make the decision on where the service are actually located at deployment time. In fact, services can be moved to another location while dialogs are active without losing any messages.

The mapping between the logical service name and the transport address where the message are sent is done with a Service Broker route. A route is just a row in the sys.routes table in a database. When Service Broker needs to send a message, it looks for a route for the destination service and passes the address down to the transport layer which sends it to the database where the destination service lives.

Both the initiator and target of a dialog need a route to the opposite endpoint. One of the more common SSB scenarios involves many initiators send ing messages to a single target. For example point of sale terminals sending transactions to the home office or stock trader workstations sending trades to a back office system. Maintaining hundreds of routes on the target server to all the initiator can be messy. To avoid this issue, SSB provides a TRNASPORT route. The initiator service name contains the initiator’s network address and this name is used as the FROM service in the BEGIN DIALOG command. When the target wants to send a message back to the initiator, it uses the service name as the network address. In this way the initiator provides its own return address so you don’t have to maintain return addresses at the target system.

Service Broker also supports forwarding. Messages coming into a SQL Server instance are routed by routes in the MSDB database. If the route for a service specifies “local” as the network address, the message is routed to a service in the instance. If the network address in the MSDB route for a service specifies a TCP/IP address, the message is forwarded to the specified address. Forwarded messages are not written to the database. They are held in memory until they can be forwarded so SSB forwarding is very efficient. One common use of SSB forwarding is to set up a concentrator machine that accepts message from a large number of connections and the forwards them over a single connection to the target. This moves most of the TCP/IP connection handling to the forwarder which reduces overhead on the target machine. If you use SQL Express in the forwarder machine this can be a very economical way to reduce overhead on the target.

Comments