Enabling TDE on SQL Server AOAG 2014 Databases - Guidance

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://learn.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.