SQL Server and PostgreSQL are two the most popular RDBMs supplied with wide range of programming APIs and database management tools. While PostgreSQL is open-source and distributed under GPL license, SQL Server has quite restrictive license policies with a high cost of total ownership. These reasons force many companies and organizations to migrate their databases from SQL Server to Postgres.
The process of database migration consists of six generic steps:
- In form of DDL SQL statements, extract table definitions from the source database.
- Before loading into the target database, these statements should be converted into the destination format.
- Using a csv file as intermediate storage, export data from the source database.
- According to the destination format, transform data and load into target database.
- Using the form of SQL statements and source code, extract triggers, stored procedures, and views.
- Before loading into the target database, statements and source code should be converted into destination format.
Like other database administration routines, the process of SQL Server to Postgres migration is tedious, demanding for much time and efforts, and prone to errors. Such errors can lead to data loss, corruption, or mismanagement due to human factor. Thus, it is important to find a suitable software to automate this conversion process.
Intelligent Converters, a software vendor, has created a special tool for the migration process and it is called SQL Server to Postgres converter. On establishment in 2001, Intelligent Converters is focused on solutions for database migration and synchronization. This company has high reputation for the continuous creation of database migration tools.
Their SQL Server to Postgres converter is of high performance due to the ability to read and write data without the need for middleware components. Also, cloud solutions which is a version of SQL Server and PostgreSQL is supported. In addition to this, it is capable of automation and scheduling of database conversion which are supported by command line version. With SQL Server to Postgres converter, a database administrator can:
- Migrate data from SQL Server into new database
- Synchronize and merge this database with existing PostgreSQL database
If there is need to migrate selected records to a destination database, there is an option to filter the data through SELECT queries. This is powerful and flexible because it allows the selection of some columns and records. Also, the data can be transformed before it is converted into PostgreSQL format. Some examples are but not limited to: filtration of records, selecting and renaming individual columns, skipping NULL values, merging data from multiple tables into a single one.
In addition, column type in the resulting database can be modified. This is not difficult at all. The SQL Server to Postgres converter has a special ‘custom column mapping’ feature which makes this possible. The feature comes in the form of a dialog window which pops up with the type, default value, name, and NULL-attribute for the table’s column. However, some columns can be excluded from the table during the conversion process.
If PostgreSQL refuses remote connection, there is an option to export SQL Server data into SQL script. This process involves exporting the source database into collection of SQL statements to create tables, respective indexes and constraints after which they are filled with data. After this step, the script file is imported into PostgreSQL server through a compatible client tool.
