SQL Server 2008 (SSQA.net)

As you know data compression facility is added onto the SQL Server 2008 version, though this is not a

As you know data compression facility is added onto the SQL Server 2008 version, though this is not a

Oh, compression is that kind of technology that a DBA could sometimes call magic. And I really enjoy using SQL server 2008 in my testlab environment. But I'd say it even more that I can implement backup/restore compression right in SQL 2005. It can be surprising but an overwhelming number of my fiends and colleagues don't have any clue that it's possible to save both the space and operation completion time by implementing compression right using SQL 9.  SQL server 2008 supports on-the-fly row-level compression when it compresses the rows as you write them to the database and decompresses back as you retrieve the data. That's really a great feature and has its value but as a person who administers several SQL servers I'd like to note that probably the first thing that an administrator would start thinking if you ask him about his desire to implement some technology in his environment would be data protection. Data protection is considered to be of two types. You are protected if you have implemented a procedure that allows you roll back changes or quickly restore data from every point in your environment. But you are twice as protected if you've implemented encryption in addition to backup procedure. If you don't backup data you are playing with fire. But if you have implemented backup you still haven't solved the tasks because you should also be thinking about taking this in a safe place and making the procedure reliable. A reliable procedure is a procedure that allows you to be able to implement restore seamlessly to your environment. That is you have to worry about redundancy. When you get a redundancy you immediately have task to implement compression. While the cost of disk space goes down the amount of data rises up and it doesn’t mean that having cheap SATA backup arrays you freed yourself from a space problem. Here's where backup compression plays a great value. However, some of us still continues to implement old schemes of data backup that do not work anymore with that amount of data that we host on our SQL servers today. I remember we used something like scripts implementing zip compression over the backup data. That seemed to be reliable those days. Today when I can implement automatic backup, compression and encryption for every database on all instances on my SQL server farm with tools like Scriptlogic's Litespeed http://scriptlogic.com/products/litespeed/ that I use for my data protection procedure I can only smile on how naive I was those days when I used those scripts. As you've said I/O operations goes hi but don't forget the less size you process the lesser you have to read and the faster your I/O operations are proceeded. I'd say that I don't see any dramatic increase in memory usage and this could be because of the compression techniques implemented in Litespeed (or SQL that Litespeed uses). CPU load rises dramatically if you count a relative rise. But if you take absolute percentage and count how many resources are still free when you compress the data you'd call compression CPU load just an approach to use your CPU power effectively. If the most of CPU power goes to a Idle process it's doubted an effective process. Another thing is how quickly you can implement this. If you compress the data pretty quick and transfer it even more quicker but spend a 70-100% more on implementing this it's again not effective. That's why I'd say that data management matters here. With Litespeed I can control backup right from a special snap-in from a remote place so that I don’t  have to worry about how do I script it to run on the DB server and then upload the backed data to a file server because I can build a plan and distribute backup load through the servers selecting different techniques and tuning compression parameters for every single backup job. Then it will be automatically uploaded to a remote storage. If I wish I can then request information about how many backups  I had, which of them failed and so on. The same way I like the easy of performing SQL operations and queries with SQL management studio the same way I like the procedure of making backups with Scriptlogic's Litespeed. Take encryption. That’s so easy to select a needed algorithm and make the backup secure. Then I even can put it on a DVD and send it with a courier so that I am sure nobody would be able to restore it without knowing the password. Having such crypto secure algorithms as AES-256 I can sleep soundly as I am sure my databases will be automatically compressed, encrypted and backed up to a remote location on a nightly basis. Thus you can fine-tune your compression and encryption setting and find that solution that works best with every particular database.