Frequently Asked Questions about SQL Server TDE - Article on SQLNetHub

Frequently Asked Questions about SQL Server TDE

by

In an older article, we have discussed about Transparent Data Encryption (TDE) in SQL Server. In this article, we will be answering different frequently asked questions about SQL Server TDE.

 

What is SQL Server TDE?

SQL Server TDE stands for Transparent Data Encryption and it is Microsoft’s technology, based on which SQL Server performs real-time I/O encryption and decryption of the data and log files, that is the entire database. For achieving that, it uses a database encryption key stored in the database boot record.

 

Can database backups be compressed when TDE is enabled?

In SQL Server versions prior to SQL Server 2016, a TDE-enabled database cannot have its backup file compressed.

However, in SQL Server 2016 or later, backup compression for TDE-enabled databases is fully supported (learn more here).

 

Can I still access my application supported by the database, while TDE is in progress?

Yes, you can still access the application that is working on the database with a connection string, while TDE is in progress on the database, since the encryption is being performed on the storage layer, thus not affecting the operation of the database.

 

What happens if the TDE process stops for a database due to an issue with the database (i.e. corruption)?

Even in the scenario where the TDE process for a database is not fully completed (i.e., due to a possible corruption in the database), it is highly unlikely that it will break the .mdf or .ldf files. However, the best practice recommendation, is prior to enabling TDE for a database, to take a full backup of the database, as well as run a database integrity check (i.e., using DBCC CHECKDB), in order to ensure that your database is not corrupted.

 

What about the scenario where I’m enabling TDE for SQL Server Always On Availability Group databases?

Regarding the case where you might be using Always On Availability Groups, TDE, when enabled for a database on the primary replica, the database will be also encrypted on the secondary replica(s). However, prior to enabling TDE for the specific database on the primary replica, you will need to backup and then import the server certificate you created on the primary server, to the secondary server as well (and generally to all secondary servers in case you are using more than one), so that all servers to be able to decrypt the database with the same certificate (in this case, you will also need to create a Database Master Key for each server, prior to importing the primary replica’s server certificate). For such scenario, I would recommend to read this article on Microsoft Tech Community.

 

What are some other general recommendations for when enabling TDE in SQL Server?

  • As general recommendation, prior to enabling TDE for a database please make sure that you have performed the following:
  • A full backup of the database
  • Check for possible database corruptions/integrity issues (i.e., using DBCC CHECKDB)
  • Right after you have created the Database Master Key for master database and the Server Certificate, you have taken a backup of both and you kept the backup on a safe location, in order to be able to use it when needed (for example, when migrating the database to a new server, or when importing the server certificate to secondary replica servers, etc.).

 

Have more questions about TDE?

Feel free to join our LinkedIn page and Facebook Page, and post any other questions you might have about SQL Server TDE.

 

 

Strengthen your SQL Server Administration Skills – Enroll to our Online Course!

Check our online course on Udemy titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Via the course, you will learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
(Lifetime Access/ Live Demos / Downloadable Resources and more!)

Learn More

 

 

Learn essential SQL Server development tips! Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).

Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Essential SQL Server Development Tips for SQL Developers - Online Course
(Lifetime Access, Certificate of Completion, downloadable resources and more!)

Learn More

 

 

Read our other articles on Transparent Data Encryption

 

Other SQL Server Security-Related Articles

 

 

Featured Online Courses:

 

 

Subscribe to our newsletter and stay up to date!

Subscribe to our YouTube channel (SQLNetHub TV)

Easily generate snippets with Snippets Generator!

Secure your databases using DBA Security Advisor!

Generate dynamic T-SQL scripts with Dynamic SQL Generator!

Check our latest software releases!

Check our eBooks!

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub

Views: 3,723