none
Does SQL server support Active-Active Clustering?

    Question

  • if it doesn't does it mean that when user perform 1 transaction, I have to update both databases at a time.


    In the case of updating remote db down, I have to keep waiting for it to be up, and update?

    Thursday, July 25, 2013 4:58 AM

Answers

All replies

  • Yes it very much supports and now AA clustering is called as Multi instance clustering.Make sure you use competent hardware and enough RAM on both nodes to support them during failover when both nodes are active on same instance.

    Can you put some more light on this,can u please elaborate what u want to ask

    >>In the case of updating remote db down, I have to keep waiting for it to be up, and update?


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    Thursday, July 25, 2013 5:11 AM
  • what do I do in case I am unable to establish a connection with another server, or that server is down?
    Currently,

    Node1            Node2

    |                            |

    |-------App Server--|

    App server does updating on Node 1/Node 2

    • Edited by z0802361 Thursday, July 25, 2013 6:40 AM edit
    Thursday, July 25, 2013 6:37 AM
  • You need to specify a virtual name of server, so if one node is fail over users will be disconnected and reconnected automatically. 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 25, 2013 6:41 AM
  • As with clustering where mode than two nodes form a cluster you specify virtual name to each sql server application so no matter how many nodes are involved and which ever node is owner of application ,application uses virtual name( SQL server virtual name ,or sql cluster name) to connect to sql server .So even if your One node fails and your both node is on same second node application will be able to connect to SQL cluster using virtual name so it doesnt have to worry about hardware.

    A/A cluster generally means two different application accessing two different sql instances and both are active on different nodes(so its kind of active active).So suppose ur node 1 goes down which is having app1 which is accessing SQLINST1 .In this case SQLISNT1 will failover to node2 and with very little downtime(may be it may not appear to u a downtime) and will resume its activity on Node2

    But do remember during failover inflight transaction or transaction currently running will be rolled back ,

    Hope its clear


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, July 25, 2013 6:55 AM
  • May  I know whether this A/A  can be done in windows server R2 SQL server Management studio?

    May I know how to implement this?

    May I know when 1 database happens, what happens to the other database? Does it get updated as well?

    what if connection is down for 1 database?, then it does not get updated,  then suddenly it's up does it update the secondary database?

    Sunday, July 28, 2013 8:43 AM
  • May  I know whether this A/A  can be done in windows server R2 SQL server Management studio?

    ANS:Yes you can do it in Win server 2008 R2

    May I know how to implement this?

    ANS:You can have a look at this discussion check out the links provided by the answerer
    For A/A clustering i assume you would be running two instances on each node.

    So you have to install two separate instance of SQL server each having its own resource group.Means data file and log file of each on separate drive.

    For configuring MSDTC see above two links,.

    A/A clustering is same as A/P just in A/A both nodes have one resource ownership and both are active

    Make sure you have enough RAM on both nodes to support Failover

    May I know when 1 database happens, what happens to the other database? Does it get updated as well?

    ANS:I think first you read about clustering in details see this.Clustering never provides redundancy for database ,if you want that configure trn logshippin/mirroring for DB.I dont understand ur question well

    what if connection is down for 1 database?, then it does not get updated,  then suddenly it's up does it update the secondary database?

    ANS:Where does secondary DB comes its same database which just failover..Read this

    Additionally : what i want to say is in A/A clustering if one NODE not database goes down services owned by that node failsover to other and what ever transaction are going in that database will be rolled back .But database will remain online.So if ur running any update and failover happens it will be rolled back and you have to start update again.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Sunday, July 28, 2013 11:06 AM edited a line
    Sunday, July 28, 2013 10:59 AM
  • for the above confirm is Active/Active ?

    as I thought failover only happens in Active/Passive

    Sunday, July 28, 2013 1:28 PM
  • Failover has nothing as such to do with A/A or A/P ,it happens in both, it is basis of cluster to maintain high availability.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, July 28, 2013 1:56 PM
  • The term "clustering" in the MS and SQL Server world means a fail-over technology. The db engine executes on only one machine (node) and if that fails, then the system starts the DB engine on a different machine (node). Each node has access to the shared storage, but only one at a time. Pure fail-over. You can, however, have several database engines installed in such a set oc machines (cluster), and sometimes, unfortunately, this is called "active-active". That is a horrible terminology, as you robably understand, and we have tried to stopped the community from using that terminology for about 10 years now, with little success, I'm afraid.

    So, no, no technology where several database engines can write to the same data at the same time. If that were what you are looking for. If not, please elaborate.


    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, July 28, 2013 3:10 PM
    Moderator
  • Tibor is completely correct. Microsoft clustering is a SHARED NOTHING environment.

    I do want to nit-pick at this line though

    So, no, no technology where several database engines can write to the same data at the same time.

    You could argue transactional replication (peer-to-peer)...

    -Seam


    Sean Gallardy | Blog | Twitter

    Wednesday, July 31, 2013 2:13 AM
    Answerer
  • what i wish to achieve is:

    user can access url which can lead to users going to website at node 1 or node 2

    when user performs transaction at node 1, database should be updated at node 2.

    when user performs transaction at node 2, database should be updated at node 1.

    node 1 /node 2 are separated over a internet connection, let's say internet connection from node 1 to outside is down, which means node 1 is unaccessible.

    when user access url, database transactions updated at node 2.

    let's say internet connection is up again, 

    the updates at node 2 should update database at node 1.

    Can sql server A/A achieve this?

    Wednesday, July 31, 2013 3:55 PM
  • I agree, Sean, that replication could be seen as a type of "true" A/A technology. That could be either P2P or Merge replication. To continue the nitpicking tradition ;-), I would say that neither of these technologies are "application transparent". Definitely for P2P you want to design the app with this (potentially both HA/DR and scalability) implementation in mind. And same goes for Merge replication, but perhaps not in such a high degree (depending on how you handle conflict detection part).


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, July 31, 2013 4:36 PM
    Moderator
  • How would you handle conflicts?

    I.e., a user tries to modify the same row at the same time; one hitting node1 and another hitting node2?

    It is easy to both load balance and implement HA/DR when you don't own data (like a web-server). It is much more difficult when you do own data (like a DBMS) because if the potential for conflicts.

    In the end, you need to decide whether you are looking for a HA/DR implementation or a load-balancing implementation. The technology which is best for HA/DR might not be ideal for load-balancing, or it might not do anything at all for load balancing! So, start with that question, and based on that answer we can try do guide you further.

    So: is it HA/DR or load-balancing you are looking for?


    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, July 31, 2013 4:40 PM
    Moderator
  • This term is over used and "Active/Active" is a misrepresentation of the reality. 

    In prior years, a cluster meant and "Active/Passive" cluster.  In the simplest terms of a 2 node cluster, node 1 as "Active" handling all traffic, node2 was "Passive" doing nothing but waiting for node 1 to fail.

    In new Windows clustering, you can have cluster service "groups" which run independently on seperate nodes.  This allows node2 to do things other than nothing but be a "passive" node.

    In the SQL Server world, this term is not load blancing or any sharing at all.  This term refers to MULTIPLE instances running on a cluster, where none of the nodes are technically "passive".  There is no circimstance where you have 2 nodes running the same instance at the same time.  In a SQL Server "Active/Active" cluster it refers to a 2 node cluster where SQL Instance 1 runs on node1 with a failover partner of node2 and node2 runs SQL Instance 2 with a failover partner of node1.

    This is NOT load balancing or sharing of anything.


    • Edited by Tom Phillips Wednesday, July 31, 2013 5:05 PM edit
    Wednesday, July 31, 2013 4:56 PM
  • :)

    Agreed - although it could be transparent to the application as long as the data model was built that way... which doesn't really happen unless it's custom or home grown.

    -Sean


    Sean Gallardy | Blog | Twitter

    Wednesday, July 31, 2013 5:02 PM
    Answerer
  • True Sean. In the end, you'd want an "OK" from the app developers/supplier to use with these technologies (P2P or Merge replication)...

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, July 31, 2013 6:01 PM
    Moderator
  • I am looking for HA though

    let's say it's 2 separate links, user can click on primary link to go to node1, or DR link to go to Node 2.

    Thursday, August 01, 2013 1:33 AM
  • Is modifying the application to handle potential conflicts an option?

    Tibor Karaszi, SQL Server MVP | web | blog

    Thursday, August 01, 2013 6:40 AM
    Moderator
  • In Windows clustering, only one node handles all traffic at any time.  node 2 is "passive", no services or connections can be made to node2 until node1 fails over.

    There is no way in clustering to change your connection string to the "DR link".

    If that is what you want, you would need to use another method.


    Thursday, August 01, 2013 3:08 PM
  • So MS SQL server only works for Active -Passive only

    nowadays customers want active active as they think that if a server is passive and does nothing, it only serves as a failback purpose only.

    looks like my definition of Active-Active is different for your definition

    Mine definition of Active-Active is that both nodes can process transactions(user is able to connect to both nodes,2 nodes can handle the traffic), and also any changes in one will perform the update to another.




    • Edited by z0802361 Tuesday, August 06, 2013 2:27 AM
    Tuesday, August 06, 2013 2:18 AM
  • Correct.  The 2 nodes are doing "something", just not the same thing.  The term "active-active" refers to what used to be the passive node not being idle all the time, not load balancing.

    If you think about it, it makes sense, because a "failover cluster" shares the hard drives.  You only have one copy of the data.  You cannot have 2 instances accessing the same drives/data, so it is not load balancing.

    If you want load balancing, you need to use merge or P2P replication or mirroring to 2 different instances with their own copy of data.

    Tuesday, August 06, 2013 1:33 PM
  • I just want to confirm whether sql server can perform the A-A clustering.

    Users can perform transactions on Node 1 and on Node 2, not just on primary node only.

    And it's for DR Purpose not load balancing

    It has to fulfil this requirement:

    what i wish to achieve is:

    1.user can access different url which can lead to users going to website at node 1 or node 2

    2. when user performs transaction at node 1, database should be updated at node 2.

    3. when user performs transaction at node 2, database should be updated at node 1.

    4. node 1 /node 2 are separated over a internet connection, let's say internet connection from node 1 to outside is down, which means node 1 is unaccessible.

    5. when user access url, database transactions updated at node 2 and not updated at node 1 since connection to node 1 is down.

    let's say internet connection is up again, 

    6. the updates at node 2 should update database at node 1.

    Can sql server achieve this


    • Edited by z0802361 Tuesday, August 06, 2013 3:57 PM
    Tuesday, August 06, 2013 3:55 PM
  • According to what I know, for clustering to happen 1 database is always at the write mode, where transactions can write to database, and the other database is always at read mode

    for active -active, I need both database to be at write mode?

    Tuesday, August 06, 2013 3:56 PM
  • Yes SQL Server can do this, examples already given are peer-to-peer transactional replication, merge replication, service broken implementations, etc.

    This has NOTHING to do with clustering, all nomenclature pertaining to it should cease as that is not what you're looking for.


    Sean Gallardy | Blog | Twitter

    Tuesday, August 06, 2013 5:22 PM
    Answerer
  • Forget about the term "active active clustering".

    What exactly are you wanting?  Are you wanting 2 copies of the same data on 2 servers in 2 locations and being able to use either interchangably (this is what it sounds like)?  That is extremely difficult and I would not recommend it unless you really need it.  That would be merge or P2P replication.

    If you just need 2 copies of the database on different servers, and when the "main" server fails, traffic goes to the 2nd server?   That is failover clustering or AlwaysOn failover.

    Please read: http://technet.microsoft.com/en-us/library/ms190202.aspx

    Tuesday, August 06, 2013 5:58 PM
  • Forget about the term "active active clustering".

    What exactly are you wanting?  Are you wanting 2 copies of the same data on 2 servers in 2 locations and being able to use either interchangably (this is what it sounds like)?  That is extremely difficult and I would not recommend it unless you really need it.  That would be merge or P2P replication.

    If you just need 2 copies of the database on different servers, and when the "main" server fails, traffic goes to the 2nd server?   That is failover clustering or AlwaysOn failover.

    Please read: http://technet.microsoft.com/en-us/library/ms190202.aspx

    I think it will be the first option, traffic can go either to first server or 2nd server which does the processing of the data. That is a customer's requirement of Active-Active, I am just implementing out the solution.

    • Edited by z0802361 Wednesday, August 07, 2013 7:42 AM
    Wednesday, August 07, 2013 7:41 AM
  • Again, how would you handle conflicts? I.e., two users modifying the same data at the same time? This is the key point here. There is no implementation where you can have two copies of the same of data, and a user can connect to any of these two and then modify this data - without a possibility for a conflict. That is an archituctectural impossibility. This means that is you really want to go for "tow sets of data, two database engines, update on both", you need to sit down and think how you want to handle the possibilities for update conflicts. After that you can contemplate what technical solution to use, be it P2P or Merge replication. But none of these are transparent to the application, as mentioned because the possibilities for conflicts!

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, August 07, 2013 10:28 AM
    Moderator
  • As Tibor said, have 2 "synced" copies of the database is extremely difficult.  It needs careful planning and regular maintenance and is not needed in most circimstances. 

    I would not recommend it.

    Wednesday, August 07, 2013 3:07 PM
  • Again, how would you handle conflicts? I.e., two users modifying the same data at the same time? This is the key point here. There is no implementation where you can have two copies of the same of data, and a user can connect to any of these two and then modify this data - without a possibility for a conflict. That is an archituctectural impossibility. This means that is you really want to go for "tow sets of data, two database engines, update on both", you need to sit down and think how you want to handle the possibilities for update conflicts. After that you can contemplate what technical solution to use, be it P2P or Merge replication. But none of these are transparent to the application, as mentioned because the possibilities for conflicts!

    Tibor Karaszi, SQL Server MVP | web | blog

    i would first update local and then update remote database, i was wondering there is any kind of sql server support for P2P or merge replication so that I do not need to update manually.

    Saturday, August 10, 2013 12:36 PM
  • "i would first update local and then update remote database"

    That doesn't answer how you handle a conflict. Say that user A modify a row on server X. At the same time (before A's modification is made on server Y) modifies user B the same row on server Y. You now have a conflict.

    Is this acceptable for you app, at all?

    How would you want to handle this? In the app, by somehow trying to make sure that the conflict never happens in the first place (node A "owns" a subset of the data and users to this not only modifies the data that the node "owns"). Basically, the conflict management is do-it-yourself. If you want this, then go for P2P replication.

    Or do you want SQL Server to decide a "winner". I.e., either A or B wins. And whoever wins will just overwrite the other's modification. Scary, huh? If you want this, then go for merge replication.

    As you hopefully realize, "update anywhere" is not something simple you just add on to an existing app, without making sure that you app, and business, can handle and work well with the consequences.


    Tibor Karaszi, SQL Server MVP | web | blog

    Sunday, August 11, 2013 9:47 AM
    Moderator
  • What you are describing, having a primary database and a seperate updated copy (which is read only), is refered too as "AlwaysOn" or "Database Mirroring".

    Please see: http://technet.microsoft.com/en-us/library/ff877884.aspx

    Monday, August 12, 2013 1:10 PM