Best HA option for database failover

Question

Best HA option for database failover

Rishabh Patil 161

Hi All,

One of our production database is getting corrupted more than what we would like at the moment. Usually we run CheckDB, rebuild indexes and things gets sorted but the yesterday's one was quite difficult and we had like 10 hours of downtime.

Thinking about HA/DR- Mirroring, replication, AG comes in mind but I want to know which one is best way to go for and why.

Any help would be appreciated

Thanks in advance

Accepted answer

0 additional answers

Your answer

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

Corruption does not come out nowhere, but it is due to hardware-related issues. Very often it is the I/O subsystem that is the culprit. If you have local disks, a good idea is to replace these disks, but not very many people have local disks these days. Often databases are on a SAN, which means there is a whole slew of components that can cause trouble. In any case, your prime concern should be to track down the cause for this corruption, and depending how important those databases are, you may need to goes as far to replace the entire server.

As for HA/DR solutions there are many alternatives, what is the best depends on a number of things:

In case of a disaster, what downtime can you accept. (The so-called RTO, Recovery Time Objective.)
In case of a disaster, how much data can you afford to lose? The last 15 minutes? Content with restoring the full backup from last night?. (This is your RPO, Recovery Point Objective.)
What is your budget?
What is the competence level of your organisation?

The last point should not be overlooked. Availability Groups is a great technology, but it is complex, and you don't understand it, it may cause you more grief than if you do not use it.

If you don't have any HA/DR solution today, I would recommend log shipping. This is not the best technology from a strict HA/DR perspective, but it is the simplest to set up and understand. And, particularly, this is about the only technology that cannot have any impact on the current production environment.

Rishabh Patil 161 Reputation points

2023-08-01T11:47:23.87+00:00

Thanks @Erland Sommarskog

We have 4 nodes under cluster and all these node has different databases running -and its 3par SAN.

In one of the cluster this database is failing, we don't see frequent issues with other databases on other nodes.

Downtime should be less than a hour

Data loss minimum as possible- say 15 minutes as the database support core product

Budget is no concern

The organization is a player in mainframe service business.

Hope this helps

Thanks @Erland Sommarskog
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-08-01T20:51:52.9866667+00:00

Again, your top priority should be to replace/repair the cluster where you see corruption.

As for an HA solution, if you have a cluster, you can set up failover between the nodes. Then again, for a real HA solution, you want the secondary node to be passive, since if one node fails and the instances on this node fails over to another instance, that instance will now get increased load.

In any case, since you say that budget is not a concern, my advice is that you engage an HA consultant to help you to find the best HA solution for you. Myself, I am certainly not an HA expert. But more importantly, a conversation in a forum tends to be quite superficial, not the least since there is information you cannot disclose in public. With a good consultant on site (or for that matter over Teams), you can speak more freely and discuss for a longer time. Also, a good consultant can ask you a lot more questions that I can think of to determine what might be the best for you.
Rishabh Patil 161 Reputation points

2023-08-02T09:01:53.53+00:00

Thanks, hearing you, I think I got the direction I needed to hit it.

Share via

Best HA option for database failover

0 additional answers

Your answer