Many technical experts, including developers, project managers, and database administrators, are increasingly opting for open-source solutions in their corporate infrastructure. In this regard, PostgreSQL emerges as a viable alternative for migrating databases from proprietary DBMSs like Microsoft SQL Server or Azure SQL, its cloud-based variant. By embracing open-source databases, enterprises and developers can enjoy a lower total cost of ownership and reduce the risk of vendor lock-in.
There are two options available for migrating a database from SQL Server to Postgres: manual migration or using automated tools. If you choose to migrate manually, you can recreate the database schema in PostgreSQL by generating scripts from MS SQL Server Management Studio and executing them in PostgreSQL via SQL Shell or PgAdmin. However, there may be compatibility issues with data types between MS SQL and PostgreSQL, which can lead to errors during data export. To mitigate this risk, many database specialists rely on specialized commercial software that automates the database migration process.
Manual Database Migration
To manually migrate a database from SQL Server to Postgres, you first need to generate scripts for either a table or the entire database. These scripts must then be transformed to match the target SQL dialect and subsequently executed in PostgreSQL. Here are the steps to generate scripts for an SQL Server database:
- Connect to the source server using SQL Server Management Studio and navigate to the desired database.
- Highlight the database name, open the pop-up menu via right-click and select “Tasks” > “Generate Scripts” item
- Choose the scope of generating migration scripts: either the entire database and all its objects or specific database objects.
- Set the scripting options: choose to open the scripts in a new query window, generate them in a single file, or generate one object script per file.
- Review the selected objects and settings, and then finish. The scripts will be generated.
After generating the scripts, execute them in PostgreSQL’s SQL Shell or PgAdmin to create schemas in the target database based on the executed scripts.
Database Migration using EnterpriseDB Toolkit
Migrating a database using scripts can be time-consuming, but there are intermediary tools available that can partially automate the migration process, saving time and effort. One such tool is the EnterpriseDB Migration Toolkit. Here’s how you can use it:
- Download the toolkit from the official website or install it via the Application Stack Builder.
- Open the Stack Builder, ensure you have an internet connection, and specify the PostgreSQL host for the Migration Toolkit installation.
- Go to menu item Categories > Registration-required and trial products > EnterpriseDB Tools, select the Migration Toolkit, and follow instructions of the installation wizard to download and install the product.
- Configure connection information for SQL Server and PostgreSQL databases in the file toolkit.properties. Edit the file using a text editor and include mandatory parameters like SRC_DB_URL, SRC_DB_USER, SRC_DB_PASSWORD, TARGET_DB_URL, TARGET_DB_USER, and TARGET_DB_PASSWORD.
- Once the toolkit.properties file is properly configured, proceed with the database migration by calling the runMTK.bat batch file with the appropriate parameters.
After completing the migration, verify the successful migration of the schema and data from the source database by checking them in PostgreSQL.
Commercial Tools for SQL Server to Postgres Database Migration
While the EnterpriseDB Migration Toolkit provides a way to migrate a SQL Server database to PostgreSQL, it still requires a significant amount of administrative work, such as application configuration. For those seeking a more streamlined and user-friendly database migration experience, we recommend using commercial tools specifically designed for this purpose.
One notable tool is the SQL Server to Postgres converter developed by Intelligent Converters, a reputable vendor of database migration software. This software provides sufficient capacities and features for efficient and flexible migration, including support for all modern versions of on-premises SQL Server and PostgreSQL, as well as DBaaS platforms like Azure SQL, Heroku, and Amazon RDS. The converter processes schemas, data, indexes, constraints, and views. It allows customization of the target table and offers pre-processing data being migrated via SELECT-queries. Scheduling and scripting capabilities are supplied by command line support.
SQL Server to Postgres converter comes with easy-to-use wizard-style interface guiding through the entire database migration process. It includes the following steps:
- Choose to migrate MS SQL database either to PostgreSQL server directly or into a local SQL script file containing statements to create all necessary database entries in PostgreSQL and load the data.
2. Provide required connection details for SQL Server: host name or IP address, user name, and password.
3. Provide required connection details for PostgreSQL: host name or IP address, port (only if differs from standard 5432), user name and password.
4. Select MS SQL and PostgreSQL databases, customize conversion settings.
5. Select MS SQL tables to migrate. Double click on a selected item to edit the resulting table: change name, type and other attribute of any column or exclude particular columns from migration.
After all these steps are completed, the SQL Server to Postgres database migration process will be launched with visual progress indication. After migration is done you will be able to store conversion settings into profile to simplify next run by clicking ‘Save Settings’ button.
