Simon's SQL

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 : 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