SQL Server on AWS: RDS, EC2, Aurora PostgreSQL and Babelfish compared
Compare features, high availability, backups, tooling and licensing for SQL Server workloads across RDS for SQL Server, RDS Custom, SQL Server on EC2, Aurora PostgreSQL and Babelfish, and scan your T-SQL for blockers before you choose.
Assumes: .NET 10 LTS · Microsoft.Data.SqlClient 6.x · AWS CLI v2 · RDS for SQL Server 2016–2025 · Region us-east-1. Verified against official sources on 2026-11-18.
Learning objectives
By the end of this lesson you can:
- Place each SQL Server hosting option on AWS on a control vs convenience spectrum: SQL Server on EC2, RDS Custom for SQL Server, RDS for SQL Server, Babelfish for Aurora PostgreSQL, and a full move to Aurora PostgreSQL or RDS for PostgreSQL.
- Compare them side by side on features, high availability, backup and restore, tooling, security and licensing.
- Name the SQL Server features that RDS does not support (for example FILESTREAM,
xp_cmdshell, replication, maintenance plans), and say which ones are blockers for a typical bank estate. - Explain the licensing models: License Included and Bring Your Own Media (BYOM) on RDS, and licence-included or BYOL on EC2. Explain why licence cost often decides the architecture.
- Run a small C# readiness scanner over a folder of T-SQL scripts to find RDS blockers and Babelfish risks before anyone signs a migration plan.
Prerequisites
- Choosing a database: SQL vs NoSQL on AWS
- Working knowledge of SQL Server: Always On availability groups, backups, SQL Agent, logins vs users
- Tools: .NET 10 SDK. Optional: Docker for the local SQL Server exercise
Why this matters in banking
Harbourline runs about 40 SQL Server databases on-premises: the core banking ledger, the payments hub, a data mart, and a long tail of departmental applications. Each one has a history: CLR assemblies for IBAN validation written in 2012, a linked server to an Oracle risk system, nightly SSIS packages, and FILESTREAM for scanned customer documents.
A consultant's slide says "Lift and shift everything to Amazon RDS for SQL Server. Fully managed!" Three months later:
- the CLR assembly will not load on SQL Server 2019 or later on RDS;
- FILESTREAM is not supported;
- the SSIS packages cannot run on a SQL Server 2025 RDS instance;
- the licence-included bill for Enterprise edition is larger than the compute.
None of this is unusual. The fix is a comparison done before the decision: which hosting option supports which feature, what you give up for "managed", and what each option costs to license. This lesson builds that comparison and a tool to gather the evidence.
The idea in plain language
There are five broad ways to run a SQL Server workload on AWS. They trade control against convenience:
- SQL Server on Amazon EC2: you run Windows and SQL Server on a virtual machine. Everything works as on-premises, including
sysadmin, FILESTREAM, CLR and log shipping. You also do all the patching, backups and HA plumbing yourself. - Amazon RDS Custom for SQL Server: a managed RDS instance where you also get OS and
sysadminaccess, for applications that need customisation. A middle ground with its own limits. - Amazon RDS for SQL Server: AWS runs the OS, patching, backups, point-in-time restore and Multi-AZ. You get the
db_ownerrole on user databases, notsysadmin. Some features are missing. - Babelfish for Aurora PostgreSQL: the database is PostgreSQL underneath, but it speaks SQL Server's network protocol (TDS) on port 1433 and understands much of T-SQL. Applications may connect with few changes. Coverage is incomplete, so you must test.
- Aurora PostgreSQL or RDS for PostgreSQL (full conversion): convert schema and code to PostgreSQL. This needs the most migration effort and gives the lowest running cost (no SQL Server licence).
Analogy: Moving the bank's archive to a new building. EC2 is renting an empty floor and bringing your own shelves, staff and security guards. RDS for SQL Server is renting a managed archive service: they run the building, the fire suppression and the nightly backups, but you can't drill holes in the walls. Babelfish is a modern archive whose staff speak your old filing language well enough for most requests. Full conversion is re-filing everything into the new building's system: hard work once, cheaper every year after.
What problem each option solves
| Option | What it solves | What you give up |
|---|---|---|
| SQL Server on EC2 | Full compatibility; any edition, feature or third-party agent | You patch, back up, cluster and monitor. Highest operational load |
| RDS Custom for SQL Server | Managed backups and automation plus OS/sysadmin access for apps that need it | More responsibility than RDS (you can break the automation); fewer features than EC2 ⚠ VERIFY feature list for your use case |
| RDS for SQL Server | Managed patching, automated backups and PITR, Multi-AZ with Always On AGs or mirroring, read replicas (Enterprise) | No sysadmin, no OS access, and an explicit list of unsupported features |
| Babelfish for Aurora PostgreSQL | Keep T-SQL clients and much code; lose the SQL Server licence; gain Aurora's storage and failover | Incomplete T-SQL coverage; behaviour differences; two dialects to understand |
| Aurora / RDS for PostgreSQL | Lowest licence cost; open-source ecosystem; IAM database authentication | Schema, procedures and application SQL must be converted and retested |
Features RDS for SQL Server does not support
AWS publishes the list. Checked 2026-11-18, it includes:
- FILESTREAM and FileTables
- Extended stored procedures, including
xp_cmdshell - SQL Server replication
- Database log shipping
- Maintenance plans (use RDS automated backups and SQL Agent jobs)
- PolyBase and Stretch Database
- Server-level triggers
TRUSTWORTHY(it requiressysadmin)CREATE ENDPOINT, including Service Broker endpoints- Buffer pool extension, Policy-Based Management, Data Quality Services
- Database snapshots (RDS supports instance snapshots only)
- Backup to Azure Blob Storage
There is also limited support:
- CLR works only in
SAFEmode, and only on SQL Server 2016 and earlier. It is not supported on 2017 and later. - Linked servers have limited support, including Oracle OLEDB.
With SQL Server 2022 on RDS, SSAS is not supported, and Multi-AZ uses Always On only (no database mirroring). With SQL Server 2025 on RDS, SSIS is not supported either.
These lists change. Always re-check the AWS page before you commit.
Other hard limits worth knowing
- Storage: General Purpose SSD up to 16 TiB; Provisioned IOPS up to 64 TiB (all editions).
- Databases per instance: 30 on micro-to-large classes. On xlarge and above: 100 single-AZ, 75 with Always On Multi-AZ, 50 with mirroring.
- Read replicas: Enterprise edition only, up to 15 per source. The instance class needs at least 4 vCPUs.
- Instance quota: by default 10 license-included instances per SQL Server edition per Region (adjustable).
- Web edition is licensed by Microsoft only for public, internet-facing web workloads. It is not a cheap way to run an internal banking database.
Microsoft / Azure comparison
This lesson is the comparison, so here it is in one table. SQL MI means Azure SQL Managed Instance.
| Capability | On-prem SQL Server | Azure SQL MI | RDS for SQL Server | SQL Server on EC2 | Babelfish (Aurora PG) | Aurora / RDS PostgreSQL |
|---|---|---|---|---|---|---|
| Admin level | sysadmin | Near-full | db_owner per database, no sysadmin | sysadmin | PostgreSQL roles mapped to T-SQL logins | PostgreSQL roles (rds_superuser on RDS) |
| OS access | Yes | No | No | Yes | No | No |
| HA in one Region | Always On AGs / FCI | Built in | Multi-AZ (Always On AGs; mirroring on older versions) | You build AGs/FCI | Aurora replicas, failover usually well under a minute ⚠ VERIFY | Multi-AZ instance or cluster; Aurora replicas |
| Readable secondaries | AG secondaries | Yes (BC tier) | Read replicas (Enterprise) | AG secondaries | Aurora readers | Read replicas / readers |
| Backups | Native .bak, maintenance plans | Automated + PITR | Automated snapshots + PITR; native .bak to/from S3 via an option | You run them (to S3 or AWS Backup) | Aurora continuous backup + PITR | Automated + PITR |
| Agent jobs | SQL Agent | SQL Agent | SQL Agent | SQL Agent | No SQL Agent; use pg_cron or external schedulers ⚠ VERIFY | pg_cron extension or EventBridge Scheduler |
| SSIS / SSRS / SSAS | Yes | No (use ADF / Power BI) | SSRS supported via option; SSIS and SSAS depend on version (none on 2025) | Yes | No | No |
| CLR | Yes | Yes | 2016 and earlier, SAFE only | Yes | No | No (use extensions / app code) |
| Windows authentication | AD | Entra ID / AD | AWS Managed Microsoft AD or self-managed AD | AD | Kerberos via AD ⚠ VERIFY | Kerberos via AD; IAM database authentication |
| Encryption at rest | TDE | TDE | KMS storage encryption + optional TDE | EBS encryption + TDE | KMS | KMS |
| Licence | Your own | Included (vCore) or Azure Hybrid Benefit | License Included or BYOM | License Included AMI or BYOL (Dedicated Hosts) | None (PostgreSQL) | None |
Big differences for a .NET banking developer:
- On RDS you are never
sysadmin. Server-level tasks become RDS stored procedures (for examplemsdb.dbo.rds_backup_databasefor native backups to S3) or console/API settings (parameter groups, option groups). - Option groups turn on features such as TDE, native backup/restore, SSRS and MSDTC. Parameter groups replace
sp_configurefor most settings. - IAM database authentication works for MariaDB, MySQL and PostgreSQL only, not SQL Server. SQL Server on RDS uses SQL logins (credentials in Secrets Manager) or Windows authentication through Active Directory.
Architecture
The five options on the control/convenience spectrum:
A typical target architecture for Harbourline mixes them. The ledger moves to PostgreSQL, a vendor package that requires SQL Server stays on RDS, and a legacy app with FILESTREAM stays on EC2 until it is retired:
Security boundary: all four stores sit in private subnets. They are encrypted with customer managed KMS keys, and their credentials are never in application configuration. On EC2, you own OS hardening, patching and backup encryption. On RDS and Aurora, AWS owns those, and you own users, network exposure and key policy.
Step-by-step implementation
You will build an evidence-based comparison for one database. It is design work, plus one free local tool.
Inventory the database
For one SQL Server database, record: edition and version, size, peak transactions per second, HA method, backup method, and the features in use: CLR, FILESTREAM, linked servers, SQL Agent jobs, SSIS/SSRS/SSAS, Service Broker, replication, cross-database queries, Windows authentication, TDE.
Scan the code for blockers
Export the schema and programmable objects as .sql files (SSMS Generate Scripts, or sqlpackage /Action:Script for a DACPAC). Run the readiness scanner from the Complete code section over the folder. It flags RDS-unsupported features and common Babelfish risks.
Get an authoritative assessment
The scanner is a fast first pass, not the final word. For Babelfish, run Babelfish Compass, the free assessment tool from AWS ⚠ VERIFY current download location. For conversion to PostgreSQL, run a DMS Schema Conversion assessment report, covered in the database migration lesson. DMS Schema Conversion has no additional charge. Since July 2026 it can also assess an offline SQL Server source from extracted metadata.
Price the options
Use the AWS Pricing Calculator for RDS for SQL Server (License Included, your edition) and for Aurora or RDS PostgreSQL at the same vCPU and memory. Add EC2 with licence-included Windows/SQL AMIs if EC2 is on the table. Licence cost frequently dominates.
Decide per database and record it
Pick a target per database and write an ADR. The previous lesson has the template. A bank estate almost always ends up with more than one target.
AWS Console instructions
All of these are read-only; cancel any create wizard.
- Console → RDS → Create database → Standard create → Microsoft SQL Server: look at Database management type (Amazon RDS vs Amazon RDS Custom), Edition (Express, Web, Standard, Enterprise), License (license-included or bring your own media) and Engine version. Then cancel.
- Console → RDS → Option groups → Create group: choose engine
sqlserver-seand look at the available options (for exampleSQLSERVER_BACKUP_RESTORE,TDE,SSRS,MSDTC). Cancel. - Console → RDS → Parameter groups: open the default
sqlserver-se-16.0group (if listed) and search formax degree of parallelismandrds.force_ssl. - Console → RDS → Create database → Amazon Aurora → Aurora (PostgreSQL Compatible): find the Babelfish settings section and its migration mode choice (single-db or multi-db). Cancel.
AWS CLI
# SQL Server engine versions RDS can create today (read-only, free)
aws rds describe-db-engine-versions --engine sqlserver-se \
--query "DBEngineVersions[].EngineVersion" --output text \
--profile learn-sandbox --region us-east-1
# Options available for SQL Server Standard 2022 (16.00) option groups
aws rds describe-option-group-options --engine-name sqlserver-se --major-engine-version 16.00 \
--query "OptionGroupOptions[].Name" --output text \
--profile learn-sandbox --region us-east-1
# Aurora PostgreSQL versions that list Babelfish as a supported feature ⚠ VERIFY the attribute name in your CLI version
aws rds describe-db-engine-versions --engine aurora-postgresql \
--query "DBEngineVersions[?SupportsBabelfish==\`true\`].EngineVersion" --output text \
--profile learn-sandbox --region us-east-1Infrastructure as Code
You won't deploy SQL Server in this lesson. The cost-safe RDS deployment is the PostgreSQL instance in the RDS lesson. Here is what an RDS for SQL Server Standard definition looks like, so you can compare the knobs. Do not apply it: license-included Standard edition costs far more than the Free plan covers.
// Design-only: RDS for SQL Server Standard, Multi-AZ, encrypted, private. Do not deploy in the course.
using Amazon.CDK;
using Amazon.CDK.AWS.EC2;
using Amazon.CDK.AWS.KMS;
using Amazon.CDK.AWS.RDS;
using Constructs;
public sealed class VendorSqlServerStack : Stack
{
public VendorSqlServerStack(Construct scope, string id, IVpc vpc, IStackProps? props = null) : base(scope, id, props)
{
var key = new Key(this, "DbKey", new KeyProps { EnableKeyRotation = true, Alias = "alias/hl-m06-vendor-sql" });
_ = new DatabaseInstance(this, "VendorSql", new DatabaseInstanceProps
{
Engine = DatabaseInstanceEngine.SqlServerSe(new SqlServerSeInstanceEngineProps
{
Version = SqlServerEngineVersion.VER_16, // SQL Server 2022
}),
LicenseModel = LicenseModel.LICENSE_INCLUDED,
InstanceType = InstanceType.Of(InstanceClass.M6I, InstanceSize.XLARGE),
Vpc = vpc,
VpcSubnets = new SubnetSelection { SubnetType = SubnetType.PRIVATE_ISOLATED },
MultiAz = true, // Always On AGs on SQL Server 2022
StorageEncrypted = true,
StorageEncryptionKey = key,
AllocatedStorage = 200,
Credentials = Credentials.FromGeneratedSecret("vendor_admin"), // stored in Secrets Manager
BackupRetention = Duration.Days(14),
DeletionProtection = true,
PubliclyAccessible = false,
});
}
}Complete code
RDS readiness scanner: a dependency-free .NET 10 console app. It scans a folder of .sql files for constructs that are unsupported on RDS for SQL Server, or that are known risks for Babelfish and PostgreSQL conversion. It prints a report and exits with a non-zero code if there are blockers, so it can gate a migration pipeline.
using System.Text.RegularExpressions;
// Usage: dotnet run -- <folder-with-sql-files> [--target rds|babelfish|postgres]
// Exit code: 0 = no blockers for the chosen target, 1 = blockers found, 2 = bad arguments.
if (args.Length == 0 || !Directory.Exists(args[0]))
{
Console.Error.WriteLine("Usage: dotnet run -- <folder-with-sql-files> [--target rds|babelfish|postgres]");
return 2;
}
var target = args.SkipWhile(a => a != "--target").Skip(1).FirstOrDefault() ?? "rds";
var rules = Rules.For(target);
var findings = new List<Finding>();
foreach (var file in Directory.EnumerateFiles(args[0], "*.sql", SearchOption.AllDirectories))
{
var lines = File.ReadAllLines(file);
for (var i = 0; i < lines.Length; i++)
{
foreach (var rule in rules)
{
if (rule.Pattern.IsMatch(lines[i]))
{
findings.Add(new Finding(Path.GetRelativePath(args[0], file), i + 1, rule));
}
}
}
}
Console.WriteLine($"Target: {target}. Files scanned under {args[0]}. Findings: {findings.Count}");
foreach (var group in findings.GroupBy(f => f.Rule.Id).OrderByDescending(g => g.First().Rule.Severity))
{
var r = group.First().Rule;
Console.WriteLine();
Console.WriteLine($"[{r.Severity}] {r.Id}: {r.Title} ({group.Count()} hit(s))");
Console.WriteLine($" Why: {r.Why}");
foreach (var f in group.Take(5))
{
Console.WriteLine($" - {f.File}:{f.Line}");
}
}
var blockers = findings.Count(f => f.Rule.Severity == Severity.Blocker);
Console.WriteLine();
Console.WriteLine(blockers == 0 ? "No blockers found (still run the official assessment tools)." : $"{blockers} blocker(s) found.");
return blockers == 0 ? 0 : 1;
internal enum Severity { Info, Risk, Blocker }
internal sealed record Rule(string Id, Severity Severity, string Title, string Why, Regex Pattern);
internal sealed record Finding(string File, int Line, Rule Rule);
internal static class Rules
{
private static Regex R(string pattern) => new(pattern, RegexOptions.IgnoreCase | RegexOptions.Compiled);
// Not supported on RDS for SQL Server (AWS list, checked 2026-11-18). Also blockers for Babelfish/PostgreSQL.
private static readonly Rule[] RdsBlockers =
[
new("XP-CMDSHELL", Severity.Blocker, "xp_cmdshell / extended stored procedures", "RDS has no OS access; extended procedures are not supported.", R(@"\bxp_cmdshell\b|\bsp_addextendedproc\b")),
new("FILESTREAM", Severity.Blocker, "FILESTREAM or FileTable", "Not supported on RDS. Move documents to S3 and store keys in the database.", R(@"\bFILESTREAM\b|\bFILETABLE\b|AS\s+FILETABLE")),
new("ENDPOINT", Severity.Blocker, "CREATE ENDPOINT (incl. Service Broker endpoints)", "T-SQL endpoints are not available on RDS.", R(@"\bCREATE\s+ENDPOINT\b")),
new("TRUSTWORTHY", Severity.Blocker, "TRUSTWORTHY database property", "Requires sysadmin, which RDS does not grant.", R(@"\bSET\s+TRUSTWORTHY\s+ON\b")),
new("SERVER-TRIGGER", Severity.Blocker, "Server-level trigger", "Server-level triggers are not supported on RDS.", R(@"\bON\s+ALL\s+SERVER\b")),
new("REPLICATION", Severity.Blocker, "SQL Server replication procedures", "Replication is not supported on RDS. Use DMS CDC or read replicas.", R(@"\bsp_add(publication|subscription|article|distributor)\b")),
new("CLR", Severity.Blocker, "CLR assembly", "CLR is not supported on RDS for SQL Server 2017+ (SAFE only on 2016 and earlier).", R(@"\bCREATE\s+ASSEMBLY\b|\bEXTERNAL\s+NAME\b")),
];
// Works on RDS, but needs attention.
private static readonly Rule[] RdsRisks =
[
new("LINKED-SERVER", Severity.Risk, "Linked server / distributed query", "Limited support on RDS; prefer service-to-service APIs or DMS.", R(@"\bsp_addlinkedserver\b|\bOPENQUERY\b|\bOPENROWSET\b|\bOPENDATASOURCE\b")),
new("MSDB-JOBS", Severity.Risk, "SQL Agent job definitions", "Supported on RDS, but scripts that touch msdb system tables directly may fail. Use the sp_add_job API.", R(@"\bmsdb\.dbo\.sysjobs\b|\bsp_add_job\b")),
new("BACKUP-TSQL", Severity.Risk, "BACKUP DATABASE / RESTORE DATABASE", "Native backups on RDS go through rds_backup_database / rds_restore_database to S3.", R(@"^\s*(BACKUP|RESTORE)\s+DATABASE\b")),
new("CROSS-DB", Severity.Info, "Three-part names (cross-database queries)", "Fine within one RDS instance; breaks if databases are split across instances or converted.", R(@"\[\w+\]\.\[\w+\]\.\[\w+\]")),
];
// Extra risks when the target is Babelfish or a PostgreSQL conversion. Babelfish coverage changes
// with each release, so treat these as prompts to run Babelfish Compass, not as verdicts.
private static readonly Rule[] ConversionRisks =
[
new("SQL-AGENT", Severity.Risk, "SQL Agent dependency", "No SQL Agent in Aurora PostgreSQL; use pg_cron or EventBridge Scheduler.", R(@"\bsp_add_job\b|\bsp_start_job\b|\bmsdb\b")),
new("HIERARCHYID-GEO", Severity.Risk, "hierarchyid / geography / geometry types", "Check support in your Babelfish version or plan a PostGIS/ltree conversion.", R(@"\bhierarchyid\b|\bgeography\b|\bgeometry\b")),
new("MERGE", Severity.Risk, "MERGE statement", "Semantics differ; verify behaviour under Babelfish or rewrite as INSERT ... ON CONFLICT.", R(@"^\s*MERGE\b")),
new("NOLOCK", Severity.Info, "NOLOCK / READUNCOMMITTED hints", "PostgreSQL MVCC never reads uncommitted data; hints are ignored or rejected. Review the intent.", R(@"\bNOLOCK\b|\bREADUNCOMMITTED\b")),
new("IDENTITY-INSERT", Severity.Info, "SET IDENTITY_INSERT", "Behaviour differs from PostgreSQL identity columns; test data loads.", R(@"\bIDENTITY_INSERT\b")),
new("DYNAMIC-SQL", Severity.Info, "Dynamic SQL (sp_executesql / EXEC(@sql))", "Converters can't see inside strings; review by hand.", R(@"\bsp_executesql\b|\bEXEC\s*\(\s*@")),
];
public static Rule[] For(string target) => target.ToLowerInvariant() switch
{
"rds" => [.. RdsBlockers, .. RdsRisks],
"babelfish" or "postgres" => [.. RdsBlockers, .. RdsRisks, .. ConversionRisks],
_ => throw new ArgumentException($"Unknown target '{target}'. Use rds, babelfish or postgres."),
};
}A small fictional sample to try it on:
-- Fictional Harbourline procedure with several on-prem habits
CREATE PROCEDURE dbo.usp_ArchiveStatements @AccountId char(14)
AS
BEGIN
SET NOCOUNT ON;
-- Copy PDFs out of the FILESTREAM table to a file share
DECLARE @cmd nvarchar(4000) = N'robocopy \\docstore\statements \\archive\statements';
EXEC master..xp_cmdshell @cmd;
SELECT s.StatementId, s.Pdf
FROM dbo.Statements s WITH (NOLOCK)
WHERE s.AccountId = @AccountId;
SELECT * FROM OPENQUERY(RISKORA, 'SELECT score FROM risk.scores');
ENDdotnet new console -n RdsReadiness -f net10.0
# replace Program.cs with the scanner, create sample-sql\ with the file above
dotnet run --project RdsReadiness -- .\sample-sql --target babelfishExpected output (abridged):
Target: babelfish. Files scanned under .\sample-sql. Findings: 3
[Blocker] XP-CMDSHELL: xp_cmdshell / extended stored procedures (1 hit(s))
Why: RDS has no OS access; extended procedures are not supported.
- dbo.usp_ArchiveStatements.sql:8
[Risk] LINKED-SERVER: Linked server / distributed query (1 hit(s))
...
[Info] NOLOCK: NOLOCK / READUNCOMMITTED hints (1 hit(s))
...
1 blocker(s) found.Code explained line by line
- The first
if: argument check. It returns exit code 2 so a pipeline can tell "bad usage" apart from "blockers found". target = args.SkipWhile(...): reads the optional--targetvalue. The default isrds, the most conservative lift-and-shift target.Rules.For(target): Babelfish and PostgreSQL targets get all the RDS rules plus the conversion risks. Anything that breaks on RDS also breaks there.- The nested
foreach: line-by-line regex matching. It is simple and fast, and it does not understand comments or strings, so treat the output as a lead, not proof. findings.GroupBy(...): groups hits by rule and prints the worst severity first, with up to five locations each, so a report on a large estate stays readable.return blockers == 0 ? 0 : 1;: the gate. Wire it into CI on the repository that holds your exported schema, and nobody can quietly add a newxp_cmdshellcall to a database you plan to move.RdsBlockers: each rule maps to an item on the AWS "not supported" list. TheWhytext tells the reader what to do instead (S3 for documents, DMS for replication).CLRis a blocker because RDS supports CLR only on 2016 and earlier, which is out of mainstream support. Treat it as gone.ConversionRisks: deliberately labelled risks. Babelfish coverage improves with each release, so the right next step is Babelfish Compass, not an argument with the scanner.
Security considerations
- No
sysadminis a security feature. On RDS, nobody (including your DBAs) can runxp_cmdshellor touch the OS. For auditors, that removes a whole class of privileged-access findings. Explain this when people complain about losingsysadmin. - Windows authentication on RDS requires joining the instance to AWS Managed Microsoft AD or a self-managed AD. Plan the trust and network path. Otherwise use SQL logins with credentials in Secrets Manager, rotated automatically. IAM database authentication is not available for SQL Server.
- TDE vs storage encryption. RDS encrypts storage with KMS (choose a customer managed key at creation; you can't add it later). TDE is an additional option for SQL Server, with its own certificates. Running both is allowed but costs some performance. Most banks start with KMS storage encryption and add TDE only where policy demands it.
- On EC2 you own everything: OS patches, SQL CUs, backup encryption, AG listener security, local administrators. Budget for it.
- Native
.bakfiles in S3 contain the whole database. Encrypt the bucket with a KMS key, block public access, restrict access to the RDS option's IAM role, and set a lifecycle rule.
⚠ Learning shortcut — not for production
The readiness scanner uses regular expressions on script text. It misses features configured at server level (for example Agent proxies or linked server definitions created through the GUI), and it can flag text inside comments. In a real migration, combine it with DMS Schema Conversion or Babelfish Compass reports, a query of sys.assemblies, sys.servers, sys.database_files and msdb on the live server (read-only), and interviews with the application owners.
Banking use case
Harbourline's 40 databases, triaged with this lesson's method (fictional):
| Group | Count | Example | Target | Reason |
|---|---|---|---|---|
| Clean T-SQL, .NET apps with EF Core or Dapper | 18 | Customer preferences, branch rota | Aurora PostgreSQL (full conversion) | Small schemas; EF Core switches provider; no licence cost |
| Heavy T-SQL, many procs, can't change app soon | 9 | Treasury reconciliation | Babelfish (pilot first) | Compass report shows over 90% supported constructs; the rest rewritten ⚠ VERIFY percentages on your own estate |
| Vendor package certified only on SQL Server | 6 | Card management (vendor) | RDS for SQL Server SE, Multi-AZ | Vendor support contract; no blockers found |
Uses FILESTREAM, CLR or xp_cmdshell | 4 | Statement archive | SQL Server on EC2, then refactor to S3 + PostgreSQL | Blockers on RDS; refactor in phase 2 |
| Data mart with SSIS and SSAS | 3 | Finance mart | Rebuild on S3 + Glue + Redshift (Module 7) | SSIS/SSAS unsupported on newer RDS versions; analytics belongs in the lake |
The licence saving from moving 27 databases off SQL Server funded the refactoring of the four EC2 holdouts.
Common mistakes
- Assuming RDS for SQL Server equals on-prem SQL Server. Check the unsupported list first.
- Forgetting that CLR is gone on 2017+ on RDS. Many bank databases have an old CLR helper for IBAN or check-digit validation. Move it into application code.
- Picking Web edition to save money. Microsoft's licence terms restrict it to public web workloads.
- Treating Babelfish as a guarantee. It is a compatibility layer with documented differences. Run Compass and a full regression test.
- Planning read replicas on Standard edition. RDS for SQL Server read replicas need Enterprise edition.
- Ignoring the database count limit. Consolidating 60 small databases onto one RDS instance can exceed the per-instance limit, especially with Multi-AZ.
- Comparing compute prices only. The SQL Server licence is often the largest line item.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
CREATE ASSEMBLY fails on RDS | CLR unsupported on 2017+ | Move logic to the application or a Lambda; or EC2 if unavoidable |
EXECUTE permission denied on xp_cmdshell | Extended procs unsupported | Replace with SQL Agent + an external service, or a Lambda via sp_invoke_external_rest_endpoint on 2025 ⚠ VERIFY |
RESTORE DATABASE from a .bak fails | Native restore needs the SQLSERVER_BACKUP_RESTORE option and an S3 role | Add the option group, then use msdb.dbo.rds_restore_database |
| Can't create a read replica | Standard edition, or fewer than 4 vCPUs | Use Enterprise, a larger class, or Multi-AZ for HA only |
| Modify to Multi-AZ fails with "instance has N databases" | Per-instance database limit for that mode | Bigger class or split databases across instances |
| Babelfish client error on a specific statement | Unsupported or differently-behaving T-SQL | Check the Babelfish differences page; rewrite that statement |
Cost and cleanup
This lesson costs nothing: no resources are created, and the scanner runs locally.
For context when you price options: RDS for SQL Server license-included pricing includes the Microsoft licence and is far higher per vCPU than PostgreSQL. Express edition is the only SQL Server edition on the RDS Free plan (with db.t3.micro / db.t4g.micro). It is fine for learning, but not production-grade (Microsoft limits its database size and compute) ⚠ VERIFY current Express limits. For local practice use SQL Server Developer edition in Docker, which is free for non-production use:
# Optional local SQL Server for experiments (Developer edition, non-production use only)
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Local-Only-Pa55word!" -p 127.0.0.1:14333:1433 --name hl-sql -d mcr.microsoft.com/mssql/server:2022-latest
# Cleanup
docker rm -f hl-sql⚠ The sa password above is a local-only placeholder. Never reuse it, and never expose the container port beyond 127.0.0.1.
If you opened any Create wizard in the console, make sure nothing was created:
aws rds describe-db-instances --query "DBInstances[].DBInstanceIdentifier" --profile learn-sandbox --region us-east-1
aws rds describe-option-groups --query "OptionGroupsList[?starts_with(OptionGroupName, 'hl-')].OptionGroupName" --profile learn-sandbox --region us-east-1Exercises
- 🟢 Guided: Build the readiness scanner, run it on the sample procedure with all three targets (
rds,babelfish,postgres), and explain why the finding count changes. - 🟡 Challenge: Add two rules: one that flags
sp_send_dbmail(Database Mail on RDS needs configuration ⚠ VERIFY current support), and one that flagsWAITFOR DELAYinside procedures. Add a--jsonflag that writes findings as JSON for a pipeline artefact. - 🔴 Live problem: The head of operations writes: "The vendor says their treasury system is 'certified on SQL Server 2019 Enterprise'. Finance says Enterprise licence-included on RDS is too expensive. Can we run it on Standard on RDS, or on Babelfish?" Write the reply: what you would check, who you would ask, and the options with their risks.
Assignment
Add a migration-triage.md to your portfolio repository. It should have a table of at least five fictional databases with features in use, scanner results, chosen target, reason and phase. Include the scanner source in a tools/ folder with a README.
Solutions
With --target rds the scanner reports XP-CMDSHELL (blocker) and LINKED-SERVER (risk), because OPENQUERY uses a linked server. FILESTREAM is mentioned only in a comment, which shows the regex limitation. With babelfish or postgres it also reports NOLOCK (info), because the conversion rules are added on top.
Why the count changes: each target inherits the stricter target's rules. Anything that breaks on RDS also breaks on PostgreSQL, and PostgreSQL adds its own concerns (MVCC makes NOLOCK meaningless). The comment hit is a good discussion point: a production scanner should strip comments first, or use a T-SQL parser such as Microsoft's Microsoft.SqlServer.TransactSql.ScriptDom package.
Add to RdsRisks:
new("DBMAIL", Severity.Risk, "Database Mail", "Configure Database Mail on RDS via parameter group and msdb procedures, or move notifications to SNS/SES.", R(@"\bsp_send_dbmail\b")),
new("WAITFOR", Severity.Info, "WAITFOR DELAY in code", "Holds a connection and locks while waiting; usually a sign of polling that belongs in a scheduler or queue.", R(@"\bWAITFOR\s+DELAY\b")),For --json, collect findings into an anonymous list (file, line, id, severity) and write it with System.Text.Json.JsonSerializer.Serialize(..., new JsonSerializerOptions { WriteIndented = true }) to the given path. Why: a machine-readable artefact lets the pipeline publish the report and lets a dashboard track blockers going down over time.
Check first:
- What features does the vendor actually use? Run the scanner and query
sys.dm_db_persisted_sku_features. That DMV lists edition-specific features persisted in a database ⚠ VERIFY on your version. If nothing Enterprise-only is persisted, Standard may work technically. - Is certification a contractual condition for support? Ask the vendor in writing whether Standard edition, RDS, or Babelfish is supported. Babelfish is almost certainly not certified for a third-party package.
- HA needs. Standard on RDS supports Multi-AZ (Always On on 2022), but not read replicas.
Options:
- (a) RDS for SQL Server Standard, Multi-AZ, if the vendor confirms support. Moderate cost; managed.
- (b) RDS Enterprise license-included. Expensive; zero compatibility risk.
- (c) EC2 with BYOL if Harbourline owns Enterprise licences with Software Assurance and License Mobility, or RDS BYOM for Enterprise or Standard. Cost depends on existing agreements.
Babelfish is not appropriate for an uncertified vendor package. Reply with these options, the questions for the vendor and procurement, and a recommendation to start with (a), conditional on written vendor support.
Knowledge check
Answer: Any four of: FILESTREAM/FileTables, xp_cmdshell and extended stored procedures, replication, log shipping, maintenance plans, PolyBase, Stretch Database, server-level triggers, TRUSTWORTHY, CREATE ENDPOINT, buffer pool extension, database snapshots, backup to Azure Blob Storage. CLR is limited to SAFE mode on 2016 and earlier.
Answer: License Included (Enterprise, Standard, Web, Express), where the price includes the licence. Bring Your Own Media (Enterprise, Standard, Developer), where you use your own licences with Software Assurance and License Mobility, and AWS doesn't charge for the SQL Server licence. Developer edition via BYOM is for non-production only.
Answer: Babelfish lets Aurora PostgreSQL accept SQL Server clients over the TDS protocol (port 1433) and run much of T-SQL. The main risk is that T-SQL coverage and behaviour are not identical, so every workload needs an assessment (Babelfish Compass) and regression testing.
Answer: No. IAM database authentication supports MariaDB, MySQL and PostgreSQL. For SQL Server, use SQL logins with credentials in Secrets Manager (with rotation), or Windows authentication via Active Directory.
Answer: They depend on features RDS does not support (FILESTREAM, CLR, xp_cmdshell). EC2 gives full compatibility now, and they are refactored later (documents to S3, logic to application code) before moving to a managed target.
Interview questions
- "When would you choose SQL Server on EC2 over RDS?" When the workload depends on features RDS doesn't support (FILESTREAM, CLR on 2017+,
xp_cmdshell, replication, SSAS), needs OS-level agents, or must keep an exact certified configuration. I'd treat it as a temporary step with a refactoring plan, because you take on all the patching, backups and HA work. - "What's Babelfish good for, and what wouldn't you use it for?" It's good for in-house applications with lots of T-SQL that you want to move off SQL Server licensing without rewriting every query at once. I wouldn't use it for uncertified vendor packages, or for code that leans on unsupported features. Always assess with Compass and regression-test.
- "How do you do a native SQL Server backup on RDS?" Add the
SQLSERVER_BACKUP_RESTOREoption to the instance's option group, with an IAM role for an S3 bucket. Then callmsdb.dbo.rds_backup_databaseandrds_restore_database. Encrypt and lifecycle the bucket. - "What changes for a DBA team moving to RDS?" No
sysadminor OS access. Server settings live in parameter and option groups. Backups and patching are automated and configured, not scripted. HA is a checkbox with its own behaviour. Monitoring is CloudWatch and Database Insights instead of agents on the box. Their time shifts to performance, schema and data quality.
Certification practice
A company runs SQL Server Standard edition and wants a managed service on AWS with automatic failover across Availability Zones. It also wants to offload reporting queries to a readable copy. What must it do?
A. Use Amazon RDS for SQL Server Standard edition with Multi-AZ and add a read replica B. Use Amazon RDS for SQL Server Enterprise edition with Multi-AZ and add a read replica C. Use Amazon RDS for SQL Server Web edition with a read replica D. Use Amazon Aurora MySQL with Babelfish
Answer: B. Read replicas for RDS for SQL Server require Enterprise edition, and Multi-AZ provides the automatic failover. A fails because Standard can't have read replicas. C fails because Web edition is restricted to public web workloads and also has no read replicas. D fails because Babelfish is a feature of Aurora PostgreSQL, not MySQL.
A bank's in-house .NET applications use about 30 small SQL Server databases with standard T-SQL and no server-level features. The bank wants to reduce licensing costs while minimising application changes in the first phase. Which approach is MOST appropriate?
A. Move all databases to Amazon RDS for SQL Server Enterprise edition license-included B. Assess the databases with Babelfish Compass and migrate suitable ones to Babelfish for Aurora PostgreSQL C. Move the databases to Amazon DynamoDB D. Move the databases to SQL Server on EC2 with license-included AMIs
Answer: B. Babelfish removes the SQL Server licence while letting T-SQL clients connect with few changes, and Compass identifies which databases fit. A and D keep (or increase) licence costs. C requires a full redesign of data models and applications.
A team needs to restore a native SQL Server .bak file from on-premises into Amazon RDS for SQL Server. What is required?
A. Copy the file to the instance's D: drive with Remote Desktop and run RESTORE DATABASE
B. Upload the file to Amazon S3, add the SQLSERVER_BACKUP_RESTORE option with an IAM role to the instance's option group, and run msdb.dbo.rds_restore_database
C. Use AWS DMS full load from the .bak file
D. Convert the .bak to CSV and use the COPY command
Answer: B. RDS has no host access (A). Native restore uses S3 plus the option group and RDS stored procedures. DMS reads from a live source database, not from a .bak file (C). COPY is PostgreSQL/Redshift syntax and would lose the schema (D).
Completion checklist
- I can place EC2, RDS Custom, RDS, Babelfish and PostgreSQL on the control/convenience spectrum
- I can list RDS-unsupported SQL Server features and the licensing models
- I ran the readiness scanner on the sample and understood each finding
- I produced a triage table for at least five fictional databases
- I confirmed no AWS resources were created
Key takeaways
- SQL Server on AWS is a spectrum: EC2 (control) → RDS Custom → RDS (managed) → Babelfish → PostgreSQL (lowest licence cost).
- RDS for SQL Server is not on-prem SQL Server: no
sysadmin, and a published list of unsupported features. CLR is gone on 2017+. - Licensing often decides the architecture. Compare License Included, BYOM and PostgreSQL before choosing.
- Babelfish is a strong option for T-SQL-heavy in-house apps, but it needs assessment and testing, not faith.
- Gather evidence before deciding: scan code, run official assessments, price options, and write one ADR per database.
Further reading
- Amazon RDS for Microsoft SQL Server
- Features not supported and features with limited support (RDS for SQL Server)
- Licensing Microsoft SQL Server on Amazon RDS
- Working with read replicas for Microsoft SQL Server in Amazon RDS
- Using Babelfish for Aurora PostgreSQL
- Next: Amazon RDS for SQL Server and PostgreSQL
Independent learning resource — not affiliated with, endorsed or sponsored by Amazon Web Services. AWS and related marks are trademarks of Amazon.com, Inc. or its affiliates. All banking data in examples is fictional. Sample architectures are for learning and are not automatically compliant with GDPR, PCI DSS or any banking regulation.
Choosing a database: SQL vs NoSQL on AWS
Use a repeatable decision framework (access patterns, consistency, scale, operations and cost) to choose between RDS, Aurora, DynamoDB, ElastiCache, DocumentDB, Redshift and S3 for a banking workload, and record the choice in an ADR.
Amazon RDS for SQL Server and PostgreSQL
Create and operate an RDS instance like a bank would: pick the engine, edition and licence model, size instance and gp3 storage, manage parameter and option groups, plan maintenance windows and upgrades, and audit every instance's posture from C#.