SQL Cluster Questions

  • active node is failed while running transaction, would the transaction continued on new active node or transaction will fail in this scenario ?

  • I think during a failover, memory is lost, as computing is moved from one computer to another.

    These are the rules of ACID properties of a transaction. Unless you are handling transactions differently?

    If you are using MSTDC, transaction will roll back on any distributions that were made as well.

  • It will fail. In-flight transactions don't fail over. After crash-recovery, the database will roll back any uncommitted transactions.

  • you mean transaction will rollback on New Passive node and failed on Old Passive Node by using MSDTC ? transaction

  • When the active node fails and the cluster fails over, SQL restarts on the new node. Part of that restart is crash recovery. During crash recovery any uncommitted transactions (uncommitted at the point the previous active node failed) are rolled back. Once the recovery process finishes, the DB comes online. There's no DTC because there's only one instance of SQL (the other node failed)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • How memory Works in SQL Cluster ?

    If active node is fail all the data stored in main memory (Data cache, Buffer cache)

    Would it transfer to New Passive node Main memory ?

  • No.

    Cluster failover is NOT live migration. Failover is just an automatic version of stopping a SQL service on one node and starting it on another.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I talking about Entire server is down due to some hard ware issue, which is Passive Node.

    as regular practice, SQL Server will load the data in Main memory to improve the performance.

    in this scenario, how the memory works if passive node is down ?

    Example: Dirty Pages are in Node1 memory when the entire Node 1 is down

  • Huh?

    A clustered SQL Server doesn't care whether the passive node is down or not. Passive node is passive, there's no SQL instance running on it.

    As I've already explained, a cluster failover, for the purposes of memory, transactions, works exactly the same way as if you manually stopped a SQL instance and then manually started it. Memory is not transfered (it's a cluster failover, not live migration), active transactions are rolled back.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • sri2015 (2/18/2016)


    I talking about Entire server is down due to some hard ware issue, which is Passive Node.

    as regular practice, SQL Server will load the data in Main memory to improve the performance.

    in this scenario, how the memory works if passive node is down ?

    Example: Dirty Pages are in Node1 memory when the entire Node 1 is down

    This is not Oracle and it's not RAC!

    Gail has already explained what happens during a failover

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply