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

Azure IaaS SQL Backups – Stop burning money

14th January 2021 By John McCormack 2 Comments

This post is part of a planned series on 7 ways for data professionals to save money in Azure. It’s so easy for costs to escalate in the cloud, and being proactive is the best way to optimise your costs, and make sure you aren’t spending more than you need to. In this post, I look at how to cut costs by implementing an Azure IaaS SQL Backups retention policy and ensuring you are not paying for more than you need.

Azure IaaS SQL Backups

This advice is to specific on-premises or IaaS backups which are going directly into an Azure blob storage account. Whilst blob storage can be reasonably cheap, if you keep every backup with no retention policy, the costs will soon escalate.

Scenario

You have 5 databases on a SQL Server Instance. You take daily full backups of each database on your instance. You also take log backups every 15 minutes as each database is in full recovery mode. This means in 1 week, you will have 35 full backups and 3,360 transaction log backups. This multiplies to 1,820 full and 174,720 t-log backups over 52 weeks. Multiply this for 7 years or more and the costs can get very expensive.

Your company policy only requires point in time restore functionality for 2 weeks of data. On top of that, daily full backups for the previous 3 months and a weekly full backup for between 3 months and 7 years must be kept.

For simplicity, lets assume an average full backup size of 25GB and a log backup size of 100MB.

Costs with no planned retention policy

TimescaleFull backupsLog backupsBlob storage tier
(East US – GPV2 – LRS – Standard
)
Monthly Cost
Previous 7 years (5 DBs)12,7751,226,400Hot£4,784.79 + £1856.92
£6,641.71

Costs with a planned retention policy

TimescaleFull backupsLog backupsBlob Storage tier
(East US – GPV2 – LRS – Standard)
Monthly Cost
Previous 14 days706,720Hot £27.13
£10.42
£38.55
15 days to 93 days3900Cool£110.45
94 days to 2,555 days17580Archive£45.40
TOTAL2,2186,720Mixed£194.40

So if we optimise the storage to only keep the full and t-log backups that we need, and we use cool and archive storage correctly, we can save over £6,000 per month. There are four main ways to actively manage data retention in order that you optimise your cloud costs, without breaching your data retention policy.

1. Azure Blob Storage lifecycle management

Lifecycle management rules are a simple way to manage files within blob storage. You can either delete files or move them to a cheaper storage class. The rules are fairly customisable so this a basic option which works well without too much configuration.

There are huge savings to be made by moving storage to the archive tier. If you’re holding on to backups for regulatory purposes or to adhere to company policies but won’t be using them regularly, the archive tier provides great value for money. Your data may take several hours to be available when you make a request so it’s not suitable for any disaster recovery scenario but providing you don’t mind waiting, you will cut your Azure bill substantially.

https://docs.microsoft.com/en-us/azure/storage/blobs/storage-lifecycle-management-concepts?tabs=azure-portal

2. SQL Server IaaS Agent extension

If you have SQL Server running in Azure VMs, you can opt to have them automatically backed up by the SQL Server IaaS Agent Extension. Whilst there is some customisation available, you can’t retain your backups beyond 30 days. This makes it unsuitable for our scenario here but if all you need is 30 days or fewer, then this is a great option. Having Azure automatically handle the backups, gets you one step closer to Platform as a Service whilst still retaining the flexibility or running a full SQL Server instance.

sql server iaas agent extension backup

Automated backups for SQL Server 2014

Automated backups for SQL Server 2016+

3. Azure Backup (SQL Server VMs)

You can use Azure backup, via recovery services vault to configure backups on multiple servers. You can choose to back up specific databases or all, including any new ones added after configuration by selecting AUTOPROTECT. There are very detailed retention options which allow you to set a very fine grained retention policy.

4. Roll your own solution

This isn’t needed as much as it used to be. It used to be the case that the lifecycle rules were very inflexible and to keep certain backups, and change storage options, you needed to use your own script. My preferred choice was using PowerShell and Azure Automation Runbooks. With one client, this solution saved hundreds of thousands of pound per year.

* I would only recommend this if the two options above don’t provide you with the flexibility you need to manage your storage retention, according to your own complex requirements.

P.S Don’t forget backup compression

With all of these solutions, don’t forget to enable compression on your backups. This will help you save even more money in addition to the retention policy you have just put in place.

