The service broker is administratively disabled

Question

I have just restored some databases what use service broker from LIVE to staging.

it is not working and when I put a profile trace to run it only shows messages related to database id 1 and 4, something is wrong but I am struggling to identity what is causing this behaviour.

Answer 1

I got it fixed after reviewing and resetting all broker related database IDs.

First thing I did was to see what we have (on both source and target servers):

--=====================================================================
-- checking what we have and where we point to
--=====================================================================
SELECT @@SERVERNAME
-- my_target_server

USE [master]
GO
SELECT   [name]
 ,[is_broker_enabled] 
 ,[service_broker_guid]
FROM [sys].[databases] 
WHERE 1=1
  AND is_broker_enabled = 1
ORDER BY  NAME
GO

SELECT name,is_broker_enabled,service_broker_guid, is_trustworthy_on FROM sys.databases
order by 2 desc, 3 desc,  1

I saw that the broker id needed changed:

--CD718202-CB34-4DCD-BADC-7800C5F2FF3D
  ALTER DATABASE ORCASTG SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE  
  ALTER DATABASE ORCASTG SET NEW_BROKER with rollback immediate
  ALTER DATABASE ORCASTG SET trustworthy on with rollback immediate

I am sorry for the trustworthy, this is how it has been set up and I never changed it.

on the target server:

on the source server:

so i have changed inside the routers the service broker ids.

--targer server 
USE [cola]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_ORCARoute')
DROP ROUTE [rou_ORCARoute]

CREATE ROUTE [rou_ORCARoute]   
WITH  SERVICE_NAME  = N'svc_SendStatusChangeService' ,  
BROKER_INSTANCE  = N'CD718202-CB34-4DCD-BADC-7800C5F2FF3D' ,  --BROKER ID OF ORCASTG
ADDRESS  = N'TCP://mysourceserver.mycompany.com:4022' --IP address of my source server
GO

--source server     
USE [ORCASTG]
GO
IF  EXISTS (SELECT * FROM sys.routes WHERE name = N'rou_CAUKRoute')
DROP ROUTE [rou_CAUKRoute]

CREATE ROUTE [rou_CAUKRoute]   
       WITH  SERVICE_NAME  = N'svc_receiveStatusChangeService' ,  
       BROKER_INSTANCE  = N'452711F3-60CD-47E6-BFA4-3BD8C9D6AB56' ,   --BROKER_ID OF COLA  
       ADDRESS  = N'TCP://mytargetserver.mycompany.com:4022' --IP address of my target server
GO

even after putting the correct boker_ids for the databases in the the routers above, still I was not getting any communication going through.

I had to review the the routers, I noticed I was using ip addresses in place of the server names, and that was working fine, but we did some changes on our domains and that was no longer working, so using the test below, I first identified it was not longer working (the connectivity), then changed it accordingly using the server names.

 declare @sql varchar(1008)
 set @sql = 'powershell.exe -command Test-NetConnection 200.1.0.198.:4022'
 EXEC xp_cmdshell @sql

After those changes, all started to work fine.