Enabling Service Broker

As I’m in the process of building a deployment package for SQL Server Service Broker I expect that most of my blogs will reference service broker in some form or the other, over the next few weeks. The following code comes out of deploying an env capable of hosting Service Broker.

This first statement ensures a master key in the Database is setup. Without it you’ll receive an error inside the transmission queue similar to:

The session keys for this conversation could not be created or accessed. The database master key is required for this operation.

--Check for a master key Create if needed
if exists(
select * from sys.databases where name = db_name()
and is_master_key_encrypted_by_server = 0)
Begin
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123'
End


The next statement is used for enabling service broker in the database. If Service Broker is disabled you'll see a message like this in the transmission_queue

The broker is disabled in the sender's database.

if exists(select * from sys.databases where name = db_name()
and is_broker_enabled = 0)
Begin
alter database senddb
set Enable_Broker
end

To disable
alter database senddb
set Disable_Broker

Creating Service Broker Endpoints in SQL Server

You can only have one service broker endpoint per SQL Server instance. When creating a service broker application you need to know what TCP port you endpoint is listening on, so your deployment scripts will be referencing correctly. For this reason I recommend using a common port across your enterprise. For example 4000. Since you can only have one endpoint you need a deployement script that will check both the existence of an endpoint and the tcp port conforms to your enterprise. The script below does just that.



--Check existence of a different port
if exists(select top 1 * from sys.tcp_endpoints where type = 3 and Port != 4444)
Begin
--Drop Endpoint
Declare @Endpointname varchar(100)
set @Endpointname = (select name from sys.tcp_endpoints where type = 3)
execute ('Drop Endpoint ' + @Endpointname)
end
go
--If no endpoint exists or was just dropped create
if not exists (select top 1 * from sys.tcp_endpoints where type = 3)
Begin
CREATE ENDPOINT [ServiceBrokerEndpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 4444, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
End




If you try to create an endpoint that exist you’ll see an error that’s similar to the one below.






Msg 7862, Level 16, State 1, Line 1
An endpoint of the requested type already exists. Only one endpoint of this type is supported. Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint 'ServiceBrokerEndpoint'.