IT Certification Category (English)728x90

Filed Under: Azure, cost-optimization, front-page Tagged With: azure billing, backups, cost optimisation, cost optimization, sql backups

Let me solve your SQL Server problems

11th January 2021 By John McCormack Leave a Comment

I’ve started a SQL Server consultancy business, focussing on short term engagements with ongoing support where needed. I’m keen to know what problems you are having with SQL Server that you would like someone to have a look at it.

What do you get from me?

In my bespoke SQL Server health checks ,I’ll present the results with actionable steps for you to take. View a sample health check. I also offer cost optimization reviews to help you save £1,000s from your Azure Bill.

What is the cost?

The SQL Server health check is £295 + VAT. The cost optimization review depends on the size of your environment. You can book a free introductory call to discuss.

Why would you want my help?

Experience

I’ve been working with SQL Server for around 10 years now. I’ve worked with large multinational companies and privately held enterprises, that operate on a global scale. I have a track record of solving problems related to SQL Server performance and I’m an experienced production DBA as well.

I also have a proven track record in cost optimisation of cloud resources, on both AWS and Azure. In my last contract, I saved the customer over £400k per year between SQL and storage costs.

My Certifications

  • MCSE: Data Management and Analytics certification
  • AWS Cloud Solutions Architect Associate
  • Azure: AZ-900 & DP-900

Client feedback

DBA Troubleshooting Impact

It’s been a genuine pleasure John. You’ve come to the rescue so many times I’ve lost count.

Duncan Lundie, Head of Application Operation, City Facilities Management

Best of luck John. It’s been a pleasure and you’ve been a lifesaver (more than…) a few times

Ronnie McGee, Senior Project Manager, City Facilities Management

Thanks for everything John. You will really be missed in terms of approach, achievements, mindset and skills! It’s been great working with you and hopefully our paths will cross again.

Stephen Meeke, Principal Engineer, City Facilities Management
Cost optimization (cloud)

Superb work on project X! Proactive, detailed, outcome driven and focused!

Nick Prudhoe, CTO, City Facilities Management

John is doing a fantastic job on Project X pushing the boundaries and saving thousands of pounds to Mercury. For me, it’s not just the cost savings which makes him stand out but the “Agile” thinking and drive to make things better.

Gautam Sivakumar, Cluster Lead, City Facilities Management

Public Profile

I’ve spoken at major data platform conferences such as SQLBits, Data Scotland and DataGrillen. I blog regularly and I also contribute to open source projects on GitHub. I try to be a member of the SQL community who gives as much as he takes.

What other things can I help with?

  • I can review and fix your indexes.
  • Tune queries, stored procedures or views to return data faster.
  • Health check of your instance, to reassure you or tell you what needs to be fixed.
  • Review your cloud expenditure and identify savings.
  • Configure Availability Groups.
  • Most production DBA activities/tasks.

These are just a few examples, you can ask me anything.

Let’s do this, send me your details to request a SQL Server Health Check.

If you prefer, you can book a free 15 minute introductory call directly with me.

Alternatively, you can call me on:  +44 (0) 330 133 2710.

Filed Under: front-page Tagged With: sql server health check, sql server problems

Firefighting – When your SQL Server is in big trouble

6th January 2021 By John McCormack Leave a Comment

When your SQL Server is in big trouble

It’s so important to stay calm when your SQL Server is in big trouble. I’m talking about when blocking is through the roof, when CPU is pinned and people are yelling from all different directions. Staying calm isn’t just about a state of mind, you need to have a process that you work through, that you have practised and you know inside out.

Part 1

How bad is it?

Silhouette of a firefighter in front of a blaze

In this post, I want to describe what I call a priority 2 problem. It is serious and business impacting but the server is online. It needs immediate attention however or it could escalate to a P1.

P1 – Business critical system(s) are unavailable
P2 – Serious impairment of functionality on a critical system
P3 – Performance is slower than usual but requests are completing
P4 – Performance is generally sub optimal and should be improved

Get a colleague on comms

One thing that is guaranteed to happen is that people who are not directly involved in fixing the issue, will want updates. They have good reason to want updates and are well meaning, but the constant need to reply to emails or Teams messages will seriously impact the speed at which you are working.

