SQL Server Always On availability group cluster in the AWS Cloud

Microsoft gives HA features like a charm . Lower to higher deployment costs its giving many features as per business requirements . Replication , Mirroring , Log shipping and Always On many features available to build HA Setup in On Premises . 

Like wise , we can setup all the above features in Cloud as well . In that we can see Always on availability group cluster in this blog 

What is Always On Availability Group?

• An availability group supports a replicated environment for a discrete set of user databases, known as availability databases. 
• You can create an availability group for high availability (HA) or for read-scale. An HA availability group is a group of databases that fail over together.  

Environment Setup for Always on Availability Group ,

Launched one Active Directory and two SQL Nodes with below range . Detailed setup for environment steps are below ,

Below are the detailed steps for environment steps ,

Step 1 : Create ag-sql-vpc with 10.0.0.0/16 IPv4 CIDR range

Step 2 : Create two private subnets ( 10.0.3.0/24 , 10.0.1.0/24 ) for SQL Nodes and one public subnet ( 10.0.4.0/24 ) for Active Directory  

Step 3 : Launched the windows instances with two secondary ip's for Failover Cluster and Always on Listener

In this POC Setup , Launched windows instance and installed SQL Server Developer edition . Also we can launch Windows with SQL Server 2016 based on your requirements 

Step 4 : Change the computer properties and rename the instance names accordingly 

Step 5 : Completed the AD Server configuration and its named as ag-sql-AD , After that change DNS server address in network properties in ag-sql-node1 and ag-sql-node2 ( 10.0.4.33 is static IP of AD Server )

Step 6 : Once modified the DNS configuration reboot the server and login with AD administrator account 

Step 7 : Once logged in with AD login , Install the failover clustering and below dependent features in ag-sql-node1 and ag-sql-node2

Configuring Shared Drive for Backup and Restore 

Step 8 : Between the ag-sql-node1 and ag-sql-node2 needs to take backup and log backups for Always on background process

Step 9 : Create folder in ag-sql-node2 and share with everyone in AD account

  

Step 10 : Take one time backup of DW_Mart and DataLake in that shared folder . Created Shared drive will be used while always on group creation 

Failover Cluster Configuration 

Step 11 : Open the Failover Cluster Manager console and Create the cluster . Browse and add the both servers 

Step 12 : Once all the steps finished , create the cluster wizard 

Step 13 : Click agsqlprod failover cluster and modify the cluster core resources . In this we need to add secondary IP for both nodes ( 10.0.1.11 and 10.0.3.11 )

Once we added both secondary IP's one of the IP will be come to online 

If we have not added secondary IP , it will show as an error like below 

Configuring SQL Server Services  

Step 14 : Once all the steps are completed on Failover cluster manager , modify the SQL Service Account to AD service account 

Step 15 : Next right click the SQL Server Service in configuration manager and enable the Always on High Availability on ag-sql-node1 and ag-sql-node2 SQL instances 

Create and Configuring the Availability Group 

Step 16 : Right click the always on group wizard and create the availability group as agsqldb

Step 17 : Based on the requirements add the number of replicas ,

Step 18 : Below are the endpoints and make sure allowed below ports between the cluster nodes 

Step 19 : Then create availability group listener with remaining secondary IP ( 10.0.1.12 and 10.0.3.12 )

Step 20 : Once everything is completed click Next to create availability group 

Once its created we can able to see the Cluster Manager Role in the Failover cluster manager console ,

Ready to sync the Data from Primary to Secondary 

After all availability group is healthy and primary and secondary nodes are synchronized 

Thanks for Reading !!! Any corrections or any doubt please contact me directly !!! 

Redis to Elasticache Migration in AWS

Comparing Redis , AWS Elasticache giving multiple options to cache the data in cloud . Its enhanced with two ways of Access control options in PaaS Service 

Below are the few mission handling features we loved in elastic cache ,

