Como configurar os SQL Server Network Protocols do SQL Server dedicado do BizTalk Server

Todos os protocolos de rede existentes no SQL Server (SQL Server Network Protocols) são instalados durante a instalação do SQL Server, mas podem ou não estar activados. E você precisa estar ciente de que estes protocolos podem ter bastante impacto no sua plataforma BizTalk Server, por exemplo:

• Sob certas condições de stress o protocolo de ” Shared Memory” pode reduzir o desempenho do BizTalk Server.
• Ou o BizTalk Server perder a conectividade com o servidor remoto de SQL Server que aloja as bases de dados do BizTalk Server, este problema pode acontecer se os protocolos necessários não tiverem correctamente activados no SQL Server.

Desta forma, na maior parte dos cenários, claro que depende um pouco da infra-estrutura da nossa plataforma BizTalk Server, é necessário realizar a seguinte configuração:

• Desactivar os protocolos: “Shared Memory” e “VIA”
• Activar os protocolos: TCP/IP” e “Named Pipes”

Nos podemos configurar estes protocolos através da ferramenta “SQL Server Configuration Manager” existente no SQL Server:

• Click Start, All Programs, Microsoft SQL Server 2008 R2, click Configuration Tools, e de seguida click SQL Server Configuration Manager. (ver mais aqui)

No entanto podemos efectuar esta tarefa através de PowerShell script e desta forma talvez poder automatizar certas tarefas, e a pedido de um leitor do meu blog, decidi criar este PowerShell script, que efectua estas tarefas:

[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

 

Depois de activar ou desactivar os protocolos, você deve parar e reiniciar os serviços do SQL para que as alterações entre em vigor:

$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

 

Poderá efetuar download do script aqui:

How to set SQL Server Network Protocols in the SQL Server for BTS Databases (2.3 KB)
Microsoft | TechNet Gallery