Back in the good old pre-covid days of office working, this could easily be achieved by someone sitting near you and the two of you communicating verbally. With remote working being more and more common now, I recommend you have some kind of audio channel open that you can speak when you need to and silence is ok too. This could be a phone call on speaker, or a teams call. The technology isn’t the main thing here, the idea is that you can express updates vocally to someone capable of communicating with the wider organisation.

Where possible, your colleague should be technically capable of answering related questions. They should open a chat in Teams or Slack or whatever software your organisation prefers and provide regular updates there. They should answer questions, keep to the update schedule and most importantly, divert people away from you.

A pre practiced routine

Now the scenarios can be different, in this one I want to talk about how I would handle a SQL Server which appears to be functioning (barely) but is extremely slow. In other words, it is a priority 2 incident as defined above. Helpdesk supervisors are calling tech support to advise and customers are calling them as the website is unusable. On top of that, the MI team and data scientists are contacting the DBAs directly because their queries won’t run.

Have a script or checklist

In my script, I tend to use well established community stored procedures. The sort of ones that most DBAs know about and many use. If you start trying to write new queries in the middle of a slow down, that is going to cost you time. Stick with what works, what is established and what you have used before and know well.

I’ve mentioned these in previous posts but the main things I am going to run are:

  1. sp_whoisactive – https://github.com/amachanic/sp_whoisactive
  2. sp_blitzfirst – https://www.brentozar.com/first-aid/
  3. sp_blitzlock – https://www.brentozar.com/first-aid/

sp_whoisactive

I always run this first because it it designed specifically for showing you current database activity, and it has saved my bacon so many times in the past. Simply running this parameter free will show you what is running on your SQL Server right now. It is ordered by query duration descending so the long runners are at the top. It can give you blocking information, information on wait types and on open transactions as well. For advanced features, you need to use some parameters.

sp_blitzfirst

If you can find what you need with sp_whoisactive, you may not even need to use this stored procedure, Where this procedure comes into its own is it tells you when certain things have recently changed or when they out of the ordinary.

e.g. sp_blitzfirst will tell you if:

  1. The plan cache has been recently erased
  2. You have high CPU utilization from a program other than SQL Server
  3. How long each wait type has been waiting during the previous 5 seconds

If something stands out that you don’t understand, there will be data in the URL column that you can copy/paste into a web browser. The web page will give you an explanation of the problem.

sp_blitzlock

This proc is all about deadlocks. You might not need to run it if the first two have given you enough information to fix the issue in hand. However, if deadlocks are at the root of your problems, this will tell you which queries are involved and when. It’s also easier to read than deadlock graphs.

There are more things that I’ll run in the fullness of time but we’ll leave them for later. I only want to focus on these 3 things for now to zero in on the immediate problem.

The script

USE dba

/*
	To run this, you also need to install:
	- sp_whoisactive
	- sp_blitzfirst
	- sp_blitzlock
*/

-- Raises an error if you run the whole script in error
RAISERROR ('Dont run it all at once',20,-1) WITH LOG

-- sp_whoisactive with various parameters
EXEC sp_whoisactive @find_block_leaders = 1 --,@get_locks = 1
EXEC sp_whoisactive @sort_order = 'sql_text' -- Tells at a glance if you have a lot of the same query running. For the F5 report runner troublemakers


-- What has been hurting us in the last 5 seconds. Look for wait stats, and anything out of the ordinary, such as the plan cache has been recently erased.
EXEC dbo.sp_BlitzFirst @expertmode = 1


-- Are we experiencing deadlocks 
EXEC sp_BlitzLock

-- Deadlocks in last hour
DECLARE	@StartDateBlitz datetime = (SELECT DATEADD(HH,-1,GETDATE())), @EndDateBlitz DATETIME = (SELECT GETDATE())
EXEC sp_BlitzLock @EndDate = @EndDateBlitz, @StartDate = @StartDateBlitz
GO



/*  Some other things to consider

	Have the usual optimisation jobs run as expected. Stats/indexes etc
	If one proc has regressed badly, could it help to clear only that plan from the cache or to recompile it.
	EXEC sp_blitz -- Although not as handy as the others for real time issues. Better used as a health check
	EXEC sp_blitzcache -- More useful for helping you identify the resource hungry queries, allowing you 
	EXEC sp_readerrorlog 0,1, 'memory'
*/

