views:

198

answers:

3

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?

+2  A: 

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.

Marc Gravell
Thanks, unfortunately I can't alter the underlying data or else I would! So in terms of the SQL-CLR solution, I would create a GetMyData sproc which would compress it into a .NET data type?
Duncan
A: 

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.

JohnW
+1  A: 

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 :).

Mihai Limbășan