When comparing Microsoft SQL Server and PostgreSQL it is important to understand that both RDBMS are very popular and have huge collection of administration and development tools. The main advantage of PostgreSQL towards MS SQL is less restrictive licensing policy and open source. And this is the reason why many companies migrate from SQL Server to PostgreSQL.
Migration from MS SQL to PostgreSQL can be organized in these steps:
- Extract table definitions from SQL Server database with corresponding indexes and constraints in form of CREATE-statements
- These DDL statements must be translated into PostgreSQL dialect of SQL and imported to the target database
- Extract data from MS SQL database as comma separated values
- The data must be transformed to comply with the destination format and import it into PostgreSQL database
- Views, stored procedures, functions and triggers must be extracted from the source database as SQL statements and source code
- Translate these statements and source code into PostgreSQL format and load it into the database
Even this brief algorithm indicates that database migration from SQL Server to PostgreSQL is quite complicated procedure requiring a lot of resources. Also there is high risk of data loss or corruption due to the human factor when migrating manually. It is reasonable to use special software in order to simplify and automate migration process especially for large and complicated databases.
One of the tools designed to migrate databases from SQL Server to PostgreSQL is offered by Intelligent Converters, software provider specializing in field of database conversion, migration and synchronization since 2001. The program converts table definitions, data, indexes, constraints and views with extremely high performance due to efficient approach to reading and writing data. It supports all versions of MS SQL and PostgreSQL including cloud variations such as Azure SQL and Heroku. SQL Server to PostgreSQL converter supports command line in order to automate, to script and to schedule database migration.The program allows not only converts MS SQL data into new database but also merge or synchronize it with existing PostgreSQL database.
If there is need to convert partial data only, the tool can filter data via SELECT queries for this purpose. This feature allows to select particular columns, apply restrictions to the data and even transform it before migrating to PostgreSQL.
Some migration projects require to change column type, name or other attributes in the resulting database. SQL Server to PostgreSQL converter provides custom column mapping feature for these purposes. It is implemented as a dialog to specify new name, type, default value and NULL-attribute for any column and to exclude certain columns from migration.
For situations when there is no direct connection to PostgreSQL server, the program can export the SQL Server database into SQL script. Following this scenario source database is exported into a local script file with SQL-statements to create tables, indexes,constraints and the data. Then database administrator can load the script file into the target PostgreSQL database using client tools like psql or pgAdmin.
