Invoke Rest API from an Azure Runbook ( Powershell) to scale up and Down an Azure Synapse Database

Question

I've created an Azure Powershell Runbook to scale UP or Down a Datawarehouse dedicated SQL Pool using the Invoke-RestMethod but is failing with the following error:

At line:31 char:11 + $Body = @ + ~ Unrecognized token in source text. At line:36 char:6 + "name" = "DW500c" + ~~~~~~ The assignment expression is not valid. The input to an assignment operator must be an object that is able to accept assignments, such as a variable or a property.

Here is the code being used

$Url = "https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$sqlServerName/databases/$SynapseSqlPoolName?api-version=2020-08-01-preview HTTP/1.1 Content-Type: application/json; charset=UTF-8"
    $Body = @ 
        {
            location: "West Europe",
            "sku":
            {
                "name" = "DW500c"
            }
        }       

    Invoke-RestMethod -Method POST -Uri $url -Body $body -UseBasicParsing

I've tried to change the "=" with ":" but it gives the same error

I've tried with the solution below provided by Mathias but I'm getting a new error

$Body = @{
        location = "West Europe"
        sku = @{
            name = "DW500c"
        }
    }

Error:

The remote server returned an error ( 400) Bad Request

Invoke-RestMethod : {"error":{"code":"MissingApiVersionParameter","message":"The api-version query parameter (?api-version=) is required for all requests."}} At line:38 char:3 + Invoke-RestMethod -Method POST -Uri $url -Body $body -UseBasi ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (System.Net.HttpWebRequest:HttpWebRequest) [Invoke-RestMethod], WebException + FullyQualifiedErrorId : WebCmdletWebResponseException,Microsoft.PowerShell.Commands.InvokeRestMethodCommand

Latest code but getting "The specified content type is invalid"

$Url = "https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$sqlServerName/databases/$SynapseSqlPoolName?api-version=2020-08-01-preview" 
    $ContentType = "HTTP/1.1 Content-Type:application/json;charset=UTF-8"
    $Body = @{
        location = "West Europe"
        sku = @{
            name = "DW500c"
        }
    }       

    Invoke-RestMethod -Method POST -Uri $url -ContentType $ContentType -Body $body -UseBasicParsing

The problem with the Content Type has been fixed but I'm still getting the api version error . Below is the full code

Param
(
    # This is the Resource group where Azure Synapse Analytics SQL Pool is located   
    [Parameter(Mandatory=$True)]  
    [String] $resourceGroupName
    ,
    # This is the name of the Azure SQL Server hosting the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $sqlServerName
    ,
    # This is the name of the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $SynapseSqlPoolName
    ,
    # This is the name of the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $SubscriptionId
    ,
    # This is the name of the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $Sku
)

    $ConnectionName = 'AzureRunAsConnection'
    $ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName

    'Log in to Azure...'
    $null = Connect-AzAccount `
    -ServicePrincipal `
    -TenantId $ServicePrincipalConnection.TenantId `
    -ApplicationId $ServicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint 

    $Url = "https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$sqlServerName/databases/$SynapseSqlPoolName?api-version=2020-08-01-preview" 
    $ContentType = "application/json;charset=UTF-8"
    $Body = @{
        location = "West Europe"
        sku = @{
            name = $Sku
        }
    }       

    Invoke-RestMethod -Method POST -Uri $url -ContentType $ContentType -Body $body -UseBasicParsing

Mathias R. Jessen · Answer 1 · 2022-05-03T19:24:11.707

0

I've tried to change the "=" with ":" but it gives the same error

That'd be the correct course of action, but make sure you use a hashtable literal (@{ ... }) for the inner sku object too:

$Body = @{
    location = "West Europe"
    sku = @{
        name = "DW500c"
    }
}

It appears that the string value you assign to $Url contains a little more than just the URL - remove the trailing HTTP header garbage from the URL, and then pass the Content-Type header value to the -ContentType parameter of Invoke-RestMethod:

$Url = "https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$sqlServerName/databases/$SynapseSqlPoolName?api-version=2020-08-01-preview" 
$ContentType = 'application/json;charset=UTF-8'

# ...

Invoke-RestMethod -Method POST -Uri $url -ContentType $ContentType -Body $body -UseBasicParsing

edited May 03 '22 at 19:24

answered May 03 '22 at 17:46

Mathias R. Jessen

157,619
12
148
206

I got another error after doing the suggested update , I've edited my question and added there – Josh Sama May 03 '22 at 19:05
@JoshSama It's probably because of the trailing request header values in `$Url`. Should be `$Url = "https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$sqlServerName/databases/$SynapseSqlPoolName?api-version=2020-08-01-preview"`. If you want to pass an explicit `Content-Type` header, use `Invoke-RestMethod`'s `-ContentType` parameter – Mathias R. Jessen May 03 '22 at 19:06
I've tried that but I see now, "The specified content type is invalid.. I've updated my question – Josh Sama May 03 '22 at 19:15
Should I remove the rest and just leave "application/json" – Josh Sama May 03 '22 at 19:18
@JoshSama That's because `"HTTP/1.1 Content-Type:application/json;charset=UTF-8"` is not a valid MIME type, you want `-ContentType 'application/json;charset=UTF-8'`. – Mathias R. Jessen May 03 '22 at 19:18
@JoshSama It almost looks like you copy-pasted this data from the first three lines of a raw HTTP request. You might want to [read up on what the individual components of an HTTP message header mean](https://developer.mozilla.org/en-US/docs/Web/HTTP/Messages) :) – Mathias R. Jessen May 03 '22 at 19:21
@JoshSama `;charset=UTF-8` is a valid (and meaningful) part of the content type descriptor, but the stuff leading up to `application/json` is not part of it. – Mathias R. Jessen May 03 '22 at 19:25
let me read the URL you just provided, I've added the full code to the question and the issue with the content type seems to be fixed but is going back to the API Version error :/ – Josh Sama May 03 '22 at 19:27

score 0 · Accepted Answer · answered May 04 '22 at 00:55

Thanks to Mathias R. Jessen for his guidance, thanks to the 100 websites I've reviewed and to the 100 tests or more performed today , I finally found the way to make it work. We need to keep in mind that there is a different URL for a normal Azure database VS a Synapse database ( mine is synapse DB).

There are a couple of more tweaks that can be done to the code like , getting the subscriptionId and apiversion automatically instead of hardcoding it and also using some try , catch and IF condition to do the scale the Synapse DB when is not Paused but the code below accomplish what needs to done to scaleup and down a Snapse DB using a Powershell Runbook in Azure ( calling an API)

Param
(
    # This is the Resource group where Azure Synapse Analytics SQL Pool is located   
    [Parameter(Mandatory=$True)]  
    [String] $resourceGroupName
    ,
    # This is the name of the Azure SQL Server hosting the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $sqlServerName
    ,
    # This is the name of the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $SynapseSqlPoolName
    ,
    # This is the name of the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $Sku
    ,
    # This is the name of the Azure Synapse Analytics SQL Pool
    [Parameter(Mandatory=$True)]  
    [String] $SubscriptionId
)

    $ConnectionName = 'AzureRunAsConnection'
    $ServicePrincipalConnection = Get-AutomationConnection -Name $ConnectionName

    'Log in to Azure...'
    $null = Connect-AzAccount `
    -ServicePrincipal `
    -TenantId $ServicePrincipalConnection.TenantId `
    -ApplicationId $ServicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint

    $azContext = Get-AzContext
    $azProfile = [Microsoft.Azure.Commands.Common.Authentication.Abstractions.AzureRmProfileProvider]::Instance.Profile
    $profileClient = New-Object -TypeName Microsoft.Azure.Commands.ResourceManager.Common.RMProfileClient -ArgumentList ($azProfile)
    $token = $profileClient.AcquireAccessToken($azContext.Subscription.TenantId)

    
    $authHeader = @{
        'Content-Type'='application/json'
        'Authorization'='Bearer '+$token.AccessToken
        }   


    $apiversion = "2021-06-01"
    $Url = "https://management.azure.com/subscriptions/$SubscriptionId/resourceGroups/$resourceGroupName/providers/Microsoft.Synapse/workspaces/$sqlServerName/sqlPools/$SynapseSqlPoolName"+"?api-version=$apiversion" 
    $ContentType = "application/json;charset=UTF-8"
    $Body = @{
        location = "westeurope"
        sku = @{
            name = $Sku
        }
    }   

    Invoke-RestMethod -Uri $Url -Method PUT -Headers $authHeader -Body ($body|ConvertTo-Json) ## -UseBasicParsing

Invoke Rest API from an Azure Runbook ( Powershell) to scale up and Down an Azure Synapse Database

2 Answers2