In this section, I’ll move the manufacturing database to another SQL Server instance to demonstrate how to configure Service Broker networking. Although communicating between two machines is more impressive, if you have only one machine, you can use two instances on the same machine to set up a remote Service Broker connection. To set up a dialog initiator in another instance to test communications, detach the Manufacturing database, move the Manufacturing.mdf and Manufacturing_log.ldf files to the second instance, and attach the database to the second instance.
In the instance you have been using for the previous samples, detach the Manufacturing database with this command:
sp_detach_db Manufacturing
Move the Manufacturing.mdf and Manufacturing_log.ldf files for the Manufacturing database to the machine where the second instance is located. If you are using a second instance on the same machine, you just need to move them to the Data directory for the second instance. Attach the Manufacturing database to the second instance with the following command:
CREATE DATABASE Manufacturing ON (FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataManufacturing. mdf'), ON (FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataManufacturing_ log.ldf') FOR ATTACH WITH ENABLE_BROKER;
Note that the book formatting has wrapped the filename. Type these on a single line. Remember to include the ENABLE_BROKER option so that Service Broker will run after the attach. You must also encrypt the database master key with the new instance’s service master key so that Service Broker can access its encrypted data:
USE Manufacturing; GO OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Pass.word1'; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
There are two layered protocols involved in exchanging Service Broker messages: the dialog protocol and the Adjacent Broker Protocol. The dialog protocol that’s been already discussed maintains the reliable, persistent, stateful sessions between conversation endpoints. Messages are acknowledged and retried at this level, and dialog security is part of this protocol. Also, the dialog protocol is unaware of the transport protocol used to transfer messages on the network.
The "bits on the wire" protocol that Service Broker uses to send data over the network is called the adjacent broker protocol (ABP) because it communicates between SQL Server instances connected by the network. The current ABP implementation works only on TCP/IP connections, but it is designed to be extensible to other network transports in the future. The ABP is a simple protocol designed to efficiently use the available bandwidth to transfer Service Broker messages between SQL Server instances. The ABP is so efficient that several customers have reported better performance by having the dialog initiator and target on separate machines than having them on the same machine. Of course, your results may vary.
Service Broker messages from all the sys.transmission_queues in all the databases on a SQL Server instance are processed by a global instance of the ABP logic. The ABP logic is called a Service Broker endpoint. This endpoint is distinctly different from a conversation endpoint. The endpoint maintains connections to all the SQL Server instances that the local instance has dialogs to. Only one TCP/IP connection is maintained between a given pair of instances.
To ensure fairness, messages waiting to be sent are referenced in a transmission list that maintains a list of all messages waiting to be sent in all the transmission queues in the instance in the order of when they were placed on the queue. Multiple messages to be sent to the same destination are placed into a single TCP/IP message for more efficient transfer. This process is called boxcaring. The format of an ABP message is a header that contains information about where the message is going and where it came from. It also includes the sequence number, fragment numbers, and when the message was sent. This header is used by the dialog protocol and the message body as a binary large object (LOB). The first messages from the initiator contain a security header to transfer the keys and information necessary to establish dialog security. When the first message is received back from the target, the initiator knows that the target has the security information, so subsequent messages on that dialog do not include the security header.
The Adjacent Broker Protocol also includes security features. ABP connections are only allowed between instances that have been authenticated and authorized to connect. Because this authentication is done on a connection-oriented session, Microsoft Windows authentication is supported. Certificate-based authentication is also supported to handle connections where Windows authentication won’t work. Setting up ABP authentication is covered in the next section. All messages sent over an ABP connection are signed so that any message-tampering or corruption is detected. In addition, the ABP connection can be encrypted. In many cases, encryption isn’t necessary because the messages are already encrypted by dialog security, but encryption at the ABP level will ensure that even messages from unsecured dialogs will be encrypted. The encryption software recognizes messages encrypted by the dialog protocol, so encrypted data is not double-encrypted.
In SQL Server 2005, all network connections are handled through endpoints. The types of endpoints available are T-SQL, SOAP, Database Mirroring, and Service Broker. The CREATE/ALTER endpoint statements are used to configure the endpoints. The currently configured endpoints are listed in the sys.endpoints catalogue view. A Service Broker endpoint is used to configure the port that the instance will use to listen for connections and to specify what kind of security will be used on the connection. The CREATE ENDPOINT syntax includes several protocols for connections, but currently only TCP/IP is supported. The command to create a basic Service Broker endpoint looks like this:
CREATE ENDPOINT InventoryServer
STATE = STARTED
AS TCP ( LISTENER_PORT = 4030 )
FOR SERVICE_BROKER (
AUTHENTICATION = WINDOWS,
ENCRYPTION = SUPPORTED);The first few lines are pretty self-explanatory, the STATE = STARTED line means the endpoint will start listening for connections immediately, and the LISTENER_PORT clause means the endpoint will listen for connections on port 4030. The port you use doesn’t matter as long as it is above 1024 and not already in use on your machine. If you have a tightly controlled network, your network administrator will assign port numbers for you to use. All Service Broker ABP connections must be authenticated. The WINDOWS option means a Windows authentication protocol–either NTLM or KERBEROS–will be used to authenticate the TCP/IP connections.
There are other options that let you specify which protocol to use. In situations where Windows authentication isn’t possible, certificates can be used to authenticate the connection. The certificate authentication protocol used is the same SSPI protocol that Secure Sockets Layer (SSL) Internet connections use. I’ll cover how to configure both kinds of authentication and how to control which servers you allow to connect to yours later in this section. The ENCRYPTION clause specifies whether encryption should be used on the ABP connection. The three options for this parameter are DISABLED, REQUIRED, and SUPPORTED. Table 11-1 explains how the settings for the two endpoints determine whether the connection will be encrypted.
Table 11-1. ENCRYPTION Settings for Two Endpoints
Endpoint 1 | Endpoint 2 | Encrypted |
|---|---|---|
DISABLED | SUPPORTED | NO |
DISABLED | DISABLED | NO |
DISABLED | REQUIRED | ERROR |
SUPPORTED | SUPPORTED | YES |
SUPPORTED | REQUIRED | YES |
REQUIRED | REQUIRED | YES |
REQUIRED | DISABLED | ERROR |
This clause also allows you to specify RC4 or AES as the encryption algorithm to use. AES is more secure than RC4, but RC4 is significantly faster.
The CREATE/ALTER ENDPOINT commands are used to configure the endpoint parameters for ABP. You must also configure permissions to determine which other instances are allowed to connect to the local instance. This is done by creating a SQL Server login in the local instance’s master database to represent the remote instances and then granting that login CONNECT permission to the endpoint. When a remote instance connects to the Service Broker endpoint, the connection is authenticated using either a Windows protocol or the SSPI protocol, and the authenticated login is checked for CONNECT permission to the endpoint. If the authentication fails or the authenticated login fails, the connection is closed.
Windows authentication is generally easier to configure, so I’ll start there. The Windows user that is authenticated is the Service Account for the instance. By default, licensed editions of SQL Server use the local system account as the service account and SQL Express uses Network Service as the service account. For this example, you’ll configure a Windows server named MYPC10 for the Inventory instance and a SQL Express instance on the same server as the Manufacturing instance. Log on to the master database of the instance where the Inventory database is, and execute this script:
-- Create an endpoint for the inventory server -- with Windows authentication CREATE ENDPOINT InventoryEndpoint STATE = STARTED AS TCP ( LISTENER_PORT = 5523 ) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ); -- Grant Network Service connect privileges GRANT CONNECT ON ENDPOINT::InventoryEndpoint TO [NT AUTHORITYNetwork Service];
That’s all there is to configuring the Inventory instance. Next log on to the instance where the Manufacturing database is, and execute this script:
-- Create an endpoint for the manufacturing server -- with Windows authentication CREATE ENDPOINT ManufacturingEndpoint STATE = STARTED AS TCP ( LISTENER_PORT = 5524 ) FOR SERVICE_BROKER ( AUTHENTICATION = WINDOWS ); --Create a login for remote system in this instance -- Change to your domain and server name! CREATE LOGIN [MYDOMAINMYPC10$] FROM Windows; -- Grant Local System connect privilege GRANT CONNECT ON ENDPOINT::ManufacturingEndpoint TO [MYDOMAINMYPC10$];
Notice that the local system account is seen as the machine account MYPC10$. Machine accounts work only in Kerberos environments, so if you are working on an NTLM network, you will have to either use domain account as the service accounts or use certificate authentication.
There are two shortcuts you can use if you want to do some quick development or testing without going through a lot of setup. If you use the same domain user account as the user account for both SQL Server instances and configure the endpoints for Windows authentication, the connection will work because the service account for each instance is an admin in the other instance and no additional authorization is necessary. If you are in a trusted network and don’t really care who connects to the instance, you can grant CONNECT permissions to PUBLIC and any Windows user that can log on to the Windows server can connect to the Service Broker endpoint:
GRANT CONNECT ON ENDPOINT::InventoryServer TO Public;
Certificate-based authentication for Adjacent Broker Protocol endpoints uses the same SSPI authentication protocol as SSL. SSL is the encryption used if you browse an HTTPS: site on the Internet. Although Service Broker uses the same authentication protocol as SSL, it does not use SSL because SSL encrypts everything on the wire. Because secure Service Broker dialogs are already encrypted, re-encrypting this data would be a significant waste of resources. Like mutual SSL authentication (and dialog security), certificate authentication requires each of the two endpoints to have a private key certificate. Because SQL Server might not be connected to the Internet or the private key certificate might not be issued by a certificate authority, the public key certificates required are also stored in the database. The way authentication works is similar to the dialog security process that’s already been discussed, but there are some differences in the way it is configured. The following example will walk you through connecting a SQL Server instance that contains our Inventory service with another instance that contains the Manufacturing service. The setup will be the same whether the two instances are running on the same box or on different boxes. You’ll start by creating the endpoint and its private key certificate in the Inventory instance. The scripts in Example 11-6, Example 11-7, and Example 11-8 should all be run from the master database.
Example 11-6. Set up inventory private key
/*-------------------------------------------------------------------- -- Setup Certificate authenticated Endpoint -- on the Inventory server --------------------------------------------------------------------*/ -- Create a certificate to represent the inventory -- server and export it to a file CREATE CERTIFICATE InventoryCertPriv WITH SUBJECT = 'ForInventoryAuth'; BACKUP CERTIFICATE InventoryCertPriv TO FILE = 'C:InventoryCertPub'; GO -- Create a Service Broker Endpoint that uses this -- certificate for authentication CREATE ENDPOINT InventoryEndpoint STATE = STARTED AS TCP ( LISTENER_PORT = 4423 ) FOR SERVICE_BROKER ( AUTHENTICATION = CERTIFICATE InventoryCertPriv );
Notice that the endpoint configuration specifies which certificate to use for the private key of the inventory endpoint. In this example, both instances were on the same server, so exporting the public key certificate to the C: drive makes sense. In a distributed environment, you would have to come up with another way to move the certificate to the opposite endpoint. A network-mapped directory, ftp, and e-mail are all viable alternatives.
Now that you have the private key of the inventory instance configured, you’ll need to move to the manufacturing instance to create its private key and import the inventory public key by running the code in Example 11-7.
Example 11-7. Set up manufacturing endpoint
/*--------------------------------------------------------------------
-- Setup Certificate authenticated Endpoint
-- on the Manufacturing server
--------------------------------------------------------------------*/
-- Create a certificate to represent the
-- manufacturing server and export it to a file
CREATE CERTIFICATE ManufacturingCertPriv
WITH SUBJECT = 'ForManufacturingAuth';
BACKUP CERTIFICATE ManufacturingCertPriv
TO FILE = 'C:ManufacturingCertPub';
GO
-- Create a Service Broker Endpoint that uses this
-- certificate for authentication
CREATE ENDPOINT ManufacturingEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4424 )
FOR SERVICE_BROKER (AUTHENTICATION =
CERTIFICATE ManufacturingCertPriv);
-- Create a user and login to represent the
-- inventory server on the manufacturing server
CREATE LOGIN InventoryProxy
WITH PASSWORD = 'dj47dkri837&?>';
CREATE USER InventoryProxy;
-- Import the certificate exported by the inventory server
CREATE CERTIFICATE InventoryCertPub
AUTHORIZATION InventoryProxy
FROM FILE = 'C:InventoryCertPub';
-- Grant connect privileges to the login that
-- represents the inventory server
GRANT CONNECT ON ENDPOINT::ManufacturingEndpoint
TO InventoryProxy;Notice that you had to create both a user and login as proxies for the inventory server. The login is necessary because it must be granted the permission to connect to the endpoint to allow the Inventory instance to connect, and the user is necessary because logins can’t own certificates. It should never be necessary for anyone to log into the instance with the proxy login, so the password should be long and random for maximum security.
All that’s left to do is switch back to the inventory instance to import the manufacturing public key and create the required login and user by running the code in Example 11-8.
Example 11-8. Finish the inventory endpoint
/*-------------------------------------------------------------------- -- Finish the certificate-authenticated endpoint -- on the Inventory server --------------------------------------------------------------------*/ -- Create a user and login to represent the -- manufacturing server on the inventory server CREATE LOGIN ManufacturingProxy WITH PASSWORD = 'dj47dkri837&?>'; CREATE USER ManufacturingProxy; -- Import the certificate exported by the Manufacturing server CREATE CERTIFICATE InventoryCertPub AUTHORIZATION ManufacturingProxy FROM FILE = 'C:ManufacturingCertPub'; -- Grant connect privileges to the login that -- represents the Manufacturing server GRANT CONNECT ON ENDPOINT::InventoryEndpoint TO ManufacturingProxy;
As you can see, setting up certificate authentication between instances is a little more complex than setting up Windows authentication. The advantage is that certificate authentication will work on any Windows security configuration, while Windows authentication might require Kerberos for some service accounts and might not work across domains.
Now that you have learned how to configure security for the Adjacent Broker Protocol, you will learn how Service Broker dialogs determine where messages should be delivered.
A Service Broker route is just a mapping from a service to the network address where messages to that service should be sent. Service Broker routes are simple but flexible enough to support large complex networks. Here is a basic Service Broker route:
CREATE ROUTE Inventory_route WITH SERVICE_NAME = '//microsoft.com/InventoryService', ADDRESS = 'TCP://mypc11:4424';
The routes configured in a database can be examined in the sys.routes view The name of the route is there so that you can ALTER or DROP it. The SERVICE_NAME is the name of the service that this route provides an address for. Remember that service names use binary collation, so the name in the route must match exactly the name of the service. (Cut-and-paste is your friend here.) A common source of routing problems is having the case of the service name wrong. The ADDRESS is the network address that messages for this service should be sent to. Service Broker supports only the TCP/IP protocol, so the address should begin with "TCP://". Next comes the network address, which can be a DNS name, a host name, or an IP address. The ":4040" in the example indicates that Service Broker should connect to port 4040 on the remote server. This port number must match the port number configured for the Service Broker endpoint in the remote instance. If two or more instances are installed on the same remote server, you target which one gets the message with the port number. Because the port number is determined when you create the remote endpoint, there is no default port number.
It’s important to remember that both the initiator and target services of a dialog need endpoints. One of the more common network configuration errors is forgetting to configure the route from the target service back to the initiator. When this happens, messages are delivered successfully from the initiator to the target but no response is returned. This leads to the strange behavior that messages are received on the target queue and processed successfully, but the messages are still on the sys.transmission_queue of the initiator database because the acknowledgement for the messages is not delivered to the initiator. When you see the messages in the sys.transmission_queue of the initiator database, you think they are not being sent. In reality, they have been delivered and processed. If you use profiler to monitor Service Broker message delivery, you will see the messages being delivered to the target periodically and being rejected as duplicates. If the application sends messages only in one direction, it will appear to be working but will gradually get slower because thousands of messages a minute are being resent by the initiator and ignored by the target. With this in mind, be sure you also log in to the Inventory database and create a route back to the Manufacturing instance:
CREATE ROUTE Manufacturing_route WITH SERVICE_NAME = '//microsoft.com/ManufacturingService', ADDRESS = 'TCP://mypc11:4423';
If you have created a route from the Manufacturing database for the InventoryService and in the Inventory database for the ManufacturingService, the distributed Service Broker configuration should be complete. Log in to the Manufacturing database, and run AddItemProc to test your dialog. Select from the sys.dm_broker_connections view to see whether a network connection was established. If it was not, check sys.transmission_queue for an error status and monitor the broker connection traces in SQL Server Profiler.
In some cases, you might need to route a dialog to a particular database in a SQL Server instance. For example, the instance might have both a production and test version of the service in two different databases. The route to the service must be able to route the dialog to the correct database in the instance. This is done by specifying the BROKER_INSTANCE parameter in the CREATE ROUTE command. The BROKER_INSTANCE is the service_broker_guid column of the sys.databases table for the required database. The following example shows a route with the BROKER_INSTANCE parameter specified:
CREATE ROUTE ManufacturingRoute WITH SERVICE_NAME = '//microsoft.com/ManufacturingService', BROKER_INSTANCE = 'A29EEDD3-27E3-4591-94D9-B97BAFBDB936', ADDRESS = 'TCP://mypc11:4423';
Service Broker uses routes in the database where the services are located to determine which remote instance messages to a service should be sent to. When the message arrives at the remote instance, Service Broker must determine what to do with the incoming message. Service Broker uses the routes stored in the msdb database to decide what to do with incoming messages. For example, if there are three or four copies of the InventoryService in an instance, a route with the BROKER_INSTANCE specified can be used to route incoming messages for the InventoryService to the correct database. This can also lead to problems if the msdb database is attached or restored without the BROKER_ENABLED flag so that Service Broker is disabled in msdb.
Establishing a Service Broker conversation will fail if there is no route available for the TO SERVICE. This might seem unlikely to you because if you have been following along with the examples, you have created several conversations without creating a route for the services involved. The reason this works is that there is a route called AutoCreatedLocal that is created when a database is created. If you look at this route in the sys.routes view, you will see that the remote_service_name and broker_instance columns are both NULL and the address column is LOCAL. The NULL columns are what Service Broker uses as wildcards. The wildcards mean that this route will match any service name and any broker instance. A wildcard is used as the last matching criteria so that if another route matches the service name or broker instance the more specific match will always be used. You can create your own wildcard routes by not specifying one or both of the matching parameters.
The LOCAL value for the address is one of two special addresses used in Service Broker. LOCAL means that Service Broker will look for a service in the local SQL Server instance that matches the name. If the database where the route is located has a copy of the service, that copy will be used. If that database doesn’t have a copy of the service, Service Broker will look in other databases in the SQL Server instance for a matching service. If more than one copy of the service is found, a random one will be chosen. Because the AutoCreatedLocal route is a wildcard route with an address of LOCAL, Service Broker will check for a local service if there is no matching remote_service_name in the sys.routes view.
The other special address is TRANSPORT. If no other match is found and there is a matching route with the TRANSPORT address, Service Broker will attempt to use the name of the service as a route. For example, if the service name is "TCP:\mypc11:4040Manufacturing", Service Broker will send the message to TCP:\mypc11:4040. Although this might seem like a strange feature, think of an application that involves several thousand initiators connecting to a target service. Maintaining return routes for all the initiators on the target instance would be very painful. On the other hand, if each initiator names the FROM SERVICE with its network address and port, the target only needs to have a single wildcard route with the TRANSPORT address to route responses to all the initiators.
If there are multiple routes for the same service name in a database, Service Broker will randomly pick one of them when sending the first message in a conversation. This provides a simple form of load balancing among a number of identical services. Notice that this balancing happens on a per-conversation, not per-message, basis. This makes sense if you realize that the first message of the conversation will insert a row in sys.conversation_endpoints at the remote database. So, if the second message goes to a different database, Service Broker will not be able to process it because there is no conversation information in sys.conversation_endpoints for the conversation the message belongs to. This is one of the few situations where having a large number of conversations is a good idea.
The issue with this type of load balancing is that it assumes all services with the same name are identical, so it doesn’t matter which one you use. Depending on what the service does and how often the data used by that service changes, keeping multiple target services synchronized might be difficult. On the other hand, if the data for the service changes only rarely, replication can be used to keep the services synchronized or services can call each other with changes through a synchronization service. In other circumstances, it might make more sense for the data in the copies of the service to be partitioned. For example, one service might handle customer numbers 1 through 10000, another 10001 through 20000, and so on. In this case, the application will have to decide which copy of the service to begin the dialog with and use the broker instance in the BEGIN DIALOG command to select the appropriate one. This kind of load balancing works well if the data can be readily partitioned between services and all requests to the service know which customer number the request is for.
One possible problem to look out for is "accidental" load balancing. Whenever Service Broker finds two or more routes to a service, it will load balance dialogs among them. If you intend to have two routes, that is a good thing. However, consider a situation in which you added a route to the production version of a service and forgot to drop the route to the test version. Service Broker will send half the dialogs to test and half to production, which probably isn’t what you intended. A more subtle way this happens is if you are using a LOCAL route for the service and the service exists on multiple databases in the local instance. In that case, these services will be load balanced. This generally means that about half of the dialogs will end up where you intended.
To ensure that accidental load balancing doesn’t happen, you should create a route for the service and specify the broker instance instead of letting it use the default route. This way you can be sure that the dialog is going where you expect it to go. This same principle applies to messages coming into the instance from outside. If there is only the default route available in msdb, the incoming dialogs will be load balanced if there are multiple copies of the service in the instance. The fix here is the same–create a route for the service in msdb, and use the broker instance to ensure the dialog goes where you want it to.
If the SQL Server instance you are routing messages to is actually two instances that are the primary and secondary databases of a database mirroring pair, Service Broker routing can handle this very well. The CREATE ROUTE command will accept the address of the instances that contain both the primary and secondary databases:
CREATE ROUTE ManufacturingMirrorRoute WITH SERVICE_NAME = '//microsoft.com/ManufacturingService', BROKER_INSTANCE = 'A29EEDD3-27E3-4591-94D9-B97BAFBDB936', ADDRESS = 'TCP://mypc11:4040', MIRROR_ADDRESS = 'TCP://mypc15:5834';
When Service Broker opens a connection for this route, it will connect to both instances of the mirrored pair and determine which database is the primary one. If the primary fails over and the other database becomes the primary, the Service Broker running on the new primary will notify any remote Service Brokers with conversations open that it is now the primary. The remote Service Broker will then immediately start sending the messages to the new primary database. This means that not only no messages are lost when the database fails over (because all the queues are in the database), but also the remote applications will start sending to the new primary immediately. This arrangement provides an unprecedented degree of reliability for asynchronous distributed applications. In most cases, a failure will cause only a few seconds of hesitation and nothing will be lost.
When an incoming message arrives at the SQL Server instance, Service Broker looks in sys.routes in the msdb database for a route that matches the service name of the incoming message. If the address in the route is LOCAL, the message is put in the appropriate queue in a local database. If the address is a remote address, the message is put in a forwarding queue and sent to the remote instance specified in the address. Forwarding routes look exactly like other routes except they are created in the msdb database. The ADDRESS parameter in the CREATE ROUTE command was originally known as the "next hop address" because if forwarding is used, the address points to the next Service Broker in the forwarding chain, not the final destination.
Forwarding can be used to create complex network topologies, with messages forwarded from server to server until they reach their final destination. It is not necessary to persist messages to be forwarded because if they are lost, they will be re-sent from the message source if no acknowledgement is received. For this reason, the forwarding queues are memory queues. You can also configure the maximum size of the forwarding queues so that forwarded messages don’t use too much memory. This command will turn on message forwarding and limit the forwarding queue to 50 MB of memory:
ALTER ENDPOINT InventoryEndpoint FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED, MESSAGE_FORWARD_SIZE = 50);