Backing up SQL 2016 TDE Database

rickmhallrickmhall Posts: 2
edited December 23, 2016 5:57AM in SQL Backup
Does SQL Backup 8 support compressed backups when backing up SQL 2016 TDE databases? When I change the MAXTRANSFERSIZE to anything greater than 64K using Microsoft SQL Backup then I get about 78% compression of the TDE database. I am not seeing any compression from Redgate and have tried numerous MAXTRANSFERSIZEs.

Thank you,
Rick
Tagged:

Comments

  • peteypetey Posts: 2,358 New member
    Encrypted data don't compress very well. Try taking a native SQL Server backup of your TDE database, then compress the backup file using Winzip or another compression tool. SQL Backup's compression level ought to be near those levels.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • crimdoncrimdon Posts: 53 Bronze 2
    Is this going to be improved upon? We're currently thinking about using TDE but would have to ditch Redgate as a backup solution as it stands
  • The problem is that TDE encrypts the data at rest. We can only read what's there and what's there is encrypted. Compressing encrypted files just doesn't work. In fact, I've seen the compression increase the size of files. We would have to be able to read the unencrypted data, which, with TDE, isn't going to happen.
  • crimdoncrimdon Posts: 53 Bronze 2
    I appreciate that and have done some testing myself and seem similar results. However, with 2016, Microsoft seem to have solved that issue. I thought the Redgate back program used the VDI so should get the same compression levels as using a BACKUP DATABASE command.
  • The principal difference is that Microsoft can read the structure below the encryption and then back that up through compression and encryption. It's a difference between access to the underlying code and no access to the underlying code.
  • DonFergusonDonFerguson San Diego, CAPosts: 111 Silver 1
    According to the latest release notes, TDE backup compression support was added with version 9.1.4.  However I am running on version 9.2.7 and TDE backup compression on a SQL Server with 2016 SP2 CU2 is not working.  For a sanity check, I was able to get TDE backup compression to work with native backup.  Is this a bug?  Should I test TDE backup compression on an earlier release of SQL Backup?

    Version 9.1.4 August 31st, 2017

    This release of SQL Backup  includes the following enhancements and bug fixes:

    Fixes

    • SB-5792 Support compression of TDE-enabled databases
    • SB-5808 Allow logging of data files.
    • SB-5810 Improved deleting of old backup files when using ERASEFILES syntax
    • Support for partial and partial differential backups in Restore Wizard in the GUI
    • Improved error reporting in the Restore Wizard
    • Update to UsageClient - can now cross NTLM secured proxies, and fix potential delays on shutdown
  • crimdoncrimdon Posts: 53 Bronze 2
    I keep looking at this every now and again. The bug I was told by Redgate technical support was with SQL Server rather than SQL Backup. I was wondering if anyone knows if its been fixed yet?
  • crimdoncrimdon Posts: 53 Bronze 2
    Latest test for me with SQL Backup v10 (latest patch) and SQL Server 2016 (latest patch).

    The database is 30Gb with TDE applied to it.

    Clearly SQL Backup can't be used with a TDE database if you want to also achieve compression on the backups.


  • DonFergusonDonFerguson San Diego, CAPosts: 111 Silver 1
    I had to open a ticket with support and was finally able to get it to work, but there are a few hoops.

    They have fixed TDE to work using the SQLCOMPRESSION option and the Microsoft SQL Bug was fixed with a SP and CU some time ago.  Please make sure you have a fairly recent SP/CU as you could have issues if you are using a version of SQL 2016 that doesn't have the fix, sorry I don't know the exact time it was fixed but you could look it up or just make sure your SQL Server patching is sufficiently up to date. 

    You also need to add an undocumented key to the registry: REG_DWORD to HKLM\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(nameofinstance) called FORCETDECOMPRESSION with a value of 1.  If you have further questions check with Red Gate support.



  • crimdoncrimdon Posts: 53 Bronze 2
    Hi Don,

    Thanks for that info. Do you manage to get as good (if not better) compression than using native SQL?
  • DonFergusonDonFerguson San Diego, CAPosts: 111 Silver 1
    It's basically the same as it's using native compression.  The main advantage is that it works in scope using the familiar SQL Backup interface.  
Sign In or Register to comment.