In a previous installment (Blog Part 1), I went over a high-level description of SQL Server 2022 and what new features came with its release. Afterwards, a comparison was made to help readers understand what upgrading to 2022 would look like against an alternative solution, migrating to Azure SQL. Now, I will go into detail on the feature parity between the two solutions to help your understanding of how feasible and desirable this migration will be. There are some features available in SQL Server that Azure SQL does not natively support, but I will mention other light-weight Azure tools that can achieve the same thing. Finally, I will discuss the cost implications of both solutions, and by the end, you should be able to decide if a migration to Azure SQL is the right choice moving forward.
While Azure SQL is touted as the source of origin for new SQL Server features, at the end of the day, Azure SQL is still a subset of SQL Server, and not every feature around hyperscale processing and storage has been able to make its way to the cloud. I will continue to directly compare the two services though, as the lacking features are mostly a concern of massive-scale businesses, and fortunately, the Azure ecosystem has an answer for any feature discrepancies between the two. Let me abstract where Azure SQL Database fits in the RDBMS picture before stepping through differences between the software.
It can be seen from the image that by migrating, the need for active administration is reduced and can lower costs overall, or at the very least, the ability of more modularity to control the costs expected is gained. As companies shift to a Cloud-based or hybrid solution, the door opens to shift high-value employees to more business-critical tasks that create company value as opposed to having them monitor complex on-prem infrastructures and their maintenance.
Let me do a side-by-side comparison of the features that differ between the two:
Feature Parity Between Azure SQL DB and SQL Server 2022
| AZURE SQL DATABASE | SQL SERVER 2022 |
| Most used SQL Server features are available. | All SQL Server features are available. |
| 99.995% SLA Availability agreement with MSFT. | % Availability variable depending on On-Prem architecture design (determined internally). |
| Built-in and automated Backups, Patching, and Recover. | Backups, Patching, and Recovery must be manually designed in a high-availability solution. |
| Always on the latest stable database engine version. | Must manually change database engine versions as required. |
| CPU and Storage characteristics of infrastructure powering Databases can be manipulated on demand and via mobile devices. | CPU and Storage characteristics of infrastructure cannot be manipulated without purchasing and removing the server hardware. |
| Auto scaling provided as a toggleable feature that does not require configuration outside of which metrics trigger it. | On-demand scalability is extremely unfeasible. |
| Ability to pause the computational power and only incur cost for storage. | Unable to pause database state, always on infrastructure. |
| Built-in Advanced Intelligence and Security (Firewalls, VNET rules, Authentication/Authorization management, Row Level Security, Advanced Threat Protection, Transport Layer Security and TDE). The majority of configuration is handled by MSFT or already enabled. | Query Intelligence introduced in 2022, Some built-in security features such as Encrypted Communications over SSL/TLS, DPAPI for encrypting data at rest, and authentication/authorization. Up to DBA to configure these features, must be manually enabled. |
| Ability to manipulate DB resources on mobile devices. | DBA Required On-Prem for updates. |
| Databases up to 100TB. | Databases up to 524,272 TB (Restrictions mostly limited by storage space obtained during data center design). |
| Able to store databases from multiple tenants. | Database only able to store instances from the on-premises setup. |
| Database Software automatically configured, patched, and upgraded by Azure. | Manual Configuration, Patching, and Upgrading. |
| Simple availability to resources with ability to guard access behind virtual networks. Default communication is secure and requires no additional configuration. | Complex process involved in Server Network configuration. Protocol enablement, encryption, DB Engine exposure or hiding, Service principal name registration and configuring the SQL Server Browser service are all required for a strong security posture. |
| Pricing Calculator accessibility for cost predictions. | Fixed Licensing Yearly cost / Manual calculations required on on-premises hardware to understand cost possibilities. |
| Ability to Leverage Elastic Pools Multiple DB Instances. | 1 Database Instance. |
| OS Agnostic (while it is operated from an OS, clients never see it or worry about it). | Windows/Linux. |
| Built-in and automated Backups. | Database Snapshots. |
| Store Procedures / Common Language Runtime. | Stored Procedures / Extended Stored Procedures. |
| – | Semantic Search. |
| DDL/DML/LOGON Triggers. | All Triggers. |
| No Partitioning. | Horizontal Partitioning. |
There are many direct-comparison winners on the Azure SQL side, but there are some winners on the SQL Server side to be addressed. Most features in which SQL Server stands a step above relate to its ability to support databases of massive scale while having the ability to query them efficiently. The cloud is still relatively new, and it is hard to achieve the same results as an on-prem warehouse storing endless server stacks with the newest hardware powering them, or even a VM replicating a similar concept. That leaves users with differences in their DB max size as well as the partitioning methods available to organize them. In addition to this, Azure SQL only supports DDL, DML, and LOGON triggers and does not support many older-version SQL Server trigger arguments such as ALL SERVER and WITH ENCRPYTION arguments. For a full overview of supported trigger arguments, please visit: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16.
I have brought attention to some pitfalls of Azure SQL, but as a testament to Microsoft’s commitment to the Azure ecosystem, Azure does have ways to accommodate the discrepancies. Confining myself to an Azure-SQL-only solution, an alternative approach involves scaling out the cloud solution to have more DB instances to circumvent the 100TB Database limit. Recall from above, with Azure SQL I can host multiple databases by simply requesting more. For people who believe that scaling out to multiple instances of Azure SQL is undesirable, Azure does have the ability to host a SQL Server instance on the lowest cost VM’s available on the market for the task, and clients still gain the benefit of being integrated into Azure for its native security, monitoring and scaling capabilities. More info on this approach can be found here.
For sanity, included below is an overview of where the Database management systems share feature functionality to reassure clients that standard RDBMS needs are met by Azure SQL.
| Feature Commonalities between SQL Server 2022 and Azure SQL |
| ACID guaranteed transactions |
| Concurrency & Durability |
| JDBC/ODBC API Integration |
| Data Scheming |
| Data Typing |
| XML/JSON Support |
| Secondary Indices |
There are a few features of SQL Server that do not have direct feature comparison abilities to something in Azure SQL; however, each of these features can have their functionality achieved and even surpassed by other tools that are available in the Azure Ecosystem. These tools do incur cost, but there is a modular nature to Azure pricing that reassures clients that they are paying for what they need, as opposed to often paying for more than what is needed with a SQL Server license. I will discuss the pricing model in more depth later in this blog.
| Azure Service options for common SQL Server 2022 Features | |
| Azure Solution | SQL Server 2022 Feature |
| Azure Monitor ($) | Data Auditing |
| Azure Data Factory SSIS Integration Runtime ($) | SSIS (SQL Server Integration Services) |
| Power BI ($) | SSAS (SQL Server Analysis Services) |
| Power BI ($) | SSRS (SQL Server Reporting Services) |
| Azure Synapse Analytics Dedicated SQL Pools or Polybase for Azure Data Factory ($) | PolyBase |
Pricing information on the following tools may be found here:
https://azure.microsoft.com/en-us/pricing/details/monitor/
https://azure.microsoft.com/en-us/pricing/details/synapse-analytics/
https://azure.microsoft.com/en-us/pricing/details/data-factory/data-pipeline/
I have now touched on most of the features that people would be concerned about losing after a software migration. Although Azure SQL cannot deliver the same promises on hyperscale qualities, most enterprise projects can be completed under the Azure SQL umbrella, and it has been made clear that Azure has multiple ways of accommodating these types of projects outside of Azure SQL’s scope. If Azure SQL has checked all the boxes thus far, all that is missing is more understanding around the costs that it will incur. Let me step through a comparison of cost using SQL Server vs Azure SQL.
Pricing is described in terms of SQL Server 2019, as the 2022 pricing model has not been finalized while SQL Server 2022 is in public preview. Azure SQL Pricing images follow the SQL Server pricing.
SQL Server 2019 Pricing
SQL Server Recurring Pricing
Azure SQL Pricing
Azure SQL Storage Pricing
Azure SQL Backup Storage Pricing
Walking through the previous graphics, SQL Server Enterprise includes an upfront cost of $13,748 per core and will follow up with a yearly subscription fee of $5,434 a year. SQL Server Standard has an upfront cost of $3,586 per core and a recurring $1,418/year subscription fee. Additional costs that must be considered relate to the hardware or software that is hosting the server, as well as the employees who monitor them. As previously mentioned, SQL Server is often hosted on a virtual machine, so the physical hardware is not on-premises. Those environments usually involve runtime costs, as well as throughput costs for communications to and from the VM. If VMs are not being used, physical hardware costs must be considered alongside upkeep and networking costs associated with a physical data center. Air Conditioning is needed to keep those computers cool, and employees must be assigned to ensure the servers are constantly running. This implies variable costs as well as a ton of overhead relating to making sure all these pieces are constantly active and moving as required in terms of employees, datacenter environment and the server hardware stacks themselves. The large upfront costs involved in initializing a SQL Server infrastructure, or to upgrade existing hardware and licenses to meet new needs, combined with efforts required by the company to have all the pieces in place for a SQL Server approach are often disincentivizing enough to lead people towards alternative solutions.
This all contrasts to the cloud Azure SQL Model, where pricing is more pay-as-you-go and fees can be locked in and reduced by paying for multiple years of commitment. In the SQL Server scenario, it is understood that any physical hardware would work for years to come, so the multi-year commitments in this Azure SQL scenario should not be viewed as trying to lock customers into a contract but rather should be viewed as understandable cost-saving measures. It can also be seen alongside this monthly payment model, that clients can be flexible with the resources powering their SQL solution. If I am a low-traffic customer, I am not stuck with an over-compensating licensing cost that is meant to encapsulate larger sized companies with dissimilar needs. I can pause computation support for databases and only costs for storage will be incurred for additional cost savings measures. Storage is also based on a monthly charge completely dependent on the amount of data stored. This pattern stays true for backup storage. This type of pricing model allows me to utilize the idempotent MSFT Azure cost calculator for our resources so I can understand the cost over the next few years to come and properly budget in a predictive fashion, this is not possible when I host my own SQL Server solution.
For Azure SQL in the most expensive scenario, clients can leverage 10 vCores and 51GB of memory on a pay-as-you-go model. It initially seems as if it is more expensive than a SQL Server license, even more so if storage and backup costs are tacked on. I would be looking at $26,000 a year for a fraction of the potential of database sizing. However, recall that with this model there is absolutely no need for recurring VM hosting costs, there is reduced need for Database Administrators to spend their high hourly rate on backups and infrastructure, and there is no need to worry about variable monthly utility costs that come with keeping everything running. The $26,000 a year tag associated with Azure includes the subject matter experts required to administer and keep these databases efficient, the infrastructure powering it all, and nearly all other configuration costs thanks to it being a SaaS offering. These points expose the difference in pricing. For example, in an on-prem solution, I know that I would require an employee to manage the solution and those salaries can range anywhere from $100,000/year to $200,000 a year. Associated with this is knowing that database administration is a full-time job, so it is hard to imagine much will come from that employee other than keeping the DB running in all aspects. If I were to shift to the $26,000 a year model with Azure SQL, I can explore having our high-value employee deliver more-direct business value instead of maintenance. I can also spend less time figuring out hardware specifications and tuning parameters, and more time developing apps and solutions. Migrating to the cloud keeps costs low, predictable, and straight forward while letting employees focus on more enjoyable and profitable tasks.
I have now detailed a direct comparison of features between SQL Server 2022 and Azure SQL as well as a comparison of costs to expect with either option. I have highlighted that while in some specific scenarios SQL Server seems necessary, Azure has ways to achieve the same thing and more. I highlighted minor built-in functionality that is lacking in Azure SQL when compared to SQL Server but offered other Azure tools that could be easily integrated with the Azure SQL Solution to achieve the same thing. With this knowledge, it should be clearer why Azure SQL Adoption has grown exponentially over the past couple of years and will continue to do so.
SQL Server should still be viewed as a viable solution after gaining an understanding of the alternatives. SQL Server will continue to receive upgrades and provide the necessary services people are used to, but Microsoft will continue to highlight that its newest features originate in its Azure ecosystem. Deprecation of software is difficult to handle, and there are always unavoidable issues involved with on-premises setups in relation to many things. Instead of handling this, positioning an organization on a bleeding edge technology opens the door for more highly scalable and flexible solutions that companies are realizing and believing in more over time, but, if a company is on a tight schedule or if more-dedicated upgrade discussions are planned for down the road, then a SQL Server 2022 upgrade may be worthwhile in the interim.
As read throughout the blog, if a company is hoping to complete more of an overhaul of the company data architecture with a goal of increased efficiency and data quality, then a migration to Azure SQL would be best. Azure SQL is also the best option when aiming to cut costs or reorganize workforce efforts from maintenance control to business impact, while also being able to embrace emerging solution concepts relating to AI and ML. SQL Server only stays as the best option if a company needs something quick and familiar or if the database size limits previously described are necessary – and even then, staging a server on Azure VM’s should be considered in the path forward. If any concerns still linger on how realistic it is to see a solution come to life on the Azure ecosystem, Microsoft provides highly detailed documentation on all this software and their documentation always provides alternatives to features people require.
