As far as a cluster is concerned, you are limited to Active/Passive for a given database.
If you want to have multiple instances accessing the same database, then the two simplest things are:
a) Mirroring. This works OK in SQL 2005 and above, but is greatly improved in SQL 2012. Mirroring will allow you to have one instance doing all the updates while the other instances read committed data.
b) Peer to Peer replication. This is what we use. It does support multiple instances updating the same set of data and sharing the updates, but you need your application to be designed to support this. We need P2P as we populate scale-out servers at multiple sites, which cannot be done with mirroring, but direct all our updates to a single instance because our applications cannot cope with multi-site update.
c) Merge replication. The typical use case for this is where you have subscriber databases that may not be connected to your network full time. It allows more flexibility than P2P, but is more complex to maintain.
Original author: SQL Server FineBuild 1-click install and best practice configuration of SQL Server 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005. 1 Dec 2016
: now over 39,000 downloads.
Disclaimer: All information provided is a personal opinion that may not match reality.
Quote: "When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist." - Archbishop Hélder Câmara