Tag Archives: Named Instances

Directory naming convention in SQL Server

Posted on by
Reply

In this blog we shall take a look at the naming convention of SQL Server Instance directories since SQL Server 2005. By default, SQL Server is installed in C:\Program Files\Microsoft SQL Server directory. The Instance directory is then created depending on the SQL Server Version that is installed. For each SQL Instance installed, there would be a separate directory for that Instance, all the binary files of that instance are stored in that directory.

In SQL Server 2005, the instance directories are named as MSSQL.1, MSSQL.2, MSSQL.3 and so on as new Instances are installed. This is inconvenience to find the instances by looking at the naming of these directories. See below that there are 2 directories MSSQL.1and MSSQL.2 one of them being a default instance and the other one is obviously a named instance.

Where as starting from SQL Server 2008, the naming of the directories has been changed to reflect the Instance they belong to. Instance directory names in SQL 2008 start with MSSQL10. followed by the instance name. See that there are 2 directories named MSSQL10.SQL2008INSTANCE1 and MSSQL10.SQL2008INSTANCE2. 

This naming convention has continued in the next releases of SQL Server, such as SQL 2008 R2 (which is often referred as 10.5) hence the naming is MSSQL10_5.xyz and finally in SQL Server 2012, the Instance directory is named as MSSQL11.xyz…

Finally this is how the SQL Server configuration Manager looks like on the server. There are total of 6 SQL Server Instances as you can see.

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

— Bru Medishetty

Connecting to Named and Default Instances in SQL Server

Posted on by
Reply

You might be aware that SQL Server supports multiple instances on a single machine and a there can be one default instance on any machine. When working with a default instance of a SQL Server you can connect to it by using just the name of the Windows Server where it is installed on. Whereas the when you have to connect to a named instance of SQL Server, you have to connect using the convention Servername\Instancename.

Let us look at this with examples.

Connecting to Default Instance:

When connecting to a default instance of SQL Server, you just have to enter the machine name where the SQL Server is installed. In the below example, I am connecting to a SQL Server 2005 default instance on a Server named SQLSVR2005VM.

Connecting to a named Instance:

Connecting to a named instance, you have to specify the machinename followed by a back slash “\” followed by the instance name. In this example (picture below), we are going to connect to a named instance called SQLTESTINSTANCE on the same machine SQLSVR2005VM, hence we need to type SQLSVR2005VM\SQLTESTINSTANCE to connect to the named instance.

Note: It is possible to configure the port number of a named instance to be 1433 in order to be able to access similar to default instance (without the back slash), but that case is not considered for this blog post. 

Do you like this site? Like our FB page @ Facebook.com\LearnSQLWithBru so that, you know when there is a new blog post.

–Bru Medishetty