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 PowerShell – Part 2

In Part 1 of this series, I talked a little about what a SQL Managed Instance is and how it works like an on premise SQL Server Instance.  In this next part of the series, I will be showing the steps necessary to deploy a Managed Instance using PowerShell.

Deploying a SQL Managed Instance is a little different than that of a SQL Database in Azure or even a SQL Server instance on a virtual machine.  There are a couple of prerequisites for a SQL Managed Instance.


You can find more detail from Microsoft in the article titled Configure a VNet for Azure SQL Database Managed Instance

  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 could 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.

Here is a sample script to setup a VNet and a subnet.  Here, I am setting up the parameters for the names of the VNet and Subnet.  I can then create the VNet and the SubNet using the PowerShell commands.

$ResourceGroupName = "RG-DBGRL93-01P"
$VNetName = "vnet-dba-mi-azcloud"
$SubNetName = "snet-dba-mi-azcloud"

$virtualNetwork = New-AzureRmVirtualNetwork `
  -ResourceGroupName $ResourceGroupName `
  -Location "North Central US" `
  -Name $VNetName `

  $subnetConfig = Add-AzureRmVirtualNetworkSubnetConfig `
  -Name $SubNetName `
  -AddressPrefix `
  -VirtualNetwork $virtualNetwork

  $virtualNetwork | Set-AzureRmVirtualNetwork

To Create the Route Table and Route

$Route = New-AzureRmRouteConfig -Name "dbatc-mi-rte" -AddressPrefix -NextHopType "Internet"
New-AzureRmRouteTable -Name "RouteTableMI" -ResourceGroupName $ResourceGroupName -Location "North Central US" -Route $Route

Once the VNet, Subnet and Route Table are in place you are ready to deploy the SQL Managed Instance.

I found the following script here, it is a pretty good article showing that has another example using PowerShell.  In this snippet, you can see that the command to create the SQL Managed Instance needs the Subnet ID.  We can get that by running the Get-AzureRmVirtualNetwork and the Get-AzureRmVirtualNetworkSubnetConfig commands.

The Subnet ID needs to be in the format of, getting this ID will be necessary for the ARM Template script I will be working on for the next article.

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

Now, once we create the requirements, we can setup PowerShell to run the commands to deploy the managed instance.  In this script, I am using the command New-AzureRmSqlInstance.  This command is available after installing the AzureRM.SQL module

Install-Module -Name AzureRM.Sql -RequiredVersion 4.12.1
Then run the
Import-Module AzureRm.Sql

Once that is setup you can put it all together as below.

$ResourceGroupName = "RG-DBGRL93-01P"
$VNetName = "vnet-dba-mi-azcloud"
$SubNetName = "snet-dba-mi-azcloud"

$vNet = Get-AzureRmVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName
$subnet = Get-AzureRmVirtualNetworkSubnetConfig -Name $SubnetName -VirtualNetwork $vNet
$subnetId = $subnet.Id

New-AzureRmSqlInstance -Name "dbaitc-mi-ps-01" `
-ResourceGroupName $ResourceGroupName -Location "North Central US" -SubnetId $subnetId `
-AdministratorCredential (Get-Credential) `
-StorageSizeInGB 1024 -VCore 8 -Edition "GeneralPurpose" `
-ComputeGeneration Gen4 -LicenseType LicenseIncluded

Hope this helps out, since Managed Instances are still pretty new, it took me some time to figure this out.  Go ahead and give it a try and let me know how it goes.


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