Service Broker GUID out of Sync

Question

How do we determine if the Database GUID is different that the Sys Database has registered prior to Running Enable? We are trying to avoid the following error:
the Service Broker GUID in the database (62198123-2367-48FB-AC38-CF4ECC63342A) does not match the one in sys.databases (00000000-0000-0000-0000-000000000000).

In some cases it is ok to create a new GUID, but if the clients are already messaging we want to just re-enable the current GUID. If we get that message we get into a state that the customer has to call in for support.

Scott

Answer 1

Hi @Scott Patton ,

We can using below T-SQL to check GUID for SQL databases.

select name, database_id, service_broker_guid, is_broker_enabled from sys.databases  

We can using below t-SQL to enable service broker. If the Database GUID is different with the Sys Database has registered. You will get the error message that you mentioned.

ALTER DATABASE yourDBname SET ENABLE_BROKER  

>the Service Broker GUID in the database (62198123-2367-48FB-AC38-CF4ECC63342A) does not match the one in sys.databases (00000000-0000-0000-0000-000000000000).

When we got the error message, we can run below T-SQL to set a new service broker for this database. Here we force a new GUID upon the database, the Rollback Immediate forces any open connections closed. Obviously you want to be VERY careful doing something like that in production, but you should be OK in development.

ALTER DATABASE yourDBname SET NEW_BROKER WITH ROLLBACK IMMEDIATE  

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.

Answer 2

I don't have the full answer to this. Particularly, I don't know how you find the Service Broker GUID inside the database. And nor for that matter why they get out of sync.

However, you mentioned in a comment which you since seems to have deleted, that you have only been able to repro this with RESTORE, and that was also my initial thought when I see the thread.

I don't know how you handle the databases, but when you restore a Service-Broker enabled database from one server to another, you need to be careful. As you may know, there can only be one Service-Broker-enabled database on an instance with the same Service Broker GUID.

When you restore a database, you can specify ENABLE_BROKER or NEW_BROKER. Here you need to make a decision of what you want. If you are transferring a database from on server to another because of hardware upgrade or whatever, you obviously want to retain existing conversations and ENABLE_BROKER is a good choice. But if you are taking a copy of a database to QA or test environment, you should probably select NEW_BROKER, to kill all existing conversations (unless you are going do some troubleshooting.)

As I said, I don't know this mismatch occurs, but I find it difficult to believe that it would happen without the database moving from one server to another.

By the way, are there any availability groups involved here?