When do I install SQL Server on a Virtual Server?

This article is one part of a 5 part series highlighting where DBAs can deploy SQL Server databases. The 5 places where your SQL database can live are:

• Physical server
• Virtual server
• Cloud server (IaaS – Azure VM or AWS EC2)
• Cloud instance (PaaS – Azure Managed Instance)
• Cloud database (DBaaS – Azure SQL Database or AWS RDS)

Now, installing SQL Server on a virtual server may seem like a great compromise – and indeed, it is!  There are many pros AND cons to this solution, and they need to be thoughtfully considered for your specific application. I am confident that in >90% of scenarios, a virtual machine for SQL is the right answer.  While the reality is that it’s not an easy answer to do it well, this virtual machine will get the job done.

A virtual server for SQL allows you to get the exact horsepower you need, contain licensing costs, scale with a simple reboot, and get some easy high availability.  Virtualizing SQL does require that you have a good relationship with your virtualization admin (and storage admin, and networking admin, and Windows admin…) so that the tuning and best practices for this type of deployment can be followed. Troubleshooting can also be challenging.  Be aware of finger-pointing, and know where to find detailed statistics at each layer AND what they each mean – VM host stats, CPU Ready, etc.

Top 5 (+1) Pros to installing SQL Server on a virtual server:

1. Control license costs
   1. The most popular licensing method for Standard AND Enterprise is per core
      1. This approach gives you unlimited user counts and no CAL counting
      2. For smaller organizations, Server + CAL pricing may be advantageous
   2. Give your VM 4 cores (no less) and license only 4 cores!!!
      1. Microsoft minimum core purchase is 2x two core packs (4 cores total)
   3. Add cores as performance requirements dictate and license them 2 at a time (6 cores, 8 cores, 10…)
2. Own the stack
   1. Windows Server OS + Hypervisor + Compute + Storage – all yours (or your team’s)
   2. So long as you have a compute cluster with a current generation CPU and an all flash SAN, performance should be within 5% of a bare-metal deployment
   3. Don’t let management push you to install on hardware >3yrs old, or on a SAN that still runs on spinning disks!
3. Memory is FREE (or at least really cheap)
   1. SQL Server Standard 2016 and higher can use up to 128GB for the DB Engine alone
   2. It costs you “nothing” to get more RAM for smaller deployments
   3. You may need to beg your VMware admin to give it to you, lol!
4. CHEAP high availability
   1. Hypervisor clusters mean your SQL Server is now HA (mostly)
   2. Can automatically reboot in any hardware failure event
   3. Can pre-emptively move (live migrate, vMotion) to another host for host maintenance
   4. Still requires downtime to patch Windows OS – not true HA
5. Scale easily and quickly (with a reboot)
   1. It’s easy to add more CPU cores, more RAM, more disks to a VM compared to a physical server!
   2. Just send Microsoft more money when you add more CPU, everything else is “free”
6. Live migrate (or vMotion) your VM to a new hardware with no downtime
   1. Move VM to new host servers (bigger better faster) with no downtime
   2. Move disks to new storage (bigger better faster) with no downtime
   3. Move from that lame RAID5 spinning disk to new RAID10 SSD or an NVMe flash card without application disconnects
   4. Makes for seamless hardware refreshes!!

Top 5 Cons to virtual servers:

1. Troubleshooting
   1. Have a good team of knowledgeable engineers in server, virtualization, storage, management, and in monitoring focus areas to ensure optimal performance of your SQL database
   2. Don’t let anyone say, “It’s not a <my area> problem, I’m going home.”
   3. Requires knowledge in all areas of expertise to ensure multiple pathways, eliminating bottlenecks, and getting configuration right
2. Lots of layers of “highly available”
   1. Host cluster can move VMs to hosts to avoid host maintenance/outages
      1. But patching Windows/SQL still requires downtime
   2. Add on Failover Cluster in SQL or other methods to get true HA
