SQL Server Performance

IP Addresses of TCP/IP Properties

Discussion in 'SQL Server 2005 General DBA Questions' started by pcsql, Feb 8, 2007.

  1. pcsql New Member

    I want to understand the IP Addresses tab of the TCP/IP Properties in SQL Server Configuration Manager. The following settings are from the default named instance of SQL Server 2005 Express (computernameSQLEXPRESS) on a Windows XP SP2 machine with IP Address 192.168.1.151.

    IP1 has the following settings:
    Active: Yes
    Enabled: No
    IP Address: 192.168.1.151
    TCP Dynamic Ports: 0
    TCP Port: 1433

    IP2 has the following settings:
    Active: Yes
    Enabled: No
    IP Address: 127.0.0.1
    TCP Dynamic Ports: 0
    TCP Port:

    IPAll
    TCP Dynamic Ports: 1121
    TCP Port:

    Is the instance using Dynamic Port or Static Port? Normally, I will see blank for TCP Port when TCP Dynamic Ports is 0 in IP1 but in this case, TCP Port has 1433.

    My guess is that the instance is using Dynamic Port and the port is 1121.
  2. Luis Martin Moderator

    I never work with express, but in any Server the default port is 1433.
    If you want to change it, in sql properties (EM or Management Studio) there is a place to do it.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  3. pcsql New Member

    Hi Luis,

    Thanks for your reply. My question actually applies to any edition of SQL Server 2005 not just Express. The Server Network Utility of SQL Server 2000 can only specify a Default Port for TCP/IP. But in SQL Server 2005, that settings has been splitted into ip1, ip2, and ipall and I want to understand how they work.
  4. alzdba Member

    Keep in mind that the ip-address you get to see is stored in the registry !
    It is the ip-address that was in use when you installed sqlserver !
    Using another networkcard may get you a new ipaddress whilst the registrykey will still contain the old one.
    Your sqlserver will work, only the ipinfo is wrong.
    The portnumber is accurate.
  5. pcsql New Member

    Hi alzdba,

    Which registry contains the ip address in use when SQL Server is installed? I'm also trying to understand the registry used by SQL Server.


  6. ghemant Moderator

    Hi,
    I don't remember the exact but it is located in HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesTcpip

    Regards

    Hemantgiri S. Goswami
    MS SQL Server MVP
    -------------------------
    "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami

    http://hemantgirisgoswami.blogspot.com
  7. alzdba Member

    Here's where I found it
    [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibTcpIP1]
    "Enabled"=dword:00000000
    "Active"=dword:00000001
    "TcpPort"=""
    "TcpDynamicPorts"="0"
    "DisplayName"="Specific IP Address"
    "IpAddress"="***.***.***.***"

  8. satya Moderator

  9. pcsql New Member

    Hi alzdba,

    I found the registry you mentioned:

    [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1MSSQLServerSuperSocketNetLibTcpIP1]

    but I also found another registry

    [HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSQLEXPRESSMSSQLServerSuperSocketNetLibTcp]

    which does not have any subkeys but have the string value TcpPort. Any idea what is this registry for?
  10. alzdba Member

    - are you running both sqlexpress and sqlserver2005 on the same machine ?

    - btw : did you check the KB Satya mentioned [?] [^]
  11. satya Moderator

    KB823938 defines the behaviour of this issue and more information on how you have to control the settings, IMHO its not good to attach on registry without a testing.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  12. pcsql New Member

    Hi alzdba,

    Yes, I'm running the following SQL Server instances in the same machine:

    SQL Server 2005 Standard Edition
    SQL Server 2005 Express
    SQL Server 2000

  13. pcsql New Member

    Hi Satya,

    Thanks for those MS article links.


  14. alzdba Member

    quote:Originally posted by pcsql

    Hi alzdba,
    Yes, I'm running the following SQL Server instances in the same machine:
    SQL Server 2005 Standard Edition
    SQL Server 2005 Express
    SQL Server 2000
    As you will have noticed when reading the KB Satya refered to, you should find a set of reg-keys per sqlinstance.
    ...MSSQL.1.. for your first SQL2005 instance
    ...SQLEXPRESS... for your sqlexpress instance

    You'll also find a set for you sql2000 instance, buth they don't store the ipaddress with sql2000. (...MSSQLServerMSSQLServer...)
  15. pcsql New Member

    Hi alzdba,

    I have these registry:

    HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.1
    HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL.2
    HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSQL2005
    HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerSQLEXPRESS

    The MSSQL.1 and SQL2005 seems to be for the instance SQL2005 and the MSSQL.2 and SQLEXPRESS are for the instance SQLEXPRESS.
  16. alzdba Member

    - your key SQL2005 is the pointer from the named instance to the correct MSSQL.x-regkey.
    Likewize for the SQLEXPRESS.

Share This Page