Barney's Blog

Blog archive

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


comments powered by Disqus

Reader Comments:

Fri, Feb 14, 2014

http://www.frugalmoose.com/ DOT http://w DOT ww.insuranceslife.net http://www.joyofslowcommunication.com/p DOT rednisone.html http://w DOT ww.lifewithconfidence.net http://l DOT unchpailleft.com http://www.lehighmbawomen.com/ DOT http://w DOT ww.edmedicationguide.com

Tue, Feb 11, 2014

http://www.bitrimulti.net/v DOT iagra-online.html http://w DOT ww.autocoverageonline.net http://w DOT ww.leadinglifeinsurancebrands.com http://w DOT ww.medspricechart.com http://w DOT ww.insuranceratesinminutes.com http://w DOT ww.insuranceslife.net

Fri, Jan 31, 2014

http://www.insurecaronline.com/ DOT http://www.globemedgeorgetown.com/ DOT http://www.prchicagoway.com/ DOT http://www.yeeshkabob.com/ DOT http://www.themaghrebcenterblog.com/ DOT http://www.abwheminn.org/ DOT

Sun, Jan 26, 2014

http://www.myhomeinsuranceplace.com/ DOT http://w DOT ww.findairflights.com http://w DOT ww.cheapinsurancemate.com http://www.healthinsurcover.com/ DOT http://w DOT ww.healthinsurancebible.com http://b DOT aapalsa2013.com http://w DOT ww.protectionrates.net

Sun, Jan 19, 2014

http://w DOT ww.getcheaphealthinsurance.net http://www.forgetyoured.net/ DOT http://w DOT ww.allhealthinsurers.net http://w DOT ww.lifeinsurancpricing.com http://w DOT ww.buycheaphomeinsurance.com http://w DOT ww.cheapautoinsurcover.com

Sat, Nov 9, 2013 Topher Cheap Car Insurance Quote

http://www.carinsurquote.net/ DOT

Sat, Nov 9, 2013 Heloise car group insurance low

http://www.carinsurquote.net/ DOT

Sat, Nov 9, 2013 Debra car insurance quotes

http://www.carinsurquote.net/ DOT

Fri, Nov 8, 2013 Alexavia vehicle insurance pa

http://www.carinsurquote.net/ DOT

Fri, Oct 18, 2013 Spasic MikeJ / I am old and I did my best work as a scientist/engineer on NT I put up NT4 on a new box just for the nolatsgia. Your offer of sp6a is a God-send! Thank you so much. I am in your debt.

MikeJ / I am old and I did my best work as a scientist/engineer on NT I put up NT4 on a new box just for the http://k DOT wurmzagspo.com. Your offer of sp6a is a God-send! Thank you so much. I am in your debt.

Thu, Sep 26, 2013 Amor hard combs not really relavent but i waiting at the dumb air port and the only web site i can get on to is this one good read anyway

hard combs not really http://b DOT dcvjczzpvt.com but i waiting at the dumb air port and the only web site i can get on to is this one good read anyway

Tue, Sep 24, 2013 Firdaus The Ships's Voyages I feel technological know-how just caeuss it to be even worse. Now there is a channel to never ever care, now there wouldn't be considered a likelihood for them to find .

The Ships's Voyages I feel technological know-how just caeuss it to be even worse. Now there is a channel to never ever care, now there wouldn't be considered a likelihood for them to find .

Add Your Comment Now:

Your Name:(optional)
Your Email:(optional)
Your Location:(optional)
Comment:
Please type the letters/numbers you see above

Redmond Tech Watch

Sign up for our newsletter.

I agree to this site's Privacy Policy.