Feature Request: Backup Policy type "SQL Server in Azure VM"

[joeyjesky]

Community Note

• Please vote on this issue by adding a 👍 reaction to the original issue to help the community and maintainers prioritize this request
• Please do not leave "+1" or "me too" comments, they generate extra noise for issue followers and do not help prioritize the request
• If you are interested in working on this issue or have submitted a pull request, please leave a comment

Description

The ability to create a backup policy for Policy Type = 'SQL Server in Azure VM'

New or Affected Resource(s)

Cannot create backup policy for SQL Server in Azure VM.

• azurerm_recovery_services_protection_policy_vm

Potential Terraform Configuration

       {
  "condition": "[parameters('isNewPolicy')]",
  "type": "Microsoft.RecoveryServices/vaults/backupPolicies",
  "apiVersion": "2018-01-10",
  "name": "[concat(parameters('vaultName'), '/', parameters('policyName'))]",
  "dependsOn": [
    "[resourceId('Microsoft.RecoveryServices/vaults/', parameters('vaultName'))]"
  ],
  "properties": {
    "backupManagementType": "[variables('backupManagementType')]",
    "workloadType": "[parameters('workloadType')]",
    "settings": {
      "timeZone": "UTC",
      "issqlcompression": false,
      "isCompression": false
    },
    "subProtectionPolicy": [
      {
        "policyType": "Full",
        "schedulePolicy": {
            "schedulePolicyType": "SimpleSchedulePolicy",
            "scheduleRunFrequency": "Daily",
            "scheduleRunTimes": [
                "2019-03-24T16:00:00Z"
            ],
            "scheduleWeeklyFrequency": 0
        },
        "retentionPolicy": {
            "retentionPolicyType": "LongTermRetentionPolicy",
            "dailySchedule": {
                "retentionTimes": [
                    "2019-03-24T16:00:00Z"
                ],
                "retentionDuration": {
                    "count": 30,
                    "durationType": "Days"
                }
            }
        }
      },
      {
          "policyType": "Log",
          "schedulePolicy": {
              "schedulePolicyType": "LogSchedulePolicy",
              "scheduleFrequencyInMins": 60
          },
          "retentionPolicy": {
              "retentionPolicyType": "SimpleRetentionPolicy",
              "retentionDuration": {
                  "count": 30,
                  "durationType": "Days"
              }
          }
      }
    ]
  }
},

References

https://github.com/Azure/azure-quickstart-templates/tree/master/101-recovery-services-vm-workload-backup

• #0000

[mpjtaylor]

also need this

[hriaz]

The issue / feature request applies to workload-type= SAPHANA

[benj-ch]

Extremely interested in this. Any updates on this request ?

[aronoeldb]

Interested in this, any update?

[jackwesleyroper]

Also need this feature!

[petri-o-ojala]

Azure Backup for Workloads definetly needs some love and support, multiple customer cases where we'd need native Terraform support for this feature (with HANA but should be implemented across the board).

[dileepalbert]

Extremely interested see any progress on this topic

[davidsampson-hv]

We also really need support for SQL in Azure VM policies

[Sam123ben]

Any update on this please??? We need this ASAP to complete out Vault Service autmation.

[flappje]

I was also looking how I could add SQL to Recovery Servies Vault and I found this PR: #17765
I think this could be the solution for this Issue. so let's hope they merge this soon

[pvrk]

This functionality is now released with provider version 3.20.0. Please see the details of the issue, mentioned above.

[ArjunMR89]

I am trying to backup SQL in Azure VM and used the policy azurerm_backup_policy_vm_workload with azurerm provider version 3.23.0. The policy is successfully deployed in Azure but I couldn't find any resource to backup SQL in Azure VM. I tried using azurerm_backup_protected_vm by passing virtualMachines ID and SqlVirtualMachines ID but both resulted in errors. Am I missing something? Is only backup policy available at the moment? Is the backup of SQL in Azure VM still not supported using Terraform?

