Use native SQL Server compression by default

DanSwitzer2

Is there a way to change the default settings of SQL Backup 10.1.25 so that "Use native SQL Server compression" is enabled by default?

We use TDE on our databases and that's the only way to get good compression on our backups, so I'd like to make it the default so there's less chance that someone incorrectly configures a backup.

I looked through the HKLM\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(local) to see if I saw any registry settings that might control it, but nothing seems like it matches.

Jon_Kirkwood

Hi @DanSwitzer2

 

Thank you for reaching out on the Redgate forums regarding your SQL Backup native SQL Compression

 

The default setting for backups is set to compression level 1 & 'Use Native SQL Server compression' to be disabled. Unfortunately this cannot be changed at an application level.

 

In the GUI, you can modify these settings and save a Template to be used in future backup jobs. 

This will keep the settings you have applied and allow the user to make changes before creating a new backup job.

 

 

 

 If you are using scripting to conduct the backup jobs you will ideally want to in include the SQLCOMPRESSION syntax to force native SQL Server compression. Example of this below:

 

EXECUTE master..sqlbackup '-SQL "BACKUP DATABASE [MyDatabase] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\Backup\<AUTO>.sqb'' WITH MAXTRANSFERSIZE = 65536, CHECKSUM, DISKRETRYINTERVAL = 30, DISKRETRYCOUNT = 10, THREADCOUNT = 3, SQLCOMPRESSION"'

 

DanSwitzer2

@Jon_Kirkwood ,

Thanks for the response!

When you're dealing with very large databases with encryption enabled, it's really critical you only use native SQL Server compression when backing up, so it would be great to see an option to either change the default compression or maybe provide a warning when trying to use Redgate's compression when a database has encryption enabled.

Jon_Kirkwood

Thank you for the response and details are your use case of SQL Backup & the need to force Native SQL Compression.

I have escalated this internally with our development team for their awareness. I have requested any possible workarounds that can help to enable this setting; or submit this as a feature request for consideration in a future version of SQL Backup.

I will endeavour to update this post with any feedback from the development team.

Jon_Kirkwood

A quick update that our developers are looking at options around this functionality.

So I can provide some more feedback to the developers, could you advise your preferred solution from the ideas provided below

* A possible GUI change to allow this default to be modified. It would then need to be toggled accordingly depending on the type of database compression you require.
* Logic changes behind the scenes to have the compression engine switch to native SQL compression when it detects that it is backing up a TDE-enabled database.