New Feature of SQL Server 2008 Transparent Data Encryption

These days data leak prevention is the primary concern for any DBA. Microsoft introduced a new feature in SQL Server 2008 called Transparent Data Encryption which is use to protect the physical files of the database without affecting existing applications. The physical files include the database file (.mdf), the transaction log file (.ldf) and the backup files (.bak). The main purpose of this feature is to prevent unauthorized access to the data by restoring the files to another server. Until the release of SQL Server 2008, there was no method to secure the physical files of SQL server database. 

Advantages of Transparent Data Encryption:

• Implementation of TDE does not require any changes to your existing application. 
• TDE feature is easy to implement and administer.
• Encrypts the Entire Database(MDF files, LDF files, snapshots, tempdb, and backups)
• In this feature, we can easily maintain optimal query execution because until the physical data files & data itself are encrypted, the primary keys and indexes on the data did not get affected.
• It used minimum server resources to encrypt data.
• This process is invisible to the end user.

Disadvantages of Transparent Data Encryption:

• As I have already mentioned that it encrypts the entire database, it is not granular. 
• FILESTREAM data is not encrypted.
• If TDE is enabled for any database on SQL Server instance, then the tempdb database get automatically encrypted. It can be the cause of poor performance of encrypted and non-encrypted databases running on the same instance.
• TDE is available only with SQL Server 2008, Enterprise Edition and so will probably not be available to all installations within your environment.
• The Databases which is encrypted with TDE can’t take the advantage of SQL Server 2008’s new backup compression feature.

Summary:

This article will help you to get an overview about TDE & the advantages, disadvantages of TDE. This topic is very complex; you should only implement this feature after knowing all its advantages & disadvantages and after thorough testing in a test environment.