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')