seanf122
2 Bronze

SQL 2012 AlwaysOn

I have a customer with the following scenario:

Customer has a SQL 2012 AlwaysOn 2-node cluster (Windows 2012) running in site A with a single DB.  They are using SQL to replicate at the host level to another 2-node SQL cluster at site B.  According to the customer, they have activated a scenario in which they are able to bring up the replicated SQL DB in site B and allow clients to connect to the replicated copy and make changes while the primary copy of that same DB in site A is also being access and modified by other clients.  They then can use built-in functionality within SQL to essentially reconcile the changes that were made at both sides.

Is this possible with AlwaysOn?  The challenge I see is if someone from client A makes a change "block A" in the DB at site A, and then client B makes a change to that same "block A" in the same DB at site B, when SQL does this reconciliation, how does it decide who wins?

Customer purchased RP and was wondering how RP would achieve the same results (which does not seem to be possible)

Thanks!

Labels (1)
Tags (4)
0 Kudos
2 Replies
Paul_Galjan
2 Iron

Re: 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!

Highlighted
seanf122
2 Bronze

Re: SQL 2012 AlwaysOn

Thanks Paul!  Huge help!

0 Kudos