Database – Regarding High Availability for MSSQL Server

You need to do something called multi-master replication (aka master-master replication).

There are a number of guides that walk you through the process.

There are two ways to use a master-master replication setup. The improper way is to write (INSERT/UPDATE etc.) to both masters at the same time. Although MySQL provides configuration parameters that can help you if you decide to do this, I suggest you not rely on this behavior. Particularly, don't think that you can split your writes across the machines as some kind of load balancing, there is no such thing. All writes will occur on all servers.

The proper way is to use one master as a hot standby and only write to one master at a time. Trust me this is will save you a lot of heartache when things go wrong. There is even a tool, MMM (Multi-Master Replication Manager for MySQL) that can assist with monitoring and failover for this. It does require a 3rd server to monitor the masters though.

One final thing. Do NOT, I repeat, DO NOT try to "scale" outward by adding additional masters to create a loop. Like I said earlier, this is not scaling at all since every server must do every write from every other server. It is also a complete nightmare to repair replication for a non-transient failure of one of the masters. The loss of one server "breaks" every server in the loop after it, because they no longer are getting updates from the servers downstream from them.

We use MMM combined with circular replication.

We have two MySQL servers (foo-db1, foo-db2) and each is slaved to the other, so an update on any one will be sent to the other:

http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html

Now that each instance has the data, we've found that it's best that writes only go to one. To accomplish this we create a virtual IP (vIP) under a different service hostname (foo-db) that clients send INSERTs and UPDATEs to. Under Debian you would install the "mysql-mmm-agent" package and generally follow this guide:

http://mysql-mmm.org/mmm2:guide

We use this configuration on most of our web sites: two front-end web servers talking to two back-end database servers. The MMM monitor runs on one of the web servers (which one is determined by Heartbeat):

www.howtoforge.com/high-availability-load-balancer-haproxy-heartbeat-debian-etch

So in summary: use circular replication to make sure two (or more?) MySQL servers are in sync; use MMM to control where the database service vIP lives; use Heartbeat to make sure the MMM agent only runs on one machine at a time.