使用 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 脚本示例。