Siddhant Academy
How to Create a Single SQL Database: Mastering Azure Command-Line Interface (CLI)

How to Create a Single SQL Database: Mastering Azure Command-Line Interface (CLI)

Siddhant Bhattarai's photo
Siddhant Bhattarai
·

4 min read

In the ever-evolving landscape of cloud computing, the ability to swiftly and efficiently manage your database resources is crucial. Azure Command-Line Interface (Azure-CLI) provides a powerful toolset for database administration, allowing you to automate tasks with ease. This guide will walk you through the process of creating a single SQL database in Azure using Azure-CLI, a vital skill for developers and IT professionals looking to leverage Azure's scalable database services.

Why Azure SQL Database?

Azure SQL Database is a fully managed relational database service that offers scalability, high availability, and security for your data. It allows you to focus on optimizing your applications while Azure takes care of the underlying database management tasks. Whether you're building new applications or migrating existing ones, Azure SQL Database provides a reliable, cloud-based solution for your relational data.

Prerequisites

Before diving into the database creation process, ensure you meet the following prerequisites:

  • An active Azure account. If you don't have one, sign up for a free Azure account.

  • Azure-CLI installed on your machine. If you haven't installed it yet, follow the installation guide.

  • Basic familiarity with command-line tools and SQL database concepts.

Step 1: Log in to Azure

Open your terminal or command prompt and log in to your Azure account by running the following command:

az login

Follow the on-screen instructions to complete the authentication process.

Step 2: Set parameter values

The following values are used in subsequent commands to create the database and required resources. Server names need to be globally unique across all of Azure so the $RANDOM function is used to create the server name.

Change the location as appropriate for your environment. Replace 0.0.0.0 with the IP address range that matches your specific environment. Use the public IP address of the computer you're using to restrict access to the server to only your IP address.

# Define variables for resources
randomIdentifier=$((RANDOM*RANDOM))
location="East US"
resourceGroup="mystic-rg-$randomIdentifier"
server="mystic-azuresql-server-$randomIdentifier"
database="mystic_azure_sqldb$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
# Specify appropriate IP address values for your environment
# to limit access to the SQL Database server
startIp="0.0.0.0" # Update with your IP
endIp="0.0.0.0" # Update with your IP

echo "Using resource group $resourceGroup with login: $login, password: $password..."

For Windows:

# Set variables for your server and database
$resourceGroup = "myResourceGroup"
$location = "eastus"
$login = "azureuser"
$password = "Azure1234567!"
$server = "mysqlserver-$(Get-Random)"
$database = "mySampleDatabase"

# The ip address range that you want to allow to access your server
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"

# Show randomized variables
Write-host "Resource group name is" $resourceGroupName
Write-host "Server name is" $serverName

Step 3: Create a resource group

Create a resource group with the az group create command. An Azure resource group is a logical container into which Azure resources are deployed and managed. The following example creates a resource group named myResourceGroup in the eastus location:

# Create Resource Group
echo "Creating resource group $resourceGroup in $location..."
az group create --name $resourceGroup --location "$location"

Step 4: Create a server

Create a server with the az sql server create command.

# Create SQL Server
echo "Creating SQL server $server..."
az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password

Step 5: Configure a server-based firewall rule

Create a firewall rule with the az sql server firewall-rule create command.

# Configure Server Firewall Rule
echo "Configuring server firewall rule..."
az sql server firewall-rule create --resource-group $resourceGroup --server $server --name AllowYourIp --start-ip-address $startIp --end-ip-address $endIp

Step 6: Create a single database

Create a database with the az sql db create command in the serverless compute tier.

# Create SQL Database
echo "Creating SQL database $database..."
az sql db create --resource-group $resourceGroup --server $server --name $database --edition GeneralPurpose --compute-model Serverless --family Gen5 --capacity 2

echo "Database and related resources created successfully."

Step 7: Connect and Manage Your Database

With your database created, you can connect using various tools such as Azure Data Studio or SQL Server Management Studio (SSMS). Use the connection string provided in the Azure portal to establish a connection and start managing your database.

Cleaning Up

To avoid unnecessary charges, consider deleting resources you no longer need. Remove the resource group, and all contained resources, by executing:

az group delete --name $resourceGroup --yes --no-wait

Conclusion

Congratulations! You've successfully navigated the process of creating a single SQL database using Azure-CLI. This guide has equipped you with the knowledge to leverage Azure's powerful CLI for database management, allowing you to automate tasks and streamline your workflow. As you continue to explore Azure's capabilities, remember that Azure-CLI is your ally in efficiently managing cloud resources.