Creating Azure SQL Database - Step-by-Step CLI Walkthrough

Introduction

Creating an Azure SQL Database lets you have a managed database in the cloud. This solves the problem of setting up and maintaining your own database server, so you can focus on your app data.

When you want a reliable database without managing hardware or software updates.

When you need to quickly create a database for a web or mobile app.

When you want automatic backups and security features handled by Azure.

When you want to scale your database easily as your app grows.

When you want to connect your app to a cloud database with minimal setup.

Config File - azuredeploy.json

azuredeploy.json

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "sqlServerName": {
      "type": "string",
      "defaultValue": "my-sql-server-1234",
      "metadata": {
        "description": "Name of the SQL server"
      }
    },
    "sqlAdminUsername": {
      "type": "string",
      "defaultValue": "sqladminuser",
      "metadata": {
        "description": "SQL admin username"
      }
    },
    "sqlAdminPassword": {
      "type": "securestring",
      "metadata": {
        "description": "SQL admin password"
      }
    },
    "databaseName": {
      "type": "string",
      "defaultValue": "mydatabase",
      "metadata": {
        "description": "Name of the SQL database"
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "eastus",
      "metadata": {
        "description": "Location for all resources"
      }
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "[parameters('sqlServerName')]",
      "location": "[parameters('location')]",
      "properties": {
        "administratorLogin": "[parameters('sqlAdminUsername')]",
        "administratorLoginPassword": "[parameters('sqlAdminPassword')]"
      },
      "resources": [
        {
          "type": "databases",
          "apiVersion": "2021-02-01-preview",
          "name": "[parameters('databaseName')]",
          "location": "[parameters('location')]",
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', parameters('sqlServerName'))]"
          ],
          "properties": {
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "maxSizeBytes": "2147483648",
            "sampleName": "AdventureWorksLT",
            "sku": {
              "name": "Basic",
              "tier": "Basic"
            }
          }
        }
      ]
    }
  ]
}

This ARM template creates an Azure SQL Server and a SQL Database inside it.

parameters: Define names, admin user, password, database name, and location.

resources: Create the SQL server with admin credentials, then create the database with basic SKU and sample data.

Commands

Create a resource group to hold the SQL server and database. This groups related resources together.

Terminal

az group create --name myResourceGroup --location eastus

Expected Output

{

  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myResourceGroup",

  "location": "eastus",

  "managedBy": null,

  "name": "myResourceGroup",

  "properties": {

    "provisioningState": "Succeeded"

  },

  "tags": {},

  "type": "Microsoft.Resources/resourceGroups"

}

→

--name - Sets the resource group name

→

--location - Sets the Azure region for the group

Deploy the ARM template to create the SQL server and database in the resource group. The admin password is passed securely here.

Terminal

az deployment group create --resource-group myResourceGroup --template-file azuredeploy.json --parameters sqlAdminPassword=MyStrongP@ssw0rd!

Expected Output

{

  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myResourceGroup/providers/Microsoft.Resources/deployments/deployment1",

  "name": "deployment1",

  "properties": {

    "provisioningState": "Succeeded",

    "outputs": {}

  }

}

→

--resource-group - Specifies the target resource group

→

--template-file - Points to the ARM template file

→

--parameters - Passes parameters like admin password

Check the details of the created SQL database to confirm it exists and see its properties.

Terminal

az sql db show --resource-group myResourceGroup --server my-sql-server-1234 --name mydatabase

Expected Output

{

  "collation": "SQL_Latin1_General_CP1_CI_AS",

  "creationDate": "2024-06-01T12:00:00Z",

  "currentServiceObjectiveName": "Basic",

  "databaseId": "00000000-0000-0000-0000-000000000000",

  "edition": "Basic",

  "id": "/subscriptions/00000000-0000-0000-0000-000000000000/resourceGroups/myResourceGroup/providers/Microsoft.Sql/servers/my-sql-server-1234/databases/mydatabase",

  "location": "eastus",

  "maxSizeBytes": "2147483648",

  "name": "mydatabase",

  "resourceGroup": "myResourceGroup",

  "status": "Online",

  "type": "Microsoft.Sql/servers/databases"

}

→

--resource-group - Specifies the resource group

→

--server - Specifies the SQL server name

→

--name - Specifies the database name

Key Concept

If you remember nothing else from this pattern, remember: Azure SQL Database is created by deploying a server and database resource together, usually via an ARM template or CLI commands.

Common Mistakes

Using a weak or simple password for the SQL admin user

Azure requires strong passwords for security; weak passwords cause deployment failures.

Use a complex password with uppercase, lowercase, numbers, and symbols.

Not creating or specifying a resource group before deployment

Resources must belong to a resource group; deployment fails without it.

Create a resource group first using 'az group create' and specify it during deployment.

Trying to deploy the database without specifying the server name correctly

The database depends on the server; wrong server name causes errors or resource not found.

Ensure the server name matches exactly in parameters and commands.

Summary

Create a resource group to organize your Azure resources.

Deploy an ARM template that creates an Azure SQL Server and a database inside it.

Verify the database creation by checking its details with Azure CLI.