使用 PowerShell 监视和缩放单个 SQL 数据库Use PowerShell to monitor and scale a single SQL database

此 PowerShell 脚本示例监视数据库的性能指标,将数据库扩展到更高的计算大小,并根据性能指标之一创建警报规则。This PowerShell script example monitors the performance metrics of a database, scales it to a higher compute size, and creates an alert rule on one of the performance metrics.

如果没有 Azure 订阅,可在开始前创建一个试用帐户If you don't have an Azure subscription, create a trial account before you begin.

Note

本文进行了更新,以便使用新的 Azure PowerShell Az 模块。This article has been updated to use the new Azure PowerShell Az module. 你仍然可以使用 AzureRM 模块,至少在 2020 年 12 月之前,它将继续接收 bug 修补程序。You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020. 若要详细了解新的 Az 模块和 AzureRM 兼容性,请参阅新 Azure Powershell Az 模块简介To learn more about the new Az module and AzureRM compatibility, see Introducing the new Azure PowerShell Az module. 有关 Az 模块安装说明,请参阅安装 Azure PowerShellFor Az module installation instructions, see Install Azure PowerShell.

本教程需要 AZ PowerShell 1.4.0 或更高版本。This tutorial requires AZ PowerShell 1.4.0 or later. 如果需要进行升级,请参阅 Install Azure PowerShell module(安装 Azure PowerShell 模块)。If you need to upgrade, see Install Azure PowerShell module. 此外,还需要运行 Connect-AzAccount -EnvironmentName AzureChinaCloud 以创建与 Azure 的连接。You also need to run Connect-AzAccount -EnvironmentName AzureChinaCloud to create a connection with Azure.

示例脚本Sample script

# Connect-AzAccount -Environment AzureChinaCloud
# The SubscriptionId in which to create these objects
$SubscriptionId = ''
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "chinaeast"
# Set an admin login and password for your server
$adminSqlLogin = "SqlAdmin"
$password = "ChangeYourAdminPassword1"
# 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
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

# Set subscription 
Set-AzContext -SubscriptionId $subscriptionId 

# Create a resource group
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

# Create a 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 imported 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"
}
(Get-AzMetric @MonitorParameters -DetailedOutput).MetricValues

# Scale the database performance to Standard S1
$database = Set-AzSqlDatabase -ResourceGroupName $resourceGroupName `
    -ServerName $servername `
    -DatabaseName $databasename `
    -Edition "Standard" `
    -RequestedServiceObjectiveName "S1"

# Set an alert rule to automatically monitor DTU in the future
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)

Note

有关指标的完整列表,请参阅支持的指标For a full list of metrics, see metrics supported.

Tip

使用 Get-AzSqlDatabaseActivity 获取数据库操作的状态,并使用 Stop-AzSqlDatabaseActivity 取消数据库更新操作。Use Get-AzSqlDatabaseActivity to get the status of database operations and use Stop-AzSqlDatabaseActivity to cancel a database update operation.

清理部署Clean up deployment

使用以下命令删除资源组及其相关的所有资源。Use the following command to remove the resource group and all resources associated with it.

Remove-AzResourceGroup -ResourceGroupName $resourcegroupname

脚本说明Script explanation

此脚本使用以下命令。This script uses the following commands. 表中的每条命令均链接到特定于命令的文档。Each command in the table links to command specific documentation.

命令Command 注释Notes
New-AzResourceGroupNew-AzResourceGroup 创建用于存储所有资源的资源组。Creates a resource group in which all resources are stored.
New-AzSqlServerNew-AzSqlServer 创建托管单一数据库或弹性池的 SQL 数据库服务器。Creates a SQL Database server that hosts a single database or elastic pool.
Get-AzMetricGet-AzMetric 显示数据库的大小使用情况信息。Shows the size usage information for the database.
Set-AzSqlDatabaseSet-AzSqlDatabase 更新数据库属性,或者将数据库移入、移出弹性池或在弹性池之间移动。Updates database properties or moves a database into, out of, or between elastic pools.
Add-AzMetricAlertRuleAdd-AzMetricAlertRule 设置警报规则,以便在将来自动监视 DTU。Sets an alert rule to automatically monitor DTUs in the future.
Remove-AzResourceGroupRemove-AzResourceGroup 删除资源组,包括所有嵌套的资源。Deletes a resource group including all nested resources.

后续步骤Next steps

有关 Azure PowerShell 的详细信息,请参阅 Azure PowerShell 文档For more information on the Azure PowerShell, see Azure PowerShell documentation.

可以在 Azure SQL 数据库 PowerShell 脚本中找到更多 SQL 数据库 PowerShell 脚本示例。Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.