locked
TDE Encryption scan - how to speed up RRS feed

  • Question

  • Hello 
    we have a 5TB SQL Server 2014 (SP1-CU4) (KB3106660) - 12.0.4436.0 (X64) Enterprise edition running on 
    Azure VM IaaS. We want to enable TDE on this db and our initial estimates say we will need 20+
    hours for the TDE process to complete.

    As i understand, when we run ALTER database db Set encryption on, Sql server transparently starts 
    encrypting the files. It does the encryption scan via a background process.

    We have a very short time window to enable encryption and so we were looking for some guidance
    on what we can do for speeding up the process. The application team is willing to not run any
    application related processing on the DB during the encryption run and so we have the server fully 
    available for the TDE process. Our IaaS VM is 16 processor/112 GB.

    what can we do to speed up TDE scan?

    Please advise.

    thks
    MS
    Tuesday, May 31, 2016 1:28 AM

Answers

  • Hi MS_Prog,

    Firstly, I tested that on Windows Server 2012 R2 Datacenter with SQL Server 2014 Developer Edition and this works well for me. You can see the change by adding ‘Base priority’ column to Details tab in Task Manager. However, it not best practice to leave this on. With this setting, the system tends to give more resource to SQL Server, this behavior may slow down other processes in the computer.

    Secondly, this option has been marked ‘to be deprecated ’ for a long time and it still exists in SQL Server 2016 RC3.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Tuesday, May 31, 2016 5:36 AM
  • An obvious way to speed things up is to bump the VM to P11 or whatever the biggested and meanest edition is. Of course, it will show on the bill that you did. Don't forget to scale down when you are done!

    Tuesday, May 31, 2016 9:10 PM

All replies

  • Hi MS_Prog,

    I’m sorry to say that but you don’t have much control over TDE encryption process, database engine itself will automatically allocate system resource it needs for encryption. Also, the database will still be accessible during the encryption process.

    In this case, my suggestion is turn on ‘Boost SQL Server Priority’ by right-clicking on your instance in SQL Server Management Studio and selecting ‘Properties’, then going to Processors tab. You can also start the process during off-peak time so there will be more resource available, that should shorten the process duration as well.

    If you have any other questions, please let me know.

    Regards,
    Lin
    Tuesday, May 31, 2016 3:14 AM
  • Hi Lin,

    thanks for your input. Looking at the link for 'Boost Sql Server priority', is this applicable only for Windows 2008 R2? 

    Ours is windows 2012 R2 datacentre.  Also, this feature seems to be deprecated?

    --MS

    Tuesday, May 31, 2016 4:05 AM
  • Hi MS_Prog,

    Firstly, I tested that on Windows Server 2012 R2 Datacenter with SQL Server 2014 Developer Edition and this works well for me. You can see the change by adding ‘Base priority’ column to Details tab in Task Manager. However, it not best practice to leave this on. With this setting, the system tends to give more resource to SQL Server, this behavior may slow down other processes in the computer.

    Secondly, this option has been marked ‘to be deprecated ’ for a long time and it still exists in SQL Server 2016 RC3.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Tuesday, May 31, 2016 5:36 AM
  • An obvious way to speed things up is to bump the VM to P11 or whatever the biggested and meanest edition is. Of course, it will show on the bill that you did. Don't forget to scale down when you are done!

    Tuesday, May 31, 2016 9:10 PM