.::: Testing Failover Always On SQL Server (MSSQL) and collect Log after Failover :::.

 

A. Failover using GUI / SQL Management Studio

1. Capture always on before Failover

.::: SQL Server: how to simulate and catch deadlock :::.

1. Create database 

create database dead_lock_lab

go 

2. create table & row data 

USE dead_lock_lab;

 

CREATE TABLE dbo.invoices (

   id int NOT NULL,

   num nvarchar(20) NOT NULL,

   customer nvarchar(100) NOT NULL,

   created_at datetime NOT NULL,

   updated_at datetime NOT NULL,

   CONSTRAINT PK_invoices PRIMARY KEY (id)

);

 

.::: How to Sample Simulation Blocking SQL Server & Trouble shoot :::.

 

1. Login to database and create database

create database blocking;

go 

2. Open 1 Session to New Quary Windows

Begin Tran ;

select 'query 1'

CREATE TABLE ##Employees (

    EmpId INT IDENTITY,

    EmpName VARCHAR(16),

    Phone VARCHAR(16)

)

GO

INSERT INTO ##Employees (EmpName, Phone)

VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')

GO

CREATE TABLE ##Suppliers(

    SupplierId INT IDENTITY,

    SupplierName VARCHAR(64),

    Fax VARCHAR(16)

)

GO

INSERT INTO ##Suppliers (SupplierName, Fax)

VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')

GO

 

.::: Install PAF (PostgreSQL Automatic Failover ) on centos7 ( base on pacemaker, pcs, corosys ):::.

 

A. Install PAF 

1. Prepare Node 

Node01 

10.10.10.241

Node02

10.10.10.242

Ip Virtual 

10.10.10.243

2. Install pacemaker, corosys, pacemaker & configuration 

insttal pcsd

https://teguhth.blogspot.com/2018/08/how-to-install-and-configuration-pcs.html

.::: How To Set Up Redis as a Cache for MariaDB / MySQL with PHP :::.

1. Install Redish

https://teguhth.blogspot.com/2021/08/how-to-install-database-redis-on-centos.html

2. Install lamp

[root@teguhth /]# yum -y install php libapache2-mod-php php-pdo php-json php-cli

3. Install MariaDB

https://teguhth.blogspot.com/2017/10/install-mariadb-101-on-centosrhel-using.html

.::: How To Install Database Redis on Centos 7 :::.

1. Enable repository

[root@teguhth data]# yum -y install epel-release yum-utils

[root@teguhth data]# yum -y install http://rpms.remirepo.net/enterprise/remi-release-7.rpm

[root@teguhth data]# yum-config-manager --enable remi

[root@teguhth data]# 

.:: How To Replicate Set (Redundant /Mirroring) MongoDB 5.0 on Centos 7 :::.

A. Configuring the Replica Set

1. Setting up the environment Add /etc/host to nodeA nodeB 

[root@teguhth01 ~]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.10.10.21 teguhth01 NodeA

10.10.10.22 teguhth02 NodeB

[root@teguhth01 ~]# 

[root@teguhth02 ~]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.10.10.21 teguhth01 NodeA

10.10.10.22 teguhth02 NodeB

[root@teguhth02 ~]# 

.::: Install MongoDB 5.x on Centos/Redhat 7 using repository :::.

 

1. Create repo the /etc/yum.repos.d/mongodb-org-5.0.repo file for MongoDB.

[root@teguhth02 ~]# cat /etc/yum.repos.d/mongodb-org-5.0.repo

[mongodb-org-5.0]

name=MongoDB Repository

baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/5.0/x86_64/

gpgcheck=1

enabled=1

gpgkey=https://www.mongodb.org/static/pgp/server-5.0.asc

[root@teguhth02 ~]# 

.::: Change Standby Slave Database to Master/Normal on PostgreSQL ERROR: cannot execute CREATE DATABASE in a read-only transaction :::.

continue from https://teguhth.blogspot.com/2021/08/how-to-setting-streaminng-replication.html

 

 

A. Change Database Standby/Slave to Normal /Master

1. test create database for test write

teguhth=# create database tka;

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

teguhth=# 

2. Promote Standby/Slave

/usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data

[root@teguhth02 ~]# su - postgres

Last login: Sun Aug  1 15:14:46 WIB 2021 on pts/0

-bash-4.2$ /usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data

waiting for server to promote.... done

server promoted

-bash-4.2$

.::: How to Setting Streaming replication Redundant / Mirroring (Master Slave) postgreSQL 13 on Centos 7 :::.

 

A. 

B. Setting di Master Node

1. Create user replication

su - postgres

CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin'

[root@teguhth01 13]# su - postgresq;

su: user postgresq does not exist

[root@teguhth01 13]# su - postgres

Last login: Sun Aug  1 13:38:09 WIB 2021 on pts/0

-bash-4.2$ 

-bash-4.2$ 

-bash-4.2$ psql

psql (13.3)

Type "help" for help.

.::: Install postgreSQL Linux using Repository Centos 7 & PostgreSQL 14 :::.

 

A. Install progress using Repository CentOS 7.

1. Update Server (optional)

[root@teguhth01 ~]# yum -y update

2. Install PostgreSQL

[root@teguhth01 ~]# yum install postgresql-server postgresql-contrib -y

3. Inisialisasi Database

[root@teguhth01 ~]# postgresql-setup initdb

Initializing database ... OK

systemctl start postgresql

.::: Importing Exsiting server or cluster MySQL/MariaDB using Severalnines :::.

 1. Generate an SSH key to be used by ClusterControl when connecting to all managed hosts. In this example, we are using the root user to connect to the managed hosts. To generate an SSH key for the root user

[root@teguhth ~]# ssh-keygen -t rsa

2. Before creating or importing a database server/cluster into ClusterControl, set up passwordless SSH from the ClusterControl host to the database host(s). Use the following command to copy the SSH key to the target hosts

.::: Install & Configuration Cluster control Severalnines for HA MariaDB, MySQL :::.

A. Setup ClusterControl repository 
1. Manually import the Severalnines repository public key into your RPM keyring:
[root@teguhth data]# wget http://repo.severalnines.com/severalnines-repos.asc
[root@teguhth data]# rpm --import severalnines-repos.asc 
[root@teguhth data]#