• Detecting data failures and recovery the nodes automatically 
• Multi AZ features with automatic failover to replication nodes 
• Redis Cluster mode enabled supports data partitioning up to 500 shards in different zones
• Redis managed backups , automatic failure detection and log files transfer to Kinesis Firehouse and cloud watch 
• Easy online migration from any Redis source 
• Encryption at rest and Encryption in transit are supported 
• Data volume up to peta bytes with micro second response time  

Let start migrating data from Redis to Elasticache ,

Preparing our source and target Redis nodes for migration 

1. Launched two EC2 machines and installed redis 5.x version 
2. Configured replication between master and replica

        To avoid downtime , we are doing online migration from replication server . so we have modified below values in replica server . Its based on your source modify the below values

      3.Configured replication between master and replica

• protected-mode no
• bind datablogs.xcnphd.ng.0001.use1.cache.amazonaws.com 6379
• requirepass should be disabled

       Note : Transferring source master or replica should not have AUTH configured and protected mode disabled 

      4.Launch Elastic cache instance with below steps 

Cluster Mode Disabled ( Redis Engine 5.0.5 or Higher ) 

Multi-AZ Should be Enabled 

Encrypt at rest and in-transit should be disabled

If we have not followed above all steps , we will get below popup while we start the migration 

 

Starting Migration 

In Elastic console Click --> Action --> Migrate data from endpoint 

Once clicked , we can type the Source Redis Endpoint ( Private IP ) and Port ( Customized Port ) in below console ,

Then click Start Migration and follow the logs from Events in same console , 

If we get any error , It will be printed in the events log

Once its started , Restart the Master of Elasticache node one time , In our case we need to restart the replica server because its our source

Once restarted verify the info replication in Elasticache nodes 

Once started successfully , we can't perform any other operation in Elasticache nodes ,

Verifying the Data Migration Progress 

We have inserted sample record in the EC2 Master Node and verified the same in the replica and as well as in Elasticache 

Data looks good , once all the data transferred from IaaS to PaaS . Complete the migration in the Elasticache console and point the application endpoints to Elasticache node 

Authors : 👷 Krishna and 👷 Selvackp

Thanks for Reading !!!

Migrate Azure MySQL Database to Amazon RDS for MySQL Using MySQL Replication

Its Nightmare Story struggled a lot to reduce the cut over time with Azure MySQL to AWS Migration MySQL Migration.

Tested Methods, 

		
Backup and Restore - Its took 6 hours to complete
		
AWS DMS - Text Data truncated and timestamp column data was 6 hours behind from the source   	

But Nothing helped due to Bugs, features unavailable etc.., But by default replication helped us to work. 

Two types replication available in Azure MySQL ,

1.GTID based Replication

    This is we already discussed with multiple topics in our blog for GCP cloud, also we will explain how to configure GTID based replication from Azure MySQL to AWS RDS in next blog

MySQL GTID Replication and lower_case_table_names

Multi Source Replication from GCP Cloud MySQL to Percona XtraDB Cluster

2.Binlog position-based replication

    We are performing binlog position-based replication to cut over the application in this blog

Environment Setup 

Below POC environment setup done for testing 

• Azure MySQL 5.6.4 General Purpose instance launched with minimal configuration
• AWS RDS for MySQL 5.7 launched with customized parameter group 
• Network connectivity between Azure PaaS and AWS PaaS Service

Once environment is up and running, modify the below server parameters in both side 

Below values needs to update in Both environments, 

Azure MySQL: 

• replicate_wild_ignore_table=mysql.%,information_schema.%,performance_schema.%,sys.%
• binlog_row_image = FULL
• binlog_expire_logs_seconds = 1440

AWS RDS MySQL:  

• time_zone = Same as Azure MySQL
• binlog_format = ROW
• collation and character_set parameters = Same as Azure MySQL

Now both server is ready to configure the replication

Pre-Steps to configure the replication

1.Let’s verify and take a note of binlog position in Azure MySQL   

 mysql> show master status.

+------------------+----------+--------------------+------------------+-------------------+

