Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for September, 2016

SQL Saturday Session #563 – Dallas 9/24/2016

Posted by Simon Cho on 09/24/2016

Topic : Build ETL efficiently (10x) with Minimal Logging

Presentation File Download Link

 

2016-08-27_SQLSat_Oklahomajpg 2016-09-24_22-05-59

 

Posted in Common | Tagged: , , | Leave a Comment »

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

 

Posted in Common | Tagged: , | Leave a Comment »

Service Master Key issue on AG

Posted by Simon Cho on 09/22/2016

https://blogs.msdn.microsoft.com/mattm/2012/09/19/ssis-with-alwayson/

http://help.k2.com/kb001572

https://technet.microsoft.com/en-us/library/ms182754%28v=sql.110%29.aspx

Temporary solution : Alter master key again with service master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘x’ — Should be encrypted! See below
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

 

Here is SQL server 2016 AG setting.

It seems like working with SQL 2014 as well.

SQL Server 2016 AlwaysOn Availability Group Enhancements: Support for Encrypted Databases

 

 

Posted in Common | Tagged: | 1 Comment »

Saturday Night SQL VC 9/10 – Build ETL efficiently (10x) with Minimal Logging

Posted by Simon Cho on 09/12/2016

Build ETL efficiently (10x) with Minimal Logging

Here is the Presentation file. Link

http://saturdaynightsql.sqlpass.org/Home.aspx?EventID=5824

I wish to have 2-3 hour presentation length.

1 hour is short to share all those information.

 

Posted in Common | Leave a Comment »