If you are trying to consider a list of the best RDBMS with rich set of administration tools and programming APIs. Microsoft SQL Server and PostgreSQL falls in this category. But, MS SQL comes with somewhat limited licensing policy and a high cost of ownership while PostgreSQL comes with less limiting license and it is open source. Which is the reason why lots of businesses and agencies examine SQL Server to PostgreSQL migration for their databases.
Generally speaking, the migration of database comprises of the following steps:
- Extract table definitions from the source database in form of DDL SQL statements
- Convert these instructions into Postgres 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
Apparently, the process of moving database from MS SQL to PostgreSQL demands lots of work when performing it manually. Additionally, there is high-risk of data loss or corruption due to the human factor. It would be a good idea to make use of special software to improve the conversion process.
One of these conversion resources is SQL to PostgreSQL tool created by Intelligent Converters.
Intelligent Converters is a software vendor that specializes in development solutions for the conversion of database, migration and synchronization since 2001. MS SQL to PostgreSQL converter by Intelligent Conversion has exceptional high performance as a result of to the instant reading and writing of data without the need of any middleware libraries or elements. It works with all models of SQL Server and PostgreSQL as well as cloud solutions. The product shows a command line version to automate and to set up database conversion. MS SQL to PostgreSQL converter permits not only the migration of data from SQL Server into new database but as well consolidate or synchronize it with current PostgreSQL database.
If you require to move only specific records to the destination database. Not a problem, the tool enables you to filter data via SELECT queries. With this option, it is easy to pick specific columns and records or simply modify the data before converting it into PostgreSQL format. Below is a few instances of using this function for varying reasons.
- Filtering records: SELECT * FROM t1 WHERE ID > 300
- Select and rename individual columns: SELECT fn as FirstName, ln as LastName FROM Table1
- Skip NULL values: SELECT * FROM t1 WHERE description IS NOT NULL
Occasionally it is important to adjust column type in the resulting database. For this cause MS SQL to PostgreSQL converter offers feature called “custom column mapping”. It is a dialog window that enables you to change name, type, default value and NULL-attribute for any column of table and also exclude certain columns from conversion.
In case the target PostgreSQL server fails to allow remote connection, the software offers option to export the data into SQL script. In this method, the source database is exported into local a file containing SQL-statements to create tables with all indexes and constraints and fill them with the data. And after that, the database administrator can import the script file to PostgreSQL server via any conventional client tools.
