Tech

MS SQL to PostgreSQL: how to migrate

806views

 

Microsoft SQL Server is known as one of the most popular relational database management systems due to its user-friendly environment, powerful capabilities and wide collections of tools and frameworks for administration tasks. However, it also has a few serious disadvantages that force some users consider migration from MS SQL to another DBMS: 

  • Relatively high cost of ownership
  • Not full compliance with ISO SQL standard
  • Strict licensing policy

There are three popular open-source databases that may eliminate two of the issues above:

  • SQLite
  • MySQL
  • PostgreSQL

SQLite is a self-contained database management system for personal use, it does not support concurrent connections as consequently cannot be used an alternative for large multi-user projects

Unlike SQLite, MySQL offers common features that are expected from sophisticated RDBMS: strong security, scalability, backup and recovery, transactions and otherpowerful features. When talking about its disadvantages, here are the most of them:

  • Full text search is not properly implemented
  • ANSI SQL standard is not fully supported
  • Transactions and parallel writes is supported in few engines only

Finally,PostgreSQL is the most sophisticated open-source RDBMS enriched with main capabilities of enterprise level commercial systems such as extensive backup, multi-level compression, flexible storage customization and many others. This makes it ideal choice for building and deploying large databases with high demands to data integrity and reliability.

Finding PostgreSQL the best alternative to SQL Server, it is important to understand that migration from one of these DBMS to another is quite complicated process that requires strong skills in database administration. Manual implementation of this procedure involves high risk of data loss or corruption, especially for corporate-scale data warehouses having complex database logic.

It is reasonable to use special software that automates database migration process. There are multiple tools that can move database from SQL Server to PostgreSQL. Main criteria to choose the right product for this purpose are as follows: 

  • all modern versions of SQL Server and PostgreSQL must be supported including SAAS variations such as Azure SQL and Heroku
  • primary database entries (schemas, data, indexes, constraints, views) must be processed
  • options to merge or synchronize exiting PostgreSQL database with MS SQL data
  • the program must allow to modify name, type and attributes of every column begin converted 
  • tools with command line support are preferable since this feature allows to script and schedule the migration process

MS SQL to PostgreSQL converter by Intelligent Converters provides all feature listed above. The product has high performance due to direct access to both source and destination databases, no middleware libraries are used. Also, it offers the flexible feature – converting result of SELECT-queries. This powerful option can be used for wide range of purposes: filtering rows, excluding particular columns from migration, combining multiple tables into single one and many others.