Licensing for Non-Prod

Starting with SQL Server 2014, Developer edition became free for Visual Studio Dev Essential members which is free to join. If you are not a member, I highly recommend signing up by clicking here and taking advantage of some of the awesome perks including free training, Azure credits, developer tools, etc.

Developer edition is essentially the same thing as Enterprise edition (just with a different name) and should only ever be used on non-prod systems.

Prior to this, you either had to have enough MSDN\Visual Subscription licenses for non-prod to cover those accessing it or you had to pay ~$50 per user. If you are running older versions in non-prod and are not covered under a subscription model, then I would recommend speaking to your licensing rep about that to see if you need any additional purchases.

One of the biggest issues I have had with Developer edition is that it does not reflect my production server build if I am running Standard edition in production. The last thing you want is to have something developed or tested using a feature set that is not going to be available once it gets to production. My understanding is that you can run Standard or Enterprise (though why would you since Developer is the same) in non-prod, however, you must be covered under MSDN\Visual Studio subscriptions to do this. Or another option is there are several articles posted online about how to make Developer edition installs function like Standard edition though have never tried this. A quick google search will lead you down this path if interested. The good news is that there are more and more features being added to Standard edition in later versions so the issues with Developer edition vs Standard may not be an issue.

If you have any questions or concerns about whether you are covered, I would highly suggest posing any questions to your Microsoft or licensing rep.

Basics for SQL Licensing

To understand the basics of licensing, I recommend that you read the following blog post that goes through general information about licensing (Basics of SQL licensing). While the license calculator should capture a lot of this, you may be interested in reading more about it for yourself.

Licensing physical server with hyper-threading

Whenever you are licensing a physical server by cores, make sure you only license the physical cores. Currently you are not required to license cores that show up as a result of hyper-threading being enabled.

Be careful how you check this as well. If you are looking at SQL Server properties or the sys.dm_os_sys_info table, it may show logical CPU which is not always correct. I am yet to find a script that accurately captures the physical cores but will gladly update this if I do. I usually check system information, look at the hardware specifications or speak to my hardware guys to determine the actual physical core count.

You definitely do not want to be paying for more licenses then you need.

SQL Editions

When building a SQL Server, it is important to make sure you are selecting the correct version. To keep licensing costs as I can, I tend to recommend using the cheapest version possible while still accomplishing what you need to.

Here is a list of the primary versions you may see and a brief overview of each.

Enterprise ($$$$) – Full version of SQL. Used for applications requiring mission critical in-memory performance, security and high availability

Standard ($) – Slightly scaled versions of SQL. Typically used for mid-tier applications and databases

Web ($) – Web hosters and Web VAPs

Express – Free version of SQL

Developer – Non-prod use

If you are using Enterprise edition, make sure you need it and the cost difference makes sense.

Here is links to the most recent versions of SQL Server that describe what is available in each.

SQL 2017 –

SQL 2016 –

SQL 2014 –


SQL Server Pricing

The price that you pay will probably vary from organization to organization since it will largely depend on what offerings\agreements you have in place with Microsoft or Microsoft re-seller. I highly encourage you to work directly with someone on purchasing since they can may be able to work wonders on what your underlying cost. And just think, at minimum you are paying MSRP cost anyways.

I am including the MSRP pricing below, however, keep in mind what you pay may be very different. But this begins to give you and idea of what your cost may look like. Note: These costs are based on SQL 2017 pricing.

Enterprise – $14,256 per license which includes 2 cores

Standard – $3,717 per license which includes 2 cores

Standard server + CAL – $931 server license + $209 per CAL

Express – Free

Developer – Microsoft made Developer edition free starting with SQL 2014. For prior versions, check with your licensing rep. You may be covered under a visual studio\MSDN subscription or you may need to license per user

In addition to the license cost, you also need to factor in Software Assurance. As a starting point, factor in around 30% of the cost of the license to cover this to be paid yearly. With some agreements though, this may be bundled together with your licenses.

Licensing Minimums

When licensing SQL Server, there are a couple of minimum requirements that you need to consider.

If you are licensing by cores, then you need to be aware that 1 SQL license equals 2 cores (either physical of virtual). For example, if you are licensing 24 cores, you will need 12 licenses.

Be aware also, that core licenses are only sold in 2-packs so if you have a server with an odd number of cores, you will need to cover that one core with a full license. For example, if you have a 7 core server, then you would still need 4 licenses.

For a physical server, there is a 4 core minimum per physical processor. This means that you must purchase at minimum 2 licenses per physical processor. For a virtual server, there is also a 4 core minimum for licenses so, every virtual machine you build that is running SQL, requires at least 2 licenses.

What is Software Assurance?

One of the most important aspects of SQL licensing is making sure you understand why you need software assurance and how miserable your life will be without it.… Just kidding (sort of). Many things that you used to be able to do without this, now requires it so it does become more difficult to manage without it. Throughout this article, we will look at why…

First off, what is software assurance (SA)? Well, quite basically its Microsoft’s version of a paid maintenance program and can be used to cover various Microsoft products (in our case we are looking at SQL Server). There are several benefits that are only available if you have this.

Software assurance can be paid as part of an agreement or can be paid on a yearly basis, but the thing to remember is it is something that you must keep up to date based on your guidelines. Once you stop paying or choose not to renew, then this benefit cannot be added back without re-purchasing the license.

NOTE: I would strongly suggest that you work with your Microsoft licensing rep or re-seller partner to fully understand what is covered and what is not. This is intended to be an overview and does not cover everything.

Let’s look at the benefits:

Ability to upgrade to newer versions

This gives you the ability to upgrade SQL to any later version as long you keep it renewed. If you allow SA to drop, then you can only upgrade to the latest major release of SQL on your effective end date.

If you do not carry SA, then you are limited whatever the latest major release of SQL was on your purchase date. You will never be able to upgrade beyond that without buying new licenses.

Ability to move a server around a virtual environment (License Mobility)

This gives you the ability to move virtual machines (VM) running SQL around a virtual environment without needing to license all the hosts. If an individual VM is licensed with SA then you have the flexibility to move it around the virtual farm and remain compliant.

If you do not carry SA, then you need to license the all the hosts that make of the virtual farm if the VMs are going to move around to different hosts more times than once every 90 days . And if you license the hosts you are required to license them with Enterprise edition which can be costly. It might make sense to license the hosts, but most times it does not. I recommend using the licensing calculator to evaluate the costs.

Ability to run unlimited virtual machines with Enterprise edition

If you are running Enterprise edition to license your hosts, then having SA allows you to run an unlimited number of VM’s on it.

If you do not have SA and you are licensing hosts, then the total number of VM’s cannot exceed to the total number of physical cores. If it does, then you are required to the cover the extra with additional licenses.

NOTE: You cannot license hosts with Standard edition

High Availability

Whether you are running shared disk clusters, availability groups or mirroring you are entitled to unlimited number of failovers and 1 free passive per 1 active server. So, if you have a 4-node cluster with 2 active and 2 passive nodes, then you would only need to license the 2 actives. You do need to license the nodes with the most cores so you cannot beef up your passive nodes so take that into account when you are sizing.

Also, and this is very important, your passive must be truly passive. This means that it cannot be used for any production workloads, reporting, offloading backups, ETL, etc. It must be completely passive, otherwise you have to license it. Microsoft defines a passive as one that is not serving SQL Server data to clients or running active SQL Server workloads. Availability groups, log shipping or even replication are often an area that people mess this up since the data on secondaries can or will be readable.

If your non-primary copies are readable, then it is going to be difficult to label them as passive. There is no requirement that I have seen that requires the secondary to be non-readable to take advantage of the free passive with SA, but I would suggest making them non-readable if possible. Because even if they are inadvertently getting used (user queries, report, maintenance tasks, etc.) then you are on the hook for licensing it.

If you do not have SA, you can still run High Availability (HA) solutions, but it becomes a little more difficult to manage. You are provided 1 passive per 1 active, however, you are limited to 1 failover every 90 days. If you failover more often than that, then you need to license every server in your HA solution or license the hosts.

There are also some restrictions in terms of how you setup your server farm. If you have failover capability within data centers more then 4 time zones apart then speak to your licensing rep about this.

Disaster Recovery

This gives you the ability to be able to have disaster recovery (DR) without needing to purchase additional licenses. There are a few conditions here that are important to be aware of if you are taking advantage of this. The DR server cannot be running except for 1 week every 90 days, during a DR scenario in which production is down or is being used to transfer from prod to DR. If running a DR server in a virtual environment, the hosts running the DR servers must be dedicated to DR. There are other considerations that you may want to review with your licensing rep.

