sql 2012 Physical or Virtual?

davetherave2 · 7 May 2013 at 22:32

So am currently playing my favourite game at work. (How much can I get away with specing a server), this time I have to make sure I get it right so currently looking to upgrade our production environment of SQL 2000/2005 servers from their flagging out of warranty hardware and wanted some advice on if my thinking is still valid or out of date these days.

So I have always been a firm believer that SQL should live on it's own physical box with nothing else being installed on it. But with moving most of our server infrastructure to VM and the sql boxes pretty much being the only servers left on their own kit is it worth virtualizing them as well or should they still be left on their own physical boxes.

With the cost of licensing for SQL 2012 being a touch OTT for my liking it means my plans of having a 12 core (2x CPU) server is being scuppered due to the SQL licensing costs being between £15K-20K for just the standard license. (I almost fell of my seat when I was told how much the enterprise license was)

So I guess this then leads me onto the next question is MS SQL still the preferred DB of choice for a .NET environment or would something like an Oracle/MySQL or some other solution be a better product these days?

I have only ever used and admin'd a MS SQL environment so going to something else will need to have quick learning curve and allow existing MS SQL databases to be imported into.

Thanks in advance for any help on this matter.

prj1865 · 7 May 2013 at 22:59

I think the answer is going to be physical and clustered but i'll let someone with proper SQL experience confirm. (Not sure if this is down to SQL resource requirements or licensing?)

Xinn · 7 May 2013 at 23:13

Bare in mind if you go virtual you have to licence each host if using HA and DRS. If other hosts are just there as passives you can just license the active I believe.

HungryHippos · 7 May 2013 at 23:14

I guess the question may become "how heavily utilised is the Database?".

If the answer is not really then virtualised will be fine, for a really heavily utilised DB then physical may be better as it won't be contending for resources with other servers on the (presumably) ESX host.

I run a VM SQL server for a non-mission critical ASP.NET application backend and the SQL Server seems to behave itself just fine.

VM will also have scalability benefits, adding RAM/CPU/Disk as needed.

davetherave2 · 8 May 2013 at 03:20

The main server that this will be replacing is an old hp system with 2 xeons 3.6ghz(2+2), 4gb ram which is getting absolutely hammered all day every day. the cpu usage is very rarely below 80% and requires regular reboots due to indexing services and other sql services failing to function correctly and is progressively getting worse and worse as the server is asked to do more and more hence the upgrade. It is the mission critical server for the business which is why i want to make sure the purchase is right. The current server I am speccing out is 2x 6core xeons, 128gb ram, 10gb nic (depending on cost possibly a second will be added), 10tb sas storage(raid 50).

But due to licensing SQL2012 i may have to drop one of the xeons otherwise i am going to have to put it into a hyper V environment to ensure it is only using the 6 cores which seems silly. I am currently setting up a test environment on an i7 system with 16gb of ram to see how one of our databases performs.

The alternative is to get a new esx host to add to our existing vm environment and have this solely dedicated to sql and nothing else.

We have some sql 2005 servers in the vm using the bare minimum to keep them running at a decent level but they are not being utilised to much so they seem to be performing well but the intention is to bring all these disperate servers into one sql box.

I guess the next thing is then reporting services as we currently replicate via log shipping our databases to another physical server which I presume would need to be licensed as well although I guess we could just use a 2 core license for this. (amazing what springs to mind at 3am)

I think I may have to put some more thought into this.

m4cc45 · 8 May 2013 at 06:59

Disk IO is another major consideration - remember if you're running on VMware and it's hammering the disk it's going to affect every other VM on there (you could IO control it but it's a pain).

I'd seriously recommend physical if it's heavily utilised.

M.

matthab · 8 May 2013 at 08:32

Physical all the way, SQL likes to eat what it can get.

Remember the new 6 core xeons will walk all over a 4 core one. The new CPU's are very powerful and very efficient. Id start with one CPU and make provisions to buy another if needed.

I find SQL likes memory and disk IO. If its a good database and well optimized it wont use to much CPU.

Xinn · 8 May 2013 at 20:24

davetherave2 said:
The alternative is to get a new esx host to add to our existing vm environment and have this solely dedicated to sql and nothing else.

