使用 PowerShell 將資料庫新增至容錯移轉群組

發行項
03/13/2024

此 PowerShell 指令碼範例會在 Azure SQL 資料庫中建立資料庫、建立容錯移轉群組、在其中新增資料庫，以及測試容錯移轉。

注意

本文使用 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 的連線。

範例指令碼

# Add a single Azure SQL Database to a failover group

# Set variables for your server and database
$subscriptionId = '<SubscriptionID>'
$randomIdentifier = $(Get-Random)
$resourceGroupName = "myResourceGroup-$randomIdentifier"
$location = "West US 2"
$adminLogin = "azureuser"
$password = "PWD27!"+(New-Guid).Guid
$serverName = "mysqlserver-$randomIdentifier"
$databaseName = "mySampleDatabase"
$drLocation = "East US 2"
$drServerName = "mysqlsecondary-$randomIdentifier"
$failoverGroupName = "failovergrouptutorial-$randomIdentifier"


# The ip address range that you want to allow to access your server 
# Leaving at 0.0.0.0 will prevent outside-of-azure connections
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

# Show randomized variables
Write-host "Resource group name is" $resourceGroupName 
Write-host "Password is" $password  
Write-host "Server name is" $serverName 
Write-host "DR Server name is" $drServerName 
Write-host "Failover group name is" $failoverGroupName



# Set subscription ID
Set-AzContext -SubscriptionId $subscriptionId

# Create a resource group
Write-host "Creating resource group..."
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}
$resourceGroup



# Create a server with a system wide unique server name
Write-host "Creating primary logical server..."
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -Location $location `
   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
   -ArgumentList $adminLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$server

# Create a server firewall rule that allows access from the specified IP range
Write-host "Configuring firewall for primary logical server..."
$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
$serverFirewallRule

# Create General Purpose Gen5 database with 2 vCore
Write-host "Creating a gen5 2 vCore database..."
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -DatabaseName $databaseName `
   -Edition GeneralPurpose `
   -VCore 2 `
   -ComputeGeneration Gen5 `
   -MinimumCapacity 2 `
   -SampleName "AdventureWorksLT"
$database



# Create a secondary server in the failover region
Write-host "Creating a secondary logical server in the failover region..."
$drServer = New-AzSqlServer -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName `
   -Location $drLocation `
   -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
      -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$drServer



# Create a failover group between the servers
$failovergroup = Write-host "Creating a failover group between the primary and secondary server..."
New-AzSqlDatabaseFailoverGroup `
   –ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -PartnerServerName $drServerName  `
   –FailoverGroupName $failoverGroupName `
   –FailoverPolicy Manual
$failovergroup


# Add the database to the failover group
Write-host "Adding the database to the failover group..." 
Get-AzSqlDatabase `
   -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -DatabaseName $databaseName | `
Add-AzSqlDatabaseToFailoverGroup `
   -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -FailoverGroupName $failoverGroupName
Write-host "Successfully added the database to the failover group..." 



# Check role of secondary replica
Write-host "Confirming the secondary replica is secondary...." 
(Get-AzSqlDatabaseFailoverGroup `
   -FailoverGroupName $failoverGroupName `
   -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName).ReplicationRole




# Failover to secondary server
Write-host "Failing over failover group to the secondary..." 
Switch-AzSqlDatabaseFailoverGroup `
   -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName `
   -FailoverGroupName $failoverGroupName
Write-host "Failed failover group successfully to" $drServerName 

Write-host "Confirming the secondary server is now primary...." 
(Get-AzSqlDatabaseFailoverGroup `
   -FailoverGroupName $failoverGroupName `
   -ResourceGroupName $resourceGroupName `
   -ServerName $drServerName).ReplicationRole



# Revert failover to primary server
Write-host "Failing over failover group to the primary...." 
Switch-AzSqlDatabaseFailoverGroup `
   -ResourceGroupName $resourceGroupName `
   -ServerName $serverName `
   -FailoverGroupName $failoverGroupName
Write-host "Failed failover group successfully back to" $serverName


# Show randomized variables
Write-host "Resource group name is" $resourceGroupName 
Write-host "Password is" $password  
Write-host "Server name is" $serverName 
Write-host "DR Server name is" $drServerName 
Write-host "Failover group name is" $failoverGroupName

# Clean up resources by removing the resource group
# Write-host "Removing resource group..."
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName
# Write-host "Resource group removed =" $resourceGroupName

清除部署

使用下列命令來移除資源群組及其所有相關聯的資源。

Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

指令碼說明

此指令碼會使用下列命令。下表中的每個命令都會連結至命令特定的文件。

Command	注意
New-AzResourceGroup	建立用來存放所有資源的資源群組。
New-AzSqlServer	建立伺服器。
New-AzSqlServerFirewallRule	為伺服器建立伺服器層級的防火牆規則。
New-AzSqlDatabase	建立新資料庫。
New-AzSqlDatabaseFailoverGroup	建立新的容錯移轉群組。
Get-AzSqlDatabase	取得一或多個資料庫。
Add-AzSqlDatabaseToFailoverGroup	將一或多個資料庫新增至容錯移轉群組。
Get-AzSqlDatabaseFailoverGroup	取得或列出容錯移轉群組。
Switch-AzSqlDatabaseFailoverGroup	執行容錯移轉群組的容錯移轉。
Remove-AzResourceGroup	移除資源群組

下一步

如需有關 Azure PowerShell 的詳細資訊，請參閱 Azure PowerShell 文件。

其他的 SQL Database PowerShell 指令碼範例可於 Azure SQL Database PowerShell 指令碼中找到。