مشاركة عبر

  • مقالة

How to: Enable or Disable a Server Network Protocol (SQL Server PowerShell)

The TCP and named pipes network protocols are installed by SQL Server Setup, but might not be enabled. Network protocols can be enabled or disabled by using the following PowerShell scripts or by using SQL Server Configuration Manager. You must stop and restart the SQL Server Database Engine for protocol changes to take effect.

For general information about PowerShell, see SQL Server PowerShell Overview. For more information about how to manage protocols by using SQL Server Configuration Manager, see How to: Enable or Disable a Server Network Protocol (SQL Server Configuration Manager).

The SQL Server PowerShell (SQLPS.exe) utility starts a PowerShell session with the SQL Server PowerShell provider and cmdlets loaded and registered. When running PowerShell (PowerShell.exe) instead of SQL Server PowerShell, first execute the following statements to manually load the required assemblies.

# Load the assemblies
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

The following scripts enable protocols. To disable protocols, set the IsEnabled properties to $false.

To enable a server network protocol by using SQL Server PowerShell

  1. Using administrator permissions open a command prompt.

  2. To start SQL Server PowerShell, at the command prompt, type sqlps.exe.

  3. Execute the following statements to enable both the TCP and named pipes protocols. Replace <computer_name> with the name of the computer that is running SQL Server. If you are configuring a named instance, replace MSSQLSERVER with the instance name.

    $smo = 'Microsoft.SqlServer.Management.Smo.'
$wmi = new-object ($smo + 'Wmi.ManagedComputer').

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

# Enable the TCP protocol on the default instance.
$uri = "ManagedComputer[@Name='<computer_name>']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($uri)
$Tcp.IsEnabled = $true
$Tcp.Alter()
$Tcp

# Enable the named pipes protocol for the default instance.
$uri = "ManagedComputer[@Name='<computer_name>']/ ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Np']"
$Np = $wmi.GetSmoObject($uri)
$Np.IsEnabled = $true
$Np.Alter()
$Np

To configure the protocols for the local computer

  • When the script is run locally and configures the local computer, SQL Server PowerShell can make the script more flexible by dynamically determining the local computer name. To retrieve the local computer name, replace the line setting the $uri variable with the following line.

    $uri = "ManagedComputer[@Name='" + (get-item env:\computername).Value + "']/ServerInstance[@Name='MSSQLSERVER']/ServerProtocol[@Name='Tcp']"

To restart the Database Engine by using SQL Server PowerShell

  • After you enable or disable protocols, you must stop and restart the Database Engine for the change to take effect. Execute the following statements to stop and start the default instance by using SQL Server PowerShell. To stop and start a named instance replace 'MSSQLSERVER' with 'MSSQL$<instance_name>'.

    # Get a reference to the ManagedComputer class.
CD SQLSERVER:\SQL\<computer_name>
$Wmi = (get-item .).ManagedComputer
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services['MSSQLSERVER']
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop();
# Wait until the service has time to stop.
# Refresh the cache.
$DfltInstance.Refresh(); 
# Display the state of the service.
$DfltInstance
# Start the service again.
$DfltInstance.Start();
# Wait until the service has time to start.
# Refresh the cache and display the state of the service.
$DfltInstance.Refresh(); $DfltInstance