Microsoft SQL Server and PostgreSQL are both very efficient database management systems and each of them has dozens of clients ranges from individuals and small businesses to world’s largest corporations. However, many companies migrate their databases from MS SQLto PostgreSQL due to following advantages: low total cost of ownership while,open source, portability.
When planning database migration, it can be considered as sequence of the following steps:
- Extracting table definitions from the source database in form of CREATE TABLE statements
- Converting these statements to the format of target DBMS and loading to the destination database server
- Exporting MS SQL data into comma separated values as an intermediate storage
- Transforming the intermediate data according to the target format (when it is necessary) and loading it to the PostgreSQL database server
- Extracting views, stored procedures and triggers from the source database in form of SQL statements and source code
- Converting these statements and source code into the PostgreSQL format and load into the target database server
Everyone who plans to migrate MS SQL database to PostgreSQL manually should know that it requires a lot of efforts and may cause data loss or corruption due to the human factor. It is reasonable to use special software to automate the migration process especially for large and complicated databases.
For example, MS SQL to PostgreSQL converter allows to migrate database with only a few clicks of mouse button.The program provides high performance due to algorithms of direct reading and writing data without any middleware libraries or components. It supports all versions of MS SQL and PostgreSQL including Azure SQL and Heroku. The converter supports command line for scripting and scheduling the migration process. There are also options to merge and to synchronize existing PostgreSQL database with the source data.
Sometimes it is required to migrate only particular subset of records. For this purpose, the tool provides filtering data via SELECT queries. This option allows select particular columns and rows as well as transform the data before moving it to PostgreSQL database.
Some migration projects require customization of type mapping for particular columns. MS SQL to PostgreSQL converter provides feature called “custom column mapping” for this purpose. It appears as a dialog to change name, type and other attributes for any column of table as well as exclude certain columns from conversion.
In case PostgreSQL server does not allow remote access, the migration tool can export SQL Server database into SQL-script. Then database administrator will be able to load the script file to PostgreSQL server via any standard client tools.