Copying firewall rules between two Azure SQL servers

Recently I've setup active geo-replication for my database hosted on Azure which basically allows you to asynchronously replicate transactions held on a primary database to a secondary database (usually located in a different geographical region) which is set to read only.

The problem was, in a failover/disaster recovery situation, for the secondary to be usable, I would have to manually add all the existing firewall rules on the primary to the secondary which may be updated from time to time so why not automate it? I've got this running as an timer based Azure Function to run daily.


copy-firewall-rules.ps1 -sourceResourceGroup 'my-rg' -sourceSqlServerName 'my-primary-server' -targetResourceGroup 'some-other-rg' -targetSqlServer 'my-secondary-server'



function Copy-AzureSQLServerFirewallRules([string]$sourceResourceGroup, [string]$sourceSqlServerName, [string]$targetResourceGroup, [string]$targetSqlServer)
    ## First remove existing rules from target server
    $existingfirewallRules = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $targetResourceGroup -ServerName $targetServerName 
    foreach($rule in $existingfirewallRules)
        Write-Host Removing rule : $rule.FireWallRuleName from $targetServerName
        Remove-AzureRmSqlServerFirewallRule -FirewallRuleName $rule.FirewallRuleName -ResourceGroupName $targetResourceGroup -ServerName $targetServerName

    ## Add firewall rules to target based on source
    $sourceFirewallRules = Get-AzureRmSqlServerFirewallRule -ResourceGroupName $sourceResourceGroup -ServerName $sourceSqlServerName
    foreach($r in $sourceFirewallRules)
        Write-Host Adding Rule : $r.FirewallRuleName to $targetServerName
        New-AzureRmSqlServerFirewallRule -ServerName $targetServerName -ResourceGroupName $targetResourceGroup -StartIpAddress $r.StartIpAddress -EndIpAddress $r.EndIpAddress -FirewallRuleName $r.FirewallRuleName

Copy-AzureSQLServerFirewallRules -sourceResourceGroup $sourceResourceGroup -sourceSqlServerName $sourceSqlServerName -targetResourceGroup $targetResourceGroup -targetSqlServer $targetSqlServer
comments powered by Disqus