The default instance has a name

11

August 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.

11 thoughts on “The default instance has a name

  1. notarian says:

    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.

  2. […] 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 […]

  3. Joerg Hencke says:

    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

  4. Bob Browning says:

    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.

  5. SQLGrinder says:

    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/

  6. Dennis Post says:

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,614 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: