PauseResourcesIfRunning.ps1

workflow PauseResourcesIfRunning {
    Param
    (
        [Parameter (Mandatory = $true)]
        [string] $subscriptionName
    )

    write-output "Started script with:
    Subscription - $subscriptionName"
    
    $connectionName = "AzureRunAsConnection"
    try {
        $servicePrincipalConnection = Get-AutomationConnection -Name $connectionName         

        "Logging in to Azure..."
        Add-AzureRmAccount `
            -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
        }
    }

    write-output "selecting subscription"
    Select-AzureRmSubscription -SubscriptionName $subscriptionName

    write-output "Get all SQL Datawarehouses in the subscription"
    $dws = Get-AzureRmResource | Where-Object ResourceType -EQ "Microsoft.Sql/servers/databases" | Where-Object Kind -ILike "*datawarehouse*"
    
    #Loop through each SQLDW
    foreach ($dw in $dws) {
        write-output "found data warehouse:"
        $rg = $dw.ResourceGroupName
        $dwc = $dw.ResourceName.split("/")
        $sn = $dwc[0]
        $db = $dwc[1]
        write-output "$rg, $dwc, $sn, $db"
        $status = Get-AzureRmSqlDatabase -ResourceGroupName $rg -ServerName $sn -DatabaseName $db | Select Status
        
        $dwStatus = $status.Status
        if ($dwStatus -ne "Paused") {
            write-output "Status is $dwStatus, pause the warehouse"
            #If the status is not equal to "Paused", pause the SQLDW
            Suspend-AzureRmSqlDatabase -ResourceGroupName "$rg" -ServerName "$sn" -DatabaseName "$db"
        }
        else {
            write-output "Status is $dwStatus"
        }
    }
}