John McCormack DBA

SQL Server Databases and Cloud

  • Personal
    • About
  • Free Training
    • SQL Server on Amazon RDS (Free Course)
    • Free practice questions to help you pass DP-900
  • Save money in Azure
    • Azure IaaS SQL Backups – Stop burning money
    • Your Azure SQL Database and Managed Instance is too big
    • Turn the cloud off at bedtime to save 70%
    • Your Azure SQL Virtual Machine might be too big
    • Save money with Azure SQL DB serverless
    • Save up to 73% with reserved instances
    • Delete unused instances to save money in Azure
  • Hire me
    • 60 minute cost optimization
    • Let me solve your SQL Server problems
    • Take a look at my Sessionize speaker’s profile

7 ways for data teams to save money in Azure

19th January 2021 By John McCormack Leave a Comment

Save money in Azure

save money in azure - piggy bank with sunglasses

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.

1. Review your backup retention policy

Azure IaaS SQL Backups – Stop burning money

2. Right size your Azure SQL DBs and managed instances

Your Azure SQL Database and Managed Instance is too big

3. Turn non production instances off out of hours

Turn the cloud off at bedtime to save 70%

4. Right Size your VMs to save money in Azure

Your Azure SQL Virtual Machine might be too big

5. Consider moving to Azure SQL DB serverless

Save money with Azure SQL DB serverless

6. Reserved instances

Save with reserved instances – even up to 73%

7. Delete unused instances

Delete unused instances to save money in Azure

Bonus steps:

This mini series of steps are also important.

  1. Implement Elastic Pools to share resources
  2. Look to see if you are eligible for Azure Hybrid Benefit

Everybody wins

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.

Need some help? – Book a call

Please get in touch if you would like to schedule a free introductory 15 minute call for some help in reducing your Azure bill.

Filed Under: Azure, cost-optimization Tagged With: azure, azure billing, azure iaas, azure sql db, cost optimisation, cost optimization

Your Azure SQL Virtual Machine might be too big

19th January 2021 By John McCormack Leave a Comment

Cost optimisation

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.

Is my instance oversized?

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.

Is my instance undersized?

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.

I do need more compute power, what’s next?

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 TypeCPUsMemory (RAM)Monthly cost
D16 V41664 GBCompute £505.45
OS (Windows) £400.44
SQL Licence £3,264.45
Total £4,170.34
D32 V432128 GBCompute £1011.44
OS (Windows) £800.88
SQL Licence £6,528.91
Total £8,341.22
E16s V416128 GBCompute £663.77
OS (Windows) £400.44
SQL Licence £3,264.45
Total £4,328.67
* Prices taken from Azure Calculator on 18/1/2021. Subject to change

Constrained CPU Instances

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 TypeCPUsMemory (RAM)Monthly cost
D64s V464256 GBCompute £2,022.33
OS (Windows) £1,601.76
SQL Licence £13,057.81
Total £16,681.90
E32 16s V416 available256 GBCompute £1,327.00
OS (Windows) £800.88
SQL Licence £3,264.45
Total £5,392.33
* Prices taken from Azure Calculator on 18/1/2021. Subject to change

Cost optimisation summary

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.

IT Certification Category (English)728x90

Filed Under: cost-optimization, front-page Tagged With: azure, azure iaas, cost optimisation, cost optimization

IaaS++ (Azure SQL Server IaaS Agent Extension)

11th December 2020 By John McCormack 1 Comment

IaaS++

Most DBAs or cloud practioners have seen a graph similar to this ⬇. It shows the flexibility and responsibilities between different methods of adopting SQL in Azure. SQL on VMs gives you the most flexibility but also the most administrative work. SQL DB single instance handles almost all of the “heavy lifting” (things like backup,os patching, installation etc), but gives you the least flexibility. Azure SQL DB managed instance lies some where in between. SQL on VMs are known as Infrastructure As A Service (IaaS). SQL DB (Single DB or managed instance) are known as Platform As A Service (PaaS).

flexibility vs responsibility graph

But now there is another option, called SQL Server IaaS Agent extension. I think of it as IaaS++ as it extends your SQL VMs to give them some of that heavy lifting funtionality that the PaaS offerings provide, whilst still allowing you full control over the instance.

What do you get with SQL Server IaaS Agent extension?

The main two items I will go into here are automated backups and automated patching. These are a standard on most PaaS products, with all cloud providers, however it is only down to the introduction of this “IaaS++” extension, that you can now get this for SQL on VMs.

You can also configure storage, high availability, Azure Key Vault integration and R services, as well as enabling a subscription wide view of all your instance and license types, however this post only focuses on automated backups and patching.

Real world scenarios

Patching

My client had fallen behind with patching and needed to ensure that important servers were patched regularly. By enabling automated patching, it meant that they could have only the important patches applied during an agreed window, and then look at other patches and cumulative updates when it suited them. They had a test environment that mirrored production, with a 3 node availability group cluster. (Automatic failover was enabled) so I was able to test the solution there, before going anywhere near production. The plan was as simple as this:

  1. Add a 90 minute window at 12:00 for Server1
  2. Add a 90 minute window at 02:00 for Server2
  3. Add a 90 minute window ar 04:00 for Server3.

This approached allowed 30 minutes at the end of each window for VMs to be restarted before the next VM’s window would start.

  • Click on automated patching from the SQL Virtual Machine in Azure Portal.
  • Update the toggles to set your patching window.
  • Daily or weekly schedules can be chosen.
  • If patches are applied, your VM will be restarted.
IaaS Extension Automated Patching

This approach allowed them to move from 44 outstanding patches to 4 on 3 servers without manual intervention. Failovers happened seemlessly. I’d just urge a word of caution with critical production systems, as this will restart your VMs. Are you ready for that? My advice is get comfortable with it on non prod systems before starting on production.

I think it’s a great feature. It’s not always possible to just go to Managed Instance so for those of us who need a full SQL install, this is a handy hybrid.

Backups

Another client was using the Ola Hallengren solution for managing backups. It’s the best solution out there when you need to configure your own backups but what if your cloud provider will do it for you? This client also didn’t have an experienced DBA, so in this case, it is better to let Microsoft do it. What’s more, you can configure a retention period between 1 and 30 days to stop your storage costs from ever increasing.

Before starting, make sure you don’t have your own backup solution running in parallel.

  • Click on automated backups
  • Configure the toggles to suit your needs
  • Link it to a storage account
  • Check your backups are working as expected and can be restored

These tasks can be automated as well using PowerShell or Azure CLI. I’ll maybe cover this in a future blog.

Popular posts on johnmccormack.it

How do I set up database mail for Azure SQL DB Managed Instance
Put tempdb files on D drive in Azure IAAS

Filed Under: Azure, Azure SQL DB, Azure VM, front-page Tagged With: azure, azure iaas, IaaS++, SQL server

Put tempdb files on D drive in Azure IAAS

21st March 2019 By John McCormack 7 Comments

Data loss warning Azure VMTempdb files on D drive you say?

Azure warn you not to to store data on the D drive in Azure VMs, but following this advice could mean you are missing out on some very fast local storage. It’s good general advice because this local storage is not permanently attached to your instance, meaning you could lose data or log files if your VM is stopped and restarted but what if you could afford to lose certain files? Say files that are recreated during startup anyway.

TempDB is the ideal candidate for this. No other database is suitable! Putting the tempdb data and log files onto D drive can be achieved quite easily with a little bit of effort. And you will most likely see a big improvement in tempdb read/write latency.

Just look at these results! Even for someone like me who sometimes looks at charts and often can’t see the obvious, I don’t think there is any denying when the change took place here.

tempdb latency azure IAAS
Blazing fast now

 

Overview of how to configure SQL Server to use D drive for tempdb on Azure VM

  1. Run the ALTER DATABASE command for all of the files you need to change e.g.
    • ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', FILENAME = 'D:\TempDB\tempdev.mdf')
  2. Create Folder D:\TempDB or something similar
  3. Restart SQL Server and make sure your files are created in D: as expected
  4. Manually remove any old tempdb files still lying around

Now to make sure tempdb is created when the VM restarts

  1. Create a Powershell script which will create the D:\TempDB folder on startup and start the SQL services
    • You could use this script at https://community.idera.com/database-tools/blog/b/community_blog/posts/configuring-tempdb-on-azure-iaas-for-sql-server 
    • Schedule this script with a Task Scheduler startup job
    • Change your execution policy to remote signed if it isn’t that. Set-ExecutionPolicy RemoteSigned
  2. Change the startup type of your SQL Services to Automatic (Delayed Start)
  3. Test this thoroughly in dev before you rely on it in production. If it doesn’t work, you’ll need to remember how you configured tempdb and manually create the folders, then start SQL Server. Not a fun task under pressure.
More tempdb goodness

Configuring TempDB for SQL Server 2016 instances

IT Certification Category (English)728x90

Filed Under: Azure, Azure VM, front-page Tagged With: azure, azure iaas, sql server on Azure, tempdb

Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

John McCormack · Copyright © 2025

 

Loading Comments...