As a consultant, I sometimes see SQL Servers with high CPU core counts relative to their workload. For example, the situation that inspired this post was seeing half a dozen SQL Servers with ~50 cores each, hundreds of cores total - yet they consistently had just 1-5% CPU usage. Here's what I said to management:…
Businesses who need high availability and disaster recovery usually want three servers:
A primary SQL Server where all the writes & reads take place
A high availability SQL Server sitting right next to the primary, in the same data center, keeping up with its writes, and
A disaster recovery SQL Server sitting in another city or region, keeping up with the writes, but not necessarily in real time - the writes here are usually asynchronous
Six months ago today, you installed SQL Server Evaluation Edition. You told yourself you'd take care of that sooner or later, and...you forgot. Now, your SQL Server won't start:
Error 17051: SQL Server evaluation edition has expired.
Awww, shucks. Good news though! You don't have to uninstall & reinstall from scratch - there's an easier way.
You've gotten through a lot of this training. You've tried fixing the problem with indexes, query tuning, and judicious application of various configuration switches. You've been watching webcasts and using free scripts, but...
Things just aren't getting better.
Listen, I can explain. Really.
We had a client with a 5GB database, and they wanted it to be highly available. The data powered their web site, and that site needed to be up and running in short order even if they lost the server - or an entire data center - or a region of servers.
Instance stacking is the technique of installing multiple instances of SQL Server on the same Windows instance. For example, you might have a VM or server named SQLPROD1, and have:
SQLPROD1 - default instance of SQL Server, say SQL 2016
SQLPROD1\SQL2014 - for our older apps
SQLPROD1\SharePoint - because it's supposed to be on its own instance
SQLPROD1\development - for our QA & testing
"We'd like to offload our reporting queries to a separate SQL Server."
The first costs are fairly obvious.
Hardware and storage - even if you're running it in a virtual machine, you need to account for the costs of say, 4 cores and 32GB RAM. Not only will you need storage for the databases, but you'll also need to decide whether this server gets backed up, and copied to a disaster recovery data center.
A really odd choice
I've talked a bit about my annoyance with hardware limits for Standard Edition here and here.
If you're a bunch of SQL-savvy developers who understand the Enterprise Edition features that are now available in 2016 SP1, great. You're a very small minority of shops. I'm talking about places like StackOverflow and kCura.
Starting with today's release of SQL Server 2016 Service Pack 1, Standard Edition now has a lot more of the features of Enterprise Edition. Here's the list from Microsoft's announcement post:
Performance features - in-memory OLTP (Hekaton), in-memory columnstore, operational analytics
Data warehousing features - partitioning, compression, CDC, database snapshots
Some security features - Always Encrypted, row-level security, and dynamic data masking
In the classic spirit of my How to Think Like the Engine class, let's go take a look at the StackOverflow.Users table and find all the users named Brent. There's a DisplayName field, and I'm going to be querying that a lot in this blog post, so I'll go ahead and create an index on it, then look for the Brents:
Windows Server 2016 is now licensed by the core, but that's not really the big problem. The bigger problem is buried in the licensing guide (PDF):
"A minimum of 16 core licenses is required for each server."
"A minimum of 8 core licenses is required for each physical processor."
Microsoft charges $14,256 for a 2-core pack of SQL Server 2016 Enterprise Edition.
So how much hardware can we get for $7,128?
With the release date for 2016 finally announced Everyone can start gearing up to gaze upon its far shores from the 2008R2 instance they can't or won't upgrade for various reasons. I'm excited for a lot of the improvements and enhancements coming along, and generally hope I'm wrong about customer adoption. One annoyance with the…
Two things: SQL Server is coming to Linux, and no, this isn't April 1st, although the meeting minutes kinda read that way.
Let's zoom back for a second: if you want to expand an existing business, you have two good options:
When you build SQL Servers in your data center, and you license the host with Enterprise Edition, you get the ability to run as many virtual SQL Servers as you want on that hardware.
In the cloud, though, you haven't had that luxury. In the past, with Azure VMs and Amazon Web Services' EC2 VMs, you've had to license each individual VM. You weren't able to guarantee that your VMs all lived on the same host, so you had to pay through the nose.
Licensing is really complex, but as long as you know these seven rules, you can go a long way: If you query it, you have to license it. (Backups and corruption checks are not considered querying.) You have to license all of the processor cores that the operating system sees, even if you're not allowing SQL…
Here's some of the reasons companies usually virtualize their SQL Servers:
Cost savings on hardware
Cost savings on Windows OS licensing
Cost savings on SQL Server licensing
Protect against the failure of a single hardware element
Leverage extended features for Disaster Recovery
Automatic load balancing across multiple hosts
Easier hardware replacement/migration
It's a bummer that SQL Server 2012 Standard Edition only allows you 64GB of memory. And it's good news that the limit goes up to 128GB of memory in SQL Server 2014.
But are you getting as much memory as you can out of Standard Edition?
If you don't license all of your SQL Server's CPU cores, you need to pay particular attention to your server hardware.
Say you're using an HP DL580 with four CPU sockets, each of which has a Xeon processor plugged in with 10 cores. That's a total of 40 real cores, or 80 logical processors if you've enabled hyperthreading.
You have a sneaking suspicion that your servers aren’t all paid for, and you need to get a rough idea of how SQL Server licensing works. You’ve never bought a box of SQL Server before, and you have no idea where to get started.
Microsoft Certified Master Brent Ozar will break it down into a few simple, easy-to-understand slides and show you the most popular licensing options. He’ll also explain 3 classic licensing mistakes and help you avoid ‘em in this 20-minute video.
