/images/markallison.jpg

Welcome

A blog about Cloud Computing, Data and DevOps

Connect Logic App to Azure Sql Securely

The Problem Recently, I was working on a personal project where I needed to export data from my Azure SQL Database into a JSON file roughly every hour. This JSON file would then be used by a static website hosted on Azure Blob Storage. I wanted a secure way to do this without maintaining passwords, so I decided to use Managed Identity to connect my Logic App to the Azure SQL Database.

Script to Remove NOLOCK Hints

The problem Recently, I was performance tuning and enhancing the reliability of several SQL Server 2019 and 2022 databases for a client and discovered NOLOCK hints everywhere. Why are these bad, they seem to improve performance and remove blocking for most queries? 1. Dirty reads This allows data to be read that is currently being modified by another query leading to inconsistent results. If a row that has been read gets rolled back because of an error like a deadlock, or network issue, the data never existed!

CPU Hot Add Breaks Fulltext Search

Overloaded server I recently worked on a SQL Server 2019 CU27 on-premises server that was running at 100% CPU sustained several times during business hours and also during the night running batch processes. This was impacting users, who were complaining. The VM was running in vmware which supports hot cpu add functionality. Hot add The number of CPUs was doubled from 8 to 16 to deal with the customer workload. In order for SQL Server to use these CPUs in SQL Server 2019, the RECONFIGURE command must be run.

How to Move Sql Distribution Database

The Problem The incident Recently, a system I am working on experienced a noticeable slow response time for users due to high PAGEIOLATCH_SH waits. These waits are SQL Server waiting on a latch to read data from disk into memory. At the time we noticed that the distribution agent cleanup task was running for over an hour and checking the latency on the disks revealed that disk read time was peaking at 400ms, when it should be below 10ms.

SQLMI Restore Blob Storage Access Denied

If you are using Azure SQL Managed Instance and encounter an error like this when restoring a database from an Azure Blob Storage container: Msg 3201, Level 16, State 2, Line 1 Cannot open backup device ‘https://{storage-account}.blob.core.windows.net/my-container/mydb/FULL/mydb-backup.bak’. Operating system error 5(Access is denied.). You can try the following steps to fix it: 1. Check the SAS token and generate a new one if needed. The most common cause of this error is that the SAS token used to access the Azure Blob Storage container has expired or is invalid.

Trouble Connecting Adf to Github

Hi there! I had a frustrating issue this week which had a very simple solution but it took me way too long to find it! Problem As mentioned in my previous article, I created a new resource group to demonstrate different Integration Runtimes with ADF. As part of this I tried to connect ADF to GitHub to source control the code and had real trouble. I’m using Microsoft Edge and I could connect to my repo but I got these errors:

Which ADF Integration Runtime for my workload?

Self Hosted or Managed Virtual Network for ADF? I was recently asked which runtime would be better to run ADF pipelines in Azure; use Private Managed Endpoints with the Managed Virtual Network, or provision a Self-Hosted Integration Runtime on a Virtual Machine? PaaS vs. IaaS My default position on which technology to use is PaaS where possible. Organisations should focus on providing value to their business, not micro-managing infrastructure. PaaS reduces the operational burden of managing infrastructure in most cases.

How to Move a VM to Another Vnet in Azure

Hello everyone, In this blog post, I want to share with you a problem that I faced this week with moving a VM from one vnet to another in Azure. It was not as easy as I expected and it required some downtime. The problem I had a VM that needed to move to another vnet as part of organisational change. I thought it would be a simple task of creating a new network interface card (NIC) in the target vnet and attaching it to the VM.

The Importance of Testing Your Azure Backups

My recent experience Disaster recovery testing is a critical aspect of any organization’s infrastructure. It is crucial to regularly test the resilience of your systems and make sure that they are properly backed up and can be restored in case of an emergency. Recently, a VM that had been restored as part of disaster recovery testing would not start but entered an infinite blue-screen boot loop. Blue screen loop Testing Backups As part of disaster recovery resilience testing, I restored a virtual machine from Azure Recovery Services Vault.

Troubleshooting Get-AzVirtualNetwork

Are you encountering the following error message when working with Azure in PowerShell and running the command Get-AzVirtualNetwork? 1 2 3 'Microsoft.Azure.Management.Network.Models.SecurityRule', on 'T MaxInteger[T](System.Collections.Generic.IEnumerable`1[T])' violates the constraint of type 'T'. This error can be caused by an incompatibility between PowerShell 7 and the Az Module and incompatibilty with .NET 7. To fix this issue, try the following steps: Upgrade to the latest versions of PowerShell 7 and the Az module.