Guide to MS SQL to PostgreSQL Migration

For years Microsoft SQL Server has been an extremely popular database management system, but its high cost of ownership for large databases and strict licensing policy causes some users to consider switching to open-source DBMS for reducing total cost of ownership. There are three major open-source RDBMS: SQLite, MySQL, and PostgreSQL.

SQLite is a self-contained file-based database system that is designed for embedding into applications, making it unsuitable for large databases in a multi-user environment. MySQL is more powerful than SQLite and provides a wide range of features expected from advanced RDBMS, such as security and scalability. However, it does not implement the full SQL standard, lacks full-text search, and has poor support for concurrent writes in some database engines. PostgreSQL, on the other hand, is a powerful standards-compliant RDBMS that provides integral object-oriented and/or relational database functionality, making it the best choice for projects that require a high level of reliability and data integrity.

When migrating a database from Microsoft SQL to PostgreSQL, the following steps can be taken:

• Export MS SQL table definitions in form of CREATE TABLE statements 
• Apply necessary changes to make it complied with PostgreSQL format and load to the target database
• Extract data from MS SQL tables and store it into an intermediate storage
• Transform it according to PostgreSQL format and load it into the target database
• Extract indexes and constraints from the source tables and apply it to the migrated database

To export MS SQL table definitions, in SQL 2008 and earlier, script objects and data by right-clicking on the database in Management Studio, selecting Tasks, and then Generate Scripts. In SQL 2012 and later, right-click on the database in Management Studio, select Tasks, and then Generate Scripts. 

Before loading the resulting script to PostgreSQL, remove MS SQL-specific statements, replace square brackets around database object names with double quotes, remove square brackets around types, replace the default MS SQL schema “dbo” with PostgreSQL “public,” remove all non-supported optional keywords, remove all references to filegroup like “ON PRIMARY”, replace types “INT IDENTITY” with “SERIAL,” update all non-supported data types.

To process data, it can be done via Microsoft SQL Management Studio by right-clicking on the database, selecting Tasks, and then Export Data. After the export is completed, all data will be exported into the specified file in comma-separated values (CSV) format. On the next wizard page called Provide a Source Query, compose SELECT-query as follows:

select non_binary_field_1, non_binary_field_2, cast(master.sys.fn_varbintohexstr(

cast(binary_field_name as varbinary(max))) as varchar(max)) as binary_field_name

from table_name;

The description of the approach given above indicates that database migration from MS SQL to PostgreSQL is a hard task that can take much time and efforts. Manual conversion is costly and slow, and it can lead to incorrect results and cause data loss or corruption. Database professionals usually automate database migration though the special software products. One of such solutions is SQL Server to PostgreSQL conversion tool provided by Intelligent Converters company.