Virtulize SQL on local storage

[Newb449]

Guys,

I hope I'm posting this in the right place.

I have a requirement to provide a virtulization solution on a very tight budget. As a result, I only have 2 ESXi 4 hosts with 32GB RAM and local storage, although I am toying around with the idea of iSCSI and OpenFiler on a dedicated box as a shared storage solution.

I am concerned about virtulizing SQL 2005 on this platform. Not so much SQL, but running SQL on a single ESXi 4 host with local storage (RAID 5). I'm concerned about the disk I/O contention between the virtualised SQL server and the other VMs. There's also the issue of the SQL server itself consuming disk/memory resources from its host to the point that it would not be practical for it to be virtualised.

I'll need to run 2 SQL applications to start off with. One has a 8GB minimum RAM requirement, and the other ha s a 2GB minimum requirement. This will take away from the amount of RAM I can give to other VMs.

From what I've read, SQL performance is improved when you have different RAID types and different drives for the DBs/transaction logs. These options are not possible with local storage.

I would welcome some opinion on if:

Running SQL on a single ESX4i host with local storage is ever a good idea

If it is more acceptable to use iSCSI storage

Thanks

[sflanders]

What are the specifications of the servers being turned into ESXi hosts (besides memory)? In any case, I would not recommend using ESXi for a virtualized SQL environment. While SQL can be virtualized (see: http://www.vmware.com/files/pdf/solutions/sql_server_virtual_bp.pdf) I would recommend using ESX over ESXi (http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1006543). My reaon for this recommendation is because SQL is quite resource intensive and ESXi performance is much less than ESX (http://www.davidrickard.net/2008/08/16/esxi-and-the-lousy-performance/). In addition, I would recommend the different RAID groups (i.e. RAID5 for data and RAID1 for logs), which means you should use shared storage instead of local storage,

[Newb449]

Dual core Xeon 5060 3.2Ghz

8GB RAM

50GB for OS

500GB for DB

150GB for logs

I agree, and it was my belief that it would not be a good idea to run it on a ESXi host. At least with a clustered solution you can assign more resources

[TimPhillips · Accepted solution]

Yes, better to use ESX. Best of all - ESX4, it has a lot of perfomance impovements, especially in iSCSI stack. Also for you is better to use shared storage, because of possible clustering of your SQL server in future. I would you to use iSCSI over NFS, because it has lower overhead, what gives greater perfomance for SQL. An also I recommend you not to use some free Linux-based solutions such as OpenFiler or FreeNAS - they are not best options for building good and robust solutions. If you have small budget you can try to use StarWind or Datacore.

[Newb449]

Sounds good

Thanks

[drummonds]

The issue here isn't to use ESX or not, but whether you run SQL on the servers available to you. The overhead of SQL virtualization is so low with ESX 4, and the IO throughput so high, that ESX's presence will be transparent to the application and its users. You simply must estimate your transaction throughput and subsequent IOPS and assure that your storage can support such a rate.

Scott

More information on my blog and on Twitter: