SQL Server 2016 to PostgreSQL: A Practical Migration Guide for .NET Teams

SQL Server 2016 reaches its end of extended support on July 14, 2026. For many engineering teams, this represents a fork in the road: upgrade to a newer version of SQL Server and absorb the licensing costs, or take the opportunity to migrate to PostgreSQL.

At ZenoxLab, we've helped multiple .NET teams successfully migrate from SQL Server to PostgreSQL. This guide covers the practical realities of making the switch.

Why PostgreSQL?

The shift towards PostgreSQL in the .NET ecosystem isn't just about cost. The Npgsql provider for Entity Framework Core is exceptionally well-maintained, often delivering new features faster than the official SQL Server provider. Furthermore, the rise of managed PostgreSQL platforms like AWS RDS, Azure Database for PostgreSQL, Neon, and Supabase means operational overhead is lower than ever.

EF Core: Swapping the Provider

The first step in any .NET migration is updating your EF Core configuration.

Instead of UseSqlServer(), you'll transition to UseNpgsql(). While EF Core abstracts much of the underlying database syntax, you must address several provider-specific behaviours:

1. Naming Conventions

PostgreSQL conventionally uses snake_case for tables and columns, whereas SQL Server uses PascalCase. You can force PostgreSQL to accept PascalCase by quoting identifiers, but it's generally better to adopt native conventions. The EFCore.NamingConventions package makes this translation automatic:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseNpgsql(connectionString)
        .UseSnakeCaseNamingConvention();
}

2. Migrations History

Your existing SQL Server migrations cannot be applied to PostgreSQL. The cleanest approach is to generate a fresh, initial migration that reflects your current schema state using the Npgsql provider.

Schema Differences & Gotchas

You can't just copy the schema directly. Key differences include:

Identity vs. Sequences

SQL Server uses IDENTITY columns. PostgreSQL historically used SERIAL, but modern PostgreSQL (10+) uses GENERATED ALWAYS AS IDENTITY. EF Core handles this transparently, but if you're migrating data manually, ensure your sequences are updated to the maximum ID after the data import.

Data Types

• DATETIME2 translates to timestamp with time zone (usually timestamptz). You must be rigorous about UTC dates in .NET.
• NVARCHAR(MAX) becomes simply text.
• UNIQUEIDENTIFIER maps to uuid.

Case Sensitivity

By default, SQL Server is case-insensitive (e.g., SQL_Latin1_General_CP1_CI_AS), while PostgreSQL is case-sensitive. If your application relies on case-insensitive queries (like searching emails), you'll need to use PostgreSQL's citext extension or apply ILIKE (or ToLower()) in your LINQ queries.

The Cutover Strategy

For production systems, zero-downtime (or minimal downtime) is usually required. We recommend a "dual-write, single-read" approach or using logical replication.

1. Snapshot: Take a baseline snapshot of the SQL Server database and import it to PostgreSQL.
2. Sync: Use a tool (like AWS DMS or a custom synchronisation script) to stream changes from SQL Server to PostgreSQL.
3. Validation: Run parallel read queries against both databases in staging and assert that the results match.
4. Cutover: Put the application in maintenance mode, wait for the final sync to catch up, swap the connection strings, and bring the application back online.

We Can Help

A database migration is a high-risk operation if executed poorly. If your team needs to migrate off SQL Server 2016 but lacks the bandwidth or PostgreSQL expertise, ZenoxLab can manage the entire process — from schema translation and data validation to the final cutover.

Get in touch to discuss our Database Migration service.