Problem
As companies move more resources to the cloud, what are the options for running SQL Server from various major cloud providers: Azure, AWS, and Google? In this article, we look at the similarities and differences of what is offered for Azure, AWS and Google Cloud.
Solution
The major cloud providers offer various options for hosting SQL Server from Platform as a Service (PaaS) to Infrastructure as a Service (IaaS). PaaS offerings are fully managed databases that typically include automatic backups, automatic service pack and version updates, easy scalability, easy high availability setup, encryption, and multi-region replicas. With PaaS, you pay for what you use. Most offerings are certified for industry-standard certifications and regulations such as HIPAA, SOC, and ISO.
Cloud providers offer several database platforms, including MS SQL Server, Postgres, MySQL, and others. This article will focus on the features of SQL Server offered by Azure, AWS, and Google Cloud. Note: This tip will not cover the different resource levels, pricing, or high availability details, though each cloud provider has several options. Competition may drive options to change over time.
Summary of Major Cloud Offerings for SQL Server
Next is a summary of the Microsoft Azure, Amazon Web Services (AWS), and Google Cloud PaaS (Platform as a Service) and IaaS (Infrastructure as a Service) offerings for SQL Server.
Microsoft Azure
Azure has various flexible SQL offerings. All options include auto backups, high availability, and dynamic scaling options.
Azure SQL Database
Azure SQL Database is the most basic offering and is a Platform as a Service (PaaS). If you need single, self-contained databases, this is one to consider. Azure SQL Database includes SQL functionality of a single database. Security includes SQL Users and Active Directory Users with Azure Active Directory integration. You get all the PaaS features, including monitoring and automatic backups, tuning, upgrades, and DBCC CheckDB. Azure database backups are geo-redundant, your backups are tested, and DBCC CHECKDB integrity checks are run periodically by Azure technicians as part of the offering. You will never have to worry about end-of-life SQL version support again. With Azure Hybrid Benefits, you can bring your own SQL Server license.
What you don’t get with Azure SQL DB is SQL Server features like cross database queries, linked servers, and SQL Agent.
Azure SQL Managed Instance
Azure SQL Managed Instance (MI) is the next-level PaaS offering. Since this is PaaS, you do not manage the server, the operating system (OS), database files, backups, version upgrades, or tuning. This is a good option when you need a more robust SQL feature or SQL Agent but do not need operating system control and still want to take advantage of PaaS features. Azure SQL MI includes features unavailable in Azure SQL Database like cross database queries, Linked Servers, Service Broker, SQL Agent, database mail, machine learning, and many other features included with typical SQL Servers. Azure SQL MI does include Integration Service with Azure Data Factory. Analysis Servers is a separate Azure service, and Power BI in Azure can be used in place of Reporting Services. With Azure Hybrid Benefits, you can bring your own SQL Server license. Though not all SQL Server features are available with Azure SQL MI, too many features are available to list here. Check the links provided in this article to review more information on Azure SQL MI.
Check out these webcasts:
- Five ways to improve data security on Azure SQL Managed Instance
- Modernize Your Apps with Azure SQL Managed Instance and Unlock the Benefits of the Cloud
- Optimize your costs with Azure SQL Managed Instance
- Set Yourself Up for Performance Success on Azure SQL Managed Instance
- The flexibility of hybrid options with Azure SQL Managed Instance
Azure SQL Virtual Machine
Azure Virtual Machine (VM) is an Infrastructure as a Service (IaaS) option. Azure SQL VM is available in Windows or Linux. This is a good option when you need more control over the SQL Server and access to the operating system. Azure claims high performance and lower cost of ownership with Azure SQL VMs and that it is 100-percent compatible with SQL Server on-premises workloads. Azure SQL VMs can be used to migrate on-premises SQL Servers to the cloud. Another option for Azure VMs is to use it as high availability or disaster recovery for on-premises SQL Servers. Azure Hybrid Benefits allow you to bring your own SQL Server license.
Azure has several other database offerings, including SQL Server, Oracle, COSMOS DB, PostgreSQL, MySQL, MariaDB, Cassandra, and Redis. Azure also offers Azure SQL Edge, an Internet of Things database not included in this comparison but can be found here: SQL Edge | Microsoft Azure.
Here is a reference for more details about Azure SQL offerings: Azure SQL.
Amazon Web Services (AWS)
AWS has various offerings for managed Relation Database Services (RDS), including Amazon Aurora, PostgreSQL, SQL Server, and MySQL.
The three primary options to run SQL Server on AWS follow.
Amazon RDS for Microsoft SQL Server
Amazon RDS for Microsoft SQL Server is a PaaS platform, so the service includes database management tasks, such as hardware provisioning, software patching, setup, configuration and backups. When deploying RDS, there’s an option to select the SQL Server edition (Express, Web, Standard, and Enterprise). At the time of this writing, you can choose to deploy SQL Versions 2014, 2016, 2017, or 2019. You can choose General Purpose SSD or Provisioned IOPS SSD storage if you need more horsepower. With SQL Enterprise edition, you can easily set up readable replicas for high availability and scalability using database mirroring or AlwaysOn Availability Groups. You can manage the SQL databases via SQL Server Management Studio.
Microsoft SQL Server on Amazon Elastic Compute Cloud (Amazon EC2)
Amazon EC2 is an IaaS offering. With EC2, you can choose a SQL image or manage the install, configurations, and backups and have control over the operating system. You have full control of the server. With EC2, you are not restricted to the SQL Server version supported by AWS. EC2 can be used if you have a database size requirement that exceeds those of the RDS offering or need to achieve higher IOPS than RDS. EC2 includes features not available in Amazon RDS, including Log Shipping, Replication, Database snapshots, Extended stored procedures like xp_cmdshell, Data Quality Services (DQS), FILESTREAM, Machine Learning and R Services, Maintenance plans, Resource Governor and many other features that are generally available with on-premises SQL installs.
Check out these webcasts:
- How to be an ACE DBA with Amazon RDS for SQL Server
- Accelerate SQL Server Application Migrations to AWS using Migration Hub Orchestrator
- SQL Server Cloud Optimization Strategies – Costs, Licensing and Infrastructure
- Reduce your SQL Server costs in the Cloud
Amazon RDS Custom for Microsoft SQL Server
Amazon RDS Custom for Microsoft SQL Server is a cross between PaaS and IaaS. You get the automation of RDS, but you also have access to the operating system for more control of custom settings to enable native features. You also have control over installing patches to meet application requirements.
Here is a reference for more details about AWS SQL offerings: SQL Server on the AWS Cloud.
Google Cloud SQL
Google Cloud offers Microsoft SQL Server, MySQL, and Postgres.
Cloud SQL for SQL Server
A fully managed PaaS offering, Cloud SQL for SQL Server includes common features like automatic backups, auto storage increase, and high availability. Also, available features include Active Directory integration, Linked Servers, import and export using BAK files, Replication, SQL Agent, SSIS running on a different host, and more.
Cloud SQL does not include log shipping, Database Mail, DTC, FILESTREAM, access to system stored procedures, and other server-level features. Google does not use TDE encryption, though Google does encrypt all data at the storage layer using the Advanced Encryption Standard (AES) algorithm, AES-256. To manage the SQL instance, you can use SQL Server Management Studio and the sqlcmd utility.
Google offers two performance or pricing tiers, Cloud SQL Enterprise and Cloud SQL Enterprise Plus, based on CPU and memory resource requirements. Google also has various storage options.
Self-Managed SQL Server VM
Google’s SQL IaaS offering is Self-Managed SQL Server VM images on Google Cloud Compute Engine. With this, you can bring your own license or use pre-configured SQL VMs. The current offering is fully featured SQL Server 2019 Standard or Enterprise edition, although SQL Server 2022 is the currently generally available at the time of writing. SQL Server Management Studio installed on the VM or your workstation can be used to manage the SQL Server.
Here is a reference to more details about Google SQL offerings: SQL Server on Google Cloud
Side-by-Side Comparison of Major Cloud Offerings for SQL Server
The chart below compares the major cloud providers’ offerings for SQL Server.
Features of PaaS Offerings
| PaaS Features | Azure | AWS | Google Cloud |
|---|---|---|---|
| Bring Your Own License | Yes | Yes | Yes |
| Single Database Tier Offering | Yes | ||
| PaaS SQL Server Offering | Yes | Yes | Yes |
| VM SQL Install Option (IaaS) | Yes | Yes | Yes |
| Windows Active Directory Integration | Yes | Yes | Yes |
| Automatic Backup | Yes | Yes | Yes |
| Auto Performance Tuning | Yes | Yes | Yes |
| Easy Scaling Resources | Yes | Yes | Yes |
| Auto Pause to Save Costs | Yes | ||
| Mirror, Replicate, HA | Yes | Yes | Yes |
| Select Various SQL Versions | Yes | ||
| Select Various SQL Editions | Yes | Yes | |
| Encryption | Yes | Yes | Yes |
Platform Differentiating Features
While many features are available, here are some differentiating features by platform.
| Platform | PaaS | IaaS | Single DB Tier | SQL Agent | Auto Backups | Diff SQL Versions | SSIS | Extended SPs | Time Zone Control |
|---|---|---|---|---|---|---|---|---|---|
| Azure SQL DB | Yes | Yes | Yes | ||||||
| Azure SQL MI | Yes | Yes | Yes | ||||||
| Azure VM | Yes | Yes | Yes | Yes | Yes | ||||
| AWS RDS | Yes | Yes | Yes | Yes | Yes | ||||
| AWS RDS Custom | Yes | Yes | |||||||
| AWS EC2 | Yes | Yes | Yes | Yes | Yes | Yes | Yes | ||
| Google SQL | Yes | Yes | Yes | Yes | |||||
| Google VM | Yes | Yes | Yes | Yes | Yes | Yes |
Wrap Up
To summarize:
Azure Managed Instance, AWS RDS, and Google Cloud SQL for SQL Server are very similar PaaS offerings where you do not have OS level access, and the provider takes care of tasks like backups, updates, service packs, encryption, and some level of redundancy. They can be scaled (CPU, Memory, Storage, Throughput) or add a secondary copy in a different region easily with a few clicks.
Azure VM SQL, AWS EC2, and SQL on Google Cloud Compute Engine are similar IaaS offerings you manage at the OS level.
AWS RDS Custom is a unique combination of services offered by AWS.
Azure SQL DB stands alone as a limited PaaS offering at the database level if self-contained databases meet your needs and you do not need SQL Agent and other advanced SQL Server features.
References
- Azure SQL Family: Azure SQL – Family of SQL Cloud Databases | Microsoft Azure
- AWS SQL comparing the feature of RDS & EC2: https://docs.aws.amazon.com/sql-server-ec2/latest/userguide/sql-server-on-ec2-options.html
- Google SQL Cloud: https://cloud.google.com/sql-server/ and https://cloud.google.com/sql/docs/sqlserver/features
Next Steps
- View the references above for more details of each cloud provider’s SQL offerings.
- Review MSSQLTips.com articles on the cloud:
