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?
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!
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!
Read our other articles on Transparent Data Encryption
- Transparent Data Encryption (TDE) in SQL Server
- Encrypting a SQL Server Database Backup
- 10 Facts About SQL Server Transparent Data Encryption
- Encrypting SQL Server Databases
Other SQL Server Security-Related Articles
- How to Enable SSL Certificate-Based Encryption on a SQL Server Failover Cluster
- Why You Need to Secure Your SQL Server Instances
- Policy-Based Management in SQL Server
- Should Windows “Built-In\Administrators” Group be SQL Server SysAdmins?
- Frequent Password Expiration: Time to Revise it?
- The “Public” Database Role in SQL Server
- …check all
Featured Online Courses:
- Introduction to Azure Database for MySQL
- Working with Python on Windows and SQL Server Databases
- Boost SQL Server Database Performance with In-Memory OLTP
- Introduction to Azure SQL Database for Beginners
- Essential SQL Server Administration Tips
- SQL Server Fundamentals – SQL Database for Beginners
- Essential SQL Server Development Tips for SQL Developers
- Introduction to Computer Programming for Beginners
- .NET Programming for Beginners – Windows Forms with C#
- Introduction to SQL Server Machine Learning Services
- SQL Server 2019: What’s New – New and Enhanced Features
- Entity Framework: Getting Started – Complete Beginners Guide
- How to Import and Export Data in SQL Server Databases
- Learn How to Install and Start Using SQL Server in 30 Mins
- A Guide on How to Start and Monetize a Successful Blog
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:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.