Microsoft SQL Server and PostgreSQL are both widely spread database management systems having rich set of administration tools and programming APIs. However, MS SQL has quite restrictive licensing policy and high total cost of ownership while PostgreSQL goes with less restrictive license and it is open source. Due to those reasons a lot of companies and organizations consider SQL Server to PostgreSQL database migration.
Usually, database migration is arranged according to extract-transform-load method and consists of the following steps:
- Extract table definitions with corresponding indexes and constraints from the source database in form of SQL CREATE-statements
- Convert these statements into the destination format and load into the target database
- Export data from the source database into an intermediate storage (for example, csv files)
- 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
- Convert these statements and source code into the destination format and load into the target database
The approach explored above proves that migrating MS SQL database to PostgreSQL requires many efforts when doing it manually. Moreover, it causes high risk of data loss or corruption due to the human factor. It would be wise to use special software to automate the conversion process.
One of such tools to automate SQL Server to PostgreSQL database migration has been developed by Intelligent Converters, a software vendor specializing in solutions for database conversion, migration and synchronization since 2001. The converter has extremely high performance due to direct reading and writing data without any middleware libraries or components. It supports all versions of SQL Server and PostgreSQL including SAAS platforms. The migration tool supports command line version for automation purposes. SQL Server to PostgreSQL converter allows not only migrate data from SQL Server into new database but also merge or synchronize it with existing PostgreSQL database.
When the goal is to migrate only certain records, the tool allows to filter data via SELECT queries. This option allows to select individual columns, records and even pre-process the data before converting it into PostgreSQL format. s
Sometimes it is required to modify column type or name in the target table. For this purpose, SQL Server to PostgreSQL converter provides feature called “custom column mapping”. This is the dialog window designed to change attributes for any column of converted table and to exclude certain columns from conversion.
If the target PostgreSQL server does not allow remote connection or it is a SAAS platform with limited access, the program can export the data into SQL script. With this option SQL Server database is converted into local SQL script containing statements to create tables, indexes and constraints and insert the data. Later this script file can be imported into PostgreSQL database using any standard client tools.