SQL Server 2016: end of extended support on July 14, 2026
Microsoft's lifecycle policy provides ten years of support for each SQL Server version: five years of mainstream support, followed by five years of extended support. For SQL Server 2016, the end of extended support date is July 14, 2026. After this date, Microsoft no longer publishes security updates, critical fixes, or technical updates for this version. A SQL Server 2016 instance exposed on a network after this date accumulates unpatched vulnerabilities. Regulatory requirements — PCI-DSS, GDPR, ISO 27001 — generally require that infrastructure components receive available security patches. Running SQL Server 2016 after July 2026 therefore constitutes a documented deviation in an audit.
Why choose PostgreSQL over a SQL Server upgrade or Azure SQL
- Zero licensing cost: PostgreSQL is under the PostgreSQL license (permissive, BSD-like), with no CALs and no separate Enterprise edition to purchase.
- Full infrastructure control: on a dedicated VPS, you manage versions, extensions, memory parameters, and backups without depending on a cloud vendor.
- SQL Server 2022 fixes the version debt but keeps licensing costs and ties the organization to the Microsoft ecosystem.
- Azure SQL Managed Instance simplifies migration but introduces recurring monthly costs, Azure dependency, and additional network constraints.
- PostgreSQL supports the same advanced features: analytical window functions, recursive CTEs, native JSON, table partitioning, logical replication.
- The tooling ecosystem is broad: pgAdmin, DBeaver, Metabase, PostgREST, and most ORMs (Entity Framework, Hibernate, SQLAlchemy, ActiveRecord) support PostgreSQL.
- Major Linux distributions package PostgreSQL with regular security updates included in the distribution maintenance contract.
Preliminary audit: what migrates cleanly, what requires work
Before writing a single migration command, a schema audit is essential. Elements that migrate without friction include simple tables with standard types, PRIMARY KEY, FOREIGN KEY, UNIQUE and NOT NULL constraints, classic B-tree indexes, views without proprietary T-SQL syntax, and sequences. Elements that require explicit work include stored procedures and T-SQL functions (incompatible syntax, must be rewritten in PL/pgSQL or moved to the application layer), triggers that use the virtual tables INSERTED and DELETED (PostgreSQL equivalent: NEW and OLD), ROWVERSION / TIMESTAMP columns used for optimistic concurrency detection, computed columns, locking hints (WITH (NOLOCK), WITH (UPDLOCK)), and global temporary tables (##temp). A SQL Server inventory can be obtained by querying INFORMATION_SCHEMA and system views sys.objects, sys.procedures, sys.triggers.
SQL Server → PostgreSQL data type mapping
Scroll the table
| SQL Server type | PostgreSQL type | Note |
|---|---|---|
| DATETIME2 | TIMESTAMPTZ | Store timestamps with time zone; DATETIME2 has no timezone, define UTC convention explicitly |
| UNIQUEIDENTIFIER | UUID | PostgreSQL natively supports UUID type; use `uuid-ossp` extension or `gen_random_uuid()` for generation |
| NVARCHAR(MAX) / NTEXT | TEXT | PostgreSQL stores all strings as UTF-8; no NVARCHAR/VARCHAR distinction and no separate MAX limit |
| BIT | BOOLEAN | Values `0`/`1` become `false`/`true`; watch for BIT columns used as integers in calculations |
| MONEY / SMALLMONEY | NUMERIC(19,4) | SQL Server MONEY is a scaled integer, NUMERIC is exact; identical behavior for financial calculations |
| INT IDENTITY(1,1) | SERIAL or GENERATED ALWAYS AS IDENTITY | SERIAL is shorthand for a sequence; GENERATED ALWAYS AS IDENTITY is SQL:2003 standard, preferred for new tables |
| ROWVERSION / TIMESTAMP | bytea + trigger or xmin | No direct equivalent; the system column `xmin` handles optimistic concurrency detection in most cases |
Common T-SQL incompatibilities and their PostgreSQL equivalents
Several common T-SQL constructs have no direct equivalent in standard SQL and require rewriting.
TOP N without ORDER BY: SQL Server accepts SELECT TOP 10 * FROM t without an order clause. PostgreSQL requires LIMIT 10; if a defined order matters, add an explicit ORDER BY.
ISNULL(expr, value): the T-SQL ISNULL function maps to the standard COALESCE(expr, value). Behavior is identical for common cases.
GETDATE() and GETUTCDATE(): these functions become NOW() (timestamp with session timezone) and NOW() AT TIME ZONE 'UTC' or CURRENT_TIMESTAMP respectively.
DATEDIFF and DATEADD: PostgreSQL uses interval arithmetic: date + INTERVAL '7 days', date2 - date1 returns an INTERVAL.
WITH (NOLOCK) hint: this hint requests a read without a shared lock (non-repeatable reads). PostgreSQL does not support this hint; the READ COMMITTED isolation level (default) covers most use cases. For analytical reports that tolerate slightly stale data, a read replica is the structural solution.
PRINT in procedures: use RAISE NOTICE 'message' in PL/pgSQL.
GO as a batch separator: this separator does not exist in PostgreSQL. SQL scripts must be restructured into transactions or separate DO $$...$$ blocks.
8-step migration plan
Schema inventory and audit: export the list of all tables, columns with types, indexes, constraints, stored procedures, functions, triggers, and views. Identify and prioritize objects that require manual rewriting.
Set up the target PostgreSQL VPS: install PostgreSQL 16, configure
postgresql.conf(shared memory, connections, WAL), create the application user with minimum required privileges, enable TLS for remote connections.Schema and data conversion with pgloader or manually:
pgloader mssql://user:pass@host/db postgresql://user:pass@vps/dbmigrates the schema and data in one command. For complex schemas, export the SQL Server DDL and adapt it manually, applying the type mapping table.Validate the migrated schema: verify table count, row count per table, referential integrity (
FOREIGN KEY), and indexes. Compare actual types with expected types viainformation_schema.columns.Rewrite stored procedures and triggers: each T-SQL object must be rewritten in PL/pgSQL or moved to the application layer. Test each procedure independently with representative data sets.
Adapt the application code: search the source code for all literal SQL strings and parameterized queries that use proprietary T-SQL constructs. Update the connection driver (switch from
mssqltopg,psycopg2,npgsql, or the appropriate driver for your stack).Load and regression testing: run the complete regression test suite against the staging PostgreSQL database. Use
pgbenchto validate performance under load. Compare execution plans for critical queries (EXPLAIN ANALYZE) between SQL Server and PostgreSQL.Cutover during a maintenance window: schedule a window (night or weekend), put the application in maintenance mode, run a final differential migration (data inserted since the initial migration), point the application DSN to PostgreSQL, validate critical functions, and lift maintenance. Keep SQL Server in read-only mode for 48 hours to allow a fast rollback if needed.
Configuring PostgreSQL 16 on a VPS for production
A default PostgreSQL installation is not tuned for production. The following parameters in postgresql.conf are a starting point for a VPS with 4 GB RAM.
shared_buffers: set to 25% of available RAM, 1GB for 4 GB. This parameter determines the memory allocated to PostgreSQL's internal cache.
effective_cache_size: set to 75% of total RAM, 3GB. This parameter informs the query planner about available memory for the operating system cache.
work_mem: 16MB to 64MB depending on the complexity of sort queries. This parameter applies per sort operation and per connection; too high a value on an instance with many concurrent connections exhausts memory.
maintenance_work_mem: 256MB for maintenance operations (VACUUM, CREATE INDEX).
max_connections: 100 is sufficient for most applications using a connection pool. If the application opens one connection per request without a pool, use PgBouncer upstream.
For pg_hba.conf, restrict remote connections to the application server's IP address and require TLS: hostssl all appuser 10.0.0.5/32 scram-sha-256.
Enable TLS by generating a certificate (Let's Encrypt or internal certificate) and configuring ssl = on, ssl_cert_file, and ssl_key_file in postgresql.conf.
Stored procedures and T-SQL triggers: the main pitfall
T-SQL stored procedures are the main friction point in a SQL Server to PostgreSQL migration. T-SQL is a proprietary dialect with control flow structures, cursor handling, and error handling (TRY...CATCH blocks) that have no direct equivalent in PL/pgSQL.
SQL Server FAST_FORWARD cursors become FOR loops in PL/pgSQL, but performance is similar and cursor usage should in any case be questioned: a set-based query almost always replaces a cursor more efficiently.
The TRY...CATCH block becomes BEGIN...EXCEPTION WHEN OTHERS THEN in PL/pgSQL.
Local temporary tables (#temp) correspond to session-level temporary tables in PostgreSQL created with CREATE TEMPORARY TABLE. Global temporary tables (##temp) have no direct equivalent — they must be replaced by permanent tables with explicit lifecycle management, or by tables in a dedicated schema with cleanup policies.
Triggers that reference INSERTED and DELETED (SQL Server virtual tables) must be rewritten to use NEW and OLD in PostgreSQL. The trigger structure also differs: PostgreSQL distinguishes BEFORE and AFTER triggers, and FOR EACH ROW from FOR EACH STATEMENT triggers.
A pragmatic approach is to move stored procedure logic into the application layer during migration, which simplifies testing and reduces coupling between the application and the database.
Load testing before cutover: pgbench and query plan comparison
Before cutting over to production, validate performance with pgbench. Initialize a representative test database: pgbench -i -s 50 db_name creates a benchmark schema with 5 million rows. Run a load simulation: pgbench -c 20 -j 4 -T 60 db_name simulates 20 parallel connections for 60 seconds and reports transactions per second. For critical application queries, compare execution plans between SQL Server (SET STATISTICS IO ON; SELECT ...) and PostgreSQL (EXPLAIN (ANALYZE, BUFFERS) SELECT ...). Watch for unexpected full table scans (missing index or uncalculated statistics — run ANALYZE after migration) and NESTED LOOP joins on large volumes that would benefit from a HASH JOIN. Update statistics after the initial migration with ANALYZE table_name or VACUUM ANALYZE for the entire database.
Troubleshooting — common errors in a SQL Server to PostgreSQL migration
Several error categories come up systematically in early migrations.
ERROR: column is of type uuid but expression is of type character varying: a UNIQUEIDENTIFIER column migrated to UUID receives a UUID-formatted string without an explicit cast. Either adapt the application query to send a proper UUID type, add an implicit cast in PostgreSQL (CREATE CAST), or migrate the column to TEXT if UUID semantics are not exploited.
ERROR: operator does not exist: boolean = integer: a BIT column migrated to BOOLEAN is compared to 0 or 1. Replace col = 1 with col = true or simply col in WHERE clauses.
ERROR: function isnull(character varying, text) does not exist: replace all occurrences of ISNULL with COALESCE.
ERROR: syntax error at or near "TOP": the SELECT TOP N syntax does not exist in PostgreSQL. Replace with SELECT ... LIMIT N.
Silent overflows on MONEY → NUMERIC columns: verify that the extreme values in the source table fit within the target precision NUMERIC(19,4). SQL Server MONEY has a range of ±922,337,203,685,477.5807; NUMERIC(19,4) covers ±999,999,999,999,999.9999, sufficient in almost all cases.
Data loss on DATETIME columns without timezone: SQL Server DATETIME and DATETIME2 do not store timezone. If the database was fed by applications in multiple timezones, timestamps migrated to TIMESTAMPTZ without prior conversion will be interpreted as UTC. Define a convention (UTC or local time) and apply an AT TIME ZONE conversion during migration if necessary.
Verify the migration and restore user access
After cutover, a structured verification checklist reduces the risk of restoring users to an incomplete database.
Verify row counts for all critical tables: query information_schema.tables with a COUNT(*) per table, compared against the figures recorded from SQL Server before migration.
Verify referential integrity: SELECT * FROM pg_constraint WHERE contype = 'f' lists all foreign key constraints; attempt an insert that would violate a constraint to confirm it is active.
Verify indexes: SELECT indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' lists all created indexes. Compare with the SQL Server index list.
Test critical application functions: purchase flow, authentication, reports, exports — at minimum the paths touching the most voluminous tables and most complex queries.
Monitor PostgreSQL logs for the first 24 hours: tail -f /var/log/postgresql/postgresql-*.log. Errors like character varying vs uuid type mismatches, connection timeouts, or suboptimal query plans typically appear in the first hours of real traffic.
Keep SQL Server in read-only mode for 48 hours with restricted access limited to the technical team. Rollback means pointing the application DSN back to SQL Server and replaying data written to PostgreSQL during the cutover window.
