Cost saving with SQL Server – Right Sizing

Last night a DBA saved my… Budget.

I saved more than my annual salary in SQL Server Licensing costs last year.

That sounded like a pretty cool line to get on my resume, and it’s something that lots of us have the opportunity to work towards.

SQL Server licensing is expensive, yet SQL Server can be good value too. Pressures on costs are high – both from the aspect of increasing licence costs (there was a price hike not long after the initial Brexit vote in the UK) and internally with the organisation looking for cost savings wherever possible.

This meant a thorough review of our SQL Server estate to check our costs and to identify savings that could be made. I broke this down into the following areas:

• Right Licensing
• Consolidation
• Appropriate architecture

This article is one of a series on the subject, covering “Right Licensing”. If you’ve never heard of the term, don’t worry, I made it up.

Disclaimer

It seems only right her to pop in a Disclaimer. As per all of my Blog posts, this article reflects my views and opinions, not those of my employer, Microsoft or anyone else.

Licensing needs should to be checked with Microsoft or an authorised Vendor.

Right Licensing

The first consideration for Licensing is to ensure that you are using the most appropriate Edition of SQL Server.

SQL Server is available in a number of different Editions. There are differences in across Versions of SQL Server, so I’ll just cover the main Editions.

• Express
• Standard
• Enterprise
• Developer

Developer Edition (Free)

Developer Edition has been free for use from SQL Server 2014 onwards. Previous to this, it was available at a “per Developer” cost, or free through a MSDN Subscription.

If your Server is to be used purely for Development or Test purposes then it may be appropriate to use SQL Server Developer Edition.

Be aware that the functionality of SQL Server Developer Edition is the same as SQL Server Enterprise Edition. This means that you might accidentally introduce a disparity between your Development / Test and Production environments – i.e. using SQL Server Developer Edition to develop for implementation on SQL Server Standard Edition. Certain features of Enterprise Edition may cause your code to slow once it goes into Production on Standard Edition – e.g. merry-go-round scans / access to over 128Gb of memory. That’s inconvenient, but if you use features such as Data Partitioning, you will need to use Enterprise Edition in Production and incur the higher costs.

Visual Studio Subscription ($)

The service formerly known as MSDN. Certain levels of Visual Studio Subscriptions provide free licences for using SQL Server in a non-Production environment. Sounds similar to SQL Server Developer Edition, but you are not restricted on the Version or Edition of SQL Server that you can use. Protects against a potentially costly accidental dependency on SQL Server Enterprise Edition.

A Visual Studio licence is required for each Developer / Tester who will be using the system but . Interestingly, a SQL Server licenced with a Visual Studio Key does not report this through querying @@Version. SQL Server reports Standard, Enterprise or Developer Edition so you need to ensure that you have records of your Visual Studio licences, together with robust proof that this Server is only being used for Development / Test when the Microsoft Licence True-Up comes along.

Standard Edition ($$)

Standard Edition is limited by the number of Compute and Memory that it can address. This is currently set to the lesser of 4 Sockets or 24 Cores on CPU and 128Gb of memory for SQL Server 2017. This is slightly higher than the limitations on previous versions. Not much else to say. If your requirements are satisfied, use this Edition.

Enterprise Edition ($$$$$$$$)

SQL Server Enterprise Edition is significantly more expensive than Standard Edition (about four times the cost). Be sure that it’s required. Tell-tale signs are where the Server exceeds the number of CPUs or maximum memory supported by Standard Edition. The latest version of “sp_blitz” by Brent Ozar also report on Enterprise Edition features that are in use on your Server, making it easier to identify if you can save by switching down to Standard Edition.

Changing Editions

If you are currently using a Paid edition and are looking for a simple method of moving your already installed SQL Server from Standard or Enterprise Edition to Developer Edition, then you’re out of luck. I considered adding a Matrix showing possible Upgrade paths, but it’s easier to ask the question “Can Microsoft charge more for licensing that your current situation?”. If the answer is “yes” then you will be able to Upgrade, if “No” then you cannot. You will need to uninstall the old copy of SQL Server before installing Developer Edition. I have checked for other solutions, but nothing seems sufficiently safe to warrant trying. Uninstall / reinstall is the only way to do it.

General Point about Core-based licensing

Microsoft has a rule about Cores in a Server. If Cores are installed in the Server, you must license them. Regardless of whether they are being used by SQL Server or not. This creates a strange situation with SQL Server Standard Edition whereby you need to licence Cores that SQL Server cannot utilise. You cannot switch off Cores within a Server, SQL will still know that they are there. If you have multiple Processors, you could remove a Processor but let’s not. You can end up seriously compromising your Server as you may lose access to half of the RAM, Network connectivity and Storage connections. It’s a slippery slope.

A far better idea is to have switched on Infrastructure people (like we do) who know these points and will choose Servers destined for use with SQL Server  – less Cores, higher Clock speeds.

Express Edition

A quick note on SQL Express Edition. I don’t officially recognise this as an option, although I acknowledge that it exists (and look after some). There are too many limitations to consider it “production ready” for anything other than as a local Database for vendor-supplied Applications – e.g. Maximum Database size, no SQL Server Agent. Even if SQL Express has been included as part of an Application, I’d prefer to move the Database onto a “proper” version of SQL Server.

Microsoft Server Applications

Some Microsoft Applications come with a free licence for SQL Server – e.g. Team Foundation Services or SCCM. This is useful, so long as you stick to the limitations. Normally, you get a licence for SQL Server Standard Edition on a single Server, to be used solely for the Microsoft Application that it came with. You cannot store any other Data on that Server – just one Table and you’re unlicensed!

Overall Cost consideration

Licensing Production environments comes at a cost, but you should always consider the overall cost when comparing to alternatives. One of the changes with the brave new world of Cloud computing is that you often end up paying for environments regardless of their use. You have no rights to develop for free!

During my journey, I found a number of Licensed SQL Servers being used purely for Development and Test Purposes together with a lower number of SQL Enterprise Edition installs where Standard Edition would have been fine.

Through “Right Sizing”, I was able to make considerable cost savings.

Done properly, you only pay for a maximum of half the environments – only Production, no Dev or Test.

But SQL Server is still too expensive, I hear them cry.

To be continued…