使用 PowerShell 來監視和調整 Azure SQL Database 中的單一資料庫

此 PowerShell 指令碼範例會監視單一資料庫的效能計量、將其調整為較高的計算大小，並在其中一個效能計量上建立警示規則。

注意

本文使用 Azure Az PowerShell 模組，這是與 Azure 互動時建議使用的 PowerShell 模組。若要開始使用 Az PowerShell 模組，請參閱安裝 Azure PowerShell。若要瞭解如何移轉至 Az PowerShell 模組，請參閱將 Azure PowerShell 從 AzureRM 移轉至 Az。

使用 Azure Cloud Shell

Azure 提供的 Azure Cloud Shell 是一個互動式的命令行環境，您可以透過瀏覽器使用。您可以使用 Bash 或 PowerShell 搭配 Cloud Shell，與 Azure 服務共同使用。您可以使用 Cloud Shell 預先安裝的命令，執行本文提到的程式碼，而不必在本機環境上安裝任何工具。

要啟動 Azure Cloud Shell：

選項	範例/連結
選擇程式碼區塊右上角的試試。選取 [試用] 並不會自動將程式碼複製到 Cloud Shell 中。
請前往 https://shell.azure.com，或選取 [啟動 Cloud Shell] 按鈕，在瀏覽器中開啟 Cloud Shell。
選取 Azure 入口網站右上方功能表列上的 [Cloud Shell] 按鈕。

若要在 Azure Cloud Shell 中執行本文中的程式碼：

啟動 Cloud Shell。
選取程式碼區塊上的 [複製] 按鈕，複製程式碼。
透過在 Windows 和 Linux 上選取 Ctrl+Shift+V；或在 macOS 上選取 Cmd+Shift+V，將程式碼貼到 Cloud Shell 工作階段中。
選取 Enter 鍵執行程式碼。

如果選擇在本機安裝並使用 PowerShell，此教學課程需要 Az PowerShell 1.4.0 或更新版本。如果您需要升級，請參閱安裝 Azure PowerShell 模組。如果您在本機執行 PowerShell，則也需要執行 Connect-AzAccount 以建立與 Azure 的連線。

範例指令碼

# This script requires the following
# - Az.Resources
# - Az.Accounts
# - Az.Monitor
# - Az.Sql

# First, run Connect-AzAccount

# Set the subscription in which to create these objects. This is displayed on objects in the Azure portal.
$subscriptionId = "<Subscription-ID>"
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "westus2"
# Set an admin login and password for your server
$adminSqlLogin = "<admin>"
$password = "<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 credential for the server admin
$adminCredential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force)

# Create a new server with a system-wide unique server name
$serverParams = @{
    ResourceGroupName           = $resourceGroupName
    ServerName                  = $serverName
    Location                    = $location
    SqlAdministratorCredentials = $adminCredential
}
$server = New-AzSqlServer @serverParams

# Create a server firewall rule that allows access from the specified IP range
$firewallParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    FirewallRuleName  = "AllowedIPs"
    StartIpAddress    = $startIp
    EndIpAddress      = $endIp
}
$serverFirewallRule = New-AzSqlServerFirewallRule @firewallParams

# Create a blank database with an S0 performance level
$databaseParams = @{
    ResourceGroupName             = $resourceGroupName
    ServerName                    = $serverName
    DatabaseName                  = $databaseName
    RequestedServiceObjectiveName = "S0"
    SampleName                    = "AdventureWorksLT"
}
$database = New-AzSqlDatabase @databaseParams

# 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
$scaleParams = @{
    ResourceGroupName             = $resourceGroupName
    ServerName                    = $serverName
    DatabaseName                  = $databaseName
    Edition                       = "Standard"
    RequestedServiceObjectiveName = "S1"
}
$database = Set-AzSqlDatabase @scaleParams

# 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 a 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.
$actionGroupParams = @{
    Name              = "mysample-email-the-azure-admins"
    ShortName         = "AzAdminsGrp"
    ResourceGroupName = $resourceGroupName
    Receiver          = $receiver
}
$actionGroup = Set-AzActionGroup @actionGroupParams

# 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.
$criteriaParams = @{
    MetricName      = "dtu_consumption_percent"
    TimeAggregation = "Average"
    Operator        = "GreaterThan"
    Threshold       = 90
}
$criteria = New-AzMetricAlertRuleV2Criteria @criteriaParams

# Create the Alert rule.
# Add-AzMetricAlertRuleV2 adds or updates a V2 (non-classic) metric-based alert rule.
$alertRuleParams = @{
    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
}
Add-AzMetricAlertRuleV2 @alertRuleParams

<#
# Set up an alert rule using Azure Monitor for the database
# Note that Add-AzMetricAlertRule is deprecated. Use Add-AzMetricAlertRuleV2 instead.
$deprecatedAlertParams = @{
    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)
}
Add-AzMetricAlertRule @deprecatedAlertParams
#>

# 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	刪除資源群組，包括所有的巢狀資源。

意見反應

此頁面對您有幫助嗎？

Last updated on 2025-06-11