2

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

https://gallery.technet.microsoft.com/scriptcenter/Create-an-AlwaysOn-4f450340

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?

0

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()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.