Skip to content

🚀 Hyperscale Tier - Azure SQL Database

Tier

Hyperscale is a scalable storage architecture that supports databases up to 100TB with instant backups and fast restores.

🎯 Overview

Hyperscale tier uses a multi-tiered distributed architecture with page servers and compute nodes, enabling massive scale with minimal operational overhead.

Key Benefits

  • Massive Scale: Up to 100TB databases
  • Fast Backups: Snapshot-based, complete in seconds
  • Rapid Scaling: Scale compute up/down in minutes
  • Read Scale-out: Up to 4 high-availability replicas
  • Storage Auto-grow: No pre-provisioning required

🏗️ Architecture

graph TB
    Client[Client Application] --> Primary[Primary Compute<br/>Read-Write]
    Client --> Replica1[HA Replica 1<br/>Read-Only]
    Client --> Replica2[HA Replica 2<br/>Read-Only]

    Primary --> PageServer1[Page Server 1]
    Primary --> PageServer2[Page Server 2]
    Replica1 --> PageServer1
    Replica2 --> PageServer2

    PageServer1 --> Storage[Azure Storage<br/>Data Files]
    PageServer2 --> Storage

    Log[Log Service] --> Storage
    Primary --> Log

🚀 Create Hyperscale Database

# Create Hyperscale database
az sql db create \
  --resource-group myresourcegroup \
  --server mysqlserver \
  --name hypersaledb \
  --edition Hyperscale \
  --service-objective HS_Gen5_4 \
  --read-scale Enabled \
  --zone-redundant false

# Add read replica
az sql db replica create \
  --name hypersaledb \
  --resource-group myresourcegroup \
  --server mysqlserver \
  --secondary-type ReadOnlyReplica

Query Routing

import pyodbc

# Primary connection (read-write)
primary_conn_string = """
    Server=tcp:mysqlserver.database.windows.net,1433;
    Database=hypersaledb;
    ApplicationIntent=ReadWrite;
"""

# Replica connection (read-only)
replica_conn_string = """
    Server=tcp:mysqlserver.database.windows.net,1433;
    Database=hypersaledb;
    ApplicationIntent=ReadOnly;
"""

# Route writes to primary
primary_conn = pyodbc.connect(primary_conn_string)
cursor = primary_conn.cursor()
cursor.execute("INSERT INTO Orders VALUES (1, 'Product', 100)")
primary_conn.commit()

# Route reads to replica
replica_conn = pyodbc.connect(replica_conn_string)
cursor = replica_conn.cursor()
cursor.execute("SELECT * FROM Orders")
results = cursor.fetchall()

📊 Performance Optimization

Columnstore Indexes

-- Create columnstore index for analytics
CREATE CLUSTERED COLUMNSTORE INDEX CCI_Orders
ON Orders;

-- Optimized for large scans
SELECT ProductCategory, SUM(OrderTotal)
FROM Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY ProductCategory;

Read Scale-out

-- Offload reporting queries to read replicas
-- Add ApplicationIntent=ReadOnly to connection string
SELECT
    CustomerID,
    COUNT(*) as TotalOrders,
    SUM(OrderAmount) as TotalSpent
FROM Orders
WHERE OrderDate >= DATEADD(month, -12, GETDATE())
GROUP BY CustomerID
ORDER BY TotalSpent DESC;

💰 Cost Optimization

-- Scale down during off-hours
ALTER DATABASE hypersaledb
MODIFY (SERVICE_OBJECTIVE = 'HS_Gen5_2');

-- Scale up for peak hours
ALTER DATABASE hypersaledb
MODIFY (SERVICE_OBJECTIVE = 'HS_Gen5_8');

Last Updated: 2025-01-28