Sql-server – Enabling TDE on SQL Server AOAG 2014 Databases – Guidance

availability-groupsperformancesql serversql server 2014transparent-data-encryption

I am completely new to Transparent Data encryption and please help me on below questions related to enabling the TDE on existing PRODUCTION Databases.

Primary Question:

What are the things which I should keep in mind before enabling the TDE on PRODUCTION servers ?

NOTES

  1. I have SQL Server 2014 AOAG Databases PROD site. The number of DBs is close to 10.
  2. I read about DEK , SMK , Certificate creation process and backup of Key's and certificates.

Questions:

  1. Now the Databases are TDE free ,what will be the new changes or effects which I will be seeing post enablement of TDE in my PROD site.
  2. What is the performance impact on my existing PRODUCTION database accessibility.
  3. Will there be any performance impact at all ? What is the range of impact after enabling TDE on my DBs ?
  4. What will be the effect on the AOAG component Failover part ? Will I face issues while the Databases are with TDE enabled ?
  5. What will be the affect while Backing up or Restoring the Databases to different SQL Server ?
  6. If I enable TDE on Primary Replica and all secondaries will I be able to add the DBs to AOAG 2014 using the Wizard.

Please guide me , I was not completely sure of the consequences of this change handling , please help me know the disadvantages related to existing situation and post enabling TDE and performance related issues and points.

Many thanks.

Best Answer

1 Now the Databases are TDE free ,what will be the new changes or effects which I will be seeing post enablement of TDE in my PROD site.

The data at rest is encrypted, the data in memory not. The encryption might take a while, depending on your database size, you can follow this up with:

SELECT DB_Name(database_id) AS DB, encryption_state 
FROM sys.dm_database_encryption_keys;

Your backups will go slower (10-20 %) and use more cpu If you are going to disk a lot (not enough memory, bad queries , ...) then I would not enable TDE before fixing this.

If any filegroups are read only, then enabling TDE will fail. There are other restrictions, found here: https://docs.microsoft.com/nl-nl/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-2017

I would also advise to test TDE on a DEV server first.

2 What is the performance impact on my existing PRODUCTION database accessibility.

&

3 Will there be any performance impact at all ? What is the range of impact after enabling TDE on my DBs ?

The data at rest is encrypted, the data in memory is not.

Your backups will go slower (10-20 %) and use more cpu.

When modifying data you will see some overhead, since it will have to be encrypted on disk.

If your disk access is low, then the overhead will be lower and TDE will have a lower impact on performance.

4 What will be the effect on the AOAG component Failover part ? Will I face issues while the Databases are with TDE enabled ?

On the secondary, do the following:

1 Make backups of the master key and the certificates

2) Create a master key

3) Restore the key and Certificate from the primary on the secondary

4) Restore the database on the secondary

5) add the database to the AG

(more info here: https://blogs.msdn.microsoft.com/sqlhenryweng/2017/11/08/configure-tde-encrypted-database-in-sql-server-alwayson-availability-group/)

5 What will be the affect while Backing up or Restoring the Databases to different SQL Server ?

To do restores from the backups you need to have the same certificate that was used for encryption. This certificate is protected by the master key.

To accomplish this:

1 Make backups of the master key and the certificates 2 Restore the key and certificates on the new principal and mirror pairs

Extra checklist to migrate to a new server:

  • There's a database master key in the master database of the new server.
  • The certificate used to encrypt the database is restored along with its private key.
  • Restore the database

6 If I enable TDE on Primary Replica and all secondaries will I be able to add the DBs to AOAG 2014 using the Wizard.

See Question 4 for more info on this.

Related Question