Migrate SQL Server Database to PostgreSQL

Leave a Comment
Both MS SQL and PostgreSQL are popular modern DBMS with powerful capabilities. They also provide high quality administration tools and programming APIs. However, everyone who compares license terms and conditions of these systems will find that Microsoft SQL is more restrictive and has higher cost of ownership than PostgreSQL. Therefore, it is not a surprise that many businesses and organizations migrate their databases to PostgreSQL server.

In order to migrate database between two DBMS the following steps must be taken:
-          table definitions must be exported from the source database as DDL statements
-          those DDL statements must be converted into the destination format and then loaded into the destination database
-          extract data from Microsoft SQL database into comma separated values (csv) format
-          convert the data into PostgreSQL format and import it into the target database
-          extract other database objects like views, stored procedures and triggers in form of SQL statements and source code

-          transform it into PostgreSQL format and load into the destination database
Let us now consider each of these steps in details. This is how to export MS SQL table definitions into DLL statements:
-          in SQL Server 2008 and earlier right-click on database in Management Studio and select pop-up menu Tasks, item Generate Scripts. Go through the wizard and make sure to select "data" checkbox which that is set to false by default
-          in SQL 2012 and later right-click on database in Management Studio and select pop-up menu Tasks, item Generate Scripts. On the "Set scripting options" tab click on Advanced, then select "data only", or "data and schema" for "Types of data to script" (in the General section)
The resulting script must be transformed according to PostgreSQL syntax before loading to the destination database.
-          remove MS SQL specific syntax patterns such as "SET ANSI_NULLS ON", "SET QUOTED_IDENTIFIER ON", "SET ANSI_PADDING ON", etc
-          replace square brackets around names by double quotes and remove them around types
-          replace default SQL Server schema "dbo" by PostgreSQL equivalent "public"
-          remove all optional keywords that are not supported by PostgreSQL such as "WITH NOCHECK", "CLUSTERED", etc

-          update types that are not supported by PostgreSQL: "INT IDENTITY(...)" by "SERIAL", "DATETIME" by "TIMESTAMP", "MONEY" by “NUMERIC(19,4)”, etc
-          replace the Microsoft SQL query terminator "GO" by PostgreSQL equivalent ";"
Now it is time to transform MS SQL data. This procedure is also handled by Microsoft SQL Management Studio: right-click on database, select pop-up menu Tasks > Export Data. Go through the wizard and specify data source "Microsoft OLE DB Provider for SQL Server" and destination "Flat File Destination". All data will be exported into the specified csv file.
This approach may not work for table containing binary data and the workaround is required. On the wizard page "Specify Table Copy or Query" select option "Write a query to specify the data to transfer". On the next page called "Provide a Source Query" write the query casting every binary column to varchar(max) as follows:
select <field 1>, …, cast(master.sys.fn_varbintohexstr(cast(<binary field > as varbinary(max))) as varchar(max)) as <new field name> from <table name>;
The output csv file may be imported into PostgreSQL table through "COPY" command:
COPY <table name> FROM <path to csv file> DELIMITER ',' CSV;
This brief guide indicates that migrating database from MS SQL to PostgreSQL is a complicated and tedious procedure that is hard to be done manually. However, database migration becomes easy and automatic with the special software solutions.

MS SQL to PostgreSQL developed by Intelligent Converters is one of such tools. It provides great performance of the conversion process and combines easy-to-use interface with wide range of capabilities:
-          all versions of Microsoft SQL and PostgreSQL are supported (including Azure, Heroku and other cloud solutions);
-          option to automate, script and schedule conversion via command line version of the product;
-          MS SQL data can be merged into existing PostgreSQL database;
-          option to export data from SQL Server database into PostgreSQL script (for those cases when direct connection to PostgreSQL server is not available);
-          support for Unicode;
-          option to convert result of SELECT-query as a regular table;
-          option to change column attributes or exclude particular columns from conversion

As every product developed by Intelligent Converters, SQL to PostgreSQL converter has free version with limited features for trial purpose. Registered version of the product comes with 1 year subscription for updates and unlimited support. 

by Ashwin Singh

Ashwin Singh is a blogger. He enjoys to write post and share tricks. He is a student. He is behind this blog.

Follow him @ Twitter | Facebook | Google Plus

No comments:

Post a Comment

Note: only a member of this blog may post a comment.