azure sql db Archives - John McCormack DBA

You don’t need to run CHECKDB on Azure SQL DB

12th February 2021 By John McCormack Leave a Comment

right or wrong sign for dbcc checkdb — Featured image by Tumisu from Pixabay

CHECKDB on Azure SQL DB?

This is a selfish post because I’ll probably forget and go googling for info on this topic, so I though I would link to an authoritative answer on the question, do you need to run DBCC CHECKDB on Azure SQL DB?

https://azure.microsoft.com/en-gb/blog/data-integrity-in-azure-sql-database/

Microsoft distinguished engineer Peter Carlin says you don’t need to, it is monitored by Azure on your behalf. And if something was to go wrong, they will also work with you to put things right.

Although Peter says you can run them if you want, his post gives me enough peace of mind to stop running these on my Azure SQL DB instances. Let me know if you agree in the comments.

For more Azure content, check out my Azure tag.

How to find out Azure SQL DB Startup time

10th February 2021 By John McCormack 1 Comment

Alarm clock and cup of tea — Wakey Wakey SQL DB

Azure SQL DB Startup time

The query

SELECT sqlserver_start_time FROM sys.dm_os_sys_info;

Why write a post about this?

The traditional methods used for to find the start up time for SQL Server don’t work in Azure SQL DB.

I searched high and low to find this and thought I’ve got to share, and hopefully make it search engine friendly. A traditional google or bing search wasn’t bringing up the best way to find this out. I saw a lot of complicated queries to pull data, convert it and estimate start up time using functions and all kinds of magic. Actually, it turns out that the start up time is provided for you in sys.dm_os_sys_info (which works in Azure SQL DB). It also work in SQL Server which is a bonus.

Permissions to check Azure SQL DB Startup time

The permissions you need to run this query vary depending on the pricing tier of your Azure SQL DB. I ran this successfully on a P2 and a basic tier database.

On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the Server admin or an Azure Active Directory admin account is required. On all other SQL Database service objectives, the VIEW DATABASE STATE permission is required in the database.
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-info-transact-sql?view=sql-server-ver15

I hope this one liner t-sql script was useful. Please let me know in the comments if you know a better way to get the start up time of Azure SQL DB, using T-SQL.

While you’re on my site, why not take a look to see if you can save money on your Azure SQL DBs, and other Azure resources by looking at my guide: 7 ways for data teams to save money in Azure.

Featured image by congerdesign from Pixabay

Save with reserved instances – even up to 73%

21st January 2021 By John McCormack Leave a Comment

This post on how to save money with Azure Reserved instances is part of a 7 part series on cost optimisation with Azure.

Save with reserved instances

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?

What is a reserved instance?

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.

When would I reserve

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.

Save with Reserved Instances (Examples)

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

_{^{3 x E8s v4 (8 vCPUs, 64 GB RAM); Windows – SQL Server; 3 year reserved; 0 managed OS disks – S4, 100 transaction units; Inter-region transfer type, 5 GB outbound data transfer from UK West to East Asia}}

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

^{1 x Single Database, vCore, RA-GRS backup storage, General Purpose, Provisioned, Gen 5, Local redundancy, 1 8 vCore instance(s) x 730 Hours, 32 GB Storage, 0 GB backup storage}

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.

Reserved Instance Allocation

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.

What type of resources can I reserve

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.

Azure services eligible to be reserved instances

What if my needs change?

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.

Save money with Azure SQL DB serverless

20th January 2021 By John McCormack Leave a Comment

This post on how to save money with Azure SQL DB Serverless is part of a 7 part series on cost optimisation with Azure.

Provisioned vs Serverless

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.

So how do you save money with Azure SQL DB Serverless

Autopause

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. ↙

Autoscaling

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.

Per second billing

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.

Remember retry logic

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.

7 ways for data teams to save money in Azure

19th January 2021 By John McCormack Leave a Comment

Save money in Azure

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.

Implement Elastic Pools to share resources
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.