This post looks at Azure SQL Database Pricing and Cost Optimisation and what users need to know. For example, the newer database default is about ten times the price of a previous Standard S1 or four times the price of an S2 database. We have seen many customers paying ~£330 per database per month when all they really need is a standard one. For example, a standard S0 comes in at ~£11 per month while the S1 and S2 SKUs are ~£22 and ~£56 per month respectively. Just from this we can see it is important to ensure your environments have the correct database SKUs during setup time.
The new sizes are created using the vCore purchasing model as opposed to the DTU based model we are used to. The default size also depends on how you create the databases, whether through the Azure Portal, SQL Server Management Studio (SSMS), or via Script for example. If you create a database using SQL Server Management Studio you will see the Configure SLO (Service Level Objective) box. The SLO is Gen 4 or Gen 5 and the Edition is General Purpose with a max size of 32 GB. It would have previously been DTU and Standard S2. The Gen 4 lowest 1 vCore option is showing as £160 per month while the lowest Gen 5 takes it up to £316 per month. This can be changed in SMSS when creating a database as required but the default will be the new purchasing models.
Here we can see what this looks like in the Portal with pricing in GBP.
Azure SQL Database Pricing – Gen 4, 1 vCore size
Azure SQL Database Pricing – Gen 5, 2 vCore size
Default Size in SQL SSMS
What’s the Difference Between DTU and vCore Purchase Options?
Basically, the DTU (Database Transaction Units) model uses a blend of compute, memory and storage designed for “common use cases”. The vCore model is the latest and default option, where cpu cores can be selected to closely match specifications of a server. vCore also allows for independent scaling of cpu and storage as well as Hybrid Use licensing benefits.
In our opinion, we don’t see a requirement for any “minimum” default database size to be £160 or £316 per month.
Please find more information on purchasing options here :- https://docs.microsoft.com/en-us/azure/sql-database/sql-database-purchase-models
How Can We Control SQL Database Deployment Sizes?
Ideally users would not have the ability to deploy databases, as this should be done using scripts, automation and approval processes. We see many organisations that do not have these mechanisms in place, so what other options are there? Use custom Azure Policies to highlight or deny the creation of any unapproved database SKUs.
How Else Can We Optimise Azure SQL Database Costs?
Deploying databases and hoping for the best can be costly in the long run with increased risk of future service outage. Azure SQL Database Cost Optimisation can be a very worthwhile exercise when done properly.
We have recently been working with customers to save SQL Server spend. In one case the monthly bill was reduced by ~£20,000, yes £20,000! Understanding what is in use vs requirements can have a dramatic impact on performance, and yield significant cost savings..
Follow our practical tips below to start getting your databases in shape:-
1. Database Archiving and Housekeeping
Ensure your databases are not storing more data than necessary, and are compliant with customer and GDPR requirements. Do you really need to store data indefinitely? If you do, then fine. If you do not, then make sure there is something in place to remove old data. There are many ways to do this and data partitioning is just one effective way of doing this. Also make use of DBCC (Database Console Commands) for maintenance such as DBCC ShrinkDatabase to bring database sizes back down after large archiving jobs to ensure you get the best pricing.
2. Correct Service Tier, SKU and Database Settings
Ensure your databases are not storing more data than necessary, and are compliant with customer and GDPR requirements. Do you really need to store data indefinitely? If you do, then fine. If you do not, then make sure there is something in place to remove old data. There are many ways to do this and data partitioning is just one effective way of doing this. Also make use of DBCC (Database Console Commands) for maintenance such as DBCC ShrinkDatabase to bring database sizes back down after large archiving jobs to ensure you get the best pricing.
3. Review Azure SQL Database Recommendation but do not Blindly Follow Them
Use the performance metrics and other recommendations provided by Azure SQL Server. Evaluate “Long Running Queries” to identify and view timings and frequency of slow running queries.
Azure SQL may provide index recommendations but these do not take into account everything such as inserts and updates. The recommendations would not pick up on database design issues and would not suggest alternative solutions such as query re-write, index column ordering or adding indexed views for example. A specialist such as ourselves, another Microsoft partner or experienced DBA can help with database design to yield significant benefits.
5. Auto Scaling
Azure SQL also offers a serverless option in which we can set min and max vCore settings from 0.5 cores up to 40 cores. This allows the resources to scale based on demand and bills for the compute used per second. This model automatically pauses the database when it is inactive and resumes once it is being used. We can control the autopause delay to set how long the database is inactive before it is paused. This may work well for databases that have unpredictable usage patterns or those that are not used for long periods of time. As a best practice applications should be able to withstand transient connectivity issues by using retry logic. See link here for more information on this feature and for example scenarios. https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
6. Intelligent Insights and Azure SQL Analytics
Intelligent Insights is a new service that uses AI technology to monitor your databases for many metrics such as wait times, locking and more to look for problems and provide root cause analysis. Use Azure SQL Analytics, a reporting tool for all your SQL Servers and integrates with Azure Monitor for intelligent performance troubleshooting.
Summary
Organisations are spending way more than necessary on their cloud environments by simply using the defaults provided by the platform/s. Virtual Machines (VMs) are another example of this. Default VM disks offered by Microsoft are the more expensive Premium SSD type, rather than Standard SSD or Standard HDD disks. Understanding these elements together with correct licensing, automation and governance all help to ensure services run optimally and cost efficiently. If you think your organisation is still over-spending, follow the steps in this guide as a starting point. Get in touch to speak with our experts as we love to help our customers save money.
Recent Comments