The Best Way To Move MS SQL To Postgresql

0 9

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

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

  • SQLite
  • MySQL
  • PostgreSQL

SQLite can be described as a file-based database and a self-contained database system, created and embedded only into programs; therefore, it 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 or lack of support for simultaneous writes

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

To move the database from MS SQL to PostgreSQL, all of the following can 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 an intermediate CSV files
  • Transform the data from CSV files according to PostgreSQL format
  • Load the improved data into a PostgreSQL database using its facilities of importing CSV files

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

SQL Server Management Studio
  • For SQL Server version 2008 and earlier right-click on the 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 the default.
  • For SQL Server version 2012 and later right-click on the database in Management Studio and navigate to Tasks > Generate Scripts item of the popup menu. Find the “Set scripting options” tab, click on the “Advanced” link, and select parameter “Types of data to script” as “data only” or “data and schema” in the General section.

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

The following steps are required to load resulting DDL scripts into PostgreSQL properly: 

  • Remove SQL Server-specific keywords from the statements (i.e., SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON, SET ANSI_PADDING ON)
  • Square brackets around database object names is a part of MS SQL dialect and must be replaced by double quotes
  • Remove square brackets around types as PostgreSQL does not accept this kind of syntax
  • The 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)
  • All reference to filegroup must be removed as PostgreSQL does not support this feature (i.e. “ON PRIMARY”)
  • Replace auto-increment types INT IDENTITY(…) by SERIAL, BIGINT IDENTITY(…) by BIGSERIAL
  • Convert all non-supported MS SQL data types into PostgreSQL equivalents (i.e. DATETIME becomes TIMESTAMP, MONEY becomes NUMERIC(19,4))
  • Replace all SQL Server statement terminators “GO” by the PostgreSQL synonym “;”

The next step will be to process the data, which can be done 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, select “Microsoft OLE DB Provider for SQL Server” as the data source, and “Flat File Destination” as the destination.

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

The workaround specified below is applied to binary data. Walkthrough 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”. 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 PostgreSQL?

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

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

In the case of “Permission denied” error message, try to 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 SQL Server to PostgreSQL converter is one of those tools.

The program vendor, Intelligent Converters that are 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.

Netasha Adams
Netasha Adams is a content writer and SEO specialist with nearly a decade of experience helping brands and businesses communicate with their audience.

Leave a Reply

Your email address will not be published. Required fields are marked *