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 !!!

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

Monitoring Data Growth With Query Exporter + Prometheus + Grafana

In this experimental scenario , gathering the table growth statistics using query exporter and loading the data into time series Prometheus database . Grafana dashboards shows the growth in minutes basis

Table growth statistics gathered from localhost mysql instance for testing purpose

Frequently receiving questions like , how to we monitor the data growth of each critical tables. We can have some statistics about data growth and add convenient space for future

Launched three separate docker containers ( Query Exporter , Prometheus and PMM Server ) for each components . Added additional Dashboards in PMM Server for testing purpose

Docker Containers

Prometheus and Query Exporter Configuration files mapped with localhost volume like below ,

docker run -p 9560:9560/tcp -v "$PWD/config.yaml:/config.yaml" --rm -it adonato/query-exporter:latest -- /config.yaml

docker run -p 9090:9090 -v /tmp/prometheus.yml:/etc/prometheus/prometheus.yml prom/prometheus

config.yaml ( Query Exporter configuration file ) :

databases:
  datablogs:
    dsn: mysql://queryexporter:test123@172.31.90.32:3306/test
    keep-connected: true

metrics:
  metric1:
    type: gauge
   # type: counter
    description: A sample gauge

queries:
  query1:
    interval: 3s
    databases: [datablogs]
    metrics: [metric1]
    sql: SELECT TABLE_ROWS as metric1 FROM information_schema.tables WHERE table_schema = 'test' and table_name='actor';

prometheus ( Prometheus configuration file ) :

global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'queryexporter'
    scrape_interval: 5s
    static_configs:
      - targets: ['172.17.0.3:9560']

Both Query Exporter and Prometheus docker container started and we have to allow appropriate ports to access the web console through browser

AWS Console Inbound Rules

Lets verify the Query Exporter and Prometheus running status

Query exporter metrics

Prometheus targets status

Query Exporter is gathering the statistics and loading the data into Prometheus database . Then

Add the Prometheus data source into Grafana

Using the collected metrics display the dashboards on every minutes

Monitoring Dashboards

References :

Query Exporter : https://github.com/albertodonato/query-exporter

PMM Server : https://www.percona.com/doc/percona-monitoring-and-management/2.x/install/docker-setting-up.html

Prometheus : https://www.digitalocean.com/community/tutorials/how-to-install-prometheus-using-docker-on-ubuntu-14-04

MySQL GTID Replication and lower_case_table_names

Error 'Table 'EMPLOYEES.POSITION' doesn't exist' on query. Default database: 'employees'. Query: 'ALTER TABLE EMPLOYEES.POSITION ADD COLUMN phone VARCHAR(15)'

Interesting , table exist on slave server . But we are getting above error frequently and unable to broken the replication because database size is too big

We are having environment as GTID replication setup with windows server (Master) to Ubuntu Linux machine (Slave) . When we dig into all the findings , concluded its may be with case sensitivity issue . Lower_case_table_names variable value is same on both servers . But as per MySQL documents 

Database and table names are not case-sensitive in Windows , but are case-sensitive in most varieties of Unix.Column,index,stored routine, and event names are not case-sensitive on any platform,nor are column aliases

So , decided to tackle the current situation without data loss in slave server . Changing the lower_case_table_names is not best practice and it will corrupt case_sensitive names data files . 

we have stopped the slave , executed all the DML and DDL statements in slave server to match the schema deployments with master server .

And followed GTID replication error skipping process to skip the transactions manually handled in slave servers 

Capture the error GTID with master binlog files

root@master:/var/log/mysql# mysqlbinlog --start-position=904 mysql-bin.000004

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#200102 11:35:24 server id 1 end_log_pos 123 CRC32 0x14c9c2f7 Start: binlog v 4, server v 5.7.28-0ubuntu0.16.04.2-log created 200102 11:35:24

# Warning: this binlog is either in use or was not closed properly.

BINLOG '

fNUNXg8BAAAAdwAAAHsAAAABAAQANS43LjI4LTB1YnVudHUwLjE2LjA0LjItbG9nAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA

AffCyRQ=

'/*!*/;

# at 904

#200102 12:06:51 server id 1 end_log_pos 969 CRC32 0x3564eba7 GTID last_committed=4 sequence_number=5 rbr_only=yes

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;

SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78'/*!*/;

# at 969

#200102 12:06:51 server id 1 end_log_pos 1042 CRC32 0xb527b5d3 Query thread_id=328 exec_time=0 error_code=0

Skipped the binlog position 904 transactions in slave server

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> SET @@SESSION.GTID_NEXT= '69b98063-2d31-11ea-9586-12b321a8670d:78';

Query OK, 0 rows affected (0.00 sec)

mysql> begin ; commit;

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> SET gtid_next=automatic;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

Finally , GTID Replication begins to sync pending SQL threads and upto date the data with Master server !!!

Docker with Percona Backup for MongoDB

