使用 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 an admin login and password for your server
$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 name
$databaseName = "mySampleDatabase"
# 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 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 a blank database with an S0 performance level
$database = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $databaseName `
-RequestedServiceObjectiveName "S0" `
-SampleName "AdventureWorksLT"
# Monitor the DTU consumption on the database in 5 minute intervals
$MonitorParameters = @{
ResourceId = "/subscriptions/$($(Get-AzContext).Subscription.Id)/resourceGroups/$resourceGroupName/providers/Microsoft.Sql/servers/$serverName/databases/$databaseName"
TimeGrain = [TimeSpan]::Parse("00:05:00")
MetricNames = "dtu_consumption_percent"
}
$metric = Get-AzMetric @monitorparameters
$metric.Data
# Scale the database performance to Standard S1
$database = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $servername `
-DatabaseName $databasename `
-Edition "Standard" `
-RequestedServiceObjectiveName "S1"
# 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 (in this case, an email group), 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/databases/$databaseName" `
-Condition $criteria `
-ActionGroup $actionGroupObject `
-Severity 3 #Informational
<#
# Set up an alert rule using Azure Monitor for the database
# 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/databases/$databaseName" `
-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
注意
有关指标的完整列表,请参阅支持的指标。
提示
使用 Get-AzSqlDatabaseActivity 获取数据库操作的状态,并使用 Stop-AzSqlDatabaseActivity 取消数据库更新操作。
清理部署
使用以下命令删除资源组及其相关的所有资源。
Remove-AzResourceGroup -ResourceGroupName $resourcegroupname
脚本说明
此脚本使用以下命令。 表中的每条命令链接到特定于命令的文档。
命令 | 注释 |
---|---|
New-AzResourceGroup | 创建用于存储所有资源的资源组。 |
New-AzSqlServer | 创建托管单一数据库或弹性池的服务器。 |
Get-AzMetric | 显示数据库的大小使用情况信息。 |
Set-AzSqlDatabase | 更新数据库属性,或者将数据库移入、移出弹性池或在弹性池之间移动。 |
Add-AzMetricAlertRule | (已弃用)添加或更新警报规则,以便在将来自动监视指标。 仅适用于经典的基于指标的警报规则。 |
Add-AzMetricAlertRuleV2 | 添加或更新警报规则,以便在将来自动监视指标。 仅适用于非经典的基于指标的警报规则。 |
Remove-AzResourceGroup | 删除资源组,包括所有嵌套的资源。 |
后续步骤
有关 Azure PowerShell 的详细信息,请参阅 Azure PowerShell 文档。
可以在 Azure PowerShell 脚本中找到其他 PowerShell 脚本示例。