Migration of Database from SQL Server to PostgreSQL

When it comes to the list of most popular RDBMS that has rich set of administration tools and programming APIs, Microsoft SQL server and PostgreSQL falls in this category.

Moreover, MS SQL comes with quite restrictive licensing policyand the cost of ownership is very high.That is one of the reasons why organizations and businesses takes the migration of their database from SQL Server to PostgreSQL more seriously.

Below are few ideas of what the database migration is all about:

  • Extract table definitions from the source database in form of DDL SQL statements
  • Convert these SQL statements according to syntax of the destination format and load to PostgreSQL database
  • Export SQL Server data into an intermediate storage (for example, comma separated values)
  • Transform the data according to the destination format and load it into the target database
  • Extract views, stored procedures and triggersfrom SQL Server database in form of SQL statements and source code
  • Convert these statements and source code into the destination format and load into the target database

The process of migrating database from MS SQL to PostgreSQLcalls for a lot of work when it is done manually. There is also a high risk of loss of data and corruption due to human factor, however, to be on a safe side, it is better to use dedicated software to automate the conversion method.

One of the best conversion tools you can use for this purpose has been developedby Intelligent Converters. Intelligent Converters is a frontier in the software industry with focus on solutions for database conversion, migration and synchronization since 2001. MS SQL to PostgreSQL converter offered by this company has an awesome performance due to efficient algorithms of direct reading and writing data implemented in the product. The software supports all versionsof SQL Server and PostgreSQL such as cloud solutions.

The software comes with a command line feature that helps to automate and to schedule database conversion. With the MS SQL to PostgreSQL converter, you can also migrate data from SQL Server into new database as well as merge or synchronize it with currentPostgreSQL database.

If you need to move few records to a destination database, this tool will allow you to filter data via SELECT queries. You can also select some specific columns and records or even transform the data before converting it into PostgreSQL format with this option. Below are few examples of the different ways you can use this software.

  • Filtering records: SELECT fname, lname FROM People WHERE id>1000
  • Select and rename individual columns: SELECT fname as [First Name], lname as [Last Name] FROM People
  • Skip NULL values: SELECT fname, lname FROM People WHERE phone IS NOT NULL

Often, it is very good to change the column type in the resulting database. As a result of this, the MS SQL to PostgreSQL converter offers feature called “customcolumn mapping”. It is a dialog window that gives you to opportunity to change name, type, default value and NULL-attribute for any column of table as well as exclude certain columns from conversion.

If the destinationPostgreSQL server doesn’t allow remote connection, the software offers some option that enables you to export the data into SQL script. Using this process, the source database is exported into local file containing SQL-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.

To learn more about MS SQL to PostgreSQL converter visit the official product page: