Back in 2011 I wrote couple of Blog posts when I was initially exploring Encryption options we have in SQL Server. Never really got a chance to work on TDE since then. Fast forward to 2016, I am participating in TDE project, where we are enabling TDE for few of our databases which are hosted on SQL Server Failover Clustered Instances. Currently we are in testting/POC phase. In this post, I will share what my findings are about TDE on clustered instances. Let’s get started…
Enabling TDE on a clustered database is no different than how we enable on a Standalone Instance. Steps are as follows:
--a. Create database TDE_Test GO --1.create a master key in master database. USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Very$ecurepwd123' --2.Create a certificate(name it) USE master GO CREATE CERTIFICATE CLUSTPRD1_TDECert WITH SUBJECT = 'Transparent Data Encryption Certificate' --3. Create a database encryption key. –Based on the certificate from Step 2–Can use AES, DES, Triple DES, RC4 etc USE TDE_Test--The db to protect GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CLUSTPRD1_TDECert--Cert from Step 2 --4. Turn it on! USE TDE_Test--The db to protect GO ALTER DATABASE TDE_Test SET ENCRYPTION ON GO -----Backup Certs---V V Imp USE MASTER GO BACKUP CERTIFICATE CLUSTPRD1_TDECert TO FILE = 'C:\TDE_Keys\CLUSTPRD1_TDECert.cer' WITH PRIVATE KEY (FILE = 'C:\TDE_Keys\CLUSTPRD1_TDECert_Key.pvk' , ENCRYPTION BY PASSWORD = 'MyAw3S0m3Pwd123#' ) GO --Backup SMK---VVV Important</pre> BACKUP SERVICE MASTER KEY TO FILE = 'c:\keys\SQLPRD1_SMK.bak' ENCRYPTION BY PASSWORD = 'gytj6%&5gjOUytp';
Okay…so what happens when SQL gets failed over to another node? Will TDE work? Will the database be still in usable state? The answer is “YES” from my initial testing. Everything remains intact. Nothing breaks!
Note: This is different from the way how it works in Alwayson AGs.
Reason: See below Screenshot taken from BOL.
The Master Key which we created on step 1 is protected by Service master key(SMK) which is scoped at Instance level is the root of our Hierarchy, And then the certificate and the keys are scoped at master and user database levels which will be failed over and moved between nodes.So….Nothing special is required for FCIs. Please correct me If I am wrong and post any thoughts in comments.