January 14, 2018 at 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
January 14, 2018 at 8:46 pm
sdavis754 - Sunday, January 14, 2018 1:21 AMHi 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.
January 15, 2018 at 2:00 am
( 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
January 15, 2018 at 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?
January 15, 2018 at 3:44 am
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
January 16, 2018 at 5:46 am
sdavis754 - Sunday, January 14, 2018 1:21 AM1.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" 😉
January 16, 2018 at 9:19 am
Thank you for the answers...
January 16, 2018 at 10:41 am
sdavis754 - Monday, January 15, 2018 2:19 AMThank 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