07. Azure SQL / Cosmos DB

どっちを使う？

SQL Server 論理サーバ

まず「論理 SQL サーバ」を作り、その中に複数 DB を配置するモデル。

resource "azurerm_mssql_server" "main" {
  name                = "sql-myapp-prd-jpe"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  version             = "12.0"

  administrator_login          = "sqladmin"
  administrator_login_password = random_password.sql.result

  # Entra ID 認証も併用
  azuread_administrator {
    login_username = data.azuread_group.sql_admins.display_name
    object_id      = data.azuread_group.sql_admins.object_id
  }

  minimum_tls_version          = "1.2"
  public_network_access_enabled = false   # プライベートのみ

  tags = local.common_tags
}

resource "random_password" "sql" {
  length  = 32
  special = true
}

SQL Database

resource "azurerm_mssql_database" "myapp" {
  name      = "myapp"
  server_id = azurerm_mssql_server.main.id

  sku_name      = "S0"                    # DTU モデル: S0/S1/S2... または GP_Gen5_2 (vCore)
  max_size_gb   = 50
  collation     = "Japanese_CI_AS"

  zone_redundant = true
  storage_account_type = "Geo"            # ローカル / Zone / Geo

  short_term_retention_policy {
    retention_days = 7
  }

  long_term_retention_policy {
    weekly_retention  = "P4W"
    monthly_retention = "P12M"
    yearly_retention  = "P5Y"
    week_of_year      = 1
  }

  threat_detection_policy {
    state = "Enabled"
  }
}

本番向け SQL の設定

# Transparent Data Encryption (TDE) は新規 DB はデフォルトで有効

# ファイアウォール（VNet のみ許可）
resource "azurerm_mssql_virtual_network_rule" "main" {
  name      = "allow-app-subnet"
  server_id = azurerm_mssql_server.main.id
  subnet_id = azurerm_subnet.private.id
}

# 監査ログを Log Analytics に
resource "azurerm_mssql_server_extended_auditing_policy" "main" {
  server_id              = azurerm_mssql_server.main.id
  storage_endpoint       = azurerm_storage_account.audit.primary_blob_endpoint
  retention_in_days      = 90
}

Cosmos DB

resource "azurerm_cosmosdb_account" "main" {
  name                = "cosmos-myapp-prd"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location
  offer_type          = "Standard"
  kind                = "GlobalDocumentDB"   # SQL API

  consistency_policy {
    consistency_level = "Session"
  }

  geo_location {
    location          = "Japan East"
    failover_priority = 0
  }
  geo_location {
    location          = "Japan West"
    failover_priority = 1
  }

  capabilities {
    name = "EnableServerless"   # 従量制（容量予約しない）
  }
}

resource "azurerm_cosmosdb_sql_database" "myapp" {
  name                = "myapp"
  resource_group_name = azurerm_resource_group.main.name
  account_name        = azurerm_cosmosdb_account.main.name
}

resource "azurerm_cosmosdb_sql_container" "sessions" {
  name                  = "sessions"
  resource_group_name   = azurerm_resource_group.main.name
  account_name          = azurerm_cosmosdb_account.main.name
  database_name         = azurerm_cosmosdb_sql_database.myapp.name
  partition_key_paths   = ["/userId"]
  default_ttl           = 86400   # 24h で自動削除
}

PostgreSQL Flexible Server

resource "azurerm_postgresql_flexible_server" "main" {
  name                = "pg-myapp-prd"
  resource_group_name = azurerm_resource_group.main.name
  location            = azurerm_resource_group.main.location

  version  = "16"
  sku_name = "GP_Standard_D2s_v3"

  delegated_subnet_id = azurerm_subnet.db.id   # subnet を delegation で予約
  private_dns_zone_id = azurerm_private_dns_zone.postgres.id

  storage_mb = 32768
  zone       = "1"
  high_availability {
    mode                      = "ZoneRedundant"
    standby_availability_zone = "2"
  }

  backup_retention_days        = 14
  geo_redundant_backup_enabled = true

  administrator_login    = "pgadmin"
  administrator_password = random_password.pg.result

  authentication {
    active_directory_auth_enabled = true
    password_auth_enabled         = true
    tenant_id                     = data.azurerm_client_config.current.tenant_id
  }
}