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

AWS Security options

Now a days cyber security is very important concern in the world. Daily basis everybody getting knowingly or unknowingly lot of cyber-attacks. Protecting data and environment is became lot of challenges. AWS is providing lot of services to protect our cloud environment from cyber-attack. AWS security is shared responsibilities between AWS and us. AWS reduces burden of protecting the infrastructure and services offered by them. Our responsibility is to use the proper tools and services to protect the services what we are using.

Following diagram represent the shared responsibility mode which is provided by AWS. For more details please refer here

 

Next challenge is to protect our application environment. To protect  our environment, it better to follow leading industrial standard cyber security framework. Cyber security framework will provide a set of guidelines, standard and best practice which we can implement.

In the cyber security industry, National Institute of Standards and Technology (NIST) is widely used and popular cyber security framework. NIST cyber framework is having set of guidelines to mitigating organization cyber-attack. The five core framework functions of NIST are listed below

In the context of NIST, AWS is providing various security services to adhere NIST framework. As AWS is having so many securities feature, we no need to use all. Based on the application need we need to use proper security service to protect our application.

Following table contain details of the security services provided by AWS. Following are currently available service based on their site, it may get outdated after sometime as AWS is keep adding more services for security click here to see exact details

As AWS is providing more security services to protect, we need to choose proper tools to protect our environment. Following diagram represent sample security design

 

IAM : This service provides access control to AWS services. Using role groups, Roles and Policies we can control access to one or group of people.

Cloud Trail:  This service records all AWS account activity. This service will be helpful to monitor and detect any unauthorized access

Cloud Watch: This service will be helpful to monitor application. This service we can configure to collect all the application service calls.  By default, it is integrated with 70+ AWS Services.

VPC: Virtual private cloud (VPC) enables us to create virtual network

Secret Manager: This service will provide a feature to store and retrieve application secrets like password. This service easily enables us to store database passwords, access key and other secrets and rotate

KMS : Key management service will help us to create and manage encryption key to encrypt data in rest. It is integrated with aws services to simply encrypt and decrypt data.

Certificate manager: This service will be helpful to quickly create a certificate, deploy it on AWS resources, such as Application Load Balancing, Amazon CloudFront distributions, and APIs on Amazon API Gateway. This also enable us to create private certificate for internal resources

Cognito : This service will be helpful to build authentication control in web and mobile very quickly. Cognito provides user pools and identity pools.    

Security hub : Security Hub collects security data from across AWS accounts, services, and supported third-party partner products and validated against industrial security standards like CIS, PCI DSS and AWS foundation security.

AWS Shield : This service will be helpful to protect for DDoS (Distributed Denial of Service) attack

AWS WAF: This service will protect web applications or APIs against common web exploits and bots that may affect availability and compromise security of application.

AWS Inspector: This service continuously scan EC2 instances and container images for software vulnerability.

AWS Macie: Macie automates the discovery of sensitive data, such as personally identifiable information (PII) and financial data, to provide you with a better understanding of the data that your organization stores in Amazon Simple Storage Service (Amazon S3)

On top of it we can use additional vendor services to further enhance security. AWS security competency partners are available here

Vulnerability Management

Software vulnerabilities are inevitable, we need to have a proper process to identity, evaluating, treating and reporting. In the software development phase Identifying vulnerability is crucial, on top of it identifying severity of vulnerability issue is very critical. As part of identifying activity, not all vulnerabilities will create huge problem, some of them will create huge problem. For example, if we do a code vulnerability scan it list lot of security issue, most of them will not create a problem. 

Vulnerability management encompasses of discovering, evaluating, prioritizing, remediating, and reporting of systems and software vulnerabilities. Our intentions should be remediated before attackers gets opportunity. vulnerability management is an important control for defending against various threats and minimizing.

Vulnerability management platform needs to identify common software security vulnerabilities of business and consumer threat. Few common vulnerabilities are

1.       OS command injection

2.       SQL injection

3.       Buffer overflow

4.       Missing data encryption

5.       Authentication and authorization

6.       Cross site script and

7.       URL re direction

Some of the reason for security vulnerabilities are as follows

1.       Unpatched system

2.       Security misconfiguration

3.       No proper security management solution in place

4.       Recent work from home challenges

Vulnerability Discovery

Finding real vulnerability in the system and software is a key phase in vulnerability management. There are lot of automated tools are available to scan application, network, and systems to identify weaknesses that could lead to vulnerable. Most of the vulnerability assessment tools can identify

• Unwanted open ports
• Software misconfiguration
• Program error that can lead to vulnerable

Following few widely used discovery methods

·         Host-based vulnerability scanners – This method is used to identify server, desktop and network vulnerabilities.  These tools mostly identify ports, configuration, and patch issue.  For example, we can use tools Nmap, Aircrack to scan and identity issues

 ·         Static application security testing (SAST) – This method is used to secure software by reviewing its source code to identify sources of vulnerabilities. SAST focuses on the code content of the application, scanning the source code and its components to identify potential security vulnerabilities. For example SonarQube,Rips, etc tools are available to find code issues

 ·         Dynamic application security testing (DAST) is a program that communicates with a web application through the web front-end, to identify potential security vulnerabilities and architectural weaknesses in the application. For example we can use Netsparker, OWASP Zed Attack tools

 ·         Interactive application security testing (IAST) analyzes code for security vulnerabilities while an application is run by an automated test, human tester, or any activity that interacts with application functionality. It reports vulnerabilities in real-time. For example we can use Netsparker, OWASP Zed Attack tools

 ·         Penetration testing attempts to exploit any vulnerabilities in a system to find out whether unauthorized access or other malicious activity is possible, and identify which flaws pose an actual threat to the application

Vulnerability Priority

