We help IT Professionals succeed at work.

Any way to compress data from SQL queries

I have a web server that is far (geographically) from the database it uses. I notice when a db query returns many records it takes a long time to send that data to the web server...

Is there a way to implement data compression between the client and sql db?
Can this be done at the TCP level or sql/server level?

web server client is running on Win 2008 Web SP2/64-bit.
sql server 2008 std is running on Win 2008 Std SP2/64-bit
Comment
Watch Question

Refer this
http://www.sql-server-performance.com/articles/dba/Data_Compression_in_SQL_Server_2008_p1.aspx
http://www.mssqltips.com/tip.asp?tip=1582

Open in new window

Almost all web servers have the hability for compression implicit, I use Tomcat and inside of server.xml conf file i can set the value compression="force".

So this is up on your web server not to your app not to your DB.

Remember once the data is zipped or rared, no matter you try to compress again, the size wil be the same, so find how enable compression for your web server.
Commented:
Links below related with data storage format instead of result set compression.

You can create CLR stored procedure which will compress the stream of the data with Deflate/GZip streams and make web service (or client) to decompress it. One of the options - use DataTable in CLR procedure, serialize it to XML via regular DataTable.SaveToXml() method, compress resulting xml and send it to the web server. Next you can decompress it and deserialize it there. Obviously it would lead to additional CPU overhead on SQL and web servers as well as additional coding.

I would also suggest to take a look if your web server really need to have so much data returned in every request.

Author

Commented:
I think the best solution might be to create a site-to-site VPN tunnel between the 2 servers, but I can't seem to find any info on how to do this.
If you create a VPN tunnel, the speed will decrease because data have to be encapsulated and encrypted/decrypted

Author

Commented:
a web servers page compression is not what I need