Compressing TCP in SQL Server 2008 [closed]

As it currently stands, this question is not a good fit for our Q&A format. We expect answers to be supported by facts, references, or expertise, but this question will likely solicit debate, arguments, polling, or extended discussion. If you feel that this question can be improved and possibly reopened, visit the help center for guidance. Closed 11 years ago. 开发者_StackOverflow中文版

We have .NET 4.0 application using Entity Framework. Application connects remotely over TCP to the SQL Server. When on LAN, it's fast, but over internet the traffic is very high.

All we would like to do is to turn on some TCP compression but it looks like that SQL Server 2008 doesn't provide this feature.

What's the simplest solution to make TCP communication compressed?

---

You are trying to solve the problem on the wrong level / layer. Profile your communication with SQL server and start to think about optimizations. That is the only valid point to start. Incorrect usage of EF can lead in terrible chatty and slow communication with SQL server and that is something that will simply not be solved by any compression because chatty sequential communication means multiple unnecessary roundtrips to database where each single roundtrip will increase the duration of processing by its latency. I have already seen solutions (and some of them I did myself) where incorrect usage of EF created thousands of lazy loading queries within single request processing.

And yes it can end up in replacing part of your EF code with stored procedures and custom queries and in the worst case with abandoning whole EF.

If your problem is amount of transferred data it is again time to think about optimization and reducing amount of transferred data to only needed subset or perhaps using some preprocessing on SQL server in stored procedure or view. Btw. such thinking should be done during application design where you should think about target environment where the application will run.

Edit:

One more note. It is not very common to communicate with database over WAN. Usually such requirement leads to implementing another tier with business logic sitting on the server in LAN with SQL server. This business logic tier exposes web services to client on WAN. This architecture can reduce latency when communicating with database a lot and in the same time a correct architecture of message exchanging between client and service can lead to additional improvements.

---

SQL Server uses the TDS protocal.

It doesn't care whether you use TCP or Named Pipes or "the force" to get data from A to B

You'd need to enable or set up some kind of compression (Google search):

• at the OS level
• the interface/hardware level
• the network level