For years Microsoft SQL Server and PostgreSQL are known as very popular RDBMS supplied with huge collection of administration and development tools. PostgreSQL key benefit over MS SQL is less restrictive licensing policy and open source. For many companies and organizations this is the key reason to migrate from SQL Server to PostgreSQL.
MS SQL database can be migrated to PostgreSQL through the steps below:
- Extract definitions of MS SQL tables, indexes and constraints in form of CREATE-statements
- Translate these statements into PostgreSQL format and load into the database
- Export data from MS SQL database in form of csv files (one file per table)
- Make the data complied with PostgreSQL format and import it into the database
- Extract MS SQL views, stored procedures and triggers in form of SQL statements and source code
- Translate these statements and source code into PostgreSQL format and load it into the database
This brief scheme of migration proves that moving a database from SQL Server to PostgreSQL is a hard task requiring a lot of resources. Running this task manually relates to high risk of data loss or corruption due to the human factor. 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. This database converter provides extremely high performance due to efficient approach to reading and writing data. It migrates table definitions, data, indexes, constraints and views with all necessary properties and attributes. All versions of MS SQL and PostgreSQL including cloud variations such as Azure SQL and Heroku are supported. Installation pack of SQL Server to PostgreSQL converter includes command line version to allow customers automate database migration. Besides migration into new database, the program also allows merging and synchronizing with existing PostgreSQL database.
Also, the tool can filter data for migration via SELECT queries. This powerful and flexible feature allows to select particular columns, apply restrictions to the data and even transform it before migrating to PostgreSQL.
Sometimes it is required to change column type, name or other attributes during the database migration. For this purpose, SQL Server to PostgreSQL converter offers ‘Edit Table’ feature. It is implemented as a dialog window with controls for entering new name, type, default value and NULL-attribute for any column as well as excluding it from migration.
When the target PostgreSQL server does not allow direct connection, the program can export the source database into a local script file. This approach provides exporting source database into a local script file with SQL-statements to create tables, indexes, constraints and the data. Later the person responsible for database migration can import this script file into PostgreSQL database via standard tools like psql or pgAdmin.
Leave a Reply