Azure CLI for SQL

Automating Azure SQL with the Azure CLI

Introduction: Why Infrastructure as Code Matters for Databases

In the modern era of cloud computing, managing database infrastructure through a web portal—often called "ClickOps"—is becoming an obsolete practice. When you manually configure a SQL database through the Azure Portal, you create an environment that is difficult to replicate, document, or audit. This is where Infrastructure as Code (IaC) comes into play. By using the Azure Command-Line Interface (CLI), you can define, deploy, and manage your SQL resources using scripts that are version-controlled, repeatable, and transparent.

The Azure CLI provides a powerful, cross-platform toolset that allows you to interact with Azure Resource Manager (ARM) directly from your terminal. Whether you are automating the deployment of a development environment, scaling a production database based on demand, or managing security configurations across hundreds of servers, the Azure CLI is the industry-standard way to bridge the gap between human intent and cloud reality. This lesson will guide you through the process of mastering the Azure CLI for SQL, moving from basic resource creation to advanced automation workflows.

Setting the Stage: Prerequisites and Environment Setup

Before you can run a single command, you must ensure your environment is prepared. The Azure CLI is a modular tool, and while it comes with a vast set of commands, we will focus specifically on the az sql command group.

Installation and Authentication

If you have not already installed the Azure CLI, you should download the installer from the official Microsoft documentation site for your specific operating system (Windows, macOS, or Linux). Once installed, the first step in any automation session is authentication. You can authenticate by running az login, which will open a browser window for you to sign in with your Azure credentials.

Tip: If you are working in a non-interactive environment, such as a CI/CD pipeline, avoid using az login with a user account. Instead, use a Service Principal with a client secret or certificate. This allows the automation to run without human intervention and adheres to the principle of least privilege.

Once authenticated, it is good practice to set your default subscription if you work with multiple accounts. Use the command az account set --subscription "Your-Subscription-Name" to ensure that your subsequent commands are executed in the correct context.

Core Concepts: Resources and Logical Servers

To understand Azure SQL automation, you must first understand the hierarchy of resources. In the Azure SQL world, you rarely deploy a "database" in isolation. Instead, you deploy a Logical SQL Server, which acts as a container for your databases, and then you deploy the Database itself inside that server.

Creating a Resource Group

Everything in Azure lives inside a Resource Group. Think of this as a folder for your cloud assets. If you are starting a new project, you should create a dedicated resource group to keep your database environment isolated from other services.

# Create a resource group
az group create --name MyDatabaseRG --location eastus

This command creates a container in the "East US" region. Keeping your resources in the same region as your application servers is a best practice to minimize latency and data transfer costs.

Provisioning the Logical SQL Server

The logical server is the administrative boundary. It holds the firewall rules, login credentials, and the databases themselves. When you create this, you must define an administrator username and password.

# Create a logical SQL server
az sql server create \
    --name my-unique-sql-server-name \
    --resource-group MyDatabaseRG \
    --location eastus \
    --admin-user sqladmin \
    --admin-password 'StrongPassword123!'

Warning: Never hardcode passwords in your scripts. If you are checking these scripts into version control, you should use environment variables or a secret management service like Azure Key Vault to inject the password at runtime.

Managing Databases: Lifecycle Operations

Once the server is ready, you can start creating databases. The Azure CLI allows you to specify the edition, the service objective (performance tier), and even the collation settings.

Creating a Basic Database

To create a standard database with minimal configuration, use the following command:

# Create a basic database
az sql db create \
    --resource-group MyDatabaseRG \
    --server my-unique-sql-server-name \
    --name MyInventoryDB \
    --service-objective S0

The --service-objective flag is critical here. It determines the performance level of your database. In the "Standard" tier, "S0" is the smallest, while "S12" is the largest. If you are using the vCore-based purchasing model, you would use the --capacity flag instead.

Scaling Databases Dynamically

One of the greatest advantages of using the Azure CLI is the ability to scale resources on the fly. Suppose your application experiences a seasonal spike in traffic. You can automate the scaling process without requiring manual intervention.

# Scale the database to a higher performance tier
az sql db update \
    --resource-group MyDatabaseRG \
    --server my-unique-sql-server-name \
    --name MyInventoryDB \
    --service-objective S2

This command updates the database configuration instantly. This is extremely useful for batch jobs that require high throughput during specific hours of the day.

Security: Firewall Rules and Identity

Database security is non-negotiable. By default, a new Azure SQL server is locked down. You must explicitly allow traffic to reach your server by configuring firewall rules.

Configuring Firewall Rules

You can allow specific IP addresses or ranges to access your SQL server. This is essential for allowing your application servers or your local development machine to connect.

# Allow a specific IP address
az sql server firewall-rule create \
    --resource-group MyDatabaseRG \
    --server my-unique-sql-server-name \
    --name AllowMyOfficeIP \
    --start-ip-address 1.2.3.4 \
    --end-ip-address 1.2.3.4

Callout: Principle of Least Privilege When configuring firewall rules, always aim for the narrowest scope possible. Never use 0.0.0.0 to 255.255.255.255 unless the database is intended to be public, which is rarely the case for production systems. Use specific ranges or, better yet, integrate with Azure Private Link to keep your traffic on the Microsoft backbone network.

Managing Admin Access

Beyond the server admin, you should manage Active Directory (AD) administrators. Using AD authentication is more secure than SQL authentication because it leverages your existing identity management policies, including Multi-Factor Authentication (MFA).

# Set an Azure AD administrator
az sql server ad-admin create \
    --resource-group MyDatabaseRG \
    --server-name my-unique-sql-server-name \
    --display-name "DBA Team" \
    --object-id <your-azure-ad-object-id>

Advanced Automation: Scripting and Loops

The true power of the Azure CLI emerges when you combine it with shell scripting. If you need to deploy a set of databases for a multi-tenant application, you don't need to run commands individually; you can use a loop.

Bulk Deployment Example

Imagine you need to deploy a database for every customer in a list. You can write a simple bash script to iterate through an array and provision resources.

# Define a list of customers
customers=("CustomerA" "CustomerB" "CustomerC")

for name in "${customers[@]}"
do
    echo "Creating database for $name..."
    az sql db create \
        --resource-group MyDatabaseRG \
        --server my-unique-sql-server-name \
        --name "db_$name" \
        --service-objective S0
done

This script demonstrates how to turn a manual task that would take 30 minutes into a process that takes seconds. This is the essence of automation: reducing human error and increasing consistency across environments.

Comparing Purchasing Models: DTU vs. vCore

When automating database deployment, you must decide which purchasing model to use. The Azure CLI handles these slightly differently.

Note: The vCore model is generally recommended for modern applications because it offers more flexibility. It allows you to use the Azure Hybrid Benefit, which can save you significant costs if you already have SQL Server licenses on-premises.

Best Practices for SQL Automation

To maintain a healthy database lifecycle, you should adhere to several industry-standard practices when using the Azure CLI.

1. Use Resource Tags: Always tag your resources. Tags allow you to track costs, manage ownership, and automate cleanup. For example, add --tags Environment=Prod Project=Inventory to your creation commands.
2. Infrastructure as Code (IaC) Versioning: Store your shell scripts in a Git repository. This creates an audit trail of who changed what, and when.
3. Use Parameters: Never hardcode values like server names or resource groups. Use shell variables or parameter files to make your scripts portable across different environments (Dev, Test, Prod).
4. Implement Error Handling: Always check the exit status of your CLI commands. Use if [ $? -eq 0 ]; then ... to ensure that if a command fails, the script stops rather than proceeding with a broken configuration.
5. Regular Audits: Use the az sql server list and az sql db list commands to periodically audit your environment. You can pipe the output to a JSON file and compare it against your expected state.

Common Pitfalls and How to Avoid Them

Even experienced professionals encounter issues when automating cloud infrastructure. Being aware of these pitfalls can save you hours of debugging.

1. Naming Collisions

SQL Server names must be globally unique within Azure because they form the base of the FQDN (e.g., my-server.database.windows.net). If your script fails with a "Name already in use" error, it is likely because someone else in the global Azure ecosystem has already claimed that name.

• Solution: Use a naming convention that includes a random suffix or a specific project identifier, such as sql-proj-prod-001.

2. Ignoring Resource Limits

Azure imposes quotas on the number of servers and databases you can create in a single subscription. If you are running a massive automation script that creates hundreds of databases, you may hit a subscription limit.

