I fixed it after reviewing and resetting all broker related database IDs.
The first thing I did was see what we have (both on the source and destination 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 to change:
--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
Sorry for the trustworthy, this is how it was configured and I never changed it.
on the destination server:
on the origin server:
So I have changed the Service Broker identifiers inside the routers.
--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 previous routers, I still did not receive any communication.
I had to check the routers, I noticed that I was using IP addresses instead of the names of the servers, and that worked fine, but we made some changes to our domains and that no longer worked, so using the test below, I first identified that was no longer working (connectivity), then changed it accordingly using server names.
declare @sql varchar(1008) set @sql = 'powershell.exe -command Test-NetConnection 184.108.40.206.:4022' EXEC xp_cmdshell @sql
After those changes, everything started working fine.