You are here
Home > Technology > The Best Way To Move MS SQL To Postgresql

The Best Way To Move MS SQL To Postgresql

Because of the user-friendly and easy to understand interface of the Microsoft SQL (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
  • high cost of ownership (not good for owners of large databases)

Reviewing the open-source databases is advisable in order to cut back on price tag of ownership. In which, you will find three main open-source databases 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,

  • no support for full text search
  • does not implement the full SQL standard
  • poor support for parallel writes in a few database engines.

The PostgreSQL stands out as the standard RDBMS with relational along with integral object-oriented database functionality that makes it the best option when data integrity as well as high level of reliability are needed.

To move database from MS SQL to PostgreSQL all of the following can be performed:

  • export MS SQL table definitions
  • convert them to the PostgreSQL format
  • load the results to a PostgreSQL server
  • export the source SQL data to an intermediate storage
  • convert data to the PostgreSQL format
  • load into the target database.

The Best Way To Export MS SQL Table Definitions

  • for the SQL 2008 and earlier versions; right-click on database in Management Studio, then click on Tasks, Generate Scripts. Ensure to check the wizard to see that “data” is set to false, which is default.
  • for SQL 2012 and later versions: right-click on database in Management Studio, then click on Tasks, Generate Scripts. On the “Set scripting options” tab click on Advanced, and select “data only”, or “data and schema” for “Types of data to script” (in the General section).

Correct the resulting script before you proceed to the next step.

The Best Way To Load Results To Postsql

  • remove MS SQL specific statements (i.e. “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON”)
  • replace square brackets around database object names by double quotes
  • remove square brackets around types
  • replace default MS SQL schema “dbo” by PostgreSQL “public”
  • remove all optional keywords that are not backed up by the target DBMS (i.e. “WITH NOCHECK”, “CLUSTERED”)
  • remove all reference to filegroup (i.e. “ON PRIMARY”)
  • replace types “INT IDENTITY(…)” by “SERIAL”
  • update all non-supported data types (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
  • replace the MS SQL query terminator “GO” with the PostgreSQL one “;”

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

  • right-click on database, then click Tasks, Export Data
  • go through the wizard and select “Microsoft OLE DB Provider for SQL Server” as data source, and “Flat File Destination” as destination.

Once export is carried out, the exported data will appear in the destination file within the comma-separated values (CSV) format.

Workaround should be applied if a few of the tables contain binary data. To achieve this, browse through the wizard page and then click the “Write a query to specify the data to transfer” option. This wizard page is furthermore referred to as “Specify Table Copy or Query”. On the next wizard page known as “Provide a Source Query”, create the following SELECT-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 goes into an infinite hang, making this approach not applicable for large binary data, say 1MB and above.

How To Load The Resulting CSV File Into The Target Postgresql Table

Use the “COPY” as follows: COPY table-name FROM path-to-csv-file DELIMITER ‘,’ CSV;

Try the “\COPY” command if you receive a “Permission denied” error message with the “COPY” command.

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 SQL to PostgreSQL converter is one of those tools.

The program vendor, Intelligent Converters that is focused on database conversion and synchronization techniques since 2001 created the MSSQL-to-PostgreSQL tool.

The tool, 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 conversions.