Pretty much what we have done. 3 high spec ESX hosts in their own cluster dedicated to SQL (with as few instances as possible, a fair bit of consolidating small DB's to a shared instance) and use the other 9 hosts for non SQL.

Not sure how strict MS are regarding virtualisation if you have the dedicated SQL hosts in the same cluster as the other hosts. Yes you can create rules to prevent VM's running on other hosts except in an HA event but I dunno if that will wash with them as its easy enough to disable the rule.

davetherave2 · 8 May 2013 at 21:15

The issue I have is that MS seem to have priced out the mid sized company that requires upgrades to their SQL infrastructure.

The fact that you need to license it on cores vs physical processors is really prohibative. I just see it as another way for MS to make more money out of their business customers due to poorer sales in other divsions within the company. (rant over)

I think due to budget constraints it may be something that is delayed yet again and actually get a decent budget that covers all the licensing requirements and hardware requirements rather than doing it half hearted. (the powers that be are not going to be happy about that)

topher · 8 May 2013 at 21:23

SQL as a VM will be fine as long as you have the resources and storage with high enough iops.

I have used RDM's with Netapp kit and EQL 10GB iscsi multipath and ran SQL DB's without any issues.

the below is from a post on another forum, but its bang on imo..
what storage will you be using?

A single VM with 4GB Ram and you include your DB's in the same flat file, VM lives on 2 spindles, indexing massive amounts of data, it will suck.

Put the OS/SQL on VM with massive amounts of RAM, use RDM to mount LUNS on dedicated spindles to separate DB's / Logs, high I/O ceiling by using the correct amount of spindles, Size block size to match your SAN, Size SQL for your resources, and have a well maintained maintenance plan, and it will work great.

m4cc45 · 8 May 2013 at 21:51

If you have a VM with massive amounts of memory it makes having it a VM almost pointless. Unless you have over-provisioned ESXi hosts you can't vMotion it. Don't get me wrong it makes DR a hell of a lot easier but if you're using a host to run just one server it's pointless (especially due to licencing on VMware as well and also, as other people have mentioned, if it is on HA / DRS / etc. then you have to licence the multiple hosts which will be considerably more CPUs).

Having a single CPU and then moving to dual-CPU's is also another issue as you then have to factor in SQL licencing. Personally, as people have said, Physical is probably your best bet in this instance.

Xinn · 8 May 2013 at 23:25

topher said:
RDM's with Netapp kit

Snap but with 8Gb fibre. SnapManager for SQL and Snap Mirror to offsite makes backup/recovery and DR a dream.

Yamahahahahaha · 9 May 2013 at 10:08

davetherave2 said:
With the cost of licensing for SQL 2012 being a touch OTT for my liking it means my plans of having a 12 core (2x CPU) server is being scuppered due to the SQL licensing costs being between £15K-20K for just the standard license. (I almost fell of my seat when I was told how much the enterprise license was)

You have two 'cheap' options here:
1) Get a 2-socket dualcore Xeon physical box (E5-2637s), which will allow you to put as many SQL instances on that box as you please with a minimal amount of licensing (2 x Enterprise Core licenses ~ 20k).
2) Virtualise SQL 2012 and put it in a VM locked to 4 vCores, and use 2 x Enterprise Core licenses. You are limited to one instance if you only license up the VM.

Your preference should really depend on how you'll be providing storage. If you have a nice beefy 4xGbE or nx10GbE SAN, with lots of fast disk and tiering, go with the VM. Otherwise, go with the physical box with fast disk and possibly some SSDs (depending on your database sizes).

You might also be able to get away with buying Standard Core licenses, but you'd have to be very sure you don't want the nice features Enterprise offers.

davetherave2 said:
So I guess this then leads me onto the next question is MS SQL still the preferred DB of choice for a .NET environment or would something like an Oracle/MySQL or some other solution be a better product these days?

You can use other platforms but if there's no specific requirement, and you already know it works on MS SQL, then stick with it.

davetherave2 said:
I guess the next thing is then reporting services as we currently replicate via log shipping our databases to another physical server which I presume would need to be licensed as well although I guess we could just use a 2 core license for this. (amazing what springs to mind at 3am)

Xinn said:
Bare in mind if you go virtual you have to licence each host if using HA and DRS. If other hosts are just there as passives you can just license the active I believe.

