使用 PowerShell 在 Azure SQL 資料庫中監視和調整彈性集區

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

注意

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

使用 Azure Cloud Shell

Azure Cloud Shell 是裝載於 Azure 中的互動式殼層環境，可在瀏覽器中使用。您可以使用 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 elastic pool name
$poolName = "MySamplePool"
# Set an admin login and password for your database
$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 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

$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 elastic database pool
$elasticPoolParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    ElasticPoolName   = $poolName
    Edition           = "Standard"
    Dtu               = 50
    DatabaseDtuMin    = 10
    DatabaseDtuMax    = 50
}
$elasticPool = New-AzSqlElasticPool @elasticPoolParams

# 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 two blank databases in the pool
$firstDatabaseParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    DatabaseName      = $firstDatabaseName
    ElasticPoolName   = $poolName
}
$firstDatabase = New-AzSqlDatabase @firstDatabaseParams
$secondDatabaseParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    DatabaseName      = $secondDatabaseName
    ElasticPoolName   = $poolName
}
$secondDatabase = New-AzSqlDatabase @secondDatabaseParams

# 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
$scaleParams = @{
    ResourceGroupName = $resourceGroupName
    ServerName        = $serverName
    ElasticPoolName   = $poolName
    Edition           = "Standard"
    Dtu               = 100
    DatabaseDtuMin    = 20
    DatabaseDtuMax    = 100
}
$elasticPool = Set-AzSqlElasticPool @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, 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/elasticPools/$poolName"
    Condition         = $criteria
    ActionGroup       = $actionGroupObject
    Severity          = 3 #Informational
}
Add-AzMetricAlertRuleV2 @alertRuleParams

<#
# 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.
$deprecatedAlertParams = @{
    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)
}
Add-AzMetricAlertRule @deprecatedAlertParams
#>

# 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	(已取代) 新增或更新警示規則，以在未來自動監視計量。僅適用於傳統計量型警示規則。
Add-AzMetricAlertRuleV2	新增或更新警示規則，以在未來自動監視計量。僅適用於非傳統計量型警示規則。
Remove-AzResourceGroup	刪除資源群組，包括所有的巢狀資源。

意見反應

此頁面對您有幫助嗎？

上次更新時間： 2025-06-11