Migrating Database from SQL Server to PostgreSQL

The Microsoft SQL (MS SQL), because of its user-friendly and easy to learn interface, is one of the most popularly known database management system (DBMS) in the world. The system however has two notable disadvantages, which may sometimes mean users have to seek substitute DBMS. They include:

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

Reviewing the open-source databases is recommended so as to cutback on total cost of ownership. For which, there are three main open-source databases namely:

  • SQLite
  • MySQL
  • PostgreSQL

SQLite is a file-based database and a self-contained database system, developed and embedded only into applications, hence cannot be used in the multi-user environment as a replacement for large databases.

The MySQL, on the other hand, is more powerful and provides features typical of an advanced RDBMS. These features include: scalability, security, and various storage units for different purposes. Some of its disadvantages include,

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

The PostgreSQL is the standard RDBMS with relational as well as integral object-oriented database functionality, which makes it the best choice when data integrity and high level of reliability are required.

To migrate database from MS SQL to PostgreSQL the following can be done:

  • 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

How 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.

How 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 supported 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 completed, the exported data will exist in the destination file in the comma-separated values (CSV) format.

Workaround must be applied if some of the tables contain binary data. To do this, go through the wizard page and click on the “Write a query to specify the data to transfer” option. This wizard page is also known as the “Specify Table Copy or Query”. On the next wizard page known as “Provide a Source Query”, create the following SELECT-query:

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 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 MSSQL-to-PostgreSQL is one of those tools. The software vendor, Intelligent Converters, who have specialized in database conversion and synchronization techniques since 2001, made the SQL to PostgreSQL tool.

The tool, upon direct connection to both source and target databases, offers a high performance conversion that does not require ODBC drivers or other middleware components. Italsoallowsscripting, automationandschedulingofconversions.

Your Turn To Talk

Your email address will not be published.