Sql-server – Can’t create an Availability Group with Powershell when instance uses non-standard port

availability-groupspowershellsql-server-2012

I am trying to create an Always On Availability Group with PowerShell, something I have done many times before but am hitting an issue now that I am using non-standard ports for the SQL Instance

I generally use a slightly modified version of this script

But I get an error saying that the current instance SERVER\INSTANCE,Port must be added to the availability group before it can be created. Now obviously that is the value I pass in at the top of the script

$SqlServerPrimName = "SERVER\INSTANCE,Port"

Which is then used to create a SMO object

$SqlServerPrim = New-Object Microsoft.SqlServer.Management.Smo.Server($SqlServerPrimName)

And then added to the Availability Group here

$AvailabilityGroup.AvailabilityReplicas.Add($PrimaryReplica)

But it obviously is not working!

I notice also that if you try and call $SQLServerPrim it comes back empty for InstanceName even though the object is correctly loaded with other values

Creating an Availability Group through the GUI works

Using the New-SQLAvailability* Cmdlets have the same error

Any idea what I need to change in the code to make this work?

Best Answer

Change $SqlServerPrimName = "SERVER\INSTANCE,Port" to $SqlServerPrimName = "SERVER\INSTANCE" and change the port for the instance afterwards.

$ComputerName = gc env:computername
$SqlServerPrimName = "SERVER\INSTANCE"
$SQLServerPort = 1433
$wmi = new-object ("Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer") $computerName
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpPort"].value = "$portNumber"
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].IPAddresses["IPAll"].IPAddressProperties["TcpDynamicPorts"].value = [System.String]::Empty
#We need to commit the changes by calling the Alter method
$wmi.ServerInstances["$SqlServerPrimName"].ServerProtocols["Tcp"].Alter()
Related Question