Though MS SQL and PostgreSQL servers are popular relational database management system (RDBMS), though there are quite some disparities that induce users to consider migrating to PostgreSQL. The MS SQL Server comes with a highly restrictive licensing and cost of ownership are quite high, if the database is of substantial size of being used by a significant number of clients, while PostgreSQL comes with a less restrictive licensing, it is also reliable and of high performance, and it is open source. As a matter of fact, a great number of users consider migrating database from SQL Server to PostgreSQL.
To execute a successful database migration, certain steps are considered; but summarily it is a process of extracting data from the source system, cleansing, transforming and loading it into the target system, using certain migration rules.
Migration of database between two RDBMS is usually implemented according to extract-transform-load method that consists of the following steps:
- Extract table definitions with corresponding indexes and constraints from the source database in form of SQL CREATE-statements
- Translate all of these statements according to PostgreSQL format and import to the target database
- Extract contents of MS SQL database into an intermediate storage (for example, csv files)
- Transform the data so it complies with PostgreSQL format and import it into tables
- Export MS SQL views, stored procedures, and triggers as SQL statements and source code
- Finally, rewrite SQL statements and source code according to PostgreSQL format and import into the destination database
It is recommended that while carrying out a database migration, a special software should be used to carry out an automated conversion process. When manually done, the procedures of migrating database from MS SQL to PostgreSQL may require enormous efforts. There is every possibility that data may be lost or corrupt in the process, due to the human factor. Data loss may be considered as the highest risk in data migration, as the cost involved in rectifying same adds up as to financial and reputation risk.
One of such software that can be used to automate data migration is SQL Server to PostgreSQL converter, developed by Intelligent Converters. This software is commendable in a lot of ways when it comes to database migration. With this software, loss of data during migration is limited to a reduced rate, if any. Intelligent Converters is a software vendor that specializes in database conversion, migration, and synchronization since for more than 20 years. The SQL to PostgreSQL converter carries out a direct reading and writing of data without middleware libraries making it reliable in the area of database migration. This software also supports all version of SQL Server and PostgreSQL, plus cloud solution, it provides command line version that enables automation and scheduling of database conversion. What’s more, the SQL Server to PostgreSQL Data Migration software not only serves as an aid to data migration but also merges and synchronizes data with existing PostgreSQL database.
Do you wish to migrate a specific data to the destination database? Go ahead and filter the record with the SELECT Queries feature in this software. With this, migrating certain records is easy and achievable without affecting the whole record before converting it into PostgreSQL format. This SELECT Queries feature can equally be used to carry out other tasks like;
- Filtering records,
- Select and rename individual columns,
- Skip NULL values.
At times, it is essential to change column type in the consequential database, and this can be done with a tool featured in the MS SQL to PostgreSQL Converter called “custom column mapping”. This tool allows you to change name, type, default value, NULL-attribute and even delete or exclude any column from conversion.
If the target PostgreSQL system does not allow remote connection, the user can export the data into SQL script. According to this approach the source database is exported into file with SQL-statements that creates tables along with indexes and constraints and fills them with the data. The database administrator can import the script file to PostgreSQL server via any standard client tools.