SQL Server → PostgreSQL
Move off Microsoft SQL Server to open-source PostgreSQL — eliminating per-core licensing costs while retaining your stored procedures, triggers, views, and data.
Why migrate from SQL Server to PostgreSQL?
- ✓Eliminate SQL Server Enterprise licensing ($7,000+ per core)
- ✓Run on Linux — no Windows Server dependency
- ✓True open-source with no vendor lock-in
- ✓PostgreSQL logical replication is free and built-in
- ✓Extensions: PostGIS, pg_partman, pg_cron, TimescaleDB
- ✓JSON/JSONB support far outperforms SQL Server JSON_VALUE
Prerequisites
- →SQL Server 2012 or later (source)
- →SA or db_owner role on the source database
- →CDC must be enabled at database level if using LSN streaming
- →PostgreSQL 13+ target with sufficient storage
Pre-migration Assessment
Run the Assessment Report from DBMigrateAIPro. For SQL Server sources the report highlights: linked server references (manual rewrite required), CLR stored procedures (not supported, flagged CRITICAL), computed columns, XML columns, and T-SQL-specific syntax patterns that need review.
Schema Conversion
DBMigrateAIPro introspects sys.tables, sys.columns, sys.indexes, and sys.foreign_keys to build PostgreSQL DDL. Key conversions: IDENTITY columns become GENERATED ALWAYS AS IDENTITY, UNIQUEIDENTIFIER becomes UUID, database-level objects are mapped to PostgreSQL schemas. The database-to-schema mapping is configurable.
T-SQL to PL/pgSQL Transpilation
Stored procedures, functions, and triggers are transpiled. Key replacements: TOP n → LIMIT n, ISNULL → COALESCE, GETDATE() → NOW(), DATEADD/DATEDIFF → interval arithmetic, PRINT → RAISE NOTICE, @@ROWCOUNT → GET DIAGNOSTICS. Table hints (NOLOCK, ROWLOCK) are stripped.
Bulk Data Migration
Data is streamed from SQL Server using TDS protocol and loaded into PostgreSQL via COPY. FK constraints are deferred during load. Default parallel workers: 8. VARBINARY/IMAGE columns are streamed as BYTEA.
CDC with LSN Tracking (Optional)
After bulk load, enable Change Data Capture in the CDC tab. DBMigrateAIPro reads SQL Server CDC change tables (enabled via sys.sp_cdc_enable_table) and applies changes to PostgreSQL. Log Sequence Numbers (LSNs) track the replication position.
Validate & Cutover
Run the Comparison Report to validate row counts. When satisfied, update your application connection string, stop CDC, and run VACUUM ANALYZE on the PostgreSQL target to build statistics for the query planner.
Type Mapping Reference
| SQL Server Type | PostgreSQL Type |
|---|---|
INT | INTEGER |
BIGINT | BIGINT |
SMALLINT / TINYINT | SMALLINT |
BIT | BOOLEAN |
DECIMAL(p, s) / NUMERIC(p, s) | NUMERIC(p, s) |
FLOAT / REAL | DOUBLE PRECISION / REAL |
MONEY / SMALLMONEY | NUMERIC(19, 4) |
NVARCHAR(n) / VARCHAR(n) | VARCHAR(n) |
NVARCHAR(MAX) / TEXT | TEXT |
NCHAR / CHAR | CHAR |
VARBINARY / IMAGE | BYTEA |
DATETIME / DATETIME2 | TIMESTAMP |
DATETIMEOFFSET | TIMESTAMPTZ |
DATE / TIME | DATE / TIME |
UNIQUEIDENTIFIER | UUID |
XML | TEXT (or JSONB) |
IDENTITY(1,1) | GENERATED ALWAYS AS IDENTITY |
BIT (column) | BOOLEAN |
Common Issues
Login failedSQL Server authentication mode
Set Authentication Mode to "SQL Server and Windows Authentication" in SQL Server Management Studio → Server Properties → Security.
T-SQL syntax errorStored procedures using TOP / NOLOCK hints
DBMigrateAIPro converts TOP n to LIMIT n. Table hints (NOLOCK, ROWLOCK) are stripped. Review flagged objects.
DataError: UUIDUNIQUEIDENTIFIER string format
SQL Server stores GUIDs with mixed case and braces. DBMigrateAIPro normalises to lowercase UUID on copy.
Schema mapping errorSQL Server database vs PostgreSQL schema
Each SQL Server database becomes a PostgreSQL schema by default. Set schema_prefix in migration settings to customise.
Sequence gapIDENTITY columns not re-seeded
DBMigrateAIPro auto-syncs identity sequences post-migration. If a gap appears, run: SELECT setval('seq', (SELECT MAX(id) FROM t));