使用 PowerShell 在 Azure SQL 数据库中监视和缩放弹性池
适用于:Azure SQL 数据库
此 PowerShell 脚本示例监视弹性池的性能指标,将其扩展到更高的计算大小,并基于性能指标之一创建警报规则。
如果没有 Azure 试用版订阅,请在开始前创建一个试用版订阅。
注意
本文使用 Azure Az PowerShell 模块,这是与 Azure 交互时推荐使用的 PowerShell 模块。 若要开始使用 Az PowerShell 模块,请参阅安装 Azure PowerShell。 若要了解如何迁移到 Az PowerShell 模块,请参阅 将 Azure PowerShell 从 AzureRM 迁移到 Az。
本教程需要 Az PowerShell 1.4.0 或更高版本。 如果需要进行升级,请参阅 Install Azure PowerShell module(安装 Azure PowerShell 模块)。 此外,还需要运行 Connect-AzAccount -EnvironmentName AzureChinaCloud
以创建与 Azure 的连接。
示例脚本
# This script requires the following
# - Az.Resources
# - Az.Accounts
# - Az.Monitor
# - Az.Sql
# First, run Connect-AzAccount -Environment AzureChinaCloud
# Set the subscription in which to create these objects. This is displayed on objects in the Azure portal.
$SubscriptionId = ''
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "chinaeast2"
# Set elastic pool name
$poolName = "MySamplePool"
# Set an admin login and password for your database
$adminSqlLogin = "SqlAdmin"
$password = (New-Guid).Guid # Generates a randomized GUID password.
# Set server name - 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 via the firewall rule
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"
# Set subscription
Set-AzContext -SubscriptionId $subscriptionId
# Create a new resource group
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location
# Create a new server with a system wide unique server name
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-Location $location `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
# Create elastic database pool
$elasticPool = New-AzSqlElasticPool -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-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
# Create two blank database in the pool
$firstDatabase = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $firstDatabaseName `
-ElasticPoolName $poolName
$secondDatabase = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $secondDatabaseName `
-ElasticPoolName $poolName
# Monitor the DTU consumption of the pool in 5 minute intervals
$monitorparameters = @{
ResourceId = "/subscriptions/$($(Get-AzContext).Subscription.Id)/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/elasticPools/$poolName"
TimeGrain = [TimeSpan]::Parse("00:05:00")
MetricNames = "dtu_consumption_percent"
}
$metric = Get-AzMetric @monitorparameters
$metric.Data
# Scale the pool
$elasticPool = Set-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-ElasticPoolName $poolName `
-Edition "Standard" `
-Dtu 100 `
-DatabaseDtuMin 20 `
-DatabaseDtuMax 100
# Set up an Alert rule using Azure Monitor for the database
# Add an Alert that fires when the pool utilization reaches 90%
# Objects needed: an Action Group Receiver, an Action Group, Alert Criteria, and finally an Alert Rule.
# Creates an new action group receiver object with a target email address.
$receiver = New-AzActionGroupReceiver `
-Name "my Sample Azure Admins" `
-EmailAddress "azure-admins-group@contoso.com"
# Creates a new or updates an existing action group.
$actionGroup = Set-AzActionGroup `
-Name "mysample-email-the-azure-admins" `
-ShortName "AzAdminsGrp" `
-ResourceGroupName $resourceGroupName `
-Receiver $receiver
# Fetch the created AzActionGroup into an object of type Microsoft.Azure.Management.Monitor.Models.ActivityLogAlertActionGroup
$actionGroupObject = New-AzActionGroup -ActionGroupId $actionGroup.Id
# Create a criteria for the Alert to monitor.
$criteria = New-AzMetricAlertRuleV2Criteria `
-MetricName "dtu_consumption_percent" `
-TimeAggregation Average `
-Operator GreaterThan `
-Threshold 90
# Create the Alert rule.
# Add-AzMetricAlertRuleV2 adds or updates a V2 (non-classic) metric-based alert rule.
Add-AzMetricAlertRuleV2 -Name "mySample_Alert_DTU_consumption_pct" `
-ResourceGroupName $resourceGroupName `
-WindowSize (New-TimeSpan -Minutes 1) `
-Frequency (New-TimeSpan -Minutes 1) `
-TargetResourceId "/subscriptions/$($(Get-AzContext).Subscription.Id)/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/elasticPools/$poolName" `
-Condition $criteria `
-ActionGroup $actionGroupObject `
-Severity 3 #Informational
<#
# Set up an alert rule using Azure Monitor for the database
# Add a classic alert that fires when the pool utilization reaches 90%
# Note that Add-AzMetricAlertRule is deprecated. Use Add-AzMetricAlertRuleV2 instead.
Add-AzMetricAlertRule -ResourceGroup $resourceGroupName `
-Name "mySampleAlertRule" `
-Location $location `
-TargetResourceId "/subscriptions/$($(Get-AzContext).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" `
-Action $(New-AzAlertRuleEmail -SendToServiceOwner)
#>
# Clean up deployment
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
清理部署
使用以下命令删除资源组及其相关的所有资源。
Remove-AzResourceGroup -ResourceGroupName $resourcegroupname
脚本说明
此脚本使用以下命令。 表中的每条命令链接到特定于命令的文档。
命令 | 注释 |
---|---|
New-AzResourceGroup | 创建用于存储所有资源的资源组。 |
New-AzSqlServer | 创建托管数据库或弹性池的服务器。 |
New-AzSqlElasticPool | 创建弹性池。 |
New-AzSqlDatabase | 在服务器中创建数据库。 |
Get-AzMetric | 显示数据库的大小使用情况信息。 |
Set-AzSqlElasticPool | 更新弹性池属性。 |
Add-AzMetricAlertRule | (DeprecateD) 添加或更新预警规则,以便在将来自动监视指标。 仅适用于经典的基于指标的警报规则。 |
Add-AzMetricAlertRuleV2 | 添加或更新警报规则,以便在将来自动监视指标。 仅适用于非经典的基于指标的警报规则。 |
Remove-AzResourceGroup | 删除资源组,包括所有嵌套的资源。 |
后续步骤
有关 Azure PowerShell 的详细信息,请参阅 Azure PowerShell 文档。
可以在 Azure PowerShell 脚本中找到其他 PowerShell 脚本示例。