9

I'm in the process of researching TDE implementation on a VLDB and would like to know if anyone has real world experience with enabling encryption on a production server.

What kind of problems (if any) can I expect? My testing shows very little impact in a test environment, but I'm concerned about how long this is going to take to setup in prod. Any feedback is appreciated.

I read somewhere that I'll need to disable Backups and database maintenance during the encryption process. Is this true?

This is very concerning, since my testing took ~4 days to complete the encryption.

Improve this question
3
  • 3
    Note that all traffic going through tempdb becomes encrypted when being written to disk as well, so in addition to whatever workload you test against this database, make sure you concurrently test any and all workloads that may have an impact on tempdb.
    – Aaron Bertrand
    Commented Jun 9, 2016 at 19:48
  • 1
    Thanks Max and Aaron. This db wouldn't choke a whale, it's ~1TB. I think that's still considered large these days. I've actually done the encryption test with a copy of the real db in my test environment and it seemed to do it's thing in the background without impacting performance. I'm hoping that will be the case in prod, but I'm just trying to cover my bases.
    – Brickpack
    Commented Jun 9, 2016 at 21:21
  • @Brickpack - add those details to your question using the edit button.
    – Hannah Vernon ♦
    Commented Jun 10, 2016 at 0:32

1 Answer 1

Reset to default
12

We have recently implemented TDE along with AlwaysON in Production running SQL Server 2014.

In our application, we noticed a slight (1-3%) increase in CPU utilization. Your environment is different, so do a thorough load testing with some realistic PROD datasize.

Takeaways from embracing TDE :

  • Backup compression and TDE does not go hand in hand i.e. You wont be able to leverage backup compression when you enable TDE. This means your VLDB backups will be HUGE !

  • Instant File Initialization is not available when you enable TDE.

  • When restoring the databases from PROD to other environments (e.g. UAT, etc), you need to export the certificate so that the database can be restored.

  • You need to properly keep safe and backup certificates that you use to enable TDE.

  • Enabling TDE on a VLDB takes time. You can use this script to list out the databases, encryption status along with other useful information.

    Remember: TDE will encrypt data at rest (on disk) ONLY. You still have to use encryption (e.g. force encryption) to protect data inflight.

Edit:

SQL server 2016 now supports backup compression on TDE enabled databases

while backing up a TDE-enable database, the compression will kick in ONLY if MAXTRANSFERSIZE is specified in the BACKUP command. Moreover, the value of MAXTRANSFERSIZE must be greater than 65536 (64 KB).

Improve this answer
1
  • Thanks Kin. This is all great info. I'll definitely use the encryption status script.
    – Brickpack
    Commented Jun 9, 2016 at 21:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.