Azure SQL

This Terraform module creates an Azure SQL Server and associated databases in an optional SQL Elastic Pool with DTU purchasing model or vCore purchasing model only along with Firewall rules and Diagnostic settings enabled.

Global versioning rule for Azure modules

Module version	Terraform version	AzureRM version
>= 7.x.x	1.3.x	>= 3.0
>= 6.x.x	1.x	>= 3.0
>= 5.x.x	0.15.x	>= 2.0
>= 4.x.x	0.13.x / 0.14.x	>= 2.0
>= 3.x.x	0.12.x	>= 2.0
>= 2.x.x	0.12.x	< 2.0
< 2.x.x	0.11.x	< 2.0

Usage

This module is optimized to work with the ToTheNew Storage Account

locals {
  env         = var.environment
  name        = var.client_name
  name_prefix = "${local.env}${local.name}"
}

resource "azurerm_resource_group" "rg" {
  name     = "${local.name_prefix}rg"
  location = var.location
  tags     = var.extra_tags
}

module "storage_account" {
  source = "git::https://github.com/tothenew/terraform-azure-storageaccount.git"

  account_name               = "cloudscrapertesting2"
  resource_group_name        = azurerm_resource_group.rg.name
  location                   = azurerm_resource_group.rg.location
  log_analytics_workspace_id = module.log_analytics.workspace_id

  account_kind = "BlobStorage"
}


module "log_analytics" {
  source = "git::https://github.com/tothenew/terraform-azure-loganalytics.git"

  workspace_name      = "${local.name_prefix}-log"
  resource_group_name = azurerm_resource_group.rg.name
  location            = azurerm_resource_group.rg.location
  tags                = var.extra_tags
}

resource "random_password" "admin_password" {
  special          = true
  override_special = "#$%&-_+{}<>:"
  upper            = true
  lower            = true
  number           = true
  length           = 32
}

# Elastic Pool
module "sql_elastic" {
  source  = "git::https://github.com/tothenew/terraform-azure-azuresql.git"

  client_name         = var.client_name
  environment         = var.environment
  location            = module.azure_region.location
  location_short      = module.azure_region.location_short
  stack               = var.stack
  resource_group_name = module.rg.resource_group_name

  administrator_login    = "adminsqltest"
  administrator_password = random_password.admin_password.result
  create_databases_users = true

  elastic_pool_enabled  = true
  elastic_pool_max_size = "50"
  elastic_pool_sku = {
    tier     = "GeneralPurpose"
    capacity = 2
  }

  logs_destinations_ids = [
    module.logs.log_analytics_workspace_id,
    module.logs.logs_storage_account_id,
  ]

  databases = [
    {
      name        = "db1"
      max_size_gb = 50
    },
    {
      name        = "db2"
      max_size_gb = 180
    }
  ]

  custom_users = [
    {
      database = "db1"
      name     = "db1_custom1"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db1"
      name     = "db1_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db2"
      name     = "db2_custom1"
      roles    = []
    },
    {
      database = "db2"
      name     = "db2_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    }
  ]
}

# Single Database

module "sql_single" {
  source  = "git::https://github.com/tothenew/terraform-azure-azuresql.git"

  client_name         = var.client_name
  environment         = var.environment
  location            = module.azure_region.location
  location_short      = module.azure_region.location_short
  stack               = var.stack
  resource_group_name = module.rg.resource_group_name

  administrator_login    = "adminsqltest"
  administrator_password = random_password.admin_password.result
  create_databases_users = true

  elastic_pool_enabled = false

  logs_destinations_ids = [
    module.logs.log_analytics_workspace_id,
    module.logs.logs_storage_account_id,
  ]

  databases = [
    {
      name        = "db1"
      max_size_gb = 50
    },
    {
      name        = "db2"
      max_size_gb = 180
    }
  ]

  custom_users = [
    {
      database = "db1"
      name     = "db1_custom1"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db1"
      name     = "db1_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    },
    {
      database = "db2"
      name     = "db2_custom1"
      roles    = []
    },
    {
      database = "db2"
      name     = "db2_custom2"
      roles    = ["db_accessadmin", "db_securityadmin"]
    }
  ]
}

Providers

Name	Version
azurecaf	~> 1.2, >= 1.2.22
azurerm	~> 3.39

Resources

Name	Type
azurerm_mssql_database.elastic_pool_database	resource
azurerm_mssql_database.single_database	resource
azurerm_mssql_database_extended_auditing_policy.elastic_pool_db	resource
azurerm_mssql_database_extended_auditing_policy.single_db	resource
azurerm_mssql_elasticpool.elastic_pool	resource
azurerm_mssql_firewall_rule.firewall_rule	resource
azurerm_mssql_server.sql	resource
azurerm_mssql_server_extended_auditing_policy.sql_server	resource
azurerm_mssql_server_security_alert_policy.sql_server	resource
azurerm_mssql_server_vulnerability_assessment.sql_server	resource
azurerm_mssql_virtual_network_rule.vnet_rule	resource
azurecaf_name.sql	data source
azurecaf_name.sql_dbs	data source
azurecaf_name.sql_pool	data source

Inputs

Name	Description	Type	Default	Required
administrator_login	Administrator login for SQL Server	string	n/a	yes
administrator_password	Administrator password for SQL Server	string	n/a	yes
alerting_email_addresses	List of email addresses to send reports for threat detection and vulnerability assesment	list(string)	[]	no
allowed_cidr_list	Allowed IP addresses to access the server in CIDR format. Default to all Azure services	list(string)	[ "0.0.0.0/32" ]	no
allowed_subnets_ids	List of Subnet ID to allow to connect to the SQL Instance	list(string)	[]	no
azuread_administrator	Azure AD Administrator configuration block of this SQL Server.	object({ login_username = optional(string) object_id = optional(string) tenant_id = optional(string) azuread_authentication_only = optional(bool) })	null	no
backup_retention	Definition of long term backup retention for all the databases in this SQL Server.	object({ weekly_retention = optional(number) monthly_retention = optional(number) yearly_retention = optional(number) week_of_year = optional(number) })	{}	no
client_name	Client name/account used in naming	string	n/a	yes
connection_policy	The connection policy the server will use. Possible values are Default, Proxy, and Redirect	string	"Default"	no
create_databases_users	True to create a user named _user on each database with generated password and role db_owner.	bool	true	no
custom_diagnostic_settings_name	Custom name of the diagnostics settings, name will be 'default' if not set.	string	"default"	no
custom_users	List of objects for custom users creation. Password are generated. These users are created within the "custom_users" submodule.	list(object({ name = string database = string roles = optional(list(string)) }))	[]	no
databases	List of the databases configurations for this server.	list(object({ name = string license_type = optional(string) sku_name = optional(string) max_size_gb = number create_mode = optional(string) min_capacity = optional(number) auto_pause_delay_in_minutes = optional(number) read_scale = optional(string) read_replica_count = optional(number) creation_source_database_id = optional(string) restore_point_in_time = optional(string) recover_database_id = optional(string) restore_dropped_database_id = optional(string) storage_account_type = optional(string, "Geo") database_extra_tags = optional(map(string), {}) }))	[]	no
databases_collation	SQL Collation for the databases	string	"SQL_Latin1_General_CP1_CI_AS"	no
databases_extended_auditing_enabled	True to enable extended auditing for SQL databases	bool	false	no
databases_extended_auditing_retention_days	Databases extended auditing logs retention	number	30	no
databases_zone_redundant	True to have databases zone redundant, which means the replicas of the databases will be spread across multiple availability zones. This property is only settable for Premium and Business Critical databases.	bool	null	no
default_tags_enabled	Option to enable or disable default tags	bool	true	no
elastic_pool_custom_name	Name of the SQL Elastic Pool, generated if not set.	string	""	no
elastic_pool_databases_max_capacity	The maximum capacity (DTU or vCore) any one database can consume in the Elastic Pool. Default to the max Elastic Pool capacity.	number	null	no
elastic_pool_databases_min_capacity	The minimum capacity (DTU or vCore) all databases are guaranteed in the Elastic Pool. Defaults to 0.	number	0	no
elastic_pool_enabled	True to deploy the databases in an ElasticPool, single databases are deployed otherwise.	bool	false	no
elastic_pool_extra_tags	Extra tags to add on ElasticPool	map(string)	{}	no
elastic_pool_license_type	Specifies the license type applied to this database. Possible values are LicenseIncluded and BasePrice	string	null	no
elastic_pool_max_size	Maximum size of the Elastic Pool in gigabytes	string	null	no
elastic_pool_sku	SKU for the Elastic Pool with tier and eDTUs capacity. Premium tier with zone redundancy is mandatory for high availability. Possible values for tier are GeneralPurpose, BusinessCritical for vCore models and Basic, Standard, or Premium for DTU based models. See https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits-elastic-pools"	object({ tier = string, capacity = number, family = optional(string, "Gen5") })	null	no
elastic_pool_zone_redundant	True to have the Elastic Pool zone redundant, SKU tier must be Premium to use it. This is mandatory for high availability.	bool	false	no
environment	Project environment	string	n/a	yes
extra_tags	Extra tags to add	map(string)	{}	no
location	Azure location for SQL Server.	string	n/a	yes
location_short	Short string for Azure location.	string	n/a	yes
logs_categories	Log categories to send to destinations.	list(string)	null	no
logs_destinations_ids	List of destination resources IDs for logs diagnostic destination. Can be Storage Account, Log Analytics Workspace and Event Hub. No more than one of each can be set. If you want to specify an Azure EventHub to send logs and metrics to, you need to provide a formated string with both the EventHub Namespace authorization send ID and the EventHub name (name of the queue to use in the Namespace) separated by the `	` character.	list(string)	n/a
logs_metrics_categories	Metrics categories to send to destinations.	list(string)	null	no
name_prefix	Optional prefix for the generated name	string	""	no
name_suffix	Optional suffix for the generated name	string	""	no
outbound_network_restriction_enabled	Whether outbound network traffic is restricted for this server	bool	false	no
point_in_time_backup_interval_in_hours	The hours between each differential backup. This is only applicable to live databases but not dropped databases. Value has to be 12 or 24. Defaults to 12 hours.	number	12	no
point_in_time_restore_retention_days	Point In Time Restore configuration. Value has to be between 7 and 35	number	7	no
public_network_access_enabled	True to allow public network access for this server	bool	false	no
resource_group_name	Resource group name	string	n/a	yes
security_storage_account_access_key	Storage Account access key used to store security logs and reports	string	null	no
security_storage_account_blob_endpoint	Storage Account blob endpoint used to store security logs and reports	string	null	no
security_storage_account_container_name	Storage Account container name where to store SQL Server vulneralibility assessment	string	null	no
server_custom_name	Name of the SQL Server, generated if not set.	string	""	no
server_extra_tags	Extra tags to add on SQL Server or ElasticPool	map(string)	{}	no
server_version	Version of the SQL Server. Valid values are: 2.0 (for v11 server) and 12.0 (for v12 server). See https://www.terraform.io/docs/providers/azurerm/r/sql_server.html#version	string	"12.0"	no
single_databases_sku_name	Specifies the name of the SKU used by the database. For example, GP_S_Gen5_2, HS_Gen4_1, BC_Gen5_2. Use only if elastic_pool_enabled variable is set to false. More documentation here	string	"GP_Gen5_2"	no
sql_server_extended_auditing_enabled	True to enable extended auditing for SQL Server	bool	false	no
sql_server_extended_auditing_retention_days	Server extended auditing logs retention	number	30	no
sql_server_security_alerting_enabled	True to enable security alerting for this SQL Server	bool	false	no
sql_server_vulnerability_assessment_enabled	True to enable vulnerability assessment for this SQL Server	bool	false	no
stack	Project stack name	string	n/a	yes
threat_detection_policy_disabled_alerts	Specifies a list of alerts which should be disabled. Possible values include Access_Anomaly, Sql_Injection and Sql_Injection_Vulnerability	list(string)	[]	no
threat_detection_policy_enabled	True to enable thread detection policy on the databases	bool	false	no
threat_detection_policy_retention_days	Specifies the number of days to keep in the Threat Detection audit logs	number	7	no
tls_minimum_version	The TLS minimum version for all SQL Database associated with the server. Valid values are: 1.0, 1.1 and 1.2.	string	"1.2"	no
use_caf_naming	Use the Azure CAF naming provider to generate default resource name. server_custom_name and elastic_pool_custom_name override this if set. Legacy default name is used if this is set to false.	bool	true	no
use_caf_naming_for_databases	Use the Azure CAF naming provider to generate databases names.	bool	false	no

Outputs

Name	Description
custom_databases_users	Map of the custom SQL Databases users
custom_databases_users_roles	Map of the custom SQL Databases users roles
default_administrator_databases_connection_strings	Map of the SQL Databases with administrator credentials connection strings
default_databases_users	Map of the SQL Databases dedicated users
identity	Identity block with principal ID and tenant ID used for this SQL Server
security_alert_policy_id	ID of the MS SQL Server Security Alert Policy
sql_administrator_login	SQL Administrator login
sql_administrator_password	SQL Administrator password
sql_databases	SQL Databases
sql_databases_id	Map of the SQL Databases IDs
sql_elastic_pool	SQL Elastic Pool
sql_elastic_pool_id	ID of the SQL Elastic Pool
sql_server	SQL Server
terraform_module	Information about this Terraform module
vulnerability_assessment_id	ID of the MS SQL Server Vulnerability Assessment

Authors

Module managed by TO THE NEW Pvt. Ltd.

License

Apache 2 Licensed. See LICENSE for full details.

Related documentation

Microsoft Azure root documentation: docs.microsoft.com/en-us/azure/sql-database/