Database cluster and load balancing

Question

What is database clustering  If you allow the same database to be on 2 different servers how do they keep the data between synchronized  And how does this differ from load balancing from a database server perspective

User · Answer

From SQL Server point of view   Clustering will give you an active - passive configuration   Meaning in a 2 node cluster  one of them will be the active  serving  and the other one will be passive  waiting to take over when the active node fails    It s a high availability from hardware point of view   You can have an active-active cluster  but it will require multiple instances of SQL Server running on each node    i e  Instance 1 on Node A failing over to Instance 2 on Node B  and instance 1 on Node B failing over to instance 2 on Node A    Load balancing  at least from SQL Server point of view  does not exists  at least in the same sense of web server load balancing    You can t balance load that way   However  you can split your application to run on some database on server 1 and also run on some database on server 2  etc   This is the primary mean of  load balancing  in SQL world

User · Answer

Database clustering is a bit of an ambiguous term  some vendors consider a cluster having two or more servers share the same storage  some others call a cluster a set of replicated servers   Replication defines the method by which a set of servers remain synchronized without having to share the storage being able to be geographically disperse  there are two main ways of going about it    master-master  or multi-master  replication  Any server can update the database  It is usually taken care of by a different module within the database  or a whole different software running on top of them in some cases     Downside is that it is very hard to do well  and some systems lose ACID properties when in this mode of replication    Upside is that it is flexible and you can support the failure of any server while still having the database updated  master-slave replication  There is only a single copy of authoritative data  which is the pushed to the slave servers    Downside is that it is less fault tolerant  if the master dies  there are no further changes in the slaves   Upside is that it is easier to do than multi-master and it usually preserve ACID properties    Load balancing is a different concept  it consists distributing the queries sent to those servers so the load is as evenly distributed as possible  It is usually done at the application layer  or with a connection pool   The only direct relation between replication and load balancing is that you need some replication to be able to load balance  else you d have a single server

User · Answer

Database Clustering is actually a mode of synchronous replication between two or possibly more nodes with an added functionality of fault tolerance added to your system  and that too in a shared nothing architecture  By shared nothing it means that the individual nodes actually don t share any physical resources like disk or memory   As far as keeping the data synchronized is concerned  there is a management server to which all the data nodes are connected along with the SQL node to achieve this talking specifically about MySQL    Now about the differences  load balancing is just one result that could be achieved through clustering  the others include high availability  scalability and fault tolerance

User · Answer

Clustering uses shared storage of some kind  a drive cage or a SAN  for example   and puts two database front-ends on it  The front end servers share an IP address and cluster network name that clients use to connect  and they decide between themselves who is currently in charge of serving client requests   If you re asking about a particular database server  add that to your question and we can add details on their implementation  but at its core  that s what clustering is

[database] Database cluster and load balancing

Examples related to database

Examples related to load-balancing

Examples related to cluster-computing

Examples related to database-agnostic