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