[AVM Module Issue]: Azure SQL Server with an Elastic Pool and a Database

[gsuttie]

Check for previous/existing GitHub issues

• [x] I have checked for previous/existing GitHub issues

Issue Type?

I'm not sure

Module Name

avm/res/sql/server

(Optional) Module Version

latest

Description

How can you define an Azure SQK Server with a secondary database and an elastic pool al lin the same block. It allows you to create databaes[] and elasticPools[], but how do you add the database to the elastic pool using just one script block?

(Optional) Correlation Id

No response

[microsoft-github-policy-service[bot]]

[!IMPORTANT] The "Needs: Triage :mag:" label must be removed once the triage process is complete!

[!TIP] For additional guidance on how to triage this issue/PR, see the BRM Issue Triage documentation.

[avm-team-linter[bot]]

@gsuttie, thanks for submitting this issue for the avm/res/sql/server module!

[!IMPORTANT] A member of the @Azure/avm-res-sql-server-module-owners-bicep or @Azure/avm-res-sql-server-module-contributors-bicep team will review it soon!

[peterbud]

Hi @gsuttie , have you checked the README file, specifically at the large parameter set example: https://github.com/Azure/bicep-registry-modules/blob/main/avm/res/sql/server/README.md#example-5-using-large-parameter-set

Or do you have something else where you have an issue with? Could you provide a specific example maybe?

[gsuttie]

Hi @peterbud yeah that refers to elasticPoolId: '' with databases[] - whats the id of the Elastic Pool then?

[peterbud]

That is the resourceId of the Elastic Pool resource. Good example could be the test bicep file in the repo: https://github.com/Azure/bicep-registry-modules/blob/main/avm/res/sql/server/tests/e2e/max/main.test.bicep

First the elastic pool is defined in the server deployment:

...
    elasticPools: [
      {
        name: '${namePrefix}-${serviceShort}-ep-001'
        skuName: 'GP_Gen5'
        skuTier: 'GeneralPurpose'
        skuCapacity: 10
      }
    ]

And then at the database it is referred with the full resourceId:

...
databases: [
      {
        name: '${namePrefix}-${serviceShort}db-001'
        elasticPoolId: '${resourceGroup.id}/providers/Microsoft.Sql/servers/${namePrefix}-${serviceShort}/elasticPools/${namePrefix}-${serviceShort}-ep-001'
}]

HTH

[gsuttie]

How come the elasticPoolId starts with ${resourceGroup.Id} - seems a bit odd.

[gsuttie]

So if i only had this Bicep, I want to create a SQL Server, Database and Elastic pool in one block of code as below, is this possible?

module sqlServer 'br/public:avm/res/sql/server:0.8.0' = if (deploySQLServer) {
  scope: resourceGroup(workloadsResourceGroupArray[4].name)
  name: 'sqlServer-${environmentName}'
  params: {
    name: sqlServerName
    administratorLogin: sqlAdministratorLogin
    administratorLoginPassword: keyVault.getSecret(config.kvSQLPassword)
    managedIdentities: {
      systemAssigned: false
      userAssignedResourceIds: [
        managedIdentity.id
      ]
    }
    primaryUserAssignedIdentityId: managedIdentity.id
    location: location
    tags: tags
    databases: [
      {
        name: 'demodbserveruksouthgregor'
        maxSizeBytes: 2147483648
        skuName: 'GP_Gen5_2'
        skuTier: 'GeneralPurpose'
        skuFamily: 'Gen5'
        zoneRedundant: false
        capacity: 2
        elasticPoolName: elasticPoolName
        //elasticPoolId: '${resourceGroup.id}/providers/Microsoft.Sql/servers/${sqlServerName}/elasticPools/${elasticPoolName}-ep-001'
        elasticPoolId: '<what goes here?>
      }
    ]
    elasticPools: [
      {
        maxSizeBytes: 34359738368
        name: elasticPoolName
        perDatabaseSettings: {
            minCapacity: 0
            maxCapacity: 2
        }
        skuCapacity: 2
        skuName: 'GP_Gen5'
        skuTier: 'GeneralPurpose'
        zoneRedundant: false
        maintenanceConfigurationId: '/subscriptions/${subscriptionId}/providers/Microsoft.Maintenance/publicMaintenanceConfigurations/SQL_Default'
      }
    ]
  }
}

[peterbud]

Few observations:

• There is no elasticPoolName under the database object
• To keep it simple I'd not specify maintenanceConfigurationId at the beginning
• You need the resourceId for the workloadsResourceGroupArray[4], under which you deploy the server (like using with the existing keyword or similar. Let's assume that will be in your variable resourceGroupId
• then the elasticPoolId would be something like: '${resourceGroupId}/providers/Microsoft.Sql/servers/${sqlServerName}/elasticPools/${elasticPoolName}'

[peterbud]

Closing now, if it's needed we can re-open.