Key Limitations of Azure SQL Database: Missing Features You Should Know
When we are choosing a database solution from multiple options, it is always tempting to choose the one that is easiest to manage, especially now when we have so many managed services that promise to simplify the complexities of database administration. With features such as automated backups, scaling, and built-in security, these services can significantly ease your workload. So why not choose them? They may indeed be more costly options, but we all know that time is the most valuable resource.
However, you should understand that the abstraction of management tasks comes with a price. These managed services often have limitations compared to self-managed solutions.
One illustrative example of the significant difference between self-managed and managed services is Azure SQL.
Azure SQL Database, along with Azure SQL Managed Instance and SQL Server on Azure Virtual Machines, are options for SQL in the Azure cloud. Azure SQL Database is the most managed option and appears to be the best choice when looking for SQL Server options in Azure. However, while Azure SQL Database is indeed a great service, it lacks some important functions that are common parts of SQL Server functionality. If you have significant experience with SQL Server, you might expect features that Azure SQL Database cannot provide. To avoid future regrets, let’s dive deeply into the limitations of Azure SQL Database in comparison to a self-managed SQL Server.
Useful SQL Server features that Azure SQL Database doesn’t have:
SQL Server Agent
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks like backups, database maintenance, monitoring, and data processing. This feature is especially useful if you would like to automate routine tasks and can significantly simplify database administration. However, if you choose Azure SQL Database instead of SQL Server, you would need to use Azure Automation, Elastic Jobs, or Azure Functions for task scheduling and automation, which is a less convenient way to do it.
SQL Server Integration Services (SSIS)
If you are familiar with SSIS, the native SQL Server tool for data integration, ETL operations, and complex data workflows, you are probably laughing right now. While SQL Server Integration Services is indeed a bit outdated, it is still very convenient to have an integrated ETL tool directly in your DBMS, even if it is a little bit difficult to work with. If you choose Azure SQL Database, you must use Data Factory for ETL processes or run SSIS packages via Azure Data Factory’s SSIS Integration Runtime.
Linked Servers & Cross-Database Transactions
Usually, when you work with SQL Server, it is common practice to have multiple databases to organize and manage different applications, environments, or data sets. This approach is very convenient until you need to query data across multiple databases simultaneously, for example, to create an ad hoc report or perform data analysis. Creating Linked Servers for simplified data access can save the day, but unfortunately, Azure SQL Database does not support this powerful feature.
Moreover, the situation becomes significantly more complex when it is necessary to maintain data consistency and atomicity across operations that span multiple databases. Unlike Azure SQL Database, SQL Server supports distributed transactions and cross-database transactions natively using the Microsoft Distributed Transaction Coordinator, which can make this process much easier.
Replication
One of the biggest advantages of SQL Server compared to other DBMS is its support for multiple types of replication, offering almost any option you might need:
- Transactional Replication: Transactional Replication distributes data and database objects in near real-time from a publisher to subscribers. Changes at the publisher are propagated to subscribers as they occur. One of the most powerful options for transactional replication is bidirectional transactional replication, which allows two servers to exchange changes with each other.
- Merge Replication: Merge Replication allows changes to be made at both the publisher and the subscribers. During data replication, SQL Server Snapshot Agent and Merge Agent detect and resolve all conflicts based on specified rules.
- Snapshot Replication: Snapshot Replication distributes a complete data snapshot to subscribers at specified intervals and can be used when there is no need for continuous monitoring of changes.
- Peer-to-Peer Replication: Peer-to-Peer Replication allows multiple nodes to synchronize changes with each other, similar to bidirectional transactional replication. The difference is that Peer-to-Peer Replication synchronizes changes among multiple nodes in a multi-master setup, while bidirectional transactional replication synchronizes changes between two servers in a two-way setup.
And these are only the most popular ones. In this table, you can see all types of replication. Unfortunately, Azure SQL does not support any of these types (Azure SQL Database supports Standard Transactional and Snapshot replication types only as a subscriber).
Instead of various data replication options, Azure SQL Database offers alternative features for data replication and synchronization, such as Geo-Replication and Auto-Failover Groups. However, this functionality is far from the powerful replication options available in a self-managed SQL Server.
Advanced Data Security Features
While Azure SQL Database includes built-in security features, it does not match the level of control and customization available with a self-managed SQL Server. The most useful missing features are Transparent Data Encryption with BYOK, Advanced Auditing, and Policy-Based Management.
I listed only the options I missed the most while using Azure SQL Database. You should know that this list is much broader. Please always check the official documentation, this information can become outdated as Microsoft continuously updates and improves its services.
Here is the list of other important features of SQL Server missing in Azure SQL Database. To understand these features, please check the links:
In summary, Azure SQL Database offers significant advantages in terms of ease of management and scalability. However, for those who rely on the full suite of SQL Server features, the limitations of Azure SQL Database may pose challenges. Carefully weighing the benefits of managed services against these limitations will help ensure you choose the right solution for your specific requirements.
If you are interested in SQL Server, please check my other articles on this topic:
