Issue with service broker not working after Always on Failover SQL Server 2017

Question

Issue with service broker not working after Always on Failover SQL Server 2017

mo boy 396

Dear Experts, We have a CRM database that is configured as part of Always on AG and these databases were migrated recently from another set of servers. Service broker seems to work on only one of the nodes and it is not working after a failover. Even before, we used to have this problem but after failover, we used to regenerate the certificate and stop and restart the service broker queues after failover. However, this time, nothing seems to work and only option is to failback to the original node. Could you please share your suggestions on how this can be fixed? Just letting you know, this is also multi subnet failover. However, the registerallprovidersIP is disabled and hostrecordTTL set to 300.

1 answer

Your answer

Answer 1

Javier Villegas 900 MVP

Hi @mo boy I have a similar scenario with SQL 2019 Shared Storage Failover Cluster and AG at top of it. After failover or server restart some Service Broker Queues get hanged so we have a case open with Microsoft. In the meantime and as a workaround I put in place a SQL Job that runs right after the service starts which is restarting the queues. It is not ideal but we can survive until having the fix Regards Javier

mo boy 396 Reputation points

2024-02-08T21:12:45.2+00:00

@javier villegas The weird thing is even after restarting the queues, the service broker doesn't seem to work. We only have to fail back to the original node where it seems to work. We are just not able to figure out why this happens.

Before migration, the service brokers were working after restarting the queues after failover.
Erland Sommarskog 119.9K Reputation points MVP

2024-02-08T22:23:46.89+00:00

Could you clarify with what you mean with "doesn't seem to work"? That is, what is happening? Do you get error messages? Something else?
mo boy 396 Reputation points

2024-02-08T23:24:16.8533333+00:00

The queue is not processing when we failover. It keeps building up and we have to fail back. The sys.transmission_queue keeps growing.
Erland Sommarskog 119.9K Reputation points MVP

2024-02-09T20:24:11.3566667+00:00

So how have you set up Service Broker? Are both ends of the dialogs in the same database? Or is it a more complex topology?

Have you verified that service_broker_guid in sys.databases is the same on both nodes?

Share via

Issue with service broker not working after Always on Failover SQL Server 2017

1 answer

Your answer