Due to long term trend of migration from proprietor software with strict licensing policy to free open-source equivalents, it is not a surprise that many companies are willing to move from Microsoft SQL to PostgreSQL. Both database management systems combine easy-to-use interface with powerful capabilities sufficient for any scale of databases.However, when thinking about customization, deployment and scaling of DBMS, PostgreSQL appears to be more advanced option towards MS SQL because of support for multiple platforms, full compliance with SQL standard and open source.
In most cases database migration is arranged according to ETL (extract-transform-load) method that is a sequence of the following actions:
- Export table definitions from MS SQL database in form CREATE-statements
- Convert these statements according to PostgreSQL format (that includes removing”SET ANSI_NULLS ON”, “SET QUOTED_IDENTIFIER ON” and other MS SQL specific keywords; replace “dbo” schema by PostgreSQL default”public”; properly map data types that are not supported by PostgreSQL)
- Import transformed DDL statements into the target database
- Export data from MS SQL tables into CSV files
- Make necessary transformations with special attention to date/time, binary and spatial data
- Import the CSV data into PostgreSQL tables using COPY FROM command
The brief description of migration procedure given above proves that moving SQL Server database to PostgreSQL is quite complicated task which can take much time and efforts when doing it manually. Database specialists often prefer to use dedicated database migration tools to automate the process within just a few button-clicks and to avoid risk of data loss or corruption.
MS SQL to PostgreSQL converter by Intelligent Converters is one of conversion tools for such tasks. Intelligent Converters is a software vendor focused on database migration and synchronization for more than 20 years. Their MS SQL to PostgreSQL converter is easy-to-use even for those who do not have extensive experience in database administration.On the other hand, the tool can migrate extremely large and complicated databases due to high performance of reading and writing algorithms.
Capabilities of MS SQL to PostgreSQL converter:
- all versions of SQL Server and PostgreSQL are supported including SAAS variations such as Azure and Amazon RDS
- automating and scheduling database conversion through support of command line arguments
- option to filter and pre-process data through SELECT queries
- intelligent migration of spatial data via well-known text (WKT) representation
- merge and synchronize scenarios of processing existing PostgreSQL database
- option to change column name, type and attributes or exclude particular columns from migration
- conversion settings can be stored into profile to simplify next tun and to launch the program in the batch mode
Besides direct migration to PostgreSQL server, SQL Server to PostgreSQL converter provides useful option to export the source database into script file. This file contains SQL-statements compatible with PostgreSQL syntax to create all required database objects and fill tables with the data. The specialist responsible for database administrator can validate the script and then import it to PostgreSQL server using one of the standard tools:
- Any command line client for PostgreSQL can load script file through the following statement
psql -h host -d database -U user -f script_file
- php PgAdmin management tool for PostgreSQL can import script file via these steps
- select PostgreSQL database
- go to the “SQL” button
- click “Choose File” button and specify SQL script file
- click on “Execute”
SQL Server to PostgreSQL converter is supplied with free demo version having limited features for evaluation purpose. Demo version migrates not more than fifty rows pet table and does not migrate foreign keys. Intelligent Converters provides unlimited support and 1 year subscription for updates for full paid version of the product.
