SQL Server On-Premises vs AWS RDS

Compare Microsoft SQL Server On-Premises vs AWS RDS

Features

SQL Server On-Premises

AWS RDS for SQL Server

Deployment

Provision hardware/VMs and install OS and SQL – slowest option, entirely manual (weeks to procure if new hardware).

Launch via AWS console by selecting SQL edition/size – automated provisioning in minutes, no OS install needed.

Management

Fully self-managed by your IT team (complete responsibility for maintenance).

Fully managed by AWS – automated backups, patching during maintenance windows.

Customization

Unlimited – choose any hardware, OS, SQL configuration (most flexible).

Somewhat limited – no OS access, must use parameter groups for tuning; many but not all SQL features availableSomewhat limited – no OS access, must use parameter groups for tuning; many but not all SQL features available.

Use Case

Ideal for strict data control, low-latency to on-prem apps, or existing data center investments; also when compliance or legacy systems mandate it.

Great for simplifying DB management in AWS for standard workloads – companies that want cloud automation and can live within RDS feature limits (trading some flexibility for ease).

Maintenance Responsibility

Your IT team entirely – on-prem requires internal processes for all updates.

Amazon – AWS applies SQL patches during set maintenance windows (or automatic minor version upgrades).

Operational Control

Complete control at hardware, OS, and DB level – no restrictions (can even delay patches indefinitely, for example).

Partial – you get an admin account but not full sysadmin; certain procedures and settings are blocked for stability.

Backups

Completely on user – typically use maintenance plans or enterprise backup tools writing to tapes/cloud.

Automated: daily snapshots + log backups (PITR up to 35 days) are handled by AWS; you can also take manual snapshots for longer retention.

Scalability

Vertical: buy/upgrade hardware (major project); Horizontal: add servers and distribute load (requires significant effort and maybe app changes).

Vertical: modify instance class (reboot); Horizontal: Read Replicas (for Enterprise) allow up to 5 read-only copies (including cross-region); no horizontal write scaling (one primary).

Performance

Potentially the highest (you can use top-end hardware, dedicated storage arrays, etc.); ultimate performance if budget allows, with no virtualization overhead if physical.

Strong performance for most workloads but with some cloud overhead: RDS uses EBS storage (e.g., gp3 or io2 volumes up to 64k+ IOPS), and you can choose instance types with more memory/CPU. Very large workloads or those needing special hardware (like NVMe) might not fit in RDS’s offerings as of now.

Elasticity

None – capacity is fixed once purchased. Scaling requires planning and manual execution (or new hardware purchase).

No automatic compute scaling – the instance class is fixed. RDS does support auto storage increase, so storage can grow with usage. For compute, one could use AWS Application Auto Scaling for RDS in some cases to trigger a bigger instance on schedule, but it involves a reboot – so essentially not seamless auto-scale.

SQL Server Version

Any version/edition for which you have media and license – including very old versions (some orgs still run 2008 or 2005 for legacy apps, which on-prem allows, albeit unsupported by MS).

RDS supports multiple SQL Server versions (as of 2023, versions 2012, 2014, 2016, 2017, 2019, and now 2022 are available on RDS) – you choose a major version when launching, and AWS handles the minor version patching. However, older versions eventually get deprecated for new instances.

SQL Feature Support

Everything SQL Server offers is available if you set it up – including things like replication, Linked Servers, Service Broker, etc. You have to configure them, but nothing is disabled by a provider.

Most SQL Server features are supported, but RDS lacks extensibility: no FILESTREAM, PolyBase, ML Services, CLR (2017+), or xp_cmdshell. Some server-level settings can’t be changed after launch.

Cross-Database Queries

Yes, common on on-prem to have many DBs on one server and join them. Also, cross-server via linked server is possible.

Yes, if multiple databases are on the same RDS instance, you can cross-query them. You cannot cross-query between different RDS instances directly (no official linked server support for cross-instance, since you lack sysadmin to configure it, though heterogeneous links might be possible in some cases with limitations).

Custom Software

Yes – common to have monitoring agents (SCOM, etc.) on SQL servers, or backup agents. You could even run a third-party application on the same machine (though best practice is to separate, it’s up to you).

