1

This is the first time we are applying TDE on production databases that are critical and I have the questions below.

While TDE is in progress:

  1. Can we still access the application that is working on this database with a connection string?
  2. If TDE fails while it is encrypting the database, will it break the .mdf file? How would I recover the database?
  3. Are the .ldf and .mdf still accessible, can we read and write to these files?
  4. What are the database operations that will be affected while the TDE is in progress?
  5. Will TDE have any effect on disk usage?
  6. Will TDE have any effect on database backups? Are both the .bak, .trn files encrypted automatically if TDE is enabled?
  7. What are the areas that we need to concentrate on while TDE is being applied? Is an outage needed?
  8. Will there be any effect on SQL Server service packs or cumulative updates?

The platform of relevance is SQL Server 2014 SP2 GDR with availability groups.

  • 3
    What work have you done so far? This is a lot of questions - you must have looked some of this up already? – George.Palacios Feb 15 at 13:07
  • Yes I did test things on Development but would like cross verify things who already done this . As I want to deal with PROD little worried and want to extra cautious. – DBOn Feb 15 at 13:15
2
  1. Can we still access application that is working on this database with a connection string?

Yes. TDE is implemented only at the storage layer. Data is decrypted as it is read from disk. Accordingly, nothing beyond the storage layer is affected.

  1. If TDE fails while it is encrypting data, will the encryption fail and break the .mdf file. How would I recover the database?

I would highly recommend getting a full backup prior to encrypting just to be safe. However, it is highly unlikely that a problem will occur that will corrupt the database or leave it unusable.

  1. Are the .ldf and .mdf, still accessible and we can read and write this file's?

As mentioned previously, the encryption/decryption is done at the storage layer, and the system uses the .mdf and .ldf files in the same way that it uses them for unencrypted databases. It simply encrypts data before writing, and decrypts data after reading.

  1. What are the database operations that will be affected while the TDE is in progress?

The system will use significant CPU and disk I/O as it has to read, encrypt, and write every data page in the database.

  1. Will TDE have affect on disk usage?

No, the size of the database will stay the same. If you're using compression or other technologies it will continue to work with it at TDE is done only when reading and writing pages to disk.

  1. Will TDE have any impact on database backups? Are both the .bak and .trn files encrypted automatically if TDE is enabled?

The backups may be somewhat slower depending on a varying number of factors and the backup files will be somewhat larger if you were previously using compressed backups. How much will depend on the type of data and version of SQL Server (latest versions support compression on TDE databases). Yes, the data in the .bak and .trn files will be encrypted.

  1. What are the areas that we need to concentrate on while TDE is being applied? Is an outage needed?

I would recommend concentrating on a nice cup of tea or coffee. While you are enjoying the beverage of your choice, keep an eye on the SQL Server error log. An outage is not required, but due to the resource utilization required to encrypt the database, having a maintenance window is ideal.

  1. Will there be any affect on SQL Server service packs or cumulative updates?

In general, the answer is "no." However, if a defect is found that affects only TDE, then it may be advisable to install the CU that includes the fix. We can't see into the future, but you will install the same CU and service packs whether TDE is used or not. It is simply a feature of the database engine, so all updates to the database engine will include all of the TDE functionality.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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