Change Port Number for SQL Server 2012 Cluster

clusteringfailoversql-server-2012

I have been trying to change the port number of a SQL 2012 cluster with little success. I had thought that I could just do this the same way as normal through configuration manager however, even with the services down in the cluster and changing the port on both nodes the old port number remains.

I have started reading about the need to remove and then add in checkpoints for the cluster but have been unable to find anything relating to SQL 2012.

I am assuming I need to use the PowerShell commands for Remove-ClusterCheckpoint and Add-ClusterCheckpoint but if I am honest I am not clear what parameters I should be using.

Has anyone done this for SQL 2012 on Windows 2012 R2?

Best Answer

Cluster instances use registry keys checkpointing to ensure that all nodes in the cluster share the same configuration. Checkpointing works while the resource (the SQL Server Service in this case) is online. When the resource is offline, checkpointing doesn't happen.

In the context of your issue, just change the port from the configuration manager on the active node while the service is running.

As an alternative, you can edit the contents of the checkpoint file directly, as described in the section "Method 1" in this page.

As far as removing/adding checkpoints with PowerShell is concerned, the syntax is straightforward:

Get-ClusterCheckpoint #lists all checkpoints

#remove checkpoint
Remove-ClusterCheckpoint -ResourceName "SQL Network Name (InstanceName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceId\MSSQLServer" 

#add checkpoint
Add-ClusterCheckpoint -ResourceName "SQL Network Name (InstanceName)" -RegistryCheckpoint "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.InstanceId\MSSQLServer"
Related Question