Backing up SQL 2016 TDE Database

rickmhall

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

petey

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.

crimdon

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

Grant

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.

crimdon

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.

Grant

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.

DonFerguson

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

crimdon

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?

crimdon

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.

DonFerguson

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.

crimdon

Hi Don,

Thanks for that info. Do you manage to get as good (if not better) compression than using native SQL?

DonFerguson

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.  

josh_smith

We're ditching our 3rd party TDE tool and implementing native TDE currently. With 10.1.9 do we still need to edit the registry?

petey2

Yes, you still need to make the changes in the registry.

TonyW

We recently implemented TDE for all of our SQL Server databases. Afterwards, we began receiving a warning from SQL Backup to use native compression, so we checked that box in the backup job configuration window which disabled Redgate compression and should be using native SQL Server backup compression. We are using SQL Backup version 10.1.24.2187 and SQL Server 2019 (latest or near to the latest CUs). I have read through this post, and I think we have SQL Backup configured as discussed above. A registry change is mentioned in the post; is it still needed? 

For our backup files, our full backups are being compressed, but our transaction log backups are not compressed. Is this expected?

Also, I can't find Redgate SQL Backup documentation with specific instructions on backing up and compressing TDE enabled databases. If there is one, please provide a link. If not, please ask your documentation department to write one. Thanks.

squigley

Our recommendation is to use the SQLCOMPRESSION keyword in place of COMPRESSION = <value between 1 and 4> keyword.  Or if using the wizards to configure a backup, select the option to use native SQL Server Compression.

With the release of V10.1.14 back in November 2021, the previous requirement to add a registry key called ForceTDECompression is no longer required.  Also a new exit code 463 was added  to generate a warning when a TDE enabled database is not backup up using the SQLCOMPRESSION, to highlight to SQL Backup users that they should be using SQLCOMPRESSION and not SQL Backup's compression algorithms.

TonyW

Thank you for your reply. It appears we have our backups configured correctly. I want to ask again a question that was not answered. For our Redgate backup jobs for TDE-enabled databases using native SQL Server compression, our full backups are being compressed, but our transaction log backups are not compressed. Is this expected and why?

squigley

It should apply as long as the SQLCompression keyword is part of the transaction backup job script. That keyword allow the native SQL server compression to apply to the transaction log backup.