RDS to Aurora data source migration in AWS QuickSight

 

Recently, we came across a problem when we migrated our existing RDS to Aurora. We have already developed a lot of QuickSight reports using RDS. Once we migrated to Aurora, there was no direct way to change the data source connection from RDS to Aurora.

Data source editing using the AWS console is allowed to change the instance id, username, and password.

The following is the step to edit the data source connection using the AWS console.

1. Select Datasets on the left side of the QuickSight, and then click the New Dataset button in the top right corner.
2. Scroll down to the FROM EXISTING DATA SOURCES section and select a data source.
3. In the popup, click Edit Data Source.
4. Change the required details like instance ID, username, and password.
5. Click "Validate connection."
6. If the connection validates, click Update data source.

 

The above steps will help you to update your RDS connection. If you want to change the connection from RDS to Aurora or any other connection, you can use the following AWS CLI commands.

Step 1: We wanted to know the data source id to edit via AWS CLI. To know the data source ID, run the following command in the command line. which will list all the available data sources along with their data source id

aws quicksight list-data-sources --aws-account-id <<account id> --region <<Region>>

Step 2 : Generate an update skeleton using the following AWS CLI command.

aws quicksight update-data-source --generate-cli-skeleton input > edit-data-source.json 

JSON contains following content which includes all the section

"{
    "AwsAccountId": "",
    "DataSourceId": "",
    "Name": "",
    "DataSourceParameters": {
        "AmazonElasticsearchParameters": {
            "Domain": ""
        },
        "AthenaParameters": {
            "WorkGroup": ""
        },
        "AuroraParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "AuroraPostgreSqlParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "AwsIotAnalyticsParameters": {
            "DataSetName": ""
        },
        "JiraParameters": {
            "SiteBaseUrl": ""
        },
        "MariaDbParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "MySqlParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "OracleParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "PostgreSqlParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "PrestoParameters": {
            "Host": "",
            "Port": 0,
            "Catalog": ""
        },
        "RdsParameters": {
            "InstanceId": "",
            "Database": ""
        },
        "RedshiftParameters": {
            "Host": "",
            "Port": 0,
            "Database": "",
            "ClusterId": ""
        },
        "S3Parameters": {
            "ManifestFileLocation": {
                "Bucket": "",
                "Key": ""
            }
        },
        "ServiceNowParameters": {
            "SiteBaseUrl": ""
        },
        "SnowflakeParameters": {
            "Host": "",
            "Database": "",
            "Warehouse": ""
        },
        "SparkParameters": {
            "Host": "",
            "Port": 0
        },
        "SqlServerParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "TeradataParameters": {
            "Host": "",
            "Port": 0,
            "Database": ""
        },
        "TwitterParameters": {
            "Query": "",
            "MaxRows": 0
        },
        "AmazonOpenSearchParameters": {
            "Domain": ""
        },
        "ExasolParameters": {
            "Host": "",
            "Port": 0
        }
    },
    "Credentials": {
        "CredentialPair": {
            "Username": "",
            "Password": "",
            "AlternateDataSourceParameters": [
                {
                    "AmazonElasticsearchParameters": {
                        "Domain": ""
                    },
                    "AthenaParameters": {
                        "WorkGroup": ""
                    },
                    "AuroraParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "AuroraPostgreSqlParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "AwsIotAnalyticsParameters": {
                        "DataSetName": ""
                    },
                    "JiraParameters": {
                        "SiteBaseUrl": ""
                    },
                    "MariaDbParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "MySqlParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "OracleParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "PostgreSqlParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "PrestoParameters": {
                        "Host": "",
                        "Port": 0,
                        "Catalog": ""
                    },
                    "RdsParameters": {
                        "InstanceId": "",
                        "Database": ""
                    },
                    "RedshiftParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": "",
                        "ClusterId": ""
                    },
                    "S3Parameters": {
                        "ManifestFileLocation": {
                            "Bucket": "",
                            "Key": ""
                        }
                    },
                    "ServiceNowParameters": {
                        "SiteBaseUrl": ""
                    },
                    "SnowflakeParameters": {
                        "Host": "",
                        "Database": "",
                        "Warehouse": ""
                    },
                    "SparkParameters": {
                        "Host": "",
                        "Port": 0
                    },
                    "SqlServerParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "TeradataParameters": {
                        "Host": "",
                        "Port": 0,
                        "Database": ""
                    },
                    "TwitterParameters": {
                        "Query": "",
                        "MaxRows": 0
                    },
                    "AmazonOpenSearchParameters": {
                        "Domain": ""
                    },
                    "ExasolParameters": {
                        "Host": "",
                        "Port": 0
                    }
                }
            ]
        },
        "CopySourceArn": ""
    },
    "VpcConnectionProperties": {
        "VpcConnectionArn": ""
    },
    "SslProperties": {
        "DisableSsl": true
    }
}"

