ARM deploymentScripts resource is GA

There is new resource in Azure called Microsoft.Resources/deploymentScripts ( which fills void of ability to run custom code as part of deployment where ARM code on it’s own is not sufficient.

In the background Azure creates ACI container for you with Managed Identity and Az CLI/Powershell tools so you can pretty much do anything your heart desired which you could have accomplished from command line.

Example which I run into and which was solved with new resource is ability to calculate future date with specific time where built-in time functions of ARM templates are insufficient.

Azure automation account softwareUpdateConfigurations resource requires scheduleInfo startTime property to be specified in future only which on it’s own not difficult to implement in just ARM date functions but impossible to set it to specific time of the day. (Say 10 AM EST which is required for recurrence of patching cycle). This is where sample use of deploymentScripts is shining since powershell can easily tackle this setup to calculate tomorrows day at specific time.

Example below adds startTime property to be tomorrows day at specific time as required by passing parameter to powershell script.

Template is below with highligted line referencing output of deploymentScripts providing information about next date/time.

         "type": "Microsoft.Automation/automationAccounts/softwareUpdateConfigurations",
         "apiVersion": "2017-05-15-preview",
         "copy": {
            "name": "deploymentScriptcopy",
            "count": "[length(parameters('other').schedule)]"
         "name": "[concat(parameters('management'), '/', parameters('other').schedule[copyIndex()].Name)]",
         "dependsOn": [
            "[resourceId('Microsoft.Automation/automationAccounts', parameters('management')]",
            "[concat('GetNextScheduledDate-script-', parameters('other').schedule[copyIndex()].Name)]"
         "properties": {
            "updateConfiguration": {
               "operatingSystem": "Windows",
               "windows": {
                  "includedUpdateClassifications": "Critical, Security, UpdateRollup, FeaturePack, ServicePack, Definition, Tools, Updates",
                  "rebootSetting": "IfRequired"
               "targets": {
                  "azureQueries": [
                        "scope": "[parameters('other').subscriptionList]",
                        "tagSettings": {
                           "tags": {
                              "patchgroup": [
                           "filterOperator": "All"
                        "locations": []
               "duration": "PT2H"
            "scheduleInfo": {
               "startTime": "[reference(concat('GetNextScheduledDate-script-', parameters('other').schedule[copyIndex()].Name)).outputs.text]",
               "expiryTime": "9999-12-31T17:59:00-06:00",
               "interval": 1,
               "frequency": "Month",
               "timeZone": "UTC",
               "advancedSchedule": {
                  "monthlyOccurrences": [
                        "occurrence": "[parameters('other').schedule[copyIndex()].weekNumber]",
                        "day": "[parameters('other').schedule[copyIndex()].weekDay]"

DeploymentScripts code is below which takes as a parameter Hour variable and returns next date at that specific hour via $DeploymentScriptOutputs['text'] variable

         "type": "Microsoft.Resources/deploymentScripts",
         "apiVersion": "2020-10-01",
         "copy": {
            "name": "deploymentScriptcopy",
            "count": "[length(parameters('other').schedule)]"
         "name": "[concat('GetNextScheduledDate-script-', parameters('other').schedule[copyIndex()].Name)]",
         "location": "[resourceGroup().location]",
         "kind": "AzurePowerShell",
         "properties": {
            "forceUpdateTag": "1",
            "azPowerShellVersion": "5.0",
            "scriptContent": "
             param (
                [Parameter(Mandatory = $true)]
             $output = (Get-Date -Hour $Hour -Minute 0 -Second 0).AddDays(1)
             $DeploymentScriptOutputs = @{}
             $DeploymentScriptOutputs['text'] = $output
            "arguments": "[concat(' -Hour ', parameters('other').schedule[copyIndex()].Hour)]",
            "timeout": "PT1H",
            "cleanupPreference": "OnSuccess",
            "retentionInterval": "P1D"

How to use Azure monitor workbooks for managing Azure inventory

Posts earlier ( and showed how to assist inventory managers to get necessary information for auditing or similar reasons which are lacking in native Azure portal.

This post will show how to provide similar functionality by utilizing following technologies

  1. Azure monitor workbooks
  2. Azure Resource Graph behind the scenes

Start with navigating to Azure Monitor/Workbooks and create new workbook from Empty. Import code for workbook by clicking “Edit” button and click “advanced editor”/”Gallery Template”. Code to paste is available here

Once saved you shall get working inventory workbook with 2 tabs. One is showing geographic distribution of your VMs and second tab list of VMs with preset information in template.

Details about how this all put together is below.

Azure monitor workbook allows to execute Azure Resource Graph queries and present results in multitude of visualizations.

Current workbook consists of 2 tabs which first element of workbook initialising 2 tabs (Maps and VMs). As action for user who selected a tab a parameter names view is set to specific value which is used in views to hide/show specific items.

Next is resource graph query for all VMs in subscription with visualization of type Map. Resource graph query is below pulling all VMs and summarizing them by location

  | where type == 'microsoft.compute/virtualmachines'
  | where resourceGroup notcontains 'databricks'
  | summarize ["Number of Resources"] = count() by ["Location"] =  location

This view shall be only shown when user selects Map tab which is governed on second tab of the view

Next view is view for VMs which are result of graph query below

  | where type == 'microsoft.compute/virtualmachines'
  | where resourceGroup notcontains 'databricks'
| join kind = leftouter (
| where type == 'microsoft.compute/virtualmachines'
 | mv-expand disk = properties.storageProfile.dataDisks
 | summarize TotalDataDisks = sum(toint(disk.diskSizeGB)) by name, id
 ) on $ == $
 | extend Name = name,
 ["Resource Group"] = resourceGroup,
   location,  ["OS Type"] =  properties.storageProfile.osDisk.osType, VMsize = properties.hardwareProfile.vmSize,
   OsDisk = properties.storageProfile.osDisk.diskSizeGB,
   associated_nic = extract(".*/(.*)\"}$", 1, tostring(properties.networkProfile.networkInterfaces[0])),
   associated_nic_id = tostring(properties.networkProfile.networkInterfaces[0].id),
   powerstate = strcat_array(split(properties.extended.instanceView.powerState.code, "/", 1), '')
 | join kind = leftouter (
   | where type == ''
   | project ["Public IP Address"] = properties.ipAddress,
     associated_nic = extract(".*/networkInterfaces/(.*?)/.*", 1, tostring(
   ) on associated_nic
  | join kind = leftouter (
  | where type == '' 
  | project PrivateIP = properties.ipConfigurations[0].properties.privateIPAddress, 
  subnet = strcat_array(split(properties.ipConfigurations[0], "/", 10),''), 
vnet = strcat_array(split(properties.ipConfigurations[0], "/", 8), ''), id,
["accelerated networking"] = properties.enableAcceleratedNetworking
) on $left.associated_nic_id == $
| project Name, ["Resource Group"], location, tostring(["OS Type"]), tostring(VMsize), tostring(OsDisk), TotalDataDisks, tostring(["Public IP Address"]), tostring(powerstate), tostring(PrivateIP), subnet, vnet, tostring(["accelerated networking"]), tags

It’s KQL query with aggregation and conditional functions to get desired set of columns. You can debug KQL query separately from workbook inside Azure Resource Explorer. Only subset of KQL language is available for Resource Graph queries, which are listed here.

You can pass parameters from different areas of workbook to a query via query parameter which is done for passing Location parameter to Azure Resource Graph query as denoted below with squiggly brackets

| where location in ({location})

Additional query item is added called VMsizesJSON is JSON based view with static values for available VM sizes, which is later joined to results of Azure Resource Graph query above to add CPU/RAM capability for each VM since those values are not available directly in Azure Resource Graph

Power column is custom column of type Icon which allows to display images based on value of a cell with wide range of icon choices. In this case it’s used to display if VM is running or not.

Data Disks column is represented as Heatmap and also formatted as bytes sizes value to correctly display Gib/Tib etc.

Additional ability can be added for people to be able to export data to Excel file for offline viewing/analysis

So far solution based on Azure monitor workbooks seems to be the best choice for outputting Azure inventory information for consumption, it’s robust, easy to maintain and always current

Automating Azure SQL firewall rules based on Azure Service Tags

Azure SQL firewall rules are based on start and end IP addresses and do not use either CIDR notation or have any capability to use Azure Service Tags (like NSGs or Azure firewall can). So if your task is to allow inbound connectivity to Azure SQL only from certain Azure serviceTags then it will require you to manually check IP ranges for specific service tags periodically and manually input/update/delete in Azure SQL firewall rules.

Solution below will assist in this task by periodically pulling JSON file with IP ranges for various service tags in Azure and apply/update them properly in Azure SQL firewall rules. Please note that as of right now (August 2020) ServiceTag API is not returning current information and solution relies on downloading complete JSON file from Azure by parsing HTML contents to find location of latest file.

Deployment steps

ARM template and parameter file can be downloaded from Github repo. Template creates 2 resources (Logic App and API connector) and requires 3 parameters to work.

"LogicAppResourceGroupID": {
 "value": "/subscriptions/8211cd03-4f97-4ee6-af42-38cad1387992/resourceGroups/LogicApp-RG"
"SQLResourceID": {
 "value": "/subscriptions/8211cd03-4f97-4ee6-af42-38cad1387992/resourceGroups/SQL-RG/providers/Microsoft.Sql/servers/whateverserver21"
"ServiceTagName": {
 "value": "HDInsight.EastUS"
  • LogicAppResourceGroupID parameter is Azure Resource ID for ResourceGroup where your logic app is located
  • SQLResourceID parameter is Azure Resource ID for SQL server where you want to modify firewall rules
  • ServiceTagName parameter is name of service tag which you want to incorporate into Azure SQL firewall

Deploy ARM template along with parameter file either from command line or Azure portal.

Once deployed additional step needs to be taken to authorize API connection for LogicApp to perform actions on your behalf. Go to Logic App designer and any step which requires call to Azure API (for example Connections under Try read current Tags. It will have exclamation point next to it. Click on Invalid button and authorize connection on next screen

Run Logic App and if everything works ok you will see following

  • Network Security Group called dummy created in Logic App resource group which will have tag with a name of ServiceTag and Iteration Number. Iteration number identifies latest version of IP ranges for specific ServiceTag.
  • SQL firewall rules update with IP ranges for desired service tag

Implementation details

Logic app specified in ARM template executes following steps

  1. Try to read current tags from network security group and find current Iteration number
  2. Downloads HTML file of the page which allows to download JSON file
  3. Parses HTML file to find location of JSON file
  4. Downloads JSON files
  5. Parses JSON and filters only ServiceTag requested
  6. Compares Iteration Number in downloaded file to current iteration number obtained in step 1
  7. If Iteration number is different then read all current SQL firewall rules
  8. Iterates through each IP range obtained in step above and checks if there is already existing firewall rule corresponding to current item
  9. If there is none then new rule needs to be created
    • API is called to convert CIDR notation into start/end IP addresses
    • Response is parsed
    • New Firewall rule is created
  10. Iterates through all existing firewall rules to find any existing copies of firewall rules which are no longer needed (IP ranges were deleted from servicetag)
  11. If there is a firewall rule which do not have a match with new IP range then firewall rule is deleted
  12. If all steps above succeeded then update NSG tag to latest Iteration number for specific ServiceTag

Windows Failover cluster using shared disks in Azure

Creating SQL FC on shared disks

Create new resource group and create 2 Ultra Disks in supported region. First disk is for storing data and second one is quorum drive

$rg = New-AzResourceGroup -Name SharedDisk-RG -Location 'East US'
$datadiskconfig = New-AzDiskConfig -Location $rg.Location -DiskSizeGB 1024 -AccountType UltraSSD_LRS -CreateOption Empty -DiskIOPSReadWrite 2000 -DiskMBpsReadWrite 200 -DiskIOPSReadOnly 100 -DiskMBpsReadOnly 1 -MaxSharesCount 5 -Zone 1
New-AzDisk -ResourceGroupName $rg.ResourceGroupName -DiskName SharedDisk -Disk $datadiskConfig
$datadiskconfig = New-AzDiskConfig -Location $rg.Location -DiskSizeGB 8 -AccountType UltraSSD_LRS -CreateOption Empty -DiskIOPSReadWrite 2000 -DiskMBpsReadWrite 200 -DiskIOPSReadOnly 100 -DiskMBpsReadOnly 1 -MaxSharesCount 5 -Zone 1
New-AzDisk -ResourceGroupName $rg.ResourceGroupName -DiskName QuorumDrive -Disk $datadiskConfig

Create 2 VMs using image SQL Server 2019 on Windows 2019. I used SQL 2019 free Developer license for demo purposes. Compatible VM sizes with ultra disk are available here. I used D8s_v3. VM shall be deployed to availability zone which supports UltraDisk. You can find zone number by executing following. VMs needs to be joined to AD once they are provisioned. It’s requirements for all nodes to be in AD for FCI to work

PS /home/azureuser> $region = "eastus"
PS /home/azureuser> $vmSize = "Standard_D8s_v3"
PS /home/azureuser> (Get-AzComputeResourceSku | where {$_.Locations.Contains($region) -and ($_.Name -eq $vmSize) -and $_.LocationInfo[0].ZoneDetails.Count -gt 0})[0].LocationInfo[0].ZoneDetails

Name      Capabilities
----      ------------
{1, 3, 2} {UltraSSDAvailable}

Attach 2 ultradisks as data disk and quorum drive to both created VMs by stopping it and enabling Ultra disk functionality and adding both shared disks.

Login to VMs and install failover clustering and administration tools on both nodes

PS C:\Users\cloudadmin> Install-WindowsFeature Failover-Clustering, RSAT-Clustering

Success Restart Needed Exit Code      Feature Result
------- -------------- ---------      --------------
True    Yes            SuccessRest... {Remote Server Administration Tools, Failo...
WARNING: You must restart this server to finish the installation process.

Create failover cluster by adding 2 nodes to it

Open disk management and create volume/format your shareddisk and quorum drive. Add disks to Storage/Disks in cluster manager.

Configure disk quorum for cluster and choose quorum disk as cluster setting

Launch SQL 2019 installation from installation media and choose “New SQL server failover cluster installation”

Go through wizard installation and choose appropriate resources

Configure cluster IP to be any static IP address from VNET

Configure data directories to be on shareddisk

Go to SQL Server Configuration manager and enable IP connectivity on both dedicated and cluster server IP addresses, restart SQL service

SQL server can be accessed by either VNN (virtual network name) or DNN (distributed network name). Difference between those are documented here (link). DNN is new offer which available only on certain versions of OS and SQL server. It essentially registers IP addresses of both nodes in DNS and informs client to choose currently active node. No need for internal load balancer. To create DNN for my example I used powershell below. DNN resource name is sqldnnresource and DNS name is sqldnn.

PS C:\Users\cloudadmin.SQL> Add-ClusterResource -Name sqldnnresource -ResourceType "Distributed Network Name" -Group "SQL Server"

Name           State   OwnerGroup ResourceType
----           -----   ---------- ------------
sqldnnresource Offline SQL Server Distributed Network Name

PS C:\Users\cloudadmin.SQL> Get-ClusterResource -Name sqldnnresource | Set-ClusterParameter -Name DNSName -Value sqldnn
PS C:\Users\cloudadmin.SQL> Start-ClusterResource -Name sqldnnresource

Name           State  OwnerGroup ResourceType
----           -----  ---------- ------------
sqldnnresource Online SQL Server Distributed Network Name

Once resource is started you can verify that resource in fact registered DNS name and it shall return IP addresses of both nodes of the cluster

PS C:\Users\cloudadmin.SQL> resolve-dnsname sqldnn

Name                                           Type   TTL   Section    IPAddress
----                                           ----   ---   -------    ---------
sqldnn.sql.local                               A      1200  Answer
sqldnn.sql.local                               A      1200  Answer

Go to second server and choose “Add a node to SQL Server Failover Cluster” as installation procedure and finish adding second node to SQL cluster created earlier.

At this point you shall have a following:

  1. Windows Failover Cluster
  2. SQL Server failover cluster
  3. 2 shared disks (one used as a data drive and another one as a quorum)
  4. DNN (distributed network name) which points to all nodes in your cluster

Testing SQL FC

Following powershell script will help to test functionality of failover. Run the script and it will output current owner of SQL Cluster. You can test failover in Failover cluster manager by moving cluster role to a different node.

import-module sqlserver
function QueryDB{
$mirrorConnString = "Server=sqldnn;MultiSubnetFailover=True;user id=sa;password=A1234567890!;Connection Timeout=3"
try {
$record = (Invoke-Sqlcmd -Query "SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS')" -ConnectionString $mirrorConnString -AbortOnError:$false -ErrorVariable err -ErrorAction SilentlyContinue).column1
$record = "No response"

"{0} {1}" -f $(Get-Date), $record
while ($true)
start-sleep 1

Output would be similar to below. You can see downtime usually less then 30 seconds.

PS C:\Users\cloudadmin> script.ps1
8/13/2020 5:53:45 PM SQL1
8/13/2020 5:53:46 PM SQL1
8/13/2020 5:53:47 PM SQL1
8/13/2020 5:53:53 PM No response
8/13/2020 5:53:54 PM No response
8/13/2020 5:53:55 PM No response
8/13/2020 5:53:56 PM No response
8/13/2020 5:53:57 PM No response
8/13/2020 5:54:01 PM SQL2
8/13/2020 5:54:02 PM SQL2
8/13/2020 5:54:03 PM SQL2
8/13/2020 5:54:04 PM SQL2
8/13/2020 5:54:05 PM SQL2

Ultimate test though is to abruptly end cluster resource instead of scheduled failover. You can use notmyfault.exe to crash your server.

Downtime for unexpected shutdown is slightly longer (around 1 min)