Thanks Doctor L, thank makes pretty good sense about the fixed/dynamic ports. It's funny no one mentioned this issue to me before. I don't like having non-standard setups, so I'd actually prefer to leave all the ports the way they were for my SQLExpress instance, but then I don't know how to connect to an instance from the outside world -- Patient K
On 27 November 2013 17:25, GregAtGregLowDotCom <g...@greglow.com> wrote: > Hi Greg, > > > > Back in the dreamtime, you could only have a single copy of SQL Server > installed on a computer. When SQL Server 2000 appeared, they provided the > ability to install additional copies (ie: named instances) of SQL Server in > addition to the “default” instance. At the time, you could have 16 of them, > now you can have 50 of them. > > > > When you connect just using the name of your computer, you are connecting > to port 1433 for the default instance. > > > > Using an unmodified installation, SQL Express installs itself as a named > instance called “SQLEXPRESS”, so instead of connecting to “mycomputer” you > would connect to “mycomputer\SQLEXPRESS”. You could also use the shared > memory provider by connecting to “.\SQLEXPRESS” or “(local)\SQLEXPRESS”. > > > > You can install Express as a “default” instance but that won’t be what you > will have done. You will have installed it as a “named instance” called > SQLEXPRESS. > > > > The default for “named instances” is that they use dynamic ports. That’s > why you will have seen 0 in the port settings in SQL Configuration Manager. > When you connect to “somecomputer\someinstance”, your client starts by > having a UDP based discussion (on port 1434) with the SQL Browser service. > That service returns details of the port that the instance you mentioned is > currently listening on. Your client then connects to that port. > > > > Named instances can, however, be configured to use fixed ports. > > > > What it sounds like you have now configured, is that you have a named > instance configured for port 1433. While that’s uncommon, there’s nothing > wrong with it per se. It just means that if you then tried to install a > default instance (for example a SQL Server developer edition) using default > settings, that install would fail. > > > > Regards, > > > > Greg > > > > Dr Greg Low > > > > 1300SQLSQL (1300 775 775) office | +61 419201410 mobile│ +61 3 8676 4913 > fax > > SQL Down Under | Web: www.sqldownunder.com > > > > *From:* ozdotnet-boun...@ozdotnet.com [mailto: > ozdotnet-boun...@ozdotnet.com] *On Behalf Of *Greg Keogh > *Sent:* Saturday, 23 November 2013 10:03 AM > *To:* ozDotNet > *Subject:* [OT] Public SQL Server [answer found] > > > > It's all to do with dynamic and static ports, something I haven't anyone > discuss before. > > > > http://technet.microsoft.com/en-us/library/ms177440.aspx > > > > I deleted the Dynamic Ports 0 and added TCP Port 1433 in all the IP > settings. I don't know if all need to be changed, but I haven't got time to > debug it all. I hope this change doesn't have any nasty delayed side > effects. > > > > Greg >
