Posts

As we all know backing up to Azure Storage has been easy for some years now, but only since SQL Server 2022 have Microsoft supported backing up to S3. This includes third party providers that implement the S3 bucket protocol. Why do this? To keep backups safe and secure, best practise dictates that backup files should be off-site. Rather than backing up locally to a volume and then copying the file to S3, money can be saved by not having to provision space for local volumes. It is common in AWS to use an FSx volume for backups, or even a mounted EBS volume, but these costs can be removed by backing up directly to an S3 bucket. ...

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

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

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. When that was run all looked fine. ...

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

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. A SAS token is a shared access signature that grants limited access to a resource in Azure. You can generate a SAS token for your Azure Blob Storage container using the Azure portal, PowerShell, CLI, or SDK. ...

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

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

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. However, it seems that it’s not possible to create a NIC in another Vnet and attach it to a VM attached to a different vnet. Also, as you may know a VM must have at least one NIC attached at all times which must be connected to a vnet. ...

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. The first test was done using the latest snapshot backup, and it took around two minutes to complete. However, when I checked the overview page for the virtual machine in the Azure portal, I noticed that the virtual machine agent status was not ready. This meant that I couldn’t connect to the VM, not even from within the virtual network. ...