How to find out important instance level information from DMV/Query?


Please use below code:


Comments in Green explains the outcome of the code.
Please visit https://msdn.microsoft.com/en-us/library/cc645956.aspx for more information.

-- @@SERVERNAME provides the currently configured local server name.
select SERVERPROPERTY ('ServerName') as 'Server Name'

-- What is the machine name? (Windows computer name on which the server instance is running.)
select serverproperty ('MachineName') as 'Machine Name'

-- What is the machine NETBIOS Name? (Note: NetBIOS name of the local computer on which the instance of SQL Server is currently running.)
select serverproperty ('ComputerNamePhysicalNetBIOS') as 'NETBIOS'

-- Process ID of SQL Server service
select serverproperty ('ProcessID') as 'Process ID'

-- Information around Resource Database
select SERVERPROPERTY ('Resourceversion') as 'Resource DB Version',  SERVERPROPERTY ('ResourceLastUpdateDateTime') as 'Last date and time that the Resource database was last updated.'

-- Is the instance clustered? (1 For Clustered, 0 for Non Clustered)
select serverproperty ('isClustered') as 'Clustered or Not'

-- Is the instance a Local DB?
select serverproperty ('IsLocalDB') as '1 = Yes, 0 = No'

-- Does server support in memory OLTP?
select serverproperty ('IsXTPSupported') as '1 = Yes, 0 = No'

-- Does server support Always On?
select serverproperty ('IsHADREnabled') as '1 = Enabled, 0 = Not Enabled, Null = Not Applicable'

-- Status of Always On?
select serverproperty ('HadrManagerStatus') as '1 = Started & Running, 2 = Not Started & Failed, 0 = Not Started & Pending Communication,  Null = Not Applicable'

-- Default data path
select serverproperty ('InstanceDefaultDataPath')

-- Default log path
select serverproperty ('InstanceDefaultLogPath')

-- Full text related information
select serverproperty ('IsFullTextInstalled') as '1 = Yes, 0 = No, Null = Not Applicable'

-- Authentication Mode
select serverproperty ('IntegratedSecurityOnly') as '1 = Integrated security (Windows Authentication), 2 = Mixed Mode, Null = Not Applicable'

-- Filestram related details (0 = Disabled, 1 = Enabled for TSQL access, 2 = Enables FILESTREAM for Transact-SQL and Win32 streaming access)

select SERVERPROPERTY ('FilestreamConfiguredLevel'), SERVERPROPERTY ('FilestreamEffectiveLevel'), SERVERPROPERTY ('FilestreamShareName')

Comments