Creating Availability group for an Encrypted database:
In this blog we will walk you through the steps to create availability group for an encrypted database. Though we have various articles, I would like to keep the entire step by step approach in one place.
We cannot create an availability group for an encrypted database from the availability group wizard. You will receive the below error
This wizard cannot add a database containing database encryption key to an availability group
We have to add the database using T-SQL statements.
Before you configure Always On for a TDE database make sure the following pre requisites are followed.
1. Always On endpoint port is opened in all node (5022 is default port, which can be changed).
2. Startup account of cluster service is added as SQL Server login and granted the ALTER ANY AVAILABILITY GROUP permission (for AlwaysOn Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).
In Windows Server 2008 Failover Clusters, the cluster service no longer runs in the context of a domain user account. Instead, the cluster service runs in the context of a local system (Nt authority\ system) account that has restricted rights to the cluster node. By default, Kerberos authentication is used. If the application does not support Kerberos authentication, NTLM authentication is used.
http://support.microsoft.com/kb/947049 has the details.
When installing the Database Engine as an Always On Availability Groups or SQL Failover Cluster Instance (SQL FCI), LOCAL SYSTEM is provisioned in the Database Engine. The LOCAL SYSTEM login is granted the ALTER ANY AVAILABILITY GROUP permission (for Always On Availability Groups) and the VIEW SERVER STATE permission (for SQL FCI).
Let me demonstrate few examples here:
Method 1 (Using T-SQL and GUI):
In this example, let us assume that you have a two node windows cluster server A and server B with two SQL standalone 2012 instances. Availability group feature is enabled for both the instances.
On server A which is our primary replica, perform the below steps
1. Enable TDE for the database test.
CREATE MASTER KEY ENCRYPTION BY PASSWORD ='Test@123';
CREATE CERTIFICATE TestCert WITH SUBJECT = 'Cert@123'
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TestCert
ALTER DATABASE test
SET ENCRYPTION ON
2. Backup the certificate on the primary server
BACKUP CERTIFICATE TestCert TO FILE = 'C:\test\TestCert.cer'
WITH PRIVATE KEY (FILE = 'C:\test\TestCert.pvk' ,
ENCRYPTION BY PASSWORD = 'Pas$w0rd');
3. Take full and log backups for database test
4. Create an availability group named Test_AG from management studio ( create availability group )
5. Now add server B as secondary replica, while doing this select Skip initial data synchronization as we need to do it manually.
6. Now add the encrypted database Test to the availability group by running the below command
ALTER AVAILABILITY GROUP Test_AG ADD DATABASE Test
7. Verify the availability replicas and database on primary from the management studio
On server B which is our primary replica, perform the below steps
1. Create the master key and certificate from the backup taken from primary.
CREATE CERTIFICATE TestCert
FROM FILE = 'C:\test\TestCert.cer'
WITH PRIVATE KEY
FILE = 'C:\test\TestCert.pvk',
DECRYPTION BY PASSWORD = 'Pas$w0rd'
2. Restore the database full and transaction log backups with no recovery
OPEN MASTER KEY DECRYPTION BY PASSWORD = ''Test@123';
Restore database test from disk='c:\test\test.bak'
Restore log test from disk='c:\test\test.trn'
3. Add the database to the availability group by running the below command
OPEN MASTER KEY DECRYPTION BY PASSWORD = ''Test@123';
ALTER DATABASE Test SET HADR AVAILABILITY GROUP = Test_AG;
4. You can verify the data availability group status and database status from management studio for the secondary replica.
Perform the above steps to add multiple encrypted databases to availability group.
Method 2 (only T-SQL):
: connect primary Server
Create database TDE_AlwaysOn
--ENABLE TDE ON DATABASE
--Create Master key
Create master key encryption by password = 'passw0rd!'
Create certificate Server_Cert with subject = 'Server Certificate'
Backup certificate Server_Cert to file = '\\PRIMARYSERVER\KeyBackup\Serv_Cert'
with private key(file = '\\PRIMARYSERVER\KeyBackup\Serv_Cert_Priv_Key',
Encryption by password = 'passw0rd!')
--Enable TDE on the datbase
Create database encryption key with algorithm = AES_128
encryption by server certificate Server_Cert
--Create hadr endpoint
create endpoint Hadr_Endpoint as TCP(
Listener_port = 5022) for data_mirroring(Role=all, encryption= REQUIRED ALGORITHM AES)
--start the endpoint
alter endpoint Hadr_Endpoint state = started;
--grant connect on endpoint to the SQL Service account(s) (SQL Server service accounts on all the nodes in the AG).
grant connect on endpoint::[Hadr_Endpoint] to [corp\sqlsvc1]
--Take full and tranaction log backups of the TDE database
backup database TDE_AlwaysOn to disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_full.bak'
backup log TDE_AlwaysOn to disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_tran.trn'
--Create AlwaysOn Availability Group for TDE database
CREATE Availability GROUP [AG_TDE]
WITH (Automated_Backup_Preference = Secondary)
FOR DATABASE [TDE_AlwaysOn]
Replica ON N'primaryServer'
WITH (Endpoint_URL = N'TCP://PRIMARYSERVER.corp.contoso.com:5022',
Failover_Mode = Manual,
Availability_Mode = Asynchronous_Commit,
Backup_Priority = 50,
Secondary_Role(Allow_Connections = ALL)
WITH (Endpoint_URL = N'TCP://SECONDAYSERVER.corp.contoso.com:5022',
: connect secondayServer
--create master key
create master key encryption by password = 'passw0rd!'
--backup master key
backup master key to file = '\\PRIMARYSERVER\KeyBackup\secondayServer_master_key'
encryption by password = 'passw0rd!'
--create certificate protected by master key
create certificate secondayServer_cert
from file = '\\PRIMARYSERVER\KeyBackup\Serv_Cert'
decryption by password = 'passw0rd!')
ALTER Availability GROUP AG_TDE JOIN;
restore database TDE_AlwaysOn from disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_full.bak' with norecovery
restore log TDE_AlwaysOn from disk = '\\PRIMARYSERVER\KeyBackup\TDE_AlwaysOn_tran.trn' with norecovery
--grant connect on endpoint to the SQL Service account(s)
alter database TDE_AlwaysOn set HADR Availability group = AG_TDE
Sravani Saluru , Support Engineer, Microsoft India GTSC
Kumar Bijayanta, Support Engineer, Microsoft India GTSC
Karthick Krishnamurthy, Technical Advisor, Microsoft India GTSC
Nice script !
Thanks for sharing
When reading your article, the information is useful. I still have a few questions:
1. did you set up TDE on instances of the sql cluster? Or did you set TDE on the physical node of sql cluster?
2. Did you use the same SQL/Domain account when setting up TDE?
Isn't there an
ALTER TDE_AlwaysOn SET ENCRYPTION ON;
missing in Method 2 ?