2

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 ?

3
  • I'd get familiar with sql's dmv stats, you might start seeing some strange io issues. Are the datastores on FC or ISCSI or NFS?
    – tony roth
    Sep 20, 2011 at 13:37
  • They are on FC and for storage we are using EMC symmetrix.
    – Primoz
    Sep 20, 2011 at 13:45
  • for sql the DMV stats will show where the real issues are, perfmon and PAL are good to, but to look at real sql issue then the DMV stats will give you the most info. I usually start out with perfmon, and usually that will give me hint as to the problem but occasionally I need to dig further by using dmv's.
    – tony roth
    Sep 20, 2011 at 13:52

2 Answers 2

2

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.

1
  • I don't know about data and logs if to put them in snapshots. If you have a lot of data (100GB and more) and there is much I/O then taking snapshot takes a long time and cunsumes much disk's. Also when you revert to old snapshot you get old data and then you must restore from backup itd. Please write one example where snapshot of data and logs could come handy. Maybe I missed something.
    – Primoz
    Sep 20, 2011 at 13:35
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.

1
  • But even if other workloads "smear" the access I stil think that separating logs and data is beter than keep them together.
    – Primoz
    Sep 20, 2011 at 15:12

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .