Category Archives: Azure

Upserting Data to Azure SQL DB using PowerShell

For a project involving Azure Security I needed to store fairly large amounts of data in an Azure PaaS database using PowerShell.

If a row already exists, I want to do an UPDATE command, otherwise an INSERT command, also known as an UPSERT in SQL.

It should also use parameters to avoid issues with quotes in fields, and should convert PowerShell null’s/empty objects to the SQL equivalent.

The following function is what resulted, it only supports a single WHERE clause, but should be easy to enhance for those looking to improve 🙂

function invoke-sqlUpsert{
    Param(
        [String]$tableName,
        [Array]$values, # example: @(@{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar})
        [PSObject]$primaryKey, # example: @{"column"="deviceId";"value"="123415";"dataType"=[Data.SQLDBType]::NVarChar}
        [System.Data.Common.DbConnection]$sqlConn
    )

    $sqlQuery = "BEGIN TRANSACTION;
    UPDATE $tableName
        SET "
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)=@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
        }
    }
    $sqlQuery = "$($sqlQuery)
        WHERE $($primaryKey.column) = @$($primaryKey.column);
    IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO $tableName ($($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$sqlQuery$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
        }
    }
    $sqlQuery = "$($sqlQuery)
            )
            VALUES (@$($primaryKey.column),"
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlQuery = "$($sqlQuery)@$($values[$i].column)"
        if($i -lt $values.Count-1){
            $sqlQuery = "$sqlQuery,"
        }
    }
    $sqlQuery = "$($sqlQuery));
        END
    COMMIT TRANSACTION;"
           

    $sqlCmd=new-object system.Data.SqlClient.SqlCommand($sqlQuery, $sqlConn)
    $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($primaryKey.column)",$primaryKey.dataType))) | OUT-NULL
    $sqlCmd.Parameters[0].Value = $primaryKey.value
    for($i = 0;$i -lt $values.Count;$i++){
        $sqlCmd.Parameters.Add((New-OBJECT DATA.SQLClient.SQLParameter("@$($values[$i].column)",$values[$i].dataType))) | OUT-NULL
        $sqlCmd.Parameters[$i+1].Value = $values[$i].value
    }

    for($i=0;$i -lt $sqlCmd.Parameters.count;$i++){
        if($sqlCmd.Parameters[$i].Value -eq $null){
            $sqlCmd.Parameters[$i].Value = [System.DBNull]::Value
        }
    }
    if($sqlCmd.ExecuteNonQuery() -ne 1){
        Throw $_
    }
}

An example of how to connect from an Azure Function before using this command:

using namespace System.Data.SqlClient
using namespace System.Net
$msi_authenticationResult = Invoke-RestMethod -Method Get -Headers @{'Secret' = $env:MSI_SECRET} -Uri ($env:MSI_ENDPOINT +'?resource=https://database.windows.net/&api-version=2017-09-01')
$sqlConn = New-Object System.Data.SqlClient.SqlConnection
$sqlConn.ConnectionString = "Data Source = yourserver.database.windows.net; Initial Catalog = yourdatabase"
$sqlConn.AccessToken = $msi_authenticationResult.access_token
$sqlConn.Open()

Add remoteapp to WVD hostpool using an ARM template

As I couldn’t find anyone who had written about adding a RemoteApp to a Windows Virtual Desktop hostpool (existing) in an existing Workspace, I figured I’d share my template.

  • It automatically creates an application group if it doesn’t exist yet
  • It takes into account existing applications if the application group already exists
  • The group has to be a remoteapp group, not a desktop group
  • This template is idempotent
  • This template can authorize any existing principal you specify
  • It creates 1 or more remoteapps in the group specified
  • It automatically manages the link to an existing hostpool/workspace

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/ARM%20templates/add-remoteAppToWVDHostPool.json

Incremental WVD deployments or redeploying persistent WVD hosts

When you add hosts to a WVD hostpool, and want to do so programmatically, this can be done by seperately deploying new hosts.

Redeploying an existing host manually is easy if it isn’t persistent, but if the disk should be retained, you’ll want to disconnect and reconnect it. This would require a lot of scripting (or third party tools).

As I wanted to keep our WVD solution contained to a single ARM template, some creativity was needed. For example, when deploying a new host that has a newer version of a gallery image, ARM would normally complain that the existing hosts don’t have the correct image version and it can’t change that for you since it is a read-only property (imageReference). Another example would be to move the VM between availability zones.

Linked WVD ARM template solves all these issues, but has a couple of important parameters to be aware of if you’re going to use it;

  1. redeploy: set to true in a scenario where you want to upgrade existing hosts, e.g. from a different VM type, or want to redeploy to another availability Zone. It will use the provided list of disk names to create new VM’s based on these existing disks
  2. existingDiskNames: if redeploy = true, supply the list of disk names of the persist VM’s you just deleted.
  3. vmNumberOfInstances: the number of VM’s you want to have (including existing VM’s)
  4. existingNumberOfInstances: the number of VM’s already present in the hostpool when running this arm template.
    #3 and #4 should be the same if redeploy = true, the template does not support redeploying and expanding the hostpool in the same deployment, this should be done in order.

After redeploying hosts, make sure to reconnect the users as they were assigned before you deleted them.

Download the ARM template here from GIT:

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/ARM%20templates/wvdDynamicHostpool.json

Enable WVD diagnostics for Azure Monitor using ARM Templates

Now that Azure Monitor has support for Windows Virtual Desktop, I wanted to enable this for all WVD workspaces, host pools and application groups.

Christiaan beautifully described how to do this through the Azure Portal, but of course, in an enterprise environment we don’t click around, we define everything as Code!

This example ARM template will enable the Diagnostics setting for the hostpool, workspace and application group vectors. To get data from VM’s (hosts), use your preferred method (plenty of examples exist already and depend on your setup).

After applying / including / redeploying this ARM template in your WVD environment, you’ll be able to see metrics coming in to the Azure Monitor Insights dashboard for WVD 🙂

https://gitlab.com/Lieben/assortedFunctions/-/blob/master/ARM%20templates/WVDDiagnosticSettings.json

Conditional nested ARM template to add WVD application group to Workspace

In Windows Virtual Desktop (ARM version), applications are part of application groups, which in turn get nested under Workspaces.

In an ‘Infra As Code’ world these should be deployed through ARM templates (or Az Cli/Ps scripts). I had a long wrestle with ARM today getting applications assigned to workspaces ONLY if they weren’t already assigned.

Azure throws a friendly 400 error if you try to add an app that already exists, and interestingly, the ARM ‘contains’ function fails to properly evaluate WVD Workspace members when in a nested template.

So, I had to resort to some trickery by converting it to a string. For anyone else wanting to incrementally attach application groups to workspaces, feel free to copy/clone my template 🙂

Git source

{
    "$schema": "http://schema.management.azure.com/schemas/2014-04-01-preview/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "workspaceName": {
            "type": "string",
            "metadata": {
                "description": "The name of the Workspace."
            },
            "defaultValue": "NLD-WVD-WS01"
        },
        "workspaceResourceGroup": {
            "type": "string",
            "metadata": {
                "description": "The workspace resource group Name."
            },
            "defaultValue": "WE-WVD-RG"
        },
        "appGroupName": {
            "type": "string",
            "metadata": {
                "description": "The name of the Application Group to be linked."
            },
            "defaultValue": "testag2"
        }
    },
    "variables": {
        "appGroupResourceId": "[resourceId('Microsoft.DesktopVirtualization/applicationgroups/', parameters('appGroupName'))]"
    },
    "resources": [
        {
            "apiVersion": "2018-05-01",
            "name": "AddAppGroupToWorkspaceIncrementally",
            "type": "Microsoft.Resources/deployments",
            "resourceGroup": "[parameters('workspaceResourceGroup')]",
            "properties": {
                "mode": "Incremental",
                "template": {
                    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
                    "contentVersion": "1.0.0.0",
                    "resources": [
                        {
                            "name": "[parameters('workspaceName')]",
                            "apiVersion": "2019-12-10-preview",
                            "condition": "[not(greater(indexOf(string(reference(concat('/subscriptions/',subscription().subscriptionId,'/resourceGroups/',parameters('workspaceResourceGroup'),'/providers/Microsoft.DesktopVirtualization/workspaces/',parameters('workspaceName')),'2019-12-10-preview','Full').properties.applicationGroupReferences),variables('appGroupResourceId')),0))]",
                            "type": "Microsoft.DesktopVirtualization/workspaces",
                            "location": "eastus",
                            "properties": {
                                "applicationGroupReferences": "[union(reference(concat('/subscriptions/',subscription().subscriptionId,'/resourceGroups/',parameters('workspaceResourceGroup'),'/providers/Microsoft.DesktopVirtualization/workspaces/',parameters('workspaceName')),'2019-12-10-preview','Full').properties.applicationGroupReferences,array(variables('appGroupResourceId')))]"
                            }
                        }
                    ]
                }
            }
        }        
    ]
}