I have been looking into an issue concerning a CRM 2011 database growing in size and storing temporary backups was a problem because of disk space on the server.
The database team suddenly realized compression had not been turned and thought this would be a great solution to the problem.
I raised this point to an infrastructure buddy and when he said
Infrastructure Buddy: I think there’s a reason why compression isn’t turned on.
Hosk : What do you mean I said
Infrastructure Buddy: I can’t quite remember but your comment has set off a small alarm, I think it’s something to do with encryption and SQL Server version.
I did some searching on the internet and finally found this MSDN article Transparent Data Encryption (TDE) which goes into detail about encrypting databases in SQL Server 2008 R2.
CRM 2013 has added database encryption and if you want to read more about that here are two good articles
Dynamics CRM Girl – CRM 2013: CRM And SQL Encryption
Sonoma Partners – Data Encryption in CRM 2013
In CRM 2011 the way to encrypt the database was to do it using the Encryption functionality in SQL Server 2008 R2. So if we get back to my question/problem of compression.
Why wasn’t compression turned on?
Reading the article I found this section which seemed to yield some answers
Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.
It seems there is no real benefit to compressing database backups if you have encryption because based on the article SQL Server can’t really compress the encrypted database.
This answer seems to make logical sense to me and it’s better than the initial thought where I was concerned compressing the backups would interfere with the encrypted databases and potentially have an effect on the encrypted database, maybe damaging it.
It explains why compression wasn’t initially turned on and it will explain why turning compression on will not save much space.
Time to add more space to the database server
Hosk's Dynamic Blog 