Usare PowerShell per il monitoraggio e il ridimensionamento di un pool elastico SQL nel database SQL di AzureUse PowerShell to monitor and scale a SQL elastic pool in Azure SQL Database

Questo script di esempio PowerShell consente di monitorare la metrica delle prestazioni di un pool elastico, ne aumenta le prestazioni a un livello superiore e crea una regola di avviso per la metrica di una delle prestazioni.This PowerShell script example monitors the performance metrics of an elastic pool, scales it to a higher performance level, and creates an alert rule on one of the performance metrics.

Questo esempio richiede il modulo Azure PowerShell 4.0 o versioni successive.This sample requires the Azure PowerShell module version 4.0 or later. Eseguire Get-Module -ListAvailable AzureRM per trovare la versione.Run Get-Module -ListAvailable AzureRM to find the version. Se è necessario eseguire l'installazione o l'aggiornamento, vedere come installare il modulo Azure PowerShell.If you need to install or upgrade, see Install Azure PowerShell module.

Eseguire Login-AzureRmAccount per creare una connessione con Azure.Run Login-AzureRmAccount to create a connection with Azure.

Script di esempioSample script

# Login-AzureRmAccount
# Set the resource group name and location for your server
$resourcegroupname = "myResourceGroup-$(Get-Random)"
$location = "southcentralus"
# Set elastic poool names
$poolname = "MySamplePool"
# Set an admin login and password for your database
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
# The logical server name has to be unique in the system
$servername = "server-$(Get-Random)"
# The sample database names
$firstdatabasename = "myFirstSampleDatabase"
$seconddatabasename = "mySecondSampleDatabase"
# The ip address range that you want to allow to access your server
$startip = "0.0.0.0"
$endip = "0.0.0.0"

# Create a new resource group
$resourcegroup = New-AzureRmResourceGroup -Name $resourcegroupname -Location $location

# Create a new server with a system wide unique server name
$server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -Location $location `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

# Create elastic database pool
$elasticpool = -AzureRmSqlElasticPool -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -ElasticPoolName $poolname `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 50

# Create a server firewall rule that allows access from the specified IP range
$serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $startip -EndIpAddress $endip

# Create two blank database in the pool
$firstdatabase = New-AzureRmSqlDatabase  -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -DatabaseName $firstdatabasename `
    -ElasticPoolName $poolname
$seconddatabase = New-AzureRmSqlDatabase  -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -DatabaseName $seconddatabasename `
    -ElasticPoolName $poolname

# Monitor the pool
$monitorparameters = @{
  ResourceId = "/subscriptions/$($(Get-AzureRMContext).Subscription.Id)/resourceGroups/$resourcegroupname/providers/Microsoft.Sql/servers/$servername/elasticPools/$poolname"
  TimeGrain = [TimeSpan]::Parse("00:05:00")
  MetricNames = "dtu_consumption_percent"
}
(Get-AzureRmMetric @monitorparameters -DetailedOutput).MetricValues

# Scale the pool
$elasticpool = Set-AzureRmSqlElasticPool -ResourceGroupName $resourcegroupname `
    -ServerName $servername `
    -ElasticPoolName $poolname `
    -Edition "Standard" `
    -Dtu 100 `
    -DatabaseDtuMin 20 `
    -DatabaseDtuMax 100

# Add an alert that fires when the pool utilization reaches 90%
Add-AzureRMMetricAlertRule -ResourceGroup $resourcegroupname `
    -Name "mySampleAlertRule" `
    -Location $location `
    -TargetResourceId "/subscriptions/$($(Get-AzureRMContext).Subscription.Id)/resourceGroups/$resourcegroupname/providers/Microsoft.Sql/servers/$servername/elasticPools/$poolname" `
    -MetricName "dtu_consumption_percent" `
    -Operator "GreaterThan" `
    -Threshold 90 `
    -WindowSize $([TimeSpan]::Parse("00:05:00")) `
    -TimeAggregationOperator "Average" `
    -Actions $(New-AzureRmAlertRuleEmail -SendToServiceOwners)

# Clean up deployment 
# Remove-AzureRmResourceGroup -ResourceGroupName $resourcegroupname

Pulire la distribuzioneClean up deployment

Dopo l'esecuzione dello script di esempio, è possibile usare il comando seguente per rimuovere il gruppo di risorse e tutte le risorse ad esso associate.After the script sample has been run, the following command can be used to remove the resource group and all resources associated with it.

Remove-AzureRmResourceGroup -ResourceGroupName $resourcegroupname

Spiegazione dello scriptScript explanation

Questo script usa i comandi seguenti.This script uses the following commands. Ogni comando della tabella include collegamenti alla documentazione specifica del comando.Each command in the table links to command specific documentation.

ComandoCommand NoteNotes
New-AzureRmResourceGroupNew-AzureRmResourceGroup Consente di creare un gruppo di risorse in cui sono archiviate tutte le risorse.Creates a resource group in which all resources are stored.
New-AzureRmSqlServerNew-AzureRmSqlServer Crea un server logico che ospita un database o un pool elastico.Creates a logical server that hosts a database or elastic pool.
New-AzureRmSqlElasticPoolNew-AzureRmSqlElasticPool Crea un pool elastico all'interno di un server logico.Creates an elastic pool within a logical server.
New-AzureRmSqlDatabaseNew-AzureRmSqlDatabase Crea un database in un server logico come database singolo o in pool.Creates a database in a logical server as a single or a pooled database.
Get-AzureRmMetricGet-AzureRmMetric Mostra le informazioni sull'utilizzo delle dimensioni per il database.Shows the size usage information for the database.
Add-AzureRMMetricAlertRuleAdd-AzureRMMetricAlertRule Aggiunge o aggiorna una regola di avviso basata su metriche.Adds or updates a metric-based alert rule.
Set-AzureRmSqlElasticPoolSet-AzureRmSqlElasticPool Aggiorna le proprietà dei pool elastici.Updates elastic pool properties
Add-AzureRMMetricAlertRuleAdd-AzureRMMetricAlertRule Imposta una regola di avviso per monitorare automaticamente le DTU in futuro.Sets an alert rule to automatically monitor DTUs in the future.
Remove-AzureRmResourceGroupRemove-AzureRmResourceGroup Consente di eliminare un gruppo di risorse incluse tutte le risorse annidate.Deletes a resource group including all nested resources.

Passaggi successiviNext steps

Per altre informazioni su Azure PowerShell, vedere la documentazione di Azure PowerShell.For more information on the Azure PowerShell, see Azure PowerShell documentation.

Per altri esempi, vedere tra gli script di PowerShell per database SQL di Azure.Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.