The default instance has a name
11August 31, 2015 by Kenneth Fisher
The default instance actually has an instance name. It’s MSSQLSERVER. This is probably familiar to a lot of you but hold on because I’m going to go a little bit deeper. When we connect to a default instance we typically just put down the server name right? Is that because the name MSSQLSERVER is special somehow? Nope. Not at all.
I’m guessing everyone has heard of port 1433 right? Well that particular port IS special. You do not have to specifically name the instance if it is on port 1433. And since by default the default instance starts on port 1433 …
So for example if we have two instances one is the default and the other is TOtherOne. Normally we would expect to reference these like this:
| Referenced By | Instance Name | Port |
| Server | MSSQLSERVER | 1433 |
| Server\TOtherOne | TOtherOne | Not 1433 |
But if we change the port assignments so that TOtherOne is port 1433 and MSSQLSERVER is pointing to, well, really anything else. Now we reference them like this:
| Referenced By | Instance Name | Port |
| Server\MSSQLSERVER | MSSQLSERVER | Not 1433 |
| Server | TOtherOne | 1433 |
If you want to read up more on how this works I would recommend reading this article on the SQL Server Browser Service.
This also allows us to do something really useful. If we have multiple instances on a single server we can alias them in such a way that each instance is called by a different server name and without an instance name. I was able to find some good instructions on how to do this here.
SQL Studies 
I am confused on the 3rd and 4th bullets. Do you need to specifically reference the default instance as though it were named MSSQLSERVER? What’s the 4th bullet then?
Yes. If the “default” instance is not pointing to port 1433 then you have to call it by name. MSSQLSERVER. The fourth bullet is how you would reference the instance Server\TOtherOne if it’s pointing to port 1433. I’ll try to make it a bit clearer.
[…] downs in Excel – video Join Me for Free #PowerPivot Training w/ @ExcelBIPASS Sept 10 – 12p CDT The default instance has a name Power BI Desktop & Excel Avoid Frequent use of TVPs With Wide Rows SQL 2014 queries under a […]
Hi Kenneth,
thanks for your post. Since that was a hard lesson for me to learn some months ago, I have just an addition to your tip at the end, relating to the aliasing of multiple instances listening on the same standard tcp port 1433:
Be aware of old sql clients that use something called an “InstanceValidity” check. This check seems to rely on a fixxed relation between port 1433 and the default instance name “mssqlserver”.
As a result those old clients have to submit the port in their connection string when connecting to a named instance listeneing on the standard port.
(Sources:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7f353b59-7e7f-4ec3-adcb-e69ca2629b21/named-sql-2008-r2-server-listening-on-default-port-1433-with-dedicated-ip-address-requires-port?forum=sqldataaccess
and cited in:
http://serverfault.com/questions/579887/how-to-connect-an-odbc-dsn-to-nondefault-sql-server-instance-on-default-port).
Regards, Joerg
Great tip! Thanks. I haven’t seen that before.
Just a side note to your article…
We never use port 1433. Many years ago a virus used the well known fact that by default SQL Server uses that port. We “old timers” remember quite well the havoc that the SQL Server Slammer virus caused. We always define a different port number and disable the browser.
Oh I remember the Slammer too. I didn’t get hit with it personally but heard about it. My understanding is that at this point port scanning is so improved that it doesn’t really matter. Of course I’m not an expert in this by any means so I am more than willing to be proven wrong.
Nice article. On a cluster you can allow more than one instance be referenced without the instance name by making them all listen on port 1433. Are there any pro/con to this approach on a cluster vs creating alias as you mentioned?
http://www.trycatchfinally.net/2009/05/Accessing-a-clustered-SQL-Server-instance-without-the-instance-name/
Actually the cluster approach is the same (if I understand correctly). Certainly the first place I saw this particular trick was on a cluster setup.
Hi Kenneth,
Really glad I read this when it was released. Just had to install a couple of instances. The firewall in between is allowing port 1433 but nothing else. I swapped the ports for the default instance and named instance. I (And my developers) can now connect to the “Dev” instance as if it was the default instance.
Thanks!
BTW If you need to connected to SQLSvr01\Dev then the Browser service must be running.
Probably common knowledge, but I never realized. We only had default instances.
Glad it was helpful! And yes, you have to have the browser to hit any database other than the default. Assuming you are using the instance name. You can call a named instance by port number instead without the browser. And in fact that’s a good idea if you have a firewall. If you use the browser with a firewall you have to open port 1434 as well. Take a look here: https://sqlstudies.com/2015/03/30/port-numbers-and-sql-server/