Many modern technical experts including developers, project managers, and database administrators are inclined towards utilizing open-source solutions for every aspect of their corporate infrastructure. In line with this tendency, PostgreSQL presents itself as a viable alternative for migrating databases from proprietary DBMS such as Microsoft SQL Server or its cloud-based variation, Azure SQL. By adopting open-source databases, enterprises and developers can benefit from lower total cost of ownership and minimize the risk of being locked to a single vendor.
There are two options available to migrate database from MS SQL to PostgreSQL: manual migration or using automated tools. If you choose to manually migrate, you can recreate the same database schema in PostgreSQL by generating scripts from MS SQL Server Management Studio and then executing those scripts in PostgreSQL via SQL Shell or PgAdmin. However, there may be compatibility issues with data types between MS SQL and PostgreSQL, which may result in errors during data export. To avoid this risk, many database specialists use special commercial software that makes database migration a fully automatedprocedure.
Manual Database Migration
In order to manually migrate a database, you must first generate scripts for either a table or the entire database. These scripts must then be transformed to conform to the target dialect of SQL, and subsequently executed in PostgreSQL. To generate scripts for an SQL Server database, follow the steps below:
- Connect to the source server from SQL Server Management Studio and navigate to the database that you want to migrate. Right-click on the database name to open the pop-up menu and select “Tasks” > “Generate Scripts” item.
- Choose the objects for which you want to generate scripts, either the entire database and all its objects or specific database objects.
- Set the Scripting Option – choose to either open the scripts in a new query window, generate them in a single file, or generate one object script in one file.
- Review your objects and settings, and then finish. The scripts will be generated.
After generating the scripts, execute them in either 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 a time-consuming process, but there are intermediary tools available that can partially automate the migration, saving time and effort. One such tool is the EnterpriseDB Migration Toolkit.
The toolkit can be downloaded from the official website or installed via the Application Stack Builder. To install it, open the Stack Builder, verify your internet connection, and specify the PostgreSQL host where you want to install the Migration Toolkit. Then, navigate to Categories > Registration-required and trial products >EnterpriseDB Tools, select the Migration Toolkit, and follow the installation wizard’s prompts to download and install it.
To use the software, you need to properly configure the toolkit.properties file, which contains the configuration and connection information for both the MS SQL and PostgreSQL databases. Locate the file, which can be found in C:\Program Files\edb\mtk\etc for Windows users, and use a text editor to modify its contents. The file must include mandatory parameters such as SRC_DB_URL, SRC_DB_USER, SRC_DB_PASSWORD, TARGET_DB_URL, TARGET_DB_USER, and TARGET_DB_PASSWORD. The database URL must follow a specific format.
Once the toolkit.properties file is properly configured, you can proceed with the database migration using the Migration Toolkit by calling the runMTK.bat batch file with the appropriate parameters. For example, to migrate a single schema from SQL Server to EDB PostgreSQL Advanced Server, use the command:
.\runMTK.bat -sourcedbtypesqlserver -targetdbtypepostgres [options, …] <schema_name>
If you need to migrate multiple schemas, you can specify them as a comma-delimited list.
After completing the migration, you can verify that the schema and data from the source database have been successfully migrated by checking them in PostgreSQL.
Commercial Tools for MS SQL to PostgreSQL Database Migration
While the EnterpriseDB Migration Toolkit provides a way to migrate a SQL Server database to PostgreSQL, the process still requires a significant amount of administrative work, such as configuring the application. For those who need a more streamlined and easy database migration experience, we recommend using commercial tools specifically designed for this purpose.
One such tool is the MS SQL to PostgreSQL converter developed by Intelligent Converters, a well-known vendor of database migration software. This tool offers all the necessary features for efficient and flexible migration, including support for modern versions of both on-premises MS SQL and PostgreSQL, as well as popular cloud variations like Azure SQL, Heroku, and Amazon RDS. The tool can migrate schemas, data, indexes, constraints, and views, and allows customization of resulting table structures. It also offers pre-processing of data for migration via SELECT-queries, as well as scheduling and scripting capabilities via command line support.
MS SQL to PostgreSQL converter has wizard-style interface that leads through the following steps:
- Choose either to migrate database to PostgreSQL server directly or export it into SQL script file.
- Provide all necessary information to connect to MS SQL server: server host name or IP address, user name and password.
- Provide all necessary information to connect to PostgreSQL server: server host name or IP address, port (only if differs from standard 5432), user name and password.
- Select MS SQL and PostgreSQL databases, customize conversion settings.
- 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.
