Service Broker not working on SQL 2019 High Availability Replica

Douglas Brown 1 Reputation point
2021-03-30T13:46:30.863+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,755 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Guoxiong 8,126 Reputation points
    2021-03-30T14:58:16.187+00:00

    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.