# ======================================================================================================================
# 
# NAME: Get-TCPPort.ps1
# 
# AUTHOR: Yan Pan
# DATE  : 7/19/2008
# 
# COMMENT: This script queries the registry on a SQL Server host to find the TCP port for an instance on the host.  
# =======================================================================================================================

##############################################################################
# Initialize parameters 
##############################################################################
param ( 
	[switch]$help,
	[string]$hostName = {},		# Name of the SQL Server host. 
	[string]$instanceName = {}	# Name of the SQL Server instance.	
    )


function getTcpPort([String] $pHostName, [String] $pInstanceName)
{
	$strTcpPort=""

	$reg = [WMIClass]"\\$pHostName\root\default:stdRegProv"
	$HKEY_LOCAL_MACHINE = 2147483650

	#SQL Server 2000 or SQL Server 2005/2008 resides on the same host as SQL Server 2000
	# Default instance
	if ($pInstanceName -eq 'MSSQLSERVER') {
		$strKeyPath = "SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp"
		$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
		if ($strTcpPort) {
			return $strTcpPort
		}
		
	}
	# Named instance
	else {
		$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp"
		$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
		if ($strTcpPort) {
			return $strTcpPort
		}
	}

	#SQL Server 2005
	for ($i=1; $i -le 50; $i++) {
		$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i"
		$strInstanceName=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"").svalue
			
		if ($strInstanceName -eq $pInstanceName) {
			$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.$i\MSSQLServer\SuperSocketNetLib\tcp\IPAll"
			$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue

			return $strTcpPort	
		}
	}

	#SQL Server 2008
	$strKeyPath = "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.$pInstanceName\MSSQLServer\SuperSocketNetLib\Tcp\IPAll"
	$strTcpPort=$reg.GetStringValue($HKEY_LOCAL_MACHINE,$strKeyPath,"TcpPort").svalue
	if ($strTcpPort) {
		return $strTcpPort
	}
	
	return ""
}


##############################################################################
# Main Program 
##############################################################################
if ( $help ) {
	"Usage: Get-TCPPort -hostName <string[]> -instanceName <string[]>"
	exit 0
}

if ( $hostName.Length -eq 0 ) {
	"Please enter a host name."
	exit 1
}

if ( $instanceName.Length -eq 0 ) {
	"Please enter a server name."
	exit 1
}


$tcpPort=(getTcpPort $hostName $instanceName)

# If TCP Port is not available, the server or the host doesn't exist.
if ($tcpPort -eq "") {
	"TCP port is not found. Please check the server name and the host name."
	"If the server is a default instance, please use the MSSQLSERVER as the instance name."	
	exit 2
}

$tcpPort