Blog de Florent Appointaire

Blog sur les technologies Microsoft (Windows Server, System Center, Azure, Windows Azure Pack/Azure Stack, etc;)

[SQL Server] Changer le port dynamique d'une instance en fixe

Souhaitant installer SCCM sur mon environnement, je dois fixer le port de mon instance SQL Server, sur le port 1433.

Mon serveur SQL étant installé sur un Server Core, je n'ai pas de console. La console SQL Server Configuration Manager ne pouvant pas être utilisé depuis un autre ordinateur (elle essaye de se connecter aux instances locales du serveur), il a fallu trouver une autre solution.

Le seul moyen que j'ai trouvé, est de passer en PowerShell, en exécutant le script suivant:

[void][Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
[void][Reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")
$SQLName = "FLOAPP-SQL01"
$Instance = "SC"
$wmi = new-object ('Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer') $SQLName
$urn = "ManagedComputer[@Name='$SQLName']/ServerInstance[@Name='$Instance']/ServerProtocol[@Name='Tcp']"
$Tcp = $wmi.GetSmoObject($urn)
$Enabled = $Tcp.IsEnabled
IF (!$Enabled)
{$Tcp.IsEnabled = $true }
$wmi.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[0].Value = ""
$wmi.GetSmoObject($urn + "/IPAddress[@Name='IPAll']").IPAddressProperties[1].Value = "1433"
$TCP.alter()

Ici, je supprime le port dynamique et je fixe le port en 1433.

Vous pouvez utiliser le script suivant pour vérifier les ports utilisés de vos instances:

# Store Current Location to return to it when we're done
Push-Location;

# Let's get all the possible hives in the registry on a given system
# We'll use Where-Object to filter down to only those hives which begin with MSSQL
# This gets rid SSAS, SSIS, and SSRS for versions of SQL Server 2008 and up. It also
# gets rid of any hives that are under "Microsoft SQL Server" that aren't for instance
# configuration (or at least not what we're looking for)
Get-ChildItem -Path 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' | Where-Object {$_.Name -like '*MSSQL*'} | foreach {

# Get Instance Name
$props = Get-ItemProperty -path $_.PSPath;

# If there is no default value, this isn't an instance. We need to trap in case the
# property doesn't exist
try {
$InstanceName = $props.psobject.Properties["(default)"].value;
}
catch {
$InstanceName = "";
}

# If there is a valid instance name, proceed farther
if ($InstanceName.length -gt 0) {

# Navigate the child keys
foreach ($key in Get-ChildItem -path $_.pspath){

# Find entries belonging to actual database engine instances
if ($key.name -like "*MSSQLServer*")
{

# Navigate to the key where the TCP settings are stored
Set-Location -path $key.pspath;
cd .\SuperSocketNetLib\tcp -ErrorAction SilentlyContinue;

# Ensure we're only reporting against the right keys

$TCPKey = Get-Location;

if ($TCPKey -like '*SuperSocketNetLib*') {

$TCPProps = Get-ItemProperty $TCPKey;

# Find out if TCP is enabled
$Enabled = $TCPProps.psobject.Properties["enabled"].value;

# Begin the reporting
Write-Host "Instance Name: $InstanceName";
Write-Host "------------------------------------------------------------------------------";

# If TCP is not enabled, there's point finding all the ports. Therefore, we check.
if ($Enabled -eq 1)
{
foreach ($Key in gci $TCPKey)
{
$IPprops = Get-ItemProperty $Key.pspath;
$IPAddress = $IPProps.psobject.Properties["IpAddress"].Value;

# For the Key IPAll, there is no IPAddress value. therefore, we trap for it.
if ($IPAddress -eq $null)
{
$IPAddress = "All"
}

Write-Host " IP Address: $IPAddress";
Write-Host " Dyn. Ports: ", $IPProps.psobject.Properties["TcpDynamicPorts"].Value;
Write-Host " Sta. Ports: ", $IPProps.psobject.Properties["TcpPort"].Value;

}
} else {
Write-Host " TCP not enabled."
}

Write-Host "------------------------------------------------------------------------------";
Write-Host "";
}
}
}
}
}

Source du script: https://www.mssqltips.com/sqlservertip/3542/automated-way-to-get-all-port-information-for-sql-server-instances/ 

Facebook Like
Anonymous