question

DouglasBrown-5495 avatar image
0 Votes"
DouglasBrown-5495 asked Cathyji-msft commented

Service Broker not working on SQL 2019 High Availability Replica

I have Service Broker setup in SQL 2019 AlwayOn Availability group. Both the source and the target databases are in separate AG’s. Messages transfer with no problem while both source and target are on the primary servers. The out routes are setup using the AG listener. When I fail over the target database to the secondary replica messages fail and the error I find in the profiler trace is ‘An exception occurred while enqueueing a message in the target queue. Error: 15581, State: 7. Please create a master key in the database or open the master key in the session before performing this operation’. Is it possible to have both the primary and the secondary encrypted with the service master key of their respective servers?

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered Cathyji-msft commented

You can make the same Service Master Key on both primary and secondary replica in an AG group by backing up the service master key on the primary replica to a shared folder and then restoring it on the secondary replica.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks that worked very well.

0 Votes 0 ·

Hi @DouglasBrown-5495,

Thanks for posting your issue on Q&A. I am glad to hear that your issue have been resolved. If the reply from Guoxiong is helpful, please "Accept answer" or "Up-Vote" for the same which might be beneficial to other community members reading this thread.

0 Votes 0 ·