There are a few things a good DBA really can’t live without in his or her arsenal of tools to better manage a wide array of instances. This includes OLAP and OLTP strategies. One of those tools is a good compression tool. I’ve never been one to promote software and kind of hate it when others try to jam it down my throat. I have what I need to make the decision on my own and know how to use Google and read reviews along with statistics provided. That being said I’m going to go off that typically demeanor and talk a bit about Quests LiteSpeed product and how I think it can benefit most DBAs. LiteSpeed is only one of many products out there first off. I tested four different products when I was given the budget and task to implement compression in the company I currently am employed with. I implore you to do the same and don’t just take any DBAs word no matter what the amount of experience they have is or given reputations. You gain experience from taking in others knowledge and putting it to test on your own and making your own “final” decisions. Every installation is different and the calculations you have to take into account for choosing the product that fits you is always going to vary.

At a high level, compression software in itself can give you a vast amount of options that you simply may not be able to grasp without.

  1. Onsite retention of backups
  2. Log shipping abilities that otherwise can flood pipes
  3. Compression options nonexistent in other concepts
  4. Ability to use partially native SQL Server tasks still
  5. Extremely low account of training required
  6. Fast implementation
  7. Very slight interruption to the native internal functionality of SQL Server

The last point is the greatest reason I chose Quest. It’s a mouth full of a statement to say the least. The key factor of #7 is the fact that there is a very small amount of change that goes into SQL Server to achieve the products full potential. With a few other products that I will not name, objects and change was required to the master or msdb databases. Certain features were also required to be turned on that I just was not willing to turn on for security reasons. We work hard to secure data and something like backups should not compromise that.

Onsite retention is to the point it makes you invincible to requests for data. In the growing audit prone level we exist in these days, you must be able to produce data in a quick manner. This of course depends on your disk and data size as far as retention. 500GB of disk with high compression rates will give you a vast amount of space to work with databases in the 100GB ranges though. In the installation I’m currently maintaining, I have been given the ability with compression to withstand request in a timely fashion of a month in the past. That means simply, I have a month’s worth of backups on local disk. This consists of databases well exceeding the 200GB range. In an earlier blog I mentioned the importance of reporting to management of where the database server installations are and the state of the data. This includes reporting on disk savings and data recovery abilities. In the last 2 weeks alone that report has shown a 12.98TB savings. This is an 82% saving over uncompressed backups. Yeah, that is a number that pays for itself. Backup retention alone is the selling point.

In my current installation and DR strategy I am using log shipping also. I have the allowance and business acceptance of the time difference in recovery. In short that means my analysis of time spans between log shipments and a failure is accepted by the business. Log shipping is a good DR strategy for small to mid-size companies. You can’t beat the cost savings but MUST have the business buy in for the distance in data recovery between shipments. Most inexperienced and experienced DBAs will argue that the hard part of a DR strategy on LS is the fail-back strategy. Honestly, that to me is a show of lack of ability to use what you have to manage your data in these events. Fail-back strategy can be as simple as database level triggers on the DR standby servers to initiate the same backup strategy on the DR site that you already have on the primary site. Yes people. If you fail over to a DR site you probably should backup the data over there once it is your production data. You would be surprised how many times this is overlooked and if at that time your DR goes down I wouldn’t want to be in your shoes without backups starting up once a failover. To do this, have your DR site setup to automatically create and start backups using the same tools and landscape from you primary servers. The fail-back strategy is simply to bring the primary instances and databases up to speed and in sync with data with the backups that run on your DR site in the event of a disaster. That is the cheapest and completely sound way to bring things back online after the disaster is situated. It works! And it works very well in my experience.

This all fits in place with log shipping and compression with LiteSpeed by giving you the means of compressing large log backups down to almost nothing and having the ability to send them down the pipe without interruption to the business operations. You could take the compression out of the picture here but then you’re forced to go farther out in time between the backups to allow copy time to the standby servers. That in most cases is not acceptable by the business. When I tested and implemented LiteSpeed for log shipping it was literally a 10 minute setup. This was impressive to me. Even more impressive was the fact it worked the first time around. I did test against other compression options for log shipping and this one was just vastly better than the other installation and configurations required. Again, just my experience of using the product and you have to take it to heart as much as you trust my word and from your own testing.

There really isn’t much more I can say to why getting budget for compression tools in-house is important and can really make you a better DBA. My choice of LiteSpeed was based off my revamped landscape when I was hired to do just that. The tools work and work very well. I’ve been impressed with the staff at Quest and the response I’ve been given when I did have to contact them. They seem to pride themselves on having on hand some of the best people in our field. That alone tells me the level of quality. LiteSpeed has a nice added feature of a log reader tool inside the console as well. That is pretty cool and nice not to have multiple different installations just to have that ability.

So there you have my promo and probably the last one you’ll ever hear me give on a named tool I recommend. I’m hoping you take from this not that you should go out and get LiteSpeed but more that you should be compressing your backups and not relying on offsite tape only. There is nothing more irritating than users over your back while you wait a day to get tape back. Compression tools make you look good and keep the concept of backup/recovery down to a much quicker responsive task. The cost savings is much higher than some think also and it can be an easy sell for management to purchase these tools. After my review of SQL Server 2008 Enterprise with compression features I’m still staying with my configurations, but keep that new 2008 feature in mind as well. Then again if your DB’s fit on a thumb drive I’m guessing this will all be a waste of reading time to you 🙂

Btw…when I say don’t rely on tape, this doesn’t mean you shouldn’t send tape offsite. DB gods help you if you don’t do that 😉

LiteSpeed backup analyzer (also a great feature!)