Azure SQL Managed Instance - Long term Retention

Question

Hello all,

I can setup LTR policies -PITR, weekly, yearly backups for all existing databases that are created on Azure SQL MI.
However, the policies DO NOT apply for any new databases created after the policy is created. Is there a way to create a default policy that gets
automatically applies to any new database create henceforth.

Thanks,
Mandar

Answer 1

Yes, it is possible to create a default policy that applies to any new databases created in Azure SQL Managed Instance.

You can achieve this by using the Azure Policy service. Azure Policy is a service that allows you to create, assign, and manage policies for Azure resources. You can use Azure Policy to apply a policy definition to a specific scope such as a resource group, subscription, or management group.

To create a policy definition that applies to Azure SQL Managed Instance, you can use the built-in policy definition called "Audit SQL server and database options." This policy definition audits the server and database options for all SQL servers and databases in a given scope. You can customize this policy definition to set the LTR policies as per your requirement.

After creating the policy definition, you can assign it to a specific scope such as the subscription or management group. This policy will apply to all new databases created in that scope.

Please refer to the Azure Policy documentation for more information on how to create and assign policy definitions: https://docs.microsoft.com/en-us/azure/governance/policy/overview.