Migration from SQL Server to PostgreSQL AWS RDS

Hi Friends,

Last week, I have done one of the POC for migrating one of existing on-premises SQL server database to PostgreSQL AWS RDS and here I am writing my experience and findings and surely this will help you in your migraiton if you are planning or will plan in future.

 

AWS provides,

 

1. SCT (Schema conversion tool), to convert your source database schema which includes everything like (table, SP, function, view, trigger, index etc.) to target database standard schema and then you apply that conversion of schema to your target database by same tool.
2. DMS (Database migration service), this will help you to migrate your data from source database to target database. This also help you to migrate table schema but no other schema like SP, function etc. So in case if you have use case just to migrate data then you can only go with DMS and you do not require SCT and it will migrate your table schema and all the available data to your target database. 

 

Below are various link which will help you to understand in details on various aspects including step by step activities to perform. Specially you the yellow highlighted one will help you to go step by step and migrate your on-premises SQL server to AWS RDS(PostgreSQL).

 

Also in below section you will find all the challenges areas and key points(Tips) which I have noted during my migration and will help you in your migration.

 

https://www.ispirer.com/products/sql-server-to-amazon-aurora-postgresql-migration --understanding benefits of migration and using AWS RDS

 

https://docs.aws.amazon.com/dms/latest/sbs/DMS-SBS-Welcome.html --various migration plan and step by step documentation

 

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html - migrating to PostgreSQL(target) requirement and limitations like available data types etc.

 

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html  - migrating from sql server(source) requirement and limitations like available data types etc.

 

https://docs.aws.amazon.com/dms/latest/sbs/CHAP_Introduction.html --AWS migration tool (SCT - schema conversion tool) + DMS (database migration service)

 

https://docs.aws.amazon.com/dms/latest/sbs/CHAP_SQLServer2Aurora.html -- migration steps (sql server to postgresql)

 

 

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateDBInstance.html --document to creating AWS RDS instance

 

https://aws.amazon.com/getting-started/tutorials/create-connect-postgresql-db/ --document to create AWS PostgreSQL instance and connect

 

 

https://d1.awsstatic.com/asset-repository/products/AWS%20Database%20Migration%20Service/SQL%20Server%20to%20Aurora%20PostgreSQL%20Migration%20Playbook%201.0%20Preliminary.pdf  --having all PostgreSQL features details and syntax as compare to your source db migration

 

 

https://www.youtube.com/watch?v=WeXXtBNtwxk --AWS console, migration demo from SQL server to PostgreSQL

 

https://www.youtube.com/watch?v=5eF9_UB73TI --migration view CLI/PowerShell

 

https://www.youtube.com/watch?v=yIC1p3f-dt8 --AWS console, migration demo from sql server on premises to aws rds sql server

 

Basic Steps are-

 

1. Create sql instance on aws rds

 

2. Database, db right click ->task, export, data source, sql server native client and put source and target db(Aws db details), next and map and finished

 

Note : Your IAM user role must have required access of RDS and DMS (full or administrative access)

 

Client tool/UI to connect : Once your PostgreSQL is configured/hosted and ready to use then there are multiple client tool/UI to connect and work with query-

 

• PostgreSQL client tool (PGAdmin) to connect with PostgreSQL instance and work with query similar to sql management studio. You have to download and install it (postgresql-11.7-2-windows-x64.exe).

 

C:\Program Files\PostgreSQL\11\pgAdmin 4\bin\pgAdmin4.exe --once install then you have to run the exe and then it will start running on localhost with         dynamic port each time like below.

 

http://127.0.0.1:54942/browser/  or you can copy the URL from the taskbar right corner icon which start appearing when you run the above exe.

 

• Similar to above (PGAdmin), we can also use SQL workbench as client tool to connect with PostgreSQL but this require java to be install.

 

SQL Workbench http://www.sql-workbench.eu/

 

• Similar to above (PGAdmin or SQL-Workbench) if you want to connect the PostgreSQL with SQL management studio then you and do the same with ODBC driver as liked server

 

https://www.devart.com/odbc/postgresql/docs/microsoft_sql_server_manager_s.htm

 

 

PostgreSQL does not provide jobs/agent option, you have to user any other tool/agent like pgAgent, Task Scheduler

 

https://sqlbackupandftp.com/blog/postgresql-job-scheduler   or   https://www.dbrnd.com/2015/05/how-to-create-job-in-postgresql/

 

 