Sounds interesting from Percona Backup tool for MongoDB !!! I just wants to try and explore the tool with docker on today !!! Docker is first time for me , but in few days docker become favorite one to use for all kind of HA scenario works

Lets move into today's practices and issues ,

Note : Percona Backup for MongoDB supports Percona Server for MongoDB or MongoDB Community Server version 3.6 or higher with MongoDB replication enabled

Step 1 : Launched the Ubuntu 16.04 machine from AWS , then updated the latest packages and installed the docker 

sudo apt-get update 

sudo apt install docker.io

sudo systemctl start docker

sudo systemctl enable docker  

Once docker installed with latest package verify the docker version using docker --version 

Step 2 : Installed two docker mongo containers with replica set enabled 

docker run --detach --name datablogs-mongo-primary --volume /var/lib/mongo:/data/db --volume /etc/mongodb.conf:/etc/mongo.conf --publish 44444:27017 mongo --replSet datablogs-repl-set

docker run --detach --name datablogs-mongo-secondary --volume /var/lib/mongo-slave:/data/db --volume /etc/mongodb-slave.conf:/etc/mongo.conf --publish 55555:27017 mongo --replSet datablogs-repl-set

We need to access the mongoDB instances outside the world , so I have publised the mongo db ports with different one 

                                                             --publish 44444:27017

                                                             --publish 55555:27017

To access the mongoDB , we need to check the IP Address of both containers using below command

docker inspect datablogs-mongo-primary | grep IPAddress

docker inspect datablogs-mongo-secondary | grep IPAddress

-- usage of extensions refer with docker help

Step 3 : Configure mongo replica 

We need to login with docker command line and configure and start the replication between mongo servers . login into primary mongo container and execute below commands in mongo shell ,

docker exec -it datablogs-mongo-primary /bin/bash

config = {"_id" : "datablogs-repl-set","members" : [{"_id" : 0,"host" : "172.17.0.2:27017"},{"_id" : 1,"host" : "172.17.0.3:27017"}]};

rs.initiate(config);

Once we initiated the replication primary mongo shell will be changed 

Step 4 : Install Percona Backup and Configure 

Before proceeding this activity , we need to update and upgrade the packages using apt-get on each mongo containers

Installed the percona backup for mongoDB with below reference URL , we need to follow percona site for proper installation 

https://www.percona.com/doc/percona-repo-config/percona-release.html

https://www.percona.com/blog/2019/12/17/percona-backup-for-mongodb-in-action/

Once installed the pbm tool , login each mongo containers  set storage path and start the pbm agent . I have used local storage path for mongo backup

storage.yaml : 

type: filesystem
filesystem:
  path: /tmp

pbm store set --config=storage.yaml --mongodb-uri="mongodb://127.0.0.1:55555/"

pbm-agent --mongodb-uri mongodb://172.17.0.2:27017 &

Step 5 : Backup and restore the collections using pbm 

Once completed the setup running the backup in secondary mongo server 

pbm backup --mongodb-uri mongodb://127.0.0.1:27017

Dropped the datablogs db and restored the backup using pbm

Finally verfied the db and collections in primary server 

Am Really happy tested percona backup for mongoDB with Docker today !!! Keep learning !!!

Repair GTID Based Slave on Percona Cluster

Problem : 

We are running 5 node percona cluster on Ubuntu 16.04, and its configured with master-slave replication. Suddenly we got an alert for replica broken from slave server, which was earlier configured with normal replication 

We have tried to sync the data and configure the replication, unable to fix that immediately due to huge transactions and GTID enabled servers. So we have decided to follow with innobackupex tool, and problem fixed in 2 hours 

Followed all the steps from percona doc and shared the experience in my environment 

Steps involving to repair the broken Replication :

1.Backup master server 

2.Prepare the backup 

3.Restore and Configure the Replication

4Check Replication Status

1.Backup master server 

We need to configure the complete master server database into a slave. So we are taking a full backup from the master server, before proceeding the backup we should check disk space available for the backup because of its system-level backup 

We have created a specific user for taking a backup from master server, once the backup is completed will get OK like below,

2. Prepare the backup for Restore

We need to prepare the backups to apply the transaction logs into data files, once it is OK, data files are ready to restore 

Before moving the prepared files into slave server, verify the GTID information from xtrabackup_binlog_info 

3.Restore and Configure the Replication

We can restore the backup else to create a new data directory and move the files into the directory. Stop mysql service before start restoring

innobackupex --copy-dir /mnt/2019-11-26_09-35-31

Default it will restore the data related files in server datadir , using the server my.cnf configuration file .Once changed the data directory, we need to change the owner and permission of the MySQL data directory 

chown mysql:mysql /mnt/mysqldatanew

And restart the service with a new data directory, once its started login with master MySQL root user password. Because we have taken file backup from the master so metadata will be the same of master

Execute below commands to configure the replication 

4. Check Replication Status

Once slave is configured, verify the replication status as below 

Also, Slave has retrieved a new transaction 

Thanks for Reading !!!