SQL Server has a user-friendly interface and also simple to understand, that makes it one of the most popularly well-known database management system in the world. At the same time, the DBMS has two significant drawbacks:

strict licensing policieshigh cost of ownership (bad for managers of large databases)

These restrictions might at times suggest end users to lookout for an alternative system.

Analyzing the open-source databases is highly recommended to be able to cut back on price tag of ownership. You will need the PostgreSQL. It is simply one of the most popular open-source DBMS with relational database with object-oriented database functionality, making it the most suitable choice when it comes to data integrity as well as high level of reliability.

To migrate database from SQL Server to PostgreSQL, the steps below are required:

extract SQL Server table definitions and export it into DDL statementsconvert them to comply with the PostgreSQL formatload the final results to a PostgreSQL serverexport the source data to an external intermediate storage outside of the SQL Server databaseconvert the exported data to the PostgreSQL formatload into the target database.

During SQL Server to PostgreSQL migration, the source table definitions can be exported through the Management Studio (Tasks > Generate Scripts menu item). Ensure to check the wizard to see that "data" is set to false, which is default. Do not forget to transform the resulting script before importing it into PostgreSQL database as follows:

remove SQL Server specific statements such as SET ANSI_NULLS ON, SET QUOTED_IDENTIFIER ON, SET ANSI_PADDING ON, etcreplace square brackets around database object names and types (it a name is composed from multiple words, enclose it into quotes)replace default SQL Server schema "dbo" by PostgreSQL "public"update all non-supported data types (i.e. "DATETIME" becomes "TIMESTAMP", "MONEY" becomes NUMERIC(19,4), INT IDENTITY becomes SERIAL)replace the SQL Server query terminator "GO" with the PostgreSQL one ";"

Next step of SQL Server to PostgreSQL migration is to process the data, which can also be performed via SQL Server Management Studio (Tasks > Export Data). Select "Microsoft OLE DB Provider for SQL Server" as data source, and "Flat File Destination" as destination on the appropriate wizard page. Once export is performed, the exported data will appear in the destination file inside the comma-separated values (CSV) format.

If SQL Server table contain binary data, it is necessary to apply the workaround during export into CSV format. On the "Specify Table Copy or Query" wizard page check "Write a query to specify the data to transfer" option. On the next wizard page known as "Provide a Source Query", create the following SELECT-query:

SELECT
nonbinaryfield1,
nonbinaryfield2,
cast( master.sys.fn_varbintohexstr(
cast( binaryfield as varbinary(max))) as varchar(max)
) as binary-field-name
FROM
mssql_table_name

The query goes into an infinite hang, making this approach not applicable for large binary data, say 1MB and above.

Now it is the time to load CSV files into PostgreSQL tables using the COPY statement as follows:

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

Try the "COPY" command in case you get a "Permission denied" error message with the "COPY" command.

The series of steps mentioned above suggests that SQL Server to PostgreSQL database migration does call for a great deal of effort and it is often a complex process. Manual conversions cost a lot, time-consuming, and might often cause loss of data or corruption resulting in incorrect results. However, you will find modern tools, that can enable you to convert and migrate the data between two DBMS in a few clicks. Intelligent Converters, is a vendor of software programs that specializes in database conversion and synchronization procedures since 2001, made the SQL Server to PostgreSQL migration tool.

The tool, upon direct link with both source and target databases, provides a high quality conversion that doesn't require ODBC drivers or any other middleware components. Additionally, it allows scripting, automation and scheduling of conversions.