Very Basic Question on Clustering

  • Hi All,

    I am a new guy into SQL server...Just coming from a diff DB Admin background..
    I am just confused with below question.

    1.In sql server 2 node cluster,what is local to each node?My question is about system DB.Is it locally created for each Node? Or System DB also only 1 and is common for both nodes, like user DB in a SAN?

    2.I Have above doubt due to patching. Eg- If I am doing a particular patching and assume that patch has to update some of the system DB, eg Master..then , if system DB is shared, the patch in effect applied to both active and passive node at same time.., eventhough I am doing pacthing only to my passive node..Is my understanding is correct?

    Pls any expert give me some answer.

    Regards
    Davis

  • sdavis754 - Sunday, January 14, 2018 1:21 AM

    Hi All,

    I am a new guy into SQL server...Just coming from a diff DB Admin background..
    I am just confused with below question.

    1.In sql server 2 node cluster,what is local to each node?My question is about system DB.Is it locally created for each Node? Or System DB also only 1 and is common for both nodes, like user DB in a SAN?

    2.I Have above doubt due to patching. Eg- If I am doing a particular patching and assume that patch has to update some of the system DB, eg Master..then , if system DB is shared, the patch in effect applied to both active and passive node at same time.., eventhough I am doing pacthing only to my passive node..Is my understanding is correct?

    Pls any expert give me some answer.

    Regards
    Davis

    Well, I'm certainly not an expert on clustering, but I know the basics of a Windows Server Failover Cluster.  The databases (both user and system) are on my SAN.  The SQL Server application itself is installed on each server and the server accesses the databases when it's the active node.  When patching, I patch the inactive node, then failover, then patch the now-inactive node.  In other words, if server A is active, patch server B, fail over to server B, then patch server A.  You may need to reboot after patching, but I usually do so anyway.

    For a true expert on clustering, I'd recommend reading Perry Whittle's stairway series on the subject at http://www.sqlservercentral.com/stairway/112556/.  Level 4 is about Windows Server Failover Cluster.

  • ( Starting with SQL2012, ) you could opt to move tempdb to a local drive on each node. ref: http://www.sqlservercentral.com/articles/failover/137066/

    Each  node  has to be hotfixed ( cumulative update ) individually. At failover/move time, SQLServer will apply or rollback the hotfixes  ( CU ) at startup time.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • Thank you both for the replies.
    I Understand the best practice of cluster patching is ,patch on passive first, then fail over..then patch on the other node, which is passive now.
    So my doubt is still there..If the system dbs are in SAN,(that means each node shaqres the  MASTER/MSDB/MODEL and optionally tempdb), then any patch that makes an update to MASTER DB in passive node, will in effect makes patching to current active node as well?

  • No. local software is updated and scripts are provided to upgrade the db at startup (failover) time.
    At startup (failover) time upgrades are being checked and executed if needed.
    If failback is needed to the non-upgraded node, the dbs are reverted (downgraded) at failback time at the non-upgraded node.

    So you will notice longer startup time whenever upgrade / downgrade is needed, but the live system will not be impacted by the upgrade of the passive node.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • sdavis754 - Sunday, January 14, 2018 1:21 AM

    1.In sql server 2 node cluster,what is local to each node?My question is about system DB.Is it locally created for each Node? Or System DB also only 1 and is common for both nodes, like user DB in a SAN?

    In a 2 node cluster with a Failover Cluster Instance of sql server running, only the software binaries and the windows services are local to each node.
    All databases (system and user) are deployed to shared storage locations, this shared storage is a dependency of the clustered role services.

    In a 2 node cluster with alwayson availability groups, the software binaries and services are local to each node along with their own system databases and user databases.

    All of this is detailed in my Stairway to AlwaysOn which has been linked above

    sdavis754 - Sunday,january 14, 2018 1:21 AM


    2.I Have above doubt due to patching. Eg- If I am doing a particular patching and assume that patch has to update some of the system DB, eg Master..then , if system DB is shared, the patch in effect applied to both active and passive node at same time.., eventhough I am doing pacthing only to my passive node..Is my understanding is correct?

    When patching a failover cluster instance you use the rolling upgrade approach.
    You patch any nodes first that do not own the cluster role, once done and if necessary rebooted you failover the instance to one of these nodes and then patch the remaining node that has just had the role drained from it.

    This is also true for Availability Groups, they too use the rolling upgrade approach
    Read the stairway and come back if you have any further questions

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

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

  • Thank you for the answers...

  • sdavis754 - Monday, January 15, 2018 2:19 AM

    Thank you both for the replies.
    I Understand the best practice of cluster patching is ,patch on passive first, then fail over..then patch on the other node, which is passive now.
    So my doubt is still there..If the system dbs are in SAN,(that means each node shaqres the  MASTER/MSDB/MODEL and optionally tempdb), then any patch that makes an update to MASTER DB in passive node, will in effect makes patching to current active node as well?

    Maybe a clarification is that even though the 2 nodes "share" disk space on the SAN, only the one node that is active can actually see that shared disk at any point in time.  The SQL Server services on the passive node will not be running, they only run on the active node.  When you patch the passive node, you are updating the software of a program that is not running, then when you change which node is active, the database files on the shared SAN would then be upgraded to match the program running on the now active node.

Viewing 8 posts - 1 through 7 (of 7 total)

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