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
AND is_broker_enabled = 1
ORDER BY NAME
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:
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.
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
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
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 18.104.22.168.:4022'
EXEC xp_cmdshell @sql
After those changes, all started to work fine.