Azure SQL

Deploy and manage Azure SQL Database, Elastic Pools, and Cosmos DB. Covers server provisioning, firewall rules, geo-replication, backup strategies, performance tuning, security hardening, and Terraform configurations.

部署并管理Azure SQL Database、弹性池以及Cosmos DB。涵盖服务器预配、防火墙规则、异地复制、备份策略、性能调优、安全加固和Terraform配置等内容。

When to Use

适用场景

You need a fully managed relational database on Azure.
Your application requires geo-replication for disaster recovery.
You need elastic scaling across multiple databases with Elastic Pools.
You are migrating on-premises SQL Server workloads to the cloud.
You need a globally distributed NoSQL database (Cosmos DB).

你需要在Azure上部署全托管关系型数据库
你的应用需要异地复制来实现灾难恢复
你需要通过弹性池实现跨多个数据库的弹性扩展
你正在将本地SQL Server工作负载迁移到云端
你需要一个全局分布式的NoSQL数据库（Cosmos DB）

Prerequisites

前提条件

bash

undefined

bash

undefined

Install Azure CLI

curl -sL https://aka.ms/InstallAzureCLIDeb | sudo bash

Login and set subscription

az login az account set --subscription "my-subscription-id"

Create resource group

az group create --name database-rg --location eastus

undefined

az group create --name database-rg --location eastus

undefined

SQL Server and Database Creation

SQL服务器与数据库创建

Create SQL Server

创建SQL服务器

bash

undefined

bash

undefined

Create logical SQL server

az sql server create
--resource-group database-rg
--name myapp-sqlserver
--location eastus
--admin-user sqladmin
--admin-password 'S3cur3P@ssw0rd!'
--enable-public-network false
--minimal-tls-version 1.2

Enable Azure AD authentication

az sql server ad-admin create
--resource-group database-rg
--server-name myapp-sqlserver
--display-name "SQL Admins"
--object-id "{aad-group-object-id}"

Enable Azure AD only authentication (disable SQL auth)

az sql server ad-only-auth enable
--resource-group database-rg
--name myapp-sqlserver

undefined

az sql server ad-only-auth enable
--resource-group database-rg
--name myapp-sqlserver

undefined

Create Databases

创建数据库

bash

undefined

bash

undefined

Create General Purpose database

az sql db create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-db
--edition GeneralPurpose
--compute-model Serverless
--auto-pause-delay 60
--min-capacity 0.5
--max-size 32GB
--backup-storage-redundancy Geo
--zone-redundant false

Create Business Critical database for production

az sql db create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-prod-db
--edition BusinessCritical
--service-objective BC_Gen5_4
--max-size 256GB
--backup-storage-redundancy Geo
--zone-redundant true
--read-scale Enabled

Create Hyperscale database for large workloads

az sql db create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-analytics-db
--edition Hyperscale
--service-objective HS_Gen5_4
--ha-replicas 2

List databases on server

az sql db list
--resource-group database-rg
--server myapp-sqlserver
--output table

undefined

az sql db list
--resource-group database-rg
--server myapp-sqlserver
--output table

undefined

Elastic Pools

弹性池

bash

undefined

bash

undefined

Create elastic pool

az sql elastic-pool create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-pool
--edition GeneralPurpose
--capacity 4
--db-max-capacity 2
--db-min-capacity 0.25
--max-size 256GB
--zone-redundant false

Move database into elastic pool

az sql db update
--resource-group database-rg
--server myapp-sqlserver
--name myapp-db
--elastic-pool myapp-pool

Monitor elastic pool usage

az sql elastic-pool list-dbs
--resource-group database-rg
--server myapp-sqlserver
--name myapp-pool
--output table

undefined

az sql elastic-pool list-dbs
--resource-group database-rg
--server myapp-sqlserver
--name myapp-pool
--output table

undefined

Firewall Rules and Network Security

防火墙规则与网络安全

bash

undefined

bash

undefined

Allow Azure services

az sql server firewall-rule create
--resource-group database-rg
--server myapp-sqlserver
--name AllowAzureServices
--start-ip-address 0.0.0.0
--end-ip-address 0.0.0.0

Allow specific IP range (office network)

az sql server firewall-rule create
--resource-group database-rg
--server myapp-sqlserver
--name AllowOffice
--start-ip-address 203.0.113.0
--end-ip-address 203.0.113.255

Allow your current client IP

az sql server firewall-rule create
--resource-group database-rg
--server myapp-sqlserver
--name AllowMyIP
--start-ip-address "$(curl -s ifconfig.me)"
--end-ip-address "$(curl -s ifconfig.me)"

Create VNet rule for subnet access

az sql server vnet-rule create
--resource-group database-rg
--server myapp-sqlserver
--name AllowAppSubnet
--vnet-name spoke-prod-vnet
--subnet app-subnet

List firewall rules

az sql server firewall-rule list
--resource-group database-rg
--server myapp-sqlserver
--output table

Remove a firewall rule

az sql server firewall-rule delete
--resource-group database-rg
--server myapp-sqlserver
--name AllowMyIP

undefined

az sql server firewall-rule delete
--resource-group database-rg
--server myapp-sqlserver
--name AllowMyIP

undefined

Geo-Replication and Failover

异地复制与故障转移

bash

undefined

bash

undefined

Create failover group with secondary server

az sql server create
--resource-group database-rg
--name myapp-sqlserver-secondary
--location westus
--admin-user sqladmin
--admin-password 'S3cur3P@ssw0rd!'

az sql failover-group create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-failover-group
--partner-server myapp-sqlserver-secondary
--partner-resource-group database-rg
--failover-policy Automatic
--grace-period 1
--add-db myapp-prod-db

az sql server create
--resource-group database-rg
--name myapp-sqlserver-secondary
--location westus
--admin-user sqladmin
--admin-password 'S3cur3P@ssw0rd!'

az sql failover-group create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-failover-group
--partner-server myapp-sqlserver-secondary
--partner-resource-group database-rg
--failover-policy Automatic
--grace-period 1
--add-db myapp-prod-db

Check failover group status

az sql failover-group show
--resource-group database-rg
--server myapp-sqlserver
--name myapp-failover-group
--output table

Manual failover (for testing or planned maintenance)

az sql failover-group set-primary
--resource-group database-rg
--server myapp-sqlserver-secondary
--name myapp-failover-group

Create active geo-replication (without failover group)

az sql db replica create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-db
--partner-server myapp-sqlserver-secondary
--partner-resource-group database-rg

undefined

az sql db replica create
--resource-group database-rg
--server myapp-sqlserver
--name myapp-db
--partner-server myapp-sqlserver-secondary
--partner-resource-group database-rg

undefined

Backup and Restore

备份与恢复

bash

undefined

bash

undefined

Configure short-term retention (1-35 days)

az sql db str-policy set
--resource-group database-rg
--server myapp-sqlserver
--name myapp-prod-db
--retention-days 14
--diffbackup-hours 12

Configure long-term retention

az sql db ltr-policy set
--resource-group database-rg
--server myapp-sqlserver
--name myapp-prod-db
--weekly-retention P4W
--monthly-retention P12M
--yearly-retention P5Y
--week-of-year 1

Restore database to a point in time

az sql db restore
--resource-group database-rg
--server myapp-sqlserver
--name myapp-db-restored
--dest-name myapp-db-restored
--time "2026-03-23T10:00:00Z"

Restore from long-term backup

az sql db ltr-backup list
--resource-group database-rg
--server myapp-sqlserver
--database myapp-prod-db
--output table

Export database to bacpac

az sql db export
--resource-group database-rg
--server myapp-sqlserver
--name myapp-db
--admin-user sqladmin
--admin-password 'S3cur3P@ssw0rd!'
--storage-key-type StorageAccessKey
--storage-key "{storage-account-key}"
--storage-uri "https://mystorageacct.blob.core.windows.net/backups/myapp-db.bacpac"

undefined

az sql db export
--resource-group database-rg
--server myapp-sqlserver
--name myapp-db
--admin-user sqladmin
--admin-password 'S3cur3P@ssw0rd!'
--storage-key-type StorageAccessKey
--storage-key "{storage-account-key}"
--storage-uri "https://mystorageacct.blob.core.windows.net/backups/myapp-db.bacpac"

undefined

Performance Tuning

性能调优

bash

undefined

bash

undefined

Enable automatic tuning

az sql db update
--resource-group database-rg
--server myapp-sqlserver
--name myapp-prod-db
--set tags.autoTuning=enabled

Check database performance recommendations

az sql db advisor list
--resource-group database-rg
--server myapp-sqlserver
--database myapp-prod-db
--output table

Scale database tier

az sql db update
--resource-group database-rg
--server myapp-sqlserver
--name myapp-prod-db
--service-objective BC_Gen5_8

Enable Query Store (via SQL)

sqlcmd -S myapp-sqlserver.database.windows.net -d myapp-prod-db -Q "ALTER DATABASE [myapp-prod-db] SET QUERY_STORE = ON"

View DTU/vCore usage metrics

az monitor metrics list
--resource "/subscriptions/{sub}/resourceGroups/database-rg/providers/Microsoft.Sql/servers/myapp-sqlserver/databases/myapp-prod-db"
--metric "cpu_percent" "dtu_consumption_percent" "storage_percent"
--interval PT1H
--output table

undefined

az monitor metrics list
--resource "/subscriptions/{sub}/resourceGroups/database-rg/providers/Microsoft.Sql/servers/myapp-sqlserver/databases/myapp-prod-db"
--metric "cpu_percent" "dtu_consumption_percent" "storage_percent"
--interval PT1H
--output table

undefined

Security Hardening

安全加固

bash

undefined

bash

undefined

Enable Advanced Threat Protection

az sql db threat-policy update
--resource-group database-rg
--server myapp-sqlserver
--name myapp-prod-db
--state Enabled
--email-addresses security@example.com
--email-account-admins true

Enable auditing to storage

az sql server audit-policy update
--resource-group database-rg
--name myapp-sqlserver
--state Enabled
--storage-account mystorageacct
--retention-days 90

Enable auditing to Log Analytics

az sql server audit-policy update
--resource-group database-rg
--name myapp-sqlserver
--state Enabled
--lats Enabled
--lawri "/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.OperationalInsights/workspaces/{workspace}"

Enable Transparent Data Encryption (enabled by default)

az sql db tde set
--resource-group database-rg
--server myapp-sqlserver
--database myapp-prod-db
--status Enabled

Enable vulnerability assessment

az sql vm update
--resource-group database-rg
--name myapp-sqlserver

undefined

az sql vm update
--resource-group database-rg
--name myapp-sqlserver

undefined

Cosmos DB

bash

undefined

bash

undefined

Create Cosmos DB account with SQL API

az cosmosdb create
--resource-group database-rg
--name myapp-cosmos
--default-consistency-level Session
--locations regionName=eastus failoverPriority=0 isZoneRedundant=true
--locations regionName=westus failoverPriority=1 isZoneRedundant=false
--enable-automatic-failover true
--enable-multiple-write-locations false

Create database

az cosmosdb sql database create
--resource-group database-rg
--account-name myapp-cosmos
--name myappdb
--throughput 400

Create container with partition key

az cosmosdb sql container create
--resource-group database-rg
--account-name myapp-cosmos
--database-name myappdb
--name orders
--partition-key-path "/customerId"
--throughput 400
--idx @indexing-policy.json

Enable autoscale throughput

az cosmosdb sql container throughput update
--resource-group database-rg
--account-name myapp-cosmos
--database-name myappdb
--name orders
--max-throughput 4000

undefined

az cosmosdb sql container throughput update
--resource-group database-rg
--account-name myapp-cosmos
--database-name myappdb
--name orders
--max-throughput 4000

undefined

Terraform Configuration

Terraform配置

hcl

resource "azurerm_mssql_server" "main" {
  name                         = "myapp-sqlserver"
  resource_group_name          = azurerm_resource_group.main.name
  location                     = azurerm_resource_group.main.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password = var.sql_admin_password
  minimum_tls_version          = "1.2"
  public_network_access_enabled = false

  azuread_administrator {
    login_username = "SQL Admins"
    object_id      = var.sql_admin_aad_group_id
  }

  tags = var.tags
}

resource "azurerm_mssql_database" "main" {
  name         = "myapp-prod-db"
  server_id    = azurerm_mssql_server.main.id
  collation    = "SQL_Latin1_General_CP1_CI_AS"
  license_type = "LicenseIncluded"
  sku_name     = "BC_Gen5_4"
  max_size_gb  = 256
  zone_redundant = true
  read_scale     = true

  short_term_retention_policy {
    retention_days           = 14
    backup_interval_in_hours = 12
  }

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

  threat_detection_policy {
    state                      = "Enabled"
    email_addresses            = ["security@example.com"]
    email_account_admins       = "Enabled"
    retention_days             = 90
    storage_endpoint           = azurerm_storage_account.audit.primary_blob_endpoint
    storage_account_access_key = azurerm_storage_account.audit.primary_access_key
  }

  tags = var.tags
}

resource "azurerm_mssql_failover_group" "main" {
  name      = "myapp-failover-group"
  server_id = azurerm_mssql_server.main.id
  databases = [azurerm_mssql_database.main.id]

  partner_server {
    id = azurerm_mssql_server.secondary.id
  }

  read_write_endpoint_failover_policy {
    mode          = "Automatic"
    grace_minutes = 60
  }

  tags = var.tags
}

resource "azurerm_private_endpoint" "sql" {
  name                = "sql-private-endpoint"
  location            = azurerm_resource_group.main.location
  resource_group_name = azurerm_resource_group.main.name
  subnet_id           = azurerm_subnet.data.id

  private_service_connection {
    name                           = "sql-connection"
    private_connection_resource_id = azurerm_mssql_server.main.id
    subresource_names              = ["sqlServer"]
    is_manual_connection           = false
  }
}

hcl

resource "azurerm_mssql_server" "main" {
  name                         = "myapp-sqlserver"
  resource_group_name          = azurerm_resource_group.main.name
  location                     = azurerm_resource_group.main.location
  version                      = "12.0"
  administrator_login          = "sqladmin"
  administrator_login_password = var.sql_admin_password
  minimum_tls_version          = "1.2"
  public_network_access_enabled = false

  azuread_administrator {
    login_username = "SQL Admins"
    object_id      = var.sql_admin_aad_group_id
  }

  tags = var.tags
}

resource "azurerm_mssql_database" "main" {
  name         = "myapp-prod-db"
  server_id    = azurerm_mssql_server.main.id
  collation    = "SQL_Latin1_General_CP1_CI_AS"
  license_type = "LicenseIncluded"
  sku_name     = "BC_Gen5_4"
  max_size_gb  = 256
  zone_redundant = true
  read_scale     = true

  short_term_retention_policy {
    retention_days           = 14
    backup_interval_in_hours = 12
  }

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

  threat_detection_policy {
    state                      = "Enabled"
    email_addresses            = ["security@example.com"]
    email_account_admins       = "Enabled"
    retention_days             = 90
    storage_endpoint           = azurerm_storage_account.audit.primary_blob_endpoint
    storage_account_access_key = azurerm_storage_account.audit.primary_access_key
  }

  tags = var.tags
}

resource "azurerm_mssql_failover_group" "main" {
  name      = "myapp-failover-group"
  server_id = azurerm_mssql_server.main.id
  databases = [azurerm_mssql_database.main.id]

  partner_server {
    id = azurerm_mssql_server.secondary.id
  }

  read_write_endpoint_failover_policy {
    mode          = "Automatic"
    grace_minutes = 60
  }

  tags = var.tags
}

resource "azurerm_private_endpoint" "sql" {
  name                = "sql-private-endpoint"
  location            = azurerm_resource_group.main.location
  resource_group_name = azurerm_resource_group.main.name
  subnet_id           = azurerm_subnet.data.id

  private_service_connection {
    name                           = "sql-connection"
    private_connection_resource_id = azurerm_mssql_server.main.id
    subresource_names              = ["sqlServer"]
    is_manual_connection           = false
  }
}

Troubleshooting

故障排查

Symptom	Cause	Fix
Cannot connect to SQL server	Firewall rule missing or public access disabled	Add client IP with `az sql server firewall-rule create` or use private endpoint
Login failed for user	Incorrect credentials or Azure AD not configured	Verify admin credentials; enable Azure AD auth on the server
Database DTU at 100%	Under-provisioned tier or inefficient queries	Scale up service objective; review Query Store for expensive queries
Geo-replication lag is high	Large transaction volumes or network latency	Monitor with `sys.dm_geo_replication_link_status` ; consider Hyperscale
Point-in-time restore fails	Requested time is outside retention window	Check retention policy; use long-term backups for older data
Elastic pool running out of eDTUs	Too many active databases in pool	Increase pool capacity or move heavy databases to dedicated tier
TDE key rotation failure	Key Vault access policy missing	Grant SQL server managed identity GET, WRAP, UNWRAP permissions
Connection timeout from app	Network path blocked or DNS issue	Use `az network watcher test-connectivity` ; verify private DNS resolution

症状	原因	解决方法
无法连接到SQL服务器	缺少防火墙规则或公共访问已禁用	使用 `az sql server firewall-rule create` 添加客户端IP，或使用专用终结点
用户登录失败	凭据错误或未配置Azure AD	验证管理员凭据；在服务器上启用Azure AD身份验证
数据库DTU使用率达100%	层配置不足或查询效率低下	升级服务目标；查看Query Store中的高成本查询
异地复制延迟过高	事务量过大或网络延迟	使用 `sys.dm_geo_replication_link_status` 监控；考虑使用Hyperscale层
时点恢复失败	请求时间超出保留窗口	检查保留策略；对旧数据使用长期备份
弹性池eDTU耗尽	池中的活跃数据库过多	增加池容量，或将高负载数据库移至专用层
TDE密钥轮换失败	缺少Key Vault访问策略	授予SQL服务器托管标识GET、WRAP、UNWRAP权限
应用连接超时	网络路径被阻断或DNS问题	使用 `az network watcher test-connectivity` 测试；验证专用DNS解析

azure-sql

Original

Translation

Azure SQL

Azure SQL

When to Use

适用场景

Prerequisites

前提条件

Install Azure CLI

Install Azure CLI

Login and set subscription

Login and set subscription

Create resource group

Create resource group

SQL Server and Database Creation

SQL服务器与数据库创建

Create SQL Server

创建SQL服务器

Create logical SQL server

Create logical SQL server

Enable Azure AD authentication

Enable Azure AD authentication

Enable Azure AD only authentication (disable SQL auth)

Enable Azure AD only authentication (disable SQL auth)

Create Databases

创建数据库

Create General Purpose database

Create General Purpose database

Create Business Critical database for production

Create Business Critical database for production

Create Hyperscale database for large workloads

Create Hyperscale database for large workloads

List databases on server

List databases on server

Elastic Pools

弹性池

Create elastic pool

Create elastic pool

Move database into elastic pool

Move database into elastic pool

Monitor elastic pool usage

Monitor elastic pool usage

Firewall Rules and Network Security

防火墙规则与网络安全

Allow Azure services

Allow Azure services

Allow specific IP range (office network)

Allow specific IP range (office network)

Allow your current client IP

Allow your current client IP

Create VNet rule for subnet access

Create VNet rule for subnet access

List firewall rules

List firewall rules

Remove a firewall rule

Remove a firewall rule

Geo-Replication and Failover

异地复制与故障转移

Create failover group with secondary server

Create failover group with secondary server

Check failover group status

Check failover group status

Manual failover (for testing or planned maintenance)

Manual failover (for testing or planned maintenance)

Create active geo-replication (without failover group)

Create active geo-replication (without failover group)

Backup and Restore

备份与恢复

Configure short-term retention (1-35 days)

Configure short-term retention (1-35 days)

Configure long-term retention

Configure long-term retention

Restore database to a point in time

Restore database to a point in time

Restore from long-term backup

Restore from long-term backup

Export database to bacpac

Export database to bacpac

Performance Tuning