Dual site active-active highly available database

Question

I have an application that is being run on two separate sites. Ideally they would share a database, because each application needs to see what the other is writing to the database.

However, the sites are geographically separate and each needs to be able to operate if the other one goes down.

There also needs to be high availability within each site.

Does MS SQL AlwaysOn provide this functionality? From what I've read it seems to only support a read only secondary site.

I see that a question similar to this was already asked, but it's from before AlwaysOn was released: Is it possible to have 2 or more databases active, and synchronized between them?

Answer 1

Availability Groups provide you the ability to scale out reads via secondary replicas (end user reads, like reporting workloads, as well as things like backup and DBCC). They do not offer anything new that lets you scale out writes.

Your options there are still things like merge and peer-to-peer replication, or manual sharding or distributed partitioning (where the app knows which database to write to and read from). I don't know to what extent (if at all) any of those solutions will support seamless failover. That might be better achieved with manual partitioning and Availability Groups or even mirroring - but again your app would have to understand that there is more than one database and they are distinct and separate. Availability Groups themselves don't provide anything to distribute the writes automatically.