Migrate Database From SQL Server to PostgreSQL

The user-friendly capabilities and easy-to-learn interface of the Microsoft SQL Server, makes it to be one of the most widely known database management system (DBMS) in this field. Nevertheless, the system has two significant drawbacks that could sometimes suggest users to lookout for a replacement for the DBMS. This disadvantages include:

• Strict licensing policies
• High cost of a license, especially for large databases

When choosing the target system for database migration, it is reasonable to consider open-source databases as it can cut down the total cost of ownership. In this category, you’ll find three leading open-source databases such as:

• SQLite
• MySQL
• PostgreSQL.

SQLite can be referred to as a document-based database and a self-contained database system. It is a good choice to integrate the database into an application, but it cannot be used in the multi-user environment for large databases.

The MySQL, on the contrary, is more robust. It offers features that are typical of a sophisticated RDBMS. These functions consist of scalability, security, and various storage units for various functions. A few of its disadvantages consist of,

• No assistance for full-text search
• Does not carry out the full SQL standard
• Inferior support for parallel writes in certain database engines.

PostgreSQL is the normal RDBMS that comes with a relational feature as well as a built-in object-oriented database functionality. This is one of the features that makes it the best option when it comes to data integrity with a high level of reliability.

To effectively move the database from SQL Server to PostgreSQL, the following needs to be done:

• export MS SQL table definitions
• convert them to the PostgreSQL format
• load the results to a PostgreSQL server
• export SQL Server data into CSV files
• convert data into the PostgreSQL format
• load into the target database.

Table definitions can be extracted from SQL Server database using one of these options depending on the DBMS version:

• For the Microsoft SQL versions before 2012: right-click on the database in Management Studio, then select Tasks > Generate Scripts menu item. Ensure that the “data” option is not selected, which is the default.
• For Microsoft SQL 2012 and later versions: in Management Studio highlight the database, then right-click it and select Tasks > Generate Scripts menu item. Then, uncheck the “data” option on the “Set scripting options” tab.

Before you proceed to the next step, update the resulting SQL script using the check list below:

• remove SQL Server specific statements having no equivalents in PostgreSQL
• replace square brackets for all database object names by double quotes
• take out square brackets around types
• replace name of default schema “dbo” of SQL Server to PostgreSQL “public”
• take out all optional keywords that are not supported by the target DBMS (i.e. “WITH NOCHECK”, “CLUSTERED”)
• take out all reference to filegroup (i.e. “ON PRIMARY”)
• change types “INT IDENTITY(…)” by “SERIAL”
• update all non-supported data types (i.e. “DATETIME” becomes “TIMESTAMP”, “MONEY” becomes NUMERIC(19,4))
• Convert query terminator “GO” of SQL Server to PostgreSQL equivalent “;”

The next move is to process the data, which can be accomplished with the use of the MS SQL Management Studio.

• Right-click on the database and select Tasks > Export Data menu item
• Follow the well-explained steps of the wizard and select “Microsoft OLE DB Provider for SQL Server” as data source and “Flat File Destination” as the destination.

As soon as the export is finalized, the exported data will exists in the destination file in the comma-separated values (CSV) format.

Workaround must be employed if some of the tables contain binary data. To achieve this, click on the “Write a query to specify the data to transfer” option after going through the wizard page. This wizard page is called the “Specify Table Copy or Query”. On the “Provide a Source Query” wizard page, enter this query:

select non-binary-field1, non-binary-field2, cast( master.sys.fn_varbintohexstr( cast( binary-field-name as varbinary(max))) as varchar(max)) as binary-field-name from table-name

The query may run into an infinite hang as this method is not suitable for heavy binary data, let’s say 1MB and above.

Use the PostgreSQL “COPY” command to load the resulting CSV files into the target tables as follows:

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

Try the “\COPY” command if you receive a “Permission denied” error message with the “COPY” command.

The series of actions mentioned above implies that the database migration requires a whole lot of effort, and most times, it is often an advanced procedure.

Manual conversions are expensive, time-consuming, and may often result in data loss or corruption, which could lead to inaccurate results. You can find modern-day solutions which can transform and migrate data between two DBMSs in a couple of clicks. One of these solutions is SQL Server to PostgreSQL migration tool by Intelligent Converters, a software vendor that specializes in database conversion and synchronization techniques. They have been in this business since 2001.

With a direct connection to both the source and target databases, the tool offers a high-performance conversion that doesn’t require ODBC drivers or other middleware elements. It also enables scripting, automation, and scheduling of conversions via command line support. SQL Server to PostgreSQL converter works with all modern versions of source and target DBMS, both on-premises and in the cloud (for instance, Azure SQL, Heroku, Amazon RDS).

Other features:

• Migrates tables, views, data, indexes, constraints, and sequences with all related properties
• SSL connection to PostgreSQL is supported
• Option to merge or synchronized existing PostgreSQL database with MS SQL data
• Migrated data can be filtered and preprocessed through SELECT-queries
• Option to edit table (modify name, type, and other attributes of every column)
• Can export SQL Server database into PostgreSQL script (suitable for those cases when the target PostgreSQL server does not allow remote connections)
• Easy-to-use interface

Visit the official site of Intelligent Converters to learn more about SQL Server to PostgreSQL converter.