Creating Azure SQL Database - Try It Yourself

Creating Azure SQL Database

📖 Scenario: You are setting up a simple Azure SQL Database to store customer data for a small business. This database will be used by the company's web application to save and retrieve customer information securely.

🎯 Goal: Build an Azure SQL Database resource using Azure Resource Manager (ARM) template syntax step-by-step. You will create the database server, configure the database, and finalize the deployment configuration.

📋 What You'll Learn

Create an Azure SQL Server resource with a specific name and location

Add an Azure SQL Database resource linked to the server

Configure the database with a basic performance tier

Complete the ARM template with necessary properties for deployment

💡 Why This Matters

🌍 Real World

Azure SQL Databases are widely used to store application data in the cloud with managed infrastructure.

💼 Career

Knowing how to define and deploy Azure SQL resources is essential for cloud engineers and developers working with Microsoft Azure.

1

Create Azure SQL Server resource

Create a resource dictionary called azure_resources with one entry for an Azure SQL Server. The server must have type set to "Microsoft.Sql/servers", name set to "myserver123", and location set to "eastus". Include an empty properties dictionary.

Azure

# Your code here

Hint

Think of azure_resources as a dictionary holding your cloud resources. Start by adding the SQL Server with the exact keys and values.

2

Add Azure SQL Database resource

Add a new entry to azure_resources called sqlDatabase. This should be a dictionary with type set to "Microsoft.Sql/servers/databases", name set to "myserver123/mydatabase", location set to "eastus", and an empty properties dictionary.

Azure

azure_resources = {
    "sqlServer": {
        "type": "Microsoft.Sql/servers",
        "name": "myserver123",
        "location": "eastus",
        "properties": {}
    }
}
# Add sqlDatabase resource here

Hint

Remember to add the database as a new key in the azure_resources dictionary with the exact structure.

3

Configure database performance tier

Inside the properties dictionary of sqlDatabase, add a key sku with a dictionary value containing name set to "Basic" and tier set to "Basic".

Azure

azure_resources = {
    "sqlServer": {
        "type": "Microsoft.Sql/servers",
        "name": "myserver123",
        "location": "eastus",
        "properties": {}
    },
    "sqlDatabase": {
        "type": "Microsoft.Sql/servers/databases",
        "name": "myserver123/mydatabase",
        "location": "eastus",
        "properties": {
            # Add sku configuration here
        }
    }
}

Hint

The sku property defines the performance level. Use the exact keys and values as shown.

4

Finalize ARM template structure

Wrap the azure_resources dictionary inside a new dictionary called arm_template with a key resources that holds a list of the two resource dictionaries (sqlServer and sqlDatabase).

Azure

azure_resources = {
    "sqlServer": {
        "type": "Microsoft.Sql/servers",
        "name": "myserver123",
        "location": "eastus",
        "properties": {}
    },
    "sqlDatabase": {
        "type": "Microsoft.Sql/servers/databases",
        "name": "myserver123/mydatabase",
        "location": "eastus",
        "properties": {
            "sku": {
                "name": "Basic",
                "tier": "Basic"
            }
        }
    }
}
# Wrap azure_resources in arm_template with resources list

Hint

The ARM template expects a list of resources under the resources key. Put both server and database inside that list.

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