Powershell Function to Get SQL Server’s Network Protocols

#Function to get SQL Server network protocols
Function Get-SQLProtocols
{
	Param([String]$ComputerName = $env:COMPUTERNAME)

	[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null

	$Server = New-Object "Microsoft.SqlServer.Management.Smo.Server" $ComputerName

	$VersionMajor = $Server.VersionMajor

	Get-WmiObject -ComputerName $ComputerName -NameSpace root\Microsoft\SqlServer\ComputerManagement$VersionMajor -Class ClientNetworkProtocol | Select-Object ProtocolName, ProtocolDisplayName, ProtocolOrder

}#End of function

The output of the above function usually looks like this:

ProtocolName ProtocolDisplayName ProtocolOrder
sm
Shared Memory
1
tcp
TCP/IP
2
np
Named Pipes
3
via
VIA
0

Number ‘0’ means corresponding protocol is ‘disabled’, while the NO. ‘1’ has the highest priority to be applied when a DB connection is being established.

Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in Database Connection, Powershell. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s