Disk Encryption and The Impact to DBAs

Question

Disk Encryption and The Impact to DBAs

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

rhamnusia Old Hand Points: 365 More actions · Answer 1

Thanks to all for posting. I am trying to locate and devour any information or experiences encountered by others around encryption, and do appreciate your time and information. I do believe the choice depends up on circumstances and need, as well as an environmental decision.
We have multiple DBs we encrypt with third party encryption. We chose not to use TDE for a few reasons:
When we are protecting a very small subset of the data. In one instance we are protecting roughly 100 GB from a 4.5 TB database, and the sensitive data is not expected to grow proportionally with the other data. Decided no need to encrypt everything and suffer the overhead. Encrypted data exists on separate filegroup located on separate LUN.
Storage savings - the storage controller compresses on write and saves about 60% space by doing so. My understanding it is not possible to gain significant storage savings by compressing encrypted data so encrypting the full database consumes another ~2TB of physical storage for the single DB.
We were able to perform while online by dropping the clustered index then rebuilding on the encrypted filegroup. Yes it takes time and will have table lock, but the majority of the system function is available.
No dependance on SQL version, 2008 R2 and up.

We DO use TDE when the majority of the databases on the instance contains sensitive data. Too cumbersome to relocate tables to different filegroup when say, more than 40% of the data is sensitive and expected to grow.

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

TDE will impact compression of backups, because backup compression is applied on top of encrypted data. However, TDE has no effect on table and index compression, because TDE is applied on top of pages and rows. So, TDE should have no impact on the online storage requirements of your database.
https://msdn.microsoft.com/en-us/library/dd894051(v=sql.100).aspx

Also, I've never seen TDE add more than a 2% impact to read or write throughput. However, it probably depends on the specific data access patterns of your application. Compression, in general, requires more computation for writes than it does for reads, and most of the databases I work with on a daily basis tend to be large, write-once, read-many, and are typically bulk loaded rather than high transaction volume. When it comes to performance impact of compression, your mileage will vary.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

lbnayak Old Hand Points: 383 More actions · Answer 3

It appears to me that you can be hit with performance issues of a different nature if you are not careful with your design while incorporating AlwaysEncrypted. Eg. Let us assume that you have Person table which had a BirthDate, ZipCode and Gender column. Someone makes a determination that taken together, these can reveal the identity of a Person and need to be AlwaysEncrypt'ed . How do you now do queries of the type "SELECT for me Persons who are Females over the age 50 living in Marin County". Before these columns were AlwaysEncrypted, a simple SELECT statement could do date comparison on BirthDate ,literal comparisons against Gender and ZipCode and give you the results. With these columns AlwaysEncrypted, the SQL Server cannot do these operations. This processing will now need to be done at the application end . Is my reasoning correct ? At a minimum, the schema will now need to change to accommodate a Age column and the decision to AlwaysEncrypt Gender and ZipCode reconsidered.
In general, dynamic queries which make use of literal parameters targeting AlwaysEncrypted columns would also be impacted.
All these constraints are not obvious, at least to me they were not. The "Encryption on the wire, encryption in memory and encryption at rest" advertisement for AlwaysEncrypted seems to have some not-so-trivial costs involved.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

SQLBlimp wrote:

Eric M Russell - Tuesday, January 16, 2018 7:29 AM
Where I work, we've started implementing SQL Server TDE, which I believe has many advantages over FDE (Full Disk Encryption) solutions.
TDE:
- Is free with the SQL Server license. Starting with 2016 SP1, TDE is now available on Standard and Express editions.
- Encrypts only the databases that need encryption.
(snip)
Hi --
There are some shops that cannot move up to 2016 or newer and may be handcuffed to a lower version. BitLocker was rejected by the Infrastructure manager in favor of SafeNet ProtectFile There was an issue that caused him to reject BitLocker, but he is out of town so I can't ask him. The article recounts some of our challenges with SafeNet.

I realize this is a year and a half old thread, but we recently just implemented SafeNet ProtectFile with SQL Server databases. We were able to avoid significant impact in performance was ensuring a special registry key was setup:

\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Sfntpffd\OptimizeDbIo = 1