High Availability

Service Broker may not work after Failover in AG.

We need to check a couple of setting.

  1. Service broker Enable : By default disabled even restored from Primary
    1. Doesn’t allow enabled broker in AG setting. It’s required remove first.
    2. First failover only.
  2. Trustworthy On : By default off even restored from Primary.
    1. First failover only.
  3. Encryption/Decryption issue
    1. Database master key encrypted by “Service Master key”.
    2. So secondary instance can’t encrypt/decrypt master key since Service master key created at the first installation.
    3. Temporary solution : alter master key with current instance service master key
      1. Once failover, it need to execute.

#Service Master key

Script

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- #1 : First failover only. Remove AG required. After run the script we can rejoin it.
SELECT name FROM sys.databases WHERE is_broker_enabled=1
-- If you don't see your DB. need to run below command.
ALTER DATABASE <ServiceBrokerDB> SET ENABLE_BROKER
GO
-- #2 : Concorrunt connection may be interuppted. First failover only.
SELECT name FROM sys.databases WHERE is_trustworthy_on=1
-- If you don't see your DB. need to run below command.
ALTER DATABASE <ServiceBrokerDB> SET TRUSTWORTHY ON
GO
-- #3 : Need to execute it every failover. More detail please see "Service Master Key" document.
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'x' -- Should be encrypted! See below
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY