Category: Licensing

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.

If you have a good understanding of SQL licensing (or read my previous blog post) then there are several things you can do to help optimize your environment to make sure you are getting the most from your licensing. In this article, I will also go through and talk about things you should be doing to make sure you are in compliance, but also things that you can do that present a significant cost savings to your organization.

I have worked with or for several different organizations throughout my career and been able to help them discover millions in licensing savings\compliance issues. The information below is the process that I have went through and checked with each of them to discover this.

Make sure you are selecting the correct licensing model.

I would encourage you to read Understanding the Basics of SQL licensing if you not have done so already or if you are unsure. In my experience, the most costly mistake that people make is licensing physical hosts over licensing VM’s. I have seen several cases where this amounts to millions of dollars cost difference. Licensing by host certainly has its place, but make sure you understand what that is. And if you are not sure what is the best, then take the time to do the math and see which way is the most cost effective.

Correct any issues with resource over-provisioning.

Take a look at the servers running in your environment and see if they are actually using all of the resources that are allocated to them. The most important thing to pay attention to is CPU since you are licensing based on cores. The second most important thing to look at is memory and that is because this alone could affect whether you need Enterprise or Standard. Then you still may want to look at other resource metrics that could have an impact on your CPU.

• • CPU – Check your average CPU utilization over time (especially during critical processing hours) and see what it is running at. Do not just compile an average number, but look at the CPU usage over a period of time so you can understand your spikes. My general recommendation is to try to keep average CPU somewhere around 30%. If its way under that then you may be over-provisioned and if you are way over that you could be experiencing performance issues as a result. What I am typically looking for here is obvious signs, for example, if you never extend beyond 5% utilization on a 8 core server you may be over-provisioned and paying for something you are not using.
    • • Remember that there is a 2 licensing minimum for SQL so running 2 cores is the same as 4 cores from a licensing perspective.
  • Memory – While memory is not part of the licensing guidelines it can play a supporting role, because it can define what version you need. I have seen customers purchase Enterprise edition due to the memory resource constraints of Standard edition (remember the 64GB memory limits in SQL 2012?). I have also seen examples where this was not needed and the company effectively over-bought. When you look at this, do not just look at memory utilization which is typically high, but look at internal SQL memory counters such as Page Life Expectancy and Buffer cache Hit Ratio. You want to get an idea of how often SQL is able to read from memory vs disk and determine if it is acceptable for performance.

Make sure you are running the correct version of SQL.

Oftentimes companies will run Enterprise edition in situations where it is not needed. Make sure if you are running Enterprise edition you have a valid reason to do so.

NOTE: Microsoft does not have a supported downgrade path so if you need to downgrade you will to do a uninstall\reinstall

My rule of thumb is to run the lowest version of SQL that you can while still meeting business needs. Of course, Enterprise is nice and beneficial, but it is actually needed

Avoid running applications on the same server as the database.

If at all possible, the database should always be on an isolated server. You do not want to be paying expensive licensing costs only to be having some other application consuming those resources.

A good check in this situation is to look at memory and CPU consumed by SQL and by other things running and view what the difference is. If there is a wide margin you need to figure out what is going and see if it is something that can be fixed.

Look for opportunities to virtualize your physical database servers. 

There is a good chance that if you have a database server in your environment that the workload on that may have changed at some point for better or worse. Databases and applications are retired, new ones are brought on, processes get moved, or any sort of other thing that can have an impact on the server. With physical servers you are more limited in terms of your ability to grow or decrease resources (CPU\Memory). While you can do it, it takes provisioning or removing hardware. With a virtual server you typically have an easier path related to scalability (whether up or down). Not saying you should virtualize everything, but something to consider when looking at your environment.

Spend time performance tuning where you can.

This encompasses a lot of stuff from server tuning, query tuning, index tuning, application tuning, etc. If you can perform work to reduce the CPU load then you can reduce the number of cores and ultimately reduce licensing. This is a loaded topic that deserves blog posts of its own, but there are several great resources out there to assist with this.

My experience with most of the customers or companies I have worked with is that most of the performance tuning that goes on is reactive. Meaning someone is having a problem with something being slow or not performing up to standard. But I would argue that pro-active performance tuning is also very important and can come with significant cost savings. Its just something that I do not think there is enough prioritization on.

For example, I was working on a server once with a customer that was 24 cores and running Enterprise edition. I was able to tune over a short period of time to the point we were able to reduce the core count down to 12 (in half). Looking at retail numbers, this reduced the licensing cost on this server from 168K down to 84K, saving 84K in the process. While this may not always be the case, you probably have a lot of things that you could reduce a little, and a little can add up to a lot.

Look for consolidation opportunities, especially on servers that do not meet the minimum licensing core count.

Look at your environment and look for opportunities to consolidate databases. Not every database deserves or needs to be running on its own server.

There are a few things that you need to consider when look at consolidation opportunities.

• • You need make sure you are not required the database on its own server and there are different scenarios where this may be required.
  • Check to see which servers do not meet the the minimum licensing requirement. These are prime candidates for consolidations because these are costing you money that you are not getting any return on.
  • Look at resource utilization on your existing servers to see if they would be better combined.

I think this is a topic that can be expanded on greatly and I will plan to do so. Because there is a bit of art to how you handle this. Consolidations can trigger a conversation of cost vs ease of management. I do not recommend changes based on cost alone and think you should always factor in the level of difficulty to manage. More to come on this topic.

Define and execute on a decommission process.

If something is not being used try to get it removed as soon as you can to free up those licenses.

Oftentimes servers stop getting used and they sit out in the environment taking up licenses and you continue to pay for maintenance on those. I suggest performing audits occasionally to see if a server is still being used (if you are unsure). I will be creating a new blog post soon that details how you can do this.

Determine if SQL licensing was included in the purchase of an application.

If you are using 3rd party vendor products you may want to double-check to see if the SQL licenses are included with the product. Especially if they are supporting the product for you and you are just hosting it. You do not want to be paying for a license a 2nd time over. If you are unsure, check your contract or check with the vendor. I would warn against assuming as I have been surprised before.

Keep in mind that if the licenses are included then they usually have certain restrictions so please work with your Vendor or licensing manager to check this. Commonly, the only thing you are allowed to run on the server is there product, so running on a shared instance may void the license.

Wrap up:

SQL licensing is expensive and there is a lot that can be done to reduce these costs. I have worked with companies to save millions by going through the things listed above and you can too.

Ever find yourself confused about how SQL licensing? If you have, welcome to the club! There are so many components to it that it can get crazy fast. The information in this article lays out some of the more important aspects of SQL licensing to be mindful of, as well as, situations where you may want to consider each. For this article, I am using the latest information from the SQL Server 2017 licensing guide. So any old timers out there that are still on a grandfathered plan then you will need to work with your rep.

SQL 2017 Licensing Guide

I highly recommend working with Microsoft or a 3rd party re-seller to obtain licenses and talk with them about your plan. Additionally, I also recommend getting any information they give you in writing. Unfortunately people make mistakes on this topic and things can get miscommunicated and you do not want that.  There are tons of horror stories where someone built there licensing structure on something that turned out to be wrong.

Disclaimer: I do not work for Microsoft or any 3rd party approved re-seller. I am just a guy who has spent a lot of time working on this subject sharing information I have learned with you.

Starting from the beginning let’s look at the primary editions of SQL and the differences between them. It is important to make sure that you are picking the correct one when installing SQL due to the licensing implications.

There are 5 primary editions of SQL Server that are currently available

• • Enterprise ($$$$) – This is the full version with no limitations. Should only be used when Standard does not meet the requirements for what you need
    • • Cost: MSRP for a single Enterprise license (which covers 2 cores) is $14,256. The cost could be lower with your Microsoft enterprise agreement. Check with your licensing manager for exact costs
  • Standard ($) – Some enterprise functionality not available although will work for most stuff
    • • Cost: MSRP for a single Standard license (which covers 2 cores) is $3,717. The cost could be lower with your Microsoft enterprise agreement. Check with your licensing manager for exact costs.
  • Web ($) – This version is somewhere between Standard and Express in terms of features. This is used for web hosters and Web VAPs and is only availible under the Microsoft Services Provider License (SPLA)
    • • Cost: Speak to your rep about the costs here
  • Express (free) – A free version of SQL that has several limitations
    • • Cost: Free
  • Developer – This is the full version of SQL, but should exclusively be used in non-prod. Starting in SQL 2014, Microsoft made this free. If you are running earlier versions you may be covered under a Visual Studio or MSDN subscription otherwise you need to speak to your licensing rep to see if there is any costs here. I will create a separate post specific to this subject.

Ways to license 

When looking at how you can license each of these are a handful of models to be familiar with for SQL. While this article does not specifically apply to Azure, a lot of this information does apply, although, there are additional options in Azure.

There are 4 ways that you can license a production SQL Instance

1. 1. License the Physical Hosts for virtual environment by physical cores (Limited to Enterprise edition) – This means that you license all physical cores on each server in a virtual cluster
   2. License a Physical server by physical cores (non-virtual) – This means that you all license all physical cores on the physical server for the version of SQL that is running on it.
   3. License the individual VM’s by virtual cores – In a virtual environment, you can license each individual VM with the version of SQL that you are running.
   4. License using server + CAL (limited to Standard edition) – This means that you must assign a license to the server running SQL Server and acquire a CAL for each user or device that accesses the server

There are a few other caveats to SQL licensing that you also need to be aware of.

Other important notes about licenses

1. 1. 1 license equals 2 physical cores. On a VM, 1 license equals 2 virtual cores.
   2. There is a 4 core minimum per physical processor. This means that there is a 2 license minimum per physical core. For a VM, you license the virtual cores with a 4 core minimum.

Software Assurance

In addition to the licensing you apply, there is also software assurance to consider. Software assurance is typically around 30% the cost of the license and is paid yearly. Although this is more of a starting point and can be lower, you need to work with your rep on this. Also, be aware that if you have this and drop it, then it cannot be added back a later time. To reclaim software assurance you need to re-buy the licenses.

Here are some of the benefits\reasons of why you want to consider getting SA on all licensing purchases.

Benefits of Software Assurance (SA)

• • Upgrade – Gives you the ability to upgrade SQL to any version (up to the latest).
    • • Without SA you are limited to the latest version of SQL released when your SA expired
  • Virtualization – Have the ability to move servers around a virtual environment without needing to license the hosts
    • • You cannot have a Virtual environment without SA unless you license all physical hosts
  • High Availability and Disaster Recovery – For high availability you can now have multiple passive (local, DR, and in the cloud) without additional licenses needed. Previously, you were limited to 1 passive per paid active. The standby nodes do have to truly passive and cannot be used for any type of production workload.
  • License Mobility – Ability to transfer licenses
  • Support benefits 
  • Training and deployment planning services (sometimes)

Looking at each of the Licensing models deeper

Here is a deeper explanation of each of the licensing models and the pros\cons of each.

License using server + CAL

CAL licenses can be purchased for servers running Standard edition only. It is usually used when the number of clients (user or device) is pre-determined. To license for this you have to pay a server license plus a fee for each Device or User CAL. Check with you licensing manager for cost.

License a Physical server by physical cores (non-virtual)

This one is pretty self-explanatory. If you are running SQL on physical servers you will need to license the physical cores of the servers they are running on. If you are running a passive\standby physical node then that would be covered under SA or have licenses applied if you do not have SA.

• • Be aware there is a 4 core minimum per physical processor.
  • You do not have to license for hyper-threading just the actual physical cores. Be sure to double check this. The value that SQL Server is not always right because it will include hyper-threading so be sure to check to see the actual physical core count.

Licensing the Physical Hosts for virtual environment by physical cores

Pros:

• • Easier to manage since you apply the licenses to the hosts. You can build whatever you want on it without needing to track as much
  • This model “can” function without software assurance (though could affect other things)
    • • If you have a lot of licenses that do not have SA then you may want to look at using this as an interim solution
  • You can “potentially” save money by using hyper-threading, private cloud scenarios with VM density and using dynamic provisioning and de-provisioning of VM resources

Other Considerations:

• • This model can function without Software assurance as long as the number of VM’s do not exceed the physical core count. For example, if you have 40 physical cores then you can only run 40 virtual machines. If you go over the limit then a Enterprise license is required for each additional Virtual machine you are over
  • With software assurance, you can run as many Virtual machines as you like without limit
  • This option is oftentimes more difficult to quantify costs for projects\budgets. It is more difficult to communicate the cost for an application requiring SQL since it is shared by many things. This comes into play when you need to determine who is responsible for paying for what or whose budget it comes out of
  • You may end up paying for licenses then you are using. To come out ahead you have to have more licensed number of cores on the VM’s then you have on the hosts.
  • Must have a well-managed\well-designed infrastructure to maintain compliance.
    • • You probably want to segregate SQL servers in your environment to dedicated virtual hosts to minimize your footprint. This requires consideration to be taken in the design stages of your infrastructure. Additionally, if you have some licenses that go up to SQL 2012 and others that go up to SQL 2014, then you may have to create dedicated Virtual clusters to host certain versions of SQL.
  • Limited opportunities to reduce licensing footprint. You cannot tune\consolidate a SQL Server and reduce the core count on it and affect the licensing, because you still have to license all of the hosts in the virtual cluster despite what is allocated to the VM’s.
  • Potential for more hardware needed to support this licensing model. If you have to create several different virtual clusters to contain different SQL versions you could end up requiring more hardware then what you need.

Recommendation: Do not select this option by default. I have worked with and seen several companies choose this option and cost themselves a significant amount of money that did not need to be spent. In fact, I think this is one the biggest mistakes I have seen in terms of licensing. There are situations where it makes sense, but not for everything. If you are unsure, perform an exercise to look at the math and see if it makes sense. Check yourself!

License the individual VM’s by virtual cores

Pros:

• • Usually the lowest cost option and the better option from what I have seen. The reason being is it’s less common to see more licensable cores being allocated to the VM’s then what the hosts have. Additionally, it is likely that you may be running multiple editions of SQL (Standard\Enterprise) on the same cluster.
  • Cost is more easily determined for budgeting\spending. It is easy to communicate the cost to the business when a new SQL VM gets built since you know exactly how many licenses it needs.
  • You are only playing for what you need.
  • Grants immediate opportunity to reduce licensing. You can tune\optimize as much as you can and reduce cores. As you reduce the cores, you can reduce the licenses needing to be allocated to it.

Other Considerations:

• • Licenses have to be managed per VM to remain in compliance. This requires you to keep track of your inventory to understand how many licenses are allocated. There is a little more management here over licensing the hosts. You cannot just build something anytime you want and expect to be in compliance.
  • Software assurance is required if the virtual server can move around on hosts.
    • • You can license per VM without SA, but you can only failover that server once every 90 days to remain compliant (excluding permanent hardware failure)

If are unsure the best model for you, CHECK!

In my experience it is almost always a cheaper option to license per individual VM then it is to license the host. However, if you are unsure I would encourage you to do an exercise where you look at how many licenses you would need for the host vs how many you would need to license the VM and determine your cost difference.

Future Topics:

1. What does building an inventory look like and how can you accomplish this? What tools are available?
2. Advanced scenarios with licensing where you do not have software assurance
3. Specifics to licensing a non-production environment
4. Licensing for Analytic Platform System (Parallel Data Warehouse)
5. Further licensing considerations with High Availibility
6. SQL licensing in Azure
7. Tools that you can use to better manage your licensing