To work with .net visual studio(C#) to connect PostgreSQL, the standard ADO.Net code for SQL is bit different for PostgreSQL and for that you have to install the Npgsql package and then you have to use their respective commands like NpgsqlConnection instead of standard ADO.Net SQLConnection.

 

Code Examples you will find below.

 

PM> Install-Package Npgsql   http://www.npgsql.org/  or   http://zetcode.com/csharp/postgresql/

 

 

PROBLEM AREAS OF MIGRAITON TO POSTGRESQL FROM SQL SERVER

 

• Schema conversation tool(SCT) more 200+ having conversion issue and you have to manually correct this.
• DATA TYPE MISMATCH, WHICH WILL CAUSE HUGE SPECAILLY BECUASE OF ENTITY FRAMEWORK BEING USE CORRECTION AND THAT MANUALLY HAS TO CHANGE IN CODE
• String/Character is case sensitive with postgrad like where condition
• LOTS OF SYNTAX MISMATCH SPECIALLL THE SP WHICH RECENTLY THEY HAVE LAUNCHED IN VERSION 11 AND STILL PROBLEM WITH RESULTSET RETURN
• C# Syntax is different for PostgreSQL connection and query/sp/function execution and you have to manually change your code.
• JOB, BACKUPS DOES NOT SUPPORT, OTHER TOOL you have to use.
• Table hint does not supported
• Clustered index does not supported
• Automatic conversion of collation is not supported
• BCC migration is not currently supported
• Doesn't support the UNPIVOT clause
• CLR function does not supported.
• This will lead lots of effort includes
• "dev(migration - schema conversion issue specially + code changes),
• "testing - entire system to test (API, admin, services),
• "Risk if we left with something to migrate or change,
• "sql job - backups separate monitoring
• "Skills - employee may not have the expertise to work with PostgreSQL and first they have to learn and then work on.

 

KEY   POINTS  TIPS-

 

Overall Steps:

 

1. Your source SQL database must in place and have public access or in same VPC where you are using SCT and DMS. Take note of SQL server IP, database, login id and password to connect.
2. Login to AWS console and create your PostgreSQL RDS instance and take note of ARN, database, super login id and password to connect.
3. Download SCT tool and required driver and configure your source(SQL server) and target(PostgreSQL) and do conversion of source schema and verify the conversion report detail and summary and if you find any conversion issue then correct those conversion and apply all the schema to target DB.
4. Login to AWS console and go to DMS service and here create the replication instance, source target end point (define sql and postgresql connection), task and run the task to migrate your source data to target DB.
5. Now move your code base db connection to PostgreSQL and start using. Or you can use PGAdmin to connect with PostgreSQL and verify your schema, data and run your own query.

 

 SCT tool:

 

1. To use this tool, you have to download multiple software including source and target database driver.
1. Download SCT (AWS Schema Conversion Tool-1.0.632.msi - in my case), which will give you UI to configure your source and target DB and convert your source schema and apply to target.

 

 

2. Download SQL driver(sqljdbc_6.0.8112.200_enu.tar.gz - in my case, post download and extract this, it will give you .jar file to use further), which will help you to connect your source SQL database into SCT tool. Here you make sure to download jar file not the msi or exe one as this will not be installed instead path of this driver need to be configure with SCT tool.

 

 

3. Download PostgreSQL driver(postgresql-42.2.12.jar - in my case), which will help you to connect your target PostgreSQL into SCT tool. Here you make sure to download jar file not the msi or exe one as this will not be installed instead path of this driver need to be configure with SCT tool.

 

 

4. Once SCT successfully install then open that and configure your source and target db including drivers

 

 

 

Note : The driver, you can even configure globally, so that it does not require to select for every project and you can do this by global setting

 

               

Once conversion is done, then you can check the summary and detailed report and post correction of your scrip having conversion issue if any then apply your all converted schema to target db by right click on the target schema or db

 

 

5. Now you can connect to PostgreSQL client like PGAdmin in my case to run your sql queries.

 

         Make sure you have PGAdmin install and running then run the query like below-

 

 

 

6. DMS: Your schema is already migrated and now to migrate your data, login to AWS console and go to DMS service and use the below steps-
1. Create replication instance (this is kind of your backend ec2 which will help you in migration)
2. Create source and target endpoint (when you will specify your source and target DB connection details).

 

Note - Here after creating end point you can do connection test and in case your RDS PostgreSQL is not connecting then you can try with private IP instead of public IP and also verify that your VPC inbound rules.

 

3. Create task and specify your created replication instance, source and target endpoint and runt the task to migrate the data.

 

 

Also while creating task, make sure to configure selection run (which is the source schema selection like which schema and if you want to specify any starting characters like tbl% of your table to migrate else you can leave to just % which means all tables to migrate). Similarly select transformation rule (which is the target schema and this is specially require if your source and target schema name is different like in my case my source SQL server DB schema is .dbo but my target PostgreSQL schema is .rajeevpoc_postgresql_dbo).

 

 Once task is successfully executed then you can again use PGAdmin to run the query and verify the data.

 

 

7. Working with visual studio(C#) to connect with PostgreSQL and fetch data.
1. Here I have created ASP.Net core web application.
2. First you have to install Npgsql package

 

 

 

3. Below are some useful codes to help you.

 

 

using Microsoft.AspNetCore.Mvc;

using Microsoft.AspNetCore.Mvc.RazorPages;

using System;

using System.Collections.Generic;

using Npgsql;

 

namespace WebTestApplication.Pages

{

    public class ViewModel : PageModel

    {

        [BindProperty]

        public List<Data> Datas { get; set; }

        public void OnGet()

        {

            List<Data> datas = new List<Data>(); 

 

 /*------------call SP Example start-------------*/

            // Connect to PostgreSQL

            NpgsqlConnection conn = new NpgsqlConnection("Host=AWS PostgreSQl ARN;Port=****; Username=**; Password=**; Database=RajeevPOC_Postgresql");

            conn.Open();

            // Define a command to call stored procedure show_cities_multiple

            NpgsqlCommand command = new NpgsqlCommand("CALL rajeevpoc_postgresql_dbo.usp_test('MYDATA_p_refcur')", conn);

            //command.Prepare();

            //command.Parameters.AddWithValue("intype","1");

            // Execute the stored procedure and obtain the first result set

            NpgsqlDataReader dr = command.ExecuteReader();

            // Output the rows of the first result set

            while (dr.Read())

            {

                string testresult = Convert.ToString(dr[0]);

            }

            conn.Close();

 /*------------call SP Example end-------------*/

 

            /*------------call function start-------------

            // Connect to PostgreSQL

            NpgsqlConnection conn = new NpgsqlConnection("Host=AWS PostgreSQl ARN;Port=****; Username=**; Password=**;       Database=RajeevPOC_Postgresql");

            conn.Open(); 

            // Define a command to call stored procedure show_cities_multiple

            NpgsqlCommand command = new NpgsqlCommand("rajeevpoc_postgresql_dbo.usp_test44", conn);

            command.CommandType = CommandType.StoredProcedure;

            //command.Parameters.AddWithValue("intype","1");

            // Execute the stored procedure and obtain the first result set

            NpgsqlDataReader dr = command.ExecuteReader();

            // Output the rows of the first result set

            while (dr.Read())

            {

                datas.Add(new Data

                {

                    id = dr["id"] != DBNull.Value ? Convert.ToInt32(dr["id"]) : 0,

                    code = dr["code"] != DBNull.Value ? Convert.ToString(dr["code"]) : "",

                    name = dr["name"] != DBNull.Value ? Convert.ToString(dr["name"]) : "",

                    points = dr["points"] != DBNull.Value ? Convert.ToDouble(dr["points"]) : 0

                });

            }

 

            //--------this is when you have one more table into result set, so like your SP/Function returning 2 table result kind of dataset

            //// Switch to the second result set

            //dr2.NextResult();

            //// Output the rows of the second result set

            //while (dr2.Read())

            //    Console.Write("{0}\t{1} \n", dr2[0], dr2[1]);       

 

            conn.Close();

            ----------------call function end-------------*/  

 

            /*---------select query inline start----------------------

            var conString = "Host=AWS PostgreSQl ARN;Port=****; Username=**; Password=**; Database=RajeevPOC_Postgresql";

            //string conString = "Server=AWS PostgreSQL ARN;Port=****;User Id=**;Password=**;Database=RajeevPOC_Postgresql;";

            using (var con = new NpgsqlConnection(conString))

            {

                con.Open();

                var sql = "select id, code, name, points, 0 totalpoints from rajeevpoc_postgresql_dbo.tbl_data";

                //using (var cmd = new NpgsqlCommand(sql, con))

                using (var cmd = new NpgsqlCommand())

                {

                    cmd.Connection = con;

                    cmd.CommandText = sql; 

                    var dr = cmd.ExecuteReader(); 

                    while (dr.Read())

                    {

                        datas.Add(new Data

                        {

                            id = dr["id"] != DBNull.Value ? Convert.ToInt32(dr["id"]) : 0,

                            code = dr["code"] != DBNull.Value ? Convert.ToString(dr["code"]) : "",

                            name = dr["name"] != DBNull.Value ? Convert.ToString(dr["name"]) : "",

                            points = dr["points"] != DBNull.Value ? Convert.ToDouble(dr["points"]) : 0,

                            totalpoints = dr["totalpoints"] != DBNull.Value ? Convert.ToDouble(dr["totalpoints"]) : 0

                        });

                    }

                }

            }

            -------------select query inline end----------------*/

 

            Datas = datas;

        }

    } 

 

    public class Data

    {

        public int id { get; set; }

        public string code { get; set; }

        public string name { get; set; }

        public double points { get; set; }

        public double totalpoints { get; set; }

    }

}