SQL Server Service Broker – Networking

This post on SQL Server – Networking is part of a series on this blog that will explore SQL Server Service Broker, a native messaging and queueing technology built into the SQL Server Database Engine.

Previous posts:

See all posts in the series here.

In this installment, we discuss networking and routing components for Service Broker. So far, we’ve deployed our Service Broker solution inside a single database. The real magic of using a messaging service is communicating and synchronizing data across distributed systems. In the next two posts we’ll learn about the different components that make that magic happen in Service Broker. We’ll begin with the networking infrastructure.

Endpoints

Service Broker uses TCP/IP to communicate with other Service Broker services on the network. Therefore, the first step in distributing a Service Broker solution is to create a TCP endpoint in the SQL Server instance. It will be used to listen for Service Broker communication over the network. We do that using the CREATE ENDPOINT command. When we create the endpoint, we need to specify a unique port number, in this case 4023. Make sure that port number isn’t being used by any other services on this server. We also specify the authentication type. Service Broker endpoints support Windows authentication (used in the example below) or certificate-based authentication.

For our taxes example code, we’ve decided to move the IRS-related components to separate database on another server. So we’ll need to create an endpoint on each instance:

[sourcecode language="sql"]
--Create an endpoint on our Taxpayer instance
CREATE ENDPOINT TaxpayerEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4023 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
--Create an endpoint on our IRS instance
CREATE ENDPOINT IRSEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
[/sourcecode]

Note that you can only create one Service Broker endpoint per instance. Regardless of how many Service Broker solutions you’re deploying in that instance, all their network communications will go through that one endpoint. Also, if you’re using a firewall on the server, you’ll want to ensure you configure it to allow incoming connections on that port.

Routes

One of the first questions you might ask when distributing Service Broker solutions is “how does SQL Server know where the other service is?” That’s where routes come in. We use routes to tell SQL Server the server name and endpoint port of a remote service on the network.

For example, in our taxes solution, we would create a route in the Taxpayer database that points to the IRS service, and a route in the IRS database that points to the Taxpayer service:

[sourcecode language="sql"]
--create in taxpayer database
CREATE ROUTE IRSRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/IRSService',
ADDRESS = N'TCP://LAB-SB2:4022'; --the IRS endpoint
GO
--create in IRS database
CREATE ROUTE TaxpayerRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/TaxpayerService',
ADDRESS = N'TCP://LAB-SB1:4023'; --the Taxpayer endpoint
GO
[/sourcecode]

Each database contains a system table, sys.routes. When a conversation is started, SQL Server looks through the sys.routes table in the database where the conversation started, for a route with the service name (and Service Broker GUID, if used) specified in the conversation.

If a Service Broker identifier is specified in the conversation, SQL Server will look for a route with an exact service name and Service Broker identifier match. In the case of an exact match not being found, or if no Service Broker identifier is specified in the conversation, it will look for a match based on the service name alone. When SQL Server finds several matches based on the service name that have different Service Broker identifiers, it will arbitrarily pick one of the identifiers and then match the routes that use that identifier.

Note that incoming messages also go through a routing process, except their routing table is in msdb. When we configure Service Broker for network communications, we need to create a local route for messages received from the remote service:

[sourcecode language="sql"]
--create on taxpayer instance
USE msdb
GO
CREATE ROUTE TaxpayerRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/TaxpayerService',
ADDRESS = N'LOCAL'
GO
--create on IRS instance
USE msdb
GO
CREATE ROUTE IRSRoute
WITH SERVICE_NAME =
N'//SBDemo/Taxes/IRSService',
ADDRESS = N'LOCAL'
GO
[/sourcecode]

Conclusion

We’ve got our networking and routing configured for Service Broker communications. We’re almost ready to send messages between our two servers! There’s just one more thing we need to do: configure security. More on that next time.

Original post by Colleen M. Morrow found here.