As a database architect who has implemented Azure SQL solutions for numerous companies, I’ve encountered nearly every limitation and constraint that Azure SQL has to offer. While Azure SQL Database provides remarkable scalability and benefits, understanding its limitations is crucial for the successful deployment.
Table of Contents
- Azure SQL Database Limitations
- Storage and Performance Limitations
- DTU and vCore Limitations
- IOPS and Throughput Constraints
- Functional and Feature Limitations
- Connectivity and Networking Limitations
- Data Migration and Backup Limitations
- Scaling and Elasticity Constraints
- Overcoming Azure SQL Database Limitations: Recommended Strategies
- Final Thoughts
Azure SQL Database Limitations
Let’s deep dive into the key limitations of Azure SQL Database.
Storage and Performance Limitations
Azure SQL Database is available in various service tiers, each with distinct limitations regarding storage capacity, compute resources, and performance capabilities. Understanding these boundaries is essential for proper capacity planning.
Maximum Database Size Constraints
One of the most fundamental limitations is the maximum database size, which varies by service tier:
| Service Tier | Maximum Database Size |
|---|---|
| Basic | 2 GB |
| Standard | 1 TB |
| Premium | 4 TB |
| Hyperscale | 100 TB |
| Business Critical | 4 TB |
| General Purpose | 4 TB |
DTU and vCore Limitations
Azure SQL Database offers two purchasing models: DTU (Database Transaction Unit) and vCore. Each has specific limitations:
DTU Model Limitations:
- Basic tier: Max 5 DTUs
- Standard tier: Max 3000 DTUs
- Premium tier: Max 4000 DTUs
vCore Model Limitations:
- General Purpose: Up to 80 vCores
- Business Critical: Up to 80 vCores
- Hyperscale: Up to 80 vCores
IOPS and Throughput Constraints
Different tiers also impose limits on Input/Output Operations Per Second (IOPS) and throughput:
| Service Tier | Max IOPS | Max Throughput |
|---|---|---|
| Basic | 2,100 | 30 MB/s |
| Standard | 24,000 | 340 MB/s |
| Premium | 48,000 | 680 MB/s |
| Hyperscale | 200,000+ | 1,000+ MB/s |
Functional and Feature Limitations
Beyond storage and performance, Azure SQL Database has several functional limitations compared to on-premises SQL Server.
T-SQL Command Restrictions
Several T-SQL commands and features available in on-premises SQL Server are either limited or unavailable in Azure SQL Database:
- Unavailable commands: BACKUP/RESTORE (replaced by automated backups), USE to switch between databases
- Modified functionality: DBCC commands (limited subset available)
- Restricted features: Cross-database queries (limited to the elastic query feature)
Database File Control Limitations
Unlike on-premises SQL Server, you cannot:
- Control physical file placement
- Configure multiple data files for a single database
- Directly manage file groups
- Control auto-growth settings in the traditional way
Cross-Database Functionality Constraints
Cross-database operations have significant limitations in Azure SQL Database:
- Limited cross-database queries (requires elastic query setup)
- No cross-database transactions
- No distributed transactions using MS DTC
- Limited linked server functionality
Connectivity and Networking Limitations
Understanding connectivity constraints is crucial for designing application architecture.
Connection Limits
Azure SQL Database imposes maximum concurrent connection limits:
| Service Tier | Maximum Concurrent Connections |
|---|---|
| Basic | 30 |
| Standard | 30,000 |
| Premium | 30,000 |
| Hyperscale | 100,000 |
Network Security Constraints
While Azure provides robust security options, they come with certain limitations:
- Public endpoint exposure (unless using Private Link)
- VNet integration requires additional configuration
- Firewall rules management complexity
- Limited IP-based restriction options
Data Migration and Backup Limitations
Moving data to and from Azure SQL Database has its own set of constraints.
Import/Export Size Limits
When importing databases using bacpac files:
- Maximum Bacpac file size: 200 GB
- Larger databases require alternative migration approaches
Backup Retention Limitations
Azure SQL Database automated backups have retention limits:
- Point-in-time restore: Maximum 35 days
- Long-term retention: Up to 10 years (with appropriate configuration)
For an insurance company in Minneapolis with regulatory requirements to maintain data for 7 years, I implemented a combination of long-term retention backups and a custom archiving solution to Azure Blob Storage for older data.
Scaling and Elasticity Constraints
While Azure SQL Database offers impressive scaling capabilities, there are important limitations to consider.
Scaling Frequency Limitations
There are constraints on how often you can scale resources:
- DTU model: Limited to 4 scale operations within 24 hours per database
- Service tier changes: May require database downtime
- Scaling operations: Can take minutes to hours, depending on database size
Elastic Pool Limitations
If using elastic pools for multiple databases:
- Maximum databases per pool: 500 (Basic), 500 (Standard), 100 (Premium)
- Pool resource sharing may lead to “noisy neighbor” issues
- eDTU allocation management complexity
Finally, let’s summarize the key limitations.
- You won’t be able to use the Windows authentication on Azure SQL. Only you can use Azure Active Directory tokens for login, as well as SQL logins.
- Another limitation is that you won’t get the Database Diagrams here in the case of Azure SQL.
- There is no Extensible key management functionality available for Azure SQL.
- There is no support for event tracking or notification features.
- Microsoft is only responsible for managing various features, such as backup and restore, Always-On, database mirroring, and log shipping, among others. You won’t have the opportunity to configure it yourself. You have to rely solely on Microsoft.
- Another essential point is that Replication features are not available for Azure SQL.
- Another major limitation is that server-level permissions are not available with Azure SQL.
- Azure SQL does not support FILESTREAM, which points to any external locations.
- You won’t be able to manage hardware-related server settings, such as memory, worker threads, and CPU affinity.
- There is no support for exporting and importing a database that has an external data source or tables.
- You won’t get the support for the Cross-database ownership chaining feature.
- There is no support for the SSIS and MDS.
- There is no provision to update or change the Collation settings of system objects in the DB.
Overcoming Azure SQL Database Limitations: Recommended Strategies
Throughout my career, optimizing Azure SQL deployments, I’ve developed several strategies to work around these limitations effectively.
1. Implement Database Sharding
For databases approaching size limits, I often implement sharding strategies:
- Horizontal partitioning by customer, date range, or region
- Using elastic query for cross-shard reporting
- Maintaining a metadata database to track shard locations
2. Use Hybrid Deployments
When certain workloads face Azure SQL limitations, consider a hybrid approach:
- Keep systems requiring unsupported features on-premises
- Use SQL Managed Instance for workloads requiring more SQL Server compatibility
- Implement data synchronization between environments
3. Optimize Application Design
Modifying application architecture can often overcome database limitations:
- Implement proper connection pooling
- Design for stateless connections where possible
- Utilize caching layers (Azure Redis Cache, Application-level caching)
- Move appropriate processing to the application or the middle tier
Final Thoughts
Understanding Azure SQL limitations is crucial for a successful deployment and smooth operation.
For most modern applications with good scaling needs, Azure SQL Database provides an excellent balance of managed benefits and performance. However, for large applications with heavy cross-database dependencies or specialized T-SQL requirements, SQL Managed Instance or hybrid solutions often prove more effective.
The key is a thorough assessment before migration. By identifying which limitations might impact your workload, you can develop appropriate mitigation strategies or choose alternative deployment models that better align with your requirements.
Just wondering if you are interested in creating an Azure SQL database.
You may also like the following articles.
I am Rajkishore, and I am a Microsoft Certified IT Consultant. I have over 14 years of experience in Microsoft Azure and AWS, with good experience in Azure Functions, Storage, Virtual Machines, Logic Apps, PowerShell Commands, CLI Commands, Machine Learning, AI, Azure Cognitive Services, DevOps, etc. Not only that, I do have good real-time experience in designing and developing cloud-native data integrations on Azure or AWS, etc. I hope you will learn from these practical Azure tutorials. Read more.
