• Adam Thurgar

Oncology App - TDE


When dealing with any sensitive data you need to do your best to safeguard it. Having decided that we were using Enterprise edition allowed us to use some features not available in Standard edition. One of these was TDE (Transparent Data Encryption). TDE provides you with database security for data at rest. You protect the underlying database files (mdf, ndf), log files and backups. Now TDE is not perfect and people can break the encryption, it just makes it more difficult.

The technical details of implementing TDE is not that hard. But you need to understand the need to manage certificates and keys. So this is an added layer of complexity.We had also implemented Availability Groups so you needed the certificate and key management to be able to restore databases to the secondaries.

The following link is a very good starting point for TDE.

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption

One of the problems that people worry about with TDE is the performance overhead, and yes there is a cost. This is where the applications testing capabilities proved valuable. When TDE was enable CPU usage went up between 3% to 5%. IO operations (both read and write) increased latency of 8% to 10%. So there is a physical cost to your hardware. This will obviously depend on what CPU's you are using, how many, now much RAM you have and what type and speed of storage you have available.


2 views

Recent Posts

See All

Cardinality estimator

Recently I was asked by a software vendor to review a particular query that ran in under a second on a SQL Server 2014 installation at a compatibility level of 110 (SQL Server 2012), but when run unde

Index fragmentation

A law firm client, occasionally has issues with their legal software, that is provided by the global leader in this field. The response from the software provider is always the same - you have index f

Deleting large amounts of data

I had a client call me about wanting to delete a large amount of data from their database. They knew what tables they wanted to delete from. They also knew that deleting large amounts of data causes t