Complete Guide to SQL Server to PostgreSQL Database Migration
One of the most popular RDBMs with a nice set of programming APIs and administration tools are PostgreSQL and MS SQL. Both servers have distinctive features. PostgreSQL is distributed under the license similar to BSD or MIT and it is and open-sourced, on the other hand MS SQL is a proprietor database management system having quite restrictive license policies and high ownership cost. These reasons are responsible for the upward motion of businesses and organizations towards PostgreSQL.
There are many steps involved in the database migration. These are:
- In form of DDL SQL statements, extract table definitions from the MS SQL database.
- Before loading into the target database, these statements must be transformed with respect to the PostgreSQL format.
- Using collection of comma separate files 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 PostgreSQL database, statements and source code must be translated into destination format with special attention to MS SQL built-in functions missing in PostgreSQL.
Like almost every migration between advanced relational DBMS, the process of SQL Server to PostgreSQL migration is tedious and may cause many errors due to human factor when doing manually. Such errors can be data loss, data corruption, or data mismanagement due to human factor. Thus, it is important to find a suitable software that gives an opportunity for the automation of the conversion process.
Intelligent Converters, a software vendor, has created a special tool for the migration process and it is called MS SQL to PostgreSQL converter. On establishment in 2001, Intelligent Converters has continuously rendered solutions for database synchronization, migration, and conversion. This company has high reputation for the continuous creation of database migration tools.
The converter is of high performance. The reason for this is 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 MS SQL to PostgreSQL converters, 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 for a database administrator to migrate selected records to a destination database, there is a function which allows the filtration of data through SELECT queries. This is important because it allows the selection of some columns and records. Also, the data can be transformed before it is converted into PostgreSQL format. Below are some examples of using SELECT queries capability:
- Filtration of records
- Selection and renaming individual columns
- Skipping NULL values
In addition, column type in the resulting database can be modified. This is not difficult at all. The MS SQL to PostgreSQL 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 remote connection is disallowed, there is an option for the export of data into SQL script. This process involves the export of database into an ‘SQL-statement’ containing file which has the ability to create tables. More so, the tables are filled with 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.
