June 20, 2017 at 5:08 am
Hi,
Currently have a database setup in HA.
Whenever a fail over occurs we need to run the below on the new primary in order to open encryption key.
OPEN MASTER KEY DECRYPTION BY PASSWORD and ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
Does anyone know a way to automate this or for it to happen automatically when a fail over occurs?
June 20, 2017 at 5:26 am
can you detail a little more about your ha environment please
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
June 20, 2017 at 5:52 am
3 Servers.
Server 1 and 2 synchronous commit with automatic fail over.
Server 3, asynchronous with manual fail over.
I can run a job that checks which server is primary and it run the commands if its primary.
Just wondering is it possible for HA to pick this up and open encryption key?
June 23, 2017 at 7:17 am
What is "HA" in this scenario? It could be a number of things (FCI, AlwaysOn Availability Groups, etc.)
June 23, 2017 at 7:31 am
juniorDBA13 - Tuesday, June 20, 2017 5:52 AM3 Servers.
Server 1 and 2 synchronous commit with automatic fail over.
Server 3, asynchronous with manual fail over.I can run a job that checks which server is primary and it run the commands if its primary.
Just wondering is it possible for HA to pick this up and open encryption key?
You shouldnt need to open the database master key if its encrypted by the service master key.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 30, 2018 at 9:43 am
having the same problem now
you can set up a sql job on both ends to run OPEN MASTER KEY DECRYPTION BY PASSWORD and ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY if the replica is primary. Just have the jobs execute every few minutes and write code to check for replica status. problem is the password is going to be in the job text in the open.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply