Printed 2017/08/24 03:18AM


By Steve Jones, 2008/03/03

 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: 

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?


Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.