Database migration from SQL Server to PostgreSQL

Microsoft SQL Server and PostgreSQL are both very popular RDBMShavingrich set of administration tools and programming APIs. However, MS SQL has quite restrictive licensing policyand high total cost of ownership while PostgreSQL goes with less restrictive license and it is open source. That is why many businesses and organizations consider migrating their databases from SQL Server to PostgreSQL.

SQL Server to PostgreSQL database migration is usually run according to extract-transform-load method that consists of the following steps:

  • Definitions of SQL Server tablesare extractedas data definition language(DDL) statements
  • These DDL queries must be transformed to complywith the destination format and then load into PostgreSQL database
  • SQL Server data is extracted from the source database into intermediate CSV files
  • The data must be converted according to the destination format (with special attention to dates and binary data) and imported into PostgreSQL tables
  • Such database entries as views, stored procedures and triggers are exported from SQL Server database in form of CREATE-queries and the source code
  • Those queries and source code must be converted into PostgreSQL format and imported into the destination database

Obviously, the procedure of migratingdatabase from SQL Server to PostgreSQL requires many efforts when doing it manually. Moreover,it is implied high risk of data loss or corruption due to possible human errors, that is why many database experts recommend usingdedicated software for automating the database migration.

One of such conversion tools is SQL Server to PostgreSQL developed by Intelligent Converters. Their SQL Server to PostgreSQL converter has extremely high performance due to direct reading and writing data without anymiddleware libraries or components. It supports all versions of SQL Server and PostgreSQL including cloud solutions. Command line version of the tool may helpautomate and schedule database migration. SQL Server to PostgreSQL converter allows not only migrate data from MS SQL into new database but also merge or synchronize it with existing PostgreSQL database.

For purpose of partial migration, the tool allows to filter data through SELECT queries by handling results of those queries as it would be a regular table. This advanced methodmay be used to select individual columns and records, merge multipleSQL Server tables into single one,preprocess the data before converting it into PostgreSQL format.

Sometimes, migration projects may requirecustomization ofPostgreSQL tables structure. For this purpose, SQL Server to PostgreSQL converter supports customcolumn mapping.This feature allows to customize column name, type and other attributes as well as exclude certain columns from migration.

For those situations when PostgreSQL server or cloud instance declines remote connections, SQL Server to PostgreSQL migration tool supports export of the source data into a local SQL script file containing statements to create tables with all indexes and constraints and fill them with the data. Then database administrator can import the script file to PostgreSQL server via any standard client tools.

With all the features listed above SQL Server to PostgreSQL converter comes as easy to use yet powerful solution to automate database migration within just a few clicks of mouse button. Free demo version of the tool migrates 50 rows per table that allows users to understand if it fits their needs. As every product provided by Intelligent Converters, this tool is supplied with 1-year subscription for updates.

Leave a Reply

Notify of