I had an interesting issue today when a colleague was running a long running IO intensive query. I ran sp_who2 to view the activity and saw that the query was running multi-threaded, but was blocked by a SPID of -5. Yes, that’s SPID minus five, or negative five. (putting this here so that google searches maybe work?). blocking SPID -5 Google not working I tried to use Google to get information on this but it seems that Google doesn’t allow you to search for negative numbers. So searching for SPID -5 doesn’t show any decent information to understand what’s going on. ...

As a standard practise for IaaS and on-premises SQL Servers, I usually like to install Ola Hallengren’s maintenance scripts. Why invent the wheel when we already have a super-shiny one? These scripts include integrity checks, which run DBCC CHECKDB. Is DBCC CHECKDB needed anymore? It depends. 😉 It comes down to a cost/benefit analysis. I have been working with Sql Managed Instances quite closely over the past couple of years and wondered whether DBCC CHECKDB needs to be run on these. The whole point of CHECKDB is to notify us that something is wrong but also to do something if the database becomes corrupt in some way. They are very IO intensive operations which can impact user queries on the server. ...

What if you could do a dry run of your PowerShell script when making a change to your production environment? If you’ve read my other posts, you will know I am a big advocate of infrastructure-as-code. Most of my work is done in the Microsoft Azure cloud, and although I encourage source controlling all assets within Azure, sometimes developers create things manually through the portal. Yes, I know! 🙄 Removing Azure Resources safely A scheduled drift report should alert developers or DevOps engineers that the production state is out-of-sync with the source controlled code. If this happens, and some resources need removing, then the -WhatIf switch comes in handy and provides confidence to remove items in production that shouldn’t be there. ...

One of my ADF pipelines started failing recently where a Generic REST API Linked Service gave the following error: 525: SSL handshake failed Digging deeper into the logs, I see a message from Cloudflare. It appears that the SSL configuration used is not compatible with Cloudflare. This could happen for a several reasons, including no shared cipher suites. HTTP/3 QUIC There had been no code changes in ADF, but something on the source side had changed. After working with the data provider, it turns out that Cloudflare was using HTTP/3 which causes the ADF connection to fail. ...

Windows Terminal was released in 2019 for Windows 10 on the Microsoft Store, and I only just found out about it. Not sure why I’ve been living under a rock all this time, but I’m absolutely loving this app. I found out about it after playing around with the GA release of Windows 11 last week and noticed that Windows Terminal was installed as part of the default build of the Windows 11 Pro edition. ...

An issue came up this week where some external consultants needed access to remote onto an Azure Windows VM to configure a third-party application. The consultants were given AAD logins and are working from home with dynamic IP addresses. What is the best way to grant them access to RDP onto the VM? The preferred method would be via a private endpoint into the VNet via point-to-site VPN or Bastion, but external consultants can’t be granted access that way. ...

Consider the scenario where some PowerShell scripts need to be scheduled on an Azure virtual machine, which connects to an Azure SQL Managed Instance. The PowerShell script needs to run on a schedule and must be able to connect to the managed instance securely. You would rather not have to manage passwords. What’s the best way to do this? You guessed it, Managed Identity. This post will describe two ways of doing it, one using Azure AD with a Managed Identity and one using SQL Authentication and Azure Key Vault using a Service Principal. ...

I have set up server auditing for some managed instances in the same virtual cluster to record login success and failed events and write them to blob storage. Downstream, an ADF pipeline reads the events asynchronously and inserts them into a database for further analysis and reporting. Server audit stops writing Recently the server audit stopped writing to blob storage, even though the server audit status was still running and enabled. I have seen failures before where the server audit failed because of an expiring SAS token, but in this case the SAS token has a long expiry date, so know it’s not that. ...

Bicep stopped deploying with this error I’m trying to deploy a resource group which has two storage accounts in it with this PowerShell command using a bicep template: New-AzResourceGroupDeployment -ResourceGroupName $resourcegroupName -TemplateFile .\azuredeploy.bicep -TemplateParameterFile .\azuredeploy.test.parameters.json Error New-AzResourceGroupDeployment: Cannot retrieve the dynamic parameters for the cmdlet. Build succeeded: 0 Warning(s), 0 Error(s) I haven’t made any code changes, just not deployed this resource for a while. I decided to update bicep to the latest version (at the time of writing is Bicep CLI version 0.4.412 (f1169d063e)). ...

Storage underlying a Managed Instance Microsoft Azure should not require you to have deep understanding of the underlying technologies used to provide the service, after all the whole point of PaaS is to abstract you away from implementation details. One of these details is storage of data files in the file system. When provisioning a General Tier Sql Managed Instance, the maximum storage size for the instance is capped at 8 TB. However this is not the whole story and it’s important to understand some of what is happening under the covers, especially if you are migrating an on-premises SQL Server instance to the cloud that contains a lot of databases or uses partitioning. ...