public/Get-AzureSQLIndexRecommendations.ps1
|
function Get-AzureSQLIndexRecommendations { <# .SYNOPSIS Returns sql index recommendations. .DESCRIPTION Returns sql index recommendations for Azure SQL (PaaS). .PARAMETER subscriptionName Specify the full name of the target subscription. .EXAMPLE Get-AzureSQLIndexRecommendations -subscriptionName <subscriptionName> Returns all index recommendations, for all Azure SQL Servers within said subscription. #> [CmdletBinding()] Param ( [Parameter(Mandatory=$true)] [ValidateNotNullOrEmpty()] [string]$SubscriptionName ) # check to see if local token exists (ran Login-AzureRMAccount) if (($null -eq (Get-AzureRmContext).Account)) { Write-Warning "Please run < Login-AzureRMAccount > first to create a session token...exiting." break } # Get subscription name Try { Select-AzureRmSubscription -SubscriptionName $subscriptionName -ErrorAction Stop -Verbose | Out-Null } Catch { $error[0].Exception break } $dbs_to_analyze = @() $recommendations = @() Write-Verbose "Retreiving list of SQL servers" $sql_Servers = Get-AzureRmSqlServer -Verbose Write-Verbose "Looping through SQL Servers to retreive list of databases to analyze" foreach ($sql in $sql_Servers) { Try { $db = Get-AzureRmSqlDatabase -ServerName "$($sql.ServerName)" -ResourceGroupName "$($sql.ResourceGroupName)" -ErrorAction Stop -Verbose } Catch { $error[0].Exception continue } $dbs_to_analyze += $db } foreach ($database in $dbs_to_analyze) { $parms = @{ ResourceGroupName = $database.ResourceGroupName; ServerName = $database.ServerName DatabaseName = $database.DatabaseName ErrorAction = 'Stop' } Try { $index_recommendation = Get-AzureRmSqlDatabaseIndexRecommendations @parms -Verbose $recommendations += $index_recommendation } Catch { $error[0].Exception continue } } return $recommendations } |