SQL 2012 AlwaysOn

Great question!  It’s something that I see pop up from time to time, and it’s good to have it on this forum.

It’s not possible with AlwaysOn, but it is possible with SQL.  AlwaysOn comes in two flavors – Failover Cluster (FCI), which is the same type of clustering as you’ve seen for years with SQL Server, and Availability Group (AG), which is new.  AAG uses transaction log replication to update up to four replicas synchronously or asynchronously.  The replicas in AAG are read only.  So there’s nothing in AlwaysOn specifically that facilitates a multi-writer scenario (multiple nodes making autonomous changes to a table) like you’re describing.

Your customer is probably talking about either merge replication or bi-directional transactional replication (more likely the former here).  Merge replication been around since at least the SQL 6.5 days.  It looks a little like Oracle RAC, but it's not at all like it.  Where RAC uses a technique called cache fusion to avoid conflicts entirely, SQL Server implements triggers in updates, inserts and deletes at the tabular level to propagate changes, along with a configurable merge agent to resolve conflicts when they happen.

I've never seen bi-directional transactional replication implemented, but it appears to lack an agent to resolve conflicts, and so relies on the database designer to design it in such a way to avoid conflicts.

Since these work on layers above the storage, this functionality cannot be provided by storage-layer replication like RP.  You would end up seeing stale DB cache entries on both sides as Server A updated a table, while Server B was not aware of it (exactly the problem that RAC's cache fusion resolves, which is why VPLEX fits so nicely there).

The big question is if these technologies/techniques have existed within SQL for so long, why aren't they more widely implemented?  I believe there are three primary reasons:

• The motivation for a multi-writer database is often scale-out - the database is too busy for a single node to service the transactions.  But this increases the frequency of conflicts between the servers.

• Conflict resolution requires a deep level of understanding, control, and maintenance of the data structures.  For instance, I don't see how bi-directional transactional replication could be implemented with a COTS application like SharePoint or SAP, because the database must be designed in a way to avoid conflicts.  Merge replication is possible, but requires a fair amount of research, care, and feeding.  So I more frequently see application (not SQL) level replication implementations where multiple geographically dispersed are required or desired  (See for example Metalogix SharePoint replication solution).

• This is Oracle's "bread and butter" feature.  It doesn't take many SQL DBA and developer hours in the implementation and maintenance of merge replication to justify the additional cost of Oracle Enterprise licenses.

Thanks for the question, and I hope this helps!