As we work with Microsoft customers who are implementing encryption with Extensible Key Management in SQL Server 2008 R2, the question inevitably arises about whether to use Transparent Data Encryption (TDE) or Cell Level Encryption.

As you might guess, this comes down to tradeoffs between ease of implementation, performance, and security.

Transparent Data Encryption (TDE) is very easy to implement. It doesn’t require any changes to your existing applications, and using TDE with Alliance Key Manager, our encryption key management solution,  is very straight-forward. It typically only takes a few minutes to get up and running with our encryption key manager and TDE. Cell level encryption, on the other hand, will take at least some changes to your SQL statements or .NET application code. These changes aren’t difficult at all, but you still need to make them. For some of our customers who don’t have the source code for the application, or who don’t have IT resources available, this can be a significant barrier. The good news is that the work to set up the Alliance Key Manager key server is the same for both Cell Level Encryption as for TDE. From an ease of implementation point of view, TDE is the easy winner.

The second difference between TDE and Cell Level Encryption is performance. You might think that Cell Level Encryption would perform better because there is actually less data being encrypted, but you would be wrong! TDE is the clear winner in the performance category. Microsoft estimates that there will be a 2% to 4% performance penalty with TDE. Our own tests using the publicly available SQL Stress tool (www.sqlstress.com) shows that for most databases the performance penalty is closer to the 2% value, and in some cases less than 2%. Cell Level Encryption almost always carries a bigger performance impact. So TDE is once again the winner in the performance category.

The security tradeoffs are more complex. As Microsoft has noted, TDE does not encrypt and decrypt in memory:

“Note that neither BitLocker nor TDE encrypt data in memory. This can provide a substantial performance benefit over the encryption offered in SQL Server 2005, including the use of indexed searches (discussed later). But this also means that a system administrator with access to this memory can read the unencrypted data. All users with database permissions to access data will see unencrypted data.”

Cell Level Encryption does do encryption and decryption in memory, and this provides an incremental improvement in security.  So Cell Level Encryption provides a slightly better security strategy. If you use TDE as your encryption strategy, you will want to be sure to use a number of other techniques to lock down your environment.  You can read more about this on the Microsoft MSDN web site here.

I think for most Microsoft customers the use of TDE will fit well with their tolerance for risk and their security strategy.  Whether you choose TDE or Cell Level Encryption, you end up with your data much better protected.

You need to combine encryption and good encryption key management with other steps to properly secure your Windows and SQL Server environment.  Encryption is not a magic bullet, but without it your data is exposed to loss.

For further information, download our white paper "Encryption Key Management for Microsoft SQL Server 2008" and learn about meeting encryption and key management challenges on your Microsoft SQL Server.