MS SQL to PostgreSQL Database Migration

Microsoft SQL is a popular database management system known for its user-friendly interface, but it can be costly to own for large databases and has strict licensing policies. As a result, some users may want to switch to another DBMS. When migrating a database, it’s logical to consider open-source systems to reduce total cost of ownership, such as SQLite, MySQL, and PostgreSQL.

SQLite is file-based and intended for embedding into applications rather than large multi-user environments. MySQL is more powerful than SQLite and has many advanced features, but it doesn’t fully implement the SQL standard, lacks full text search, and has poor support for concurrent writes in some database engines. PostgreSQL is a highly reliable, standards-compliant RDBMS with object-oriented and/or relational database functionality.

To migrate a database from Microsoft SQL to PostgreSQL, there are several steps involved. First, table definitions must be exported from the source SQL database and converted into PostgreSQL format before loading the resulting statements onto the PostgreSQL server. Next, the MS SQL data is exported into intermediate storage, converted into PostgreSQL format, and loaded into the target database.

To export MS SQL table definitions, users can script objects and data in SQL 2008 and earlier versions or use the Generate Scripts option in SQL 2012 and later versions. The resulting script must be corrected by removing MS SQL specific statements (SET ANSI_NULLS, SET QUOTED_IDENTIFIER, SET ANSI_PADDING, etc) and non-supported optional keywords (WITH NOCHECK, CLUSTERED, etc), replacing square brackets, updating non-supported data types, and replacing the MS SQL query terminator with the PostgreSQL one.

To process data, users can export data via Microsoft SQL Management Studio or use a query to specify the data to transfer. They need to go through the wizard specifying data source “Microsoft OLE DB Provider for SQL Server” and the destination “Flat File Destination”. If the table contains binary data, users must apply a workaround to prevent infinite hang: 

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;

Finally, the resulting CSV file can be imported into PostgreSQL using the “COPY” command:

COPY table_name FROM path_to_csv_file DELIMITER ‘,’ CSV;

While manual conversion can be costly, slow, and lead to incorrect results or data loss, there are many tools available to convert MS SQL to PostgreSQL. Intelligent Converters offer one such solution with their SQL Server to PostgreSQL conversion tool. The product uses high efficient techniques of reading and writing data that allows get high performance of migration (about 10MB+/sec on a modern hardware platform). It can migrate tables, data, indexes, constraints, sequences and views with support for all SQL Server data types including spatial (geometry and geography). All versions of MS SQL and PostgreSQL (starting from v9.0) are supported including DBaaS variations such as Azure SQL and Heroku. This MS SQL to PostgreSQL converter can preprocess data to migrate using SELECT-queries and modify structures of the target tables (customize name, type, default values for every table’s column).