Nowadays many companies migrate their databases to free open-source platforms. One of the most popular cases in this trend is MS SQL to Postgresql migration. Often some part of the data is still storing in the original DBMS, so it becomes very important to keep source and destination databases up to date. For this purpose, Database administrators run database synchronization procedure.
One of the most efficient approaches to this task is incremental synchronization. The basic idea of this technique is to extract modified data from the source database and then replicate it into the destination database on required schedule. It is not surprising that incremental synchronization method provides excellent performance since it reduces volume of the data being transferred. This featureis especially important for huge corporate scale data warehouses working with low-latency data over heterogeneous network and cloud variations of SQL Server (such as Azure) and PostgreSQL (such as Amazon RDS).
Usually, incremental synchronization between MySQL and PostgreSQL is implemented through one of the following techniques:
- Transaction Log-based
- Trigger-based
Transaction log-based approach is working as both SQL Server and PostgreSQL provide logging feature. MS SQL implements transaction log as a separate file or set of files in the database. PostgreSQL captures binary changes to the data files that is known as Write-Ahead Logging (WAL) feature. Both database management systems provide access to those transaction logs through native plugins. This method requires change log data to be exported in the internal table having particular format. Then the internal table is replicated to PostgreSQL that caused the data is synchronized with SQL Server.
Database synchronization according to trigger-based approach requires insert, update and delete triggers are built for each table of SQL Server database. Every trigger stored corresponding modifications of the table into special service table that is a custom change log for this method of synchronization. Then sync tool goes through all entries of that table and performs the same changes into PostgreSQL database.
Manual implementation of any approaches to incremental synchronization between SQLServer and PostgreSQL as specified above is quite complicated and tedious procedure. That is why many database administrators use special tools for database synchronization. MSSQL-PostgreSQL Sync is one of such tools developed by Intelligent Converters, software vendor specializing in database migration and synchronization since 2001.
Features of MSSQL-PostgreSQL Sync:
- High performance of trigger-based incremental synchronization
- All versions of SQLServer and PostgreSQL are supported including SAAS variations
- Bi-directional synchronization SQL Server->PostgreSQL and PostgreSQL->SQL Server
- Synchronization settings can be stored into profile to simplify next runs
- Support for command line allows to script and schedule sync procedure
Like every software product offered by Intelligent Converters, MSSQL-PostgreSQL Sync is supplied with 24/7 support service and 1-year subscription for updates. More details can be found on the product page: https://www.convert-in.com/m2psync.htm
