Running SQL database on virtual mcahine

I'm wondering if there are any disadvantage of running SQL database in virtualization. If the hosting server has scsi disk pools in raid 5, will it be the same running database in the hosting server and running database in virtualization?
LVL 1
crcsupportAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MrAliCommented:
Virtualization and SQL Server got a bad name when VMWare back in 2007, but it is very solid now, as long as you follow some basic rules and principles.  I personally run SQL Server on VMWare in production and as long as you keep an eye on it, things work great.

Keep your VM OS on a separate partition than your SQL Server data disks is probably the biggest thing you could do.  There are tons of random data access patterns on the VM OS files which will interfere greatly with SQL Server, especially if you are using non-SSD drives, which have better random disk IO.  

Load up the machine with RAM.  Keep your eye on the perfmon counters.  Make sure your page life expectancy is well over 500, make sure your disk sec/read disk sec/write are below 100.  Anything over 100 is piss poor performance and it must be optimized at that point.  Separate the log and data files to separate physical partitions if possible, if not, then make sure to keep an eye on the auto-growth and disk performance.  If you have the version of VMWare which has the balloon manager which sucks up memory whenever it feels like it, disable it.  

Brent Ozar has great resources for VMWare and SQL.  You need to watch his videos in depth, don't worry, they aren't boring.  
http://www.brentozar.com/community/virtualization-best-practices/

He is the best web resource for this.  I'd even recommend his $195 class on it.  Good luck and have fun!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MrAliCommented:
BTW, by 'separate partition for the VM OS files', I mean a separate disk array if at all possible.  Putting it on a different logical partition will have minimal performance benefits, but on a different disk array, it will have major performance impact.  

Also, it depends on how big your database is, your access patterns (tons of table scans?), memory flushing, maintenance requirements, and how many transactions per second you need to support at peak hours.  If it's not going to be highly utilized and your hardware is good enough, then go for it.  I'd ask you to make a test case scenario and do some data load testing first.  :)
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
Performance can be severley affected in a virtualisation, environment, if you have incorrect slow RAID 5 datastore.

See this video here, of a client SQL issues, with VMware

Poor Microsoft SQL Performance caused by Virtualisation - Not all servers are equal!


Follow this Best Practice documents for VMware
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

crcsupportAuthor Commented:
many thanks.
0
crcsupportAuthor Commented:
I want to keep this thread for my own reference, so want to add one more thing on this thread. Can you recommend any turorial on how to analyze and optimize MS SQL server?
0
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
You run the Trace on the Microsoft SQL Database for a day or week, whilst users are using it, and use the Server Profiler.

How to: Create a Trace (SQL Server Profiler)
http://technet.microsoft.com/en-us/library/ms175047.aspx

Introducing SQL Trace
http://msdn.microsoft.com/en-us/library/ms191006.aspx

About SQL Trace
http://msdn.microsoft.com/en-us/library/ms243169%28v=vs.80%29.aspx
0
MrAliCommented:
Base level SQL Server optimization is generally watching 3 core metrics, disk io, cpu, and memory, then you can get deeper into other layers.  

MS recommendations state your disk reads/sec and disk writes/sec should be under 20ms, but in reality, anything under 80ms is fine, 100ms is poor performance.  Page life expectancy in the memory pool should be AT LEAST 500 these days, if it's less you have major memory pressure we need to address right away.  Avg. Disk Queue Length in physical disks was a big deal, not so much anymore, but it should be constantly below 4, never more than that.

Then you want to mointor SQL Server using the DMVs (Dynamic Management Views).  These things are a wealth of information.  You can find bad indexes, fragmentation, usage patterns, all kinds of great information.  Let me link you to one of the best guys for this stuff, watch as many of his webcasts as you can, and let me know if you have questions:
http://www.brentozar.com/sql-server-training-videos/
I'd recommend:
Virtualization
Completely Legal Performance Enhancements
Lose Waits Fast with Wait Stats
Playing Doctor with the DMVs

If you feel any of our solutions were good, please mark whichever you did as an answer so we can get our account :P

Thanks and good luck!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VMware

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.