11

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.

migrated from stackoverflow.com Jan 31 '14 at 19:54

This question came from our site for professional and enthusiast programmers.

10

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: http://blogs.msdn.com/b/petersad/archive/2009/12/17/sql-server-2008-r2-sysprep-step-by-step.aspx

  • For named instances, the instance id is also used in the Name and DisplayName of the services. – Greenstone Walker Feb 2 '14 at 21:02
3

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.

  • 2
    Well that is about a clear as mud. – ThomasMcLeod May 3 '17 at 15:58
0

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.

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

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

Instance ID is like MSSQLnn.SQL2016_64ENT

  • You can't use underscores in the Instance Name. – HackSlash Aug 17 '18 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.