November 18, 2008 at 4:50 pm
The situation:
OS Version: Windows 2003 x64 SP2
SQL version: SQL 2005 x64 SP2
We would like the default instance on this server to run on a port other than 1433. We tried to use the System Configuration Manager on the active node of our Active/Passive cluster as per the instructions on MSDN. (http://msdn.microsoft.com/en-us/library/ms177440.aspx). This process is not working after we make the changes and the SQL Server is stopped and started using Cluster Administrator
This is what we have noticed:
a) The server does not come back on-line after restart
b) The active node setting for the 64-bit IPALL setting is properly set to 65000 as well as the 32-bit IP setting located in the registry HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server. On the passive node, the registry settings are unchanged for both the 64 & 32 bit server settings. This would lead me to believe that the settings are not replicating to the passive node via the quorum.
My questions:
1) Is there a different proces on a SQL 2005 X64 box to change the default port on the default instance?
2) Is there any reason why I cannot change the default port on the default instance on this cluster? The process seems very straightforward, but is not working in any way.
November 19, 2008 at 8:07 am
I have just changed 2 NAMED instances (on 2 servers in Active-Active cluster) via SQL Server Configuration Manager
No issues after restart successfully
e.g. "SQL Server 2005 Network Configuration"
Protocols for SERVER1, TCP/IP
Protocols for SERVER2, TCP/IP
If you change back to the default 1433 port, does it restart?
November 19, 2008 at 8:19 am
When we use SSCM to change the port back to 1433, the server does not restart. To fix this issue we took the server offline using cluster adminstrator and then did the following.
1. Cluster RES "SQL Server" /removecheckpoint:[registry key]
2. Changed the registry entry for the 32/64 tcp settings on the active node. The port setting on the passive node remained at the default port of 1433 for both the 32/64 values
3. Cluster RES "SQL Server" /addcheckpoint:[registry key]
4. Bring the SQL Server back on-line using cluster administrator
This will fix the server cluster and it will come back on line. Of course we cannot change the default of 1433 to a new value that we desire for the default instance, which is the core issue.
November 19, 2008 at 12:09 pm
We re-installed the clustered 2005 x64 server using named instance only and changed the configuration for the port using SSCM and this worked flawlessly.
Is there some documentation that states you cannot change the port number on a clustered default instance?
November 19, 2008 at 2:37 pm
You can it isn't recommended, I'll post some links for clustering. I've ran into that same problem a year ago.
November 19, 2008 at 3:32 pm
Did you try this:
In order to change the dynamic port of the server to the static port in the clustered named instance of the sql server 2005 we need to follow the following steps:
1) Take the sqlserver service offline from cluster administrator
2) Disable the checkpointing to the quorum using the following command
Cluster res “SQL Server (TEST2005)” /removecheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”
3) Change the Dynamic port of the sql server to static port on all the nodes using the method described above.
4) Enable the checkpointing to the quorum using the following command
cluster res ” SQL Server (TEST2005)” /addcheck: “Software\Microsoft\Microsoft SQL Server\MSSQL.2\MSSQLSERVER”
5) Bring the sql server service online
Pay attention to step 3 as rest all you faollowed the same way.
HTH!
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply