SQL Server - difference between instance name and instance id

Question

I'm a bit confused with SQL Server instances. The Instance Name and Instance ID are different properties, yet Instance ID tends to be defaulted to a capitalized version of the Instance Name. The installation allows you to set a different value for the ID than the Name, although typically they're the same.

Obviously, ID refers to internal reference and Name refers to user-friendly reference. But what are the fundamental differences between the two? When should one be used rather than the other?

I need to understand this while deploying / installing SQL Server 2008 R2 Express on our clients' server machines from a custom installation process.

Answer 1

By default, the instance name is used as the Instance ID. Instance ID is used to identify installation directories and registry keys for your instance of SQL Server. This is the case for default instances and named instances

(source: http://msdn.microsoft.com/en-us/library/ms143531(v=sql.120).aspx )

Install SQL 2008 r2 steps: Link

• Greenstone Walker: For named instances, the instance id is also used in the Name and DisplayName of the services.

Answer 2

I am not even a beginner but it's all in the references provided to you Jerry Dodge.

INSTANCE NAME = Is attached to a SERVER NAME just like the way it's stated. When you try to connect to an SQL Database you need the server to be able to get in. It's all about the networking and properties of your Physical or Virtual machine hosting your DATABASE.

INSTANCE ID = Is attached to SQL Instance Name. Once you provided the instance name which is the PATH to your SERVER NAME then you need the INSTANCE ID to be able to get into your programs and DATABASE path and all other software licensing requirements to run.

One is Physical or Virtual Computer and Network and the other is physical or logical path to the database storage.

• ThomasMcLeod: Well that is about a clear as mud.

Answer 3

This will help you to understand the differences.

[http://msdn.microsoft.com/en-us/library/ms143531(v=sql.120).aspx][1]

 USE master;
SELECT SERVERPROPERTY('BuildClrVersion') AS [BuildClrVersion]
, SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS ComputerNamePhysicalNetBIOS
, SERVERPROPERTY('ServerName') AS [ServerName]
, SERVERPROPERTY('InstanceName') AS [InstanceName]
, SERVERPROPERTY('Collation') AS [Collation]
, SERVERPROPERTY('ComparisonStyle') AS [ComparisonStyle]
, SERVERPROPERTY('Edition') AS [Edition]
, SERVERPROPERTY('EngineEdition') AS [EngineEdition]
, SERVERPROPERTY('IsClustered') AS [IsClustered]
, SERVERPROPERTY('IsFullTextInstalled') AS [IsFullTextInstalled]
, SERVERPROPERTY('IsIntegratedSecurityOnly') AS [IsIntegratedSecurityOnly]
, SERVERPROPERTY('IsSingleUser') AS [IsSingleUser]
, SERVERPROPERTY('LCID') AS [LCID]
, SERVERPROPERTY('LicenseType') AS [LicenseType]
, SERVERPROPERTY('MachineName') AS [MachineName]
, SERVERPROPERTY('NumLicenses') AS [NumLicenses]
, SERVERPROPERTY('ProcessID') AS [ProcessID]
, SERVERPROPERTY('ProductVersion') AS [ProductVersion]
, SERVERPROPERTY('ProductLevel') AS [ProductLevel]
, SERVERPROPERTY('ResourceLastUpdateDateTime') AS [ResourceLastUpdateDateTime]
, SERVERPROPERTY('ResourceVersion') AS [ResourceVersion]
, SERVERPROPERTY('SqlCharSetName') AS [SqlCharSetName]
, SERVERPROPERTY('SqlSortOrder') AS [SqlSortOrder]
, SERVERPROPERTY('SqlSortOrderName') AS [SqlSortOrderName];
GO

EDIT:

Instance Name : The name of the SQL Server- DEFAULT INSTANCE , Name INSTANCE( you give a name for the particular instance)

InstanceID: This is related to Instance Name. For Eg: when you press Default Instance than it will be SQLSERVER generated field instanceid or your name generated instanceid.

• Szymon: This is a query that doesn't explain anything so it makes it a link-only answer. -1.
• Jerry Dodge: Huh? How does this help me understand anything other than my SQL Server instance? Which doesn't even show the instance name?
• Jerry Dodge: I mean, it shows the capitalized version of the instance name, but not anything which demonstrates the differences.
• Maverick: Did you check the link that i provided? The query is just to see what is in your system.
• Jerry Dodge: Yes, it still doesn't explain the fundamental differences though.

Answer 4

Instance Name is like SQL20mm_64ENT e.g. you're installing PC_102\SQL20mm_64ENT

Instance ID is like MSSQLnn.SQL2016_64ENT

• HackSlash: You can't use underscores in the Instance Name.