2 Ways to Migrate SQL Server to Postgres
Being one of the most popular databases, SQL Server offers such valuable benefits as easy installation, comprehensive secure features (encryption), robust data recovery capabilities and various user-friendly features. Nevertheless, the DBMS has certain limitations such as escalating licensing costs and maintenance expenses as the database size or the number of clients expands. Additionally, SQL Server imposes a maximum size limit of 10 GB and a buffer cache of just 1MB, with the further restriction of running exclusively on Windows.
For those considering alternatives, Postgres, a completely free and open-source database, may be an enticing option. Supported by a global community, Postgres is compatible with Windows, Mac, Linux, FreeBSD, and Solaris, offering the flexibility to choose your preferred operating system. Furthermore, Postgres benefits from a wealth of open-source add-ons, enhancing its versatility and capabilities.
This whitepaper explores 2 different approaches to migrate from SQL Server to Postgres: free tool Sqlserver2pgsql and commercial SQL Server to Postgres converter offered by Intelligent Converters software company.
Migrate from SQL Server to Postgres via Sqlserver2pgsql
Sqlserver2pgsql is an open source solution written in Perl programming language. It can convert SQL Server schemas into Postgres format and create Pentaho Data Integrator (Kettle) job to migrate the data. There are following prerequisites to start using the tool:
- install Java and Kettle
- export SQL Server database into plain text format dump
- create empty PostgreSQL database
Dump file of SQL Server database can be created from SQL Server Management Studio as follows:
- open SSMS and highlight the database to export
- select Task > Generate Scripts menu item, click ‘Next’ on welcome screen
- select ‘Script Indexes’ option in the list of database objects to export and click ‘Next’
- select the tables to export and click ‘Next’
- specify file name of the resulting script and click ‘Next’
- pass through the rest wizard steps until finish
After these steps dump file of SQL Server database is generated and you can convert the source schemas into Postgres format. For this purpose, run the command line:
perl sqlserver2pgsql.pl -f my_sqlserver_script.txt -b name_of_before_script -a name_of_after_script -u name_of_unsure_script
where my_sqlserver_script.txt is the source dump file, name_of_before_script contains SQL statements to run before importing data (create tables, types, etc), name_of_after_script contains SQL statements to create indexes and constraints and it should be run after data is imported into Postgres database. The unsure script contains objects that where migrated without guarantee of correctness, for example views.
To import the data (i.e. generate the corresponding Kettle jobs) run the following command:
./sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k kettledir -sd source -sh 192.168.0.2 -sp 1433 -su dalibo -sw mysqlpass -pd dest -ph localhost -pp 5432 -pu dalibo -pw mypgpass -f sql_server_schema.sql
Where -k parameter specifies the directory to store the Kettle xml files (one file per table to migrate and one more for the overall job)
SQL Server to Postgres Converter
If the approach described above seems too complicated, consider fully automated solutions that were solely designed to migrate databases with just a few clicks of mouse button. One of such solutions is SQL Server to Postgres converter developed by Intelligent Converters.
Key features of the product:
- easy-to-use wizard style interface
- migrates schemas, data, indexes, constraints and views
- works with all modern versions of SQL Server and Postgres (including DBaaS platforms)
- filtering data to migrate through SELECT-queries
- command line support
