Setting up Service broker after restoring a database.

With a

Service Broker

application, if you have a Test environment where Production backups are restored, it often becomes necessary to reconfigure the service broker after restoration.I first look at the msdb sys.routes table to get the broker_instance.

SELECT name, remote_service_name, broker_instance
FROM msdb.sys.routes

I compare the value of the broker_instance with the service_broker_guid of the DB restored.
SELECT name, database_id, service_broker_guid
FROM sys.databases
WHERE is_broker_enabled = 1

If it does not match, use the ALTER ROUTE command to change the instance value. In case, you rename the older DB, you will end up with two databases having the same broker_instance. In such a case, you can change the GUID associated with the old database

ALTER DATABASE DB_OLD SET NEW_BROKER WITH ROLLBACK IMMEDIATE

and disable the broker for that DB.

Finally, for the restored DB, change the route to point to the appropriate server.
ALTER ROUTE TargetService
WITH
ADDRESS = 'tcp://address:port'

Date of last broker queue activation

The last time service broker queue was activated can be found using the below query.

SELECT q.name, last_activated_time
FROM sys.dm_broker_queue_monitors qm
JOIN sys.service_queues q
ON q.object_id = qm.queue_id

Note that last_activated_time is in UTC format and will need conversion to the appropriate timezone for analysis.

Finding date when message was sent by Service Broker

The sys.conversation_endpoints table has a lifetime column. You can use that to determine the time when a conversation was started as follows.

SELECT DATEADD(SECOND, -2147483647, lifetime) FROM sys.conversation_endpoints e
WHERE e.conversation_id in ('CAAB52B9-C6B3-4DD8-9E1F-7C0CD93D7724', '7AD93224-3700-4054-9BD2-7B52C9430C17')