I’ve found myself answering these questions more than three times only during the the last week. All those questions coming from very different people with different profiles. Some being our customers, some being partners, and even from some coworkers. Such repetitive question definitely deserves an attempt to answer it through a post over here.
First thing I want to clarify is that the set of variables which are part of the equation that define a SQL Server 2012 SKU (Stock Keeping Unit) only include the language (English, Japanese, German, French, Spanish, etc), the edition (Enterprise, Business Intelligence, Standard, Express, etc), and the CPU architecture it was compiled/linked for (x86, x64, or IA64). As you can see, the license model doesn’t play part of the SKU. That means that when you plan to install an instance of SQL Server, the media that you pick up (from where you will invoke setup.exe) will be the one containing the bits for the edition, language, and bitness of your chose.
Let’s imagine that given the requirements of your project, the components, features, and limitations of the Standard edition in English and targeting the x64 architecture are just those that fit the need of your database platform. Don’t run mad looking for an installation media that matches those three attributes and that also says it is the one to be used for the Per Core licensing model or the one to be used for the Server+CAL licensing model. There’s no different media or different set of binaries depending on the licensing model you plan to use.
Actually, even during the setup process there’s no place where you have to indicate the model under which you are licensing that new installation.
If you install an Enterprise edition of SQL Server, for example, there is no other licensing option available for it than the one base on the number of cores available on the server (i.e. Per Core). Therefore, if you install it on a physical hardware with 24 cores you have to pay for each of those 24 cores no matter what use of them SQL Server does. So, with the Enterprise edition the options are trivially simple and everyone understands.
However, people seems to not have very clear how does that work when they install a Standard edition of the product. Basically because they have to mix and match the following two ingredients: 1) It happens that the Standard edition can be licensed not only Per Core, but can also be licensed via the Server+CAL (Client Access License) model, and 2) the intentionally limited maximum compute capacity of a Standard edition is said to be a maximum of 4 sockets or 16 cores (the lesser of the two values based on the architecture of the processors in your particular hardware. I’ll mention a few examples later on to clarify how this goes.)
The concern/question typically comes when someone buys a physical server with more than 4 sockets (regardless of the number of cores in each of those sockets) or with more than 16 total cores (whether or not the number of sockets exceeds that imposed hard limit of 4.) And the question commonly goes like this: “We bought a server with 4 sockets, each of them with 6 cores (hexacore) where we will install our instance of SQL Server 2012 Standard edition. Since that leaves us with a 24 core system, can we install that Standard edition of SQL Server on it? And if so, how are we supposed to license it? And how many of those cores will SQL Server use?”
Our SQL Server 2012 Licensing Reference Guide, in its “SQL Server 2012 Licensing Models” declares that if you choose to license your instance of SQL Server (any of the editions you can license through that model) Per Core, you must know that:
“When running SQL Server in a physical OSE, all physical cores on the server must be licensed. Software partitioning does not reduce the number of core licenses required, except when licensing individual virtual machines (VMs). The minimum number of licenses required for each processor on the server still applies. A physical operating system environment (OSE) is configured to run directly on a physical hardware system and is all or part of an operating system instance.”
Now the technical part of the story goes like this. When SQL Server service starts, it finds out what edition it is. If it encounters it is an Standard edition, then it counts the total number of available sockets on the server and the total number of cores. With those numbers it limits the number of instances of SQL OS schedulers to a maximum driven by the lower number of those two. What does it exactly mean? I’ll show you several configuration examples below. The value in the rightmost column of the table will be highlighted in yellow when it is the total number of cores what drove SQL to cap down the maximum number of schedulers to 16, and in orange when it is the maximum number of 4 sockets which was the main driver of the limiting decision.
Cores per socket
Total cores in the server
Instances of SQL OS Schedulers
The bottom-line one can easily derive from all these facts, is that it doesn’t make sense (in terms of economy) to install an Standard edition of SQL Server in a physical server with more than 4 sockets or 16 cores when you plan to license it through the Per Core licensing model. If you do so, you will have to pay licenses for cores SQL Server will not use, typically, concurrently when the workload of the server grows requires such computing power.
However, you can still install an Standard edition of SQL Server 2012 on a server with more than 4 sockets or more than 16 total cores, and license it Per Server+CALs. In such case, the service will ask the same question during startup and will enforce the limitation described above, but you want have to pay for all the cores in the machine, whatever the total number of is.
Now, when does it make sense to use this alternative licensing model? When you deploy SQL Server Standard edition in scenarios where you can easily count users/devices and the total licensing costs are lower than using the Per Core licensing model.
Finally, from the technical standpoint is interesting to know that what SQL limits is the number of schedulers it creates to schedule the workload executed by its worker threads. However, by default SQL doesn’t affinitize the worker threads to any particular core. Deciding in what core should a given thread be run is left to Windows executive’s scheduling mechanism. Any SQL Server worker thread could run in any core of that server, there could even be more than 16 threads (if we add to the equation any worker threads running in preemptive mode) concurrently running into different processors. But what the limiting factor enforces is that there will never be more worker threads running in non-preemptive (cooperative) mode than the number of instances of SQL OS schedulers created for that SQL Server.
All the details are present in the SQL Server 2012 Licensing Reference Guide, but I thought valuable publishing this post here to clarify what is said there around the question at hand.
Would it be possible to install 2 instances of SQL Server Standard edition on a 24 core machine in such a way that Instance1 has an affinity to Core1-12 and Instance2 has an affinity to Core13-24, thereby letting me harness the full potential of the server.
Sure you can affinitize SQL workers of each instance to whichever cores you wish. However, even if you don't set an affinity mask the OS is free to schedule any thread on any of your 24 cores.