Azure CLI SQL Commands

This documentation provides a comprehensive guide to using the Azure CLI for managing Azure SQL Database resources.

Introduction to Azure SQL Database CLI Management

The Azure Command-Line Interface (CLI) offers a powerful and efficient way to manage your Azure SQL Database instances and related resources directly from your terminal. This section will guide you through the most common operations, from creating databases to managing firewalls and performance levels.

Prerequisites

Core SQL Commands

The primary command group for Azure SQL Database is az sql.

Managing SQL Servers

SQL servers are logical containers for your databases. You can create, manage, and delete them using the az sql server command group.

Creating a SQL Server

az sql server create --name <server-name> --resource-group <resource-group-name> --location <location> --admin-user <admin-login> --admin-password <admin-password>

This command creates a new SQL server. Remember to choose a strong password for the administrator account.

Listing SQL Servers

az sql server list --resource-group <resource-group-name>

Lists all SQL servers within a specified resource group.

Deleting a SQL Server

az sql server delete --name <server-name> --resource-group <resource-group-name>

Deletes a SQL server and all its contained databases and resources. This operation cannot be undone.

Managing Databases

Once you have a SQL server, you can create and manage databases within it.

Creating a Database

az sql db create --resource-group <resource-group-name> --server <server-name> --name <database-name> --edition Basic --capacity 5 --zone-redundant false

Creates a new SQL database. You can specify the edition (e.g., Basic, Standard, Premium, GeneralPurpose, BusinessCritical) and capacity (DTUs or vCores).

Listing Databases

az sql db list --resource-group <resource-group-name> --server <server-name>

Lists all databases on a specific SQL server.

Deleting a Database

az sql db delete --resource-group <resource-group-name> --server <server-name> --name <database-name>

Deletes a SQL database.

Firewall Rules

You need to configure firewall rules to allow access to your SQL server from specific IP addresses or ranges.

Creating a Firewall Rule

az sql server firewall-rule create --resource-group <resource-group-name> --server <server-name> --name <rule-name> --start-ip-address <start-ip> --end-ip-address <end-ip>

Allows access from the specified IP address range.

Allowing Azure Services Access

az sql server firewall-rule create --resource-group <resource-group-name> --server <server-name> --name AllowAllWindowsAzureIps --start-ip-address 0.0.0.0 --end-ip-address 0.0.0.0

This rule allows all Azure services to access your SQL server. Use with caution.

Performance Tiers

Adjusting performance tiers (DTUs or vCores) is crucial for balancing cost and performance.

Updating Database Service Objective

az sql db update --resource-group <resource-group-name> --server <server-name> --name <database-name> --edition Standard --capacity 20

Updates the edition and capacity (DTUs) of a database. For vCore-based models, use --tier GeneralPurpose --family Gen5 --capacity 2 for example.

Example Workflow: Deploying a Web App with Azure SQL Database

  1. Create a resource group:
    az group create --name myResourceGroup --location eastus
  2. Create an Azure SQL Server:
    az sql server create --name mysqldemoserver --resource-group myResourceGroup --location eastus --admin-user sqladmin --admin-password YourStrongPassword123!
  3. Create a firewall rule to allow access from your current IP:
    az sql server firewall-rule create --resource-group myResourceGroup --server mysqldemoserver --name AllowMyIp --start-ip-address <YOUR_PUBLIC_IP> --end-ip-address <YOUR_PUBLIC_IP>

    Replace <YOUR_PUBLIC_IP> with your actual public IP address.

  4. Create a SQL Database:
    az sql db create --resource-group myResourceGroup --server mysqldemoserver --name mySampleDatabase --edition Standard --capacity 10
  5. Deploy an App Service and configure its connection string to point to mySampleDatabase. (This step typically involves further CLI commands or Azure portal configuration.)
For advanced scenarios, consider using az sql mi for Managed Instance management, az sql elastic-pool for elastic pools, and exploring options for advanced threat protection and auditing.

Further Reading