March 31, 2010 at 9:27 am
Was wondering if anyone is using the new backup compression features in SQL 2008 in production yet?
I've done some testing and have found that both backups and restores are significantly faster with compression turned on, and the level of compression achieved is generally roughly comparable to other compression technologies.
Has anyone been using them long enough to be able to comment on their reliability? I.E. has anyone had issues where the compression caused any corruption in the backup?
Thanks,
Jason
The Redneck DBA
March 31, 2010 at 9:50 am
Yes, used in production, replacing LiteSpeed.
Very happy with it.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 10:49 am
We are using it in production as well. I am quite happy with the native compression. I am also quite happy with the speed with which our backups are running in our new environment. On the x64 blade servers our full backups are running in less than 10 minutes compared to nearly fourty-five minutes on the old x86 servers.
Now, if they could incorporate encryption natively as well, I'd be a really happy camper.
Edit: Of course, if we used TDE as well, that would be taken care of also. Looking at TDE, but not in a position to implement it yet.
March 31, 2010 at 11:02 am
Alright...I'll give it a shot.
I'm just doing simple backups now, and then zipping them. The speed of native compression is really impressive compared to my current process.
I'm fortunate enough to not have to worry about encryption for this server (anything sensitive is already encrypted in the database).
The Redneck DBA
March 31, 2010 at 11:39 am
Lynn Pettis (3/31/2010)
Now, if they could incorporate encryption natively as well, I'd be a really happy camper
Encrypt the compressed backup you mean?
Otherwise, encyption and compression do not play nicely together 😉
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 11:59 am
Paul White NZ (3/31/2010)
Lynn Pettis (3/31/2010)
Now, if they could incorporate encryption natively as well, I'd be a really happy camperEncrypt the compressed backup you mean?
Otherwise, encyption and compression do not play nicely together 😉
People keep saying that, but no one ever really explains why. HyperBac didn't seem to have any problems with encryption and compression when I tested it. I didn't use it as the backups were about 1 GB larger and we were under tight space requirements on the offsite server.
data ---> compression ---> encryption ---> final backup. Why would this have problems? I am really interested.
March 31, 2010 at 12:28 pm
Lynn Pettis (3/31/2010)
People keep saying that, but no one ever really explains why. HyperBac didn't seem to have any problems with encryption and compression when I tested it. I didn't use it as the backups were about 1 GB larger and we were under tight space requirements on the offsite server.data ---> compression ---> encryption ---> final backup. Why would this have problems? I am really interested.
The encryption happens before the compression 🙂
From Backup Compression (SQL Server):
"Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all."
From Understanding Transparent Data Encyption:
"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."
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 12:32 pm
Paul White NZ (3/31/2010)
Lynn Pettis (3/31/2010)
People keep saying that, but no one ever really explains why. HyperBac didn't seem to have any problems with encryption and compression when I tested it. I didn't use it as the backups were about 1 GB larger and we were under tight space requirements on the offsite server.data ---> compression ---> encryption ---> final backup. Why would this have problems? I am really interested.
The encryption happens before the compression 🙂
From Backup Compression (SQL Server):
"Encrypted data compresses significantly less than equivalent unencrypted data. If transparent data encryption is used to encrypt an entire database, compressing backups might not reduce their size by much, if at all."
From Understanding Transparent Data Encyption:
"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."
Okay, I understand that, but why couldn't encryption/compression work together during the backup/restore process, and that is what my little diagram above was depicting.
March 31, 2010 at 12:47 pm
Lynn Pettis (3/31/2010)
Okay, I understand that, but why couldn't encryption/compression work together during the backup/restore process, and that is what my little diagram above was depicting.
They can! Just use normal data compression (ROW/PAGE) together with TDE!
See Data Compression: Strategy, Capacity Planning and Best Practices
For some reason, I thought you were determined to use TDE and backup compression.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 12:52 pm
Paul White NZ (3/31/2010)
Lynn Pettis (3/31/2010)
Okay, I understand that, but why couldn't encryption/compression work together during the backup/restore process, and that is what my little diagram above was depicting.They can! Just use normal data compression (ROW/PAGE) together with TDE!
See Data Compression: Strategy, Capacity Planning and Best Practices
For some reason, I thought you were determined to use TDE and backup compression.
No, no, no. I have looked at it, but in our environment, using TDE may be a little much at this time. Restoring copies of production for development, upgrades, problem resolution would be more difficult and not worth the effort at this time, especially since we just lost one of the DBA team and those of us still here need to pickup the slack. Shouldn't be too hard, but there is a learning curve on things I haven't been very involved in the past 5 years.
March 31, 2010 at 12:54 pm
Paul White NZ (3/31/2010)
Lynn Pettis (3/31/2010)
Okay, I understand that, but why couldn't encryption/compression work together during the backup/restore process, and that is what my little diagram above was depicting.They can! Just use normal data compression (ROW/PAGE) together with TDE!
See Data Compression: Strategy, Capacity Planning and Best Practices
For some reason, I thought you were determined to use TDE and backup compression.
But I also think it would be nice if there was an encryption option when doing backup/restore operations as well. I also think that native compression should be a part of SQL Server 2008 SE, not just EE (DE).
March 31, 2010 at 12:56 pm
Lynn Pettis (3/31/2010)
No, no, no. I have looked at it, but in our environment, using TDE may be a little much at this time. Restoring copies of production for development, upgrades, problem resolution would be more difficult and not worth the effort at this time, especially since we just lost one of the DBA team and those of us still here need to pickup the slack. Shouldn't be too hard, but there is a learning curve on things I haven't been very involved in the past 5 years.
Ok - but it is cool though, right?
You could always use a third-party tool to encrypt your compressed backup as well, I suppose, but again you would have to manage the decryption at the destination.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 1:01 pm
Lynn Pettis (3/31/2010)
But I also think it would be nice if there was an encryption option when doing backup/restore operations as well. I also think that native compression should be a part of SQL Server 2008 SE, not just EE (DE).
Not sure I agree - EE is for large databases, large databases need compression. They have to draw the line somewhere.
Of course it would be cool if SE did everything EE does 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 31, 2010 at 1:01 pm
Paul White NZ (3/31/2010)
Lynn Pettis (3/31/2010)
No, no, no. I have looked at it, but in our environment, using TDE may be a little much at this time. Restoring copies of production for development, upgrades, problem resolution would be more difficult and not worth the effort at this time, especially since we just lost one of the DBA team and those of us still here need to pickup the slack. Shouldn't be too hard, but there is a learning curve on things I haven't been very involved in the past 5 years.Ok - but it is cool though, right?
You could always use a third-party tool to encrypt your compressed backup as well, I suppose, but again you would have to manage the decryption at the destination.
HyperBac was good at that, but we are looking at cost cutting, and where we can we are cutting back. Since we are running SQL Server 2008 Enterprise Edition for our production servers (we get an asume deal being a school district) we are going to use what is free (read as "provided as part of the product") where possible.
March 31, 2010 at 2:47 pm
fyi, compression is allowed in 2008R2 Standard Edition.
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply