Technology

Moving 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 policy and 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:

  • Source table structures are extracted as 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 triggers from SQL Server database in form of SQL statements and source code
  • Those statements and source code are converted into the target format and imported into PostgreSQL database

The process of migrating database from MS SQL to PostgreSQL calls 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 developed by 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 versions of 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 current PostgreSQL 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 “custom column 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 destination PostgreSQL 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. The resulting script can be transferred to the target server and imported into PostgreSQL database using standard client tools like psql or phpPgAdmin.

About the author

darshanshah834@gmail.com

Add Comment

Click here to post a comment

Your email address will not be published. Required fields are marked *