Introduction
Databases are essential in system, we have to choose the type and setup for the database as per our requirement. Majorly databases are characterised in two categories SQL (Structured Query Language) and NoSQL (Not Only SQL). We will going to understand the difference between these two and options to make database highly available and scalable.
Failover Strategies
Failover strategies can be applied as per the requirement to overcome data loss and interruptions.
Cold Standby
Take periodic backup of database and use the backup to restore database in case of failure. This may lead to significant data loss and downtime depending on the frequency of back up and time required to restore the database.
Warm Standby
Do replication of database and switch to replica in case actual databse fails. In this strategy delta between last replication and current databse state will be lost if database fails. Less downtime compared to cold standby.
Hot Standby
Application server (or client of database) makes changes in both, actual and stand by instance, so that in case actual database server fails, server can simply switch to the stand by instace quickly. No data loss but minor downtime during the database switching.
Scaling Strategies
Master-Slave Replication
In this setup Master databse can allow read and write, however slave can only allow read. Master replicates to data to slave on write. The idea is to keep slave as upto date as possible with master. Also, majority of the systems are read heavy, in that case such reads can be done through slave and overall load from master can be reduced.
Master-Master Replication
Both database instance are master, client can read and write in any of the instances and ideally the load is distributed. Replication takes places between masters which is bit tricky compared to master-slave as both instances is allowing write operation. However, in case of one instance failure other can quickly take place.
Federation
Database instances are created based on the functionality. Customer related data is stored in separate customer schema, product realted data is in product schema so and so forth. This allows individual databases to scale individually as per the need. If you have complex queries and join between such entities are frequent this setup may make it more complex.
Sharding
Data is distributed in nodes and each node maintains subset of data partitioned based on some criteria. For example, data of user name starting with A-N is in one node and M-Z is in another. This set up kind of struggle with "Celebrity Problem" where one of the node gets more traffic and other one sit ideal.
SQL vs. NoSQL
SQL and NoSQL databases has their own places, based on the need of the system we can choose either of them. SQL databases are relation databases, NoSQL databases can be of key-value store, document store, wide column store or graph database. Depending on the requirement we can use SQL and NoSQL or even combination of both if needed. Following are fundamental differences between these two which we should be aware.
| SQL | NoSQL | |
|---|---|---|
| Structured Query Language | Not Only SQL | |
Relationa Database | Non-relational Database | |
Schema is Predefined and Strict | Schema is dynamic and flexible | |
Vertically scalable | Horizontally scalable | |
MySQL, Postgres, Oracle etc. | Redis,MongoDB,Cassandra etc. | |
Better for structured data where complex queries with join and aggregation is required. | Better for large scale unstructured or semi-structured data | |
| ACID Compliance | BASE (Basically Available, Soft state, Eventual consistency) |
.png){kind=small}
.png){kind=small}
