SQL Server 2008 backup compression... instance level or not?
Published: Apr 07, 2014
Initially I would have said to set this within sp_configure because although it is CPU intensive, I have always found that the cost is more than covered by the level of reward.
However, I have recently discovered something which causes a little more thought before simply applying this at the instance level and not individually to backup scripts or maintenance plans.
The issue here is encrypted databases… if you have them then this feature may not be for you.
An encrypted database, due to the nature of encryption, cannot be compressed. Attempting to do this using any tool will produce negligible results. To see the results of this, refer to the below (these are two identical databases except for encryption):
As you can see, there is little point in compressing an encrypted database.
However, if you have backup compression turned on at the instance level and you don’t explicitly remove it from the encrypted database scripts then SQL Server will still run the encryption process, therefore you will have all overheads of high CPU but without any gain.
As such, if you have a high proportion of encrypted databases I would recommend leaving this disabled within sp_configure and using WITH COMPRESSION in your scripts for other databases. Likewise if you have a high proportion of unencrypted databases, then I would be inclined to change the instance setting and use WITH NO_COMPRESSION in your encrypted database backup scripts.
However, I have recently discovered something which causes a little more thought before simply applying this at the instance level and not individually to backup scripts or maintenance plans.
The issue here is encrypted databases… if you have them then this feature may not be for you.
An encrypted database, due to the nature of encryption, cannot be compressed. Attempting to do this using any tool will produce negligible results. To see the results of this, refer to the below (these are two identical databases except for encryption):
However, if you have backup compression turned on at the instance level and you don’t explicitly remove it from the encrypted database scripts then SQL Server will still run the encryption process, therefore you will have all overheads of high CPU but without any gain.
As such, if you have a high proportion of encrypted databases I would recommend leaving this disabled within sp_configure and using WITH COMPRESSION in your scripts for other databases. Likewise if you have a high proportion of unencrypted databases, then I would be inclined to change the instance setting and use WITH NO_COMPRESSION in your encrypted database backup scripts.