ADDING A TDE ENCRYPTED DB TO A SQL SERVER ALWAYSON AVAILABILITY GROUP
I’ve recommended against the use of Transparent Data Encryption (TDE) database container encryption (SQL Server & Oracle) in the past. Reference my blog or blog. TDE flat out provides no protection while the database is running, and we all strive for 24×365 uptime. Given the option, any bad guy is going to steal your data through the front door rather than shut down your database and raise the ‘something is wrong’ alert. Of course, follow normal procedures for encrypting database backups that will be moved off-site, and destroying or wiping abandoned storage.
Adding a database to an existing availability group is another one of the hassles that TDE causes for the very little (or no) benefit it provides. Specifically with SQL Server 2014 and prior, one can not use the standard SQL Server Management Studio (SSMS) wizard to add a TDE encrypted database to an AlwaysOn Availability Group (AAG).
In my situation, I was lucky. We choose to encrypt the second database with the same key. The first database was in the AAG, and the AAG was functioning perfectly. The problem is the wizard had the second database greyed out… as if it was not a candidate for AAGs.
So, here’s what I did to add the TDE encrypted database. (Remember to test your interpretation of these instructions on a non-production AAG.)
- Insure that your first database is TDE encrypted, the AlwaysOn Availability Group is functioning (synchronizing) properly. This means that TDE keys have been placed on all nodes (ie replica/instances) of the AAG.
- Insure that the current ‘primary’ node has been set to the same node as the node with the second database. (There is a ‘no data loss’ method of manually failing an AAG.)
- Insure the second database is TDE encrypted with the same key as the first database. As the first database is functioning in the AAG, the keys that exist on all the AAG nodes are the same keys that are needed for the second database.
- On primary node of the AAG…
- Insure the second database is using FULL recovery.
- Use the backup wizard to take a full backup of the second database
- Use the backup wizard to take a tlog backup of the second database
- Add the second database to the AAG with the following TSQL command… ALTER AVAILABILITY GROUP aag_name ADD DATABASE database_name;
- On each of the secondary nodes of the AAG…
- Use the restore wizard to restore the full backup taken of the second database. INSURE ‘NORECOVERY’ is specified.
- Use the restore tlog wizard to restore the tlog backup taken of the second database. INSURE ‘NORECOVERY’ is specified.
- Add/Join the second database to the AAG with the following TSQL command… ALTER DATABASE database_name SET HADR AVAILABILITY GROUP = aag_name;
- Insure that the secondary nodes are synchronizing (after ‘refreshing’ in SSMS). Test a manual failover.
It’s far simpler to not have to deal with TDE in a AAG… It’s especially frustrating given that TDE offers no real security. (yea, the ‘dead horse’ thing!).
Keywords: Always On Availability Group, AlwaysOn Availability Group