Azure SQL Solutions
Image credit: MicrosoftTable of Contents
Purpose and Approach of this Research
There are various solutions within Azure available for offering MsSQL databases.
So many choices and with this comparison, I hope to give some more insights.
To make a decision on which option could be implemented, a table with a number of general comparisons is provided below. There are many more options and choices when implementing a chosen solution, which is a follow-up step when you proceed with the implementation.
| Available options | SQL databases | SQL managed instances | SQL virtual machines |
|---|---|---|---|
| Best applied for |  Azure SQL Database |  Azure SQL Managed Instance |  Azure SQL VM |
| Available options | SQL databases | SQL managed instances | SQL virtual machines |
|---|---|---|---|
| Sales pitch | The ’evergreen’ database service is always up to date, with AI-driven and automated features that optimize performance and sustainability. With serverless compute and Hyperscale storage options, resources scale on demand—letting you focus on building new applications without worrying about storage size or resource management. | ‘You always have access to the latest version of SQL.’ | Migrate SQL Server workloads to the cloud at the lowest TCO (compared to other cloud providers). Promise of the cloud & OS control. |
| Functional question/challenge | The customer wants to build modern apps (SaaS applications), potentially multi-tenant, with maximum uptime and predictable performance. | The customer wants to migrate to the cloud and reduce management overhead, but still requires instance-scoped features like SQL Server Agent, Service Broker, and CLR. | The customer wants to move to the cloud as soon as possible while retaining OS-level control and full SQL Server functionality—even when third-party applications require access to the SQL Server’s operating system. |
| Administrative burden | Serverless, DBA management still applicable. | Serverless, DBA management still applicable. | OS en DBA management |
| Versionless* | Yes | Yes | No |
| Type of solution | PaaS | SQL instance met PaaS management capability | IaaS |
| Management interfaces | SQL Management Studio, Azure Data Studio, Command-line (Azure CLI, Azure Powershell) & REST API | SQL Management Studio, Azure Data Studio, Command-line (Azure CLI, Azure Powershell) & REST API | SQL Management Studio, Azure Data Studio, Command-line (Azure CLI, Azure Powershell) |
| Backup retention | Policy Driven Default 0-35 days (up-to 10 years with Long-term backup retention) | Policy Driven Default 0-35 days (up-to 10 years with Long-term backup retention (in preview) ‘You can use SQL Agent jobs to schedule copy-only database backups as an alternative to LTR beyond 35 days.’ | Policy Driven Backup retention for years |
| Encrypted Backup | All new databases in Azure SQL are configured with TDE enabled by default. | All new databases in Azure SQL are configured with TDE enabled by default. | Afhankelijk van instellingen |
| Backup command | No, only system-initiated automatic backups | Yes, user initiated copy-only backups to Azure Blob storage (automatic system backups can’t be initiated by user) | Yes |
| Restore | point-in-time | point-in-time | point-in-time |
| Storage | General Azure Storage redundancy and location apply | General Azure Storage redundancy and location apply | General Azure Storage redundancy and location apply |
| Performance | vCore based and DTU (Database Transaction Unit) | vCore Based !! Azure SQL Managed Instance does not support a DTU-based purchasing model. | Performance Guidelines |
| SLA | 99,995 % availability | 99,99 % availability | Depending on the setup, Always On cannot be used if domain-joined machines are not in place. |
| Remark | This may not yet apply to many vendors, but it is the most scalable option, equipped with the latest technologies and offloading OS management. | Similar to a regular SQL Server, but without RDP access. | When you’re under pressure to move to the cloud (for example, when hardware support is ending). Or when you want to migrate quickly without making major application changes. |
Versionless*
Versionless SQL is an additional significant difference between IaaS and PaaS. Unlike IaaS, which is tied to a specific SQL Server version, like SQL Server 2019, SQL Database and SQL Managed Instance are versionless. The main “branch” of the SQL Server engine codebase powers SQL Server 2019, SQL Database, and SQL Managed Instance. Although SQL Server versions come out every few years, PaaS services allow Microsoft to continually update SQL databases/instances. Microsoft rolls out fixes and features as appropriate. As a consumer of the service, you don’t have control over these updates, and the result of @@VERSION won’t line up to a specific SQL Server version. But versionless SQL allows for worry-free patching for both the underlying OS and SQL Server and for Microsoft to give you the latest bits.
Additional Findings
Networking and Connectivity
- Virtual Network (VNet) Integration: Azure SQL Managed Instance requires deployment within a VNet. Planning the subnet size is crucial as each instance requires a minimum of 32 IP addresses.
- Private Endpoints (via Azure Private Link): This allows private connectivity from a virtual network to Azure SQL Database and Managed Instance, ensuring traffic remains on Microsoft’s backbone network, enhancing security.
Service Tiers and Performance
- Hyperscale Service Tier: Available for Azure SQL Database, this tier supports databases up to 100 TB and offers rapid scaling, fast backups, and high availability.
Conclusion
Azure SQL continues to evolve, offering a broad range of deployment options to cater to diverse workloads and business requirements. Recent updates include enhanced support for Always Encrypted, expanded In-Memory OLTP availability, improved cross-database query capabilities, and the introduction of data virtualization in Azure SQL Managed Instance.
The strategic use of Private Endpoints through Azure Private Link improves security by ensuring traffic remains within the Microsoft backbone. Additionally, the Hyperscale service tier provides a robust option for high-performance workloads and large-scale databases requiring up to 100 TB.
Keeping up with these advancements and incorporating them into your architecture can ensure optimal performance, security, and scalability.
SOURCES
Features comparison: Azure SQL Database and Azure SQL Managed Instance
