AWS offers two main options for deploying SQL Server in the cloud – Elastic Compute Cloud and Relational Database Service. I lately read a few blogs and did some searches with both the types of deployment of SQL Server and learnt a few things, in this blog, I am sharing my findings
Relational Database Service
AWS RDS offered a Database as a Service (DaaS). It removed the complete complexity of installing, configuring the database.
All instance options exposed by AWS are configured on the RDS dashboard. Options such as license model, port number, domain membership, authentication mode, backup settings etc. can be modified directly on the RDS Dashboard.
Elastic Compute Cloud
AWS Elastic Compute Cloud (EC2) is the virtual machine in the cloud, instance offered as Infrastructure as a Service (IaaS). Admin can choose the AMI from the community or marketplace and create a new instance. There are pre-installed SQL server AMIs are available with the license in the marketplace.
Admins will have the full access to the database server include the operating system, port configuration, firewall etc and have full freedom to choose their own tools to manage the database server.
RDS vs EC2
When deciding whether to use SQL Server on EC2 or SQL Server RDS one has to think about cost, scale and the required features.
Operating system access
In EC2, admins will have full access to the host as it is an IaaS, admins can configure or tweak the operating system for their needs.
On the other side, OS Access is not permitted when using RDS. This has a few implications, one of which is that in case you need to migrate databases, you will not be able to use native backups written to the server. You will need to configure Amazon S3 to achieve this. Amazon’s S3 has options for backing up databases to a bucket and restoring from a bucket to an RDS instance.
Native Backups
In EC2, admins can execute PowerShell commands or SQL commands to take the native backups, admins can use VSS provider to invoke the application consistent shadows
In the other hand, RDS is provided as managed service so the shell access to the host is not available, Administrative privileges are restricted to all users,
In effect, the master user, which is configured when the instance is deployed, is given db_owner privileges on ALL databases. However, privileges required for backing up databases natively are not granted. To accomplish the earlier mentioned backup to Amazon S3, it is necessary to configure an IAM role and use the same to configure an Option called SQLSERVER_BACKUP_RESTORE to the option group you have configured for your instance.
Replication
In EC2, admins can bring their own license and configure the replication. AWS RDS does not support SQL Server replication. If your intent is to replicate something from your on-prem to the AWS instance, then your options are to use a Windows Server base instance with your own SQL Server ISO/license, or to use a pricier EC2 AMI based on SQL Server.
RDS has its own replication system that will support replication to another RDS instance (even on a different availability zone) to increase resilience or automate failover.
Admins can use BCP
Administration
In EC2 admins have full administrative access and full flexibility to customize the environment, but for the complex setup, you may need database experts to set up and manage, but in RDS it is a managed service, no need of expert knowledge to set up and use
Other features which are not supported by RDS
- Stretch database
- Backing up to Microsoft Azure Blob Storage
- Buffer pool extension
- BULK INSERT and OPENROWSET(BULK…) features
- Data Quality Services
- Database Log Shipping
- Database Mail
- Distribution Transaction Coordinator (MSDTC)
- File tables
- FILESTREAM support
- Maintenance Plans
- Performance Data Collector
- Policy-Based Management
- PolyBase
- Machine Learning and R Services (requires OS access to install it)
- Replication
- Resource Governor
- Server-level triggers
- Service Broker endpoints
- T-SQL endpoints (all operations using CREATE ENDPOINT are unavailable)
- WCF Data Services
Summing-up
I would recommend using EC2 SQL instances for more serious projects requiring a wide range of SQL Server features and RDS for simple deployments or cases where cost is a big issue.
References:
Also published on Medium.
