Previously, in my article SQL Server 2012 AlwaysOn, I discussed the components that make up SQL Server 2012’s AlwaysOn and how you can configure your SQL Server environment using AlwaysOn Availability Groups (AG) to meet the ever-increasing need for ‘High Availability’ (HA) and ‘Disaster Recovery’ (DR). One of the benefits that I outlined was the ability to use the Enterprise Edition feature ‘Transparent Data Encryption’ (TDE) to secure your databases.
Transparent Data Encryption
TDE allows you to protect your databases by performing real-time I/O encryption utilising keys. This prevents anyone who does not have these keys from accessing the data. I am not going to dive into the details of TDE in this article; there are links in the References & Further Reading section later in the article for you to get a better understanding of TDE.
Configuring your AlwaysOn environment
If you have not yet set up and configured a SQL Server 2012 AlwaysOn environment, then please have a read of my article SQL Server AlwaysOn. In this article I shall assume that you already have an environment set up that is already using AlwaysOn Availability Groups (AG). Normally, if you are going to create a new AG or need to add a database into an existing AG, you can simply use the wizards provided in SSMS. However, if you are going to add a database that has been configured to be encrypted using TDE then you will not be able to use the wizards.
Whether you are creating a new AG, or you are needing to add a database that has been encrypted using TDE, there are steps that you need to complete on your SQL Server 2012 AlwaysOn Primary Replica. These are:
-
Create a Master Key on the Primary Replica
-
Backup the Master Key
-
Create a Certificate protected by the Master Key
-
Backup the Certificate
-
Create a Database Encryption Key
-
Enable a TDE on a Database
Here is some example code that can be used to perform these tasks:
1: Use Master;
2:
3:
4: -- Create a Master Key
5: Create Master Key Encryption By Password = 'P@ssw0rd1';
6:
7: -- Backup the Master Key
8: Backup Master Key
9: To File = '\PathEncryption_BackupsMyServer_MK.key'
10: Encryption By Password = 'P@ssword2';
11:
12: -- Create Certificate Protected by Master Key
13: Create Certificate MyServer_Cert
14: With Subject = 'My DEK Certificate';
15:
16: -- Backup the Certificate
17: Backup Certificate MyServer_Cert
18: To File = '\PathEncryption_BackupsMyServer_Cer'
19: With Private Key (
20: File = '\PathEncryption_BackupsMyServer_PrivKey.cer',
21: Encryption By Password = 'P@ssword3'
22: );
23:
24:
25: -- Move to the database you wish to enable TDE on
26: Use TestTDE_WithAlwaysOn;
27:
28:
29: -- Create a Database Encryption Key
30: Create Database Encryption Key
31: With Algorithm = AES_128
32: Encryption By Server Certificate MyServer_Cert;
33:
34:
35: -- Enable the Database for Encryption by TDE
36: Alter Database TestTDE_WithAlwaysOn
37: Set Encryption On;
We need to undertake some configuration steps on the Secondary Replicas in order to allow us to be able to replicate the Availability Databases. We need to:
-
Create a Master Key on the Secondary Replica
-
Backup the Master Key
-
Create Certificate from the Primary Replica
Here is some example code that can be used to perform these tasks:
1: Use Master;
2:
3:
4: -- Create a Master Key
5: Create Master Key Encryption By Password = 'P@ssw0rd1';
6:
7: -- Backup the Master Key
8: Backup Master Key
9: To File = '\PathEncryption_BackupsMyServer2_MK.key'
10: Encryption By Password = 'P@ssword2';
11:
12: -- Create Certificate Protected by Master Key
13: Create Certificate MyServer_Cert
14: From File = '\PathEncryption_BackupsMyServer_Cer'
15: With Private Key (
16: File = '\PathEncryption_BackupsMyServer_PrivKey.cer',
17: Decryption By Password = 'P@ssword3'
18: );
The above will need to be undertaken on every Secondary Replica in the SQL Server 2012 AlwaysOn environment. After the Replicas are all configured with the Certificate, the encrypted database(s) can then be made available on all of the Replicas.
When we are using the ‘New Availability Group Wizard’, we have to select those database(s) which will participate in the AlwaysOn Availability Group. If one of the databases has been enabled for encryption utilising TDE, we will not be able to use the wizard to create the AlwaysOn Availability Group. To achieve this, we need to use T-SQL or PowerShell to create the Availability Group.
This T-SQL Code (to run in SQLCMD mode in SSMS) will manually create an Availability Group:
1: -- The Below following code needs to be run using SQLCMD Mode
2: :Connect PrimaryReplicaServer
3:
4: Use Master
5: Go
6:
7: -- 1/ Create AlwaysOn AAG with TDE enabled database
8: Create Availability Group [SQL2012_TDE]
9: With (Automated_Backup_Preference = Secondary)
10: For Database [TestTDE_WithAlwaysOn]
11: Replica on N'PrimaryReplicaServer'
12: With (Endpoint_URL = N'TCP://PrimaryReplicaServer.Domain1.Com:5022',
13: Failover_Mode = Manual,
14: Availability_Mode = Asynchronous_Commit,
15: Backup_Priority = 50,
16: Secondary_Role(Allow_Connections = All)
17: ),
18: N'SecondaryReplicaServer'
19: With (Endpoint_URL = N'TCP://SecondaryReplicaServer.Domain1.Com:5022',
20: Failover_Mode = Manual,
21: Availability_Mode = Asynchronous_Commit,
22: Backup_Priority = 50,
23: Secondary_Role(Allow_Connections = All)
24: );
25: Go
26:
27: :Connect SecondaryReplicaServer
28:
29: -- 2/ Join the Secondary Replica to the Newly Created AAG.
30: Alter Availability Group [SQL2012_TDE] Join;
31: Go
32:
33: :Connect PrimaryReplicaServer
34:
35: -- 3/ Create a Full Backup
36: Backup Database [TestTDE_WithAlwaysOn]
37: To Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak'
38: With Copy_Only;
39:
40: Go
41:
42: :Connect SecondaryReplicaServer
43:
44: -- 4/ Start the restoration process to bring your database to a synchronised state
45: Restore Database [TestTDE_WithAlwaysOn]
46: From Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak'
47: With NoRecovery;
48:
49: Go
50:
51: :Connect PrimaryReplicaServer
52:
53: -- 5/ Create a TLog Backup
54: Backup Log [TestTDE_WithAlwaysOn]
55: From Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_TL.bak';
56:
57: Go
58:
59: :Connect SecondaryReplicaServer
60:
61: -- 6/ Start the restoration process to bring your database to a synchronised state
62: Restore Log [TestTDE_WithAlwaysOn]
63: From Disk = '\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak'
64: With NoRecovery;
65:
66: Go
67:
68: :Connect SecondaryReplicaServer
69:
70: -- 7/ Join the Database to the AAG and bring it into the readable synchronised state.
71: Alter Database [TestTDE_WithAlwaysOn]
72: Set HADR Availability Group = [SQL2012_TDE];
73:
74: Go
Alternatively if you prefer to use PowerShell here is some code to manually create an Availability Group:
1: Import-Module "SQLPS" -DisableNameChecking
2:
3: # Create Full Database Backup
4: Backup-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
5: "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak”
6: -ServerInstance "ServerCoreNode1"
7:
8: # Create TLog backup
9: Backup-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
10: "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_TL.bak"
11: -ServerInstance "ServerCoreNode1" -BackupAction Log
12:
13: # Restore Database and Log on Secondary (NoRecovery)
14: Restore-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
15: "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_DB.bak"
16: -ServerInstance "ServerCoreNode2" -NoRecovery
17:
18: Restore-SQLDatabase -Database "TestTDE_WithAlwaysOn" -BackupFile
19: "\PathAlwaysOn_BackupsTestTDE_WithAlwaysOn_TL.bak"
20: -ServerInstance "ServerCoreNode2" -RestoreAction Log -NoRecovery
21:
22: # Create an In-Memory representation of Primary Replica
23: $PrimaryReplica = New-SQLAvailabilityReplica -Name "ServerCoreNode1"
24: -EndPointURL "TCP://ServerCoreNode1.Test.Com:5022" -AvailabilityMode
25: "AsynchronousCommit" -FailoverMode "Manual" -Version 11 -AsTemplate
26:
27: # Create an In-Memory representation of Secondary Replica
28: $SecondaryReplica = New-SQLAvailabilityReplica -Name "ServerCoreNode2"
29: -EndPointURL "TCP://ServerCoreNode2.Test.Com:5022" -AvailabilityMode
30: "AsynchronousCommit" -FailoverMode "Manual" -Version 11 -AsTemplate
31:
32: # Create the Availability Group
33: New-SQLAvailabilityGroup -Name "SQL2012_TDE" -Path
34: "SQLServer:SQLServerCoreNode1Default" -AvailabilityReplica
35: @($PrimaryReplica,$SecondaryReplica) -Database "TestTDE_WithAlwaysOn"
36:
37: #Join the Secondary Replica to the Availability Group
38: Join-SQLAvailabilityGroup -Path "SQLServer:SQLServerCoreNode2Default"
39: -Name "SQL2012_TDE"
40:
41: # Join the Secondary Database to the Availability Group
42: Add-SQLAvailabilityDatabase -Path
43: "SQLServer:SQLServerCoreNode2DefaultAvailabilityGroupsSQL2012_TDE"
44: -Database "TestTDE_WithAlwaysOn"
If we already have an AG, and we need to add a database that has been configured for encryption, then a slight modification to the T-SQL code above will allow us to achieve this. Swap Steps 1 & 2 in the T-SQL Code above for the code below:
1: -- The Below following code needs to be run using SQLCMD Mode
2: Connect PrimaryReplicaServer
3: -- Add your database to the Existing Availability Group
4: ALTER AVAILABILITY GROUP [SQL2012_TDE] ADD DATABASE [TestTDE_WithAlwaysOn];
5: GO
We have now configured the environment to have an Encrypted database participate in a SQL Server 2012 AlwaysOn Availability Group.
References & Further Reading
Transparent Data Encryption – http://msdn.microsoft.com/en-us/library/bb934049.aspx
Create Master Key – http://technet.microsoft.com/en-us/library/ms174382.aspx
Create Certificates – http://msdn.microsoft.com/en-us/library/ms187798.aspx
Create Database Master Key – http://technet.microsoft.com/en-us/library/aa337551.aspx
Move a TDE protected Database – http://technet.microsoft.com/en-us/library/ff773063.aspx
Backup Master Keys – http://technet.microsoft.com/en-us/library/ms174387.aspx
Backup Certificates – http://msdn.microsoft.com/en-us/library/ms178578.aspx
Manually Prepare a DB for for AG’s – http://msdn.microsoft.com/en-us/library/ff878349.aspx
Encrypted Databases with AG’s – http://msdn.microsoft.com/en-us/library/hh510178.aspx
Create an AG with Powershell – http://msdn.microsoft.com/en-us/library/gg492181.aspx
Leave a Reply