July 28, 2010 at 3:30 pm
I am exploring TDE; I have a couple of questions.
1. I found that as long as one has the certificate, the encrypted DB can be restored on any server. How does TDE differs from backup with a password?
2. If in a table 2 cells have same values (e.g. ABCD123), does the encrypted values at the disk will have the same values as well? If Yes, why TDE doesn’t benefit from database compression?
July 28, 2010 at 5:46 pm
TDE works by encrypting every page before writing it to disk, and decrypting every page when reading it into memory. This means that there is no performance hit having TDE, unless you're doing a lot of I/O.
There are two types of compression - data compression, and backup compression. Data compression does work with TDE - the page is compressed, and then the compressed page is encrypted. Backup compression works by reading pages off disk, and compressing them. With TDE, the encrypted pages on disk appear to be highly randomised data (rather than chunks of repeating data if it was not encrypted), which is not compressible.
So, Data Compression + TDE = Good!
Backup Compression + TDE = Works, but no benefit!
To answer question #1, TDE compresses each page as it's written to disk - so the data and log files are encrypted prior to the backup. Backups protected with a password are NOT encrypted - they just refuse to restore unless you know the password.
#2 should be answered from the information above.
July 28, 2010 at 8:11 pm
Thank you, Jim.
I have one more question. Unless I am missing something, from a layman's point, certificate looks like a sophisticated password. If one gets hold of the certificate, he could easily restore the DB (though the DB is encrypted.)
Is there a better solution? Something like, authenticating the certificate with proper certificate-authority etc. I am just thinking loud.
July 28, 2010 at 8:51 pm
The certificate is a "fancy password". You need to protect this to ensure that the database is protected.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply