Close
AlgoliaLogoLight
Close full mode
logo

Manage Azure SQL Database with Azure CLI

Git RepositoryEdit on Github

Requirements

  • Create an Azure Account.
  • Set up Azure CLI.
  • Log in with Azure CLI.
  • Create a resource group.
  • If you haven't done these requirements, please refer to Azure CLI content for more information.

Create Azure SQL Database for Azure services

  • To use Azure SQL Database, we need to create a database server.
  • Add a firewall rule to allow Azure Service to connect to a database server.
  • Create a database.

Create Azure SQL Server

  • Command:
    az sql server create \
    --name <DATABASE_SERVER_NAME> \
    --resource-group <RESOURCE_GROUP_NAME> \
    --location <LOCATION> \
    --admin-user <ADMIN_USERNAME> \
    --admin-password <ADMIN_PASSWORD>
  • Name must be unique and we cannot use an existing server name in Azure cloud.
  • If your ADMIN_PASSWORD has special characters you need to wrap it with single quotes.
  • To list all available locations, run az account list-locations --output table.
  • To list all exiting servers, use az sql server list --output table.
  • Connect a server with <DATABASE_SERVER_NAME>.database.windows.net host name.
  • More details for Azure SQL Server

Example code to create Azure SQL Server instance:

  • Code:
    az sql server create \
    --name codesanook-example-db-server \
    --resource-group codesanook-example-resource-group \
    --location southeastasia \
    --admin-user codesanook-example-sa \
    --admin-password 'very-secured-password'
  • Note Do not forget to always generate a new strong password for admin password.

Allow Azure services and other Azure resources to connect to Azure SQL Server

  • Command:
    az sql server firewall-rule create \
    --resource-group <RESOURCE_GROUP_NAME> \
    --server <DATABASE_SERVER_NAME> \
    --name AllowAllWindowsAzureIps \
    --start-ip-address 0.0.0.0 \
    --end-ip-address 0.0.0.0
  • Example code to create a firewall rule that allows all Azure services:
    az sql server firewall-rule create \
    --server codesanok-example-db-server \
    --resource-group codesanook-example-resource-group \
    --name AllowAllWindowsAzureIps \
    --start-ip-address 0.0.0.0 \
    --end-ip-address 0.0.0.0
  • REF https://docs.azure.cn/zh-cn/cli/sql/server/firewall-rule?view=azure-cli-latest#az_sql_server_firewall_rule_create

Create Azure SQL Database

  • Command:
    az sql db create \
    --name <DATABASE_NAME> \
    --server <DATABASE_SERVER_NAME> \
    --resource-group <RESOURCE_GROUP_NAME> \
    --catalog-collation <COLLATION_OF_A_METADATA_CATALOG> \
    --collation <COLLATION_OF_A_DATABASE> \
    --edition <EDITION_OF_SKU> \
    --capacity <CAPACITY_OF_SKU> \
    --max-size <MAX_DATABASE_SIZE> \
    --backup-storage-redundancy <BACKUP_REDUNDANCY_VALUE>
  • To list all available database edition for a region, use az sql db list-editions --location <location-name> --output table.
  • For Southeast Asia location use southeastasia. To list all locations, use az account list-locations --output table.
  • Valid values of --backup-storage-redundancy are Local, Zone and Geo.
  • More details for Azure SQL database.

Example code to create a free Azure SQL Database

  • Code:
    az sql db create \
    --name codesanook-example-db \
    --server codesanook-example-db-server \
    --resource-group codesanook-example-resource-group \
    --catalog-collation SQL_Latin1_General_CP1_CI_AS \
    --collation Latin1_General_CI_AS \
    --edition Free \
    --capacity 5 \
    --max-size 32MB \
    --backup-storage-redundancy Zone
  • This will create a free Azure SQL database which has 32MB of database size and 5DTU.
  • To get more details of Free edition, run az sql db list-editions --location southeastasia --edition Free
  • Please note that subscription can have only one free database per region.
    • To provision another free database in same subscription, choose a different region.
    • To provision another free database in same region, use different subscription.

Example code to create the cheapest Azure SQL Database

  • Code:
    az sql db create \
    --name codesanook-example-db \
    --server codesanook-example-db-server \
    --resource-group codesanook-example-resource-group \
    --catalog-collation SQL_Latin1_General_CP1_CI_AS \
    --collation Thai_CI_AS \
    --edition Basic \
    --capacity 5 \
    --max-size 2GB \
    --backup-storage-redundancy Zone
  • This will create the cheapest Azure SQL database which costs $4.90 a month and has 2GB of database size.
  • Use Thai case insensitive and accent sensitive database collation.

Other useful commands

List all existing servers

  • Command:
    az sql server list \
    --resource-group <RESOURCE_GROUP_NAME> \
    --output table
  • Example code to list all existing databases on a specific server.
    az sql server list \
    --resource-group codesanook-example-resource-group \
    --output table

List all existing databases on a specific server

  • Command:
    az sql db list \
    --server <DATABASE_SERVER_NAME> \
    --resource-group <RESOURCE_GROUP_NAME> \
    --output table
  • Example code to list all existing servers in a specific resource group.
    az sql db list \
    --server codesanook-example-db-server \
    --resource-group codesanook-example-resource-group \
    --output table

Show details of a server

  • Command:
    az sql server show \
    --name <DATABASE_SERVER_NAME> \
    --resource-group <RESOURCE_GROUP_NAME>
  • Example:
    az sql server show \
    --name codesanook-example-db-server \
    --resource-group codesanook-example-resource-group

Delete a server

  • Command:
    az sql server delete \
    --name <DATABASE_SERVER_NAME> \
    --resource-group <RESOURCE_GROUP_NAME> \
    --yes
  • Example code to delete a database server:
    az sql server delete \
    --name codesanok-example-db-server \
    --resource-group codesanook-example-resource-group \
    --yes

Delete a database

  • Command:
    az sql db delete \
    --name <DATABASE_NAME> \
    --server <DATABASE_SERVER_NAME>
    --resource-group <RESOURCE_GROUP_NAME> \
    --yes
  • Example code to delete a database:
    az sql db delete \
    --name codesanok-example-db \
    --server codesanok-example-db-server \
    --resource-group codesanook-example-resource-group \
    --yes

Update an admin password

  • Command:
    az sql server update \
    --name <DATABASE_SERVER_NAME> \
    --admin-password <ADMIN_PASSWORD> \
    --resource-group <RESOURCE_GROUP_NAME>
  • Example code to update an admin password:
    az sql server update \
    --name codesanook-example-db-server \
    --admin-password 'very-secured-password' \
    --resource-group codesanook-example-resource-group
Loading comments...