I am starting to plan a migration of SQL Server 2008 R2 Enterprise Ed. to 2017 (edition TBD). One of the more important tasks assigned to me is to determine the specs for the new VMWare hardware my employer will be purchasing.

To do so, I need gather information about the current workload (on Windows Server 2008 R2 SP1 with SQL failover clustering on 5+ year-old hardware) and translate that to virtualized performance on new hardware - a VMware host which will use SQL Server 2017 and, probably, Availability Groups.

I've set up a PerfMon data collector based off of a blog post by David Klee ( link ) which will start tonight at midnight as a starting point but am still trying to wrap my head around the analysis...

i.e. "How do I take the PerfMon results from the workload running on the old hardware and translate that into requirements for new hardware ?".

Apart from PerfMon what are my other options for right-sizing the new hardware?

  • 3
    I wrote a whitepaper for Google a while back about doing this with cloud VMs, but the process is applicable to any set of new hardware. – Erik Darling Apr 9 '18 at 23:08
  • @sp_BlitzErik - as much as I enjoyed reading your whitepaper the process outlined therein is dependent upon already having a new environment to test in. What I am trying (and probably failing) to convey in my post is a need to calculate something along the lines of old_server = A (Avg. % CPU GHz @ system + internal + external clock speeds AS Overall Used CPU GHz) * B (consumed GB RAM @ _MHz), so new_server requires _GHz CPU and _GB RAM @ _MHz (+ math to compensate for improvements in technology). My guess, though, is that if this were possible someone would have packaged a solution already. – SQL_Underworld Apr 10 '18 at 4:21
  • You don't necessarily need new hardware to test on, though. You can run all these tests on your current hardware -- measure your wait stats -- and figure out which ones you want to reduce on new hardware. – Erik Darling Apr 10 '18 at 16:26
  • @sp_BlitzErik I get what you're saying. There are just many, many things wrong with this server and not enough time to fix them to truly uncover bottlenecks that aren't related to misconfigurations or poor design choices at all the various layers. It's... frightening. – SQL_Underworld Apr 10 '18 at 17:13

Please try Microsoft Assessment and Planning Toolkit. This will give you current inventory and workloads. At least run this tool for 24 hours to collect overall performance. https://www.microsoft.com/en-us/download/details.aspx?id=7826

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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