Insight SQL Testing Getting Started Guide

About this document

This is a getting started guide to start Insight SQL Testing Manager (SQL Testing Manager) on Amazon EC2 (EC2).
- Start SQL Testing Manager on an EC2 instance from AMI.
- Use AWS Migration feature to collect SQL from the source DB and execute SQL on the target DB.
Follow the instructions in the Insight SQL Testing document for further details.
For the Japanese version of documentation, please see the following online manual:
Insight SQL Testing マニュアル

1. Prepare SQL Testing Manager

1.1. Start SQL Testing Manager on an EC2 instance

Start an EC2 instance with the following settings:
- AMI: select SQL Testing Manager AMI at AWS Marketplace.
- Instance type: 4 vCPUs or more, 8GB of RAM or more (m5.xlarge is recommended.)
- Storage: 50GB for a monitored database instance (gp3 is recommended.)
  See the Insight SQL Testing manual for more details.
  - /dev/sdb: online data, Insight SQL Testing working area
  - /dev/sdc: backup data
  - /dev/sdd: WAL log area
- Security group: SSH (22), HTTP (7777)
  - Port 7777 is used for the SQL Testing Manager Web UI.
Logon to the EC2 instance with the ec2-user.

1.2. Setup SQL Testing Manager

Recognize expanded volume.
If you have expanded volume size, you must use file system–specific commands to extend the file system to the larger size.
```
sudo xfs_growfs -d /mnt/piso-data
sudo xfs_growfs -d /mnt/piso-backup
```

1.3. Web UI access

You can access SQL Testing Manager Web UI from the following URL:
http://<SQL Testing Manager’s IP address>:7777/idt/
The default username is "administrator", and the default password is your EC2 instance ID.
- Set the license Key from the SQL Testing Manager Web UI.
- After the administrator login, create a "normal" user account.
- Then log in to the SQL Testing Manager Web UI with the user name.

AWS Migration is a feature of SQL Testing Manager that allows you to repeatedly create an SQL workload and run an assessment periodically during a pre-scheduled period.
It creates a test source DB and target DB from a database snapshot, and performs an assessment specifying the two databases.

Here, we will explain how to execute AWS Migration, assuming a situation where Amazon Aurora PostgreSQL 10.21 is used as the source DB and migrated to Amazon Aurora PostgreSQL 14.4.
It also assumes that the SQL Testing Manager is in an open subnet (that has internet connectivity).

Situation: Migrating from Amazon Aurora PostgreSQL 10.21 to 14.4

Source DB: Amazon Aurora PostgreSQL 10.21
Cluster identifier: sql-testing-getting-started-guide-v10-21-no1
Assume that the source DB has a database named "insight" and a user with the same name.
Test source DB: Amazon Aurora PostgreSQL 10.21 that is restored from a snapshot
Target DB: Amazon Aurora PostgreSQL that is upgraded from 10.21 to 14.4
Region: ap-northeast-1 (Asia Pacific (Tokyo))

The upgraded version 14.4 is the version that is available when this guide is created. If the version 14.4 is not available, replace it with an available version accordingly.

2.1. Preparation

Configure log file output for Amazon Aurora PostgreSQL (source DB), from which the SQL is collected.
Here, we enable the log output on Amazon Aurora PostgreSQL in order to get the default PostgreSQL logs and use it for AWS Migration.
1. Open the Amazon RDS Console and set the DB cluster parameter group as follows:
  - Set log_statement to ALL.
  - Set log_rotation_age to 10.
  - Set log_connections to 1 (on).
  - Set log_disconnections to 1 (on).
  - Set log_filename to postgresql.log.%Y-%m-%d-%H%M.
2. Reboot the instance.

