1

Can anyone please help me understand below case:

Kindly advise on below case:

SQL Server is on 2014 SP2 GDR version

I read Compression and TDE does not go hand in hand .

I have 2 PRD Environments:

The table structures between server 1 and Server 2 are same.

  • With DB Size around 400 GB in Server 1 with FileStream Enabled. FileStream content sized around 300 GB.
  • With DB Size around 687 GB in Server 2 with FileStream Enabled. FileStream content sized around 600 GB

Currently the above Databases are with TDE Enabled.

When I backed up below are the details when they are NOT on TDE:

With DB Size around 400 GB in Server 1 took around 51 Minutes

With DB Size around 687 GB in Server 2 took around 108 Minutes

When I backed up below are the details when they are WITH TDE:

With DB Size around 400 GB in Server 1 took around 57 Minutes

With DB Size around 687 GB in Server 2 took around 207 Minutes

My Observations

In Server 1 with same specifications as Server 2 , time taken is 51 Minutes (Pre TDE) --> 57 Minutes after TDE Enabled

In Server 2 with same specifications as Server 1 , time taken is 108 Minutes (Pre TDE )--> 207 Minutes after TDE Enabled

My Question Why is that difference when compared to time taken is almost doubled in Server 2 but Server 1 it is only 7 Minutes .

NOTE: Also , my Databases are with TDE + EFS (Encrypting File System) when backed up.

Kindly advise , as Compression and TDE does not go hand in hand . What is the reason why in Server 2 the time taken is DOUBLE , where as in Server 1 it is only 7 Minutes after TDE enabled.

Regards

  • Hi @JohnEisbrener , thanks for your response . Please see I updated my question. Thanks again – DBOn Apr 17 at 15:03
  • What Service pack/CU you are running on 2014 ? Also, the timings are based on local backups or network backups ? – Kin Apr 17 at 15:13
  • @Kin , currently I am on SQL Server 2014 SP2 GDR . Also , can you please help me if applying EFS increases Database timing and size ? Updated my question. – DBOn Apr 17 at 15:32
  • 1
    what I meant is that EFS is external to sql server. SQL server has no knowledge of how EFS does encryption. Can you exclude sql server related files mdf, ldf, trn, bak etc from EFS and see if that improves the timings ? – Kin Apr 17 at 16:07
  • 1
    there you have your answer. – Kin Apr 17 at 17:25
1

Based on the comments so far -

SQL server has no knowledge of how EFS does encryption. Turning off EFS - the timing is reduced almost similar to pre-TDE timing (confirmed by OP).

This means that EFS is slowing down the backups.

Remember that both EFS and TDE will protect data at rest. So, for sql servers, you should have a global policy to exclude sql server filesystem from EFS.

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.