Column level compression in SQL Server

Question

I have a column that I would like to store a lot of text data in (XML data). Approx 8,000 chars per row, and about 100-500 rows per minute.

That much data means I will have to purge the column out fairly aggressively. (Since I have to host my SQL Server on our company's SAN, storage space is quite expensive.) But if I can find a way to compress this data down, I can keep it around longer.

I have seen things like this article on using CLR Integration to compress BLOBs in SQL 2005.

I have also seen the tool SQLCompress.NET for SQL Server 2005.

Both seem to say they do exactly what I want. Compress the data of a single column while it is stored.

However, the tool has been abandoned (no updates since 2008) and I don't know much about CLR Integration, except I have heard that it can cause problems. Also, both of these solutions are for SQL Server 2005.

So, here is my question. I am using SQL Server 2008 R2. Will either of these SQL Server 2005 solutions work well for me?

Or is there another solution that I can use to compress my data?

NOTE: Row Compression and Page Compression will not help with what I need (At least as far I can can see.) Row Compression is storing fixed length data in variable length fields and page compression reduces instances of redundant data. Neither of these will help with large blocks of text.

NOTE II: I saw this question, but its answer uses row and page compression or FILESTREAMs. I don't want to use FILESTREAMs because I loose the ability to mirror my database.

Answer 1

I think that using your best bet is either to use a client library to compress and decompress the data before you put it into SQL Server, and if you want to query by particular elements or attributes of the XML, you can extract those and store them in separate columns or normalized rows (which you'd want to do anyway- querying large XML text columns, especially for nested elements, is slow).