Azure SQL Server, Database, and Elastic Pool creation using Terraform.

The idea behind writing this article is recently one of my clients wanted us to create below components

• Azure SQL Server,
• Azure SQL DB,
• Elastic Pool,
• Data Masking,
• Storage
• IP Address Masking
• Specific Vnet mapping.
• along with Log Analytics to Monitor Diagnostic settings ( Including storage account & event hub).

Everything using Terraform. :) [Initially, I thought tough but it's easy]

As most of you might have achieved using ARM templates however my actual fun started when I went back to the drawing board and started writing it on paper to see how it can be achieved using Terraform.

and I start searching for Modules and some of the variables which are hard to achieve.

In order to Overcome the first, I went back to drawing and starting looking for the items which need to be in order.

first, I have started creating some basic components which are required for the Azure SQL server as pre-requisite.

1. Started with 3 azure services creation

• Azure resource group
• Azure SQL server name (along with user id/password)
• Azure Networking (Mainly to allow access to Azure services)

2. Once you create above-mentioned pre-requisite components then you need to plan for additional components such as

• Azure SQL DB
• SQL elastic Pool

3. then finally you need to plan for additional components such as

• adding Log Analytics
• Enabling Diagnostic settings (storage & event hub)
• Creating Azure Storage & assigning IP address mapping
• Enabling Dynamic Data masking

One of the most important things to be remembered is Dependency.

Data Masking

What is Data Masking: Dynamic Data Masking is a feature to limit the sensitive data to the non-privileged users by hiding the data of a column. Data is not masked and stored in the disk. Masking is applied to the result set returned to the user. The users with “db_owner” permission on “UNMASK” permission can still see the unmasked data when they query the table.

there are 5 different types of Data masking functionalities that are available.

• Default
• Credit card
• Email
• Random number
• Custom text

few articles which might be helpful for you guys.

https://docs.microsoft.com/en-us/powershell/module/az.sql/Set-AzSqlDatabaseDataMaskingRule?view=azps-3.8.0

https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started

https://docs.microsoft.com/en-us/rest/api/sql/datamaskingrules/createorupdate#create/update-data-masking-rule-for-text

For Dynamic Data masking functionality unfortunately there is no Terraform module available at this moment of time, so in order to overcome this situation, I have used Az commands. Below is the snippet from one of them after I used it.

Most of the cases your organization data modeler will give you all these Masking rules based on that you need to start automating it.

PowerShell commands:

New-AzSqlDatabaseDataMaskingRule -ResourceGroupName “myresourcegroup” -ServerName “prademoss12” -DatabaseName “testdb” -SchemaName “dbo” -TableName “ASSIGNMENT_CATEGORY” -ColumnName “LAST_UPDATE_USER_ID” -MaskingFunction “Text”

New-AzSqlDatabaseDataMaskingRule -ResourceGroupName “myresourcegroup11” -ServerName “prademoss12” -DatabaseName “testdb” -SchemaName “SalesLT” -TableName “Customer” -ColumnName “Phone” -MaskingFunction “Default”

Entire Terraform main.tf code

provider “azurerm” {
version = “~>1.37.0”
}

resource “azurerm_resource_group” “production” {
name = “myresourcegroup11”
location = “East US”
}

resource “azurerm_sql_server” “sqlserver” {
name=”prademoss12"
resource_group_name=”${azurerm_resource_group.production.name}”
location = “East US”
version=”12.0"
administrator_login=”testadmin”
administrator_login_password=”Passw0rd@123"
}

resource “azurerm_sql_database” “sqldatabase” {
name=”MyDatabase”
resource_group_name=”${azurerm_resource_group.production.name}”
location = “East US”
server_name=”${azurerm_sql_server.sqlserver.name}”
}

resource “azurerm_log_analytics_workspace” “logs” {
name = “prademotfflogs1”
location = “East US”
resource_group_name=”${azurerm_resource_group.production.name}”
sku = “PerNode”
retention_in_days = 30

}

resource “azurerm_monitor_diagnostic_setting” “sqldb_diagnostics” {
name = “resource-specific-diagnostics-table”
target_resource_id = azurerm_sql_database.sqldatabase.id
log_analytics_workspace_id = azurerm_log_analytics_workspace.logs.id
storage_account_id =azurerm_storage_account.storage.id

eventhub_authorization_rule_id = azurerm_eventhub_namespace_authorization_rule.example.id

log {
category = “SQLInsights”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “Errors”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “AutomaticTuning”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “QueryStoreRuntimeStatistics”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “QueryStoreWaitStatistics”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “DatabaseWaitStatistics”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “Blocks”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “Deadlocks”

retention_policy {
enabled = true
days = 7
}
}

log {
category = “Timeouts”

retention_policy {
enabled = true
days = 7
}
}

metric {
category = “Basic”


retention_policy {
enabled = false
}
}

metric {
category = “InstanceAndAppAdvanced”


retention_policy {
enabled = false
}
}

metric {
category = “WorkloadManagement”


retention_policy {
enabled = false
}
}

}

resource “azurerm_storage_account” “storage” {
name = “prademotfflogs1”
resource_group_name=”${azurerm_resource_group.production.name}”

location = “East US”
account_tier = “Standard”
account_replication_type = “LRS”

network_rules {
default_action = “Deny”
ip_rules = [“45.104.64.0/21”, “46.22.64.0/20”, “61.221.181.64/26”, “65.232.32.0/20”, “70.21.80.0/20”, “88.194.45.128/26”, “93.184.208.0/20”, “90.226.203.0/24”, “100.161.240.0/20”, “110.232.176.0/22”, “117.18.232.0/21”]
virtual_network_subnet_ids = [“${azurerm_subnet.subnet.id}”]

}

}

resource “azurerm_virtual_network” “vnet” {
name = “prademovnet13”
location = “East US”
resource_group_name =”${azurerm_resource_group.production.name}”
address_space = [“192.168.14.0/24”]

}

resource “azurerm_subnet” “subnet” {
name = “subnet1”
virtual_network_name = azurerm_virtual_network.vnet.name
resource_group_name =”${azurerm_resource_group.production.name}”
address_prefix = “192.168.14.0/28”
service_endpoints = [“Microsoft.Storage”]

}

resource “azurerm_mssql_elasticpool” “sql_mssql_elasticpool” {
name = “test”
resource_group_name =”${azurerm_resource_group.production.name}”
location = “East US”
server_name=”${azurerm_sql_server.sqlserver.name}”
max_size_gb = 32

sku {
name = “GP_Gen5”
tier = “GeneralPurpose”
family = “Gen5”
capacity = 2
}

per_database_settings {
min_capacity = 0.25
max_capacity = 2
}
}

resource “azurerm_monitor_diagnostic_setting” “sql_elasticpool_diagnostics” {
name = “elasticpool_diagnostic”

target_resource_id = azurerm_mssql_elasticpool.sql_mssql_elasticpool.id
log_analytics_workspace_id = azurerm_log_analytics_workspace.logs.id
storage_account_id =azurerm_storage_account.storage.id

eventhub_authorization_rule_id = azurerm_eventhub_namespace_authorization_rule.example.id

depends_on = [azurerm_storage_account.storage]

metric {
category = “Basic”


retention_policy {
enabled =true
days = 365
}
}

metric {
category = “InstanceAndAppAdvanced”


retention_policy {
enabled =true
days = 365
}
}

}

resource “azurerm_eventhub_namespace” “example” {
name = “prademo-ehnamespace”
location = “East US”
resource_group_name =”${azurerm_resource_group.production.name}”
sku = “Standard”
capacity = 2

}

resource “azurerm_eventhub_namespace_authorization_rule” “example” {
name = “prademo-nsauth-rule”
namespace_name = “${azurerm_eventhub_namespace.example.name}”
resource_group_name =”${azurerm_resource_group.production.name}”

listen = true
send = true
manage = true
}

resource “azurerm_eventhub” “example” {
name = “prademo-eh1”
namespace_name = “${azurerm_eventhub_namespace.example.name}”
resource_group_name =”${azurerm_resource_group.production.name}”

partition_count = 2
message_retention = 1
}

resource “azurerm_eventhub_authorization_rule” “test” {
name = “prademo-enauth-rule”
namespace_name = “${azurerm_eventhub_namespace.example.name}”
eventhub_name = “${azurerm_eventhub.example.name}”
resource_group_name =”${azurerm_resource_group.production.name}”

listen = true
send = true
manage = true
}

variable “azure_elasticpool_name” {
description = “The name of the elastic pool used by SQL Server for the environment. If empty, no Elastic Pool resource is created.”
default = “prademoelasticpool”
}

variable “azure_elasticpool_per_database_settings_max_capacity” {
description = “The maximum capacity any one database can consume (i.e. compute units).”
default = 50
}

variable “azure_elasticpool_max_size_gb” {
description = “The max data size of the elastic pool in gigabytes.”
default = “50”
}

variable “azure_elasticpool_sku_capacity” {
description = “The scale up/out capacity, representing server’s compute units.”
default = 50
}

variable “azure_elasticpool_sku_tier” {
description = “The tier of the particular SKU. We accept Basic/Standard/Premium and will concatenate it to create a BasicPool/StandardPool/PremiumPool SKU name.”
default = “Standard”
}

Entire solution link: https://github.com/v6prask/azuresql_elasticpool_diagnosticsettings

Again I just used the basic form, and it can be variable and UserID/Passwords can be called from Azure Key-vault. Also, we can make use of Managed Identity.

Feel free to provide your thoughts.