As I said earlier prioritizing and identifying is the main exercise of vulnerability management. Common Vulnerability Scoring System (CVSS), which is a free and open industry standard for assessing the severity of security vulnerabilities. CVSS Scoring system provides a numerical (0-10) representation of the severity of an information security vulnerability.

CVSS consists of three metric groups: Base, Temporal, and Environmental. The Base metrics produce a score ranging from 0 to 10, which can then be modified by scoring the Temporal and Environmental metrics. A CVSS score is also represented as a vector string, a compressed textual representation of the values used to derive the score. Two common uses of CVSS are calculating the severity of vulnerabilities discovered on one's systems and as a factor in prioritization of vulnerability remediation activities. The National Vulnerability Database (NVD) provides CVSS scores for almost all known vulnerabilities.

 

Vulnerability Mitigation

Once vulnerability is identified and prioritized, ideal next step is to remediated before it becomes serious security threat. Remediating a vulnerability means fixing or eliminating it and dealing with the root cause of the vulnerability

Remediation mostly required system patching, software update or library update which cannot be done immediately. Applying system patch sometimes will cause an issue, so need to check test properly before we apply to production environment. Even some of the remediation will take time to develop and fix.

As remediation will take time in most of the cases, organization needs to have proper mitigation plan/strategy. If anyone of the system got infected solution is identify infected file/software path them properly as it will take item.  First switch off that system and taking out that system from network is immediate mitigation steps to avoid large infection.

If application is using vulnerable library, then proper remediation is library update/need to identify alternative library and fix the issue. Immediate mitigation might be build monitor and alert system when that library is used. In that alert is there is any vulnerable call you take an immediate action.

 

Enterprise Architecture framework will provide blueprint for an enterprise to use and create an architecture. It provides principles, practices, tools, and approaches that help enterprise to follow and improve their standard.

Enterprise architecture majorly contains four types

Business Architecture: This will take a lead role to defines the business strategy and governance to develop proper architecture to achieve organizational goal

Data Architecture:  This is core for organizational data strategy. It defines organization infrastructure like how data can be collected, transferred, and stored securely

Application Architecture:  This defines technology, pattern and systems can be used in applications and how each system can interact each other to achieve business goal. It will help us to created logical and physical architecture

Technology Architecture: This will be helpful for organization to define tools and technology which will be helpful to achieve business goals.

EA Frameworks

1. Zachman Framework

This is an enterprise ontology and is a fundamental structure for enterprise architecture which provides a formal and structured way of viewing and defining an enterprise.

This framework typically represented as a 6x6 matrix.

The rows of this framework on describing six perspectives interrogatives of What, how, where, who, when and why. The columns of the framework describes specific viewpoint of a group of stakeholders

More details https://www.zachman.com/

 

2.TOGAF (The Open Group Architectural Framework)

A generic framework for developing architecture to meet different business needs. This is Widley used framework. This framework can be applied large to small enterprise.

This standard enforce business goals as architecture drives and provide set of practices as follows

                ADM (Architecture Development Method)

                ADM Guidelines and Technique

                Architecture Content.

                Enterprise Continuum

                Capability Framework

More details https://pubs.opengroup.org/architecture/togaf9-doc/arch/

To Install phpMyAdmin in EC2 Instance

Following steps will help to install phpMyAdmin in EC2 Amazon linux2 instance. Following commands, should be run in the same order after SSH into EC2 instance

1. yum update -y

2. sudo amazon-linux-extras install -y php8.0

3. sudo yum install -y httpd

4. sudo systemctl start httpd

5. sudo systemctl enable httpd

6. sudo systemctl is-enabled httpd

7. sudo usermod -a -G apache ec2-user

8. sudo chown -R apache:apache /var/www

9. sudo chmod 2775 /var/www && find /var/www -type d -exec sudo chmod 2775 {} \;

10. find /var/www -type f -exec sudo chmod 0664 {} \;

11. sudo yum install php-mbstring php-xml -y

12. sudo systemctl restart httpd

13. sudo systemctl restart php-fpm

14. cd /var/www/html

15. wget https://www.phpmyadmin.net/downloads/phpMyAdmin-latest-all-languages.tar.gz

15. mkdir MdaStageDbAdmin && tar -xvzf phpMyAdmin-latest-all-languages.tar.gz -C MdaStageDbAdmin --strip-components 1

17. mv config.sample.inc.php config.inc.php

18 blowfish_secret generate https://phpsolved.com/phpmyadmin-blowfish-secret-generator/

19. config file

20. $cfg['TempDir'] = '/tmp'; 

Enterprise Architecture

 

What is enterprise architecture?

Enterprise architecture (EA) is a process that help organization to define the business processes and IT structure to meet their needs in current and future goals.

Who is enterprise?

Any organization that shares common set of goals that is government agencies, corporation and large organizations may comprise multiple enterprises.

What is the benefit of enterprise architecture?

·         EA helps organization set and achieve their goals in systematic way

·         Efficiently plan their business strategy and set the target more realistically

·         It will introduce consistence business process and information across business unit

·         It will introduce more efficient IT operation

Why enterprise architecture?

Enterprise architecture will bring all business unit together to operate with single business process with automated way. Identify technical and process gab between business units and implement to operate integrated way to achieve business goals. Enterprise architecture will identify and analyze current technology and utilize those technology to operate business more efficiently   

Is Any process/framework being available to follow?

Yes. There are lot of frameworks are available. Widely used few frameworks are

1.       TOGAF ( The open group architecture framework)

2.       Zachman Framework  

How will frameworks help for enterprise?

Framework will provide a starting point and provide a common terminology. Frameworks are captured and defined based on the real-world example, so it will give a lot of value add instead of inventing new.