[pvrk]

@ArjunMR89 : Yes, currently only backup policy is available on Terraform. We are working on integration SQL backup too. So, you have the Azure VM registered as SQL VM?

[ArjunMR89]

@pvrk Thank you for the reply. Yes, I have Azure VM registered as SQL VM. Could you please suggest any workaround till the SQL backup is released?

[mpjtaylor]

i am using azapi_resource for now

[jschwellnus92]

i am using azapi_resource for now

Any chance you can share your working code?

[mpjtaylor]

Just created the policy in Azure and exported the json and then used this

resource "azapi_resource" "SQLBackupPolicy" {
  name                    = var.policy_name
  parent_id               = azurerm_recovery_services_vault.sqlsupport.id
  type                    = "Microsoft.RecoveryServices/vaults/backupPolicies@2022-03-01"
  ignore_missing_property = false
  body = templatefile("${path.module}/templates/${var.policy_name}.json.tftpl", {
    "timezone" : var.timezone
  })
  schema_validation_enabled = false
}

[benj-ch]

The resource has been released in August: azurerm_backup_policy_vm_workload

https://github.com/hashicorp/terraform-provider-azurerm/releases/tag/v3.20.0

[petri-o-ojala]

The documentation has an example

  protection_policy {
    policy_type = "Log"

    backup {
      frequency_in_minutes = 15
    }

    simple_retention {
      count = 8
    }
  }

And when trying to deploy almost identical configuration:

        + protection_policy {
          + policy_type = "Log"

          + backup {
              + frequency_in_minutes = 60
              + weekdays             = []
            }

          + retention_daily {
              + count = 35
            }
        }

It fails with not-so-clear error

Status=400 Code="BMSUserErrorInvalidPolicyInput" Message="Input for create or update policy is not in proper format. Please check format of parameters like schedule time, schedule days, retention time and retention days "

The provider (azurerm 3.37) adds weekdays eventhough it's not defined in the code.

[benj-ch]

There is no frequency_in_minutes in "backup" (I don't see it in the documentation).

There is frequency = "Hourly"

The weekdays is just initialized, even it's not defined in the code.
There are many examples of Terraform resource which works like that.

[petri-o-ojala]

https://registry.terraform.io/providers/hashicorp/azurerm/latest/docs/resources/backup_policy_vm_workload#frequency_in_minutes

Considering that there are only a few parameters in the Log policy (and the protection_policy is exactly the same as in the documentation example), my assumption was that the Azure API is complaining due to the weekdays[], didn't check what terraform is sending to the API. Could be something else, too.

We have had working code for workload backup policies a long time through ARM deployment with azurerm_resource_group_template_deployment and I was just planning to switch to AzAPI when I noticed that the new native resource exists, which we would prefer but it worked only for the weekly and daily policies, not for the Log policy. The Log policy there deploys fine with protection policy:

                    {
                        policyType      = "Log"
                        retentionPolicy = {
                            retentionDuration   = {
                                count        = 35
                                durationType = "Days"
                            }
                            retentionPolicyType = "SimpleRetentionPolicy"
                        }
                        schedulePolicy  = {
                            scheduleFrequencyInMins = 15
                            schedulePolicyType      = "LogSchedulePolicy"
                        }
                    }

[gebalam]

hi, any update on that?
azurerm_backup_policy_vm_workload is one thing but still I'm missing something like: azurerm_backup_protected_vm for MSSQL VM. so I could cover in code scenario with backing up databases from SQL VM with recovery services vault.

[frostsxx]

Hi guys,

Any updates on this?

ATM we can only create VM Workload but there is no way to associate SQL VM's to that Workload using Terraform...

[broomfn]

+1 I was hoping to automate this setup too, but wasn't obvious and I ended up here, I guess for the moment I'll have to use the Azure Portal to manually configure 😢