SQL Server to PostgreSQL Migration

Although Microsoft SQL is powerful and widely used for database management systems all over the world owing to its user-friendly UI and easy usability among users, it is not accepted by everyone due to the high ownership cost in the case of large databases and Microsoft SQL is also not preferred due to the strict licensing policy they follow. Hence, the need arises for database administrators to switch from MS SQL to other DBMS software that are available.

To bring down the total cost of database, people might start to consider other open-source database systems available. However, there are only 3 major and useful open-source RDBMS. They are SQLite, MySQL and PostgreSQL.

• SQLite

This is a self-contained file-based database system and can be used instead of large databases with multi-user requirements. 

• MySQL

MySQL is more advanced when compared to SQLite and it offers a variety of features that are necessarily required in RDBMS such as security, scalability, different types of storage etc. However, the major drawback is that, the full SQL standard is not implemented in MySQL, due to which there is poor support for full text search and concurrent writes on the database. 

• PostgreSQL

PostgreSQL is highly powerful and it offers standard compliant RDBMS capabilities that are object-oriented and fulfill the parameters of RDBMS as well. Hence, PostgreSQL is highly recommended for large projects that require a great deal of reliability and data integrity.

Listed below are the steps that are to be done if you wish to migrate a database from MS SQL to PostgreSQL. 

1. Export the table definitions from existing database.
2. Convert the database to PostgreSQL format.
3. The result statements are to be loaded in PostgreSQL server.
4. MS SQL data to be exported to a temporary storage.
5. Convert the data to PostgreSQL.
6. Format the data based on the target table and load the records.

Let us explore the steps mentioned above. To export the MS SQL table definitions, the following steps have to be carried out:

1. Objects and data can be scripted in SQL 2008 and the previous versions. Select Database item in menu Management Studio🡪Tasks🡪Generate Scripts.

This will open a wizard and you have to check the ‘data’ which will be preset to false.

2. For SQL 2012 and higher versions, right click on Database in Management Studio🡪Tasks🡪Generate Scripts.

Now click on the ‘Set Scripting Options’ tab. Click on Advanced🡪Data Only or Data and Schema for ‘Types of data to script’, which can be seen in the general section.

Once you obtain the script, it must be cleansed before you load it to PostgreSQL. The following steps are to be carried out for cleansing and correction: 

1. Delete the MD SQL specific statements such as “SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON”, “SET ANSI_PADDING ON”
2. The square brackets around object names in database should be replaced with double quotes.
3. ‘public’ should be used in PostgreSQL instead of the ‘dbo’ found in the case of MS SQL schema.
4. The keywords not supported by PostgreSQL should be removed, such as “WITH NOCHECK”, “CLUSTERED”
5. File group references should be deleted, such as ‘ON PRIMARY’.
6. ‘SERIAL’ should be populated instead of “INT IDENTITY(…)”
7. Non-supported data types should be changed, such as ‘DATETIME’ to ‘TIMESTAMP’ and ‘MONEY’ to NUMERIC.
8. The query terminator of MS SQL ‘GO’ should be replaced with ‘;’.

Once you have corrected the script, it is time to process the data, which can be done using the Microsoft SQL Management Studio. Right click on Database🡪Tasks🡪Export Data.

When the wizard opens, select ‘Microsoft OLE DB Provider for SQL Server’ and the data source should be selected as ‘Flat File Destination’ for destination.

Once the export is complete, the entire exported data can be seen in Comma Separated Values, or CSV format.

For tables that consist of binary data, a workaround has to be used.

You will have to select on ‘Write a query to specify the data to transfer’ option during the wizard and click on ‘Specify Table Copy or Query’. This will lead you to a wizard page named ‘Provide a Source Query’ and then, your SELECT query should be written as follows:

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>;

However, this approach cannot be used in files over 1 MB and the query hangs. Hence, you can import the resulting CSV in PostgreSQL table using the ‘COPY’ statement:

COPY <table name> FROM <path to csv file> DELIMITER ‘,’ CSV;

While running this query, if you get the ‘Permission denied’ error, use the ‘\COPY’ command.

By now, you would have realized that database migration from MS SQL to PostgreSQL is not simple and requires a lot of time and effort. You cannot do it manually as it is unreliable and time consuming. 

Luckily, there are tools to convert data from MS SQL to PostgreSQL and the tools can also migrate the entire DBMS within a few clicks of the mouse. 

Intelligent Convertors is one such software vendor that has expertise in database conversion and synchronization techniques since the past 15 years and our solution is called MSSQL-to-PostgreSQL.

By using this tool, you can achieve the fastest performance due to the direct connection between source and destination databases. There are no middleware concepts or components used such as ODBC drivers. To further enhance the usability, MSSQL-to-PostgreSQL also has command line for scripting, automating and scheduling the conversion of database.