使用 PowerShell 为入池 Azure SQL 数据库配置活动异地复制

以下 PowerShell 脚本示例为弹性池中的 Azure SQL 数据库配置活动异地复制,并将其故障转移到 Azure SQL 数据库的次要副本。

本示例需要 Azure PowerShell 模块 5.1.1 或更高版本。 运行 Get-Module -ListAvailable AzureRM 即可查找版本。 如果需要进行安装或升级,请参阅安装 Azure PowerShell 模块

运行 Login-AzureRmAccount -EnvironmentName AzureChinaCloud,创建与 Azure 的连接。

示例脚本

# Login-AzureRmAccount -EnvironmentName AzureChinaCloud
# Set the resource group name and location for your serverw
$primaryresourcegroupname = "myPrimaryResourceGroup-$(Get-Random)"
$secondaryresourcegroupname = "mySecondaryResourceGroup-$(Get-Random)"
$primarylocation = "China East"
$secondarylocation = "China North"
# The logical server names have to be unique in the system
$primaryservername = "primary-server-$(Get-Random)"
$secondaryservername = "secondary-server-$(Get-Random)"
# Set an admin login and password for your servers
$adminlogin = "ServerAdmin"
$password = "ChangeYourAdminPassword1"
# The sample database name
$databasename = "mySampleDatabase"
# The ip address ranges that you want to allow to access your servers
$primarystartip = "0.0.0.0"
$primaryendip = "0.0.0.0"
$secondarystartip = "0.0.0.0"
$secondaryendip = "0.0.0.0"
# The elastic pool names
$primarypoolname = "PrimaryPool"
$secondarypoolname = "SecondaryPool"

# Create two new resource groups
$primaryresourcegroupname = New-AzureRmResourceGroup -Name $primaryresourcegroupname -Location $primarylocation
$secondaryresourcegroupname = New-AzureRmResourceGroup -Name $secondaryresourcegroupname -Location $secondarylocation

# Create two new logical servers with a system wide unique server name

$primaryserver = New-AzureRmSqlServer -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -Location $primarylocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$secondaryserver = New-AzureRmSqlServer -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -Location $secondarylocation `
    -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

# Create a server firewall rule for each server that allows access from the specified IP range
$primaryserverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $primarystartip -EndIpAddress $primaryendip
$secondaryserverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -FirewallRuleName "AllowedIPs" -StartIpAddress $secondarystartip -EndIpAddress $secondaryendip

# Create a pool in each of the servers
$primarypool = New-AzureRmSqlElasticPool -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -ElasticPoolName $primarypoolname `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 50
$secondarypool = New-AzureRmSqlElasticPool -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -ElasticPoolName $secondarypoolname `
    -Edition "Standard" `
    -Dtu 50 `
    -DatabaseDtuMin 10 `
    -DatabaseDtuMax 50

# Create a blank database in the pool on the primary server
$database = New-AzureRmSqlDatabase  -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -DatabaseName $databasename `
    -ElasticPoolName $primarypoolname

# Establish Active Geo-Replication
$database = Get-AzureRmSqlDatabase -ResourceGroupName $primaryresourcegroupname `
    -ServerName $primaryservername `
    -DatabaseName $databasename
$database | New-AzureRmSqlDatabaseSecondary -PartnerResourceGroupName $secondaryresourcegroupname `
    -PartnerServerName $secondaryservername `
    -SecondaryElasticPoolName $primarypoolname `
    -AllowConnections "All"

# Initiate a planned failover
$database = Get-AzureRmSqlDatabase -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -DatabaseName $databasename 
$database | Set-AzureRmSqlDatabaseSecondary -PartnerResourceGroupName $primaryresourcegroupname -Failover


# Monitor Geo-Replication config and health after failover
$database = Get-AzureRmSqlDatabase -ResourceGroupName $secondaryresourcegroupname `
    -ServerName $secondaryservername `
    -DatabaseName $databasename
$database | Get-AzureRmSqlDatabaseReplicationLink -PartnerResourceGroupName $primaryresourcegroupname `
    -PartnerServerName $primaryservername

# Clean up deployment 
# Remove-AzureRmResourceGroup -ResourceGroupName $primaryresourcegroupname
# Remove-AzureRmResourceGroup -ResourceGroupName $secondaryresourcegroupname

清理部署

运行脚本示例后,可以使用以下命令删除资源组以及与其关联的所有资源。

Remove-AzureRmResourceGroup -ResourceGroupName $primaryresourcegroupname
Remove-AzureRmResourceGroup -ResourceGroupName $secondaryresourcegroupname

脚本说明

此脚本使用以下命令。 表中的每条命令均链接到特定于命令的文档。

命令 注释
New-AzureRmResourceGroup 创建用于存储所有资源的资源组。
New-AzureRmSqlServer 创建用于托管数据库或弹性池的逻辑服务器。
New-AzureRmSqlElasticPool 在逻辑服务器中创建弹性池。
New-AzureRmSqlDatabase 在逻辑服务器中创建数据库作为单一数据库或入池数据库。
Set-AzureRmSqlDatabase 更新数据库属性,或者将数据库移入、移出弹性池或在弹性池之间移动。
New-AzureRmSqlDatabaseSecondary 为现有数据库创建辅助数据库,并开始数据复制。
Get-AzureRmSqlDatabase 获取一个或多个数据库。
Set-AzureRmSqlDatabaseSecondary 将辅助数据库切换为主数据库,以便启动故障转移。
Get-AzureRmSqlDatabaseReplicationLink 获取 Azure SQL 数据库和资源组或 SQL Server 之间的异地复制链路。
Remove-AzureRmResourceGroup 删除资源组,包括所有嵌套的资源。

后续步骤

有关 Azure PowerShell 的详细信息,请参阅 Azure PowerShell 文档

可以在 Azure SQL 数据库 PowerShell 脚本中找到更多 SQL 数据库 PowerShell 脚本示例。