Adventures in SQL Server Licensing

Over the weekend, we upgraded from SQL Server Standard Edition to Enterprise Edition. (We also upgraded from 2008 to 2016, hooray!) There were of course a lot of factors driving this, but one of the more compelling ones was the desire to utilize all the processors on the host machine. SQL Server Standard is limited to 24 logical processors (official source: Compute Capacity Limits by Edition of SQL Server - see the chart near the bottom of the article). SQL Server Enterprise Edition however can use as many processors as the host machine has to offer (see same source). Our host machine has 96 logical processors, so we were only using a quarter of the capacity, so this was an important jump forward.

Imagine our surprise and alarm to come in the morning after the upgrade to see that the server was performing very poorly. We quickly discovered that it was still not using all the processors, and even worse, it had now dropped down to only 20!

After some research, we were able to discover that we had installed the wrong kind of Enterprise Edition! Enterprise Edition comes in two kinds: Server/CAL and Core-based. CAL stands for Client Access Licensing, and is a licensing model that caps how many clients can connect to the server at a time. As you can imagine, this is hard to estimate and significantly limits scalability, so it is not a popular option. Almost everyone goes for Core-based licensing, and that's what we had purchased and needed, but it was not what we had installed. Server/CAL Enterprise Edition is limited to 20 cores (40 if using hyperthreading) [see the footnote to the afore-mentioned chart].

To confirm that this was our issue, we checked the SQL Server logs at when the server started up. We saw this line (emphasis added):

SQL Server detected 4 sockets with 24 cores per socket and 24 logical processors per socket, 96 total logical processors; using 20 logical processors based on SQL Server licensing.

We also checked the edition by running SELECT SERVERPROPERTY('edition') and got Enterprise Edition (64-bit).  This seemed okay, until we realized that we needed Enterprise Edition: Core-based Licensing (64-bit).

We were afraid that we were going to have to reinstall SQL Server, but the fix turned out to be simpler than that. We had to download the right installer, the one for SQL Server 2016 Enterprise Edition Core, and then do an Edition Upgrade from the Maintenance menu. This article has a good walkthrough of how to do it: see the section "UPGRADING SQL SERVER 2008." The screenshots are from an older version of the installer, and it suggests you'll have to enter the product key manually, which you probably won't have to, it will probably be auto-filled. (This MS Docs article gives a more up-to-date and complete procedure, but it doesn't have any accompanying visuals.)

Once we reached the end of the wizard and clicked "Upgrade", the change was very quick, and once the SQL Server service was restarted, it went up to using all 96 cores! We were able to confirm that the edition was now Enterprise Edition: Core-based Licensing (64-bit), and saw the startup message changed to:

SQL Server detected 4 sockets with 24 cores per socket and 24 logical processors per socket, 96 total logical processors; using 96 logical processors based on SQL Server licensing.

It was of course a huge relief to figure this out, but it was hardly a quick process. We were down for almost three hours. So I'm putting this out there in the hopes that other folks won't have to go through the same pain. And it seems like a lot of people have felt this pain - Microsoft clearly needs to do a much better job of labeling and explaining this quirk of SQL Server installation. (Microsoft needs to massively streamline and simplify all SQL Server licensing, but that's a topic for another time.)

So, final take-aways:

1. When installing SQL Server Enterprise edition, make certain you have the right installer for the license type you have purchased, whether it be Server/CAL or Core-base Licensing.
2. If you find you have installed the wrong type of SQL Server Enterprise Edition, you can correct the issue by doing an Edition Upgrade from the correct installer.
3. SQL Server licensing is way too complex.

(Thanks to this DBA.SE answer and this blog post that helped guide us down the right path.)