Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Service Broker may not work after Failover in AG.

Posted by Simon Cho on 09/22/2016

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

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: