Skip to content

README.md

Latest commit

 

History

History
918 lines (703 loc) · 27.7 KB

README.md

File metadata and controls

918 lines (703 loc) · 27.7 KB

Deploying a Microsoft SQL Database and Web App with AZD

⏱️ Estimated Time: 20-30 minutes | 💰 Estimated Cost: ~$15-25/month | ⭐ Complexity: Intermediate

This complete, working example demonstrates how to use the Azure Developer CLI (azd) to deploy a Python Flask web application with a Microsoft SQL Database to Azure. All code is included and tested—no external dependencies required.

What You'll Learn

By completing this example, you will:

  • Deploy a multi-tier application (web app + database) using infrastructure-as-code
  • Configure secure database connections without hardcoding secrets
  • Monitor application health with Application Insights
  • Manage Azure resources efficiently with AZD CLI
  • Follow Azure best practices for security, cost optimization, and observability

Scenario Overview

  • Web App: Python Flask REST API with database connectivity
  • Database: Azure SQL Database with sample data
  • Infrastructure: Provisioned using Bicep (modular, reusable templates)
  • Deployment: Fully automated with azd commands
  • Monitoring: Application Insights for logs and telemetry

Prerequisites

Required Tools

Before starting, verify you have these tools installed:

  1. Azure CLI (version 2.50.0 or higher)

    az --version
# Expected output: azure-cli 2.50.0 or higher

  2. Azure Developer CLI (azd) (version 1.0.0 or higher)

    azd version
# Expected output: azd version 1.0.0 or higher

  3. Python 3.8+ (for local development)

    python --version
# Expected output: Python 3.8 or higher

  4. Docker (optional, for local containerized development)

    docker --version
# Expected output: Docker version 20.10 or higher

Azure Requirements

  • An active Azure subscription (create a free account)
  • Permissions to create resources in your subscription
  • Owner or Contributor role on the subscription or resource group

Knowledge Prerequisites

This is an intermediate-level example. You should be familiar with:

  • Basic command-line operations
  • Fundamental cloud concepts (resources, resource groups)
  • Basic understanding of web applications and databases

New to AZD? Start with the Getting Started guide first.

Architecture

This example deploys a two-tier architecture with a web application and SQL database:

graph TD
    Browser[User Browser] <--> WebApp[Azure Web App<br/>Flask API<br/>/health<br/>/products]
    WebApp -- Secure Connection<br/>Encrypted --> SQL[Azure SQL Database<br/>Products table<br/>Sample data]
Loading

Resource Deployment:

  • Resource Group: Container for all resources
  • App Service Plan: Linux-based hosting (B1 tier for cost efficiency)
  • Web App: Python 3.11 runtime with Flask application
  • SQL Server: Managed database server with TLS 1.2 minimum
  • SQL Database: Basic tier (2GB, suitable for development/testing)
  • Application Insights: Monitoring and logging
  • Log Analytics Workspace: Centralized log storage

Analogy: Think of this like a restaurant (web app) with a walk-in freezer (database). Customers order from the menu (API endpoints), and the kitchen (Flask app) retrieves ingredients (data) from the freezer. The restaurant manager (Application Insights) tracks everything that happens.

Folder Structure

All files are included in this example—no external dependencies required:

examples/database-app/
│
├── README.md                    # This file
├── azure.yaml                   # AZD configuration file
├── .env.sample                  # Sample environment variables
├── .gitignore                   # Git ignore patterns
│
├── infra/                       # Infrastructure as Code (Bicep)
│   ├── main.bicep              # Main orchestration template
│   ├── abbreviations.json      # Azure naming conventions
│   └── resources/              # Modular resource templates
│       ├── sql-server.bicep    # SQL Server configuration
│       ├── sql-database.bicep  # Database configuration
│       ├── app-service-plan.bicep  # Hosting plan
│       ├── app-insights.bicep  # Monitoring setup
│       └── web-app.bicep       # Web application
│
└── src/
    └── web/                    # Application source code
        ├── app.py              # Flask REST API
        ├── requirements.txt    # Python dependencies
        └── Dockerfile          # Container definition

