How to configure SQL Server Network Protocols in the SQL Server that houses BizTalk Server databases with PowerShell

Today is a special day for me, it’s my birthday, and I will continue the tradition of giving something to the community on this day (I started this tradition last year)… In the past, I wrote a series of posts describing step by step how to install and configure BizTalk Server 2010.

One of this step is to configure SQL Server Network Protocols, in special ensure that TCP/IP is enabled and Shared Memory is disabled. You can see how to accomplish this using SQL Server Configuration Manager tool here.

However a community member (anonymous) left me a comment asking me whether it was possible to accomplish this (disable shared memory) from the command line or registry and the answer is: YES, of course, you can, for example, configure all the SQL Server Network Protocols with PowerShell.

All network protocols are installed by SQL Server Setup, but may or may not be enabled. And you need to be aware that this protocols can have an impact in your BizTalk Environment, for example:

  • Under certain stress conditions (such as clients accessing SQL Server from the same computer), the SQL Server Shared Memory protocol may lower BizTalk Server performance.
  • BizTalk Server loses connectivity with a remote SQL Server computer that houses the BizTalk Server databases and this may happen if the necessary protocols for SQL Server are not enabled.

So normally we need to perform the following configuration:

  • Disable the “Shared Memory” and “VIA” protocols
  • And Enable the “TCP/IP” and “Named Pipes” protocols

How can I configure SQL Server Network Protocols with PowerShell?

This is a simple script to configure SQL Server Network Protocols for SQL Server that houses BizTalk Server databases:

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

##################################################################
# Function to Enable or Disable a SQL Server Network Protocol
##################################################################
function ChangeSQLProtocolStatus($server,$instance,$protocol,$enable){

	$smo = 'Microsoft.SqlServer.Management.Smo.'

	$wmi = new-object ($smo + 'Wmi.ManagedComputer')

	$singleWmi = $wmi | where {$_.Name -eq $server}

	$uri = "ManagedComputer[@Name='$server']/ServerInstance[@Name='$instance']/ServerProtocol[@Name='$protocol']"

	$protocol = $singleWmi.GetSmoObject($uri)

	$protocol.IsEnabled = $enable

	$protocol.Alter()

	$protocol
}

##################################################################
# Enable TCP/IP SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "TCP" -enable $true

##################################################################
# Enable Named Pipes SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "NP" -enable $true

##################################################################
# Disable Shared Memory SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "SM" -enable $false

##################################################################
# Disable VIA SQL Server Network Protocol
##################################################################
ChangeSQLProtocolStatus -server "BTS2010LAB01" -instance "MSSQLSERVER" -protocol "VIA" -enable $false

Because after we correctly set up the protocols, we need to restart the SQL services for the changes to take effect, this script additional also restart all the services and also BizTalk Services if they exist! However, this last part is optional.

$service = get-service "MSSQLSERVER"
restart-service $service.name -force #Restart SQL Services

$service = get-service "ENTSSO" #Start Enterprise Single Sign-On Service
if( $service -ne $null )
{
	start-service $service.name
}
get-service BTS* | foreach-object -process {start-service $_.Name} # Start BizTalk Services

Once again I also like to thank my friend Rui Machado for always want and help in all my small challenges, In this particular sample, Rui was the creator of the function ChangeSQLProtocolStatus 😉

The script can be found and download on Microsoft TechNet Gallery:
How to set SQL Server Network Protocols in the SQL Server for BTS Databases (7.4 KB)
Microsoft TechNet Gallery

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

2 thoughts on “How to configure SQL Server Network Protocols in the SQL Server that houses BizTalk Server databases with PowerShell”

    1. Hi Leonid, Thanks.
      When we force the restart of the MSSQLSERVER he will also restart all SQL services that depend on it and stop all the other dependent services like BizTalk Services and Enterprise Single Sign-On Service

Leave a Reply

Your email address will not be published. Required fields are marked *

BizTalk360
BizTalk Server

Over 500+ customers across
30+ countries depend on BizTalk360

Learn More
Serverless360
Azure

Manage and monitor serverless
components effortlessly

Learn More
Atomicscope
Business Users

Monitor your Business Activity in iPaaS
or Hybrid integration solutions

Learn More

Back to Top