How To Deploy an Azure SQL Database using Terraform

Moving on to Terraform seems to be an easy transition after working on some ARM Templates.  The Terraform configuration file is a little easier than a JSON file to read and work with.  Depending on what you want to deploy you would have a section for each compute resource or back end resource, i.e. storage account, resource group or a virtual machine.

In this article I will walk through a Terraform configuration file to deploy an Azure SQL database in an Azure Resource Group along with a storage account used for setting up a threat detection policy.  I am also calling a PowerShell command in the configuration file to setup the long term retention backup policy for the database.

First, setup the Terraform executable.  Terraform has a good step by step guide on installing the executable here.

Initialize Terraform

After setting up the Terraform executable in a folder, the first thing to do is to initialize Terraform.  At a command prompt or in a terminal window in VS Code, type in Terraform Init and hit enter.  Once it is initialized you are ready to begin.

Terraform Provider section
The first section is the provider section, this is where you specify the cloud provider.  For this example, I am using the Azure provider.  A list of all the providers can be found here.  In this section, you also specify the subscription ID and the Tenant ID.

provider "azurerm" {


Terraform Resources

The majority of the Terraform configuration file will consist of resource blocks.  The resource block consists of either the parameters or variables needed to deploy that resource.    Below, I will try to point out the resources I am using to deploy the SQL database.  Terraform will keep this state of the resource so that it maintains that same configuration through out your development process.

Resource Group

The first resource I have in the configuration file is to deploy a resource group.  In this one I am just giving it the name of the resource group along with the location.

resource "azurerm_resource_group" "test2" {
  name     = "RGTFDBGRL93"
  location = "North Central US"

Storage Account

Next, I am creating a storage account.  This one has a bit more detail to it.  Here you can see, I am giving it a name, telling it which resource group to deploy to along with location.  Since there are different types of storage accounts,  I need to tell it to create a standard storage account.

resource "azurerm_storage_account" "test2sa" {
  name                     = "satfsqldbdiag01"
  resource_group_name      = "${}"
  location                 = "North Central US"
  account_tier             = "Standard"
  account_replication_type = "GRS"

Logical SQL Server for Azure SQL Database

OK, now that some of the requirements are done.  We can get into the heart of creating an Azure SQL Database.  One thing about an Azure SQL Database is that it still uses a SQL Server, meaning there is a logical SQL Server instance created when deploying an Azure SQL Database.

In this next section, I point out the details of the SQL Server instance, name, resource group, location, along with the administrator login and password.  This login and password would be the equivalent of the sa account and password in a SQL Server instance on premise.

With Terraform there are a few things that can’t be configured in the resource block.    For this, I am adding a local-exec provisioner within the SQL Server resource group.  The Provisioner section gives me a way to call PowerShell commands to do some of the configuration work I need done to the database.

resource "azurerm_sql_server" "test2" {
  name                         = "sqldbsrvrtf01"
  resource_group_name          = "${}"
  location                     = "North Central US"
  version                      = "12.0"
  administrator_login          = "sqldbadmin"
  administrator_login_password = "chang3Me!"

  provisioner "local-exec" {
    command     = "Set-AzureRmSqlServerAuditing -State Enabled -ResourceGroupName ${}  -ServerName 'sqldbsrvrtf01' -StorageAccountName ${}"
    interpreter = ["PowerShell", "-Command"]

Server Firewall Rule

Now that the logical SQL Server is set, we can give it the firewall rules.

resource "azurerm_sql_firewall_rule" "test2" {
  name                = "FirewallRule1"
  resource_group_name = "${}"
  server_name         = "${}"
  start_ip_address    = ""
  end_ip_address      = ""

Azure SQL Database

Now, to the SQL Database.  You can see here, the resource blocks are pretty much the same, give it a name, resource group and location.

Notice, at the end of this block, I added a PowerShell command to set the long term backup retention policy for the database.  This will give you the ability to set monthly or weekly backup retention plans to fit your needs.

resource "azurerm_sql_database" "test2" {
  name                = "sqldbtf01"
  resource_group_name = "${}"
  location            = "North Central US"
  server_name         = "${}"

  threat_detection_policy {
    state                      = "Enabled"
    email_addresses            = [""]
    retention_days             = "30"
    storage_account_access_key = "${azurerm_storage_account.test2sa.primary_access_key}"
    storage_endpoint           = "${azurerm_storage_account.test2sa.primary_blob_endpoint}"
    use_server_default         = "Enabled"

  provisioner "local-exec" {
    command     = "Set-AzureRmSqlDatabaseBackupLongTermRetentionPolicy -ResourceGroupName ${}  -ServerName ${} -DatabaseName 'sqldbsrvrtf01' -WeeklyRetention P12W -YearlyRetention P5Y -WeekOfYear 16 "
    interpreter = ["PowerShell", "-Command"]

Basically, that is all you need. I kept it pretty simple here. Ideally you may want to create some variables and maybe some modules to that the code is a little more reusable and robust. But this is a good start to understanding Terraform.

One of the unique features of Terraform is that it creates a plan, sort of like an execution plan of the resources. When you enter Terraform Plan in the command prompt, it will list out all the resources it will deploy.

When ready, enter Terraform Apply to deploy the resource(s).

That’s about it.  It turns out to be a pretty simple process.  Next time, I will go over deploying a SQL Server virtual machine in Azure.

Hope this helps!!!

How To Deploy an Azure SQL Managed Instance using ARM Templates – Part 3

Part 1 of this series, went over the steps on how to create a SQL Managed Instance using the Azure Portal.  In part 2, I described using a PowerShell script to create a Managed Instance.  For this final part in deploying SQL Managed Instances, I will go over what it would take to use an ARM Template.

To start off, I used this blog post as a starting point and plan on building it from there.

A SQL Managed Instance has some requirements that were discussed in part 2.  The article goes into a little more detail on how to get the requirements setup.  This article from Microsoft goes into how to configure a VNet for a Managed Instance.

As a review here are the prerequisites for a SQL Managed Instance in Azure.

  1.  Setup/configure a VNet (Virtual Network) and a subnet.  Once a VNet is created, you will need to create a  dedicated subnet that the Managed Instance will be in, please note the subnet cannot contain any other cloud service.
  2. A compatible Network Security Group (NSG), the site mentioned above has the security rules laid out in detail.
  3. Create a user route table with Next Hop Internet as the mandatory user-defined route table (UDR) assigned to it.
  4. Must not have a service endpoint associated with it.
  5. Must have a minimum of 16 IP addresses assigned to it.

Once all the requirements are met, you will need to obtain the Subnet ID, it should look something like the following. We will be using this in the Parameters file for the ARM Template.

/subscriptions/<Subscription ID>/resourceGroups/RG-DBGRL93-01P/providers/Microsoft.

The first part of the ARM Template will be the parameters.  This, really is not too different from the parameters section of the Azure SQL Database template we used.  As you can see, I use a lot of the same parameters.

    "parameters": {
        "administratorLogin": {
            "type": "string"
        "administratorLoginPassword": {
            "type": "securestring"
        "location": {
            "type": "string"
        "managedInstanceName": {
            "type": "string"
        "subnetId": {
            "type": "string"
        "skuName": {
            "type": "string",
            "defaultValue": "GP_Gen4"
        "skuEdition": {
            "type": "string",
            "defaultValue": "GeneralPurpose"
        "storageSizeInGB": {
            "type": "int",
            "defaultValue": "32"
        "vCores": {
            "type": "int",
            "defaultValue": "16"
        "licenseType": {
            "type": "string",
            "defaultValue": "LicenseIncluded"
        "hardwareFamily": {
            "type": "string",
            "defaultValue": "Gen4"

In this example, I am using a separate Parameters file.  This is where I tell it the subnet ID I created and to use it for the Managed Instance.
"subnetId": {
"value": "/subscriptions//resourceGroups/RG-DBGRL93-01P/providers/Microsoft.Network/virtualNetworks/vnet-dba-mi-azcloud/subnets/snet-dba-mi-azcloud"

Usually after the Parameters section, you would create any variables that may be needed for the template.  In this example, I am not going to be using any variables.  I will be going straight to the resources section.

As you can see, it is pretty straight forward.  If you would like, there is a resources template for adding databases to the managed instance.  In this post, I am keeping it pretty basic and just deploying the Managed Instance.

   "resources": [
            "apiVersion": "2015-05-01-preview",
            "type": "Microsoft.Sql/managedInstances",
            "identity": {
                "type": "SystemAssigned"
            "location": "[parameters('location')]",
            "name": "[parameters('managedInstanceName')]",
            "sku": {
                "name": "[parameters('skuName')]",
                "tier": "[parameters('skuEdition')]"
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "subnetId": "[parameters('subnetId')]",
                "storageSizeInGB": "[parameters('storageSizeInGB')]",
                "vCores": "[parameters('vCores')]",
                "licenseType": "[parameters('licenseType')]",
                "hardwareFamily": "[parameters('hardwareFamily')]"

Finally, to deploy the Managed Instance using Powershell.

New-AzureRmResourceGroupDeployment -administratorLoginPassword $secpasswd -ResourceGroupName 'RG-DBGRL93-01P' -TemplateFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ARMTemplt_SQLMI.json' -TemplateParameterFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ARMParams_SQLMI.json'

When deploying a Managed Instance for the first time in a VNet, I have seen it take up to 3 hours.  Once it is complete, you will be able to complete the configurations and connect to it using SQL Server Management Studio.

Hope this helps in deploying SQL Managed Instance.

How to Create a Basic Azure SQL Database – Part 1


  1. Microsoft Azure Account
  2. Resource Group


Once logged into the Azure Portal click on the Option for SQL Databases from the menu on the left side.

You should then see an Add button as shown below, Click on Add.

The Create new Database Blade will then appear.

You will need to fill in the name of the database you want to create.  When ready select the subscription.  If you have not already created a Resource Group, you can click on the Create New link.

A Resource Group in Azure is basically a logical container to group the services you are creating.  This grouping can then be setup for different business functions or applications and permissions can be set accordingly.

Click on the Configure Server.  If no server is found, the create new server blade will appear.  It will require you to set a server name.

Even though you are creating a single database, Azure creates a virtual SQL Server in the background.  You are able to create multiple databases on this server.  However, it does not function as an instance of SQL Server would if you had an instance on a VM or in your data center.

Enter a name for the server admin login and a password.  Choose a location nearest you or where the database will be used.

Checking the box to Allow Azure services to access the server basically opens the firewall settings to let all IP addresses get to the database. More on the Firewall settings in another article.

Click on Select at the bottom of the blade.  Another blade will appear where you can select the pricing tier and the collation. The default is the Standard, for this demonstration, I am selecting the Basic tier.

When ready you can click on Create.  Clicking on Automation Options will create an ARM Template to save. More on ARM Templates in future articles.

Once you click on Create, the deployment process will begin.  Going to the Resource Group you created, you can also see the status of the deployment