Yes, there are load balancing services out there that will do what you ask. Because we own our own hardware, we actually have a hardware solution, using Barracuda load balancers. They monitor a page on our website (a specific page that makes a db connection and displays UP if it worked). If it says UP, we know both the web server and database are working. If it doesn't say up, we know that either the web server or the database is not working and it triggers the fail over. There are software load balancers that can achieve the same thing. Ask your hosting provider if they provide any such service.
Edit for above paragraph: Sorry, I guess I should clarify. The Barracuda's have a "last resort" option for failover. So we have only 1 server configured with 100% traffic going to it for load balancing. Upon failure, it'll use the "last resort" IP address. It's not a typical use case for a load balancer, but it works great for our small setup. We can force a failover to datacenter B if we ever need to do maintenance and then bring it back on demand.
We then use DNS failover as a secondary tier. It monitors the websites in the same way, but we give it a delay of 5 minutes or so because we have found that not everyone's ISP respects DNS TTLs, so you'll eventually get people going to both sites at the same time. So we try to avoid a DNS failover if we can.
Speaking of which in order to properly have it fail back and forth without problems, you'll need some sort of centralized database OR master-master replication. We go with the latter, but it's risky business. It's on my to-do list to re architect the database piece.
At any rate, this set up is good enough for 99.9% to 99.99% uptime which for most small businesses is acceptable.
