Both MS SQL and PostgreSQL are popular modern DBMSwith powerfulcapabilities. They also provide high quality administration tools and programming APIs.However, everyone who compares license terms and conditions of these systems will find thatMicrosoft 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 statementsmust 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 “Set scripting options” tab go to Advanced andchoose the proper “Types of data to script”
- The resulting script must be transformed according to PostgreSQL syntax before loading to the destination database.
- remove MS SQL specific syntax patternssuch 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
- do replacement of default schema “dbo” (in MS SQL) by “public” (in PostgreSQL)
- remove all optional keywords that are not supported by PostgreSQL such as “WITH NOCHECK”, “CLUSTERED”, etc
- replace types that are not supported in PostgreSQL:”INT IDENTITY(…)” by “SERIAL”, “DATETIME” by “TIMESTAMP”, “MONEY” by “NUMERIC(19,4)”, etc
- replace SQL Server 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: highlight the databasename, right-click and select menu item Tasks > Export Data. Go through the wizard and specify data source “Microsoft OLE DB Provider for SQL Server” and destination “Flat File Destination”. After these steps 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 <field1>, …, cast(master.sys.fn_varbintohexstr(cast(<binary field > as varbinary(max))) as varchar(max)) as <new field name> from <table name>;
The outputcsv 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.
SQL Server to PostgreSQL developed by Intelligent Converters is one of such tools.It providesgreat 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.
