SQL 2005 default instace IP port configuration change

  • 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.

  • 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?

    SQLServerNewbieMCITP: Database Administrator SQL Server 2005
  • 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.

  • 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?

  • You can it isn't recommended, I'll post some links for clustering. I've ran into that same problem a year ago.

  • 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.

    From:http://www.sqlserverfaq.net/2008/05/17/how-to-change-the-dynamic-port-of-the-sql-server-named-instance-to-the-static-port-in-a-sql-server-2005-clustered-instance/

    HTH!

    MJ

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply