Why is there automatic failover for HA and manual failover for DR?
It is tricky with generalizations and simplifications. However, here's an attempt:
My feeling is that HA generally is closer geographically. In such cases it is more realistic to use synchronous methods, like sync Availability Groups. In such cases, it is reasonable to failover automatically.
But when we talk DR, it is reasonable to imagine longer distances, since having some distance is part of the resilience architecture itself. And with longer distance, it is likely that you have async methods to get changes across. Like async AGs. Log shipping is by its very nature async. I.e., failover with an async solution and you lose data! Not something you want to happen "behind the scenes".
This is going to change wildly based on each business's tolerance for data loss and recovery times. But, here are my thoughts on the matter.
The SQL Servers do not live in a vacuum and our HA and DR plans are part of a concerted whole that includes app servers, BI and other resources. Add in a healthy mix of geographic dispersal and synchronization across dispersed regions isn't feasible since we don't want temporary lags in the connection to cause lag for the users connected to the primary site.
Given all of that, our business has long decided that any failover will have to be manually executed so that we can make the entire environment move as a whole. We do have synchronous partners for our SQL Servers but we still elected to use manual failover only.
Because in a DR scenario you need to check what really happened. E.g. your DR site loses contact with the primary. Should it activate? Well the primary might actually be fine and someone’s just put a backhoe through the fibre, this happens all the time. Or the primary might have been totally destroyed by a natural disaster or a terrorist atrocity. There is no way for SQL Server to figure this out for itself.
HA is easy, it’s typically within the same DC so the range of failure modes is much more tractable for automated decisions.