Compress data from Database

Question

Quick q, could be a silly one given my (lack of) findings on Google so far.

I have a Database. In this database is a Table with some Data. The Data is a large BLOB but can't be compressed (for reasons out of my control).

I have an Application that talks to this Database. I would really like to be able to ensure that the Data is compressed during transit.

As I understand it, the Database Provider would handle compression etc.

Is this the case? Are there settings on common ones, say SQL Server to enable compression?

Answer 1

For SQL Server, I found this "connect" entry, but no: I don't think TDS is currently compressed. You could (although I don't like it much) use SQL-CLR to compress it in .NET code, but it could have too much overhead.

I know it isn't an option in this case (from the question), but it is usually preferable to store BLOBs the way you want to get them. So if you want to get them compressed, store them compressed. SQL isn't a good tool for manipulating binary ;-p Such a strategy also means that you aren't using vendor-specific features - just the ability to store an opaque BLOB.

Answer 2

SQL Server 2008 is the first version of SQL Server to natively support compression of backups. Pre 2008, you need to do it with third party products.

Answer 3

If your database access layer does not provide compression, you can set up a VPN link between the database server and the application host. Most serious VPN solutions compress data in transit. OpenVPN is a simple and easy to set up solution for quickly creating a tunnel. Data is compressed in transit. Probably won't be as efficient as a native compression, but it's a possible solution. And you get encryption thrown in for free :).