The Expert’s Guide to SQL Licensing Rules
Part 1
Jump To:
Editions
How to determine SQL Edition
Downgrade Coverage
Licensing Models
Licensable SQL Components
Licensing SQL in a virtualized environment
SQL Server Pricing Models
SQL licensing can get pretty complex, especially since the switch to Core based licensing in 2012. We’re going to try and make it as simple as possible. Let’s start by discussing the different editions of SQL Server that are available today.
SQL Server Editions
SQL Server is currently available in 4 editions;
Express, Compact, LocalDB (free, comes with limitations)
Developer (free for development use)
Standard (requires a license for production use)
Enterprise (requires a license for production use)
We won’t go into the different feature sets of each edition as those are detailed extensively on Microsoft SQL page here. What’s important to know is that if you are using SQL server in a development environment, its best to use the Developer edition. SQL Server Developer Edition contains all the features of the Enterprise edition, but is completely free to use strictly for development purposes. Even though you can use Standard or Enterprise in a dev environment if you have an active MSDN subscription, it becomes difficult to manage and track dev/test devices unless you have a clear naming scheme for the devices (ie. DEVDEVICE123, TSTDEVICE123 etc.) For simplicity, its recommended to use the dev edition in a dev environment.
Older editions such as Datacenter, Business Intelligence Edition, Workgroup and Small Business Edition are all discontinued.
Here are the current product replacement mappings:
| You Purchased | Replaced by |
|---|---|
| Small Business | Standard |
| Workgroup | Standard |
| Datacenter | Enterprise |
| Business Intelligence | Enterprise |
Based on the table above, if you had an existing Datacenter license for example and continued paying Software Assurance, you would be permitted to install a more recent Enterprise edition in its place. (Your license should also have been migrated to the corresponding replacement at the time of true-up as well)
How to determine which version and edition of SQL Server Database Engine is running
To determine the version of SQL Server, you can use any of the following methods.
Method 1: Using Microsoft MAP Toolkit. MAP can scan and determine the edition of all the SQL instances on your network, and this is the preferred method. However, sometimes you can’t use a network tool to access certain devices which may reside outside the network on in a segregated DMZ. In these cases here are some alternate methods that can be used to determine the SQL Edition:
Method 2: Look at the first few lines of the Errorlog file for that instance. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files. The entries may resemble the following:
2011-03-27 22:31:33.50 Server Microsoft SQL Server 2008 (SP1) –
10.0.2531.0 (X64)
March 29 2009 10:11:52
Copyright (c) 1988-2008 Microsoft CorporationExpress Edition (64-bit)
on Windows NT 6.1 (Build 7600: )
This entry provides all the necessary information about the product, such as version, product level, 64-bit versus 32-bit, the edition of SQL Server, and the OS version on which SQL Server is running.
In this example, Express edition has been identified.
Method 3: Connect to the instance of SQL Server, and then run the following query:
Select @@version
| 1 | Select @@version |
An example of the output of this query is the following:
| 1 | Microsoft SQL Server 2008 (SP1) – |
| 2 | 10.0.2531.0 (X64) |
| 3 | March 29 2009 10:11:52 Copyright (c) 1988-2008 Microsoft Corporation |
| 4 | Express Edition (64-bit) |
| 5 | on Windows NT 6.1 <X64> (Build 7600: ) |
Again, you can see that Express edition has been identified by the query.
Downgrade Rights
Volume licensing downgrade rights also allow you to to downgrade to a previous version, meaning installing a version of equal or lower edition and version rights in place of the version you purchased. Why would someone want to do this? Mainly for compatibility issues. If you’ve built and tested your database on a previous edition and don’t have the resources to upgrade to the latest and greatest version of SQL Server, you have the option to stick with the lower version. Also in the case of Software Assurance, your license rights may be upgraded to a higher version but you may not want to force an updated install of your SQL instance.
Q: How do I keep track of all the downgrade options available to me?
The AssetLabs SQL Server Downgrade Matrix
A: We’ve put together this simple downgrade Matrix for SQL Server. This type of coverage is built in to our license services such as Engage.
How to read this matrix: Licenses for products on the leftmost column cover installations of the products to the right if there is a 1 in the cell.
Licensing Models
How SQL Server licenses are Sold
SQL Server 2019 and 2017 are currently available in either Server + CAL or Per Core options. Enterprise Edition is only available under the Per Core model, whereas Standard can be purchased in either licensing model.
To add some complexity, SQL Server licensing is licensed differently depending on whether you’re deploying SQL Server in a physical or virtual environment.
SQL Server – Per Core Based Licensing
Per Core licensing allows for an unlimited amount of users or devices to access SQL Server (both internally and externally.)
Advantage: No CALs required therefore no need to quantify the number of users or devices accessing the SQL Server.
As an example, a SQL server that is hosting a public website where you wouldn’t be able to count the number of users would require a Core based license and no SQL CALs.
How To License Per Core in a Physical Environment
Licenses are assigned to all of the physical cores on the host (physical) server, at a minimum of 4 core licenses per physical processor.
Example 1 – SQL Server with 2 processors, each with 6 cores:
- Determine how many cores there are per processor (always with a minimum requirement of four cores per processor)
2. Count the total number of cores
3. A total of 12 cores are present, therefore 6 SQL Server 2 Core Packs (6×2 = 12 core licenses) are required. (Licenses are sold in 2 core packs)
Q: How do I keep track of all the SQL Server instances, editions, number of processors, cores, VM to host relationships etc.. necessary for licensing??
A: Its true, if you’ve got more than a handful SQL Servers in your environment it can quickly become difficult to track. After years of license assessments under our belts and spending countless hours handcrafting these SQL License tracking sheets, we’ve automated the entire process through the use of Microsoft MAP and our own Server Sheet Reporting engine.
Here’s how it works:
Collect your inventory data from Microsoft MAP
After you scan your network using Microsoft’s free MAP toolkit which will discover every instance of SQL running in your environment, the edition, and any licensable components, (yes even if you only install a component of SQL it may be licensable, more on this later) you then use our custom Data Fidelity tool called AssetLabs Echo. Using Echo, you are able to validate whether you got all your servers, and which ones you missed. The problem with using MAP on its own is that it will look at all your AD device records from the beginning of time and show a horribly low percentage count of inventory success, namely because its comparing what it inventoried to what exists in your AD.
What Echo does is provides a success rate based on only active devices, instead of the stale device records sitting in your AD (and lets face it who has the time to clean up AD regularly.) So instead of something like 40%, you should see numbers like 90% success. Once you’re over 95% you can upload the data to your own account on our secure web portal.
Q: Why can’t I just use my current inventory tool like SCCM, Altiris or ServiceNow?
A: The problem with most inventory tools like SCCM is that they don’t capture the Edition of applications like SQL Server and Windows Server, and the Edition is essential to proper licensing. The difference between SQL Express edition and Enterprise edition is $28,000 at a minimum! So you can see that it is of utmost importance to grab the edition information which MAP is able to do (in most cases, see point 2 below.)
- Once your data is uploaded, it needs to be cleansed, or what we call Normalized. In this process, we scan the data for missing entries and fill in the blanks wherever possible to to add versioning and publisher information. Why is this important? So you can compare apples to apples. If you have SQL Server 2016 and SQL Server 10.5, which is the higher version? Our normalization will fix this.
Red values were automatically inserted by our normalization engine, resulting in quality data that is essential to accurate licensing.
We can see from the above example that SQL version 13 is higher than SQL 2008 R2 which happens to be version 10.5
Licensable SQL Components
2. Now you might think that you have all the necessary information but wait… remember we said MAP can collect the Edition information for SQL? Well, that’s partially true. Its true for the main SQL Server Engine install, but doesn’t always capture the editions of the SQL Components. And here’s the tricky thing with SQL, if you install any of the components listed below on their own, they are all licensable! Meaning, if any of the components are found on a device, even if SQL Server engine isn’t installed, they still require a full SQL Server license!
Which SQL components are licensable?:
SQL Server Analysis Services (AS)
SQL Server Reporting Services (RS)
SQL Server Integration Services (IS)
SQL Server Master Data Services (MDS)
SQL Server Data Quality Services (DQS)
SQL Server R Services for Windows
So now you have another thing to keep track of – SQL Components!
From the Microsoft SQL Server Licensing Guide:
”The software components of a single SQL Server 2016 license cannot be separated. Any OSE running any of the licensed components of SQL Server 2016 requires a license. For example, if the SQL Server DB is deployed in one OSE and SQL Server RS is deployed in another, both OSEs must be fully licensed for SQL Server 2016 accordingly.”
The intelligence necessary to identify licensable sql compontents is built into our services such as Engage and Prelude. You can upload your inventory and we’ll automatically be able to identify all the licensable components in your environment. Contact us for information.
Now let’s look at a few different scenarios to see what would count towards a license requirement.
Simple Scenario
| Device A. | Device B | Device C. |
|---|---|---|
| SQL Server (Standard Edition) | SQL Server Reporting Services (Standard Edition) | SQL Server 2012 (Standard Edition) |
| SQL Server Reporting Services (Standard Edition) 2012 |
Device A requires a SQL Standard license.
This should be obvious
Device B requires 1 SQL Standard License
Even though this device only has Reporting services and not a SQL database engine running, its a licensable component and therefore requires a FULL SQL Standard license.
Device C also requires 1 SQL Standard License, but it covers both the SQL Engine and the Reporting Services (as long as they are both the same version and editions. If they are not, we’ll look at that more closely in the next example.)
Complex Scenario
| Device A | Device B | Device C |
|---|---|---|
| SQL Server (Enterprise Edition) 2014 | SQL Server (Standard Edition) 2014 | SQL Server (Standard Edition) 2014 |
| SQL Server Reporting Services (Standard ) 2012 | SQL Server Reporting Services (Enterprise ) 2012 | SQL Server (Enterprise ) 2008 R2 |
Device A needs a single SQL 2014 Enterprise license, which covers both the Enterprise 2014 AND the 2012 Standard Reporting services (See downgrade matrix above)
Device B needs a SQL Standard 2014 license AND a SQL 2012 Enterprise license to cover the SQL reporting services component. (2014 standard will not cover enterprise components, even a lower version.)
OR
A single SQL 2014 Enterprise license will cover both instances.*
Device C needs a SQL Standard 2014 license AND a SQL 2008 R2 Enterprise license. This is called license stacking. (2014 Standard does not cover any lower version of Enterprise.)
OR
A single SQL Enterprise 2014 license will cover both instances.*
*A SQL Server license allows for unlimited SQL instances of the same or lower version-edition within a single OSE (Operating system environment.)
Alright you say, you think you understand the licensing rules, but now you have to look at the SQL components, editions and versions on every device and determine which one(s) are licensable? How does one manage this with only so many hours in a day?…
Again, we solve this for you through our SQL License Optimization Engine. Here’s how it works.
SQL License Optimization Engine – How it Works
Step 1. Filter out the noise
After your data gets normalized, we need to filter out the noise. Each SQL component on each device is assigned a license type (licensable or non-licensable component.) We filter out the non-licensable components because we don’t care about them.
Here’s the raw SQL inventory for a single device from SCCM:
As you can see in the above screenshot (fig. 4), we have 97 software titles with SQL in the title on a single device. No information indicating which ones are licensable.
And here’s the Streamlined version, with License Types and Categories added by AssetLabs’ normalization process:
We’ve gone from 97 SQL titles to 18 licensable components. We need to get this down to 1 SQL title that represents the device, the highest Version-Edition. This is done automatically using the coverage (Downgrade) Matrix in figure 1. The result is as you see, SQL Server Enterprise Core (2014) in the state of Production, all other components are Exempted.
Now we repeat this process for all other devices with SQL (all automatically of course.) What we’re left with is a single licensable representation of SQL on each device.
Getting all that data into a report for SQL licensing
So now we have all the licensable SQL Server instances identified per device, but that’s not enough to determine license requirements. Remember from Part 1 that SQL can be licensed by Server, Processor or Core count, and depending whether it is running on a physical device or virtualized is licensed differently. We need to incorporate all these details into the report. For this we grab the virtualization relationships from VMware VCenter through the use of a free utility called RVtools. RVtools captures all the host, cluster and VM information along with the number of vCPUs (virtual CPUs) necessary to license SQL correctly. We marry this information up with the MAP inventory information to produce a single output report. If using MS Hyper-V , the Hyper-V information is captured by MAP (with the exception of clustering information.)
Behold, the SQL Server License Assessment report from AssetLabs. This automated report outputs all the necessary details in an easy to understand report formatted to group clusters and hosts, and separate physical servers from virtual ones. You can quickly identify if the device is virtual, what host its sitting on and whether that host has other SQL instances that can share a license (Enterprise license.)
Here we have everything needed to properly allocate licenses.
In the above image (Fig 7) highlighted in yellow we are looking at a single SQL Server instance on VM ESIHM1CATDB1, it is a 2014 Standard Edition.
Example 1
The Per Core option is often the preferred licensing option when:
- The SQL Server is external facing and an unquantified number of users or devices access the server
- Deploying SQL Server Enterprise – the Enterprise edition does not support the Server + CAL licensing model
- The cost of licensing SQL Server Standard is too high based on server access
SQL Server – Server + CAL Licensing
When licensing SQL Server Standard under the Server + CAL option, customers must assign a license to the physical server hosting any operating system environment (OSE) running SQL Server and acquire a SQL Server CAL for each user or device that accesses the server.
Note: The SQL Server license allows unlimited SQL instances to be deployed on the OSE (physical or virtual).
The Server + CAL option is often the preferred licensing option when:
- Smaller SQL Server implementations exist and SQL is the supporting database for another application or smaller Intranet; and
- The number of users or devices accessing SQL Server can be quantified and Server + CAL is more cost effective than the Per Core model
Licensing SQL Server in a Virtualized Environment
When deploying SQL Server in virtualized environments, customers have the choice to license either the individual virtual machine(s) or for maximum virtualization by assigning SQL Server Enterprise Per Core licenses with Software Assurance (software maintenance) to the physical server. Again, you can choose between Server+CAL or per Core licensing models.
SQL Server – Per Core Based Licensing in a Virtualized Environment
When licensing SQL Server under the Per Core model in a virtualized environment, the following will need to be identified:
Example 1 – This SQL Server has three virtual machines, VM1 has two virtual cores, VM2 has four virtual cores, VM3 has 6 virtual cores.
- Ascertain how many virtual cores there are in each virtual machine (keeping in mind the minimum requirement of four cores per virtual machine)
- Count the total number of virtual cores
- VM1 has two virtual cores, but doesn’t meet the minimum requirement of four virtual cores. VM1 will therefore require four core licenses
- VM2 has 4 virtual cores, therefore needs 4 core licenses.
- VM3 has 6 virtual cores, therefore needs 6 core licenses.
Note: Even if the physical server has less cores than the total number of virtual cores in use for SQL, in core-based licensing all virtual cores must be licensed. The exception is if you use SQL Enterprise to license all cores on the host, you can have unlimited virtual cores on the same physical server, see details below.
Licensing for Maximum Virtualization
Starting with the SQL Server 2012 Enterprise Edition, customers who have licensed all physical cores on the server can run an unlimited number of instances of the software in a number of OSEs (physical and/or virtual) equal to the number of core licenses assigned to the server. For example, a four processor server with four cores per processor—fully licensed with sixteen core licenses—can run SQL Server software in up to sixteen VMs,
regardless of the number of virtual cores allocated to each VM.
Note:Licensing of SQL Server Enterprise in a virtualized environment is only supported by the Per Core license model.
SQL Server – Server + CAL Based Licensing in a Virtualized Environment
When licensing SQL Server Standard under the Server + CAL model in a virtualized environment, customers are required to purchased one server license per virtual machine running SQL Server irrespective of the number of virtual processors allocated. The following will need to be identified:
Example 3 – A physical server has two SQL Server virtual machines (Virtual Machine 1 and Virtual Machine 2), and two users are accessing the SQL Server virtual machines.
- Ascertain how many virtual machines have SQL Server deployed
- Virtual Machine 1 and Virtual Machine 2 – (2) Server licenses required
- Count the total number of users accessing the SQL Servers
- Two users are accessing the SQL Servers – (2) SQL Server User CALs are required
Which licensing option is best for me?
SQL Server Standard can be licensed under the Per Core or Server + CAL license options. It is recommended that you engage your Software reseller to determine which option best suits your SQL Server requirements (factoring in server role, access, technical requirements etc.) as there will be a tipping point where the Per Core model is more cost effective and easier to manage. The following tables give an idea as to whether server+Cal licensing model is more cost effective over a core model, however this doesn’t take into consideration that users can use CALs to access any equivalent version SQL database in the environment – meaning you only need to buy the proper version cal one time for a user to access any number of SQL instances.
SQL Server MSRP Pricing Models:
Enterprise Core: $7,128 per core
Standard Core: $1,859 per core
Standard: $ 931 + $209/device or user CAL
Tipping point Server+CAL model vs Standard Core:
| 4 Core Cost | 6 Core Cost | 8 Core Cost | Max # of Users | |
| Standard Core | $7,436 | $11,154 | $14,872 | Unlimited |
| Standard Server+CAL Maximum Number of Users at equivalent Price | 31 users | 48 users | 66 users |
Tipping point Server+CAL model vs Enterprise Core:
| 4 Core Cost | 6 Core Cost | 8 Core Cost | Max # of Users | |
| Enterprise Core | $28,512 | $42,768 | $57,024 | Unlimited |
| Standard Server+CAL Maximum Number of Users at equivalent Price | 131 users | 200 users | 268 users |
Some use cases to be aware of
As we have touched on here, there are a number of different licensing models available for SQL Server depending on the scenario and use case. Some of these may be relatively straight forward, others not so and the commercial implications could be significant. I would encourage open dialogue with the Database Administrators within your organizations to ensure there is some governance and understanding as to how SQL Server is deployed within your corporate environment. I intend to delve a little deeper in future posts, but in the meantime some items to consider include:
- The role of the SQL server – is it internal or external facing
- Can the number of users or devices accessing the database be quantified?
- Is access to the SQL server enabled via multiplexing?
- Do you have active Software Assurance on your SQL estate and as such access to benefits such as passive failover licensing?
- Is SQL Server deployed as part of a clustered environment?
- Does SQL Server reside in a Production or a Non-production environment?
- Are SQL Components such SQL Reporting Services deployed as standalone installs without SQL?
Additional Resources
- How to identify your SQL Server version and edition
- SQL Server Internal Database Versions
- Microsoft SQL Server Support Lifecycle
- Microsoft SQL Server Home
- Microsoft SQL Server Developer Center
- Microsoft TechNet: Microsoft SQL Server
- Microsoft Knowledge Base
- Sqlservr.exe versions
Still need help with Licensing SQL Server in your environment? Take a look at our SQL Server Licensing Services or contact us for more information.