| File                        | Position    | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+----------------------+-----------------+--------------+------------------+-------------------+

| mysql-bin.00865 | 52884741       |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

2.Create a replication user with necessary privileges

CREATE ‘repluser’@’%’ IDENTIFIED BY ‘datablogs123’.

GRANT REPLICATION SLAVE ON *.* TO ‘repluser’@’%’;

GRANT REPLICATION CLIENT ON *.* TO ‘repluser’@’%’;

GRANT SELECT, CONNECT ON *.* TO ‘repluser’@’%’;

FLUSH PRIVILEGES;

3.Using mysqldump start the schema backup and followed by data backup 

mysqldump -h datablogs.mysql.database.azure.com -u repluser@datablogs -p datablogsprod --no-data --routines --triggers --events > datablogsprodmeta.sql

mysqldump -h datablogs.mysql.database.azure.com -u repluser@datablogs -p datablogsprod --routines --triggers --events --skip-add-drop-table  --max_allowed_packet=1024M --single-transaction  > datablogsproddata.sql

4.Once backup is completed restore the data into MySQL RDS , 

mysql -h datablogs.c5exr7bs8bax.ap-southeast-1.rds.amazonaws.com -u repluser -p datablogsprod  < datablogsprodmeta.sql

mysql -h datablogs.c5exr7bs8bax.ap-southeast-1.rds.amazonaws.com -u repluser -p datablogsprod  --max_allowed_packet=1024M  < datablogsproddata.sql

Configuring the replication 

In Amazon MySQL set the external master with the help of Step 1 binlog file name and binlog position 

CALL mysql.rds_stop_replication;
CALL mysql.rds_reset_external_master;
CALL mysql.rds_set_external_master ('datablogs.mysql.database.azure.com', 3306, 'repluser@datablogs', 'repluser', 'mysql-bin.00865', 52884741, 0);
CALL mysql.rds_start_replication;

Once executed start the replication and check the slave status in RDS ,

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: datablogs.mysql.database.azure.com
                  Master_User: repluser@datablogs
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.00865
          Read_Master_Log_Pos: 52884741
               Relay_Log_File: relaylog.000004
                Relay_Log_Pos: 273
        Relay_Master_Log_File: mysql-bin.00865
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: datablogsprod
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,mysql.rds_monitor,mysql.rds_sysinfo,innodb_memcache.cache_policies,mysql.rds_history,innodb_memcache.config_options,mysql.rds_configuration,mysql.rds_replication_status
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 52884741
              Relay_Log_Space: 19742
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1053643462
                  Master_UUID: 787858b4-a021-11ea-b894-97f71069e028
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

Post Validation and troubleshooting steps of Azure MySQL Replication

Validate the data and tables counts between Azure MySQL and MySQL RDS 

If we get any errors related with system schema in the Azure use the following method to fix it , we got same error like below ,

Can't find 'mysql.__firewallrules__' doesnt exist 

We can skip the error with below commands in MySQL RDS , 

CALL mysql.rds_stop_replication;
CALL mysql.rds_skip_repl_error;
CALL mysql.rds_start_replication;

But in MySQL RDS Row by row only errors will be skip and it will take long time to do it , follow the below method and check the binlog position until for the DML , DDL queries are captured in binlog 

SHOW BINLOG EVENTS IN 'mysql-bin.00865' FROM 52884567 LIMIT 10 \G

Note : Based on the error modify the binlog file and position in above command

Once found the binlog position repeat the configuring replication step

Now All set for Azure to AWS data sync , now its ready for application cutover with zero downtime !!!

 

Export/Import Data on Amazon Oracle RDS using Data Pump utility

Its easy to refresh the schemas in Oracle RDS , We can do one time for Testing or Development Purpose 

But if its needs to run on daily basis , here is the automated code for achieve the solution  

GitHub Link for Source Code : https://github.com/selvackp/Oracle_RDS_Import_and_Export

Upgrade Oracle Apex 5.1.4 to Oracle Apex 21.1.3 in AWS RDS

