In this article we look at five key drawbacks associated with Transparent Data Encryption in SQL Server.
One of the most helpful and underused, in-built feature for data protection in SQL Sever is the Transparent Data Encryption (TDE). This feature was introduced in the 2008 edition of SQL Server and has since helped SQL users a lot by protecting their data. TDE allows for protection of data when it is at rest. Meaning you do not have to be worried anymore about any one gaining access to the data and stealing it. The TDE feature encrypts the data you have on your disk, so even if someone gains illegitimate access to your disks, they will not be able to use that data without providing the correct keys.
This method can be used to protect all the SQL files stored on your disk, whether they are in .mdf, .ndf, or .ldf. And the best part about the feature is that SQL Server Engine will take care of both, the encryption as well as the decryption. There are a lot of other features the application gives access to, for protecting your data, but they are mostly for online databases. TDE will provide protection to your databases even when offline, and not in use. However, this method is not full proof, and does have its share of disadvantages.
5 Key Drawbacks of TDE
- In TDE the master database which contains the metadata is not encrypted under TDE
- If one has authorized access to the database, through administrative and other authenticated accounts, they will be allowed to access data without any kind of restrictions. TDE cannot provide protection against authorized users.
- The compression can work the other way round with TDE, you might end up making your databases heavier by using compression, instead of reducing their size.
- The amount of CPU used when dealing with compressed backups, is more than what is used when dealing with non – compressed backups. This happens because compressing encrypted data by database engine requires more power.
- Performing compressed backup while using TDE is more time consuming, as compared to performing a compressed backup without TDE.
Key Points to Keep in Mind
What becomes evidently clear after knowing the drawbacks of TDE is, the feature is better off without compression. Using it along with compression will only make things more difficult to manage. Although the feature also has certain upsides, a user can make use of a third party software, to get similar features and more. However spending on a third party software when you already have an in – built feature, doesn’t sound wise either. But the choice should be based on your requirements. You can get a lot within a SQL Server environment, but you will always have to take into consideration the pros and cons of whatever you choose to use, make sure the pros are always greater than the cons or you might end up creating a bigger trouble for yourself as well as the organization. At times you might be better off by introducing several layers in your business continuity process and investing in a SQL Server repair tool.
Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including Access repair and sql recovery software products. For more information visit www.datanumen.com