locked
Using SQL 2017 Server and have a TDE 2012 mode database that will not create a compressed backup file RRS feed

  • Question

  • I am using SQL 2017 Server and I have a TDE database in 2012 mode with multiple backup files that will not create a compressed backup file.  I am using with COMPRESSION.  

    I also have a group of 2017 mode databases in a Maintenance Plan backup job using compression.  Some of the backup files were compressed and some of them were not.

    Is it possible to compress a TDE database in 2012 mode using SQL 2017?  Is there a setting that I am missing.



    • Edited by Sylviarf Thursday, May 21, 2020 11:47 PM
    Thursday, May 21, 2020 11:45 PM

Answers

  • Hi Sylviarf,

    >> Is it possible to compress a TDE database in 2012 mode using SQL 2017?  Is there a setting that I am missing.

    Yes, you can. As Sean says you need to specify MAXTRANSFERSIZE > 64 KB. 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). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536.  If you rely on the default, and skip specifying MAXTRANSFERSIZE explicitly in your BACKUP command, compression will be skipped. Please refer to MS blog Backup Compression for TDE-enabled Databases Or this MS document.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Sylviarf Friday, May 22, 2020 9:16 PM
    • Unmarked as answer by Sylviarf Friday, May 22, 2020 10:40 PM
    • Marked as answer by Sylviarf Saturday, May 23, 2020 12:58 AM
    Friday, May 22, 2020 6:40 AM

All replies

  • Compatibility mode does not change how the engine itself works, so it's not that it's actually running like 2012.

    Regardless, you'll need to specify a MAXTRANSFERSIZE of > 64k in order to have backup compression kick in.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Friday, May 22, 2020 2:34 AM
    Answerer
  • Hi Sylviarf,

    >> Is it possible to compress a TDE database in 2012 mode using SQL 2017?  Is there a setting that I am missing.

    Yes, you can. As Sean says you need to specify MAXTRANSFERSIZE > 64 KB. 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). The minimum value of the MAXTRANSFERSIZE parameter is 65536, and if you specify MAXTRANSFERSIZE = 65536 in the BACKUP command, then compression will not kick in. It must be “greater than” 65536.  If you rely on the default, and skip specifying MAXTRANSFERSIZE explicitly in your BACKUP command, compression will be skipped. Please refer to MS blog Backup Compression for TDE-enabled Databases Or this MS document.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Sylviarf Friday, May 22, 2020 9:16 PM
    • Unmarked as answer by Sylviarf Friday, May 22, 2020 10:40 PM
    • Marked as answer by Sylviarf Saturday, May 23, 2020 12:58 AM
    Friday, May 22, 2020 6:40 AM
  • Hello Cathy and Sean,

    Thank you both. 

    I was having issues because I set my MAXTRANSFERSIZE = 65536 and I had CHECKSUM in the backup statement.

    As soon as I update MAXTRANSFERSIZE = 131072 and removed the CHECKSUM, I was able to compress my backup now.

    Thank you!


    Sylvia Rivera

    Saturday, May 23, 2020 12:59 AM