If you are running DR using a HA technology that requires to be up (such as Availability groups) then it is treated like a passive node and the HA licensing rules apply.

If you do not have SA, then DR is not included, and you need to license your DR servers.

Licenses transferrable to the cloud

If you think moving to the cloud is somewhere in your future, then you will need SA to be able to transfer your licenses. If you do not carry this, then you will need to re-purchase the license or pay it as part of your cost in the build.

24×7 support

This gives you included 24×7 support from Microsoft for any issues that arise. You can call or open a ticket with them and get support. Without this, you are required to pay to open cases for support issues.

Opportunity for extended support on end of life

When a product reaches end of life, you do have an option to pay for extended support for usually up to 3 years. It is paid year to year and usually very expensive, but it does allow your SQL Servers to continue to be patched\supported while you work through a plan.

If you do not have SA, then this is not an option without first re-buying your license with SA and then paying extended support on top of it which is even more expensive.

Deployment planning services and training

There are deployment planning services and training that is included, but I suggest working with your Microsoft rep on understanding what this means to you and how you can use it.

Unsupported versions of SQL

As new releases of SQL keep coming, old versions keep going away. Lifecycle management is something as database professionals that we have to mindful of and be planning for.

If you are running an out of support version of SQL then you need to start thinking about what your plan is for it. If you have a choice and need to keep the server running, then you probably should strongly consider using a later version of SQL. Really, really try to do it. However, several cases here is that you are held to the limitation of a product and as much as you would love to go all “office space” on it, you value your job more, so it sticks around and you have to deal with it. There are some other examples such as licensing concerns, lack of employee participation, resource constraints, etc., but I believe these could and should be resolved.

Any time that you or your company make the choice to run on an old version of SQL (regardless of the reason), you are taking a risk and it is important to understand what these risks are and communicate it to your company. At the very least, you want to provide cover for yourself in case something does happen.

Let’s start by looking at the SQL Server product lifecycle. There are 2 dates that you need to be aware of regarding end of life.

    • End of Mainstream support – This means that up to this date Microsoft will fully support the product, as well as, continue to make changes to product design and features.
    • End of Extended support – This means that up to this date Microsoft will fully support the product, but they will no longer make changes to product design and features. Anything beyond this date is unsupported software and Microsoft will only support it if you pay for expensive extended support (which is usually limited for up to 3 years)
Version Release Date End of Mainstream End of Extended
SQL Server 2005 (SP4) 12/13/2010 4/12/2011 4/12/2016
SQL Server 2008 (SP4) 7/7/2014 7/8/2014 7/9/2019
SQL Server 2008 R2 7/20/2010 7/10/2012 7/10/2012
SQL Server 2008 R2 (SP3) 9/26/2014 7/8/2014 7/9/2019
SQL Server 2012 5/20/2012 1/14/2014 1/14/2014
SQL Server 2012 (SP3) 12/1/2015 7/11/2017 7/12/2022
SQL Server 2014 6/5/2014 7/12/2016 7/12/2016
SQL Server 2014 (SP2) 7/14/2016 7/9/2019 7/9/2024
SQL Server 2016 6/1/2016 1/9/2018 1/9/2018
SQL Server 2016 (SP1) 11/16/2016 7/13/2021 7/14/2026
SQL Server 2017 9/29/2017 10/11/2022 10/12/2027

If you have something about to come out of support, then you need to start planning for that. I recommend start planning working through upgrades as soon as you can and at minimum a couple of years in advance of end of extended support.

