Need the (default) port to connect - why is this not implicit.

  • So I have 2 instances (1 x 12, 1 x 19).

    Both have the default port set to 1433.  Same settings on each.

    I connect in from a central server.

    On one, I must specify the port the other I don't need to.

    Just wondering why.  I would have presumed the default port is implied?

    osql -S server01 (doesn't work)

    osql -S server01,1433 (does)

    osql -S server02 (works)

     

    Thanks.

     

  • The reason it is not implicit is that there is no hard rule saying the default instance must run on port 1433, and most people change it off of the default when they get SQL set up.

    As a guess as to why it is happening for you - do you have the SQL Browser service running on both?

    I expect it is not running on Server01.  My understanding of how that works is that  the SQL Browser service is what you connect to when you don't include the port.  Then the SQL Browser service looks at the instance you requested and passes you over to the appropriate port.  If no instance is specified, then it is the default instance.  If one is specified, then it is a named instance.  This also assumes that the firewall is open on the ports required for the SQL Browser service.

    But without the browser service, it has no way to know what port SQL is listening on.

     

    ALTERNATELY, you could have a SQL Alias set up on your computer for server02 so that server02 is automagically getting replaced by server02, 1433.  I have a lot of SQL aliases set up so I don't need to remember the server it is hosted on and the port.  For example, if I connect to "SSRS", it uses the SQL alias to connect to serverA/SSRS_Instance, 1234.  Note those are not REAL names and numbers, just giving examples.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

  • The SQL Server Browser isn't required for the default instance if it is running on 1433.

    You wouldn't be able to connect to a named instance on 1433 nor a default instance running on a different port if the browser service isn't running but it doesn't sound like that is the OP situation.

  • I wasn't sure if the Browser service was required for the default instance on port 1433 or not, but I generally start the browser service as soon as I install SQL as I rarely install a default instance; I usually use a named instance.

    Based on what you had said CC-597066, I would guess that the OP's issue is that Server01 is a named instance and Server02 is a default instance, but both listening on port 1433.

    Or it could still be the SQL Alias thing I mentioned.  Hard to tell with the information given.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!

Viewing 4 posts - 1 through 4 (of 4 total)

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