Azure SQL Database Cost Optimization via Azure Policies

Azure SQL Database Serverless only via Azure Policy

Azure Policy is a handy tool to apply governance at scale. They are quite underrated yet so powerful. There are lots of built-in policies in Azure and you also get a flexibility to create your own if the built-in policies don’t suffice your needs.

You can apply the policy at the below levels according to your use case:

1. Management Group
2. Subscription
3. Resource Group

However, it is recommended to create the policy definition at the Management Group level.

Subscription Level Policy Sample Use Case

You might want to restrict very high compute resources on your Dev and Test environments, hence you would like a policy to restrict the high compute resources on those environments.

One such example can be Azure API management service creation in Premium Tier. You would not want to use APIM’s premium tier on your lower environments as ideally standard tier is sufficient to handle most of the load.

Azure SQL Database Use Case

We also had a use case wherein we wanted to disallow the creation of Azure SQL databases in provisioned mode. This was primarily to reduce cloud resource wastage. We had faced scenarios where someone created a SQL on provisioned tier with 12 vCores and that spiked the Cloud Cost for that month.

We wanted the Azure SQL database to always be created in Serverless mode. We also wanted to ensure that the Azure SQL databases in serverless mode must have autopause enabled to save on cost.

Azure Policy To Rescue

This was a classic case of using Azure Policy since we wanted to have a certain set of rules to create/update resources for a resource group.

Azure Policy was created to disallow users from creating Azure SQL database instances in provisioned mode. Also, it enforces the SQL database to have autopause enabled to further save on cost when the database is not in use.

"policyRule": {
      "if": {
        "anyOf": [
          {
            "allOf": [
              {
                "field": "type",
                "equals": "Microsoft.Sql/servers/databases"
              },
              {
                "field": "Microsoft.Sql/servers/databases/autoPauseDelay",
                "greater": "[int(60)]"
              }
            ]
          },
          {
            "allOf": [
              {
                "field": "type",
                "equals": "Microsoft.Sql/servers/databases"
              },
              {
                "not": {
                  "field": "Microsoft.Sql/servers/databases/autoPauseDelay",
                  "exists": true
                }
              }
            ]
          }
        ]
      },
      "then": {
        "effect": "deny"
      }
    }

The above policy check any of one condition to be true to mark the resource as non-compliant with deny action:

1. Azure SQL database has an AutoPause delay set to a value greater than 60 minutes.
2. Azure SQL database doesn’t have an autopause field (a case of provisioned database)

As soon as you try to create/update a resource to provisioned tier, the policy won’t allow you to do it.

Policy Deny in Action

This was one use case of Azure Policy, there could be hundred such cases that you can use to enforce organizational standards upon resources on Azure.

Do write down in comments, how you have used the Azure Policy.