The best practice of database migration between different DBMS is to use an automated process that ensures accuracy and speed. There are a lot of tools that free up time of database professionals, allowing them more freedom in the development process. This whitepaper explores key features that must be provided by software product automating SQL Server to Postgres migration.
Support major on-premises and cloud platforms of the source and target DBMS. There are three possible configurations of source and target DBMS:
- Both source and destination databases are on-premises
- Source is Azure SQL and Postgres is on-premises
- Source is SQL Server and Postgres database is on a cloud platform
- Both databases are on cloud platforms
Advanced SQL Server to Postgres converted must support all of these variations working with modern versions of SQL Server, Postgres, Azure SQL, Azure for PostgreSQL, Amazon RDS and other popular DBaaS platforms.
Migrate all primary database objects. The most important components of database such as schemas, types, data, indexes, constraints and views must be migrated properly with all necessary properties and attributes. Complex types of SQL Server such as spatial (geometry and geography), xml, blob and clob must be converted properly. Ideally, database logic implemented via stored procedures, functions and triggers should be migrated as well, but it is quite hard task to automate.
Command line support. This feature allows to script and schedule SQL Server to Postgres migration that is a valuable option for the projects dealing with recurrent migration. Also, it allows to call the database migration from another application.
Customize conversion settings. Since every database migration project is unique, option to customize table or column name, type mapping and other rules of migration would be a valuable bonus. And the cherry on the cake is saving all of these settings info a profile to simplify next run of SQL Server to Postgres migration.
Filtering the data. Filtering or another pre-processing the migrated data is a flexible option than can be used for many purposes. The most straight forward method to implement it is to allow user compose SELECT-queries and process result of these queries as a regular table. Here are few examples of using this capability during SQL Server to Postgres database migration:
- Filtering records by particular column SELECT * FROM tbl1 WHERE ID > 300
- Select and rename individual columns SELECT fn as FirstName, ln as LastName FROM People
- Skip NULL values SELECT * FROM documents WHERE comments IS NOT NULL
Export into PostgreSQL script. For those cases when direct connection to the target server is not available, it is very useful if the migration tool allows to export SQL Server database into PostgreSQL script file containing SQL statements to create database objects and load the data.
Intelligent Converters provides fast and reliable tool for SQL Server to Postgres database migration supplied with all features listed above. Besides, their expert team guarantees hassle-free conversions ensuring accuracy of results every time.
