-2

In SQL server traditional cluster, does each node of cluster has their our own master database? I am sure physically they don't...but logically might be??

Also as we know that the method of applying SQL patch on traditional cluster is to patch passive node first and then failover and then do active node...if there is no SQL on passive node then how SQL patch gets applied on passive node at first place???

Any inputs are appreciated...

  • You are confusing the master database’s purpose with Failover features (Cluster vs AlwaysOn). The master is the instance’s database. It has to exist in one location. A simple Failover Cluster does not have more than one in existence. On the other hand, the Failover Cluster service on each node is likely more what you are thinking of along with the computer object that points to the current active node. – clifton_h Feb 1 at 5:56
  • Also, Failover Clusters do not necessarily have to have SQL on them. A Failover Cluster May contain a SQL role that extends to one more nodes in the Cluster...but if you are asking about OS level changes...again, Failover Cluster != SQL Server Failover Clustering. – clifton_h Feb 1 at 6:01
  • @clifton_h I got your point. SQL will be only on active node at any given point of time. So how do sql update happens on passive node? – sachin-SQLServernewbiee Feb 1 at 6:18
  • It updates the binaries and such (DLLs, etc). When you fail over, the system databases will be upgraded by the newly updated SQLSERVR.EXE (and its friends) automatically at startup time. That last step isn't even necessary if not storage changes happened between the two builds. – Tibor Karaszi Feb 1 at 8:27
3

In SQL server traditional cluster, does each node of cluster has their our own master database?

In a SQL Server FCI implementation, all of the system and user databases are stored on shared storage. The binaries are local to the machine but the databases are shared.

Also as we know that the method of applying SQL patch on traditional cluster is to patch passive node first and then failover and then do active node...if there is no SQL on passive node then how SQL patch gets applied on passive node at first place???

Like I stated above, each node has an installation of SQL Server where the binaries are local. This is why you can patch a node that isn't in use.

In addition, there are entries made into cluster specific locations which are replicated by the Windows Cluster which is how other nodes know if certain operations or items happen.

1

You may be referring to the resource database, which contains many of the system objects, as you mentioned when the update is applied in the passive node, the "file" is updated, along with binaries and other local files.

As for master database the answer is no, there's only one master database for the whole instance, no matter how many nodes you have, but each of the nodes have its own resource database, (along with the binnaries).

https://docs.microsoft.com/en-us/sql/relational-databases/databases/resource-database?view=sql-server-2017

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.