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

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

CodePudding user response：

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

CodePudding user response：

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