Database Server Clustering

All you need to know about Database Server Clustering

In simple terms, Database server clustering refers to a group of the free server that operates as a single system. Many people use database server clustering to improve I/O of the database services which, in turn, increases its availability.
There are many approaches to achieving database server clustering, however, the most popular of them are MySQL and MSSQL. This post discusses both of these approaches.

MySQL Approach for Database Server Clustering

The database service of MySQL is converted into high available services with the help of Distributed Replicated Block Device (DRBD). This software can only be used in Linux and successfully mirrors data of a service that you want to perform from the active node to the highly available cluster of the standby node. DRBD performs on top of block devices such as LVM’s logical volumes and hard disk partitions. Mirroring by using MySQL with DRBD can be carried out in protocol C which is also known as synchronous mirroring. This becomes particularly useful as the active node of the file system is only notified with a message that the writing of the block was successful or finished only when the block is written to both the disks of the cluster. You should use this to avoid the full crash of the active node due to the loss of a single transaction.
Another option is asynchronous mirroring. In asynchronous mirroring, the active node of the file system is notified with the message that the writing of the block was successful immediately after the data has been written to the local disk. Asynchronous mirroring should be used to build mirrors over long distances.
So how exactly does MySQL DRBD work? In this system, both the server nodes of MySQL are inter-connected by a DRBD network link. The network interface thus created is used to synchronize the data block that exists between the servers. In this way, it successfully avoids the private network congestion.
It is important to understand here that, when you are mirroring the data at the block device level, you will be able to access the data only on the active node. When using DRB for MySQL you should only expect high availability without any performance enhancement.

A few terms which you should know when using MySQL.

1. MySQLreplication:With the help of MySQL database replication features, the users can create a cluster that consists of two or more MySQL servers.
2. MySQL master/master replication:This is a cluster that is composed of two MySQL servers both working in active/active mode. The first server can be used to write operations while the second server can operate as a backup option.
3. MySQL master/slave replication:This cluster also consists of two servers working in an active/active mode. The master server is exclusively used for performing the write operations. The data which is present in the slave server can later be synchronized with the master server. Note that, if the master server fails, the web application will not be working.

MSSQL Approach for Database Server Clustering

MSSQL is an RDBMS (Relational Database Management System) which has been developed by Microsoft and consists of the following elements:

• Two servers in an active/passive configuration
• Minimum one domain controller
• Data storage solution

The data storage solution is linked to the database servers with the help of multipath connections which ultimately provide the users with redundant connectivity links.
You can expect high availability to your web solution while using MSSQL. If the active server stops working due to a problem, the passive server of the network will take the entire relay. The performance of the MSSQL database cluster depends on the database server CPU, SAN disks, and RAID configuration, SANA data caching mechanism, and the communication link that exists between the SAN & the servers.

A few terms which you should know when using MSSQL:

1. MSSQL replication:Both the MSSQL database replication feature and the redundant load balancing solutions create this cluster which consists of two or more MSSQL.
2. MSSQL master/master replication:A cluster that is set up with MSSQL master/master replication is composed of two MSSQL servers that exist in an active/active mode. Two separate IP addresses which should be defined by the load balancing appliance in the cluster are the following:

• Write operation IP
• Read operation IP

Remember to use only one MSSQL server to write operations for avoiding data corruption. Both the servers of the MSSQL master/master replication can equally manage the working of the web service.

3. MSSQL master/slave replication:The cluster of MSSQL master/slave replication is also composed of two MSSQL servers that are present in active/active mode. Write operation IP and read operation IP are the IP addresses that should be defined by the load balancing appliances. In this cluster, the data present in the slave server is synchronized with the master server with the help of a transactional publication mechanism. The write operations in this cluster are properly managed only by the master server. Note that a failure in the master server will result in working failure of the web application.

Why Is Database Cluster Serving Useful?

Database Cluster Server can make your life easier in a few key ways. Listed below are the few advantages of using Database Cluster serving:

• It helps you to recover faster from hardware failure.
• It acts as a troubleshooting tool kit.
• It is transparent to all the calling application.

Note that you can use a powerful server to increase the productivity by using clustering. A simple cluster will only increase the availability without any effect on the productivity. Also, this will not avoid the maintenance of the system; the maintenance work has to be done in the same way you as were doing it before.
In short, database cluster serving is quite helpful, however, there will be few things still out there which have to be done manually.