How to Migrate Data from an MSSQL Server to PostGreSQL?

So your SaaS product is a success and the users are growing. The
growth also means heaps of big data. Even though you are satisfied with the database management right now, a more efficient and cost-effective approach will become imperative for long-term business growth.

For example, shifting the infrastructure from Windows Azure to Amazon AWS is a cost-effective move and quite effective too. However, if you are
forecasting that the database will outgrow the infrastructure in near future
and you are also using a sharded (RDS instances) multi-latency strategy, the cost can go out of control. That’s because it will demand the integration of more shards and tools like Amazon RDS for SQL Server cost more per
shard.

Now, Amazon RDS for SQL Server works fine and many users are satisfied
with it. It is widely used, comes with a plethora of helpful tools, and expert
personnel. But when it comes to cost management and specific business needs, you need a less expensive, open-source alternative that can handle big data efficiently.

So what are some of the most commonly used Database Software? Well, when considering a budget-friendly and robust database management software, MySQL and PostgreSQL are the go-to choices for many companies. PostgreSQL does a better job of managing concurrency. SQL Server, on the other hand, is not so efficient in this department and it can cause deadlocked reports in the log.

(Image source: Medium)

PostgreSQL seems to be a better option in handling a scenario where multiple processes are trying to access shared data instantaneously. Now, the million-dollar question; how do I import data into PostgreSQL? This blog will help you understand the procedure but before that, let’s try and understand some coding differences between SQL Server & PostgreSQL that will help you in the data migration process.

Things to consider before Migrating Data from
SQL Server to PostgreSQL

Although both follow the American National Standards Institute’s (ANSI) standards as the official language of database administrators, there are distinctions between the syntax and data types.

Syntax

SQL Server vs PostgreSQL Syntax Differences

Data Type

SQL Server vs PostgreSQL - Data Type Differences

MSSQL to PostgreSQL Data Migration Process

Now that you know how to deal with the coding aspect, it is time to move the data from SQL Server to PostgreSQL. The data transfer process may slow down or even shut down the application for a while (depending on the size of the data), so it is a good idea to inform users beforehand. Here’s what the migration process usually looks like:

• You create Relational Database Services instances with table, views, & function schemas.
• Commence with Postgres website changes. Include the App_Offline.htm code to let users know of the downtime or maintenance in progress.
• Use the BCP tool to convert SQL Server data to CSV files. You can also use Pgloader or Sqlserver2pgsql tools to migrate data. Both tools help make the process as automatic as possible.
• Now, bring in the CSV data files into the respective tables. You can do this by typing the COPY command in the Postgres interactive terminal.
• Once the migration is complete, begin creating foreign keys & Indexes. Creating them after the data transfer will ensure better performance.
• Delete the App_Offline.htm once all the data has been transferred to PostgreSQL.
• The final step will be to load the app, the app domain, and voila! You are now ready to reap the benefits of PostgreSQL.

Once you are live with the new database engine you must test the app and the migrated database. Make sure to check the following aspects:

• Have the objects converted appropriately?
• Is Data Manipulation Language (DML)’s implementation correct?
• Insert test data into both databases and run the DML queries. It should be an identical result.
• Is DML performing as you desire? If not, make necessary changes and improve the performance.

If you are still in two minds about the process, it always a good idea to consult an experienced software development company.