SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

The default instance has a name

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.


Filed under: Microsoft SQL Server, SQLServerPedia Syndication Tagged: alias, connection problems, microsoft sql server

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...