Press "Enter" to skip to content

Category: Cloud

Geo-Zone Redundant Storage for SQL MI Backups

Published 2022-10-18 by Kevin Feasel

Niko Neugebauer moves the backups pretty far away:

The new Geo-Zone Redundant Storage (GZRS) backup storage option combines the best of two worlds – Geo-Redundant and Zone-Redundant storage, keeping backups safe from both regional (Geo-Redundant) and Data Center (Zone-Redundant) failures. It provides the highest availability for storage currently offered on Azure, improving recovery speed and enabling Point-In-Time Restore (PITR) of backups in the event of a zone failure.  

Geo-Zone Redundant Storage for Azure SQL Managed Instance backups provides 3 synchronous copies in different availability zones within the same primary region, plus an additional asynchronous copy within a single availability zone in the paired secondary region, as shown on the following picture: 

Click through for that picture and what it does for expected availability. Basically, a whole bunch of data centers would need to fail before you lose a backup. Or someone messes up DNS and makes everything unavailable for a day, not that that’s ever happened before with a large cloud service provider…

Comments closed

Running Jobs in Azure SQL Database

Published 2022-10-17 by Kevin Feasel

Etienne Lopes gets a job:

In SQL Server, many automated tasks are implemented via SQL Server Agent jobs. If you’re used to it, you may get a bit sad when you learn that SQL Server Agent is not present in Azure SQL Database. However if those automated tasks rely on T-SQL then they can still be easily implemented in Azure SQL Databases using elastic jobs that provide the ability to run T-SQL scripts on a schedule or on-demand

Read on to see how they work.

Comments closed

General Purpose Tier Azure SQL DB Performance

Published 2022-10-17 by Kevin Feasel

Reitse Eskens continues a series on comparing tiers of Azure SQL Database:

In my previous blog, I wrote about the serverless tier, the one that can go to sleep if you’re not using it for more than one hour (minimum). That tier is cheaper as long as you’re not running it for more than 25% of the time. If you need more time, go provisioned.
Another difference between serverless and provisioned is that the provisioned one gets a set number of cores whereas the serverless one has a minimum and a maximum number of cores. So this time, the blog is about the provisioned tier where you choose a fixed number of CPU’s with a fixed monthly cost.

Click through for the analysis. I’ll reiterate here that I really hope Reitse has some graphics at the end (or at least tables) which sort of lay out where the boundaries between tiers are and what the performance and cost profiles look like between them.

Comments closed

Monitoring Azure SQL Backup History

Published 2022-10-14 by Kevin Feasel

Mustafa Ashour wants you to check your backups:

Database backups are an essential part of any business continuity and disaster recovery strategy, because they help protect your data from corruption or deletion. These backups enable database restore to a point in time within the configured retention period. By default, Azure SQL Database & Azure SQL Managed Instance stores data in geo-redundant storage blobs that are replicated to a paired region. Geo-redundancy helps protect against outages that affect backup storage in the primary region. It also allows you to restore your databases/instance to a different region in the event of a regional outage/disaster.

Read on to learn more about how Azure SQL DB and Azure SQL Managed Instance perform backups, their cadence, and how you can find information on backup history.

Comments closed

Azure Stream Analytics Job Diagram Simulator

Published 2022-10-13 by Kevin Feasel

Alex Lin points out some functionality to understand Azure Stream Analytics jobs:

Azure Stream Analytics (ASA) uses a SQL query language that has been augmented with powerful temporal operators to analyze data streams. One way to optimize a Stream Analytics job’s performance is to leverage parallelism in query. A parallel job divides the workload of queries into multiple streaming nodes and reduces the overall execution time.  

We strongly recommend that you use the Azure Stream Analytics Tools extension in Visual Studio Code (VS Code) for testing and debugging queries locally. For a job to be parallel, you need to align partition keys between all inputs, query steps, and outputs. The Job Diagram Simulator in VS Code allows you to simulate a job running topology with different streaming units (SUs) and provides suggestions for improving your query parallelism. 

Click through to see how it works and the kinds of information you can get from it.

Comments closed

Power Automate Trigger on Power BI Dataset Refresh Completion

Published 2022-10-13 by Kevin Feasel

Imke Feldmann wants to know when the work is done:

If you have been working with Power BI for a while now you might have come across the standard Power BI actions in Power Automate. They allow you to automate a nice bunch of Power BI processes. But while there is a trigger that starts a flow when a Power BI dataflow action has completed, no such trigger is available for when a Power BI dataset action has (successfully) completed. So here I will present a workaround that is still possible without a premium Power Automate license.

Read on for that workaround.

Comments closed

General Purpose Serverless Azure SQL DB Performance

Published 2022-10-10 by Kevin Feasel

Reitse Eskens doesn’t need a server:

In my previous blog I wrote about the premium tier, the one that can be compared with the business critical tier. Now we’re moving away from the DTU models and back to what we DBA’ers really understand, cores, memory and disks. Before I’m going to dive into the limitations, there’s one thing you need to understand. The serverless tier is made for intermittent use. If you’re using the tier for more than 25% of the time (or about 183 hours per month), you’re better of going provisioned. This has nothing to do with performance but everything with cost. The tipping point of provisioned being cheaper is around 25% of the time.

There’s some solid advice on how to get the service to go to sleep but the bulk of the article revolves around performance.

Comments closed

Failed to Update Server Firewall Rules in Azure

Published 2022-10-10 by Kevin Feasel

Andy Leonard gets an error:

Recently, I attempted to update the Networking for an Azure SQL Server I use when delivering training. Specifically, I was attempting to set “Public network access” to “Selected networks,” add my client IP, and check the “Allow Azure services and resources to access this server” checkbox – found on the Networking blade for my new Azure server:

Fortunately, this was a case of “read the actual error message, understand the actual error.” It doesn’t always work out that way but it’s nice when it does.

Comments closed

Monitoring Blocked Processes in Azure SQL DB

Published 2022-10-05 by Kevin Feasel

Etienne Lopes wants to see what the hold-up is:

Blocked processes (and deadlocks) are often one of the main factors responsible for performance issues in the databases, as such, it’s really important to monitor them effectively and if they exist then understand where, what, why, how often, duration, etc. Having that information will greatly help in the following fine tuning process.

In this post I’ll show one way to easily monitor blocked processes in an Azure SQL database.

Click through to learn how.

Comments closed

sqlpackage and Managed Identities

Published 2022-10-04 by Kevin Feasel

Nora Yang provides a guide:

– Enable AAD auth on Azure SQL server

– Conn to Azure SQL database via AAD admin

– Create contained user for the managed identity (using Azure VM name as contained username)

        create user <vmname> from external provider;

        alter role db_owner add member <vmname>;

Read on for the full set of steps.

Comments closed