1

We are hosting sharepoint DBs with AlwaysOn AGs and FCI. My boss asked me what the options for encryption are on the content database, it's backup files and at the column level?

I have read about TDE. Could this harm performance?

Is windows level encryption possible?

Are there any other options?

2

TDE will add a little extra CPU cost to the server but that's marginal on current CPUs. It will however only protect the data at rest, but that's including backups of the databases.

It's fully compatible with failover clustering and AlwaysOn but you have to make sure that all nodes in AlwaysOn are configured correctly (https://curah.microsoft.com/282935/how-to-configure-always-on-for-a-sql-server-tde-database)

I would say that it's more practical than trying to use Bitlocker or similar techonologies.

3

My boss ask me for possibilities to do the encryption on content DB level, it backup. or on column level?

This is something you and You're boss need to sit and discuss on as what could be pros and cons of using TDE or Cell level encryption:

However Few points to note:

  1. TDE does not offer the granularity of protection that cell-level encryption offers, it does provide a level of protection that cannot be achieved through cell-level encryption

  2. TDE does not require any schema modifications and since Since the physical data files and not the data itself are encrypted, the primary keys and indexes on the data are unaffected, and so optimal query execution can be maintained.

  3. In MS whitepaper titled "Database Encryption in SQL Server 2008 Enterprise Edition", Microsoft estimates the performance degradation for TDE to be 3-5%, while cell-level encryption is estimated to be 20-28%

  4. Also, we have seen that the compress backup feature from SQL 2008 onwards which helps in saving the space without using any 3 rd party software seems to be of very less active means the compression it do with that feature on is quite less effective.

  5. As far as i know i think Instant File initialization also does not work when TDE has been implemented.

  6. Also, i am sure TDE also encrypts the backups starting from SQL server 2014, and don't think it does same for SQL server 2012

So there's few ups and downs on both TDE and Cell level, but you have to ultimately decide what suits and fits within you're system health as well as budget.

Many other points you would like to go and can be read here

Also,

Implementing TDE in conjunction with cell-level encryption provides a layered approach to data security, which enhances its effectiveness. Another option for protecting sensitive data that is available is one-way encryption, also referred to as hashing. One-way encryption can be applied alongside TDE, cell-level encryption and other obfuscation methods

  • @kasqldba about point 6. When you enable TDE you are encrypting all the pages in the database. As the backup is a page level copy all backups of the database are encrypted, no matter which version of SQL server. – Spörri May 27 '15 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.