SQL Server Databases and Cloud
Take a look at my Sessionize speaker’s profile. I talk about data and cloud topics, and have done at some major SQL conferences and local user groups. Please get in touch if you’d like me to speak at your event. My live sessions are listed.
Sometimes, WordPress mail can be a little flaky. If I don’t reply to you, please send me a DM on twitter.
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.
This post on how to save money with Azure SQL DB Serverless is part of a 7 part series on cost optimisation with Azure.
The default option is provisioned which means you are provisioned with the resources you request, and you keep them for as long as you need them. e.g. If you want 2 vCores and 32GB of storage, you have it permanently, or until you shut down the resource.
Serverless means that although your storage is provisioned, your compute resources are on demand. If you enable automatic pausing of your SQL database, this will happen after a period of time defined by you. Whilst the database is paused, you are not charged for compute costs.
You can set your database to auto pause if there is no activity. The auto-pause delay period must be between 1 hour and 7 days. When your database is completely paused, you only pay for storage. Auto pause in optional so you can keep the database online all the time, but just benefit from the auto-scaling capabilities. ↙
You can set a maximum number of vCores up to 40 and a minimum number of vCores of 0.5. The allowed minimum is related to the maximum you set. e.g. Where maximum vCores are set to 4 or fewer, you can set a minimum of 0.5.
Azure SQL DB will scale as required meaning you only pay for what you need. You could allow for some head room by setting a higher max vCore limit than you need, just beware that if it is needed, you will be charged for it.
Bear in mind as well that SQL DB Serverless offers per second billing. You pay for increased resources only when you need them. The billing is quite complex for Azure SQL DB Serverless, and you should read this billing guide for more information.
For up to date pricing information, please always refer directly to the Azure SQL DB pricing page.
When your database is paused, the first connection attempt will fail with error code 40613. If your database client has connection retry logic, you won’t need to do anything else. Otherwise, you will need to code this logic into your application.