The calm after the storm

I will dedicate a full post to this however for now, it is important to say that slow queries or resource intensive queries should be identified and added to a backlog for fixing. If one particular query is likely to go bad again, it should be treated as a priority to fix, in order that we don’t see the same issue escalating again.

You should also do a SQL Server Health Check in order to satisfy that you don’t have an sub optimal configuration causing you issues.

IT Certification Category (English)728x90

Related to: When your SQL Server is in big trouble

Locks, blocks and deadlocks in SQL Server
Zero Budget DBA – SQLBITS 2020

Filed Under: front-page, Performance Tuning, SQL Server Tagged With: firefighting, sql server troubleshooting, triage

2020 blog – a year in numbers

18th December 2020 By John McCormack Leave a Comment

Blog Performance

A few thoughts on my blog performance for 2020.

I have more than doubled my page views from 2019 to 2020 so I’m naturally delighted about that. At the time of writing and with 13 days of 2020 to go, I have had 16,773 page views. The average time on each page was 3 minutes and 53 seconds which tells me that real people are reading the content. I wasn’t really tracking this all year so these numbers were a bit of a surprise to me. In the full year of 2019, I only had 7018 page views with an average page viewing time of 3 minutes and 26 seconds.

  • 2019 blog visitors
    2019 blog visitors
  • 2020 blog visitors
    2020 blog visitors

Things are going quite well and I’ve decided to aim for 30,000 page views in 2021. Doubling the numbers again is not going to be easy, but I hope to achieve this with a steady flow of new content and updating some old material. Breaking it down, it is a modest target of 82 page views per day so is achievable if I continue to add good content.

Regular Content

I’ve been trying to put out regular content, although it is not all the same theme. I tend to find that when I solve an interesting problem, I tell myself that it would make an interesting blog post. So I’ve blogged on Azure, PowerShell, Performance tuning and whatever the #tsql2sday topic was that month. My aim for 2021 is to be a bit more specialised so you will see more posts on Azure SQL DB and Managed Instance, as well as performance tuning. These are areas I would like to work more on in 2021. Of course, I’m going to keep going with the #tsql2sday posts because they usually provide an interesting topic I wouldn’t otherwise think of.

Blog successes

One of my recent posts on database mail with managed instances has gained some traction. Although only published in October, it is already in my top 10 page views for the year. My feeling is that this is the sort of thing that people are googling for now, the MS documentation isn’t amazing either and its on trend.

One of my first ever blogs about sending html emails using database mail is at number 9. I find that hard to believe because when I see old dates on blog posts, I tend not to want to read them. I remember publishing this post and wondering if anyone would read it and even worse, if I would be ridiculed for it.

My number 1 post for the 2nd year running was EC2 SQL Server Backups to Amazon S3. I haven’t read it for a while and I think it might be in need of a major update, so I plan to do that in the next few weeks.

My 2nd most popular post, also for the 2nd year running was How to create a table in AWS Athena. I used Athena a lot in my old job and I found the documentation difficult to understand so I put together my own instructions. I can’t believe that it is still one of my most popular posts and in fact, it grew from 768 views in 2019 to 2741 views in 2020. It’s a sign to me that AWS Athena is gaining a lot of popularity. The post is 3 years old though so I think it needs to be refreshed too.

Blog disappointments

I wrote a full video series about SQL Server on AWS RDS. It wasn’t a hit. In fact, it only accounted for 246 page views. The videos are on YouTube as well and have only had 237 views. The videos were all subtitled to help with accessibility and it took me a long time. My son also did some subtitling and he got some pocket money for helping out. I have to learn from this though, I didn’t appear in the videos, they were purely PowerPoint and demos and when I think of the videos that I enjoy watching, they always have an engaging presenter.

I’m currently creating a new video series called fundamentals of Azure SQL DB Managed Instances. I hope to learn from the mistakes of my RDS course, and hopefully it will reach a wider audience.

Filed Under: front-page, Personal Tagged With: blog performance

IaaS++ (Azure SQL Server IaaS Agent Extension)

11th December 2020 By John McCormack Leave a 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

  • « Previous Page
  • 1
  • …
  • 3
  • 4
  • 5
  • 6
  • 7
  • …
  • 15
  • Next Page »
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 © 2023