Transparent Data Encryption (TDE) on AlwaysON Availability Group

Question

I’ve been tasked with securing SQL Server data and log files (MDF and LDF) using Transparent Data Encryption (TDE) across all SQL Servers in our environment, which consists of over 85 instances. This is a significant undertaking, and I’m concerned about the potential impact of deploying TDE universally across such a large number of servers, especially considering that some of these server's host databases for various business-critical applications.

I have never implemented TDE at this scale, and I’d like to ask if anyone here has experience deploying TDE across hundreds of SQL Server instances. Specifically, I’m interested in understanding the impact, if any, on performance. Additionally, are there other methods besides Transparent Data Encryption to secure database files at rest that might be worth considering?

Answer 1

Sounds like you should checkout dbatools.io to see if they have something to help you to automate this.

The critical thing is of course to keep track of all certificates so that you don't loose them. I don't have any experience of this myself, since I am not in a DBA role.

I recommend that you start with some test systems you can afford to lose, so that you get a grip of the operation. That should of course include AGs.

It is also worth mentioning that the value of TDE is of somewhat limited nature. If an intruder gains access to the machine, the intruder also has access to the key chain that is needed to decrypt the database. However, if the database files are on a SAN, and an attacker only has access to the SAN, the attacker cannot do anything with the files.

The backups will be encrypted and that is important, but that can be achieved without TDE, I believe.

Answer 2

I have just been doing this at a site with around 150 servers.

It works as advertised, but it can take quite a while to get the DBs encrypted if they are large, or if the I/O subsystem is slow. (Keep in mind that every page needs to be rewritten).

The main issue as Erland noted is managing the certificates. Getting that wrong can be a CLM (career-limiting move).

I've also written a eBook on TDE that you can download here on my free stuff page: https://sqldownunder.com/free-stuff