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 these steps 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 the 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 these protocols can have an impact on 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 also restarts all the services and 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 wanting and helping in all my small challenges. In this particular sample, Rui was the creator of the function ChangeSQLProtocolStatus 😉

Download

THIS POWERSHELL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

You can download How to set SQL Server Network Protocols in the SQL Server for BizTalk Server Databases with PowerShell from GitHub here:

Hope you find this helpful! If you liked the content or found it useful and would like to support me in writing more, consider buying (or helping to buy) a Star Wars Lego set for my son. 

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. View all posts by Sandro Pereira