Deploying Azure SQL Server using Bicep

In this article, part of our ongoing series on Azure DevOps, we're diving into how you can deploy Azure SQL Server using Bicep. This process allows you to deploy an Azure SQL Server, generate its connection string, and then pass this connection string into another Bicep file that sets up an App Service in Azure.

Before we get started I'd like to point out that I'll only show certain parts of the Bicep files just to keep this article as short as possible, but you can find the repository link at the end of the article.

How The Bicep Files Will Work

1. Variables: We will get the DB_ADMIN_USERNAME and DB_ADMIN_PASSWORD from our Azure DevOps pipeline variables.

2. PowerShell Script: These variables will then be fed into our PowerShell script, which will trigger the Bicep deployment.

3. SQL Server Module: A separate Bicep module will handle the creation of the SQL Server, generating a connection string as an output.

4. App Service Module: This connection string will then be used as a parameter in another Bicep module that creates the App Service.

5. Configuration: The connection string will be added to the App Service configuration under the connection strings instance.

6. SKU and Tier: We also have a JSON configuration file that specifies the SKU and tier of the database, which for this example will be Basic.

Bicep Code Explained

The main.bicep file combines the modules for the SQL Server and the App. The SQL Server module gets its parameters like databaseSku, databaseTier, databaseAdminUserName, and databaseAdminPassword from the main Bicep parameters.

Here is a snippet from main.bicep:

module sqlServer 'sqlserver.bicep' = {
  name: 'sqlserver'
  params: {
    prefix: prefix
    location: location
    sku: databaseSku
    tier: databaseTier
    administratorLogin: databaseAdminUserName
    administratorLoginPassword: databaseAdminPassword
  }
}

The sqlserver.bicep module is responsible for creating the SQL Server. It also generates a connection string which will be outputted and used by the app.bicep module.

output dbConnectionString string = connectionString

The app.bicep module creates the App Service and attaches the SQL database to it via the connection string.

param connectionString string
resource webApp 'Microsoft.Web/sites@2022-03-01' = {
  // ... (other properties)
  properties: {    
    // ...
    siteConfig: {
      connectionStrings: [
        {
          connectionString: connectionString
          name: 'DefaultConnection'
          type: 'SQLAzure'
        }
      ]
    }
  }
}

The prod.json/qa.json configuration files will specify database details such as its SKU and tier. We can use different values according to our environment. In this case, I'll use Basic for both, but in real-world applications, you might use a lower tier for non-prod environments and a more powerful one for the production environment.

{
  "parameters": {
    // ...
    "databaseTier": {
      "value": "Basic"
    },
    "databaseSku": {
      "value": "Basic"
    }
  }
}

The full source code is available on this Azure DevOps repository:
https://dev.azure.com/bujdea/_git/AzureDevopsYamlPipeline

Conclusion

In a real-world application you would also configure database backups using Bicep, allow managed identity authentication, and so on... but for this tutorial, I wanted to keep things simple and show you how easy it is to spin up an Azure SQL Server instance using Bicep. If you would like me to expand on this topic just drop a comment below 👇