• Solution: Check your subscription quotas using az quota list before running large-scale deployments.

3. Connection Timeouts

Sometimes, an Azure command might appear to hang. This is often due to the time it takes for a resource to provision.

• Solution: Do not manually kill the process unless you are sure it has failed. Use the --no-wait flag if you want to trigger the deployment and move on to the next task, though be aware that the next task might fail if it depends on the previous one being finished.

4. Over-Permissioning

Running CLI commands with an account that has "Owner" rights is dangerous. If a script is compromised, the attacker has full control over your subscription.

• Solution: Create a custom Role-Based Access Control (RBAC) role that only allows the specific az sql actions needed for the task at hand.

Practical Example: A Complete Deployment Workflow

To wrap up, let’s look at a complete, cohesive workflow for deploying a database, configuring a firewall, and setting a tag. This script is designed to be "idempotent," meaning it can be run multiple times without causing errors.

#!/bin/bash

# Configuration Variables
RG="MyDatabaseRG"
SERVER="my-unique-sql-server-01"
DB="AppDatabase"
LOCATION="eastus"

# 1. Create Resource Group if it doesn't exist
az group create --name $RG --location $LOCATION --output none

# 2. Check if server exists, create if not
if ! az sql server show --name $SERVER --resource-group $RG > /dev/null 2>&1; then
    echo "Creating SQL Server..."
    az sql server create --name $SERVER --resource-group $RG --location $LOCATION --admin-user sqladmin --admin-password 'ComplexPassword123!' --output none
fi

# 3. Create database
if ! az sql db show --name $DB --server $SERVER --resource-group $RG > /dev/null 2>&1; then
    echo "Creating Database..."
    az sql db create --resource-group $RG --server $SERVER --name $DB --service-objective S0 --tags Environment=Production --output none
else
    echo "Database already exists."
fi

echo "Deployment complete."

This script is highly practical. It checks for the existence of the resource before trying to create it, which prevents the common "Resource already exists" error. This is a foundational technique in DevOps and Infrastructure as Code.

Frequently Asked Questions (FAQ)

Q: Can I use the Azure CLI to restore a database from a backup? A: Yes. You can use the az sql db restore command. You will need to specify the source database and a point-in-time for the restore. This is a common requirement for disaster recovery automation.

Q: Is the Azure CLI faster than using Terraform or Bicep? A: The Azure CLI is imperative, meaning you tell the system how to do things step-by-step. Tools like Terraform and Bicep are declarative, meaning you define what you want the end state to look like. For simple tasks, CLI is faster. For complex infrastructure, declarative tools are generally preferred.

Q: Can I run Azure CLI commands from within a SQL query? A: No, the Azure CLI runs on your machine or in a CI/CD agent, not inside the SQL engine. However, you can use the sqlcmd utility or Invoke-Sqlcmd in PowerShell to run SQL queries against your database immediately after the CLI creates it.

Q: How do I handle long-running operations? A: Most Azure CLI commands have a --no-wait flag. You can use this to start a long-running process and then use az resource wait to poll the status of the resource until it is ready.

Key Takeaways

1. Automation is Essential: Moving away from manual portal configurations to CLI-driven scripts ensures your infrastructure is repeatable, documentable, and less prone to human error.
2. Hierarchy Matters: Remember that Azure SQL is hierarchical; you must have a logical server container before you can instantiate individual databases.
3. Security First: Never hardcode credentials in scripts. Always use secure injection methods like environment variables or Key Vault, and adhere to the principle of least privilege for firewall and access management.
4. Idempotency is Key: Write your scripts to check if a resource already exists before trying to create it. This makes your automation robust and safe to run repeatedly.
5. Scaling is Dynamic: The ability to change performance tiers via the command line allows you to align infrastructure costs with actual application demand, providing significant budget efficiency.
6. Tagging is Mandatory: Always tag your resources. It is the only way to effectively manage, categorize, and track the costs of your cloud assets in a professional environment.
7. Choose the Right Model: Understand the difference between DTU and vCore models to ensure your database performance and cost structure are optimized for your specific application requirements.

By mastering these concepts, you transition from being a passive user of cloud services to an active architect of your infrastructure. The Azure CLI is a tool that rewards curiosity and practice; start with small scripts, refine them, and gradually build toward fully automated deployment pipelines.