SQL Server: Transparent Data Encryption (TDE) to Encrypt a Database

SQL Server Data Encryption TDE

SQL Server 2008 introduced Transparent Data Encryption (TDE) technique to encrypt different database files like: data file, log file and backup file.

As a Database Professionals, we are also responsible for all kinds of data and database security.
You can find an N number of similar TDE theory over the internet, but still I would like to write a basic note about what is TDE and why it is important.

Any basic encryption algorithm brings your data into a state that cannot be interpreted by anyone without applying a decryption key, password or certificates.

As a DBA, we have to also maintain the decryption key, password and certificates information backup because once we lost this information, we cannot decrypt the encrypted data or database.

TDE generally uses for bulk encryption at the different type of database files.
You can enable TDE at the database level and once we enabled, it encrypts all data into the pages before it is written to the disk and it decrypts when read from the disk.

Once you enable TDE on a database, you do not require any other application code to manage encryption and decryption of the data so that you can use this feature without changing a single line of application code.

TDE uses the AES and 3DES encryption algorithms, and the encryption and decryption operations are run on background threads by SQL Server and database backup also encrypted by TDE.

TDE only encrypts the data before writing to the disk so there are also other different state where TDE is not applying encryption.

When you are sending and transferring your data between SQL Server and other application, the TDE doesn’t apply any encryption.

Before writing to the disk, data is coming from buffer pool and into this buffer pool area data is available in the plain text format so TDE doesn’t apply any encryption in this buffer pool.

The performance overhead is involved in using TDE. The encryption and decryption process do require additional CPU cycles. The overhead for using TDE ranges from about 3 percent to 30 percent, depending on the type of workload.

SQL Server instances with low I/O and low CPU usage will have the least performance impact. Servers with high CPU usage will have the most performance impact.

In the next post, You can find require T-SQL scripts and steps to enable TDE on the SQL Server Database.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

1 Comment on "SQL Server: Transparent Data Encryption (TDE) to Encrypt a Database"

Notify of

Sort by:   newest | oldest | most voted
4 months 20 days ago

Thanks for the excellent info, it really is useful.