0

I have 2 servers : a database server (SQL Server 2008, called server A) and an application server (called server B) which send SQL queries to the first one.

They are actually in the same LAN, but we planned to separate them in 2 different geographical sites. I try to estimate the minimum bandwidth required between the 2 servers, so that my application doesn't suffer from performance issues. How can I estimate the amount data that are exchanged between the two servers ?

I'd like to have, for example : for day xx/xx/xxxx, there was approximately 1.5 GB of data send from A to B and 750 MB from B to A.

Is there a diagnostic tool on SQL Server to measure that ? Or just a network tool ?

I have no access to any physical hardware device, but I'm admin of both servers. Thanks.

JYL
  • 238
  • 2
  • 7
  • Why are you putting the app and SQL servers in different locations? You usually want to keep them close, for this sort of reason. You might want to leave them where they are and add a light-weight third tier, like a web app that talks to the existing app server, or a new TS/Citrix server near the existing servers. – mfinni Jan 20 '11 at 17:48
  • You're doing it wrong. – hobodave Jan 20 '11 at 17:53
  • @hobodave - me, or JYL? – mfinni Jan 20 '11 at 17:58
  • @mfinni: The OP. Comments by default notify only the OP. In the absence of an @mfinni, assume I'm not directing it to you. – hobodave Jan 20 '11 at 18:03
  • @hobodave - Just checking :-) – mfinni Jan 20 '11 at 18:12
  • @all : I totally agree with you. Separate them is a REALLY bad decision. This decision is a decision from my managers. I'm trying to prove them that they're wrong. But I need metrics for that ! – JYL Jan 21 '11 at 09:14
  • JYL - the biggest problem isn't necessarily bandwidth, although it might be the second-biggest problem. Lag, and how your application handles it, will probably be the biggest problem. Ask your developers if they think the app can handle WAN-introduced lag. Or if it's a 3rd-party product, ask them if they support putting the SQL server across a link with average lag of however-may milliseconds. Then go to management with that info. – mfinni Jan 21 '11 at 20:31
  • You probably speak about latency... you're right. I finally think that the best and fastest prof will be a real test on site... – JYL Jan 24 '11 at 20:48
  • @mfinni and all: thank you for taking time to answer and guide me ! – JYL Jan 24 '11 at 21:05

3 Answers3

2

If you don't have access to the switch, you can use a network analysis tool like Microsoft Netmon or Wireshark. There are probably easier tools to just measure data throughput, but since I am comfortable with Wireshark I usally use it. I would set up a capture on the relevant network interface using this capture filter on the SQL Server:

host 10.10.10.10 and port 1433

Replace ip adress of server B's actual ip adress, change the port if it is not a default instance, uncheck 'promiscuous mode' and start the capture, and then view the size captured in Statistics->Summary.

I agree with the other posters that it might not be a good idea to split the servers, depending on the architecture of the application. A slight increase in response time between the servers could degrade the performance a lot, especially if there are many sequential SQL queries. That is also something you can observe in your Wireshark capture (but might be easier in SQL Profiler).

Michael Eklöf
  • 519
  • 4
  • 6
  • Thanks for your post. Wireshark usually keep in memory the captured packets. Am I wrong ? Is there an option to prevent this and just store stats ? Otherwise it will probably exlode just 10 minutes after it started... – JYL Jan 21 '11 at 09:19
  • @JYL Yes, by default it captures to memory, but if I do a longer capture of several gigabytes I set it to capture to files of a few hundred megabytes each in the 'multiple files'-dialog, I have never tried importing files of more than around 1 GB. If you expect that your application consumes more than a few GB a day I would probably find another tool. – Michael Eklöf Jan 21 '11 at 10:06
  • The network card status shows an average of 14GB sent in a day... perhaps too much for Wireshark. – JYL Jan 24 '11 at 20:52
  • Seems to be the nearest answer for the question. Measure on a short delay (2 hours for ex.) can give a idea of the total amount of the day... Thanks. – JYL Jan 24 '11 at 21:02
  • FYI : Wireshark has been running for 1 hour, generating a 2.5 GB file. Loading the captured file crashed after 20 min, I had a "Visual C++ Runtime Error"... I split it with "editcap" and after I was able to analyze some small periods of 10 min. – JYL Jan 25 '11 at 14:52
1

You would want to enable netflow on the switchports the two servers are connected to and analyze that flow.

SpacemanSpiff
  • 8,753
  • 1
  • 24
  • 35
  • Ok. Thanks for your post. I don't know netflow. Can you confirm that I don't need access on any network physical device ? (like switch...) – JYL Jan 21 '11 at 09:23
  • No, you most definitely need access to the network devices to enable netflow, and you need a server running the appropriate software to act as a collector. – mfinni Jan 21 '11 at 20:32
0

You could run Perfmon and capture bytes sent/received/sec; you'd have to capture to a file to aggregate the data, but it will show you your peaks. It will include all traffic, not just SQL.

SqlACID
  • 2,176
  • 18
  • 18
  • And it would show all traffic, not only that between these two hosts, right? If so, not a good answer for the question. – mfinni Jan 20 '11 at 17:59
  • Thanks for your post, all traffic will be Ok. But won't perfmon explode if I keep it running for hours ? – JYL Jan 21 '11 at 09:25
  • Didn't found the appropriate counters... – JYL Jan 21 '11 at 14:37
  • Should be under Performance Object: Network Interface, then make sure you select the right instance, there will be one for each nic. – SqlACID Jan 21 '11 at 17:51
  • like I said, this will show *all* traffic on those NICs, between all hosts. If the SQL server get a big update via WSUS ,that will show up in these graphs. This methodology will not show what @JYL is asking for, and if management knows that, they will demolish what might be a good argument. – mfinni Jan 21 '11 at 20:34