Tag Archives: Multiple Instances

Things to know when Installing SQL Server 2008 R2 side by side with SQL Server 2008

Posted on by
1

If you are planning to install SQL Server 2008 R2 as an additional Instance on a machine where SQL Server 2008 exists, then you have to keep in mind some implications. Though nothing fatal might occur in the first place, but there would be some changes depending on what settings / features that are selected while Installing SQL Server 2008 R2. 

When installing the first instance of 2008 R2, you would come across the below warning message. The message indicates that the Shared Components will be upgraded to SQL Server 2008 R2.

What this means to you is, when you are done performing the installation, the shared components of SQL Server 2008 will not be available any more. Some of the shared components include.. SSMS, BIDS, Profiler, Integration Services, SQL Server Browser etc..

To avoid this scenario, the way out is not to choose Shared Components in the Installation step where you select the features list, that way SQL Server 2008 R2 instance will be installed but the shared components will still be of version SQL Server 2008.

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

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

Rules to follow while naming a SQL Server Instance

Posted on by
6

When planning to install SQL Server on a server, you need to plan ahead about the name of the SQL Server Instance. If you are installing a default instance then this is not necessary. There are certain rules you will have to follow when naming your SQL Instance and lets see what are some of the acceptable names and some non acceptable names for a SQL Server Instance.

Lets start with those names which are not accepted along with some examples….

Reserved Keywords in Microsoft SQL Server are not accepted. You cannot name a SQL Instance as DATABASE or ALTER or CREATE or SCHEMA (of course Capitals Letters does not matter). See the below pic when I try to name the SQL Instance as DATABASE..

First character should not be a numerical value (0-9), it can be an alphabet (a-z), underscore ‘_’, number sign ‘#’, or ampersand ‘&’. If you try naming a SQL Instance as ‘1SQLServer’, it would not allow you to name with that name. See pic below..

Space and special characters (such as @, ^, *, \ ) are not allowed. That is, if you try naming the instance as “SQL TEST 2”, it is not accepted.

Instance name should be 16 chars or less in length. This is pretty easy to understand, if you want to name the Instance too long, then you have to think again..

As long as they are less than 17 characters, some of the acceptable names can be..

  • SQLServer123
  • SQL_Server_123
  • SQL_Server#123
  • SQL$Server123
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