Microsoft Docs

Azure SQL Database Storage Options

This article provides a comprehensive overview of the storage options available for Azure SQL Database, helping you choose the right configuration for your application's needs.

Understanding Storage in Azure SQL Database

Azure SQL Database offers flexible and scalable storage solutions to meet the demands of various workloads. The primary storage components are data files and log files, managed efficiently by Azure's infrastructure.

Key Storage Concepts

• Data Files: Store your tables, indexes, and other database objects.
• Log Files: Record all transaction activities for durability and recovery.
• Storage Tiers: Azure SQL Database offers different storage tiers (e.g., General Purpose, Business Critical) that provide varying levels of performance, durability, and cost.
• Storage Size: The maximum storage size is determined by your chosen service tier and compute size.

Storage Tiers Explained

General Purpose Tier

The General Purpose tier is designed for most common database workloads, offering a balance of cost and performance. It uses Azure Premium Storage for data and log files, providing a reliable and efficient storage solution.

• Performance: Provides predictable performance for typical transactional workloads.
• Durability: Uses standard Azure storage redundancy options.
• Cost: Generally more cost-effective for non-critical workloads.

Business Critical Tier

The Business Critical tier is optimized for mission-critical applications with high performance and low latency requirements. It utilizes fast SSD storage and includes multiple replicas for high availability.

• Performance: Offers the highest performance with low latency, suitable for demanding applications.
• Durability: Uses multiple replicas for automatic failover and enhanced data protection.
• Cost: Typically more expensive due to its high-performance characteristics.

Managing Storage

Monitoring Storage Usage

You can monitor your storage usage through the Azure portal, Azure Monitor, and system dynamic management views (DMVs). Key metrics include:

• user_data_space_used_percent
• log_space_used_percent
• max_logical_reads_per_second
• max_data_io_percent

Scaling Storage

Storage can be scaled up or down dynamically based on your needs. The maximum storage size is dependent on the service tier and compute size you select. You can adjust storage limits in the Azure portal under the database's configuration settings.

For example, to view current storage information using T-SQL:

                    
SELECT DB_NAME() AS DatabaseName,
       SUM(CASE WHEN state = 0 THEN size ELSE 0 END) * 8 / 1024 AS UnallocatedSpaceMB,
       SUM(CASE WHEN state = 1 THEN size ELSE 0 END) * 8 / 1024 AS AllocatedSpaceMB,
       SUM(CASE WHEN state = 2 THEN size ELSE 0 END) * 8 / 1024 AS ReservedSpaceMB,
       SUM(size) * 8 / 1024 AS TotalSizeMB
FROM sys.database_files;
                    
                

Storage Limits and Considerations

It's important to be aware of the storage limits imposed by different service tiers and purchasing models (DTU vs. vCore).

Choosing the Right Storage Option

The best storage option depends on your application's specific requirements:

• For typical applications with moderate performance needs: General Purpose tier is usually sufficient.
• For high-performance, mission-critical applications requiring low latency: Business Critical tier is recommended.
• For development, testing, or less critical workloads: Basic or Standard tiers (DTU model) might be cost-effective.

Always consider your budget, performance SLAs, and data growth projections when making your decision.