Both the PostgreSQL and Microsoft SQL Server are most the preferred RDBMS because of their popularity due to the fact that they have rich set of management tools and coding APIs. However, The Microsoft SQL Server possess a quite limiting licensing policy as well as very expensive cost of ownership whereas, the PostgreSQL comes with a much less limiting license and also, it an open source application. For this reason, many organizations and businesses give some thought to migrate SQL Server to PostgreSQL. However, the process of database transfer or migration includes:
- Extract table classifications from the main database in line of DDL SQL statements
- Transform or convert all of these statements to the destination format and upload directly to the target database
- Move data in the source database into a more reliable storage (for example, csv files)
- Convert the data in line with the destination format and load it to the target database
- Extract views, saved processes and triggers from SQL Server database in form of SQL statements and source code
- Transform these statements and source code to the destination format and upload directly to the target database
As you can realize from the brief explanation above, the process to migrate SQL Server to PostgreSQL needs a lot of efforts when you are carrying out this process manually. Additionally, the risk of losing data or corruption as a result of human factor is very high. So, it is a good idea to make use of an exceptional software created for the purpose of automating the conversion process. There are free and commercial tools are available for this task, however it is not recommended to use freeware since it comes without any guarantees of safety for your data.
One among this kind of software to migrate SQL Server to PostgreSQL has been created by Intelligent Converters, a frontier in the database migration and synchronization industry since 2001. They provide easy-to-use solutions with intuitive interface yet powerful enough to handle migration of large and complicated corporate databases. The prices are quite reasonable and every tool has features limited trial version to evaluate the quality and capabilities before purchasing.
The Intelligent Converters database migration software delivers exceptional result as a result of the direct reading and of writing data which has no middleware components or libraries. It also works with all popular on-premises and cloud variations of the source and destination DBMSs. Their tool to migrate SQL Server to PostgreSQL offers command line version, so that you can easily automate processes and at the same time schedule database transformation.
Aside from enabling you to migrate data from SQL Server into new database, the SQL Server to PostgreSQL converter also gives you the opportunity to consolidate or synchronize with a pre-existing PostgreSQL database.
Are you looking for how to move only specific records from one database to another? Not a problem, the software gives you the opportunity to select data you need by using the SELECT queries. With this option, it is easy to pick out specific columns and records as well as convert the data prior to converting it into PostgreSQL format. Below are a few ways to which you can easily use this feature to achieve different goals.
1) Select records to migrate according a condition: SELECT * FROM Tbl1 WHERE UID < 100
2) Select and rename individual columns: SELECT clmn1 AS Name, clmn2 as Phone FROM Tbl2
3) Skip NULL values: SELECT * FROM Tbl3 WHERE Address2 IS NOT NULL
Most time, it is recommended to modify the column type within the resulting database. To do this, the SQL Server to PostgreSQL converter comes with a feature named “custom column mapping”. This feature is a dialog window which enables you to alter name, type, default value and NULL-attribute in any column of table and also rule out specific columns from conversion.
When the target PostgreSQL server rejects remote connection, the only way to migrate SQL Server to PostgreSQL is to export the data into a local SQL script file. In this process, the source database is exported into a local file that contains SQL-statements to create tables along with indexes and constraints which is later filled with the data.
