No, ArcSDE is not designed so that the ArcSDE application server can failover. The documented
solution is to use Direct Connect and rely on the database API, but even this is not a perfect
solution, since none of the databases support this seamlessly -- you'll need to reconnect when
the primary database fails.
You didn't provide the database product or ArcSDE version, so I can't steer you to the correct
documentation, except to suggest searching with "direct connect failover {database}".
- V