How to find current SQL Server network alias with T-SQL

Question

Post reply

How to find current SQL Server network alias with T-SQL

Jim Tredinnick

SSC-Addicted

Points: 419
More actions
July 6, 2007 at 12:48 am

#177336

The current production SQL Server has a network / DNS alias that remains constant even tho machine names chop and change. Clients connect using this alias. My problem is that I'd like to find the alias from within the SQL server itself, not from the client side.
I've looked at serverproperty() but no alias property there. I found that in SQL2005 the SMO WMI ManagedComputer class has a server alias property, but I need to install .NET 2 framework and haven't tested this.
Has anyone solved this (without writing a dll to scout the network)? Thanks

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

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

does n't theis

select serverproperty('MachineName') help you.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

SELECT @@Servername and SELECT serverproperty('MachineName') will get you what SQL thinks the machine name is. Typically, this is the name the machine had when it was installed, though it can be changed. (using sp_dropserver @server = 'oldserver' and sp_addserver @server = 'server' , @local= 'local' )

To find the NetBios name of the node that SQL is running on, you can use xp_readerrorlog and look for the line that lists the netbios name (The NETBIOS name of the local node that is running the server is 'xxxxxxxxxxxx'. This is an informational message only. No user action is required.)

You can also use xp_cmdshell to run 'SET COMPUTERNAME', puit the results of that into a table and read through that.

Hope that helps.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim Tredinnick SSC-Addicted Points: 419 More actions · Answer 3

Thanks so far, Gail and Kumar

Networking is not my strong point, so i could be wrong here, but this is what I believe. The machine name (equivalent to netbios name?) is 'xxx', but the DNS alias for the machine is 'yyy' which will point to xxx.xxx.com or similar. Thus the netbios name is not the same as the DNS alias.

I need to find if the current server has the same IP address as the DNS alias.

If I was better at explaining the situation, I probably wouldn't have the problem!

Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I'm not a network expert either. You might have to do a DNS lookup from the command prompt, using xp_cmdshell, then parse the results of that.

You could try ping. See below, may not be exact, but should give you something to start with.

DECLARE @IPAddr VARCHAR(20), @Host VARCHAR(20), @cmd VARCHAR(200)

SET @IPAddr = '192.168.0.1'

SET @Host = 'localhost'

CREATE

TABLE #DNSTesting (

CmdLine VARCHAR(2000)

)

SET

@cmd = 'ping -n 1 -a ' + @IPAddr

INSERT INTO #DNSTesting

EXEC master..xp_cmdshell @cmd

SELECT

CmdLine FROM #DNSTesting WHERE CmdLine LIKE 'Pinging%'

-- will return something like "Pinging Machine1.somedomain.here.there [192.168.0.1] with 32 bytes of data:"

-- this should give you the DNS name for the IP address you specified

TRUNCATE

TABLE #DNSTesting

SET

@cmd = 'ping -n 1 ' + @Host

INSERT INTO #DNSTesting

EXEC master..xp_cmdshell @cmd

SELECT

CmdLine FROM #DNSTesting WHERE CmdLine LIKE 'Pinging%'

-- will return something like "Pinging Machine1.somedomain.here.there [192.168.0.1] with 32 bytes of data:"

-- this should give you the IP Address for the DNS name you specified

DROP

TABLE #DNSTesting

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jim Tredinnick SSC-Addicted Points: 419 More actions · Answer 5

Thanks, Gail

I will try this and let you know how we get on.. It may take a while 'cos other priorities right now.

Jim T