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.

Practice

1. What is the main purpose of creating an Azure SQL Database?

easy

A. To create virtual machines for running applications

B. To store and manage data in the cloud without managing servers

C. To host websites directly without any database

D. To manage user identities and access control

Solution

1. Step 1: Understand Azure SQL Database purpose

   Azure SQL Database is a managed cloud database service that stores data without requiring server management.

2. Step 2: Compare options with service purpose

   Options B, C, and D describe other Azure services, not Azure SQL Database.

3. Final Answer:

   To store and manage data in the cloud without managing servers -> Option B

4. Quick Check:

   Azure SQL Database = Managed cloud data storage [OK]

Hint: Azure SQL Database is for cloud data storage, not VMs or websites [OK]

Common Mistakes:

• Confusing Azure SQL Database with virtual machines
• Thinking it hosts websites directly
• Mixing it up with identity management services

2. Which Azure CLI command correctly creates a new Azure SQL Database named mydb in server myserver and resource group mygroup?

easy

A. az sql db create --resource-group mygroup --server myserver --name mydb --service-objective S0

B. az sql create db --resource-group mygroup --server myserver --name mydb

C. az sql database create --group mygroup --server myserver --db-name mydb

D. az sql db new --resource-group mygroup --server myserver --database mydb

Solution

1. Step 1: Identify correct Azure CLI syntax

   The correct command to create an Azure SQL Database uses az sql db create with parameters --resource-group, --server, --name, and --service-objective.

2. Step 2: Check each option for syntax correctness

   az sql db create --resource-group mygroup --server myserver --name mydb --service-objective S0 matches the correct syntax. Options A, B, and D use incorrect command names or parameter names.

3. Final Answer:

   az sql db create --resource-group mygroup --server myserver --name mydb --service-objective S0 -> Option A

4. Quick Check:

   Correct Azure CLI create db command = az sql db create --resource-group mygroup --server myserver --name mydb --service-objective S0 [OK]

Hint: Use 'az sql db create' with resource group, server, and name [OK]

Common Mistakes:

• Using wrong command like 'az sql create db'
• Incorrect parameter names like '--group' instead of '--resource-group'
• Missing required parameters like '--service-objective'

3. Given this Azure CLI command:

az sql db create --resource-group mygroup --server myserver --name testdb --service-objective S1

What is the expected result?

medium

A. An error occurs because the server name is missing

B. The command creates a SQL server instead of a database

C. A new Azure SQL Database named 'testdb' with performance level S1 is created

D. The database is created but with default performance level Basic

Solution

1. Step 1: Analyze the command parameters

   The command specifies resource group, server, database name, and service objective S1, which sets the performance level.

2. Step 2: Understand Azure CLI behavior

   This command creates a new database named 'testdb' on server 'myserver' with performance level S1 without errors.

3. Final Answer:

   A new Azure SQL Database named 'testdb' with performance level S1 is created -> Option C

4. Quick Check:

   Command creates database with specified name and performance [OK]

Hint: Check if all required parameters are present for creation [OK]

Common Mistakes:

• Assuming server name is missing
• Confusing database creation with server creation
• Thinking default performance applies despite explicit S1

4. You run this command:

az sql db create --resource-group mygroup --server myserver --name mydb

But receive an error about missing --service-objective. How do you fix it?

medium

A. Add --service-objective S0 to specify performance tier

B. Remove the --server parameter

C. Change --name to --database

D. Run az sql server create first

Solution

1. Step 1: Identify missing required parameter

   The error indicates --service-objective is required to set the performance tier for the database.

2. Step 2: Fix command by adding performance tier

   Adding --service-objective S0 specifies the performance level and resolves the error.

3. Final Answer:

   Add --service-objective S0 to specify performance tier -> Option A

4. Quick Check:

   Missing performance tier fixed by adding --service-objective [OK]

Hint: Always specify performance tier with --service-objective [OK]

Common Mistakes:

• Removing server parameter causes other errors
• Changing --name to --database is invalid
• Assuming server creation fixes database parameter errors

5. You want to create an Azure SQL Database with high performance and minimal downtime. Which combination of parameters is best to use in the Azure CLI command?

hard

A. az sql db create --resource-group mygroup --server myserver --name highperfdb

B. az sql db create --resource-group mygroup --server myserver --name highperfdb --service-objective Basic --zone-redundant false

C. az sql db create --resource-group mygroup --server myserver --name highperfdb --service-objective S0

D. az sql db create --resource-group mygroup --server myserver --name highperfdb --service-objective P2 --zone-redundant true

Solution

1. Step 1: Identify high performance tier

   Performance tier P2 offers higher compute and storage than Basic or S0 tiers.

2. Step 2: Enable zone redundancy for minimal downtime

   Setting --zone-redundant true ensures the database is replicated across availability zones, reducing downtime risk.

3. Final Answer:

   Use P2 tier with zone redundancy enabled -> Option D

4. Quick Check:

   High performance + zone redundancy = az sql db create --resource-group mygroup --server myserver --name highperfdb --service-objective P2 --zone-redundant true [OK]

Hint: Choose higher tier and enable zone redundancy for best uptime [OK]

Common Mistakes:

• Using Basic tier for high performance needs
• Ignoring zone redundancy option
• Omitting service objective parameter