T-SQL Tuesday 167: Encryption and Data Protection

This month’s T-SQL Tuesday topic comes from Matthew McGiffen, who asks us to talk about encryption and protecting data in SQL Server. To read the full topic invite, click the T-SQL Tuesday logo to the right.

For this month’s invite, I thought I’d write about Transparent Data Encryption (TDE) and give a reminder about how it relates to tempdb.

What is TDE?

If you’re not familiar with TDE, I’ve blogged a bit about it before over here. To summarize a bit of that post, TDE is used to encrypt data at rest. For example, consider if someone steals your data files or backup files. You want those to be encrypted so that an attacker can’t break into the files and read the contents.

While it used to be exclusive to Enterprise Edition, TDE became available in Standard Edition starting with SQL Server 2019. If you once thought about using TDE but it wasn’t available then, it might be worth thinking about again now.

TDE and tempdb

When you’re setting up TDE, you must configure it for a user database because it can’t be configured for system databases, but there’s an exception. You can’t specifically configure TDE for a system database, but when you setup a user database with TDE, whether you like it or not, tempdb will also be encrypted with TDE.

This makes a lot of sense. If you’re trying to protect your data in the user database, tempdb is ultimately going to come into play and contain some data from the user database as queries are executed.

Where this can be tricky is if you have a server where you’re using TDE for one user database and not intending to use it for any other databases. The database that is not using TDE is still going to feel the effects of tempdb having TDE. Performance issues like queries not performing as quickly as they used to are possible, so keep this in mind.

Like any change, it’s worth testing your scenario as much as you can in a non-prod environment before rolling out to production.

Thanks for reading!