Multiple database servers for performance vs failover

Question

If I have two database servers, and I am looking for maximum performance vs high-availability, what configuration would be best?

Assuming the architecture is two load-balanced web/app servers in front of two db servers, will I be able to have both db servers active with synced data, with web1 to db1, web2 to db2 setup? Is this active/active?

I'm also aware that the two db servers can have their own schema to manually 'split' the db needs of the app. In this case daily backups would be fine. We don't have 'mission critical data.'

If it matters, we have traffic around 3,000-7,000 simultaneous users.

Answer 1

You can do master-master replication, especially if there is a natural partition of your data. We have customers that do this for their users based on the user's region. That way most users don't have to deal with the effects of replication lag on their application while both databases serve as backup for each other. On the other hand, if you don't have a way to partition data, you will end up having to deal with replication errors where both servers locally update the same row, report commit success to the client, breaking replication, and which will have to be resolved manually.

Another option that we see is to have a writable master and one or more read-only slaves. The upside to this approach is that you don't have to find a partition for your data and it's a simpler setup (less likely to get you up in the middle of the night). Downside is that database reads will always be slightly stale. For queries that this staleness is unacceptable, you can always query the master so long as its able to keep up.

There are also a variety of clustered database solutions that may meet your need with less application jiggering.

Answer 2

If you are using mySQL, and you are not concerned about possible differences between your two databases, albeit for a short while, you could just set up mySQL Replication.

This is different to the active-active clustering you have asked about. Active-active clustering implies that 2 or more nodes are actively hosting services, but in a mySQL / SQL Server context, you will not have explicit "synched" data in they way you mean. Instead this could be better achieved by replication or SQL2012 AlwaysOn (if you considered using it).