So, tell my why running old versions of SQL is of concern and what I need to communicate.

    • Security – Once a version reaches end of extended support, Microsoft is no longer obligated to release security patches. So as new security attacks happen (insert most recent security attack) this leaves your SQL environment at risk. And not only are our databases at risk, but every application that relies on those databases are also susceptible to data theft and corruption.
    • Compliance issues –Several regulations such as HIPAA, SOX, GDPR, etc. require that must implement procedures for detecting, guarding against, and reporting malicious software for sensitive data. It is the responsibility of the company to make the appropriate technical and organizational measures. If you are using software that is no longer supported by Microsoft, then you may be out of compliance.
    • Maintenance support and\or costs – Once end of extended support has occurred, Microsoft is no longer supporting these, so in the event of any product related issues you will not have support or may be required to pay Microsoft a significant amount of money to get it. Additionally, if you choose to accept the risks and try to isolate these servers within your environment (network segmentation) then you will incur higher maintenance costs because you must maintain legacy servers, firewalls, and perform unique setups to try to guard against intrusions.
    • Insufficient performance – The information landscape has changed significantly over the years. Depending on the version of SQL you are running, you could be using a piece of software that has not received enhancements in nearly a decade. Think of all changes that have happened in the information landscape since then and think about the sophistication of today’s cyber-attacks. The features that were enough then just do not meet the needs of current IT environments.
    • Numerous product improvements – There is a long list of benefits that comes with an upgrade that can positively affect your applications and user experience that you should take a look at.

Be sure to understand the risks and tell the story of continuing to run on this product and do everything that you can to try to get to a later version.

Licensing with CALs

Microsoft offers the option to license using a Server+CAL model. This allows you to license users and/or devices that are connecting to your SQL Server and can be a lot more cost-effective option if you are able to quantify those.

Under this model, you will purchase a server license for each server, as well as, a client access license for each Device (Device CAL) and/or user (User CAL) accessing your SQL Server and/or any of its components. 1 CAL grants 1 user or 1 Device access to the SQL Server. Like a lot of Microsoft licensing, this works on a honor system so if you install SQL Server and use CAL licensing, you will not be prohibited from going over the amount you purchased, however, you would be out of compliance and could face financial consequences if discovered.

In order to license using this model, you must be running Standard edition and must keep track of what users and/or devices are assigned. However, this information is based on the SQL 2017 licensing guide, so if already have CALs on a previous version then you may want to speak to your licensing rep about whether these can be applied differently. You may be able to use these on editions other then Standard.

CALs are also version specific so you need to make sure whatever CAL you are assigning has access to that version of SQL. It should be at or above the version. If you have active software assurance on it, then you should be fine, this really comes into play when you do not.

Also, if something connects no matter how, it needs a CAL. Multiplexing is a term that Microsoft uses to refer to software or hardware to pool connections, reroute information, or reduce the number of devices or users that directly or indirectly access or use SQL Server. Be aware that multiplexing does not reduce the number of CALs. I suggest reading through this portion of the licensing guide which does a great job explaining this.

For more detailed information on this, check out the SQL 2017 licensing guide.

Resource limitations of SQL Server

Depending on which version of SQL you are running, you may run into cpu and memory caps and it is important to understand what these caps are so you can make the best the decision. The chart below shows each version of SQL and the scale limits. For more information, see the “Editions and supported features…” articles on the Microsoft website.

SQL Version\Edition CPU Memory (MB)
SQL Server 2008 Express 4 1410
SQL Server 2008 Standard 16 65536
SQL Server 2008 Enterprise OS Maximum OS Maximum
SQL Server 2008 Developer OS Maximum OS Maximum
SQL Server 2008 R2 Express 4 1410
SQL Server 2008 R2 Standard 16 65536
SQL Server 2008 R2 Enterprise OS Maximum OS Maximum
SQL Server 2008 R2 Developer OS Maximum OS Maximum
SQL Server 2012 Express 4 1410
SQL Server 2012 Standard 16 65536
SQL Server 2012 Enterprise OS Maximum OS Maximum
SQL Server 2012 Developer OS Maximum OS Maximum
SQL Server 2014 Express 4 1410
SQL Server 2014 Standard 16 131072
SQL Server 2014 Enterprise OS Maximum OS Maximum
SQL Server 2014 Developer OS Maximum OS Maximum
SQL Server 2016 Express 4 1410
SQL Server 2016 Standard 24 131072
SQL Server 2016 Enterprise OS Maximum OS Maximum
SQL Server 2016 Developer OS Maximum OS Maximum
SQL Server 2017 Express 4 1410
SQL Server 2017 Standard 24 131072
SQL Server 2017 Enterprise OS Maximum OS Maximum
SQL Server 2017 Developer OS Maximum OS Maximum

NOTE: There are also socket limits that apply to the core maximum that will want to look at, especially if you are running a non-Enterprise version with more than sockets.