question

abc23105213-7018 avatar image
0 Votes"
abc23105213-7018 asked OuryBa-MSFT commented

Enabling TDE on large Azure SQL database

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.

azure-sql-database
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @abc23105213-7018

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.
Existing SQL Managed Instance databases created before February 2019 are not encrypted by default.
SQL Managed Instance databases created through restore inherit encryption status from the source.

0 Votes 0 ·

Yup these Azure SQL databases were created prior to May 2017 so they were not encrypted by default. I need to encrypt them and am looking if anyone has a recommendation aside from do it off hours and deal with the performance impact.

0 Votes 0 ·
OuryBa-MSFT avatar image
0 Votes"
OuryBa-MSFT answered OuryBa-MSFT commented

Hello @abc23105213-7018

In addition to what @AndriyBilous 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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @abc23105213-7018 Did the answers helped? Please let us know

0 Votes 0 ·
AlbertoMorillo avatar image
0 Votes"
AlbertoMorillo answered AlbertoMorillo edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.