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
– PRAVEEN PDBR, Commented May 6, 2022 at 2:34

Utkarsh Pal · Accepted Answer · 2022-05-06 09:35:27Z

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 6, 2022 at 9:35

Utkarsh Pal

4,5781 gold badge8 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

PRAVEEN PDBR Over a year ago

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?

Utkarsh Pal Over a year ago

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.

PRAVEEN PDBR Over a year ago

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 · Accepted Answer · 2022-05-12 00:10:45Z

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
}

Collectives™ on Stack Overflow

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 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related