What Each File Does:

  • azure.yaml: Tells AZD what to deploy and where
  • infra/main.bicep: Orchestrates all Azure resources
  • infra/resources/*.bicep: Individual resource definitions (modular for reuse)
  • src/web/app.py: Flask application with database logic
  • requirements.txt: Python package dependencies
  • Dockerfile: Containerization instructions for deployment

Quickstart (Step-by-Step)

Step 1: Clone and Navigate

git clone https://github.com/microsoft/AZD-for-beginners.git
cd AZD-for-beginners/examples/database-app

✓ Success Check: Verify you see azure.yaml and infra/ folder:

ls
# Expected: README.md, azure.yaml, infra/, src/

Step 2: Authenticate with Azure

azd auth login

This opens your browser for Azure authentication. Sign in with your Azure credentials.

✓ Success Check: You should see:

Logged in to Azure.

Step 3: Initialize the Environment

azd init

What happens: AZD creates a local configuration for your deployment.

Prompts you'll see:

  • Environment name: Enter a short name (e.g., dev, myapp)
  • Azure subscription: Select your subscription from the list
  • Azure location: Choose a region (e.g., eastus, westeurope)

✓ Success Check: You should see:

SUCCESS: New project initialized!

Step 4: Provision Azure Resources

azd provision

What happens: AZD deploys all infrastructure (takes 5-8 minutes):

  1. Creates resource group
  2. Creates SQL Server and Database
  3. Creates App Service Plan
  4. Creates Web App
  5. Creates Application Insights
  6. Configures networking and security

You'll be prompted for:

  • SQL admin username: Enter a username (e.g., sqladmin)
  • SQL admin password: Enter a strong password (save this!)

✓ Success Check: You should see:

SUCCESS: Your application was provisioned in Azure in X minutes Y seconds.
You can view the resources created under the resource group rg-<env-name> in Azure Portal:
https://portal.azure.com/#@/resource/subscriptions/.../resourceGroups/rg-<env-name>

⏱️ Time: 5-8 minutes

Step 5: Deploy the Application

azd deploy

What happens: AZD builds and deploys your Flask application:

  1. Packages the Python application
  2. Builds the Docker container
  3. Pushes to Azure Web App
  4. Initializes the database with sample data
  5. Starts the application

✓ Success Check: You should see:

SUCCESS: Your application was deployed to Azure in X minutes Y seconds.
You can view the resources created under the resource group rg-<env-name> in Azure Portal:
https://portal.azure.com/#@/resource/subscriptions/.../resourceGroups/rg-<env-name>

⏱️ Time: 3-5 minutes

Step 6: Browse the Application

azd browse

This opens your deployed web app in the browser at https://app-<unique-id>.azurewebsites.net

✓ Success Check: You should see JSON output:

{
  "message": "Welcome to the Database App API",
  "endpoints": {
    "/": "This help message",
    "/health": "Health check endpoint",
    "/products": "List all products",
    "/products/<id>": "Get product by ID"
  }
}

Step 7: Test the API Endpoints

Health Check (verify database connection):

curl https://app-<your-id>.azurewebsites.net/health

Expected Response:

{
  "status": "healthy",
  "database": "connected"
}

List Products (sample data):

curl https://app-<your-id>.azurewebsites.net/products

Expected Response:

[
  {
    "id": 1,
    "name": "Laptop",
    "description": "High-performance laptop",
    "price": 1299.99,
    "created_at": "2025-11-19T10:30:00"
  },
  ...
]

Get Single Product:

curl https://app-<your-id>.azurewebsites.net/products/1

✓ Success Check: All endpoints return JSON data without errors.

🎉 Congratulations! You've successfully deployed a web application with a database to Azure using AZD.

Configuration Deep-Dive

Environment Variables

Secrets are managed securely via Azure App Service configuration—never hardcoded in source code.

Configured Automatically by AZD:

  • SQL_CONNECTION_STRING: Database connection with encrypted credentials
  • APPLICATIONINSIGHTS_CONNECTION_STRING: Monitoring telemetry endpoint
  • SCM_DO_BUILD_DURING_DEPLOYMENT: Enables automatic dependency installation

Where Secrets Are Stored:

  1. During azd provision, you provide SQL credentials via secure prompts
  2. AZD stores these in your local .azure/<env-name>/.env file (git-ignored)
  3. AZD injects them into Azure App Service configuration (encrypted at rest)
  4. Application reads them via os.getenv() at runtime

Local Development

For local testing, create a .env file from the sample:

cp .env.sample .env
# Edit .env with your local database connection

Local Development Workflow:

# Install dependencies
cd src/web
pip install -r requirements.txt

# Set environment variables
export SQL_CONNECTION_STRING="your-local-connection-string"

# Run the application
python app.py

Test locally:

curl http://localhost:8000/health
# Expected: {"status": "healthy", "database": "connected"}

Infrastructure as Code

All Azure resources are defined in Bicep templates (infra/ folder):

  • Modular Design: Each resource type has its own file for reusability
  • Parameterized: Customize SKUs, regions, naming conventions
  • Best Practices: Follows Azure naming standards and security defaults
  • Version Controlled: Infrastructure changes are tracked in Git

Customization Example: To change the database tier, edit infra/resources/sql-database.bicep:

sku: {
  name: 'Standard'  // Changed from 'Basic'
  tier: 'Standard'
  capacity: 10
}

Security Best Practices

This example follows Azure security best practices:

1. No Secrets in Source Code

  • ✅ Credentials stored in Azure App Service configuration (encrypted)
  • ✅ .env files excluded from Git via .gitignore
  • ✅ Secrets passed via secure parameters during provisioning

2. Encrypted Connections

  • ✅ TLS 1.2 minimum for SQL Server
  • ✅ HTTPS-only enforced for Web App
  • ✅ Database connections use encrypted channels

3. Network Security

  • ✅ SQL Server firewall configured to allow Azure services only
  • ✅ Public network access restricted (can be further locked down with Private Endpoints)
  • ✅ FTPS disabled on Web App

4. Authentication & Authorization

  • ⚠️ Current: SQL authentication (username/password)
  • ✅ Production Recommendation: Use Azure Managed Identity for passwordless authentication

To Upgrade to Managed Identity (for production):

  1. Enable managed identity on Web App
  2. Grant identity SQL permissions
  3. Update connection string to use managed identity
  4. Remove password-based authentication

5. Auditing & Compliance

  • ✅ Application Insights logs all requests and errors
  • ✅ SQL Database auditing enabled (can be configured for compliance)
  • ✅ All resources tagged for governance

Security Checklist Before Production:

  • Enable Azure Defender for SQL
  • Configure Private Endpoints for SQL Database
  • Enable Web Application Firewall (WAF)
  • Implement Azure Key Vault for secret rotation
  • Configure Azure AD authentication
  • Enable diagnostic logging for all resources

Cost Optimization

Estimated Monthly Costs (as of November 2025):

Resource SKU/Tier Estimated Cost
App Service Plan B1 (Basic) ~$13/month
SQL Database Basic (2GB) ~$5/month
Application Insights Pay-as-you-go ~$2/month (low traffic)
Total ~$20/month

💡 Cost-Saving Tips:

  1. Use Free Tier for Learning:

    • App Service: F1 tier (free, limited hours)
    • SQL Database: Use Azure SQL Database serverless
    • Application Insights: 5GB/month free ingestion

  2. Stop Resources When Not in Use:

    # Stop the web app (database still charges)
az webapp stop --name <app-name> --resource-group <rg-name>

# Restart when needed
az webapp start --name <app-name> --resource-group <rg-name>

  3. Delete Everything After Testing:

    azd down

    This removes ALL resources and stops charges.

  4. Development vs. Production SKUs:

    • Development: Basic tier (used in this example)
    • Production: Standard/Premium tier with redundancy

Cost Monitoring:

  • View costs in Azure Cost Management
  • Set up cost alerts to avoid surprises
  • Tag all resources with azd-env-name for tracking

Free Tier Alternative: For learning purposes, you can modify infra/resources/app-service-plan.bicep:

sku: {
  name: 'F1'  // Free tier
  tier: 'Free'
}

Note: Free tier has limitations (60 min/day CPU, no always-on).

Monitoring & Observability

Application Insights Integration

This example includes Application Insights for comprehensive monitoring:

What's Monitored:

  • ✅ HTTP requests (latency, status codes, endpoints)
  • ✅ Application errors and exceptions
  • ✅ Custom logging from Flask app
  • ✅ Database connection health
  • ✅ Performance metrics (CPU, memory)

Access Application Insights:

  1. Open Azure Portal
  2. Navigate to your resource group (rg-<env-name>)
  3. Click on Application Insights resource (appi-<unique-id>)

Useful Queries (Application Insights → Logs):

View All Requests:

requests
| where timestamp > ago(1h)
| order by timestamp desc
| project timestamp, name, url, resultCode, duration

Find Errors:

exceptions
| where timestamp > ago(24h)
| order by timestamp desc
| project timestamp, type, outerMessage, operation_Name

Check Health Endpoint:

requests
| where name contains "health"
| summarize count() by resultCode, bin(timestamp, 1h)

SQL Database Auditing

SQL Database auditing is enabled to track:

  • Database access patterns
  • Failed login attempts
  • Schema changes
  • Data access (for compliance)

Access Audit Logs:

  1. Azure Portal → SQL Database → Auditing
  2. View logs in Log Analytics workspace

Real-Time Monitoring

View Live Metrics:

  1. Application Insights → Live Metrics
  2. See requests, failures, and performance in real-time

Set Up Alerts: Create alerts for critical events:

  • HTTP 500 errors > 5 in 5 minutes
  • Database connection failures
  • High response times (>2 seconds)

Example Alert Creation:

az monitor metrics alert create \
  --name "High-Response-Time" \
  --resource-group <rg-name> \
  --scopes <app-insights-resource-id> \
  --condition "avg requests/duration > 2000" \
  --description "Alert when response time exceeds 2 seconds"

Troubleshooting

Common Issues and Solutions

1. azd provision fails with "Location not available"

Symptom:

Error: The subscription is not registered for the resource type 'components' in the location 'centralus'.

Solution: Choose a different Azure region or register the resource provider:

az provider register --namespace Microsoft.Insights

2. SQL Connection Fails During Deployment

Symptom:

pyodbc.OperationalError: ('08001', '[08001] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider...')

Solution:

  • Verify SQL Server firewall allows Azure services (configured automatically)
  • Check the SQL admin password was entered correctly during azd provision
  • Ensure SQL Server is fully provisioned (can take 2-3 minutes)

Verify Connection:

# From Azure Portal, go to SQL Database → Query editor
# Try to connect with your credentials

3. Web App Shows "Application Error"

Symptom: Browser shows generic error page.

Solution: Check application logs:

# View recent logs
az webapp log tail --name <app-name> --resource-group <rg-name>

Common causes:

  • Missing environment variables (check App Service → Configuration)
  • Python package installation failed (check deployment logs)
  • Database initialization error (check SQL connectivity)

4. azd deploy Fails with "Build Error"

Symptom:

Error: Failed to build project

Solution:

  • Ensure requirements.txt has no syntax errors
  • Check that Python 3.11 is specified in infra/resources/web-app.bicep
  • Verify Dockerfile has correct base image

Debug locally:

cd src/web
docker build -t test-app .
docker run -p 8000:8000 test-app

5. "Unauthorized" When Running AZD Commands

Symptom:

ERROR: (Unauthorized) The client '<id>' with object id '<id>' does not have authorization

Solution: Re-authenticate with Azure:

# Required for AZD workflows
azd auth login

# Optional if you are also using Azure CLI commands directly
az login

Verify you have the correct permissions (Contributor role) on the subscription.

6. High Database Costs

Symptom: Unexpected Azure bill.

Solution:

  • Check if you forgot to run azd down after testing
  • Verify SQL Database is using Basic tier (not Premium)
  • Review costs in Azure Cost Management
  • Set up cost alerts

Getting Help

View All AZD Environment Variables:

azd env get-values

Check Deployment Status:

az webapp show --name <app-name> --resource-group <rg-name> --query state

Access Application Logs:

az webapp log download --name <app-name> --resource-group <rg-name> --log-file app-logs.zip

Need More Help?

Practical Exercises

Exercise 1: Verify Your Deployment (Beginner)

Goal: Confirm all resources are deployed and the application is working.

Steps:

  1. List all resources in your resource group:

    az resource list --resource-group rg-<env-name> --output table

    Expected: 6-7 resources (Web App, SQL Server, SQL Database, App Service Plan, Application Insights, Log Analytics)

  2. Test all API endpoints:

    curl https://app-<your-id>.azurewebsites.net/
curl https://app-<your-id>.azurewebsites.net/health
curl https://app-<your-id>.azurewebsites.net/products
curl https://app-<your-id>.azurewebsites.net/products/1

    Expected: All return valid JSON without errors

  3. Check Application Insights:

    • Navigate to Application Insights in Azure Portal
    • Go to "Live Metrics"
    • Refresh your browser on the web app Expected: See requests appearing in real-time

Success Criteria: All 6-7 resources exist, all endpoints return data, Live Metrics shows activity.

Exercise 2: Add a New API Endpoint (Intermediate)

Goal: Extend the Flask application with a new endpoint.

Starter Code: Current endpoints in src/web/app.py

Steps:

  1. Edit src/web/app.py and add a new endpoint after the get_product() function:

    @app.route('/products/search/<keyword>')
def search_products(keyword):
    """Search products by name or description."""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute(
            "SELECT id, name, description, price, created_at FROM products WHERE name LIKE ? OR description LIKE ?",
            (f'%{keyword}%', f'%{keyword}%')
        )
        
        products = []
        for row in cursor.fetchall():
            products.append({
                'id': row[0],
                'name': row[1],
                'description': row[2],
                'price': float(row[3]) if row[3] else None,
                'created_at': row[4].isoformat() if row[4] else None
            })
        
        cursor.close()
        conn.close()
        
        logger.info(f"Search for '{keyword}' returned {len(products)} results")
        return jsonify(products), 200
        
    except Exception as e:
        logger.error(f"Error searching products: {str(e)}")
        return jsonify({'error': str(e)}), 500

  2. Deploy the updated application:

    azd deploy

  3. Test the new endpoint:

    curl https://app-<your-id>.azurewebsites.net/products/search/laptop

    Expected: Returns products matching "laptop"

Success Criteria: New endpoint works, returns filtered results, shows up in Application Insights logs.

Exercise 3: Add Monitoring and Alerts (Advanced)

Goal: Set up proactive monitoring with alerts.

Steps:

  1. Create an alert for HTTP 500 errors:

    # Get Application Insights resource ID
AI_ID=$(az monitor app-insights component show \
  --app appi-<your-id> \
  --resource-group rg-<env-name> \
  --query id -o tsv)

# Create alert
az monitor metrics alert create \
  --name "High-Error-Rate" \
  --resource-group rg-<env-name> \
  --scopes $AI_ID \
  --condition "count requests/failed > 5" \
  --window-size 5m \
  --evaluation-frequency 1m \
  --description "Alert when >5 failed requests in 5 minutes"

  2. Trigger the alert by causing errors:

    # Request a non-existent product
for i in {1..10}; do curl https://app-<your-id>.azurewebsites.net/products/999; done

  3. Check if the alert fired:

    • Azure Portal → Alerts → Alert Rules
    • Check your email (if configured)

Success Criteria: Alert rule is created, triggers on errors, notifications are received.

Exercise 4: Database Schema Changes (Advanced)

Goal: Add a new table and modify the application to use it.

Steps:

  1. Connect to SQL Database via Azure Portal Query Editor

  2. Create a new categories table:

    CREATE TABLE categories (
    id INT PRIMARY KEY IDENTITY(1,1),
    name NVARCHAR(50) NOT NULL,
    description NVARCHAR(200)
);

INSERT INTO categories (name, description) VALUES
('Electronics', 'Electronic devices and accessories'),
('Office Supplies', 'Office equipment and supplies');

-- Add category to products table
ALTER TABLE products ADD category_id INT;
UPDATE products SET category_id = 1; -- Set all to Electronics

  3. Update src/web/app.py to include category information in responses

  4. Deploy and test

Success Criteria: New table exists, products show category information, application still works.

Exercise 5: Implement Caching (Expert)

Goal: Add Azure Redis Cache to improve performance.

Steps:

  1. Add Redis Cache to infra/main.bicep
  2. Update src/web/app.py to cache product queries
  3. Measure performance improvement with Application Insights
  4. Compare response times before/after caching

Success Criteria: Redis is deployed, caching works, response times improve by >50%.

Hint: Start with Azure Cache for Redis documentation.

Cleanup

To avoid ongoing charges, delete all resources when done:

azd down

Confirmation prompt:

? Total resources to delete: 7, are you sure you want to continue? (y/N)

Type y to confirm.

✓ Success Check:

  • All resources are deleted from Azure Portal
  • No ongoing charges
  • Local .azure/<env-name> folder can be deleted

Alternative (keep infrastructure, delete data):

# Delete only the resource group (keep AZD config)
az group delete --name rg-<env-name> --yes

Learn More

Related Documentation

Next Steps in This Course

Advanced Topics

  • Managed Identity: Remove passwords and use Azure AD authentication
  • Private Endpoints: Secure database connections within a virtual network
  • CI/CD Integration: Automate deployments with GitHub Actions or Azure DevOps
  • Multi-Environment: Set up dev, staging, and production environments
  • Database Migrations: Use Alembic or Entity Framework for schema versioning

Comparison to Other Approaches

AZD vs. ARM Templates:

  • ✅ AZD: Higher-level abstraction, simpler commands
  • ⚠️ ARM: More verbose, granular control

AZD vs. Terraform:

  • ✅ AZD: Azure-native, integrated with Azure services
  • ⚠️ Terraform: Multi-cloud support, larger ecosystem

AZD vs. Azure Portal:

  • ✅ AZD: Repeatable, version-controlled, automatable
  • ⚠️ Portal: Manual clicks, difficult to reproduce

Think of AZD as: Docker Compose for Azure—simplified configuration for complex deployments.

Frequently Asked Questions

Q: Can I use a different programming language?
A: Yes! Replace src/web/ with Node.js, C#, Go, or any language. Update azure.yaml and Bicep accordingly.

Q: How do I add more databases?
A: Add another SQL Database module in infra/main.bicep or use PostgreSQL/MySQL from Azure Database services.

Q: Can I use this for production?
A: This is a starting point. For production, add: managed identity, private endpoints, redundancy, backup strategy, WAF, and enhanced monitoring.

Q: What if I want to use containers instead of code deployment?
A: Check out the Container Apps Example which uses Docker containers throughout.

Q: How do I connect to the database from my local machine?
A: Add your IP to the SQL Server firewall:

az sql server firewall-rule create \
  --resource-group rg-<env-name> \
  --server sql-<unique-id> \
  --name AllowMyIP \
  --start-ip-address <your-ip> \
  --end-ip-address <your-ip>

Q: Can I use an existing database instead of creating a new one?
A: Yes, modify infra/main.bicep to reference an existing SQL Server and update the connection string parameters.

Note: This example demonstrates best practices for deploying a web app with a database using AZD. It includes working code, comprehensive documentation, and practical exercises to reinforce learning. For production deployments, review security, scaling, compliance, and cost requirements specific to your organization.

📚 Course Navigation:
⚡