Powershell: Enabling SQL Server Named Pipes Server Protocol

0

Difficulty: Beginner

On a recent project, we ran into an issue that required us to enable a SQL Server Network Protocol – Named Pipes – for roughly 100+ servers that run Microsoft SQL Server 2008. Rather than tackle this manually, we decided to give a powershell a crack at it. After 10 minutes of tweaking a predefined script from Microsoft Technet (http://technet.microsoft.com/en-us/library/dd206997%28v=sql.105%29.aspx), we were up and running and able to proceed with the project. This might come in handy for anyone facing a similar challenge. This script pulls the list of servers from a text file. Alternatively, you could query active directory if your SQL servers are in the same OU. Near the bottom of the script is the $uri variable calls the particular SQL Server Instance. That instance can be changed to suit your environment.

 

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

# Pull the servers from a text file list
$servers=Get-Content c:holdservers.txt

foreach ($server in $servers)
{
# Enables the SQL Server Browser service and sets the startup to automatic
Write-Host "starting sqlbrowser service on $server"
Set-Service -ComputerName $server -Name sqlbrowser -StartupType Automatic

$Mc = New-Object ('Microsoft.SQLServer.Management.SMO.WMI.ManagedComputer')"$server"

# List the object properties, including the instance names.
$Wmi

Write-Host "Enabling Named Pipes for the SQL Service Instance on $server"
# Enable the named pipes protocol for the default instance.
$uri = "ManagedComputer[@Name='$server']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Np']"
$Np = $Mc.GetSmoObject($uri)
$Np.IsEnabled = $true
$Np.Alter()
$Np

Restart-Service -InputObject $(Get-Service -ComputerName $server -Name sqlserveragent) -Verbose
}

Share.

About Author

Leave A Reply

Welcome, guest maybe you should register or login