Migrate from SQL Server to PostgreSQL

pgloader moves tables, indexes and keys automatically, but the procedural layer of a SQL Server database must be rewritten by hand. Stored procedures, scalar and table-valued functions, triggers, cursors, dynamic SQL, TRY and CATCH error handling and transaction logic all depend on T-SQL behavior that PL/pgSQL and standard SQL express differently. A tool can translate a fragment of syntax, but it cannot reproduce how an error inside a transaction behaves, how nested transactions are counted, or how a batch continues after a failed statement. Each object in this layer needs a reviewed conversion and a test that compares its results with the original. Several T-SQL constructs have direct but different equivalents. Bracket identifiers such as [Order Details] become double-quoted identifiers, TOP becomes LIMIT or FETCH FIRST, and TRY and CATCH become BEGIN and EXCEPTION blocks, which PostgreSQL implements with subtransactions. Dynamic SQL built with sp_executesql should be rewritten with EXECUTE and USING or with format() for identifiers. Cursors may be replaced by set-based statements where the logic allows it. Decide these patterns once, document them, and apply them consistently so reviewers can check each object against the same rules. Identifier case needs an early decision. PostgreSQL folds unquoted names to lowercase, while SQL Server with a default case-insensitive collation lets applications refer to Customer and customer interchangeably. A mixed-case name created in SQL Server and quoted in PostgreSQL must then be quoted everywhere, including in application SQL and reporting tools. Choose a naming convention, usually lowercase snake_case, before converting code, and confirm with your application teams how they reference identifiers. Computed columns can map to generated columns. PostgreSQL 18 creates virtual generated columns by default, while earlier supported versions offer only stored generated columns, so confirm which behavior your queries and indexes need in the version you deploy. Before anyone estimates effort, inventory the code. Count objects by type (procedures, functions, triggers, views, jobs), measure their size, and mark dependencies on linked servers, CLR assemblies, SQL Agent jobs and cross-database references. Then classify each object as simple, moderate or complex based on cursors, dynamic SQL, error handling and transaction scope. Ask any partner or internal team to show this inventory and its classification rules in writing, because the quote and schedule should follow from it. Finally, plan verification before conversion begins. Build a test set with representative data, run the original and converted objects side by side, and compare row counts, outputs and error behavior. Record each deliberate difference in behavior and get the application owner to accept it. Keep the original database available until these comparisons pass, so that any defect found after cutover has a documented rollback path.

PostgreSQL is released under the PostgreSQL License, a liberal open source license similar to the BSD or MIT licenses, so there is no per-core or per-user license fee to pay for the database software itself. The savings from this migration come from retiring SQL Server licenses, and the size of that saving depends on your own agreement. Before you model it, confirm which SQL Server edition each instance runs, how cores are licensed under your agreement, whether licenses are perpetual or subscription-based, and what Software Assurance or support contract covers them and when it renews. Those terms, not list prices, determine how much of the license spend you can actually retire and when.

Retiring licenses does not remove all database costs. Infrastructure remains, and you still need servers or virtual machines, storage, network capacity and the operating system for every PostgreSQL node, including standbys, a backup target and any test or staging environments you keep during the migration. Backup and high availability tooling also has to be chosen and run. PostgreSQL provides continuous archiving, point-in-time recovery and streaming replication, but it does not detect a failed primary and promote a standby on its own, so an external failover tool and a connection pooler such as PgBouncer are normally part of the design. Each of these is a cost to plan, whether it is open source, a commercial product or staff time.

Some costs appear only during the migration and are mostly labor. Your team or a partner will rewrite T-SQL that pgloader cannot convert, including stored procedures, triggers, functions and SQL Server-specific features, and then test the application against PostgreSQL behavior. Testing covers data validation, query performance and cutover rehearsal. Your developers and database administrators need training on PostgreSQL administration, the planner, vacuum behavior and backup tooling. These are one-time costs, but they can exceed the licensing you retire in the first year if the application has heavy procedural code, so estimate them from a code inventory rather than from the number of tables.

When you request a quote from DeployOpen, ask for the written scope to name the SQL Server objects in scope, the T-SQL to be rewritten, the test cycles, the cutover rehearsals and the handover documentation. Ask which costs are one-time and which recur, such as an optional support retainer for patching, upgrades and continuous testing. Ask what the quote excludes, including your infrastructure, licenses for any commercial tools you choose, and your internal staff time. The quote should let you compare your current SQL Server spend, net of retired licenses, against the new recurring and one-time costs over the period you plan to run the system.

The largest change for your DBAs and developers is the database language itself. T-SQL stored procedures, functions and triggers do not run unchanged on PostgreSQL, so each one needs a decision: convert it to PL/pgSQL, move the logic into the application, or retire it. DBAs will work with psql, pg_dump and pg_restore in place of SSMS, and they will need to decide where SQL Server Agent jobs run after cutover, whether that is cron, a scheduler or an orchestration tool. Developers should expect unquoted identifiers to fold to lowercase, identity columns to be defined differently, and string handling, date arithmetic and paging syntax to vary. Ask your team which of these patterns appear most often in your code, because that frequency drives the rewrite effort.

Application owners see the change at the connection boundary. Each application needs a PostgreSQL driver, a revised connection string, and a review of how its ORM or data access layer generates SQL. Default transaction isolation, error codes and the way a failed statement ends an open transaction can change application behavior in ways that SQL Server test suites may not have exercised. If you plan to use PgBouncer for connection pooling, agree the pooling mode with each owner, because session-level features behave differently under transaction pooling. Each application needs one named owner who approves functional results, performance expectations and the rollback path, and who answers for that application's SQL during testing and cutover.

Security and operations teams will set up access control and monitoring largely from scratch. PostgreSQL client authentication is configured in pg_hba.conf and supports methods such as password, certificate, GSSAPI, SSPI, LDAP and PAM, so confirm which method matches your directory and how existing SQL Server logins map to database roles. Windows integrated authentication maps to SSPI or GSSAPI depending on the host platform, so confirm the method for each server. Operations will own base backups with archived WAL files for point-in-time recovery, the standby design, alert thresholds and log retention. Review audit logging requirements now, because PostgreSQL logging and extensions differ from SQL Server Audit, and agree the approved extension list with your security team before the build starts.

Before work starts, we need read access to SQL Server metadata, including the catalog views for schemas, tables, indexes, keys, constraints, routines, triggers and agent jobs, so the assessment covers every schema in scope. We also need sample data that reflects real production patterns, test data that exercises your business logic, and reconciliation data such as row counts, checksums and control totals that prove the migrated data matches the source. Name one owner for each application, with authority to approve test results. Provide the change windows in which downtime is permitted, the rollback point you can accept, and the security requirements that apply to data in transit, data at rest and privileged access.

Cutover sign-off criteria shape the written scope as much as the code does. Ask your change board to define what counts as a pass, including reconciliation thresholds, acceptable performance and the conditions for rollback. Those answers decide whether cutover is a single offline window or a phased move with replication running, and that choice changes build, test and support effort. We use these inputs to write the scope, deliverables and timeline before any migration work begins, and we ask you to confirm them in writing. Gaps found during scoping are usually cheaper to close than gaps found after the first test cycle, so treat this list as a gating step rather than paperwork.