How to encrypt your Team Foundation Server data tier

For all sorts of reasons (including GDPR looming on you) you might feel the need to encrypt your Team Foundation Server databases. Proper encryption at rest.

I realised this is not a really well documented scenario, but it is surprisingly easy to achieve.

What you need to do is leverage SQL Server TDE (Transparent Data Encryption), which is out-of-the-box since SQL Server 2008 onwards. It acts at page level and it is transparent, with little overhead.

The process of enabling TDE is very well documented, and it is based off two keys (the master key and the encryption key) and a certificate. It is very straightforward if you have a single server as a data tier, off you go.

Now, this gets slightly more complicated if you have (like me ) AlwaysOn protecting your High Availability. Well, complicated if it is the first time you approach the topic.

Working with AlwaysOn requires:

• On the Primary Replica - creating the master key, the certificate and the encryption key. Remember to backup the master key and the certificate.
• On the Secondary Replica - creating the master key and the certificate. The certificate must be created from the backup of the Primary Replica!

After these two steps you can enable TDE on the database hosted on the Primary Replica, which then will propagate on the Secondary as per AlwaysOn schedule.

If your databases are already encrypted and you want to add them to an Availability Group you’ll need to do so manually – the wizard is not going to show encrypted databases to be added to the AG.

This SQLServerCentral.com article features a set of queries I found really helpful to get started.

A suggestion though: prepare a single query for the Primary Replica preparation, run it, prepare a single one for the Secondary Replica preparation, run it, and eventually encrypt from a separate query.

The reason why I say this is simple: if anything goes wrong before you encrypt the database you can easily drop the master key, the certificate or the encryption key and start again.

Eventually, remember that encryption for large databases can take a long time. During this time, the process might stop because of database size, so remember to check the logs as well so you can restart it if you need to.