Its quick story and easy steps too to upgrade Oracle Apex in AWS RDS . But dependent ORDS (Oracle Rest Data Service) in-place upgrade little difficult task , we will solve that in next blog 

Environment Setup ,

1.Oracle RDS launched with 12.1.0 Engine version

C:\Users\datablogs>aws rds describe-db-instances
{
 “DBInstances”: [
 {
 “DBInstanceIdentifier”: “datablogs-db”,
 “DBInstanceClass”: “db.t3.medium”,
 “Engine”: “oracle-se2”,
 “DBInstanceStatus”: “available”,
 “MasterUsername”: “dataadmin”,
 “DBName”: “ORCL”,
 “Endpoint”: {
 “Address”: “datablogs-db.cv33pqc8qgq1.us-east-1.rds.amazonaws.com”,
 “Port”: 1521,
 “HostedZoneId”: “Z2R2ITUGPM61AM”
 },
.......
.......
"EngineVersion": "12.1.0.2.v25"

2.Create an option group and add APEX option value as 5.1.4 . Attached the option group into oracle 12c RDS 

C:\Users\datablogs>aws rds describe-option-groups --option-group-name oraapex
{
 “OptionGroupsList”: [
 {
 “OptionGroupName”: “oraapex”,
 “OptionGroupDescription”: “oraapex”,
 “EngineName”: “oracle-se2”,
 “MajorEngineVersion”: “12.1”,
 “Options”: [
{
 “OptionName”: “APEX”,
 “OptionDescription”: “Oracle Application Express Runtime Environment”,
 “Persistent”: false,
 “Permanent”: false,
 “OptionVersion”: “5.1.4.v1”,
 “OptionSettings”: [],
 “DBSecurityGroupMemberships”: [],
 “VpcSecurityGroupMemberships”: []
 }
 .......
}

By default , depends on the Apex version oracle RDS will be create the databases name as like version name ( Exp., Oracle Apex 5.1.4 - APEX_050100 . Oracle Apex 21.1.3 - APEX_210100 )

3.Once we launched the Oracle RDS , we can check Apex version using below command ,

Select comp_name,status,version from dba_registry order by comp_name;

Note : We can keep Oracle Apex schemas in the default user ( APEX_050100 ) else we can create separate schema and maintain it . But even if you upgrade major version database schema wont affect in oracle Apex .

4.Assume we are having workspaces and application connected with the current version

In current version 442 tables available , we are going to upgrade to Oracle 19c

5.Repeat Step 2 , Create an option group and add APEX option value as 21.1.3 . Attached the option group into oracle 19c RDS and begin the upgrade in console

5.1.Modify the Oracle instance in AWS RDS Console

5.2.Modify the additional options with new option group

6.Upgraded the Oracle RDS to 19c

C:\Users\datablogs>aws rds describe-db-instances
{
 “DBInstances”: [
 {
 “DBInstanceIdentifier”: “datablogs-db”,
 “DBInstanceClass”: “db.t3.medium”,
 “Engine”: “oracle-se2”,
 “DBInstanceStatus”: “available”,
 “MasterUsername”: “dataadmin”,
 “DBName”: “ORCL”,
 “Endpoint”: {
 “Address”: “datablogs-db.cv33pqc8qgq1.us-east-1.rds.amazonaws.com”,
 “Port”: 1521,
 “HostedZoneId”: “Z2R2ITUGPM61AM”
 },
.......
.......
"EngineVersion": "19.0.0.0.ru-2021-10.rur-2021-10.r1"

C:\Users\datablogs>aws rds describe-option-groups --option-group-name ora19apex
{
 “OptionGroupsList”: [
 {
 “OptionGroupName”: “oraapex”,
 “OptionGroupDescription”: “oraapex”,
 “EngineName”: “oracle-se2”,
 “MajorEngineVersion”: “12.1”,
 “Options”: [
{
 “OptionName”: “APEX”,
 “OptionDescription”: “Oracle Application Express Runtime Environment”,
 “Persistent”: false,
 “Permanent”: false,
 “OptionVersion”: “21.1.v1”,
 “OptionSettings”: [],
 “DBSecurityGroupMemberships”: [],
 “VpcSecurityGroupMemberships”: []
 }
 .......
}

7.Repeat Step 3 , we have upgraded to Oracle Apex 21.1.3

As we mentioned in previous note in Step 3 , new default schema created for latest version and existing schema tables didn't have any impact

Its easy task in AWS RDS to upgrade Oracle Apex , But in the next blog we will in-place upgrade of ORDS in detail

Thanks for Reading !!!

Capture Basic MySQLRemoteServerDetails

Basic Requirement to get the below details from MySQL Remote Host Using Python

1. OS Version
2. Find Physical host / VMWare
3. MySQL Port
4. Total RAM / Total CPU and Cores
5. How many Database and Size ?
6. Disk space

https://github.com/selvackp/MySQLRemoteServerDetails.git

# Required libraries for program
import paramiko
from mysql.connector import connect, Error

# Required libraries to connect
client = paramiko.SSHClient()
client.set_missing_host_key_policy(paramiko.AutoAddPolicy())

# Remote server details to enter
print('Enter the RemoteHost Login Details')
print('----------------------------------')
print('\t')
re_host_IP = input("RemoteHost IP: ")  # If any IP works , pass that to RemoteHost IP
re_host_user = input("RemoteHost Username: ")
re_host_password = input("RemoteHost Password: ")  # If no password for AWS machine just enter to fill the next details
re_host_key_file = input(
    "RemoteHost keyfile name: ")  # If no keys just enter to move next step . We have tested AWS machine , so its required to pass pem key file to connect .
print('\t')

try:
    # Remote Host Server Connectivity
    client.connect(re_host_IP, username=re_host_user, password=re_host_password, key_filename=re_host_key_file)
    stdin, stdout, stderr = client.exec_command('cat /proc/version')
    print('Remote Server OS and Version')
    print('----------------------------')
    print(stdout.readlines()[0])
    print('\t')
    print('Remote Server Total CPU and Cores')
    print('---------------------------------')
    stdin, stdout, stderr = client.exec_command('lscpu')
    print('Total CPU/Core count for Remote server: ', stdout.readlines()[3])
    print('\t')
    print('Connected Server Total RAM in GB')
    print('--------------------------------')
    stdin, stdout, stderr = client.exec_command('vmstat -s')
    print('Total RAM for Remote server: ', stdout.readlines()[0])
    print('\t')
    print('Connected Server Total Disk Spaces')
    print('----------------------------------')
    stdin, stdout, stderr = client.exec_command('df -h --total')
    print('Total Disk for Remote server: ', stdout.read().decode())
    print('\t')

    # MySQL Basic Details code below
    with connect(
            host=re_host_IP,
            user=input("Enter Remote MySQL Username: "),
            password=input("Enter Remote MySQL Password: "),
    ) as connection:
        print('\t')
        get_database_host = "select @@hostname"
        with connection.cursor() as cursor:
            cursor.execute(get_database_host)
            database_host = cursor.fetchone()
            print("Successfully connected Remote MySQL Server :", database_host[0])
            print('\t')
        print(database_host[0], 'Database Name and Size in MB')
        print('----------------------------------------------')
        get_database_size = "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;"
        with connection.cursor() as cursor:
            cursor.execute(get_database_size)
            database_size = cursor.fetchall()
            for row in database_size:
                dbname, dbsize = row
                print('{} - {}'.format(dbname, dbsize))
        select_port = 'SELECT @@port'
        with connection.cursor() as cursor:
            cursor.execute(select_port)
            database_port = cursor.fetchone()
            print('\t')
            print('Database Port :')
            print('---------------')
            print(database_host[0], 'Server MySQL Port is :', database_port[0])
        client.close()

except Error as e:
    print(e)
    exit()