Sql-server – Monitor SQL TCP traffic on the client

The out-of-the-box solution for this in SQL Server is Peer-to-Peer Transactional Replication. This requires Enterprise edition at all nodes.

If your application truly needs this architecture, I would strongly recommend using the built-in tools rather than trying to patch something together using another technology. Peer-to-Peer replication was designed for this exact scenario.

Having said that, I would recommend that you make sure this architecture is actually necessary, as opposed to having one centralized database. I'm not saying this is the case in your scenario, but sometimes the people in suits can try to dictate a solution like you're asking for, without fully understanding the technical complexity. Setting up and managing this type of replication can be challenging.

As I understand it, you want to monitor network traffic in between a web application hosted in IIS and SQL Server, both of which are running on the same server. This is going to be complicated by a few things:

• Most packet sniffers don't support monitoring traffic over a loopback interface like you are using if your web app connects to SQL Server on localhost or 127.0.0.1. Microsoft's Message Analyzer is an exception, but it is beta, doesn't appear to have much in the way of visualisation or analysis tools, and doesn't appear to be exportable to a format that network sniffers with decent throughput analysis tools can read.

• The traffic won't be analysable if you're using shared memory or local named pipes instead of TCP to connect to SQL Server.

• Wireshark also doesn't know what process IDs the packets were sent or received from, which could be a problem depending on your filtering requirements.

Wireshark has good visualisation and analysis tools compared to other network sniffers, though, including throughput graphs and statistics on conversations. What I've done in the past where I needed to filter on process ID was to capture with nmcap or Network Monitor, and then import to Wireshark to do the analysis. None of these will work if you're connecting over the loopback, though, or connecting via local named pipes or shared memory.

I agree with the advice to just get a 1 gbps switch if possible, even if you don't need it today. If you don't need it now, you might later, and it's easier to upgrade in a planned manner while your app is performing well rather than in an emergency where the network is saturated.

A few times a year I encounter a web app that is managing to saturate a 100mbps link to a database server, so I don't think this is an outlandish scenario. Many of the applications I support are not well designed, though, so this may never be a problem for you if you're using common-sense design principles like not retrieving giant results on every page view.