usually, the app developer will put a "failover partner" in the connection string for the application. When the primary goes offline, e.g. the server blows up, and the application cannot connect to the database, it will try the server specified by the failover partner.

I'd personally only use DNS redirection when you don't have automatic failover setup.