In a modern time, there are about 10 top popular relational database management platforms, and PostgreSQL part of the market has increased by 20-30% year over year. Legacy DBMS are holding back entire IT infrastructure of the organization, therefore timely migration to the most efficient platform is extremely important. Manual migration eats up countless engineering hours and causes major risks of data loss or corruption due to human errors.
This is the key reason why a company planning database migration must choose the appropriate software solution to automate and simplify the entire procedure very carefully. Failed migration leads to hours of downtime, damaged data or even worse – completely loss of the corporate data.
Fortunately, there are migration platforms providing safe and reliable way to convert SQL Server database to PostgreSQL. Some of these solutions promise advanced capabilities for efficient migrations, yet demand users to have deep technical skills. On the other hand, most of easy-to-use migration tools have no sufficient capacities to handle enterprise scale databases.
In this whitepaper most popular database migration tools for SQL Server and PostgreSQL are compared in terms of simplicity, efficiency and flexibility.
Major Capabilities of SQL Server to PostgreSQL Converters
The following basic capabilities allow particular platforms to stay above others when talking about succeeded migration for large and complicated databases:
- Schema Intelligence. Converting schemas from SQL Server to PostgreSQL includes safe data types mapping, conversion of the related properties and attributes, handling complex entries such as views and user-defined types.
- Zero Data Loss. SQL Server data must be kept safe and integrity preserved after migration to PostgreSQL database. Best practices include validation of each record being migrated without causing overhead of the migration process.
- Balance between performance and reliability. Fast is good, but safety is important as well when migrating from SQL Server to PostgreSQL. Enterprise level migration tools not only offer high performance of the migration by using efficient approaches such as “parallel snapshot” or “change data capture” but also guarantee reliability by doing comprehensive backups and point in time recovery (when it is required).
- Real-Time Monitoring. The best approach to avoiding data loss or another kind of disaster during database migration is real-time monitoring that is migration progress indication on table and row levels and comprehensive error tracking.
- Post-migration Validations. Once SQL Server to PostgreSQL database migration is completed, it is time to verify that every single operation or transaction has been done as expected. Two most popular metrics to use during validation is count of objects (tables, rows, indexes, etc) and checksum of row data. Checksum must be calculated through external algorithms rather than via internal functions provided by major DBMS (such as HASH).
Categories of Database Converters
There are lot of open-source and commercial products for migration from SQL Server to PostgreSQL, but all of them fall into one of the following categories:
- Open-Source Frameworks are usually not purpose-built for migration, yet are widely used by database specialists due to freeware nature and high capabilities of customization. On the other hand, such tools require deep technical skills to set up necessary configuration files and fulfill other prerequisites.
- Commercial extract-transform-load (ETL) tools are commonly used to migrate enterprise scale on-premises databases. Key advantages of this approach are robust conversion, high performance and comprehensive control.
- Cloud based solutions use concept of managed service to give benefits of automatic scaling and ‘pay as you go’ pricing model.
Tools for SQL Server to PostgreSQL Migration
- PgLoader is an open-source multi-purpose data loading tool for PostgreSQL. It uses highly efficient bulk copy capabilities of DBMS and offers high performance up to 3TB per hour on a modern properly tuned platform. Key features:
- Declarative approach allows to configure the tool for wise range of database management tasks including migration
- Handles safe types mapping and most of data quality issues automatically
- Intelligent errors handling and retrying capabilities
- Works with multiple data source formats including SQL Server
- MSSQL-to-PostgreSQL is a commercial database converter developed by Intelligent Converters. The program is very easy-to-use interface and does not require any technical skills to use it. At the same time, it offers high performance migration engine based on bulk reading and writing techniques and parallel processing. Key features:
- Migration of major database objects
- Deep customization of what is migrated and rules (set up types mapping, edit properties of every table and column)
- Simplifying recurrent migrations by serialization settings into profile and quick launch feature (launch via single click on a profile in Windows Explorer)
- Merging and synchronizing PostgreSQL database with SQL Server data
- Pre-processing source data via SELECT-queries
- Amazon Web Services (AWS) Database Migration Service is the ideal choice for companies using Amazon cloud for their databases because of its tight integration with RDS, Redshift, and Aurora PostgreSQL. This solution can handle one-time and continuous migrations with strong real-time monitoring mechanism that is extremely important when dealing with complicated PostgreSQL. Pay-as-you-go pricing makes AWS Database Migration Service quite affordable solution and serverless architecture even more reduces total cost of ownership (TCO). Key features:
- One of most efficient approach for continuous migration – Change Data Capture (CDC)
- Comprehensive tools for assessment and validation
- Excellent monitoring through integration with AWS CloudWatch
- Advanced failover capabilities
- Debezium is open-source platform for continuous migrations to/from PostgreSQL based on change data capture method. It performs real-time tracking of database changes and converts those changes into event streams. Companies may use the platform to implement database replication and synchronization based on event-driven architecture. Key features:
- CDC based on native logs to increasing performance
- Event streaming built on top of Kafka
- Support for distributed operations
- Wide collection of connectors
- Detailed monitoring and metrics
Choosing Suitable Solution for Migration
Proper SQL Server to PostgreSQL converter must fit requirements of the particular database migration project. Software vendors often publish huge lists of product features, but customers always have their own requirements and priorities. Evaluate and compare all selected tools using factors listed below with respect to demands of your migration project:
- Reliability – gather customer testimonials for each migration platform you consider, especially those describing similar tasks. Review the documentation, check responsiveness of support team.
- Scalability – find out if the tool is able to handle data volumes slightly exceeding the current size of your SQL Server and PostgreSQL databases. What about complex schemas, user-defined data types, partitioning and other non-trivial features that may present in the source database? Check if any benchmarks are available in the Web.
- Migration Features – focus on the capabilities you actually need for migration project, for instance, continuous migration, real-time monitoring, rollback options. Do not let yourself be confused by numerous features announced by the software vendor.
- Technical Requirements – both target database management system and migration platform must match skills of the company’s IT team. Advanced features make no sense if there is no opportunity to use them.
Conclusion
This whitepaper explores basic bottlenecks and challenges of database migration from SQL Server to PostgreSQL, reviews some tools to help with this task and gives a few ideas of the best practices. Find more related articles and guides on the official site of Intelligent Converters.