Open source alternatives to Amazon RDS for the data center

Right, if you have two instances with one database, your single-point-of-failure is the single database - anything that impacts on the database (like too much load, or accidentally deleting it, or what have you) will impact on both sites that use it.

If you have one instance with two databases, then your single-point-of-failure is the instance, and anything that impacts on the instance (such as running your OS out of disk space, for example) will impact on both sites that use it.

The only setup that allows for your sites to be completely independent of each other is to run each one on its own instance, with its own database. (And even that's not "completely" independent, just as completely independent as you can control - if something happens to hardware or infrastructure the two instances share, they'd both be impacted.)

Based on the diagram you have provided you would then have a 3rd SQL Server box to allow for an automatic fail over of the mirrored database. See this link for a description of mirroring in an HA setup - http://msdn.microsoft.com/en-ca/library/ms179344(v=sql.105).aspx.

This setup will work as long as you have the 3rd SQL Server instance to act as the witness role. This will work a bit like the way Windows Clustering works where you will need to have the majority of servers online in order to keep the mirrored database online. If your primary server goes offline then the mirror (secondary\backup) server will take over the primary role. Once the server then comes back online, the database should synchronize itself.

Also another consideration for this setup is that your application only relies on the one database. Since mirroring is database specific rather then server specific there may be cases where only one database "flips" over to the secondary server. Where if the web application is reliant on multiple databases then extra consideration will need to be given on how to handle this (this is solved in SQL Server 2012 with availability groups though)