Migration of on-premises databases to cloud has become a long tern trend a while ago. One of the most popular types of such projects is database migration from SQL Server to PostgreSQL DBMS hosted on a SAAS platform.
The migration is traditionally implemented via extract-transform-load approach that includes few steps as follows:
- Extract table definitions from SQL Server database in form of DDL statements to create tables, related indexes and constraints
- Transform those statement with appropriate type mapping and conversion of keywords and embedded functions according to PostgreSQL syntax
- Load the resulting DDL statements into the PostgreSQL database
- Extract data of every table in form of comma separate values files (one file per table)
- Convert extracted data with respect to difference in string representation of binary and date types in SQL Server and PostgreSQL
- Import the resulting CSV files into the PostgreSQL database
Migration to the target database hosted on a cloud platform goes according to the same technique. However, there are some nuances caused by SAAS nature of the target database. They are:
- Connectivity must be configured by adding IP address of data source machine in allowed list of the SAAS target
- Some cloud database management systems such as Azure for PostgreSQL requires secured connection via Secure Sockets Layer (SSL).In order to meet this requirement client application must enable Transport Layer Security (TLS) and set root certificate specified by particular SAAS provider
- Database cloud platforms often appliescustom requirements to names of database entries, attributes of indexes, vocabularies of keywords and others
Specialists responsible for database migration to cloud must pay attention to every challenge listed above.
Most of cloud database management platforms provide special tools to simplifyand partially automatedatabase migration. For example, Azure platform offers Database Migration Service that helps to migrate Oracle, MySQL, PostgreSQL, SQL Server and other popular data sources to Azure. However, those tools still require manual efforts to configure data source, set up connectivity and specify appropriate migration settings.
Users who need to migrate a lot of databases to cloud should consider using dedicated third-party solutions. One of the most popular database migration tools from on-premisesSQL Server to cloud PostgreSQL is MSSQL-to-PostgreSQL converter developed by Intelligent Converters.
Besides all the basic capabilities that must be provided by advanced SQL Server to PostgreSQL converters, the tool supports migration to cloud variations of PostgreSQL such as Azure or Amazon RDS. Also, it supports SSL connection to PostgreSQL with option to set custom certificate as well as to use standard one suitable for Azure. Having easy-to-use interface, MSSQL-to-PostgreSQL converter allows to configure and launch the entire migration procedure within just a few button clicks.