Set an IAM role to the EC2 instance where SQL Testing Manager was built.
Create the following IAM policy from the IAM console of AWS and assign a role to the EC2 instance of SQL Testing Manager.
Replace "xxxxxxxxxxxx" with your AWS account ID and "ap-northeast-1" with your region.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "rds:DescribeDBEngineVersions",
                "rds:DescribeOrderableDBInstanceOptions",
                "ec2:DescribeSecurityGroups"
            ],
            "Resource": "*"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "rds:DownloadDBLogFilePortion",
                "rds:DescribeDBInstances",
                "rds:DownloadCompleteDBLogFile",
                "rds:DescribeDBLogFiles",
                "rds:DescribeDBClusters"
            ],
            "Resource": [
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:cluster:*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:db:*"
            ]
        },
        {
            "Sid": "VisualEditor2",
            "Effect": "Allow",
            "Action": [
                "rds:StartDBCluster",
                "rds:StopDBCluster",
                "rds:StartDBInstance",
                "rds:StopDBInstance",
                "rds:CreateDBCluster",
                "rds:CreateDBInstance",
                "rds:ModifyDBInstance",
                "rds:ModifyDBCluster",
                "rds:AddTagsToResource",
                "rds:RestoreDBClusterFromSnapshot",
                "rds:RestoreDBInstanceFromDBSnapshot",
                "rds:DescribeDBClusterParameterGroups",
                "rds:DescribeDBParameterGroups",
                "rds:DescribeOptionGroups",
                "rds:DescribeDBSubnetGroups",
                "rds:DescribeDBSnapshots",
                "rds:DescribeDBClusterSnapshots",
                "rds:DescribeDBInstances",
                "rds:DescribeDBClusters",
                "rds:DeleteDBCluster",
                "rds:DeleteDBInstance"
            ],
            "Resource": [
                "arn:aws:rds::xxxxxxxxxxxx:global-cluster:*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:db:sql-testing-*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:cluster:sql-testing-*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:pg:*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:cluster-pg:*",
                "arn:aws:rds:*:*:snapshot:*",
                "arn:aws:rds:*:*:cluster-snapshot:*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:subgrp:*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:secgrp:*",
                "arn:aws:rds:ap-northeast-1:xxxxxxxxxxxx:og:*"
            ]
        }
    ]
}

Prepare a snapshot of Amazon Aurora PostgreSQL (source DB).
From the snapshot of source DB, we’ll create a test source DB (restore) and target DB (upgrade) for SQL execution.

2.2. Create a new AWS Migration

From the SQL Testing Manager Web UI, click [AWS MIGRATION].
A list of configured AWS Migration projects is displayed.
Click [New] on the AWS Migration list screen.
Follow the steps below to configure the settings:
- Set Migration information
- SQL workload setting
- Target Database Setting
- Assessment setting
- Schedule Setting
- Confirmation
After finishing those settings, run validation and create an AWS Migration project.

Even if validation succeeds, the processing may not start depending on the status of your AWS account.

2.2.1. Set Migration information

Set the AWS Migration project name. For example, enter "aws-migration-test" and proceed to the next step.
You can add notes optionally.

2.2.2. SQL workload setting

Set the source DB from which the SQL is collected.
Enter the information of the source DB and the SQL collection start date as shown below, and proceed to the next step:

Region: ap-northeast-1
DB identifier/Cluster identifier: sql-testing-getting-started-guide-v10-21-no1
Database name: insight
SQL collection start date: <Set the start date.>

2.2.3. Target Database Setting

Set the test source DB and target DB for SQL execution.
Set the security group and upgrade version for the target DB, and proceed to the next step.

RDS Snapshot ARN: arn:aws:rds:ap-northeast-1:12345678912:cluster-snapshot:sql-testing-getting-started-guide-v10-21
DB instance class: db.t3.medium
Region: ap-northeast-1
Security group: sg-example (If there are multiple groups to add, separate them with ",".)
Upgrade Version: 14.4

When creating an upgraded database from AWS Migration, a snapshot is automatically created on the AWS side.
Delete the created snapshot if it is no longer needed.
RDS instances and Aurora DB clusters are billed on AWS after launch, and even if they are stopped, they will be restarted if they are not used for 7 days.
SQL Testing Manager does not restart RDS instances or Aurora DB clusters after rebooting.
Users are responsible for managing RDS instances and Aurora DB clusters.

2.2.4. Assessment setting

Set the execution type of assessment, whether to reflect data to the DB, comparison settings for column names, etc.
Here, we leave the assessment settings as default, enter only the DB user name and password, and proceed.

DB user
- User name: insight
- Password: *********

2.2.5. Schedule Setting

Set the assessment schedule and end time to collect SQL information and repeat assessment execution.
Here, we set the assessment to execute every hour.
If you set "HOURLY", it will be executed with the timing based on 0 o’clock regardless of the execution start timing.

Schedule Setting: Set schedule
Migration period: <Set the end date.>
Assessment frequency: HOURLY
Executed every: 1

2.2.6. Confirmation

Finally, check the settings.
Click [VALIDATION] to connect to AWS and it’ll be confirmed whether the entered values are correct or not.

If the validation succeeds, a message is displayed as the image below, and [NEW] can be clicked.
Click [NEW] to create an AWS Migration project.

2.3. Confirm AWS Migration result

The newly created AWS Migration project is added to the list, and after the test source DB/target DB is created, it will be in the execution waiting state (if the Schedule Setting is enabled).
At the set processing timing, the log from the source DB is acquired, the SQL workload is created, and the assessment is executed.

Select the Migration project name from the list of AWS Migration.
The migration configuration information and a list of completed assessments are displayed.
- You can download the assessment results in CSV file format by clicking [Download] at the top right of the screen.
- Select the assessment name from the list at the bottom of the screen and check the results from the assessment summary.