locked
Static Port for SQL 2016 Instance RRS feed

  • Question

  • Hi,

    I am not SQL administrator. For our SCOM environment, I installed SQL Server 2016 with a "Named Instance".

    Unfortunately SQL setup process give no possibility to determine the TCP ports during the installation. It is for me a weakness in the process!

    Then I followed Microsoft article to delete all dynamic ports and set for all IPs the standard port TCP 1433 and re-started all SQL services:

    https://technet.microsoft.com/en-us/library/ms177440(v=sql.105).aspx

    I wanted to verify that SQL intance has really the port 1433. Then I executed T-SQL:

    USE MASTER
    GO
    xp_readerrorlog 0, 1, N'Server is listening on'
    GO

    As reult, it listed me following:

    It is for me very confusing:

    - what is "spid11s" and "Server" process here?

    - Why server listening on ::1 <ip6> and 127.0.0.1 <ip4> on port 49524, althouh my settings completely bases on TCP 1433.

    Here is the screen shot from my TCP/IP settings on SQL instance.

     

    Best Regards

    Birdal

    

     

    Tuesday, January 24, 2017 11:51 AM

Answers

  • - what is "spid11s" and "Server" process here?

    SPID 1 to 50 are used internally by SQL Server. Once SQL Server started some process has to validate the port number used so it will connect using any of the internal spid to validate it , that's what you are seeing.

    - Why server listening on ::1 <ip6> and 127.0.0.1 <ip4> on port 49524, althouh my settings completely bases on TCP 1433.

    Dedicated administrator connection (DAC) was added from SQL Server 2005 onwards. This feature enables administrators to connect to SQL Server using a special port (in your case its 49524) when sql server frozen. Since it has a dedicated port when admin connects as DAC, it will provide special dedicated threads and few memory bytes to analyse whats going on within the server. In a nutshell this port is used by DAC.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    • Proposed as answer by philfactor Wednesday, January 25, 2017 2:02 PM
    • Marked as answer by _Birdal Monday, January 30, 2017 9:56 AM
    Tuesday, January 24, 2017 12:10 PM
  • Hello Birdal,

    "spid11s" is the process id of a system process; all ids below 50 are System processes.

    SQL Server do have by Default 2 "listener", the first is "any", in your case it listen on port 1433, as you wanted.

    Then there is a DAC = "Dedicated Admin Connection" listener only for local Connections and that one listens on port 49524; see Diagnostic Connection for Database Administrators => DAC Port

    So everything is fine here.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by philfactor Wednesday, January 25, 2017 2:02 PM
    • Marked as answer by _Birdal Monday, January 30, 2017 9:56 AM
    Tuesday, January 24, 2017 12:16 PM

All replies

  • - what is "spid11s" and "Server" process here?

    SPID 1 to 50 are used internally by SQL Server. Once SQL Server started some process has to validate the port number used so it will connect using any of the internal spid to validate it , that's what you are seeing.

    - Why server listening on ::1 <ip6> and 127.0.0.1 <ip4> on port 49524, althouh my settings completely bases on TCP 1433.

    Dedicated administrator connection (DAC) was added from SQL Server 2005 onwards. This feature enables administrators to connect to SQL Server using a special port (in your case its 49524) when sql server frozen. Since it has a dedicated port when admin connects as DAC, it will provide special dedicated threads and few memory bytes to analyse whats going on within the server. In a nutshell this port is used by DAC.


    Mark as ANSWER if I helped you today :-) www.sql-articles.com

    • Proposed as answer by philfactor Wednesday, January 25, 2017 2:02 PM
    • Marked as answer by _Birdal Monday, January 30, 2017 9:56 AM
    Tuesday, January 24, 2017 12:10 PM
  • Hello Birdal,

    "spid11s" is the process id of a system process; all ids below 50 are System processes.

    SQL Server do have by Default 2 "listener", the first is "any", in your case it listen on port 1433, as you wanted.

    Then there is a DAC = "Dedicated Admin Connection" listener only for local Connections and that one listens on port 49524; see Diagnostic Connection for Database Administrators => DAC Port

    So everything is fine here.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by philfactor Wednesday, January 25, 2017 2:02 PM
    • Marked as answer by _Birdal Monday, January 30, 2017 9:56 AM
    Tuesday, January 24, 2017 12:16 PM
  • Hi Vidhya,

    Hi Olaf,

    thank you for your qualified answers.

    But I have a problem with this sentence (https://msdn.microsoft.com/en-us/library/ms189595.aspx)

    "SQL Server listens for the DAC on TCP port 1434 if available or a TCP port dynamically assigned upon Database Engine startup.
     

    That is not good that DAC listens after each Database Engine startup on another dynamic port.

    Can I set DAC port static? If yes, how?

    Best Regards

    Birdal

    Tuesday, January 24, 2017 1:42 PM
  • SCOM does not require a default instance on 1433 to install.  You specify the "servername\instancename" when installing SCOM.

    Tuesday, January 24, 2017 2:11 PM
  • Hi Tom,

    our plan & design is so that we always use Named Instances in SQL installations.

    That is independent from SCOM deployment.

    Best Regards

    Birdal


    • Edited by _Birdal Tuesday, January 24, 2017 2:19 PM
    Tuesday, January 24, 2017 2:19 PM
  • A named instance is never on 1433.   Setting it back to port 1433 is making it a "default instance".  You should either just install it as a default instance or leave it as a named instance.

    • Proposed as answer by philfactor Wednesday, January 25, 2017 2:03 PM
    Wednesday, January 25, 2017 1:05 PM