Error while executing Pause Azure SQLDatawarehouse ps script

Question

I am using Automation Account with execution of PowerShell Runbook for Pausing my Azure Datawarehouse database. Setup is completed with creation of crendentials, certificate for Thumbprints. When I executed the PS script and completed with error message

No Azure SQL Data Warehouse named ADWPOC exist on SQL Server At SuspendOrPauseAzureSQLDataWarehouse:72 char:72 + + CategoryInfo : NotSpecified: (:) [Write-Error], WriteErrorException + FullyQualifiedErrorId : Microsoft.PowerShell.Commands.WriteErrorException

I have provided the name of database and server details correctly while executing this script. I am not sure what is the problem here. Please advise.

I have imported the PS script from runbook gallery "Suspend Or Pause Azure SQL Data Warehouse". Please let me know if you want me to attach the script here?

Joy Wang-MSFT Joy Wang-MSFT · Accepted Answer · 2019-10-25T08:02:09

Not sure, but the script looks too old, it still uses the AzureRm module which has been deprecated.

To pause the data warehouse in the runbook, I recommend you to use new Az module, sample here, to use that in runbook, just follow the steps as below.

1.Navigate to your automation account in the portal -> Modules, make sure you have imported the Az.Accounts and Az.Sql modules, if not, in the Modules -> Browse Gallery, search for the modules and import them.

2.After importing the modules successfully, navigate to the Runbooks -> create a PowerShell runbook(not the PowerShell Workflow), then use the script as below in the runbook.

Note: The <server-name> should be like testserver, not testserver.database.windows.net.

$connectionName = "AzureRunAsConnection"
try
{
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName         

    "Logging in to Azure..."
    Connect-AzAccount `
        -ServicePrincipal `
        -TenantId $servicePrincipalConnection.TenantId `
        -ApplicationId $servicePrincipalConnection.ApplicationId `
        -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
}
catch {
    if (!$servicePrincipalConnection)
    {
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception
    }
}

$database = Get-AzSqlDatabase –ResourceGroupName "<resource-group-name>" –ServerName "<server-name>" –DatabaseName "<data-warehouse-name>"
if($database){

    if($database.Status -eq 'Paused'){
        Write-Output "The Data Warehouse was already paused."
    }else{
        $database | Suspend-AzSqlDatabase
        Write-Output "The Data Warehouse has been paused." 
    }

}else{

    Write-Output "The Data Warehouse does not exist."
}

Error while executing Pause Azure SQLDatawarehouse ps script

1 Answers