3

I'll be going onsite to run some performance tests for a client. They have a Windows Failover Cluster, which has a clustered instance of SQL Server. Let's assume for this post that my client's Windows Failover Cluster has two nodes, as in the image below.

  1. I'll be running a server-side trace (via a tsql script generated from SQL Profiler)--I'm quite comfortable doing this. After I connect to EkTechready14.Redmond.com\MySqlInstance via SSMS and start the trace, I am of the opinion that it doesn't matter which node is active when the trace is running. Do you agree?

  2. I'll also be running Perfmon to gather some garden variety stats. Maybe for Perfmon, it does matter which node is active. Should I run Perfmon from the client access point (EKTechready14.Redmond.com)? Or from the active node (either MSDEPLOY60-01 or MSDEPLOY60-02)?

enter image description here

Improve this question
1
  • Can't remember where i heard it (@BrentO maybe) that profiler triggered a failover. So go with server-side trace as answered below to be on the safe side.
    – Raadee
    Sep 29, 2014 at 12:53

1 Answer 1

Reset to default
3

Use a server-side trace, not Profiler. Both have an impact on throughput, Profiler much more so. ClearTrace is a great tool for offline analysis of the trace files.

To answer question 1), you connect to the instance not the node. Question 2), you obviously need to gather data from the node the instance is currently running on.

Improve this answer
7
  • 1. A server-side trace is a given. But since I didn't mention it, I'm glad you did. 2. Running Perfmon on the inactive mode would clearly seem to be a waste of time. However, what's the difference between running Perfmon on the cluster vs the active node? (I've reworded the original post above.)
    – Dave Mason
    Sep 29, 2014 at 13:20
  • @DMason Normally when I run perfmon I collect remotely from a different machine (my admin server). While I have not seen a performance impact either way it is much more manageable for me to monitor all my servers from one location. This also would let you set up your collection on both servers so if the instance failed over you would not be monitoring the inactive node.
    – RubberChickenLeader
    Sep 29, 2014 at 14:03
  • @DMason Not clear what you mean by "cluster vs the active node".
    – Mark Storey-Smith
    Sep 29, 2014 at 17:05
  • Maybe I used the wrong wording...The "client access point" is what I meant by "cluster". It's the "server" that people on the LAN connect to (as opposed to connecting to node 1 or node 2). Presumably, the client access point has its own IP address and DNS name. So...if I have my verbiage right (and my understanding of Windows Failover Cluster, what server should I target with Perfmon: the client access point? Or the active node?
    – Dave Mason
    Sep 29, 2014 at 22:36
  • @DMason Clients connect to the virtual network name of a SQL Server instance. To gather useful Perfmon statistics you need to monitor the node the SQL Server instance is active on.
    – Mark Storey-Smith
    Sep 29, 2014 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.