No – cannot access the OS. Even enabling certain SQL features that require custom action is not possible. For example, you cannot install a custom certificate for SQL Server at the OS level (RDS manages the certificates). You rely on what AWS provides out-of-box.

Network Configuration

On your corporate network – ultimate control (your own firewall, switches, VLANs). You can make it as open or isolated as you need. Typically behind corporate firewalls and accessible only within company network.

Deployed in your AWS VPC – you choose subnet group (which maps to subnets in different AZs for Multi-AZ). Usually kept in a private subnet; you control access via security groups and route tables. Option for a public endpoint exists (mainly for development or if you have no VPN).

Security

Entirely on you – from physical security of the server room, to network security, to OS and SQL security. You can achieve very high security (even completely air-gapped networks), but it requires strict processes. Encryption (TDE, etc.) must be configured by you.

RDS encrypts data, logs, and backups using AWS KMS, supports SSL for in-transit encryption, and integrates with AWS Directory Service for Windows Authentication. AWS manages OS security patching, and RDS meets compliance standards like SOC1 and PCI DSS.

Authentication

Windows Auth and SQL Auth – often tightly integrated with on-prem AD. You can enforce AD group policies, etc. If not on a domain, SQL Auth is used. No cloud-specific auth out of the box (though you could integrate Kerberos and AD FS if needed for fancy setups).

Supports SQL authentication and Windows Authentication via AWS Managed Microsoft AD, allowing domain-joined AD users to log in. Does not support Azure AD or IAM authentication for SQL Server.

High Availability

Many options but all manual: Failover Cluster Instances (with shared storage like SAN), Always On AG, database mirroring (old), log shipping as quasi-HA. Requires redundant hardware and networking. You manage failover process (though AG can auto-failover).

RDS Multi-AZ maintains a synchronous standby replica in another AZ, enabling automatic failover (30-60s) during outages. The application reconnects to the same endpoint seamlessly.

Disaster Recovery

Entirely on you: could be as robust as a secondary data center with log shipping or AGs, or as basic as offsite backup tapes. DR testing and execution are your ops team’s duty.

RDS Enterprise Edition supports Cross-Region Read Replicas for DR and read scaling, but writes require manual promotion. Non-Enterprise users rely on manual snapshots for cold standby, as automated cross-region failover isn’t built-in.

Pricing

Big upfront CapEx for hardware & licenses, then minor OpEx (power, cooling). Amortized 3-year cost for an 8-core server might be ~$1-3k/month depending on edition (Standard vs Enterprise), but you own the asset. Scaling requires new investment.

RDS for SQL Server charges hourly based on instance class, storage, and I/O. Pricing includes the SQL license, making it costlier than MySQL/Postgres. A db.m5.2xlarge (8 vCPU, 32GB) costs ~$3/hour, while Multi-AZ roughly doubles the price.

Licensing Model

Perpetual or Subscription licenses. Typically purchase per core (Enterprise ~$7.5k/core, Standard ~$1.9k/core) plus optional SA ~25%/yr. Hardware bought or leased separately. No builtin pay-per-use; however, you could run SQL Developer or Express free in non-prod.

RDS for SQL Server is License-Included only (except RDS Custom); BYOL is not supported. Costs include SQL Server CALs or per-core licensing, with discounts available via Reserved Instances (1-3 years). Express/Web editions are cheaper but limited.

Pricing Comparison of Database Configuration

8vCore + 4TB Data size + Backup,  Single Instance

On-prem not monthly billed, but roughly ~$1k (Std) / ~$3k (Ent) per month value when spread over a few years.

A db.m5.2xlarge (8 vCPU) RDS SQL Standard instance with 4TB General Purpose storage in US East costs $2.2k–$2.5k/month on-demand ($3.00–$3.50/hour), including the SQL license. Running the same on EC2 (BYOL) is cheaper but lacks RDS’s management benefits.

8vCore + 4TB Data size + Backup + DR/HA

Multi-site on-prem: requires second set of hardware (and possibly second license if active-active).
Hard to monthly-ize, but essentially 2× hardware cost; SQL license for passive is free with SA.

The same 8 vCPU Multi-AZ (HA enabled) instance costs $4.4k–$5k/month due to a standby replica. Adding a cross-region read replica increases the total to $7k+/month. Costs can be reduced with Reserved Instances or Standard edition instead of Enterprise.