SQL Script to check SQL Server cluster Nodes.

Question

SQL Script to check SQL Server cluster Nodes.

Viewing 15 posts - 16 through 30 (of 32 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Chris Houghton (3/15/2011)
Tested select * from ::fn_virtualservernodes() on both 2005 and 2008 clusters and it returned correct results for both.

So apparently all the three answers are correct?

Everybody wins! 😎

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

michael.kaufmann SSCrazy Points: 2816 More actions · Answer 2

bitbucket-25253 (3/14/2011)
Here we go again -- the specified as correct answer is an item to be depreciated in a future release of SQL Server and one possible answer, and the one I was unfortunate to select is currently available in all the SQL Server editions specified by the question and does work. So this question which should be teaching us something does exactly the opposite, it encourages us to use an item to be depreciated and declares the another selection as incorrect, but which is available and does work in the specified editions...
From the cited reference to justify the answer
Important
This Microsoft SQL Server 2000 system function is included for backward compatibility. We recommend that you use sys.dm_os_cluster_nodes (Transact-SQL instead.
Alas and alack woe is me ......
And now I have a suggested QOD which is:
Who is currently responsible for checking the accuracy of submitted QODs and when will they be replaced.
End of rant

Totally agree!

Only want to add the link to the page again: http://msdn.microsoft.com/en-us/library/ms176098.aspx

Despite the versions listed (from 2005 through 2011), the correct answer is based on an older version (SQL Server 2000). Don't really get the logic--but that's life, I guess.

Regards,

Michael

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 3

Koen Verbeeck (3/15/2011)
Chris Houghton (3/15/2011)
Tested select * from ::fn_virtualservernodes() on both 2005 and 2008 clusters and it returned correct results for both.
So apparently all the three answers are correct?
Everybody wins! 😎

It does appear that's the case, yes.

The explanation makes no attempt to explain why the "wrong" answers are wrong, either.

Kiara SSCarpal Tunnel Points: 4185 More actions · Answer 4

CirquedeSQLeil (3/14/2011)
bitbucket-25253 (3/14/2011)
Here we go again -- the specified as correct answer is an item to be depreciated in a future release of SQL Server and one possible answer, and the one I was unfortunate to select is currently available in all the SQL Server editions specified by the question and does work. So this question which should be teaching us something does exactly the opposite, it encourages us to use an item to be depreciated and declares the another selection as incorrect, but which is available and does work in the specified editions...
From the cited reference to justify the answer
Important
This Microsoft SQL Server 2000 system function is included for backward compatibility. We recommend that you use sys.dm_os_cluster_nodes (Transact-SQL instead.
Alas and alack woe is me ......
And now I have a suggested QOD which is:
I was bit by the same bug. I chose the more current command that is still available and got it wrong. I tested the query of sys.dm_os_cluster_nodes and verified that it does work.

Same here. I picked the current command, not the one being deprecated. Ah, well.

-Ki

Duncan Pryde SSCertifiable Points: 7956 More actions · Answer 5

michael.kaufmann (3/15/2011)

Totally agree!
Only want to add the link to the page again: http://msdn.microsoft.com/en-us/library/ms176098.aspx
Despite the versions listed (from 2005 through 2011), the correct answer is based on an older version (SQL Server 2000). Don't really get the logic--but that's life, I guess.
Regards,
Michael

Actually, if you run the "correct" answer in SQL 2000, you get:

Server: Msg 208, Level 16, State 1, Line 1

Invalid object name 'fn_virtualservernodes'.

You need to run it with the 2 colons for it to work - although not in SQL 2005 or later. See the link in my post above.

Not sure where that leaves us, but hey.

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 6

Does this not mean:

This view will eventually replace the fn_virtualservernodes function which will be deprecated in a future release

that the correct answer is the sys.dm_os_cluster_nodes view?

george sibbald SSC Guru Points: 104210 More actions · Answer 7

question specifically mentions SQL2011 (if it will be called that.)

SELECT * FROM ::fn_virtualservernodes() is only there for backward compatibility and should not be used. It works all the way up to SQL2008R2 but who knows if it will work in SQL2011 (sic). It is marked to be deprecated so may well not.

ipso facto the dmv is the only 100% correct answer

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

Surii Default port Points: 1497 More actions · Answer 8

I lost the point after a lot of research.

But i learned something new today... Thanks for the question, but i am not satisfied with the answer. The correct ans is DMV.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 9

Points have been awarded back and the question corrected to ask which one should be used, and then to note the DMV is the correct answer.

Surii Default port Points: 1497 More actions · Answer 10

Surii

Default port

Points: 1497

March 15, 2011 at 8:47 am

#1298889

Thanks Steve..

UMG Developer SSChampion Points: 13482 More actions · Answer 11

UMG Developer

SSChampion

Points: 13482

March 15, 2011 at 9:36 am

#1298928

Steve,

Thanks for fixing the question.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

stewartc-708166 (3/15/2011)
Given a choice between a function marked for deprecation and a fully functional DMV, I believe most of us would rather choose the DMV, as it would prevent re-work at a later stage.
Ah well, c'est la vie....

Certainly, I tend to choose the supported feature over a deprecated feature.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 13

Koen Verbeeck (3/15/2011)
Chris Houghton (3/15/2011)
Tested select * from ::fn_virtualservernodes() on both 2005 and 2008 clusters and it returned correct results for both.
So apparently all the three answers are correct?
Everybody wins! 😎

Woohoo

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

Steve Jones - SSC Editor (3/15/2011)
Points have been awarded back and the question corrected to ask which one should be used, and then to note the DMV is the correct answer.

:w00t:

Thanks Steve

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Kiara SSCarpal Tunnel Points: 4185 More actions · Answer 15

Steve Jones - SSC Editor (3/15/2011)
Points have been awarded back and the question corrected to ask which one should be used, and then to note the DMV is the correct answer.

Wow! Thanks!

-Ki