Search Results for: iaas

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

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:

Add a 90 minute window at 12:00 for Server1
Add a 90 minute window at 02:00 for Server2
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.

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.

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

Timescale	Full backups	Log backups	Blob storage tier _{(East US – GPV2 – LRS – Standard}₎	Monthly Cost
Previous 7 years (5 DBs)	12,775	1,226,400	Hot	£4,784.79 + £1856.92 £6,641.71

Costs with a planned retention policy

Timescale	Full backups	Log backups	Blob Storage tier _{(East US – GPV2 – LRS – Standard)}	Monthly Cost
Previous 14 days	70	6,720	Hot	£27.13 £10.42 £38.55
15 days to 93 days	390	0	Cool	£110.45
94 days to 2,555 days	1758	0	Archive	£45.40
TOTAL	2,218	6,720	Mixed	£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.

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.

https://youtu.be/wmbANpHos_E

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.

Put tempdb files on D drive in Azure IAAS

21st March 2019 By John McCormack 5 Comments

Tempdb 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

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')
Create Folder D:\TempDB or something similar
Restart SQL Server and make sure your files are created in D: as expected
Manually remove any old tempdb files still lying around

Now to make sure tempdb is created when the VM restarts

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
Change the startup type of your SQL Services to Automatic (Delayed Start)
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

https://johnmccormack.it/2016/12/configuring-tempdb-sql-server-2016-instances/

Thoughts on sitting DP-300 Azure Database Administrator exam

3rd April 2023 By John McCormack Leave a Comment

On Sunday morning, I sat and passed exam DP-300 which gives the certification of Microsoft Certified: Azure Administrator Associate. I’ve been sitting Microsoft exams for a number of years now but this was my first for a while. I wanted to jot down what thoughts still remained in my head following the exam.

Preparation

I prepared for the exam by following the course from ACloudGuru. I’m a big fan of this site as it has helped me learn so much about AWS; so I thought I would trust it for Azure learning as well. I have to admit I had been going through the course at a snail’s pace and by the time of completion, I had forgotten some of things I had learned initially. I sat their exam simulator and got 77% which was not a pass.

I realised from doing the exam simulator that there were some areas where my knowledge was weaker, specifically on some newer features I hadn’t worked with day to day and also migration options for the likes of Postgres and MariaDB.

I wrote down each wrong answer, learned why it was wrong and what was correct. I resat the test and got 87%. I repeated the process and got 89%. A lot of the questions were repeating by now and I felt ready to sit the exam.

Sitting the DP-300 exam

The exam itself was MUCH harder than the exam simulator, however in fairness, the exam simulator was pushing you to achieve 80% which allowed a bit of a buffer come exam day. (Microsoft only look for 700/1000). There are some areas I think the simulator could have covered more extensively and a bigger bank of questions would have been helpful.

However when it came to the exam, I managed to rely on my experience to pull some old knowledge out of the darkest recesses of my brain, and made a few educated guesses. Thank fully I passed with 800+. Not the best score but the exam itself is pass or fail.

Focus areas for sitting DP-300

I can’t be too specific here due to NDA but I would suggest if you are an experienced SQL DBA but have limited or no recent Azure working experience, some areas to focus on are:

PAAS vs IAAS
1. Remember PAAS needs less administrative effort than IAAS or on premises. Look for clues in the question.
2. Business Critical and Premium vs General Purpose and Standard
3. Azure SQL Server IaaS Agent Extension
4. Database mail for managed instance
Migration options (Online and offline) – And not just for SQL Server
Encryption vs Dynamic data masking
Query store
Key Vault
Some Azure networking like Virtual network and VPN
Know your performance DMVs and how to diagnose blocking and slow executing queries
Backup of system DBs

What’s next

I’m still deciding but it’s given me the bug after a couple of years of not doing much active learning. So I think AZ-104 to help me re-establish some Azure infrastructure knowledge or DP-203 to introduce me to some parts of Data engineering that I’m looking to learn.

DP-900 – Exam prep reading list

17th August 2020 By John McCormack Leave a Comment

DP-900 Exam prep reading list (further reading)

This DP-900 Exam prep reading list comes from Microsoft Learn. Links will be added as I go through the course material / suggested reading on the Microsoft Learn site until I have completed the full course. I am currently working my way through the material.

If you are thinking of sitting DP-900 Azure data fundamentals, I recommend following through the free course on the Microsoft learn site (scroll down past exam info) and also reading the recommended links. The links may add a bit of time onto your learning, and you can be the judge about about how intensively you need to study the extra material, however the more prepared you are, the greater your chance of success with the certification exam. Links duplicated in Microsoft Learn are only shown once in the list below.

I find it great that Microsoft are making these resources available for free these days. Some video learning would also be nice but this is a great start.

Azure Data Fundamentals: Explore core data concepts

Explore core data concepts

Explore roles and responsibilities in the world of data

Describe concepts of relational data

Explore concepts of non-relational data

Explore concepts of data analytics

Azure Data Fundamentals: Explore relational data in Azure

Explore relational data offerings in Azure

Explore provisioning and deploying relational database offerings in Azure

Query relational data in Azure

Azure Data Fundamentals: Explore non-relational data in Azure

Explore non-relational data offerings in Azure

Explore provisioning and deploying non-relational data services in Azure

Manage non-relational data stores in Azure

Azure Data Fundamentals: Explore modern data warehouse analytics in Azure

Examine components of a modern data warehouse

Explore data ingestion in Azure

Explore data storage and processing in Azure

Get started building with Power BI