Search Results for: paas

A month in the life of a DBA contractor

8th February 2019 By John McCormack Leave a Comment

I left what I considered to be a great permanent job to become a DBA contractor in Glasgow. It was one where I could pick some of my own projects, work with new and interesting technology and work in a team which collaborated well, liked each other and were good at sharing knowledge.

My reasons for moving on were two-fold. First of all, I wanted some career progression and it unfortunately just wasn’t available in my permanent job. Secondly, I wanted to work more with SQL Server and Azure. My last job started out as a SQL DBA but as a lot of people in this line of work will know, the duties have evolved considerably over time. This is mostly due to large scale cloud adoption. In my case, my workplace started heavily using AWS along with other RDBMSs and data platforms. (RDS MySQL & Aurora, ElasticSearch, Elastic Map Reduce (EMR), Glue and Athena). This gives you a lot to learn. Don’t get me wrong, I threw myself into learning about cloud solutions and I loved working with AWS (hopefully I will again) but as time went on, I was worried I would start to lose some SQL Server knowledge.

So what has the first month been like?

I have been working exclusively on the Microsoft Data Platform. At my first client, I’ve worked with SQL Server on-premises, SQL Server running on Azure VMs, Azure SQL DB, Azure Analysis Services and Azure SQL Data Warehouse.

On-Premises

The on-prem SQL Servers are mostly for hosting the databases of legacy applications and 3rd party vendor products. Some of the versions are ‘rather old‘ although these instances actually give the business the fewest headaches. I’ve worked on decommissioning unused databases that were still online, server side traces and native backups.

Azure VMs running SQL Server (IaaS)

These host the databases that have the most active development work. I’ve looked at performance issues and SQL Server configurations to help improve performance. I’ve shared best practice with the team in terms of tempdb and helped to reduce the volume of unnecessary emails from servers. (You know the ones which someone set up years ago but never induce any action from the DBAs).

Azure Analysis Services (PaaS)

This was fun. I only did some basic configuration and permissions but as I’ve never used Analysis Services much over the years, it was cool to see what was involved.

Azure SQL Data Warehouse (Paas)

It was great to get my hands on tech like this. I spent time modernising their manual point and click refresh process with PowerShell. This was a bit of a learning curve but extremely rewarding. This process will make future refreshes much easier. (I’ll share the code once I’m confident it’s good, I’d still like a few more run-throughs before doing this). Next month, I’d like to try to implement a self service process for the developers that allows them to do their own refreshes, freeing up DBA time to work on more critical items.

Azure SQL DB (PaaS)

So far, I haven’t had to do too much with Azure SQL DB on this project. I’ve listed out the instances and instance types using PowerShell. My plan is to review if these are right sized or if efficiencies can be made and I’ll also make sure the alerting is set up correctly.

Plans for next month

I’d like to offer some in depth server health reviews and work with the developers to help them make their code run faster. There is a lot of blocking due to long running stored procedures and sub optimal code. Improving a few of the big hitters will make them much happier and will ease the strain on some of our servers – at least that’s the idea.

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.

Turn the cloud off at bedtime to save 70%

18th January 2021 By John McCormack Leave a Comment

Turn the cloud off at bedtime

When you operate in a public cloud such as Azure or AWS, you pay compute costs for all of the time that your instances are online. For your business facing and business critical systems, that is usually 24/7. But what about all those non production instances that run all night and all weekend? If these don’t need to be online 24/7, you can save up to 70% of your running costs by switching them off. This is what is meant by turn the cloud off at bedtime.

Example SQL Server VM

Your development hours are the standard 9am to 5pm. To allow some flexibility, you wish to make your development instances available to developers from 8am until 6pm.

Weekly hours	Price	Saving %
168	£152.76	–
50	45.46	70.24%

^{* E16s V4 SQL Server VM in UK West, with Dev/Test licensing on Pay as you go pricing model}

Deallocate is important

Ensure that your VM status is set to deallocated in the Azure portal to ensure you don’t receive ongoing billing for your compute expenses. This is best achieved by using the Azure Portal, CLI or PowerShell. If you just shutdown a machine, this won’t deallocate your compute resources and you will still be charged.

Compute savings only

The savings to be made are on compute resources as well as any associated licenses when they are included in your PAYG pricing. These are usually your biggest expenses anyway, but it’s worth noting that you will still be charged for any storage attached to your instance.

PAYG vs Reserved Instances

This post in primarily but not exclusively focussed on PAYG instances. There is a whole post to follow on reserved instances (RI) but I’d like to say this for now: You may still make savings by shutting down a reserved instance out of hours, if you have another instance (perhaps used by a dev team in a different time zone) that can pick up the free allocation. The billing for these is complex however. This page explains how RI reservations work against VMs in more detail.

How to turn the cloud off

When I first did this, it involved a mixture of Automation runbooks, PowerShell and instance tagging. There also wasn’t any flexibility to keep instance on for a bit longer without deleting the tag. Thankfully, Azure have made this process much easier now with this guide on how to start/stop VMs during off-hours.

What can’t be turned off

Certain PaaS services don’t allow the start and stop of services. Azure SQL Database and Managed Instance do not allow the stopping or pausing of the service. This takes this money saving option away from you. You could certainly save out of hours by scaling down your instance on a schedule using automation runbooks. This is a fairly quick operation for SQL DB. As for Managed Instance, it could take up to 4 hours so whilst this isn’t usually a great option for overnights, you could consider it for weekends.

The alternative is dropping and recreating your SQL DBs or managed instances each time using Infrastructure as Code. It’s more work but potentially worth the effort if you are running many and/or large PaaS instances.

What can be turned off

These posts are aimed at SQL professionals so I will stop short of listing everything on Azure however VMs as mentioned extensively in this post are the main choice. They are easy to stop and start manually, and the automation of this process is now easier than ever.

There is a PaaS Service that allows pausing of compute and that is Synapse Analytics. SQL Pools can be brought online for ETL purposes and paused the rest of the time. This can offer significant savings on Synapse Analytics.

Summary

Review all of your non production data services in Azure. If you have a lot, automation is the best and only way to keep fully on top of ensuring you are only paying for the services when you need them. Optimizing costs isn’t a one-time event. You need to be constantly on top of your bill to ensure that new instances are adhering to your cost optimization plans.

As always, please feel free to leave a comment if you wish to join the discussion.

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