Databases for HA and DR

During a recent participation in a bid, responding to an RFP, I had to wear my ops hat and it was a refreshing change, from my dev endeavours in recent past.

One of the things, that I was required to do was to come with a solution that includes HA and DR. When considering the database options for HA and DR, I had to brush up my basics related to database replications, options and also discuss and validate with other senior tech architects who specialize in ops. Following a brief of the learnings that came out of the experience.

To know the basics of replication, the wikidpedia link for replication is very good. But first some basics.

Background: why database synchronization is required?
Scalability in the middle tier (for example from app servers) is easy to achieve through horizontal scaling, all you would need is mutiple app servers, all symmetrical and fronted by a load balancer.But in the database tier achieving scalability is lot tougher. Especially for data centric applications like OLTP, the database is the single most stressed tier. Though scale-up is an option, it does not provide for a good high availability option. Hence we have to think of 2 or more copies of database, to provide scalability as well as high availability.

Multiple databases can be a tricky option. We can have a cluster of databases all symmetric masters, fronted by a load balancer, but synchronizing data across the databases, is a huge overhead and has potential for failures such as deadlocks, during synchronization.

But to think up other options such a single master, multiple read-only slaves, one must appreciate that, not all data access is equal. If in our application, we can visualize data access as being divided into readonly and readwrite, we may find that only small percentage of application's data access is read write, say 30%. In such cases having a single master database which caters to read-write and multiple slave databases which cater to read-only can be resorted to.

In any case, having mutiple copies of the database and trying to synchronize data across those databases is something you will have to inevitably deal with, either for database load balancing, high availability(HA) or on a more remote note for disaster recovery (DR).

So what are the options for database synchronization?
There are 3 broad options for replication:

• Storage based replication
• File based replication
• Database replication: this is usually supported natively by the specific database

Storage based replication
Active (real-time) storage replication is usually implemented by distributing updates of a block device to several physical hard disks. This way, any file system supported by the operating system can be replicated without modification, as the file system code works on a level above the block device driver layer. It is implemented either in hardware (in a disk array controller) or in software (in a device driver).

When storage replication is done across locally connected disks it is known as disk mirroring. A replication is extendable across a computer network, so the disks can be located in physically distant locations. For replication, latency is the key factor because it determines either how far apart the sites can be or the type of replication that can be employed.

Basic options here are synchronous replication, which guarantees no data loss at the expense of reduced performance and aysnchronous replication where-in remote storage is updated asynchronously hence it cannot guarantee zero data loss.


File based replication
File-based replication is replicating files at a logical level rather than replicating at the storage block level. There are many different ways of performing this. Unlike with storage-level replication, the solutions almost exclusively rely on software.

File level replication solution yield a few benefits. Firstly because data is captured at a file level it can make an informed decision on whether to replicate based on the location of the file and the type of file. Hence unlike block-level storage replication where a whole volume needs to be replicated, file replication products have the ability to exclude temporary files or parts of a filesystem that hold no business value. This can substantially reduce the amount of data sent from the source machine as well as decrease the storage burden on the destination machine.

On a negative side, as this is a software only solution, it requires implementation and maintenance on the operating system level, and uses some of machine's processing power (CPU).

File based replication can be done by a kernel driver that intercepts calls to the filesystem functions, filesystem journal replication or batch replication, where-in source and destination file systems are monitored for changes.

Database replication
Database replication can be used on many database management systems, usually with a master/slave relationship between the original and the copies. The master logs the updates, which then ripple through to the slaves. The slave acknoledges the updates stating that it has received the update successfully, thus allowing the master, sending (and potentially re-sending until successfully applied) of subsequent updates.

Also database replication becomes difficult when it scales up to support either larger number of databases or increasing distances and latencies between remote slaves.

Some common aspects to consider while choosing database replication:

• Do you wish to cater to HA only or you want load balancing as well?
• What is latency for replicaton? Are participant servers in replication, local or remote(site)?
• Are you ready to contend with multiple masters or is single master good enough?
• Can you split your database access into read-write and read-only queries?

The actual process of database replication usually involves shipping database transaction logs, from master to slave. This shipping can be done as file based transfer of logs or logs can be streamed from master to slaves. Databases can support "specialized transaction logs" which are solely for purposes of replication and are hence optimized.

If you wish only HA or  redundancy, than you can have slave/s in warm standby mode. In this case, master sends all updates to slave but the slave is not used even for read-only access, it is only an up-to-date standby.For HA, using shared storage for active master and inactive standby can be resorted to. But the shared storage becomes the SPOF(single point of failure). 

If you intend to have single master and mutiple read-only slaves, either your application must be smart enough to balance, database access as readwrite connections and read-only connections. Some databases like postgresql provide third party middle-ware libraries which bifurcate client's database access and send read-write request to master and read-only request balanced across multiple slaves.

If your application has need to load balance even the read-write requests, then you might think of going in for multiple masters. Many databases refer to this as database clustering or mutli-master support. Here, read-write commands are sent out to one of the masters and masters are synchronized through mechanisms like 2 phase commits. Multi master replication has challenges such as reduced performance and increased probability of transactional conflicts and related issues like deadlocks.

In addiiton, databases can support features like ability to load balance read-only queries, by allowing parallel execution of single query across multiple read-only slaves.

Synchronous replication implies, the synchronizations across slaves is guaranteed at the cost of commit performance against the master

For DR, remoteness introduces high latency, hence log shipping or async streaming of logs, to a remote warm standby is acceptable.