How to copy connection security firewall rules,SSL,TLS from one azure DB for mysql to another azure DB for my sql server using powershell

Question

I have restored Azure Database for MySQL single server using powershell script below.
Now, post restore the DB I had to copy all the firewall rules and other settings from connection security of Azure Database for MySQL single server manually.

After restore the DB I would like to automate copying the connection security configuration from source (Azure Database for MySQL single server) to the restored (Azure Database for MySQL single server) using powershell script. I couldn't able to figure it out how to automate this.

####################### Restore DB Server ####################### Write-Host "Restoring Azure DB for my SQL Server" $restorePointInTime = (Get-Date).AddMinutes(-5) $DBServerbackupStatus=Get-AzMySqlServer -Name $SourceDBServerName -ResourceGroupName $ResourceGroupName | Restore-AzMySqlServer -Name $TargetDBServerName -ResourceGroupName $ResourceGroupName -RestorePointInTime $restorePointInTime -UsePointInTimeRestore start-sleep -s 60 Write-Host -NoNewline "DBServer Restore process is completed,please find the current status below" $DBServerbackupStatus

At least please let me know how to pass json data via for loop in powershell . — PRAVEEN PDBR, May 06 '22 at 02:34

score 0 · Answer 1 · answered May 06 '22 at 09:35

0

You can create Azure Resource Manager (ARM) template in JSON or Bicep format for to create the firewall rules and this template can be used with any Azure SQL Database.

To create a Microsoft.Sql/servers/firewallRules resource, add the following Bicep or JSON to your template.

{
  "type": "Microsoft.Sql/servers/firewallRules",
  "apiVersion": "2021-11-01-preview",
  "name": "string",
  "properties": {
    "endIpAddress": "string",
    "startIpAddress": "string"
  }
}

There are some already predefined quick start templates which you can check here.

answered May 06 '22 at 09:35

Utkarsh Pal

4,079
1
5
14

thanks for your response but if I want to get the firewall rules from existing DB and want to create the new firewall rules for different DB using the existing DB rules via powershell. I want to automate this. Could you please help here? – PRAVEEN PDBR May 06 '22 at 17:41
The most you can do it store the ARM template of your existing database (but this will include everything) and use it for new DB. – Utkarsh Pal May 07 '22 at 07:52
MT, Thanks for your response. Actually I am restoring my Azure DB for my SQL server via pwoershell script and the source DB should have some connection security parameters and the I want to copy into new Azure DB for my SQL server. For this to automate ,how to do via ARM template ? I am not clear with the ARM template which you are explaining . It will be great and appreciated if you could please provide the details. – PRAVEEN PDBR May 07 '22 at 07:59

score 0 · Accepted Answer · answered May 12 '22 at 00:10

Finally I could able to fix this and could able to write my solution and it’s worked.

##################### Updating Firewall rules from Soiurce DB server to Target DB server ##################

Write-Host -NoNewline "Updating Firewall rules from Soiurce DB server to Target DB server"
Get-AzMySqlFirewallRule -ResourceGroupName $ResourceGroupName -ServerName $SourceDBServerName | Select-Object Name, StartIPaddress, EndIPaddress | Convertto-Json | Out-File "file.json"
foreach ($entry in (Get-Content file.json -raw | ConvertFrom-Json)) {
  New-AzMySqlFirewallRule -Name $entry.Name -ResourceGroupName $ResourceGroupName -ServerName $TargetDBServerName -EndIPAddress $entry.EndIPAddress -StartIPAddress $entry.StartIPAddress
}

How to copy connection security firewall rules,SSL,TLS from one azure DB for mysql to another azure DB for my sql server using powershell

2 Answers2