How to identify machine running SQL Server instance

[Nakis]

Hi all,
we have two Win2003 machines in a Microsoft clustered environment. Each machine is running different SQL Server 2000 instances. Let's say we have machines sql1 and sql2. If sql2 goes down, sql1 automatically takes over the SQL Server instances that were running on the m/c that went down (sql2). As we are using some third party s/w (IBM's TWS sceduling tool) to run jobs on SQL Server instances (mostly backups), the TWS connects to a particular m/c and starts the SQL Server job.
The problem is that if one m/c goes down, TWS dowsn't know this, so the job fails, although the SQL Server instance runs on the other m/c.
The question is: is there a way to identify (through a script) which machine a particular SQL Server instance runs to? If yes, then we can amend our scripts, triggered by TWS, to identify this, and connect to the correct machine.

Thank you in advance.

 

[Jamfool]

dbo.xp_getnetname

go

DECLARE @NodeName varchar(30)
EXEC master.dbo.xp_regread
'HKEY_LOCAL_MACHINE',
'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName',
'ComputerName',
@NodeName OUTPUT
select 'The active node is ' + @NodeName

 

[mrdenny]

From dos you can also pull the %COMPUTERNAME% paramater.

Does the TWS have a service that runs on the SQL Server? If it does, make it a clustered resource that is bound to the SQL Services. Then have the TWS send the commands to the virtual server name not the host node name.

If not try having the TWS send the commands to the virtual name not the physical node name.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Nakis]

Hi all thanks for the replies.

Jamfool the sp is working great.
Now I'm just trying to get just the net name of the machine in a text file. I used osql:
osql -E -S servert\test -d DATABASE1 -n -i getnname.sql -o c:\tmp\nname.txt
This is the output:
------------------------------
SERVERT-SQL1

(1 row affected)

Any idea, how the output can just be SERVERT-SQL1 ?
I need to remove the rest of the lines



mrdenny
Good thinking, but we don't have the TWS (ex. OPC) client on the SQL machines -it's too costly . Also, if the TWS sends the commands to the virtual name, then since both machines can be up, which one is it going to pick up? Is it gonna be the correct one, i.e. the one running the instance we want? I can't see how this can be achived.


Thank you all again

 

[mrdenny]

Each virtual world should have it's own name that the SQL Server listens to.

As an example:
SQL01 - Physical Node 1
SQL02 - Physical Node 2
VSQL01 - Virtual World 1
VSQL02 - Virtual World 2

If you send a command to VSQL01 it doesn't matter which physical node the virutal world is running on the correct SQL Server will always respond.

If you run Jamfool's procedure via BCP it will return with out the header row.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com