I need to know the active node in case of clustered SQL 2000 from any of the
participating nodes for a particular SQL Server instance.

A shared nothing disk array is a setup in which only one node can own a
given resource at any given moment. All other nodes are denied access until
they own the resource.
Based on the above premise, is the following approach valid to ascertain
active node:

1. On each participating node, run query "select * from
::fn_virtualshareddisks()" to get the shared disk name

2. Try to access the disk name returned from above query on each of the nodes

3. If the disk is accessible from a node then it is active node

4. If the disk is not accessible from a node then it is passive node


Please let me know if above is a valid approach to ascertain active node
name in SQL 2000.

TIA.

The correct T-SQL function is fn_servershareddrives() and not
fn_virtualshareddisks()
Sorry for the typo.

"v_2ankp" wrote:

That is an invalid method.

There is only one SQL instance active at any given time. You cannot run a
T-SQL query against a node, only an instance.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP

"v_2ankp" <v2ankp (AT) discussions (DOT) microsoft.com> wrote

I agree that you run T-SQL query against an instance and not against a node.

Let me give you details about the scenario:

I have an active/passive 2-node setup on which single instance SQL 2000 is
installed. Lets say the participating nodes are Node1 and Node2. At any given
point in time there will be only one active node that hosts SQL instance. In
case of a failover the passive node takes the charge of hosting the SQL
instance.
Also the active node (which can be either node1 or node2) owns the shared
disk resource.

I have an application that is installed on both the participating nodes. My
application has a requirement that it needs to know whether the current node,
on which it is installed, is active node or a passive node.

My question is -
The application connects to SQL instance and runs T-SQL query "select * from
::fn_servershareddrives()" and get the drive letter of shared disk.
Is it correct for my application to infer that current node (on which it is
installed) is active if the drive letter, returned by executing T-SQL query
(agains the T-SQL instance) , is accessible from that node.

Appreciate all your help.

"Geoff N. Hiten" wrote:

You connect to instances, not nodes.

Nodes always show all drive letters. In Windows 2003, the disk shows up as
"offline" if it is not on the current node.

I would place whatever local disk file you have on a clustered file share
resource. That way you always address it via the clustered network share
name. Your requirement for node-awareness is a fundamental violation of
clustering and will result in a low-availability solution.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP


"v_2ankp" <v2ankp (AT) discussions (DOT) microsoft.com> wrote

Why does your application require knowledge of what node is actually hosting
SQL Server? I can see no reason for an application to care where the
database files reside - because you cannot access them directly and must use
SQL Server to access them.

If you clarify the reason you think you need that information, maybe we can
come up with a better solution for you.

"v_2ankp" <v2ankp (AT) discussions (DOT) microsoft.com> wrote

Point taken.

Now if I share the files on shared disk being used by SQL Server and create
a mapped drive, my application cannot see that drive.

I have observed that my application is running under NT-AUTHORITY/SYSTEM.

Any idea how can I make the mapped drive available to my application?

"Geoff N. Hiten" wrote:

Use a UNC name. Those will always be visible to any login.

You may have to grant rights to the machine to access the share and NTFS
file system in order to get this to work.

--
Geoff N. Hiten
Principal SQL Infrastructure Consultant
Microsoft SQL Server MVP

"v_2ankp" <v2ankp (AT) discussions (DOT) microsoft.com> wrote