Step 3: Make modifications to the JSON file.Run the following command to update the connection:

aws quicksight update-data-source --cli-input-json file:// edit-data-source.json --region <<Region>>

Example JSON to change data source connection to Aurora

{

    "AwsAccountId": <<aws account id>>

    "DataSourceId": <<Datasource nam>>,

    "Name": <<datasoure name>>,

    "DataSourceParameters": {

       "MySqlParameters": {

            "Host": “<<Aurora hostname >>",

            "Port": <<port_number>>,

            "Database": "<<database_name>>"

        }

    },

    "Credentials": {

        "CredentialPair": {

            "Username": "<<user name>>",

            "Password": "<<password>>",

       

        },

        "CopySourceArn": ""

    },

    "VpcConnectionProperties": {

        "VpcConnectionArn": "<<VPC ARN>>”

    },

    "SslProperties": {

        "DisableSsl": false

    }

}

Migrating from AWS RDS MYSQL 5.7 to Aurora 3+ (8.0 compatible)

 

When we plan for a major upgrade, it requires a lot of planning activity in terms of testing and fixing issues.

As Aurora MYSQL is compatible with MySQL, our migration from MYSQL to Aurora will not create a major issue for our application.

Migration from MYSQL 5.7 to Aurora 3+, which is compatible with MYSQL 8.0, is not very easy.

Generally, major MYSQL upgrades require a lot of planning and testing. Major upgrades performed directly in the protection instance are not recommended.

Once upgraded, we can’t revert to the previous version of the database engine. If we want to return to the previous version, we can restore the first DB snapshot taken before the migration. Until the upgrade is completed, the DB server will not be available.

There are two ways we can upgrade.

·         There is very little downtime. When we do an upgrade

·         No downtime using AWS DMS.

This document covers the minimal downtime option.

Upgrading MYSQL 5.7 to Aurora 3+ requires the following steps.

1.       migrating from 5.7 to 8.0.23.

2.       RDS MYSQL 8.0.23 to Aurora 3+ migration using snapshot migration

3.       Change the application connection string

4.       Change the Quicksight datasource

 

Migrate MYSQL RDS from 5.7 to 8.0.23

As I said earlier, we shouldn’t directly try a major upgrade on the production servers. First it should be done in the testing environment because a major upgrade requires downtime. When we do a major upgrade, sometimes the upgrade will fail, because it will check for compatible issues. If there are any compatible issues, the upgrade will not continue. It will report issues in the UpgradeFailure.log file, which will be available in the logs and events section.

Following are the general issues listed in the AWS document site

·         There must be no tables that use obsolete data types or functions.

·         There must be no orphan *.frm files.

·         Triggers must not have a missing or empty definer or an invalid creation context.

·         There must be no partitioned table that uses a storage engine that does not have native partitioning support.

·         There must be no keyword or reserved word violations. Some keywords might be reserved in MySQL 8.0 that were not reserved previously.

·         For more information, see Keywords and reserved words in the MySQL documentation.

·         There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary.

·         There must be no obsolete SQL modes defined in your sql_mode system variable setting.

·         There must be no tables or stored procedures with individual ENUM or SET column elements that exceed 255 characters or 1020 bytes in length.

·         Before upgrading to MySQL 8.0.13 or higher, there must be no table partitions that reside in shared InnoDB tablespaces.

·         There must be no queries and stored program definitions from MySQL 8.0.12 or lower that use ASC or DESC qualifiers for GROUP BY clauses.

·         Your MySQL 5.7 installation must not use features that are not supported in MySQL 8.0.

·         For more information, see Features removed in MySQL 8.0 in the MySQL documentation.

·         There must be no foreign key constraint names longer than 64 characters.

·         For improved Unicode support, consider converting objects that use the utf8mb3 charset to use the utf8mb4 charset. The utf8mb3 character set is deprecated. Also, consider using utf8mb4 for character set references instead of utf8, because currently utf8 is an alias for the utf8mb3 charset.

 

Steps to migrate

1.       Select the database and press the Modify button

2.       In the modification screen, change the DB engine version and click continue.

3.       In the scheduling of modifications option, select apply immediately to apply immediately and click Modify DB instance.

Once it done it will take few min to complete.

Migrate MYSQL RDS 8.0.23 to Aurora 3+

 

1.       Select the database.

2.       From the Action drop-down button, select Migrate Snapshot.

3.       In the migrate database screen, give the proper database snapshot name, VPC, securitygroup, etc., and click on Migrate.

It will take few min to complete