3. Some hypervisor features need to be turned off or excluded
   1. Snapshot based backups, while nice, won’t meet all of your needs for SQL databases
   2. Don’t live migrate or vMotion an online SQL Server, and turn OFF DRS or other host load balancing services that move VMs around in the middle of the day
4. SQL Server, by nature, is scale UP.
   1. VMs used to be limited in size, but now can get >1TB of RAM and 64+ cores in them
   2. If you need to keep increasing the size of your VM (greater than 16 cores for example), it might be better to go with a physical deployment rather than a virtual one
   3. We can scale out reporting workloads with LogShipping, replication, readable mirrors, and availability groups if you are trying to increase performance there, and we can do that cheaply with virtual servers!
5. Scapegoat
   1. That 5% performance hit really is just that – I’ve personally witnessed it.
   2. Don’t blame the VM for bad performance – it’s a cheap shot that usually is wrong.

If you must absolutely, positively, have the most versatile and customizable SQL server for the money, you can build it in your own datacenter, often on existing hardware, and with minimal performance impact. This is a correct, but long sentence. Ignore the green squiggles…MOST companies out there are implementing this approach and are doing it very well. Going to physical servers only makes sense if a 5% performance hit will monetarily affect your revenue AND if you are willing to pay up-front for that 5% boost. Virtual servers are a wonderful compromise for cost, performance, tuning, and control. If your application and your users live on-premises, don’t hesitate to go with a virtual server as your first option!

Tips to avoid mistakes:

• Do not overcommit memory or CPU on the host
  • ATTEMPT to have the host your SQL box is running at be less than 1:1 ratio of Virtual Cores to  Physical Cores and <100% memory provisioned
• Don’t skimp on the RAM
  • Again, SQL Server Standard can use up to 128GB of RAM – give it to it!
    • PLE in the millions? Perfect!!
  • This can drastically alleviate issues with disk latency in the stack
• Don’t let the hypervisor sysadmin deploy a “standard” VM for you.
  • Make sure they follow the best practices for that hypervisor AND specifically for SQL Server.
• Don’t use dynamic or thin provisioned disks
  • This can cause unexplainable performance issues for data loads, log writes, local backups, etc.
• Turn OFF DRS or other “host balancing” services/features
  • Moving SQL disks while online can degrade performance incredibly and corrupt indexes/tables

Here are two recent virtual SQL Server builds of mine for clients with very specific requirements for performance, proximity to application servers, and licensing restrictions.

Use cases for SQL Server:

• Small 150GB database in an OLTP (MS Dynamics CRM) system
  • ~$0 to do it WELL (not a typo)
    • Virtualization admin had spare capacity!!
    • 4 cores, 64GB RAM
    • All disks on SSD, were right-sized to 80% capacity, and thick provisioned
  • SQL Server Standard licensing ~$8,000
  • Minimal labor
    • <1 day with VMware engineer to build the right VM and get data restored
• Large 800GB database for a mixed workload (transactional and reporting)
  • ~$162,000 to get a very nice solution in place
  • Client had to add another ESXi host to maintain N-1 redundancy – ~$50,0000
    • All the Enterprise basics, same build as existing servers (all Xeon scalable procs, current generation)
    • 8 cores, 256GB RAM
  • SQL Server Enterprise licensing ~$112,000
    • Required to use more than 128GB of RAM
    • Client also needed advanced encryption so Enterprise was required
  • Already had Nimble AF40 flash array with spare capacity for this database

Choosing virtual servers for your SQL Server deployment does have its advantages, but as we learn more about Cloud implementations of databases, we might find some better methods to store our data, maybe??  Have you recently deployed SQL Server this way? What was the biggest reason you selected this implementation?

Reach out to me at paul @pbitpro.com and let’s have a conversation about SQL Server! Or, you can find me on LinkedIn at https://www.linkedin.com/in/pauldavidbell/