Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 442
  • Last Modified:

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?
0
crcsupport
Asked:
crcsupport
  • 3
  • 2
  • 2
3 Solutions
 
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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now