Enabling TDE on large Azure SQL database

Question

I need to enable TDE on some 300+ GB Azure SQL databases that are used in a production environment. Is there a recommended way to get TDE enabled in order to minimize a performance hit? SQL Server 2019 supports suspending/resuming the initial encryption but it doesn't appear to be supported in Azure SQL.

Answer 1

Hello @abc23105213

In addition to what @Andriy Bilous All newly created databases in SQL Database are encrypted by default by using service-managed transparent data encryption. Existing SQL databases created before May 2017 and SQL databases created through restore, geo-replication, and database copy are not encrypted by default. The TDE protector is set at the server level and is inherited by all databases associated with that server. You can also find more info on transparent data encryption for SQL database. Please do let us know if you have need more info.

Answer 2

TDE produces a CPU overhead despite it supports the Intel AES-NI hardware acceleration of encryption.

Some DTUs are needed to encrypt and decrypt data, but you may consider scale up the database tier to neutralize the impact and speed up the process. For example, encryption of a 500 GB on a serverless tier, Gen5 2 vcores only advances 5% on a 1 hour time interval with no user activity on the database. Maybe the maintenance window (off hours) is not enough time depending of the service level/tier and size of the database.

Use below script to monitor the progress of the encryption.

 SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
    encryption_state_desc =
    CASE encryption_state
             WHEN '0'  THEN  'No database encryption key present, no encryption'
             WHEN '1'  THEN  'Unencrypted'
             WHEN '2'  THEN  'Encryption in progress'
             WHEN '3'  THEN  'Encrypted'
             WHEN '4'  THEN  'Key change in progress'
             WHEN '5'  THEN  'Decryption in progress'
             WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
             ELSE 'No Status'
             END,
    percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys