Script to determine if two instances are on the same node

  • Comments posted to this topic are about the item Script to determine if two instances are on the same node

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • just query this from each instance and compare the values, no need for reams and reams of code

    SELECT SERVERPROPERTY('Computernamephysicalnetbios')

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

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

  • Because you can also check that SSAS and SQL are not on the same node, or two instances of SSAS, etc...

    /* ----------------------------- */
    Tochter aus Elysium, Wir betreten feuertrunken, Himmlische, dein Heiligtum!

  • Running multiple instances on sql on the same node is not uncommon or bad, in fact its a good way to get the most from your hardware as long as you don't over subscribe

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

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

  • I don't think you need to run your process every 30 minutes; you could just create an "autoexec" stored procedure in your master db (see sp_procoption), which runs any time the SQL instance is (re)started (this does get fired when a cluster fails over from one node to the other).

    Mine contain the code snippet below; it doesn't do exactly the same thing you do-- but may do enough, with the benefit of simplicity.

    IF SERVERPROPERTY('IsClustered') = 1

    SELECT nodename,CASE WHEN NodeName = SERVERPROPERTY('ComputerNamePhysicalNetBIOS') THEN 1 ELSE 0 END AS ActiveNode

    FROM sys.dm_os_cluster_nodes

    ORDER BY NodeName


    Cursors are useful if you don't know SQL

Viewing 5 posts - 1 through 4 (of 4 total)

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