SQL Server DBMS has gained worldwide popularity due to its ease of installation and configuration, advanced security features including encryption, excellent data recovery capabilities and a lot of convenient tools.
On the other hand, SQL Server is gradually losing its users due to a number of limitations such as restrictive license and maintenance cost that increases as the database size or the number of clients increases. Although the DBMS can be installed on miscellaneous OS, it is optimized for Windows only. Besides, maximum size of its database is limited by 10 GB, and the buffer cache has limit of 1 MB.
PostgreSQL is a free open-source DBMS that is available for Windows, Mac, Linux, FreeBSD and Solaris. This DBMS comes with a lot of open-source add-ons for administration and development purposes and is supported by huge international community of loyal users and developers.
This article introduces three tools for SQL Server to PostgreSQL migration (two of them free and the third is commercial) and demonstrates how to migrate between the two databases step-by-step.
Migrate using SSIS
Take the steps below to migrate SQL Server to PostgreSQL using SQL Server Integration Services:
- PostgreSQL ODBC Driver for Windows x86/x64 must be installed to work with this approach.
- Launch SSMS, right-click on the source database at the left pane and select menu item Tasks → Export Data.
- Configure the data source and destination choosing SQL Server Native Client and.Net Framework Data Provider for ODBC driver correspondingly.
- Compose the appropriate database connection strings for the data source and PostgreSQL target.
For 32-bit systems
Driver={PostgreSQL UNICODE};Server=PostgreSQL server;Port=connection port;Database=database name;Uid=username;Pwd=password;
For 64-bit system
Driver={PostgreSQL UNICODE (x64) };Server= PostgreSQL server;Port= connection port;Database= database name;Uid=username;Pwd=password;
- Select source tables and then you will be able to customize the default types mapping for migration.
Migrate via Sqlserver2pgsql
Sqlserver2pgsql is a Perl script to convert SQL Server schemas into PostgreSQL format.
Prerequisites:
- SQL Server connection details, including IP, port, username, password and database
- Access to an empty PostgreSQL target database with sufficient privileges to create objects and write the data.
Create a Docker image
Docker is a great tool for creating SQL Server to PostgreSQL database migration procedure since it can be applied to most hardware platforms and OS.
In order to create and run Java applications, OpenJDK 8 image must be created through the following statement:
FROM adoptopenjdk/openjdk8
Setup primary connection details (host, port, username, password and database) for the source and destination DBMS via environment variables MSS_HOST, MSS_PORT, MSS_USER, ENV MSS_PWD, MSS_DB, PGS_HOST, PGS_PORT, PGS_USER, PGS_PWD, PGS_DB.
Configure the working folder:
ENV MIGRATION_FOLDER=/opt/data_migration
RUN mkdir -p $MIGRATION_FOLDER
Install necessary tools to work with Sqlserver2pgsql program:
RUN apt-get update; apt-get install perl netcat -y; \
apt-get install wget unzip postgresql-client -y
Install Pentaho Data Integration (aka Kettle), famous open source integration solution that can be used for database migration:
RUN wget –progress=dot:giga https://sourceforge.net/projects/pentaho/files/latest/download?source=files -O /tmp/kettle.zip; \
unzip /tmp/kettle.zip –d /tmp/kettle; \
mv /tmp/kettle/data–integration $MIGRATION_FOLDER; \
chmod –R +x $MIGRATION_FOLDER/data–integration/*.sh
Download and install jTDS JDBC driver that is required for connecting to SQL Server from Kettle job:
RUN wget https://sourceforge.net/projects/jtds/files/latest/download?source=files –O /tmp/jtds.zip; \
unzip /tmp/jtds.zip –d /tmp/jtds; \
cp /tmp/jtds/jtds-*.jar $MIGRATION_FOLDER/data–integration/lib/; \
rm –Rf /tmp/jtds;rm –f /tmp/jtds.zip
Set up bash script to launch Kettle-job:
COPY ./scripts /scripts
RUN chmod +x /scripts/*.sh
WORKDIR $MIGRATION_FOLDER
Convert SQL Server schemas into PostgreSQL scripts:
echo !!! Set up Kettle job && \
./sqlserver2pgsql.pl -b before.sql -a after.sql -u unsure.sql -k kettlejobs -stringtype_unspecified -f conf/$SCHEMA_FILE \
-sh $MSS_HOST -sp $MSS_PORT -su $MSS_USER -sw $MSS_PWD -sd $MSS_DB \
-ph $PGS_HOST -pp $PGS_PORT -pu $PGS_USER -pw $PGS_PWD -pd $PGS_DB
Kettle-job files are stored in $MIGRATION_FOLDER/kettlejobs/migration.kjb. Next step is to run the before.sql script, which contains the PostgreSQL commands used to create the table structures.
Run Kettle-job as follows:
echo !!! Running Kettle job && \
data-integration/kitchen.sh -file=kettlejobs/migration.kjb -level=rowlevel
Finally, run after.sql script containing post-processing steps required to complete SQL Server to PostgreSQL migration.
Once all prerequisite procedures are completed as it is specified above, it is time to create a Docker container image that will handle the database migration:
docker build -t sqlserver2psql
As you may see, both free solutions specified above require plenty manual work and extensive experience in composing configuration and batch scripts. That’s why many users confider using commercial software that can migrate SQL Server to PostgreSQL with just a few clicks of mouse buttons.
Migration tool from Intelligent Converters
Being a software company specializing in database migration and synchronization for years, Intelligent Converters offer the comprehensive solution to migrate between SQL Server and PostgreSQL with easy-to-use wizard style interface and advanced context sensitive help documentation.
Key features of this SQL Server to PostgreSQL converter:
- All modern versions of on-premise and cloud DBMS are supported
- Fast migration due to efficient low-level techniques of reading and writing data, ODBC or another middleware is not required
- Options to merge or synchronize existing PostgreSQL database with SQL Server data
- Careful migration of all primary database objects (schemas, data, indexes, constraints and views)
- Command line support allows running the database migration from scripts or integrating it into customer applications
- Lot of options to customize the migration procedure (direct migration or import into SQL Script, data migration via COPY or INSERT statements, renaming tables and columns, type mapping customization and others)
Visit official site of the vendor to learn more about their SQL Server to PostgreSQL converter.