Service Broker needs to access the master key in the database - Error-28054

Question : I found this logged in the SQL Server Error Logs, during some Service Broker troubleshooting. Service Broker needs to access the master key in the database 'MyDatabase'. Error code:32. The master key has to exist and the service master key encryption is required. Error: 28054, Severity: 11, State: 1. What does it signify? Service Broker is working ok on the server, but this error appears on some days. Answer: As a first step , run this sql code , which will signify if there is a master key for the database Select name, is_master_key_encrypted_by_server from sys.databases Where name... Read more →

Service Broker - When to use for synchronizing databases

Service Broker is used by SQL developers to manage applications running across multiple SQL Server instances . It is part of the SQL Server native feature set and is a messaging and queue service. Asynchronous processing in Service Broker is very effective in supporting decoupled processes for long running tasks. It's very versatile but be warned of attempting to over engineer solutions that could be better served by other features. As a very general feature summary for Service Broker think of - Guaranteed message delivery , Transactions,Backup,API and asynchronous communication. I receive questions regularly about whether Service Broker should be... Read more →

Msg 9772, Level 16, State 1 - The Service Broker in database "xxxx" cannot be enabled because there is already an enabled Service Broker with the same ID.

Question: I'm attempting to execute the following ALTER DATABASE statement to enable BROKER on a database we've just restored. ALTER DATABASE [xxxxx] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE and getting this SQL error message Msg 9772, Level 16, State 1, Line 5 The Service Broker in database "xxxxx" cannot be enabled because there is already an enabled Service Broker with the same ID. How can I fix this problem and progress with enabling Service Broker? I've restored other databases on the same sql server without a problem . The database is restored successfully and connections are all OK Answer:This error occurs... Read more →

Troubleshooting SQL Server Service Broker

If you’re not experienced in all the nuances of Service Broker, it can be tricky to diagnose or identify if there is an issue with Service Broker. There are plenty of products using Service Broker. SCCM is an example . Here are some queries I use when needing to troubleshoot SQL Server Service Broker. --Check Service Broker activated stored procedures SELECT t.*, s.last_wait_type,* FROM sys.dm_broker_activated_tasks t INNER JOIN sys.dm_exec_requests s ON s.session_id = t.spid --check to see if the database is broker enabled select name , is_broker_enabled from sys.databases --check if queues are enabled select name,is_receive_enabled,is_enqueue_enabled from sys.service_queues --check tcp... Read more →

Change the port for sql server service broker

SQL Server Service Broker by default uses port TCP 4022. For security policy reasons I prefer to use a different port number. Before deciding on a port number check it won’t conflict with another application port usage. Use netstat to view all ports currently open . Read How to Configure static ports for SQL Server for more details Once you’ve decided on a port number – use these scripts to create the service broker endpoint and assign the listener port --create ENDPOINT USE master; GO CREATE ENDPOINT SqlBrokerEndpoint STATE = STARTED AS TCP ( LISTENER_PORT = 45000) FOR SERVICE_BROKER (... Read more →