Before we pick CPUs or memory, let’s start by looking at SQL Server 2012’s licensing costs:
These are MSRP prices and do not include the extra costs for Software Assurance (maintenance) or any discounts for being a school, non-profit, or those photos you have of Bill Gates.
Physical, Standard Edition – you’ll notice that I went with 2 processors rather than 1. Technically, if you’re dying to save money, you could get away with installing SQL Server on a server with a single CPU, but if you want to save that much money, go virtual and avoid the hardware costs altogether. I could have also gone with 2 dual-core processors, but I only know of one dealer still selling those, and we’re talking about new servers here.
Physical, Enterprise Edition – that’s $6,874 per core, so it adds up fast.
Virtual, Standard Edition – here we’re using just 4 cores, the minimum license size Microsoft allows for a new server. You can build smaller ones (and I do), but as long as you’re licensing with Standard Edition, you’re paying per guest, and the minimum cost is $7,172.
Virtual, Enterprise Edition – if you really need Enterprise features in a virtual machine, you’ll most likely be running multiple SQL Server VMs. In that scenario, you’re best off licensing Enterprise Edition at the host level, and then you can run an unlimited number of SQL Server VMs on that host. (When I say “unlimited”, I’m using it the same way your cell phone company tells you that you have unlimited Internet.)
When I’m designing servers, I start with the licensing discussion because it helps everyone focus on the real cost of the server. Often folks want to nickel-and-dime their way into 16GB of RAM and a pair of SATA drives, but once licensing costs come into play, they realize architecture here is different. Our goal is to absolutely minimize the number of cores involved – ideally deploying virtual machines as often as we can – and then when we need to go physical, we get serious about the hardware, because this stuff ain’t cheap.
Now Let’s Talk Hardware
Once you’ve picked your licensing and physical vs virtual, let’s talk hardware. I’m using a major server vendor, but the exact brand isn’t important – you can get similar pricing from the hardware vendor of your choice, and this post isn’t about making brand decisions.
Notice how the numbers are displayed as total, licensing, and hardware? That’s how you need to present them to management. When a manager looks at those physical server numbers, the hardware is still clearly the cheapest part of this transaction. If they want to drive costs down, they can start by asking why this SQL Server needs to be physical – the real way to save money here is to drop down to the Virtual column.
Again, these are public sticker prices here based off the hardware vendor’s web site, and don’t include the extra costs of Windows, management software, or volume discounts. These prices also don’t include the cost of the drive space for the data and log files. Your choice between shared storage (SANs), local SSD, or local magnetic drives varies widely between shops, so I’m leaving that out. Let’s just focus on the basic hardware at first.
Physical, Standard Edition – this is a 2-CPU rack mount server with the fastest quad-core processors available right now, 96GB of the fastest memory, a pair of magnetic hard drives for Windows, and a pair of vendor-supplied-and-supported solid state drives for TempDB.
“BUT BRENT! YOU TOLD ME THIS WAS THE SMALLEST SERVER YOU’D DEPLOY, AND THAT SOUNDS LIKE AN INCREDIBLE SPEED MACHINE!!!1! WHAT ARE YOU THINKING? ARE YOU TROLLING ME AGAIN LIKE YOU DID WITH THE FRAGMENTATION POST?”
No, this is actually what I recommend to clients. You don’t waste dry cleaning money on your dad jeans, and you don’t run $14k worth of software on $3k worth of hardware. Besides, you want this thing to last for a few years, right? You don’t want to come running back to this machine again and again trying to fix performance problems that could be fixed with a basic injection of memory.
Physical, Enterprise Edition – the exact same 2-CPU box with the same processors, but upgraded to 384GB of memory and four 400GB SSDs for TempDB.
“BRENT ZOMG YOU MUST BE JOKING THAT’S CRAZY FAST, LIKE KEN BLOCK’S FORD FIESTA FAST. NOBODY NEEDS THAT MUCH MEMORY EVER, BILL GATES TOLD ME SO!!!!ONE”
Yes, my incredulous caps-loving friend, because we need to stay focused on the $55k worth of licensing costs, the dozens (hundreds? thousands?) of employees who rely on this server every day, and the capabilities in Enterprise Edition. Right now, the $55k of licensing you bought is being wasted on crappy hardware that’s more like Stason Lee’s Ford Fiesta.
Virtual, Standard Edition – since you’re licensing by the guest, you don’t have to buy an additional host for every new SQL Server you deploy. You can just mix these in with the rest of your virtualization farm and incur incremental costs. It’s certainly not free, but it’s nowhere near as expensive as a dedicated physical box.
Virtual, Enterprise Edition – since we’re going to license this at the host level, we generally only want to run SQL Server virtual machines on this host. Any other guests here are just wasting my very valuable CPU cycles and memory – at $55k for the licensing, I need to keep this focused just on SQL Server. Because of that, I’ve got a host equipped with the same power as my Physical Enterprise Edition spec – fast cores, high memory, and some local solid state to make a vSAN deployment easier for my VMware admins. (Disclaimer: when building out a real VMware host, I’d obviously tweak this – I’d end up with 10Gb Ethernet and/or FC HBAs, for example, but that depends on each shop’s network infrastructure.)
Yes, it’s another shock-and-awe post from Brent.
If you’re building physical boxes with 16GB of memory, and then you find yourself repeatedly going back to those boxes to do performance troubleshooting, the problem isn’t the app or the server or the memory.
The problem is you, and your old-school 16GB memory fixation.
You need to step back and look at the whole picture – licensing, business needs, RPO/RTO – and stop trying to save a few bucks in ways that hurt the server, the business, and your weekends.
53 Comments. Leave new
Is there any merit in increasing the memory in the physical standard edition hardware and look at multiple instances?
John – that’s an interesting question, and I’ve wondered that myself, but I haven’t run into a customer that wants to even consider that approach. I’m sure they’re out there, I just haven’t run into them.
When I run into performance problems on Standard Edition, it usually ends up in one of three categories:
1. We can fix it effectively inside the database server with indexing, query changes, tweaking settings, etc, and still survive with 64GB RAM.
2. We can’t fix it with those kinds of tweaks, but the databases involved could easily be moved onto separate SQL Server instances. At that point, we end up making business decisions about the best home for each of those databases, and often some of them end up on a virtual server, which gets around the problem entirely.
3. We can’t fix it with tweaks, and the databases can’t be split because the app does cross-database joins (or it’s just a big single database). In that case, we have to go Enterprise Edition.
On our last enterprise server upgrade we went to 768GB of memory. Combined with the Violin SSD SAN it runs like a dream.
I would go with 6 instances of SQL 2008 R2 Std on a 8 core single CPU with 192GB RAM w/ 6xSSDs + 2xHDDs in a 1U case (instead of going virtual) total $10-12k for h/w
WQW – hmm, that’s a rather interesting choice. Why 6 instances of SQL Server? Why a single 8-core CPU instead of two quad-cores? Can you point to a vendor where you’d buy this solution from?
Instances: 6 x 32GB ~ 192GB (max for Std edition per instance)
8 cores: E5-2650 v2 is ~ $1100 (can get cheaper with 6 core)
Single CPU: Single CPU license for SQL 2008 R2 (not 4 x double-core licenses)
Vendor: HP DL360p will fit (dual socket MB)
SSDs: 480GB Intel DC S3500 or cheaper 530 (not from HP)
RAID: 420i will handle 8 drives
Windows Server Std max memory is 4TB for 2012 and newer
WQW – there’s a couple of crucial inaccuracies in there. Standard Edition’s maximum per instance is 64GB, not 32GB.
SQL Server licensing is now sold by the core, not by the socket. You’re free to install SQL Server 2008R2 if you’d like, but you have to buy SQL 2012 from Microsoft these days – they don’t sell SQL 2008R2 socket-based licensing anymore unless you’re an EA customer with a contract that hasn’t expired yet.
Because of that, it doesn’t make sense to buy a single 8-core CPU – you’re going to pay for 8 cores there, so you’re better off with two 4-core processors. The 4-core models run faster clock speeds, so you’ll end up with higher performance.
Your server design totally did make sense a few years ago, but since you can’t legitimately buy a socket license anymore (unless you buy used licenses off eBay or something like that), it’s not current.
Would you like to take another shot at it with these things in mind?
We are licensed under SPLA and we do have 2008 R2 as an option but you are right that my server setup doesn’t make sense w/ 2012 licenses. Another option is if there are already 2008 R2 licenses purchased.
Going dual 4-core CPUs makes sense only if would like to install more RAM (more that 192GB) which comes at a performance cost too.
WQW – that’s interesting, because the public documentation says only SQL Server 2012 is available under SPLA now, not SQL Server 2008. For example:
http://blogs.softchoice.com/microsoftnavigator/licensing/all-about-the-core-key-sql-2012-update-for-spla-partners/
It’s great that you’ve worked out a loophole where you can still get processor-based licensing, but that would definitely be a rare exception, and I’d love to hear more about how you got that exception.
Would you like to take another shot at your design with these facts in mind?
I’m positive we just paid 2008 R2 socket licenses for Dec 2013.
For SQL Server 2012 licences I would definitely go with 4-core E5-2637 v2 at 3.5GHz (~$1000)
For our workload any 4-core CPU would do because we are mostly disk and memory bound.
WQW – great, I’m glad you found a loophole in Microsoft’s rules. That’s definitely a rare exception, though, and I’d agree that for everyone else in the world who can’t break the licensing rules, E5-2637 v2s are a much wiser choice.
Thanks for stopping by!
wqw >> I’m positive we just paid 2008 R2 socket licenses for Dec 2013.
The 2008 R2 per-processor SKUs were discontinued at the launch of 2012.
If you’ve got an active SPLA agreement that was started before SQL Server 2012 was released, you can continue to report SQL Server 2008 R2 on the per-processor SKU.
But…
…only if you were already reporting it for that server, in the months prior to the SKU discontinuation
…only until the end date of your current SPLA agreement
If you bought, built and hosted that server after the cut-off date, you have to do the core calculation and report the 2012 per-2-core SKU.
If you’re quoting / billing a new project based on the 2008 per-proc licensing, you’re doing it wrong.
And, when your agreement renewal comes up, you have to start reporting the 2012 per-2-core SKU, regardless.
If you’ve not factored the additional per-month cost that’s going to hit your business at the time of renewal, then you need to start buttering up your clients and management for this — you’re going to have to sit through some real shouty meetings if you’ve gone for the recent 8/10-core units.
The distinction was made to allow service providers (you are a service provider, right?) to continue hosting an existing project without having to go cap-in-hand to your client for a significantly higher new monthly fee (especially for the 6-core processors that everyone was selling in the months prior).
J.
Jimbob – yep, that’s the way I understand it as well.
Hey Brent,
Another reason not to use dual-core processors (besides the fact that they are not sold in new servers anymore) is the fact that SQL Server 2012 requires a minimum of four core licenses per physical processor, so you would be paying for two core licenses that you did not even have.
Yet another reason to retire that old 2007-vintage server.
Yep! Thanks sir.
I wish I could copy those last 3 paragraphs and a link to this article and send to most direct/upper managers I work with. I’m tired of hacking the performance tuning every other week for really low end servers. If I make it sound like a hardware problem, then ‘I must not know what i’m doing’. Oh well i’m still employed 🙂
You can, actually. That’s the whole reason I blog stuff like this – to help people.
I’ve heard from many DBAs over the years who say they just forwarded links to my posts to their managers and said, “Check this out.” That at least starts a conversation, and from there the managers’ eyes are gradually opened to new possibilities.
To me it is astonishing that some managers do not grasp fundamental economic choices like the ones discussed here. It is their *job* to allocate capital, among other things.
On the other hand, some companies get this 100%. There does not seem to be a lot in-between. Either they get it or they don’t.
You forgot about my SQL Server Express server 🙂
Hi Brent,
Interesting article and in line with my own thinking (well, mine is a bit blurry, but your article helped shed some light on existing options). I’ve forwarded your article to some of my own managers so at least a discussion will start about it.
Happy new year!
We currently have an active passive cluster running SQL Server Standard 2008 R2 on physical machines. I’m interested in going “active-active” and can’t find how I would go about getting a new license or being license compliant. I would need another 2 processor license. I really want to avoid upgrading to 2012 right now if I don’t have too. Do you know if/how I can get the needed license?
If you’re buying new licensing, you’ll be buying the licensing for SQL Server 2012. That doesn’t mean you have to RUN SQL 2012 – you can still run the older versions. Check out the Microsoft SQL Server Licensing Guide PDF:
http://download.microsoft.com/download/7/3/C/73CAD4E0-D0B5-4BE5-AB49-D5B886A5AE00/SQL_Server_2012_Licensing_Reference_Guide.pdf
Perfect! That’s what I was trying to find but couldn’t… Page 20. The ability to purchase 2012 Licensing but deploy a previous version (2008 R2). Thank you!
Why or why would you want to go with a 2 socket server when licensing per core? The loss of performance/core (which is what matters) of going NUMA (as opposed to say, a single socket 8 core is just not worth it.
Thomas – great question. I’ve seen stability issues with single-socket servers and drivers, so I’m allergic to single-socket servers at this point.
As a BI guy I would like to know your take on putting the other SQL Server Services (SSAS, SSIS, SSRS) on one box. From a licensing perspective this seems to be an even better alternative for 2012 than previously.
Marco – I’d answer that with a question. If Microsoft Office was included free with SQL Server, would you remote desktop into the SQL Server and run Excel there?
No, I wouldn’t.
But I did switch from wordperfect and lotus 123 to office some years ago.
I can see the problems with putting more services on one box. But they seem to be manageable, especially with the hardware you described.
So then to continue, would you run file & print services on the SQL Server? Those are free with Windows. How about hosting web sites?
It’s all about where you draw the line for performance. There are businesses who run everything on a single box and it works great – but in that case, why not go virtual? That way, if one of the services starts to need more power, you can grant it that, without impacting the database engine.
I know you’re keeping it SQL, but you spoke about virtual machines. One thing to bear in mind, when deplying sql onto a VM cluster and allocating multiple (4 in the example) vCPUs, the host machine makes your process wait until it has 4 physical cores to allocate to tbe vm.
Hyperthetical: If your host has a single quad core cpu and you’re running e.g. 2 other vms on it with 1 vcpu each, the SQL vm has to wait untill both the other 2 machines have finished their operation (controlled by the host) before its allowed cpu time for itself.
I would only allocate 2 vCPUs to SQL in this instance, or 3 and make the other 2 machines share a physical core (but then you get messed over by the new licensing model)
Or at least thats was true back when ESXi4.x were new, according to the chap that originally installed it
Wayne – that’s actually no longer true, hasn’t been true for years. Google for VMware coscheduling and you can find a lot of good white papers from VMware about the CPU scheduler improvements since 4. Thanks though!
Great article, but I have one thing to add. I know you mentioned you weren’t going into Software Assurance costs at the outset of this article, but I think they are very relevant for virtual machines. The fine print of SQL Server licensing states that in order to perform a vMotion/Live Migration to another box you must have SA. Since that is one of the most compelling reasons to virtualize any server, I would argue it is almost a mandatory cost for any virtual SQL instance.
Brian – yep, and with Microsoft’s increased release cadence, I’d argue it’s a mandatory cost for any instance, period.
I loved to read this line: “The problem is you, and your old-school 16GB memory fixation.”
And last time I heard on a place: This is a very powerful SQL Server, it has 4 GB of memory… WHAAAT??? (Well after seeing production boxes with 2GB, it’s not that bad… it depends 🙂 )
Brent, I’d likely go for a two-socket server such as an R720 with a single socket filled with 1x 8 core than 2x 4 cores in the same machine.
For SQL DB engine, you might or might not see different performance, but my gut tells me that just like in SSAS tabular, the overhead of communicating between CPU when they need data from each others banks will be in there somewhere
Hi Mark. Thanks for the input. I’ve seen some rough problems around OS freezes when using a single socket with things like antivirus software, VSS snapshots, and USB drives, so I’ve kinda abandoned the single-socket approach. Sure, in a perfect world we’d never use those things, but the reality is that they often get added when we’re not around.
Thanks though!
I’ve been searching all morning for some clarification on a licensing issue I’ve encountered, and I have not been able to come up with a solid answer. I have a physical machine that is core licensed for 2012 Enterprise Edition. We need to install an instance of 2008 (Standard or Enterprise) on the same machine, and we are being told that we have to license all of the cores all over again for this version.
This obviously doesn’t make any sense since you cannot squeeze 2x the power from the cores, and the licensing is based on power. I’ve read the section from the 2012 licensing guide about cross edition rights. It states that it will “allow customers to deploy an alternate (usually lower) edition in place of the currently licensed version.”
The part stating “in place of” is what is confusing me. We obviously want to keep our 2012 version installed alongside of the new 2008 version (feels terrible saying “new” when referencing 2008). Do you have any experience with licensing in a scenario like this?
Jeremy – honestly, this is just a scenario I would never recommend under any circumstances for performance and reliability reasons. I don’t recommend running mixed versions of SQL Server on a single piece of bare metal hardware. You’re so much better off with virtualization in scenarios like this.
I agree, but this is a pre-production environment and we’re just looking to save some money for this small and relatively insignificant application. We’re going to have to license 4 cores if we throw it on a new VM.
Thank you for the quick response.
JeremyH – if it’s pre-production, then you’re using Developer Edition, right? Then the license problem kinda goes away.
I walked into this project after it started. They had already licensed it at the Enterprise level. There are over hundred people or so using the instance, but it would have been much better to go with developer.
Perhaps I can convince them to just go with developer for this application. I just thought we could just install the 2008 instance on the already licensed physical machine to save money for now until we move to a production environment (where I do not think this application will still exist).
OK, at least now you know why you can’t find answers. 😉 They’re doing the wrong thing, heh.
Hi Brent –
I ran across your site – very nice – and have been reading old threads to catch up.
If you happen to see this comment, I’d be interested to get your take on this question:
– How do you handle customers that are pushing for VM-hosting of SQL Server, but then provision the db server VM with only 8GB or 12GB of RAM?
I thought this situation would be rare, but it has happened several times to us. In each case, up-front the word from the customer was “don’t worry, we have an incredibly powerful ESXi/HYPER-V/etc host”, but when implementation time came, it turned into “we can only give you 12GB on the db server, until we do our ESXi upgrade, which is X months away.” And then X slips to become X+2, X+4, etc.
I have tried to address the issue with humor, pointing out that we are supporting SQL Server on a VM that has less memory than my iPhone, and that gets a laugh, but the point remains – until the VM host upgrade occurs, no additional RAM is forthcoming. And in the meantime, our application performance suffers, we spend a lot of time chasing our tail on performance issues that would be solved with sufficient RAM, and no one is happy.
Our minimum server recommendations clearly state 32GB or 64GB, depending on the customer volume. It just seems that for sites that are pushing virtualization, too often the answer is “sure, we can do that” until it comes time to actually push the button and allocate 64GB to a VM. And by then we are months into the project… in the beginning we hear “ok, we’ll allocate the full memory later, because it’s so easy to add RAM with ESXi/HYPER-V/etc… so let’s just start with 12GB for now”.
We could charge more for sites that want to use VMs for SQL Server, to make up for the (expected) additional support costs, but I doubt that would go over well. We could refuse to do business with these sites, but we operate in a small industry and word of mouth is our sales force.
Our early customers (8+ years ago) have nicely-provisioned physical boxes, because until SQL licensing transitioned per-CPU -> per-Core, it was easy to justify a physical box with plenty of RAM and cores. But as you point out in this article, the push from MS is to minimize cores, and to favor VM setups. So it appears to be a problem that is going to get worse before it gets better.
Perhaps this comment is more of a rant directed at Microsoft, because the per-CPU -> per-core transition is squeezing customers, and naturally they are trying to save money anywhere they can. SQL licensing costs keep creeping up, and I feel like the frog in the pot… but I can’t tell how close the water is to boiling 🙂
Thanks for all the great posts, and best wishes for a successful future.
Regards,
-FrankC.
FrankC – well, you have to keep in mind who usually contacts us for help. By the time someone calls us in, they’re already unhappy with performance. (No one calls the trauma surgeon when they’re healthy.) Because of that, it’s usually really easy to get people to right-size hardware and capacity at that point.
Enjoy the old posts!
Ah, you make a good point. That is a potential solution for us as well, I think. If the customer insists on provisioning with 12GB of RAM, don’t fight the battle up front. Instead, wait for the performance issue, document it, and confirm it is due to lack of RAM. If the pain is bad enough, the site will act.
Thanks for the fast reply!
Exactly!
Just received a brand new production SQL Server on a VM with 4 GB of RAM. Just came here to read this over and remind myself that I’m not crazy.
Hi Brent,
Just stumbled across your blog and am finding it a fantastic source of real-world experience.
So perhaps you can help me with a question regarding a new server I’m planning for an in-house web application?
We are upgrading from 2x Xeon E5335 quad core processors and 32GB of RAM running SQL2005, which just about performs adequately but is old hardware and some year-end reports take 30 seconds to run.
I think we are memory and cpu speed constrained at present, rather than thread/core constrained, so I’m looking at something based around either two E5-2620v3 six-core processors, or a single E5-2650v3 10-core cpu and 64GB of ram to start with. I believe this memory will be be in NUMA configuration (HP Proliant DL series server).
Either way, SQL Server will run as a dedicated single host on Hyper-V.
So, how do I work out which solution (2x 6 cores vs 1x 10 cores) will give the best results?
Thanks!
Richard – great question. Are you using SQL Server Standard Edition or Enterprise Edition, and will you be licensing at the guest level or the host level?
Hi Brent,
Will be licensing at guest level using Standard Edition (2012 or 2014).
Will start off running SQL Server on about 6 or 8 cores (12-16 threads) based on the fact that I rarely see more than 6 cores (with no multi-threading) in simultaneous use on the current system.
Thanks for your advice!
Richard – OK, if you’re licensing at the guest level with Standard Edition, you want as few cores as possible. If you never see 6 cores in use, then set up 1 virtual socket with 6 cores. (Remember, you pay $2k per each additional virtual core you turn on, and hyperthreading isn’t free with Standard Edition licensing at the guest level.)
Hi, thanks for your article. I have just a silly question, sorry to bother. If I have 2 quad-core processors…that means that I have 8 cores…right? Thanks
[…] What’s the Smallest SQL Server You Should Build? – Brent explains why 96GB RAM really isn’t all that much – especially when you compare it to SQL Server licensing costs. […]