SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in


 SQL Server 2008 brings with is a new set of features involving compression. At first I was assuming this was only backup compression, but as I've been digging in, there is a lot more to it. In fact, there are actually quite a few types of compression. We have: 

  • Backup Compression
  • Row Compression
  • Page Compression
  • Log Compression

The middle two are actually under the heading of Data Compression, and what's even cooler is they follow the pages and rows to memory, so it's a boost there if your data is actually compressible. I haven't done much with log compression, but it's designed to make the transaction logs with mirroring go across the wire quicker.

 On Wed the 5th, the Colorado PASS Code Camp takes place and Igot corralled into speaking. I picked compression, so the last couple weeks I've been reading through Books Online and testing a few things to see how well it works. I've also been getting distracted, so I spent much of this morning and today trying to get the presentation finished. I think I've got it, now I just hope it goes over well.

 A couple interesting notes: first, these features are only in the Developer and Enterprise editions, so I'm not sure how widely they'll be deployed, but I bet they really help those large systems. Second, they're simple to use. Not a lot of settings, not a lot of experimentation to do, just enable or disable them. The data compression options are by table, index, or partition, but not necessarily all of them, so you need to enable it specifically for all areas. I bet people miss out on that a bit.

The last thing that caught my eye is that if you replicate a compressed table, the distribution agent doesn't check for a downlevel client, meaning a 2000 or 2005 client. So the table creation fails. How bad is that?


The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest


No comments.

Leave a Comment

Please register or log in to leave a comment.