How to I properly measure the network performance of SQL Server across a WAN?

Question

We're in the process of migrating a number of servers to a different data center. As a stop gap measure, we've moved a set of web servers to the new data center and have left their supporting SQL Servers in our existing data center. The two are connected across a WAN link via VPN.

How do I properly measure the impact of this move on network performance and utilization?

My end goal is two fold:

• To assess the impact on end-user performance of the websites
• Determine if we need to spend more on a larger pipe

Answer 1

Sorry, if you weren't doing it before, you have absolutely no way of knowing the impact after. You would only have the "after" numbers.

That said, you should measure it from the front-end using a tool that acts like a user, logging into the site and measure the time to complete specific operations. That's the approach that most "performance monitoring" suites take. Or you could have your web application keep its own log of the time that it takes to perform SQL queries and transactions, but you'd have to write that into it and keep the data somewhere.

Also - the typical problem with a WAN link between app and DB is latency, not available bandwidth, so buying a bigger pipe might get you absolutely nothing except a bigger bill. But that's really easy to figure out now - run NTOP or NetFlow or something on your WAN device (or even your web servers) and see how much bandwidth is being used by the SQL protocol.

Answer 2

What is your aim here? Are you trying to measure speed to compare different links / sites? Or are you studying whether or not something performs adequately for your needs?

If the former, then as mfinni says, if you don't have the "before" figures then you won't have anything to compare the "after" figures with. Simple as that.

If the latter, then simply create a fair 'Service Level Agreement' for performance and availability based on what users need to the changes, and just measure further performance and availability against the SLA.