Transparent Data Encryption

Question

Transparent Data Encryption

AER

SSCrazy

Points: 2763
More actions
April 2, 2019 at 6:28 pm

#2535096

Hi,
I am a SQL Server DBA in the Company.
One of our Clients is running SQL Server 2012 Enterprise Edition utilizing AlwaysOn Cluster.
I was asked for the Security reasons to check into the Transparent Data Encryption option for the major database in the Cluster.
I need the SQL Community thoughts about this. Is anybody utilizing these features and what are pros and cons?
Also we are using few SQL Server tools, such as LiteSpeed, DPA Solarwinds, Toad for SQL Server, Embarcadero, etc... which utilization probably can be affected by these changes.
Please respond.
Thank you.
Alex
April 11, 2019 at 11:50 am

This was removed by the editor as SPAM
April 11, 2019 at 11:50 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

AER wrote:

Hi,
I am a SQL Server DBA in the Company.
One of our Clients is running SQL Server 2012 Enterprise Edition utilizing AlwaysOn Cluster.
I was asked for the Security reasons to check into the Transparent Data Encryption option for the major database in the Cluster.
I need the SQL Community thoughts about this. Is anybody utilizing these features and what are pros and cons?
Also we are using few SQL Server tools, such as LiteSpeed, DPA Solarwinds, Toad for SQL Server, Embarcadero, etc... which utilization probably can be affected by these changes.
Please respond.
Thank you.
Alex

Yes they are used together. The one issue I can think of is with LiteSpeed and backup compression. Prior to SQL Server 2016, backup compression doesn't really work. You might get some compression but little, if any. With LiteSpeed, they used to recommend using compression Level 1 or 0 to keep the CPU from pegging during backups. You'd want to check their most recent documents related to this. Compression is supported on SQL Server 2016 and above but you will want to be current on service packs, CU as there are some issues with corruption without the required service packs, CUs.

Not all tools are impacted as TDE is for data at rest.

Sue

AER SSCrazy Points: 2763 More actions · Answer 2

Thanks for your response Sue,

But my major question was to DBAs not about using tools. The major question was, does anybody using Transparent Data Encryption in SQL Server 2012 (not 2016) Enterprise Edition that has AlwaysOn Cluster installed. And if they do, then what are the pros and cons when utilizing it?

Anybody have answer to this question please?

Thank you.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

I've never really noticed anything with it enabled, it encrypts data at rest and auditors like when it's enabled. I mentioned the only con I can think of. I've used it since it was introduced.

Hopefully the other real DBAs will jump in and respond.

Andrey SSC Eights! Points: 840 More actions · Answer 4

AER wrote:

Thanks for your response Sue, But my major question was to DBAs not about using tools. The major question was, does anybody using Transparent Data Encryption in SQL Server 2012 (not 2016) Enterprise Edition that has AlwaysOn Cluster installed. And if they do, then what are the pros and cons when utilizing it? Anybody have answer to this question please? Thank you.

PROS :

+ meet requirements of Security or Business

+ backups and db files are protected

CONS:

- minor performance overhead

- to bring db online and even configure AG for TDE db on a secondary replica you have to copy master key or certificate first

- the same is valid for any side copies of encrypted DB (TEST, UAT, DEV environments)

Links:

https://www.sqlshack.com/how-to-add-a-tde-encrypted-user-database-to-an-always-on-availability-group/

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/move-a-tde-protected-database-to-another-sql-server?view=sql-server-2017

Native backup compression is now compatible with TDE (SQL2016+)

https://www.brentozar.com/archive/2016/07/tde-backup-compression-together-last/