Azure

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

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

Goodbye JSON Since Microsoft released bicep version 0.3 I thought I’d give it a try because anyone that’s authored ARM templates will know, they tend to make your eyes bleed after a while. Bicep aims to drastically simplify the authoring experience with a cleaner syntax, improved type safety, and better support for modularity and code re-use. I’m sold! I reverse-engineered an existing simple template that deploys two storage accounts by using bicep decompile and got a template like this: ...

Maintenance Window now in Preview (at the time of writing) On 2nd March 2021, Microsoft announced a long-awaited feature, at least by me, to give choice over when OS and SQL patching occurs behind-the-scenes in Azure SQL Managed Instance. Before this feature, Microsoft would patch the Virtual Cluster supporting Managed Instances outside of regular business hours in the region your Managed Instance is located. You now get two additional options: Weekday window, 10PM to 6AM local time Monday – Thursday Weekend window, 10PM to 6AM local time Friday - Sunday System default: ...