Home / server / Questions / 313415
Accepted
Primoz
Asked: 2011-09-21 05:29:11 +0800 CST

Best practice for instaling multiple instances of MS SQL 2008r2 on Windows server 2008r2 (running on esxi)

  • 772

I wann't to move my sql server onto virtualized platform (Vsphere, esxi 4.1). Here is my plan on how would I install it:

  • Vm with 3 disk's. C: for windows and sql binaries, D: for sql data, E: for logs
  • All disk on different datastores
  • Disks D: and E: are independen't so snapshoot's won't effect them
  • Root folder for every instance is on default path (C:\program files...)
  • data and log folder is moved onto D: and E: disk

Ram and cpu cores will be added as needed.

Is there a way to monitor and graph CPU, RAM usage and dik I/O for let's say 24h to get some info about load on the server (with built-in windows tools).

Will this work fine and do you have any special tips&trciks ? Best regards, Primoz.

EDIT

I tried simple test setup with just two disk C: for installation and D: for data and logs and with 2 vCPU's and 6GB of ram. With my test suite I have push the cpu usage to 90%+ and ram usage to 98%. So I'm guessing that this means that disk is fast enough and that I'm limited by CPU and RAM ?

windows sql-server vmware-esxi vmware-vsphere windows-server-2008-r2

2 Answers

  1. Best Answer

    I use almost the exact same methodology. I use the inbuilt windows perfmon tools in for monitoring my physical boxes and the inbuilt monioring graphs available in vSphere (I think in ESXi as well) for my virtual boxes. I allow my SQL data and logs to be included in our snapshots. That is environment specific so you might want to test both ways.

    EDIT - the one gotcha I can think of is the temporary type SQL tables. Those may need to be moved by hand to the new disk. http://www.sqlteam.com/article/moving-the-tempdb-database

    We use Commvault for system backups which uses the inbuilt snapshots as part of the process. By leaving the snapshots enabled on the SQL disks I avoid having to use a separate SQL backup. If your environment does not have a good low usage time on your databases this will not work for you.

    • 2

  2. Assuming that there aren't other workloads on the datastore where you'll be putting "E:" (because any other workloads there will "smear" your sequential access into random access and defeat the purpose of isolating the workload within the VM) that all seems reasonable.

    The built-in Performance Monitor (PerfMon) is exactly the tool you want to use to capture logs within the guest VM. The free Performance Analysis of Logs tool can help you slice / dice PerfMon logs to get some good analysis of the captured data.

    The hypervisor's own logs aren't bad to look at, too, but you'll get the most granular data from inside the VM.

    • 2