使用 PowerShell 在 Azure SQL Database 中監視和調整彈性集區Use PowerShell to monitor and scale an elastic pool in Azure SQL Database

此 PowerShell 指令碼範例會監視彈性集區的效能計量、將其調整為較高計算大小,並對其中一個效能計量建立警示規則。This PowerShell script example monitors the performance metrics of an elastic pool, 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 free account before you begin.

注意

本文已更新為使用新的 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.

開啟 Azure Cloud ShellOpen Azure Cloud Shell

Azure Cloud Shell 是裝載於 Azure 中的互動式殼層環境,並且會透過瀏覽器來使用。Azure Cloud Shell is an interactive shell environment hosted in Azure and used through your browse. Azure Cloud Shell 可讓您使用 bashPowerShell 殼層來執行各種可與 Azure 服務搭配運作的工具。Azure Cloud Shell allows you to use either bash or PowerShell shells to run a variety of tools to work with Azure services. Azure Cloud Shell 已預先安裝一些命令,可讓您執行本文的內容,而不必在本機環境上安裝任何工具。Azure Cloud Shell comes pre-installed with the commands to allow you to run the content of this article without having to install anything on your local environment.

若要在 Azure Cloud Shell 上執行本文所包含的任何程式碼,請開啟 Cloud Shell 工作階段、使用某個程式碼區塊上的 [複製] 按鈕來複製程式碼,然後使用 Ctrl+Shift+V (在 Windows 和 Linux 上) 或 Cmd+Shift+V (在 macOS 上) 將程式碼貼到 Cloud Shell 工作階段中。To run any code contained in this article on Azure Cloud Shell, open a Cloud Shell session, use the Copy button on a code block to copy the code, and paste it into the Cloud Shell session with Ctrl+Shift+V on Windows and Linux, or Cmd+Shift+V on macOS. 貼上的文字不會自動執行,因此請按 Enter 來執行程式碼。Pasted text is not automatically executed, so press Enter to run code.

您可以使用下列方式來啟動 Azure Cloud Shell:You can launch Azure Cloud Shell with:

選取程式碼區塊右上角的 [試試看] 。Select Try It in the upper-right corner of a code block. 這__不會__自動將文字複製到 Cloud Shell。This doesn't automatically copy text to Cloud Shell. Azure Cloud Shell 的試試看範例
在瀏覽器中開啟 shell.azure.comOpen shell.azure.com in your browser. 啟動 Azure Cloud Shell 按鈕Launch Azure Cloud Shell button
選取 Azure 入口網站右上角功能表上的 [Cloud Shell] 按鈕。Select the Cloud Shell button on the menu in the upper-right corner of the Azure portal. Azure 入口網站中的 [Cloud Shell] 按鈕

如果選擇在本機安裝和使用 PowerShell,此教學課程需要 AZ PowerShell 1.4.0 或更新版本。If you choose to install and use the PowerShell locally, this tutorial requires AZ PowerShell 1.4.0 or later. 如果您需要升級,請參閱安裝 Azure PowerShell 模組If you need to upgrade, see Install Azure PowerShell module. 如果您在本機執行 PowerShell,則也需要執行 Connect-AzAccount 以建立與 Azure 的連線。If you are running PowerShell locally, you also need to run Connect-AzAccount to create a connection with Azure.

範例指令碼Sample script

# Connect-AzAccount
$SubscriptionId = ''
# Set the resource group name and location for your server
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "westus2"
# Set elastic pool names
$poolName = "MySamplePool"
# Set an admin login and password for your database
$adminSqlLogin = "SqlAdmin"
$password = "ChangeYourAdminPassword1"
# 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
$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 elastic database pool
$elasticPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -ElasticPoolName $poolName `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 50

# 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 two blank database in the pool
$firstDatabase = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $firstDatabaseName `
    -ElasticPoolName $poolName
$secondDatabase = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $secondDatabaseName `
    -ElasticPoolName $poolName

# Monitor the pool
$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"
}
(Get-AzMetric @monitorparameters -DetailedOutput).MetricValues

# Scale the pool
$elasticPool = Set-AzSqlElasticPool -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -ElasticPoolName $poolName `
    -Edition "Standard" `
    -Dtu 100 `
    -DatabaseDtuMin 20 `
    -DatabaseDtuMax 100

# Add an alert that fires when the pool utilization reaches 90%
Add-AzMetricAlertRule -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)

# Clean up deployment 
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

清除部署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 Database 伺服器。Creates a SQL Database server that hosts a single database or elastic pool.
New-AzSqlElasticPoolNew-AzSqlElasticPool 建立彈性集區。Creates an elastic pool.
New-AzSqlDatabaseNew-AzSqlDatabase 建立單一資料庫或彈性集區中的資料庫。Creates a single database or database in an elastic pool.
Get-AzMetricGet-AzMetric 顯示資料庫的大小使用量資訊。Shows the size usage information for the database.
Add-AzMetricAlertRuleAdd-AzMetricAlertRule 新增或更新以度量為基礎的警示規則。Adds or updates a metric-based alert rule.
Set-AzSqlElasticPoolSet-AzSqlElasticPool 更新彈性集區屬性Updates elastic pool properties
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.

其他的 SQL Database PowerShell 指令碼範例可於 Azure SQL Database PowerShell 指令碼中找到。Additional SQL Database PowerShell script samples can be found in the Azure SQL Database PowerShell scripts.