The options for replicating to passives are more limited though. Log Shipping is discouraged in SQL 2012, in favour of Database Mirroring and AlwaysOn - but to use AlwaysOn you need to pay for the active licensing!

The High Availability features are a great reason to move up to SQL 2012. You mentioned that the databases are mission critical, these features are made for you. This means you'll need a two-node solution, which weighs in favour of using a VM and should be a consideration.

davetherave2 said:
The main server that this will be replacing is an old hp system with 2 xeons 3.6ghz(2+2), 4gb ram which is getting absolutely hammered all day every day. the cpu usage is very rarely below 80% and requires regular reboots due to indexing services and other sql services failing to function correctly and is progressively getting worse and worse as the server is asked to do more and more hence the upgrade.

Most of the time, high CPU usage on an SQL box is down to insufficient disk IOPs (I/O). Every time it has to wait to perform an operation reading or writing to disk, the whole shebang grinds to a halt until the disks are ready to deal with the request.

Run perfmon and take a look at the depth of your queues across all your logical volumes and their average read / write latencies. Queues should be under 20 (under 4 ideally) and average read / write latencies should be under 20ms.

Your suggested spec looks fine (CPU considerations aside) but go with RAID10 and maybe SSDs / SSD cache to improve reads.

Xinn said:
Not sure how strict MS are regarding virtualisation if you have the dedicated SQL hosts in the same cluster as the other hosts. Yes you can create rules to prevent VM's running on other hosts except in an HA event but I dunno if that will wash with them as its easy enough to disable the rule.

If you've licensed the VMs with core licenses (minimum of 2 core licenses per VM, otherwise vCores = physical cores), that's fine. If you want to run as many SQL VMs as you like on a box, all the boxes that the VM might failover to need to be fully licensed, for instance 4 x quad core processors x 2 nodes = 32 Core licenses.

It really depends on how many sites you have, how much downtime is acceptable and what your business continuity plan is (How will the other apps talk to the database if the primary server goes down?) etc.

LordSplodge · 9 May 2013 at 11:26

m4cc45 said:
Disk IO is another major consideration - remember if you're running on VMware and it's hammering the disk it's going to affect every other VM on there (you could IO control it but it's a pain).

I'd seriously recommend physical if it's heavily utilised.

M.

Correct. I'd be wary of going virtual unless you can provide dedicated disk access or the databases are lightweight.

Poor disk IO is the worst enemy of a SQL database.

Schnippzle · 9 May 2013 at 12:53

m4cc45 said:
Disk IO is another major consideration - remember if you're running on VMware and it's hammering the disk it's going to affect every other VM on there (you could IO control it but it's a pain).

I'd seriously recommend physical if it's heavily utilised.

M.

This tbh, coupled with cpu/ram requirements. We will always keep sql instances on their own physical hosts.

beavis · 9 May 2013 at 13:49

I've always stuck with physical hosts for MS-SQL.

If you are running 2012 standard then it is limited to 16 cores, rather than a number of physical processors as with 2008. Windows 2008r2 standard supports 32gb RAM, you'll need to look at higher priced versions if you need more than that.

gazza0371 · 10 May 2013 at 22:53

Probably re-iterating what a couple of others have said, it will be all about IOPS and contention with other VMs running on the same host. SQL will run fine on a VM, and in my experience the main thing to consider is whether the resources that you need to give it are close to those of a physical server (thus making it pointless to put on a VM).

Also the obvious considerations for redundancy depend on your overall ESX/VM environment along with the storage capabilities - in other words, what are the availability requirements and hence does your existing ESX/storage provide an appropriate environment for your service levels against the need to provide additional hardware for a clustered environment.

Physical is good, but obviously more costly in terms of hardware if you need clustering, whereas if your existing ESX environment already has the capabilities to provide failover without the need for dedicated hardware (i.e. you only use it when you have a DR situation).

Also backups come into play, but again depend on your VM situation and also whether you would use DAS for physical boxes or SAN based storage.

So basically what I'm saying is that it is horse for courses...main factor is whether your existing VM infrastructure can provide suitable capacity, availability, etc at an acceptable contention ratio with the VM guests that already reside on the environment.