Skip to Content
Cloud Engineering Academy
6. Databases & Data Architecture

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.

intermediate⏱ 1 h 40 min🟢 FreeSAA-C03DEA-C01

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:

  1. 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.
  2. Amazon RDS Custom for SQL Server: a managed RDS instance where you also get OS and sysadmin access, for applications that need customisation. A middle ground with its own limits.
  3. Amazon RDS for SQL Server: AWS runs the OS, patching, backups, point-in-time restore and Multi-AZ. You get the db_owner role on user databases, not sysadmin. Some features are missing.
  4. 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.
  5. 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

OptionWhat it solvesWhat you give up
SQL Server on EC2Full compatibility; any edition, feature or third-party agentYou patch, back up, cluster and monitor. Highest operational load
RDS Custom for SQL ServerManaged backups and automation plus OS/sysadmin access for apps that need itMore responsibility than RDS (you can break the automation); fewer features than EC2 ⚠ VERIFY feature list for your use case
RDS for SQL ServerManaged 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 PostgreSQLKeep T-SQL clients and much code; lose the SQL Server licence; gain Aurora's storage and failoverIncomplete T-SQL coverage; behaviour differences; two dialects to understand
Aurora / RDS for PostgreSQLLowest licence cost; open-source ecosystem; IAM database authenticationSchema, 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 requires sysadmin)
  • 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 SAFE mode, 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.

CapabilityOn-prem SQL ServerAzure SQL MIRDS for SQL ServerSQL Server on EC2Babelfish (Aurora PG)Aurora / RDS PostgreSQL
Admin levelsysadminNear-fulldb_owner per database, no sysadminsysadminPostgreSQL roles mapped to T-SQL loginsPostgreSQL roles (rds_superuser on RDS)
OS accessYesNoNoYesNoNo
HA in one RegionAlways On AGs / FCIBuilt inMulti-AZ (Always On AGs; mirroring on older versions)You build AGs/FCIAurora replicas, failover usually well under a minute ⚠ VERIFYMulti-AZ instance or cluster; Aurora replicas
Readable secondariesAG secondariesYes (BC tier)Read replicas (Enterprise)AG secondariesAurora readersRead replicas / readers
BackupsNative .bak, maintenance plansAutomated + PITRAutomated snapshots + PITR; native .bak to/from S3 via an optionYou run them (to S3 or AWS Backup)Aurora continuous backup + PITRAutomated + PITR
Agent jobsSQL AgentSQL AgentSQL AgentSQL AgentNo SQL Agent; use pg_cron or external schedulers ⚠ VERIFYpg_cron extension or EventBridge Scheduler
SSIS / SSRS / SSASYesNo (use ADF / Power BI)SSRS supported via option; SSIS and SSAS depend on version (none on 2025)YesNoNo
CLRYesYes2016 and earlier, SAFE onlyYesNoNo (use extensions / app code)
Windows authenticationADEntra ID / ADAWS Managed Microsoft AD or self-managed ADADKerberos via AD ⚠ VERIFYKerberos via AD; IAM database authentication
Encryption at restTDETDEKMS storage encryption + optional TDEEBS encryption + TDEKMSKMS
LicenceYour ownIncluded (vCore) or Azure Hybrid BenefitLicense Included or BYOMLicense 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 example msdb.dbo.rds_backup_database for 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_configure for 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-se and look at the available options (for example SQLSERVER_BACKUP_RESTORE, TDE, SSRS, MSDTC). Cancel.
  • Console → RDS → Parameter groups: open the default sqlserver-se-16.0 group (if listed) and search for max degree of parallelism and rds.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-1

Infrastructure 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.

RdsReadiness/Program.cs
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:

sample-sql/dbo.usp_ArchiveStatements.sql
-- 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');
END
dotnet 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 babelfish

Expected 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

  1. The first if: argument check. It returns exit code 2 so a pipeline can tell "bad usage" apart from "blockers found".
  2. target = args.SkipWhile(...): reads the optional --target value. The default is rds, the most conservative lift-and-shift target.
  3. Rules.For(target): Babelfish and PostgreSQL targets get all the RDS rules plus the conversion risks. Anything that breaks on RDS also breaks there.
  4. 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.
  5. 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.
  6. 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 new xp_cmdshell call to a database you plan to move.
  7. RdsBlockers: each rule maps to an item on the AWS "not supported" list. The Why text tells the reader what to do instead (S3 for documents, DMS for replication).
  8. CLR is a blocker because RDS supports CLR only on 2016 and earlier, which is out of mainstream support. Treat it as gone.
  9. 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 sysadmin is a security feature. On RDS, nobody (including your DBAs) can run xp_cmdshell or touch the OS. For auditors, that removes a whole class of privileged-access findings. Explain this when people complain about losing sysadmin.
  • 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 .bak files 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):

GroupCountExampleTargetReason
Clean T-SQL, .NET apps with EF Core or Dapper18Customer preferences, branch rotaAurora PostgreSQL (full conversion)Small schemas; EF Core switches provider; no licence cost
Heavy T-SQL, many procs, can't change app soon9Treasury reconciliationBabelfish (pilot first)Compass report shows over 90% supported constructs; the rest rewritten ⚠ VERIFY percentages on your own estate
Vendor package certified only on SQL Server6Card management (vendor)RDS for SQL Server SE, Multi-AZVendor support contract; no blockers found
Uses FILESTREAM, CLR or xp_cmdshell4Statement archiveSQL Server on EC2, then refactor to S3 + PostgreSQLBlockers on RDS; refactor in phase 2
Data mart with SSIS and SSAS3Finance martRebuild 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

SymptomLikely causeFix
CREATE ASSEMBLY fails on RDSCLR unsupported on 2017+Move logic to the application or a Lambda; or EC2 if unavoidable
EXECUTE permission denied on xp_cmdshellExtended procs unsupportedReplace with SQL Agent + an external service, or a Lambda via sp_invoke_external_rest_endpoint on 2025 ⚠ VERIFY
RESTORE DATABASE from a .bak failsNative restore needs the SQLSERVER_BACKUP_RESTORE option and an S3 roleAdd the option group, then use msdb.dbo.rds_restore_database
Can't create a read replicaStandard edition, or fewer than 4 vCPUsUse 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 modeBigger class or split databases across instances
Babelfish client error on a specific statementUnsupported or differently-behaving T-SQLCheck 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-1

Exercises

  • 🟢 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 flags WAITFOR DELAY inside procedures. Add a --json flag 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

Knowledge check

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_RESTORE option to the instance's option group, with an IAM role for an S3 bucket. Then call msdb.dbo.rds_backup_database and rds_restore_database. Encrypt and lifecycle the bucket.
  • "What changes for a DBA team moving to RDS?" No sysadmin or 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

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

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.

On this page