SQL Server Databases and Cloud
If you have not had an initial consultation with me, please book a free 15 minute call with me. 60 minute azure cost optimization is only currently available for Microsoft Azure customers.
You can follow my blog series on cost optimization to review your own Azure subscription free of charge. My posts are heavily aimed towards data professionals, however the principles can be applied to almost all products in Azure.
My final tip in my series 7 ways for data teams to save money in Azure is to delete unused instances (and orphaned storage) to save money in Azure.
It’s easy to spin up instances in Azure, especially for developers if you haven’t implemented a tight permissions policy. Pet projects and Proof of Concepts (POCs) get created regularly and the person involved needs to remember to delete all resources or else you will be charged indefinitely.
Even services which can be stopped and deallocated such as Virtual Machines will cost you money if they are not deleted. Why? If you don’t delete unused instances in Azure, you still pay for all of the associated costs including the storage which can be significant. Moreover, you need to specifically delete your storage when your delete those VMs or you’ll keep paying for that too. That is known as orphaned storage or unattached storage, and it is not cheap.
Microsoft published a PowerShell script to find unattached storage in azure which you can run to list out anywhere it occurs in your subscriptions.
I would encourage developers and engineers to use a resource group for POCs. This way, when it is done, the whole resource group can be obliterated in one go, leaving nothing behind.
Once you have got rid of all your old unused instances and orphaned storage, you may want to prevent team members from doing the same thing again. This is where a combination of Azure policies and tagging comes in handy. You can set policies to prevent new services being spun up without the appropriate tags. This Microsoft tutorial explains how to manage tag governance with Azure Policy.
This post on how to save money with Azure Reserved instances is part of a 7 part series on cost optimisation with Azure.
Did you say “Save 73% with reserved instances”? I did. In fact, Azure even advertise you can save up to 80% if you combine reserved instances (RI) with Azure Hybrid Benefit (AHB). So what do we mean by reserved instances?
A reserved instance is a way of reserving capacity in Azure. You are offered a discount by agreeing to a 1 or 3 year commitment. You can choose to pay an element up from to reduce your monthly bill, or you can just pay monthly. Paying monthly does not cost more than paying a portion of the cost upfront so it comes down to a preference for you or your company. Savings are made against compute costs, but not licence costs such as SQL Server Enterprise.
If you know that you would need an instance for 12 months or longer, it is usually worth taking a reserved instance. A typical scenario for this would be a production SQL Server running in Azure VM or a production SQL Database. If you have a stable workload that you don’t see changing in the near future, reservations are a good bet.
For this example, you are running SQL Server enterprise on E8s 4s VMs in UK West. You have a cluster of 3 to support your availability groups.
| Reservation | Compute cost | Windows licence cost | SQL licence cost | Total monthly cost |
|---|---|---|---|---|
| Pay as you go | £995.66 | £600.66 | £4,896.68 | £6,493.04 |
| 1 yr reserved | £587.31 | £600.66 | £4,896.68 | £6,084.65 |
| 3 yr reserved | £378.25 | £600.66 | £4,896.68 | £5,875.59 |
Alternatively, look at the potential savings with Azure SQL DB. In this example, we are looking at a SQL DB, 8 vCore provisioned instance in UK West on General Purpose tier.
| Reservation | Compute cost | SQL licence cost | Total monthly cost |
|---|---|---|---|
| Pay as you go | £828.17 | £435.11 | £1,263.29 |
| 1 yr reserved | £538.11 | £435.11 | £973.22 |
| 3 yr reserved | £372.67 | £435.11 | £807.78 |
I hesitated to put prices on here because they will change more frequently than I can update the blog but it gives you a general idea. For up to date pricing, please always refer to the Azure Pricing Calculator.
Thankfully, you don’t need to specify which instance uses the allocation, it is just available at an hourly grain level. If you have a live instance that matches your reservation, the reservation credits are used for billing. If you drop an instance and you have another eligible instance type, it will automatically uses the allocation. The allocation is on a use it or lose it basis. If you have a reservation but no suitable resource to use the allocation, you lose it. If you have more instances than reservations, you pay on PAYG terms for anything above your reservation levels.
As this post is directed at Azure SQL Data professionals; it is worth pointing out Virtual machines, SQL Database, Azure Synapse Analytics and Azure Blob Storage. It’s also encouraging to see discounts available for MySQL, MariaDB and PostgreSQL.
Despite being a long term commitment, Reserved Instances are fairly flexible as Microsoft acknowledges that customers’ needs will change from time to time. You can exchange or cancel reservations as your needs change up to limits imposed by Microsoft.
In this series of posts, I list 7 ways to quickly save money in Azure by using cost optimisation principles. Get on top of your cloud costs and start saving money by putting these into action. Whilst this series is specific to Azure, most of the principles can be applied to other public cloud providers. The only thing that differs is the product. Think RDS as an equivalent for SQL DB or S3 for Storage Accounts.
It’s important to keep on top of your cloud costs. Operating in the cloud means it is easy to just spin up new instances in minutes. Whilst that is great and allows company to work in agile manner, the switch to an Operational Expenditure (OPEX) model means that the cost increase can be gradual and go unnoticed.
This mini series of steps are also important.
By optimising your costs in Azure and ensuring you are paying the right price for all of your services, everybody wins. Not only are you helping your company save money, it is good for your career and it could even save jobs in your company. Not to mention turning off what you don’t use is good for the environment too.
Please get in touch if you would like to schedule a free introductory 15 minute call for some help in reducing your Azure bill.
This is post #4 in my series on 7 ways for data teams to save money in Azure. Cost optimisation is crucial to any organisation that operates in the cloud, as costs can and do run away from away you without regular attention.
To avoid repetition of my my post regarding the size of your Azure SQL DBs, please read that to understand the concepts of elasticity, scalability and CAPEX vs OPEX.
One great thing about the cloud is that you can change your mind, or adjust as you go along. Unlike days gone by where you would have a bought a giant SQL Server and ran about 10% utilisation (just to allow room for future growth), you can be much smarter now and only pay for what you need.
You should be collecting metrics on CPU and memory, as well as looking at your throughput and disk latencies to see how well you are sized. If your CPU is always below 50%, including spikes and you haven’t identified memory pressure, you can look at scaling down. In an ideal world, you should run load tests against the old and new instance types, and compare them before making a decision. Cutting CPUs will give you the biggest financial saving in Azure due to the way that licensing works.
If your CPU is regularly pinned above 85%, you need to take action. Scaling up is one option, improving your queries is another. Some people will choose to scale up and then plan fix their queries, with the ambition to scale back down again later. In my experience, this rarely happens and they just accept the new instance size as normal. Other work becomes the priority. If you can hold off scaling your instance to give you time to fix your expensive queries first, I would recommend it.
Try not to rush a decision, there are so many instance types and sizes to choose from, each one optimised for a different use case. Do your research and choose the most appropriate instance type, not just next the one up.
SQL Server is licensed by the core. The more cores, the higher your licensing costs. However although instance classes tend to double cores and memory as you step up to the next level, there is a smarter way to do it. If you have determined that you don’t need more CPU but you do need more memory, look into the memory optimised instances such as the E-Series.
Looking at the table below, imagine you are on a D16 V4 which provides 64 GB of RAM, but you need to increase your RAM to 128 GB. If you choose the next highest D-Series machine (D32 V4), you double your costs. You might get the extra cores, and they will also help performance, but the expense is unjustified.
Consider instead changing to an E-Series (E16s V4) where you can keep your cores at 16, meaning no change to Windows or SQL licensing costs but you also get the desired 128 GB of RAM. You only pay £158 more per month. *
| Instance Type | CPUs | Memory (RAM) | Monthly cost |
|---|---|---|---|
| D16 V4 | 16 | 64 GB | Compute £505.45 OS (Windows) £400.44 SQL Licence £3,264.45 Total £4,170.34 |
| D32 V4 | 32 | 128 GB | Compute £1011.44 OS (Windows) £800.88 SQL Licence £6,528.91 Total £8,341.22 |
| E16s V4 | 16 | 128 GB | Compute £663.77 OS (Windows) £400.44 SQL Licence £3,264.45 Total £4,328.67 |
For memory intensive applications such as SQL Server, constrained CPU instances provide another tool in the battle for cost optimisation.
Say you actually needed 256 GB of RAM but you would still be ok with 16 cores. Sticking with the D-Series, you would need to go up to 64 cores to get the desired memory. You can take the idea of memory optimisation a step further with constrained CPU instances. With these, you get the extra CPUs but they are not available to you. This allows an instance with even more RAM than the memory optimised instances. This means that whilst you have increased compute costs, you don’t have increased license costs as you only pay licenses based on available CPUs. This is a significant saving as you move into higher instance tiers.
| Instance Type | CPUs | Memory (RAM) | Monthly cost |
|---|---|---|---|
| D64s V4 | 64 | 256 GB | Compute £2,022.33 OS (Windows) £1,601.76 SQL Licence £13,057.81 Total £16,681.90 |
| E32 16s V4 | 16 available | 256 GB | Compute £1,327.00 OS (Windows) £800.88 SQL Licence £3,264.45 Total £5,392.33 |
Don’t provision for what you will need in the future, size your instance for what you need now and keep on top of it. Changing is quick and easy. Be aware of non standard instance types like memory optimised and Constrained CPU to maximise savings against your SQL licensing costs.
Play about with the Azure pricing calculator in order to compare instance types.