Comparing AWS SQL Server With Azure SQL Database

Both offerings are considered Platform-as-a-Service (PaaS) implementations, which by nature imposes certain limitations that we will cover. This article does not review the Infrastructure-as-a-Service (IaaS) implementation of SQL Server. I will compare the services at a high level, providing an overview of their implementation and architecture, T-SQL limitations summary and licensing models.

 

Please note that cloud-based services evolve rapidly; the information provided in this article is valid as of September 2019.

 

A stark contrast in approach to delivering services between the two cloud service giants begins with how they both deliver their SQL Server database services. Microsoft uses the latest binaries available for SQL Server (SQL Server 2019), ensuring that your application always runs on the latest binaries available. On the other end, Amazon offers a mostly fixed versioning model (from 2012 to 2017 at the time of this writing) that requires you to choose which version to use. There are pros/cons to each approach however since some legacy applications cannot always run on more modern database engines, the AWS RDS service gives organizations more flexibility by offering various database engine versions; however this is a departure from a pure Platform as a Service (PaaS) model that helps your organization run on the latest platform, which helps you avoid complex and expensive migrations in the long run, and automatically gives you additional features over time. The Microsoft Azure model drives you into a Virtual Machine implementation if you need to use earlier versions of SQL Server.

 

Availability

 

Conceptually both RDS SQL and Azure SQL DB offer a SQL Server-based hosted service that can be accessed via traditional management tools and applications.

 

From an availability standpoint, the Azure SQL Database provides 99.99% uptime (calculated monthly). Two high availability models are available depending on the service tier you select standard availability and premium availability. The standard availability model separates storage from compute by using blob storage to store data; the actual server running SQL Server simply mounts the blob storage files for the database and log files. The premium availability model implements Always On Availability Groups in which each node (up to four nodes are provisioned) in the cluster contains a local copy of the data, and ensuring that two of the nodes implement a two-phase commit to avoid data loss. It is worth noting that the Azure SQL Database service provides a secondary read replica for the premium availability tier, allowing applications to access a read-only version of a database without impacting the primary read/write database.

 

The AWS RDS SQL Server service offers a tier without availability (with no SLA) and another with a single standby server that AWS RDS can use to failover automatically with a 99.95% availability (calculated monthly). The high availability mechanism used depends on the version of SQL Server selected. The AWS RDS service also does not offer a secondary read replica at this time; reports that need access to real-time or near-time data sets will need to connect to the primary read/write instance. The ability to choose an AWS RDS SQL Server without an SLA seems to provide an interesting cost advantage for development and testing scenarios since pricing for a single instance is about half the price according to the public Amazon pricing page. The Microsoft Azure platform also offers a dev/test pricing model that provides up to 55% savings on license for Visual Studio subscribers. For critical applications requiring high availability and a read replica, the Azure SQL Database offering seems to be providing the best option at this time, both in terms of availability and secondary read replica.

 

 

 

Licensing

 

From a licensing standpoint, both cloud offerings provide a license-included fee model, so that you do not have to purchase a SQL Server license separately. Generally speaking, it is best to use a license-included model since you can easily create and destroy database instances whenever needed; so you only pay to license for the amount of time you have a running database. However, for certain companies, it may be beneficial to leverage a BYOL (bring your own license) model. Only the Azure SQL Database service offers both a license-included model for both offerings and a BYOL model for managed instances if desired. Amazon RDS SQL Server currently does not offer a BYOL licensing model due to restrictions imposed by Microsoft.  You should note that the BYOL model offered by Microsoft can provide some significant savings. 

 

(*) BYOL is only available for vCore pricing 

 

	License included model	BYOL
AWS RDS SQL Server	Yes	Yes (*)
Azure SQL – Single Database	Yes	Yes (*)
Azure SQL – Managed Instance	Yes	Yes

 

Limitations

 

There are specific limitations for both AWS RDS SQL Server and Azure SQL DB. Some of the limitations are inherent to the database version being considered while other features are simply unavailable due to management or security concerns on each platform. It is worth noting that initially, the AWS RDS SQL Server implementation offered the closest compatibility to an on-premise implementation, while Microsoft was offering a pure PaaS implementation specifically designed for cloud-born applications. However, Microsoft also released a version of its PaaS SQL Server offering called Managed Instances, which is designed to compete on feature parity with AWS RDS and offers near 100% compatibility.

 

For a complete list of T-SQL features that aren’t supported by database offering on Microsoft Azure, please see here for Managed Instances and here for Single Databases. And the equivalent documentation (significantly less detailed however) for AWS RDS SQL Server is here.

 

Here is a partial and high level synopsis of the key feature differences in the various offerings for both AWS RDS and Azure SQL Database.

 

(*) Provided by the service; access with T-SQL may be restricted or unavailable

(**) Limited support or depends on service tier selected

 

Feature	AWS RDS	Azure SQL DB (Single)	Azure SQL DB (Managed)
BACKUP command	Yes (*)	No (*)	Yes (*)
BULK INSERT	No	Yes (blob)	Yes (blob)
Change Data Capture	Yes	No	Yes
Column Store Indexes	Yes	Yes (**)	Yes
CLR	Yes (**)	No	Yes (**)
Data Quality Services	No	No	No
Database Mail	No	No	Yes
FILESTREAM	No	No	No
High Availability	Optional (*)	Yes (*)	Yes (*)
Linked Server	Yes (**)	No	Yes (**)
Log Shipping	No	No	No
OPENROWSET	No	Yes	Yes
Resource Governor	No	No	Yes
R-Services	No	Yes (preview)	No
Server-level Triggers	No	No	Yes
Service Broker	Yes (**) – no endpoint	No	Yes (**)
SQL Agent	Yes	No	Yes
TDE	Yes	Yes	Yes

 

Connectivity

 

Both services offer a very different connectivity architecture, with AWD RDS offering a direct connection pattern while the Azure SQL Database leverages a gateway that provides transparent routing or redirection. The AWS connectivity model is straightforward and requires no specific explanation since it mirrors what most developers would expect in a traditional data center design; database instances are provisioned (as Virtual Machines) and the binaries are deployed on the VM. In the AWS RDS environment, each new database instance is deployed in its own Virtual Machine. In the Azure space, however, databases can be deployed on the same server, coexisting with other customer databases with a high degree of isolation ensuring strong security. As a result, the Azure design needs a gateway mechanism to properly identify which database your application is attempting to connect to, and on which server.

 

The Azure gateway design was initially created exclusively for routing purposes (as a proxy), allowing the database service to scale significantly by dynamically routing the database connection to the server hosting the database at that point in time. This connectivity model does not apply to Managed Instances; it is only used for Single Databases service (including elastic pooled databases). From a client standpoint, this means that in the Azure SQL Database world the primary database can be hosted on any server at any point in time; while transparent in nature, this could cause temporary connectivity loss that a simple connection retry would fix. More importantly, this gateway routing architecture can also have a noticeable negative impact on applications that require fast response times.

 

While the gateway proxy design was largely transparent, Microsoft introduced a redirect mechanism that allows clients to opt for a direct database connection instead. The client still connects to the gateway first, but gets redirected to the server hosting the database at that point in time; once connected to the database the gateway is no longer needed. While this is the recommended connectivity mode for performance and lower latency, it also requires that clients modify their internal firewalls to allow connectivity to all Azure IP address ranges and ports between 11000-11999. In comparison, the proxy connection method only requires two IPs per region, on port 1433. The default connection policy is different depending on the location of the client: external clients will connect using the proxy method by default, while internally (within Azure) clients will connect using the redirect method by default.

 

 

Implementing retry logic in application code, on any cloud provider, is important since databases are usually hosted behind multiple firewalls and routers; however, retry logic is likely more important against the Azure SQL Database platform since database servers can be relocated to new hosts at any given time for overall service scalability. While the gateway approach used by the Azure SQL Database service seems extraneous at first glance, it offers some interesting management and scalability capabilities that are only possible when such an architecture is implemented. More specifically it allows the Azure platform to transparently move databases when it is necessary to do so, such as hardware malfunctions, and environment upgrades, in addition to providing a set of capabilities centrally such as firewall configuration and audit logs (you can think of the Gateway as a read-only master database for Azure SQL Database). While the AWS RDS environment also accounts for hardware and environment-related concerns the gateway model provides a high degree of server abstraction.

 

Conclusion

 

As we have seen from this high-level overview, both the AWS RDS and Azure SQL Database services compete on how to deliver the best experience for customers. AWS RDS delivers a more server-oriented model that provides strong backward compatibility for applications that need earlier versions of SQL Server, while the Azure platform provides a dynamic architecture that is always running the latest version of SQL Server and shifting the platform choice from a version decision to a service level decision (single database vs. managed instance).