there are service providers specialize in this type of dns failover, however, there's no reason why you couldn't do it yourself.
If you have your own server, and feel confident enough, host the DNS either on the passive server or some "monitor" server. This is where something like http://www.dyndns.com/ comes in handy.
If you use SQL Server, you could take advantage of log shipping or database mirroring. :)