Provisioning Azure SQL Server and Databases with Terraform

Introduction

Azure SQL Database is a fully managed relational database service provided by Microsoft Azure. It offers high availability, scalability, and security, making it a popular choice for developers and businesses. Provisioning Azure SQL Server and databases using Infrastructure as Code (IaC) practices, such as Terraform, allows for a streamlined, repeatable, and automated process. This approach not only optimizes resource management but also minimizes human error, enhances collaboration among teams, and simplifies the deployment of complex infrastructures.

In this tutorial, we will explore how to create an Azure SQL Server, provision a database, and set up firewall rules using Terraform. We will also cover integrating Azure Active Directory (AAD) authentication for enhanced security. By the end, you'll be equipped with the knowledge to manage Azure SQL resources effectively through IaC.

Prerequisites

Before diving into the tutorial, ensure you have the following prerequisites:

Terraform CLI: Install the latest version of Terraform. You can download it from the Terraform website.
Azure Subscription: You need an active Azure subscription. You can create a free account here.
Azure CLI: Install the Azure CLI to interact with Azure resources. You can find installation instructions here.
Service Principal: Create a service principal with the necessary permissions to manage Azure SQL resources. You can do this with the following command:
```
az ad sp create-for-rbac --name "<Your-Application-Name>" --role Contributor --scopes /subscriptions/<Your-Subscription-ID>
```

Fundamental Concepts

Understanding the following key concepts is essential when working with Terraform and Azure SQL resources:

HCL (HashiCorp Configuration Language): The syntax used to define resources in Terraform.
Resource Dependencies: Terraform automatically handles the dependencies between resources, ensuring they are created in the correct order.
State Management: Terraform maintains a state file that maps real-world resources to your configuration, ensuring accurate updates and changes.

Resource Syntax

The primary resources we will use in this tutorial are:

`azurerm_sql_server`

Argument	Type	Description
name	string	The name of the SQL server.
resource_group_name	string	The name of the resource group in which to create the SQL server.
location	string	The Azure location where the SQL server will be created.
version	string	The version of SQL Server to use.
administrator_login	string	The administrator login for the SQL server.
administrator_login_password	string	The password for the administrator login.
storage_account_type	string	The type of storage to use (e.g., Standard, Premium).
ssl_enforcement	string	Whether to enforce SSL connections.

`azurerm_sql_database`

Argument	Type	Description
name	string	The name of the SQL database.
resource_group_name	string	The name of the resource group in which to create the database.
server_name	string	The name of the SQL server to associate the database with.
requested_service_objective_name	string	The performance tier for the database.
collation	string	The collation for the database.

`azurerm_mssql_firewall_rule`

Argument	Type	Description
name	string	The name of the firewall rule.
resource_group_name	string	The name of the resource group.
server_name	string	The name of the SQL server to associate the firewall rule with.
start_ip_address	string	The starting IP address for the firewall rule.
end_ip_address	string	The ending IP address for the firewall rule.

Practical Examples

1. Create a New Resource Group

resource "azurerm_resource_group" "rg" {
  name     = "rg-sql-demo-${random_string.random_id.result}"
  location = "East US"
}

2. Create a Random String for Unique Resource Names

resource "random_string" "random_id" {
  length  = 8
  special = false
}

3. Provision an Azure SQL Server

resource "azurerm_sql_server" "sql_server" {
  name                         = "sqlserver-${random_string.random_id.result}"
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password  = "P@ssw0rd1234"
  ssl_enforcement              = "Enabled"
}

4. Create an Azure SQL Database

resource "azurerm_sql_database" "sql_database" {
  name                = "sqldatabase-${random_string.random_id.result}"
  resource_group_name = azurerm_resource_group.rg.name
  server_name         = azurerm_sql_server.sql_server.name
  requested_service_objective_name = "S0"
  collation           = "SQL_Latin1_General_CP1_CI_AS"
}

5. Set Up Firewall Rules

resource "azurerm_sql_firewall_rule" "firewall_rule" {
  name                = "AllowMyIP"
  resource_group_name = azurerm_resource_group.rg.name
  server_name         = azurerm_sql_server.sql_server.name
  start_ip_address    = "0.0.0.0"
  end_ip_address      = "255.255.255.255"
}

6. Enable Azure Active Directory Authentication

resource "azurerm_sql_server_active_directory_administrator" "aad_admin" {
  server_name         = azurerm_sql_server.sql_server.name
  resource_group_name = azurerm_resource_group.rg.name
  login               = "aaduser@example.com"
  tenant_id           = "<Your-Tenant-ID>"
}

7. Output Resource Information

output "sql_server_name" {
  value = azurerm_sql_server.sql_server.name
}

output "sql_database_name" {
  value = azurerm_sql_database.sql_database.name
}

8. Initialize, Plan, and Apply Terraform

# Initialize the Terraform deployment
terraform init -upgrade

# Create an execution plan
terraform plan -out main.tfplan

# Apply the execution plan
terraform apply main.tfplan

Real-World Use Cases

1. Multi-Tier Architecture

Provisioning Azure SQL databases as part of a multi-tier application architecture, where the database tier is decoupled from the application tier.

2. Disaster Recovery

Setting up secondary Azure SQL databases in different regions for disaster recovery, leveraging Terraform's ability to manage resources across multiple resource groups.

3. Automated Scaling

Using Terraform with Azure SQL Database elastic pools to manage resource scaling automatically based on traffic, thus optimizing costs and performance.

Best Practices

Use Variables: Use variables for sensitive information like passwords and location settings to enhance security and flexibility.
State Management: Store state files remotely using Azure Storage to enable collaboration and avoid state file conflicts.
Resource Naming Conventions: Adopt a consistent naming convention for resources to improve clarity and manageability.
Use Modules: Break down configurations into reusable modules for better organization and maintainability.
Secure Sensitive Data: Utilize Terraform's sensitive attribute for outputs or variables that contain sensitive information.

Common Errors

Error: "Password must be at least 8 characters long"
Cause: The provided admin password does not meet the complexity requirements.
Solution: Ensure the password is at least 8 characters long and meets Azure's password policy.
Error: "The server name is invalid"
Cause: The server name contains invalid characters or is not globally unique.
Solution: Update the server name to ensure it meets Azure's naming requirements.
Error: "Firewall rule not created"
Cause: The IP addresses specified are not valid or overlap with existing rules.
Solution: Review and adjust the start and end IP addresses in the firewall rule configuration.
Error: "Insufficient permissions"
Cause: The service principal lacks the necessary permissions to create resources.
Solution: Ensure the service principal has the required roles and permissions in Azure.

Related Resources

Resource	Description
azurerm_sql_database	Terraform resource for Azure SQL Database.
azurerm_sql_server	Terraform resource for Azure SQL Server.
azurerm_sql_firewall_rule	Terraform resource for SQL Server firewall rules.
Terraform Documentation	Official Terraform documentation for further reading.

Complete Infrastructure Script

Here’s a complete working Terraform configuration that provisions an Azure SQL Server and database with firewall rules and AAD authentication:

provider "azurerm" {
  features {}
}

resource "random_string" "random_id" {
  length  = 8
  special = false
}

resource "azurerm_resource_group" "rg" {
  name     = "rg-sql-demo-${random_string.random_id.result}"
  location = "East US"
}

resource "azurerm_sql_server" "sql_server" {
  name                         = "sqlserver-${random_string.random_id.result}"
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password  = "P@ssw0rd1234"
  ssl_enforcement              = "Enabled"
}

resource "azurerm_sql_database" "sql_database" {
  name                = "sqldatabase-${random_string.random_id.result}"
  resource_group_name = azurerm_resource_group.rg.name
  server_name         = azurerm_sql_server.sql_server.name
  requested_service_objective_name = "S0"
  collation           = "SQL_Latin1_General_CP1_CI_AS"
}

resource "azurerm_sql_firewall_rule" "firewall_rule" {
  name                = "AllowMyIP"
  resource_group_name = azurerm_resource_group.rg.name
  server_name         = azurerm_sql_server.sql_server.name
  start_ip_address    = "0.0.0.0"
  end_ip_address      = "255.255.255.255"
}

resource "azurerm_sql_server_active_directory_administrator" "aad_admin" {
  server_name         = azurerm_sql_server.sql_server.name
  resource_group_name = azurerm_resource_group.rg.name
  login               = "aaduser@example.com"
  tenant_id           = "<Your-Tenant-ID>"
}

output "sql_server_name" {
  value = azurerm_sql_server.sql_server.name
}

output "sql_database_name" {
  value = azurerm_sql_database.sql_database.name
}

Conclusion

In this tutorial, we explored how to provision Azure SQL Server and databases using Terraform. We covered the creation of firewall rules, as well as the integration of Azure Active Directory for authentication. By leveraging Terraform's capabilities, you can automate and manage your SQL resources efficiently.

Next Steps

Explore further Terraform modules for Azure resources.
Implement CI/CD pipelines to automate your Terraform deployments.
Experiment with scaling Azure SQL databases using elastic pools.