Nowadays many technical specialists (developers, project managers, database administrators) try to find open-source solutions for every component of corporate infrastructure. Following this trend, PostgreSQL can be a good option to migrate database from such proprietary DBMS as Microsoft SQL Server or its cloud variation Azure SQL. Open-source databases can be beneficial for developers and enterprises in terms of reducing total cost of ownership and risks of the single vendor lock.
Migration Methods
You have options to migrate your MS SQL database to PostgreSQL, either manually or using automated tools.
For manual migration, you can recreate the same database schema in PostgreSQL as you have in MS SQL. This task can be done by generating scripts from MS SQL Server Management Studio and then executing those scripts in PostgreSQL database via SQL Shell or PgAdmin. However, there may be compatibility issues with data\ types between MS SQL and PostgreSQL, resulting in errors during data export.
Alternatively, you can use migration tools to automate the process and save time. Some available migration tools are EnterpriseDB Migration Toolkit and Sqlserver2pgsql. These tools generate the necessary scripts and create the schema in Postgres, simplifying the migration process.
Finally, it is possible to use special commercial software for MS SQL to PostgreSQL database migration that can fully automate procedure within just a few button clicks.
Manual Database Migration
To migrate the database manually, it is required to generate scripts for either a table or a complete database, transform it according to the target dialect of SQL and finally run those scripts in PostgreSQL. Those scripts for SQL Server database can be generated following the steps below:
- Connect to the source server from SQL Server Management Studio. Navigate to the database that you want to migrate. Right-click on the database name to open pop-up menu and them select “Tasks” > “Generate Scripts” item.
- Choose the objects for which you want to generate scripts – either entire database and all its objects or specific database objects.
- Set the Scripting Option – choose either to open the scripts in a new query window or generate them in a single file or one object script in one file.
- Review your objects and settings, and then finish. The scripts will be generated.
Once the scripts are generated, you need to … Finally, you can execute them in either PostgreSQL’s SQL Shell or PgAdmin. The PostgreSQL database schema will be created based on the executed scripts.
Database Migration using EnterpriseDB Toolkit
The description of the method above may prove that database migration via generating scripts is a time consuming procedure. There are some intermediary tools available which can partially automate the database migration saving a lot of time and efforts. One of these tools is EnterpriseDB Migration Toolkit.
EnterpriseDB Migration Toolkit can be either downloaded from the official website or installed via Application Stack Builder on the local system.
Steps to install EnterpriseDB Migration Toolkit:
- Open the Application Stack Builder that is a part of PostgreSQL installation.
- Verify your Internet connection and specify the PostgreSQL host where to install the Migration Toolkit.
- Then the installation wizard will list available applications. Navigate through menu items to Categories > Registration-required and trial products > EnterpriseDB Tools. Select the Migration Toolkit and click ‘Next’.
- You’ll be asked to select your download directory. Select the desired download folder and click ‘Next’. After the Migration Toolkit is downloaded, confirm installation and then the product is ready to use.
Next thing that is required to use the software is toolkit.properties file built properly. This file contains the configuration and connection information for MS SQL and PostgreSQL databases. Follow these steps to customize the toolkit.properties file for your database migration:
- Locate the toolkit.properties file, which can be found in C:\Program Files\edb\mtk\etc for Windows users.
- Use any text editor of your choice, such as VS Code, to modify the file’s contents. The file should contain the following mandatory parameters
- SRC_DB_URL: Specifies the connection information of the source database.
- SRC_DB_USER: Specifies the username for the source database.
- SRC_DB_PASSWORD: Specifies the password for the source database.
- TARGET_DB_URL: Specifies the connection information of the target database.
- TARGET_DB_USER: Specifies the username for the target database.
- TARGET_DB_PASSWORD: Specifies the password for the target database.
The database URL must comply with the following format:
{TARGET_DB_URL|SRC_DB_URL}=jdbc:edb://host:port/database_id
Once the toolkit.properties file is properly configured, we can proceed with the database migration using the Migration Toolkit. To do this, we will call the runMTK.bat batch file as following:
.\runMTK.bat -sourcedbtype sqlserver -targetdbtype postgres [options, …] <schema_name>
If you need to migrate multiple schemas, you can mention the schema names as a comma-delimited list. If you don’t specify the -sourcedbtype and -targetdbtype parameters in the command, the Migration Toolkit will assume that the source database is Oracle and the target database is EDB PostgreSQL Advanced Server by default.
After completing the steps above, schema and data of the source database will be migrated from SQL Server to PostgreSQL. To ensure that the tables and data have been successfully migrated, you can verify them in Postgres.
Database Migration using Dedicated Commercial Tools
As you can see from the guide above, migrating SQL Server database to PostgreSQL using EnterpriseDB Migration Toolkit still requires a lot of administration work (setup and configuring the application). For those who need easy and smooth database migration, we can suggest to use commercial tools that were specially designed for this purpose.
One of such tools is MS SQL to PostgreSQL converter developed by famous vendor of database migration software – Intelligent Converters. This tool provides all the features required for efficient and flexible migration:
- Support all modern version of on-premises MS SQL and PostgreSQL
- Support all popular cloud variations of both DBMS (Azure SQL, Heroku, Amazon RDS, etc)
- Migrate schemas, data, indexes, constraints and views
- Customize the resulting table structures
- Pre-process data for migration through SELECT-queries
- Scheduling and scripting capabilities via command line support
Visit the official vendor site to learn more about MS SQL to PostgreSQL converter.