2

I am trying to implement Azure SQL failover group using Bicep templates. The problem I have is, I am unable to figure out how to reference array of databases passed as parameter in failover group's database property. When assigning array values to database property it gives me an error of expecting string values whereas assigned was array, which of course isn't correct.

resource symbolicname 'Microsoft.Sql/servers/failoverGroups@2021-11-01-preview' = {
  name: 'string'
  tags: {
    tagName1: 'tagValue1'
    tagName2: 'tagValue2'
  }
  parent: resourceSymbolicName
  properties: {
    databases: [
      'string'
    ]
    partnerServers: [
      {
        id: 'string'
      }
    ]
    readWriteEndpoint: {
      failoverPolicy: 'string'
      failoverWithDataLossGracePeriodMinutes: int
    }
  }
}

Does anyone have any working example of Azure SQL failover group using Bicep templates or point me in the right direction on the solution?

Edit 1 - main.bicep : This is the code that I am trying after adding Thomas's changes:

param databases array
param primarySqlServerName string
param drSqlServerName string
param failovergroupName string

module primarySql '***************' = {
  Deploy primary SQL Server and database
}
module drSql '****************' = {
  Deploy secondary SQL Server
}

resource sqlServerFailoverGroup 'Microsoft.Sql/servers/failoverGroups@2020-11-01-preview' = {
  name: '${primarySqlServerName}/${failovergroupName}'
  properties: {
    databases: [for database in databases: resourceId('Microsoft.Sql/servers/databases', primarySqlServerName, database)]
    readWriteEndpoint: {
      failoverPolicy: 'Automatic'
      failoverWithDataLossGracePeriodMinutes: 60
    }
    readOnlyEndpoint: {
      failoverPolicy: 'Enabled'
    }
    partnerServers: [
      {
        id: resourceId('Microsoft.Sql/servers', drSqlServerName)
      }
    ]
  }
  dependsOn: [
    primarySql
    drSql
  ]
}

Getting below error while deploying above:

Unable to process template language expressions for resource at line '1' and column '1289'. 'Unable to evaluate template language function 'resourceId': all function arguments must be string literals. Please see aka.ms/arm-template-expressions/#resourceid for usage details.

2
  • 1
    Could you post your bicep file and the related error please ? it will also help other finding this question if they encounter the same issue. Commented Jun 21, 2022 at 20:17
  • Are you sure the failover group is causing the issue ? Just tried and it is working fine for me. see updated answer. Commented Jun 25, 2022 at 5:53

1 Answer 1

3

The databases property is an of string. The partnerServers property is an array of object.

Both expect the resource id of the databases and partner servers.

Here is a simple sample of deploying 2 sql servers and databases with a failover group

sql.bicep:

param location string = resourceGroup().location
param sqlServerName string
param sqlServerPrincipalType string
param sqlServerAadAdminName string
param sqlServerAadAdminId string
param databaseNames array

// Create the Server
resource sqlServer 'Microsoft.Sql/servers@2020-11-01-preview' = {
  name: sqlServerName
  location: location
  tags: {}
  properties: {
    administrators: {
      administratorType: 'ActiveDirectory'
      principalType: sqlServerPrincipalType
      login: sqlServerAadAdminName
      sid: sqlServerAadAdminId
      azureADOnlyAuthentication: true
    }
  }
}

// We create the database only in the primary region
resource database 'Microsoft.Sql/servers/databases@2020-08-01-preview' = [for databaseName in databaseNames: if (!empty(databaseNames)) {
  name: empty(databaseNames) ? 'placeholder' : databaseName
  parent: sqlServer
  location: location
  sku: {
    name: 'S0'
    tier: 'Standard'
  }
  properties: {
    sourceDatabaseId: sqlServer.id
  }
}]

and the main.bicep file:

param failoverGroupName string
param primarySqlServerName string
param primaryLocation string
param secondarySqlServerName string
param secondaryLocation string
param sqlServerPrincipalType string
param sqlServerAadAdminName string
param sqlServerAadAdminId string

param databaseNames array

module primarySql 'sql.bicep' = {
  name: 'primarySql'
  params: {
    location: primaryLocation
    sqlServerName: primarySqlServerName
    sqlServerPrincipalType: sqlServerPrincipalType
    sqlServerAadAdminName: sqlServerAadAdminName
    sqlServerAadAdminId: sqlServerAadAdminId
    databaseNames: databaseNames
  }
}

module secondarySql 'sql.bicep' = {
  name: 'secondarySql'
  params: {
    location: secondaryLocation
    sqlServerName: secondarySqlServerName
    sqlServerPrincipalType: sqlServerPrincipalType
    sqlServerAadAdminName: sqlServerAadAdminName
    sqlServerAadAdminId: sqlServerAadAdminId
    databaseNames: []
  }
}

resource sqlServerFailoverGroup 'Microsoft.Sql/servers/failoverGroups@2020-11-01-preview' = {
  name: '${primarySqlServerName}/${failoverGroupName}'
  dependsOn: [ primarySql, secondarySql ]
  properties: {
    databases: [for dataBaseName in databaseNames: resourceId('Microsoft.Sql/servers/databases', primarySqlServerName, dataBaseName)]
    readWriteEndpoint: {
      failoverPolicy: 'Automatic'
      failoverWithDataLossGracePeriodMinutes: 60
    }
    readOnlyEndpoint: {
      failoverPolicy: 'Enabled'
    }
    partnerServers: [
      {
        id: resourceId(resourceGroup().name, 'Microsoft.Sql/servers', secondarySqlServerName)
      }
    ]
  }
}
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks, your code helped a lot! I tried your code and it works fine when I have primary SQL Server, dr SQL Server and databases already present. But when I deploy it as a part of same template which deploys my primary SQL Server, dr SQL Server and databases, it throws below error: Unable to process template language expressions for resource at line '1' and column '1289'. 'Unable to evaluate template language function 'resourceId': all function arguments must be string literals. Please see aka.ms/arm-template-expressions/#resourceid for usage details.
could you share your updated template please ?
Thanks Thomas for your help! The issue was databases was defined as an array with multiple properties. So below line was causing the issue. Changing the database with database.name at the end fixed the issue. databases: [for database in databases: resourceId('Microsoft.Sql/servers/databases', primarySqlServerName, database)] Appreciate your assistance!

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.