Because of the user-friendly and easy to understand interface of the MS SQL, it is among the most commonly known database management system (DBMS) across the world. The program however has two notable drawbacks, which may at times mean users need to seek alternative DBMS. They consist of:

• strict licensing policies
• expensive ownership (not good for owners of large databases)

Reviewing the open-source databases is advisable in order to cut back on expenses of DBMS ownership. There are three main database management systems distributed under open-source license namely:

• SQLite
• MySQL
• PostgreSQL

SQLite can be described as file-based database as well as a self-contained database system, created and embedded only into programs, therefore can’t be used by the multi-user environment as a substitute for big databases.

The MySQL, alternatively, is a lot more powerful and offers features usual for a sophisticated RDBMS. These functions include things like scalability, security, as well as other storage units for various purposes. Many of its drawbacks include:

• poor support for full text search and JSON
• lack of full compatibility with SQL standard
• poor or lack of support for user defined data types

PostgreSQL follows the standard relational DBMS known as SQL92 enhanced with object-oriented capabilities that makes it the best option for powerful and reliable corporate scale data warehousing.

To migrate database from MSSQL to Postgres all the following steps must be performed:

• extract MS SQL table definitions as DDL scripts
• convert them according to the syntax of PostgreSQL DDL statement
• create tables in the target database using those script files
• export the data from SQL Server database into intermediate CSV files
• transform the data from CSV files according to PostgreSQL format
• load the improved data into PostgreSQL database using its facilities of importing CSV files

Below you can find hot to export table definitions on different versions of SQL Server:

• highlight the database to migrate in the left pane of Management Studio and right click to open popup menu
• navigate to Tasks > Generate Scripts item of popup menu
• find “Set scripting options” tab
• click on “Advanced” link and select parameter “Types of data to script” as “data only” or “data and schema” in the General section.

Before proceeding to the next step of MSSQL to Postgres migration, it is required to correct DDL scripts according to PostgreSQL syntax:

• remove MS SQL specific keywords (i.e. SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON, SET ANSI_PADDING ON)
• names of database entries may be enclosed in square brackets, those patterns must be replaced by double quotes
• in cause of some data types are enclosed in square brackets, it must be removed since Postgres does not accept this kind of syntax
• default schema for SQL Server is “dbo” while in PostgreSQL it is “public”, so the appropriate replacement is required
• remove all optional keywords that are not backed up by the target DBMS (i.e. WITH NOCHECK, CLUSTERED)
• auto-increment type INT IDENTITY(…) is converted into SERIAL, BIGINT IDENTITY(…) is converted into BIGSERIAL
• MONEY data type is converted to NUMERIC(19,4)
• DATETIME data type is converted to TIMESTAMP

The next step will be to process the data, which is implemented using the MS SQL Management Studio.

• on the main pane right-click the database name, then select Tasks and Export Data popup menu items
• go through all steps of the appeared wizard
• set data source to “Microsoft OLE DB Provider for SQL Server”, and destination – to “Flat File Destination”

Once export is carried out, the exported data will appear in the destination file of CSV format.

One of the most important challenges when migrating from MSSQL to Postgres is correct and accurate handling binary data. The workaround below can help with this task.

Walk through the wizard until the option “Write a query to specify the data to transfer” appears. This wizard page is furthermore referred to as “Specify Table Copy or Query”. Write this SELECT-query on wizard page “Provide a Source Query”:

select non-binary-field1, non-binary-field2, cast( master.sys.fn_varbintohexstr( cast( binary-field-name as varbinary(max))) as varchar(max)) as binary-field-name from table-name

The query works for small and medium size binary data and goes into an infinite hang when applying to volumes 1MB and above.

How To Load CSV Files Into PostgreSQL

Use the standard bulk insert command “COPY” as follows:

COPY table-name FROM path-to-csv-file DELIMITER ‘,’ CSV;

In case of the error “Permission denied”, use “\COPY” command instead.

The sequence of steps listed above indicates that database migration does require a lot of effort and is usually a complex process. Manual conversions are costly, time-consuming, and can often cause data loss or corruption leading to incorrect results. There are however modern tools available now, which can convert and migrate data between two DBMS in a few clicks, and the MSSQL to Postgres converter is one of those tools.

The program vendor, Intelligent Converters that is focused on database conversion and synchronization techniques since 2001, created that tool to simplify the migration procedure for most of database entries.

The converter, upon direct link with both source and target databases, provides a high-quality conversion that doesn’t require ODBC drivers or any other middleware components. It also permits scripting, automation and scheduling of MSSQL to Postgres migration via the command line.