Why Partitioning & Scaling Matter

As data grows, a single database or storage instance can become a bottleneck. To handle scale, Azure databases and storage use partitioning (sharding), indexing, and scaling mechanisms. These strategies allow workloads to maintain performance, availability, and predictable costs.

1. Partitioning in Azure

Definition:
Splitting data into smaller segments (partitions/shards) to distribute load.

Cosmos DB Partitioning:

• Uses a partition key (e.g., UserID, ProductID).

• Ensures data is distributed evenly across physical partitions.

• Poor partition key choice = hot partition = throttling.

SQL Database Sharding:

• Split large DBs into smaller ones (horizontal partitioning).

• Requires app-level logic or Elastic Database Tools.

Best Practices:

• Choose a partition key with high cardinality.

• Avoid keys that skew traffic to a small set of values.

2. Scaling in Azure SQL

a. Service Tiers

• DTU-based (Basic, Standard, Premium).

• vCore-based (General Purpose, Business Critical, Hyperscale).

b. Elastic Pools

• Share compute resources across multiple databases.

• Best for workloads with unpredictable usage.

c. Hyperscale

• Supports 100 TB databases.

• Automatically scales storage + compute independently.

3. Scaling in Cosmos DB

• Request Units (RU/s): Throughput is provisioned based on RU.

• Autoscale RU: Adjusts automatically (up to 10x base).

• Global Distribution: Scale horizontally across multiple regions.

4. Performance Tuning Strategies

SQL Database:

• Use indexes to optimize queries.

• Use in-memory OLTP for high-transaction workloads.

• Monitor with Query Performance Insight.

• Enable Automatic Tuning (adds/drops indexes).

Cosmos DB:

• Optimize partition key choice.

• Use appropriate consistency model.

• Monitor RU consumption, avoid “429 request rate too large” errors.

Storage Accounts:

• Use ZRS/GRS for high availability.

• Enable lifecycle policies to move old data to Cool/Archive.

Example Enterprise Scenario

A gaming platform requires:

• Player session data distributed globally with low latency.

• SQL transactional data must scale to hundreds of GBs without downtime.

• Some databases have unpredictable workloads across multiple departments.

Correct design:

• Use Cosmos DB with partition key = PlayerID for session data.

• Deploy SQL Hyperscale for transactional DB.

• Place departmental workloads in an Elastic Pool.

Confusion Buster

• Elastic Pools vs Hyperscale

  • Elastic Pool = multiple small/mid DBs sharing resources.

  • Hyperscale = one very large DB scaling beyond 100 TB.

• Cosmos DB RU vs SQL DTU/vCore

  • RU = request unit abstraction for Cosmos DB.

  • DTU/vCore = performance models for SQL Database.

• Partition Key vs Index

  • Partition Key = distributes data across nodes.

  • Index = optimizes query inside a partition.

Exam Tips

• “Which feature enables SQL DB to scale to 100 TB?” → Hyperscale.

• “Which option shares compute across multiple DBs?” → Elastic Pool.

• “Which Cosmos DB feature avoids hot partitions?” → Good partition key design.

• “Which feature automatically creates/drops indexes in SQL DB?” → Automatic Tuning.

What to Expect in the Exam

• Direct Q: “Which SQL DB tier scales to 100 TB?” → Hyperscale.

• Scenario Q: “Retailer requires unpredictable workload scaling across multiple DBs.” → Elastic Pool.

• Scenario Q: “Gaming app needs evenly distributed global session data.” → Cosmos DB partitioning with PlayerID.

• Trick Q: “Partition key choice has no effect on performance in Cosmos DB.” → False.