The Right Way To Build A “Small” SQL Server

“I just need a ‘small’ SQL server for this production database.” Many CIOs have come to me making this statement, and are immediately set back by licensing costs. This blog series will present several options to meet this requirement and help tune the decision making process. We’ll discuss SQL Server Standard on different platforms including: On-premise Virtual Server, Azure IaaS, SQL Azure, and at a hosting provider like Rackspace.

26710_grande

When my clients hear how much SQL Server Standard Edition will cost to license, they react and attempt to cut corners on the project to reduce costs wherever possible – including the SQL server itself. I guarantee this is the wrong approach! Cutting corners provisioning SQL Server is never something I like to hear. Why? You get you pay for, and downgrading the database should never be an option. Trimming the server hardware will give up a lot of performance to save a relatively small amount of money.

Clients always desire minimal downtime but won’t often budget for the pricey SQL Enterprise Edition, here is my “go to” minimal configuration for an on-premises virtual server.

  • Virtual Machine
    • On a clustered hypervisor (Hyper-V WSFC or ESX HA)
    • License costs will be significantly less for a VM vs physical server
  • 4 vCPUs
  • 64GB RAM for SQL Server 2012 and older, 128GB for SQL Server 2014 and more recent
    • Could be less if only total data size is smaller
    • If you add services like SQL Server Reporting Services, Analysis Services or Integration Services, definitely increase the RAM
  • RAID10 or SSD storage for all seven attached disks
    • Boot disk, SQL Binaries, SQL Data, SQL Log, TempDB, TempDB Log, and Backups

The smallest database server I ever build for these requirements has four cores. Microsoft sells licenses in two core packs, but requires a four core license minimum. I don’t skimp on the SQL Server virtual machine (VM) with just one or two cores when I am paying Microsoft for four! Even if the workload doesn’t immediately require four cores, provision them. And when possible, make sure they are the fastest cores available like the Intel Xeon E5-2643, 2667, or the big 2690. If I am building a brand new server I will look for the “v2” version of the same chips for the latest architecture (Fall 2013)!

The four core SQL Standard license will run $7,172 (open license, retail). Wow. Again, don’t be tempted to scale back the virtual machine (VM) configuration. Throw the full 64GB (or 128GB in SQL 2014 and later) of memory in the VM and make sure the data store for the data and log files is as fast as possible. “More SSDs please,” is one of my popular quotes in this case! The virtual server’s share of resources on a typical hypervisor may be $300-500 or so in this case. Cutting back here will not save much money at all. Instead of thinking of these costs as “I can’t believe I’m spending this much on licensing, I need to make up the difference somewhere,” think “I’m spending all this on licensing so this server better PERFORM!”

When virtual, I always put the SQL VM on a clustered hypervisor to increase uptime even without a second SQL instance or Enterprise Edition.

The server above is a one-time expense! The only on-going costs will be electricity and the always helpful system administrator’s time. If high licensing costs that are due up front are a problem, let’s take a look at Azure IaaS, SQL Azure, or a datacenter hosting provider instead of slashing your server configuration. Look for updates to this blog as the series progresses!

 

To contact a SQL Ninja, email paul @pbitpro.com or call (972) 987-0637. For more information on Microsoft Windows Server and SQL Server, please visit our blog or follow us on Twitter at @pdgb.

Leave a Reply

%d bloggers like this: