Haikos Blog Blog von Haiko Hertes zu allen Themen rund um Microsoft und Datacenter

$ErrorActionPreference = "Stop"
$SubscriptionId = "YOUR_SUBSCRIPTION_ID"
 
function Get-SQLServerRecommendations()
{
    # Define the resource types
    $resourceTypes = ("Microsoft.Sql/servers/databases")
    $advisors = ("CreateIndex", "DropIndex","DbParameterization","SchemaIssue");
    $results = @()
 
    # Loop through all subscriptions
 
    $rgs = Get-AzureRmResourceGroup
 
    # Loop through all resource groups
    foreach($rg in $rgs) {
        $rgname = $rg.ResourceGroupName;
 
        # Loop through all resource types
        foreach($resourceType in $resourceTypes) {
            $resources = Get-AzureRmResource -ResourceGroupName $rgname -ResourceType $resourceType
 
            # Loop through all databases
            # Extract resource groups, servers and databases
            foreach ($resource in $resources) {
                $resourceId = $resource.ResourceId
                if ($resourceId -match ".*RESOURCEGROUPS/(?<content>.*)/PROVIDERS.*") {
                    $ResourceGroupName = $matches['content']
                } else {
                    continue
                }
                if ($resourceId -match ".*SERVERS/(?<content>.*)/DATABASES.*") {
                    $ServerName = $matches['content']
                } else {
                    continue
                }
                if ($resourceId -match ".*/DATABASES/(?<content>.*)") {
                    $DatabaseName = $matches['content']
                } else {
                    continue
                }
 
                # Skip if master
                if ($DatabaseName -eq "master") {
                    continue
                }
 
                # Loop through all Automatic tuning recommendation types
                foreach ($advisor in ($advisors -notmatch "SchemaIssue")) {
                    $recs = Get-AzureRmSqlDatabaseRecommendedAction -ResourceGroupName $ResourceGroupName -ServerName $ServerName  -DatabaseName $DatabaseName -AdvisorName $advisor
                    foreach ($r in $recs) {
                        if ($r.State.CurrentValue -eq "Active") {
                            $object = New-Object -TypeName PSObject
                            $object | Add-Member -Name 'SubscriptionId' -MemberType Noteproperty -Value $subscriptionId
                            $object | Add-Member -Name 'ResourceGroupName' -MemberType Noteproperty -Value $r.ResourceGroupName
                            $object | Add-Member -Name 'ServerName' -MemberType Noteproperty -Value $r.ServerName
                            $object | Add-Member -Name 'DatabaseName' -MemberType Noteproperty -Value $r.DatabaseName
                            $object | Add-Member -Name 'Advisor' -MemberType Noteproperty -Value $advisor
                            $object | Add-Member -Name 'Script' -MemberType Noteproperty -Value $r.ImplementationDetails.Script
                            $results += $object
                        }
                    }
                }
            }
        }
    }
    Return $results
}
 
$AzurePasswordSecure = ConvertTo-SecureString "$($YOUR_AZURE_PASSWORD)" -AsPlainText -Force
$AzureCredentials = New-Object System.Management.Automation.PSCredential ("$YOUR_AZURE_USER", $AzurePasswordSecure)
Connect-AzureRmAccount -Credential $AzureCredentials | Out-Null
Select-AzureRmSubscription -Subscription $SubscriptionId | Out-Null
 
$Recommendations = Get-SQLServerRecommendations
$table = $Recommendations | Sort-Object DatabaseName,Advisor | Format-Table Databasename,Advisor,Script -AutoSize -Wrap
Write-Output $table
 
$head = "<style>
td {background-color:lightgrey;}
table {width:100%;}
th {font-size:14pt;background-color:lightblue;}
</style>
<title>SQL Server performance recommendations</title>"
 
[string]$html = $Recommendations | ConvertTo-Html -Property Databasename,Advisor,Script -Body "<h1>Azure SQL Server automatic tuning recommendations for $stage</h1>Auto-generated by PUT_SOMETHING_HERE<br><br>" -Head $head
 
Send-MailMessage -Body $html -SmtpServer YOUR.SMTPSERVER.COM -From sender@domain.com -To recipient@domain.com -Subject "MS SQL Recommendations - $(Get-Date -Format "yyyy-dd-MM HH:mm:ss")" -BodyAsHtml