Q&A with Bradley Ball: Transparent Data Encryption in SQL Server 2012
Microsoft introduced transparent data encryption (TDE) when it released SQL Server 2008, adding full database encryption rather than the limited cell-based encryption that debuted in SQL Server 2005. While there are no major new TDE features in SQL Server 2012, Microsoft has upped the ante by enabling the database master keys to use the Advanced Encryption Standard (AES) 256 encryption algorithm. The earlier versions used the Triple Data Encryption Standard (TDE).
Bradley Ball, a senior consultant at Pragmatic Works Software and upcoming sessions speaker at this year's Live! 360 event, has a deep understanding of how to use database encryption in SQL Server 2012.
Q: Are there new overall technologies that improve encryption related to security?
A: Not in this edition, but what we did get is a stabilization of the TDE code base. There were issues that SQL 2008 and 2008 R2 had with TDE -- with its use of snapshot isolation level and the version store in tempdb, for example -- that have been fixed in SQL 2012.
We're getting a more mature code base, which will benefit adopters of SQL 2012. One example is that you used to be able to drop a certificate even if it was in use. After restarting, the SQL instance of all the databases that used TDE would be placed in suspect mode, until the certificate could be replaced and the instance restarted.
In SQL Server 2012 that dependency is now enforced by code. Attempting to drop a certificate currently in use on a database will fail.
Q: Are there new technologies or techniques that encrypt while retaining performance?
A: There has always been a slight CPU penalty to TDE. If your CPU usage averages 70 percent or higher daily, then you may not want to consider TDE without performing benchmark testing in a lower lifecycle.
TDE is so dependent on I/O that if you have an I/O bottleneck it could translate into higher CPU. If you know you have an I/O bottleneck, you'd want to perform benchmark testing in a lower lifecycle using TDE before placing it into production. However, on most systems I've worked on after implementing TDE and comparing before and after baselines, I typically don't notice a difference.
Q: Can you share any best practices or tips?
A: The big thing to remember is once you start using TDE, your certificate backups are just as important as your database backups! If you don't have a certificate on hand and need to restore a backup to a new server, your backup file is tied to that certificate. Until you restore a copy of your certificate, you can't restore a copy of your backup. This includes detach and attach operations as well.
At my blog, I have the slide deck from previous presentations, as well as scripts that will assist in managing TDE once implemented -- including scripts to automate the backups of your certificates.
Q: Are there free tools or third parties you think are worth looking at?
A: Not with regard to TDE. Any other third-party product that performs encryption on data at rest interfaces with the Microsoft API at the Windows storage level. I'd rather have my writes to disk handled from cradle to grave by Microsoft than by anyone else.
If heading out to Orlando for this year's Live! 360 event in December, make sure to catch Bradley's workshop, "Transparent Database Encryption Inside and Out in SQL Server 2012."
Posted by Doug Barney on 11/05/2012 at 1:19 PM