How to Migrate PostgreSQL to SQL Server – Step by Step Guide

Why Migrate from PostgreSQL to SQL Server?

Organizations choose to migrate between database platforms for a variety of strategic, operational, and technical reasons. Below are the most common drivers for moving from PostgreSQL to SQL Server:

• Performance & Scalability — Move to a database platform that better suits your growing data needs, with improved query performance and handling of large datasets.
• Cost Optimization — Reduce licensing and operational costs by switching to a more efficient or open-source database system.
• Modern Infrastructure — Upgrade from legacy database systems to modern, cloud-ready platforms with better security and support.
• Simplified Management — Consolidate multiple database types into a unified environment, reducing administrative overhead.
• Feature Requirements — Leverage advanced features like native JSON support, better indexing, or improved replication capabilities.

With ESF Database Migration Toolkit, the entire migration process is streamlined through an intuitive wizard interface — no manual scripting or complex configurations required.

Prerequisites

Software Required:

Download ESF Database Migration Toolkit »

System Requirements:

• Windows 7 or higher (Windows 10/11 recommended)
• PostgreSQL 7.x or higher — installed and accessible
• SQL Server 6.5 or higher — installed and accessible
• Appropriate database connection credentials (server, port, username, password)

Step-by-Step Migration Guide

Follow these simple steps to migrate your data from PostgreSQL to SQL Server using ESF Database Migration Toolkit:

1. Step 1: Configure PostgreSQL as Source

   Launch ESF Database Migration Toolkit and click "Next" to open the "Choose a Data Source" dialog. Select PostgreSQL and enter your connection details.

   Configure PostgreSQL Data Source

   

   • In the "Choose a Data Source" dialog:
     • Select "PostgreSQL"
   • Enter server connection details:
     • Server name: localhost (default)
     • Port: 5432 (default)
   • Provide authentication credentials:
     • Username: postgres (default)
     • Associated password
   • Database selection:
     • Click the Refresh Database button
     • Select target database from available options
   • Schema selection:
     • Click the Refresh Schema button
     • Choose desired schema (public schema is default)

2. Step 2: Configure SQL Server as Destination

   In the "Choose a Destination" dialog, select SQL Server and configure the connection settings for your target database.

   Configure Microsoft SQL Server Destination

   

   • In the "Choose a Destination" dialog:
     • Select "Microsoft SQL Server"
   • Server connection details:
     • Enter SQL Server host name (optionally with instance name), e.g., localhost\sqlexpress
     • For TCP/IP connections:
       • Specify server port (default: 0 uses named pipes)
       • Provide username (e.g., sa) and password
     • For Windows Authentication:
       • Check the Windows Authentication checkbox
   • Database configuration:
     • Click the Refresh button to list existing databases
     • Select existing database or enter new database name
     • Note: Non-existing databases will be automatically created during migration
   • Schema configuration:
     • Click the refresh button to list existing schemas
     • Select existing schema or enter new schema name
     • Default schema: dbo (if left blank)
     • Note: Non-existing schemas will be automatically created during migration

3. Step 3: Select Tables and Configure Mapping

   Choose the specific tables and views you want to migrate, then customize the field mappings between source and destination.

   In "Select Source Table(s) & View(s)" Dialog

   • Select migration objects: Choose tables or views to include in the migration.
     

     

   • Modify table structure: Click the ellipsis (...) button to access table options and schema adjustments.
     

     

   • Configure field mapping: In the Field Mapping options:
     • Customize destination fields (name, data type, default value, comments)
     • Select data transfer method:
       • Overwrite Table (replace existing data)
       • Empty Data (truncate before insert)
       • Append Data (add to existing data)
       • Skip Table (exclude from transfer)
     • Apply data filters before transfer

4. Step 4: Execute Migration

   Review your settings and start the automated data transfer process.

   Execution Dialog

   

   • Start migration: Click "Submit" to initiate automated data transfer from PostgreSQL to SQL Server.
   • Monitor progress: Click "Browse Log" for real-time migration tracking, including issue resolution details.
   • Save configuration: Click "Save as job" to store settings for:
     • Quick reloads of migration jobs
     • Command-line execution (use: dmtc.exe --help for parameter options)

5. Step 5: Verify Results

   After the migration completes, verify that all data has been transferred correctly.

Troubleshooting & Common Issues

Connection Errors

If you encounter connection issues when configuring PostgreSQL or SQL Server, verify the following:

• Firewall settings — Ensure the database port is not blocked by Windows Firewall or network security policies
• Authentication — Double-check username and password; some databases require specific authentication modes
• Network access — Confirm that the database server allows remote connections from your machine
• ODBC drivers — For certain databases, ensure the appropriate ODBC driver is installed on your system

Slow Migration Performance

• Large datasets — ESF Database Migration Toolkit uses bulk-insert technology, but extremely large tables (millions of rows) may take time. Consider migrating during off-peak hours.
• Network latency — For remote database servers, network bandwidth and latency can impact transfer speed
• Index overhead — Consider migrating without indexes first, then adding them afterward for faster data insertion