Select serverproperty('propertyname')

SERVERPROPERTY ( 'propertyname' )
Property	Values returned
BuildClrVersion	Version of the Microsoft .NET Framework common language runtime (CLR) that was used while building the instance of SQL Server .
Collation	Name of the default collation for the server.
CollationID	ID of the SQL Server collation.
ComputerNamePhysicalNetBIOS	NetBIOS name of the local computer on which the instance of SQL Server is currently running. For a clustered instance of SQL Server on a failover cluster, this value changes as the instance of SQL Server fails over to other nodes in the failover cluster. On a stand-alone instance of SQL Server , this value remains constant and returns the same value as the MachineName property.
Edition	Installed product edition of the instance of SQL Server . Returns: 'Enterprise Edition', ‘Enterprise Edition: Core-based Licensing’ etc….
EditionID	EditionID represents the installed product edition of the instance of SQL Server . Use the value of this property to determine features and limits, such as Compute Capacity Limits by Edition of SQL Server. 1804890536 = Enterprise 1872460670 = Enterprise Edition: Core-based Licensing 610778273= Enterprise Evaluation 284895786 = Business Intelligence -2117995310 = Developer -1592396055 = Express -133711905= Express with Advanced Services -1534726760 = Standard 1293598313 = Web 1674378470 = SQL Database or SQL Data Warehouse
EngineEdition	Database Engine edition of the instance of SQL Server installed on the server. 1 = Personal or Desktop Engine (Not available in SQL Server 2005 and later versions.) 2 = Standard (This is returned for Standard, Web, and Business Intelligence.) 3 = Enterprise (This is returned for Evaluation, Developer, and both Enterprise editions.) 4 = Express (This is returned for Express, Express with Tools and Express with Advanced Services) 5 = SQL Database 6 - SQL Data Warehouse
HadrManagerStatus	Applies to: SQL Server 2012 through SQL Server 2016 . Indicates whether the Always On Availability Groups manager has started. 0 = Not started, pending communication. 1 = Started and running. 2 = Not started and failed. NULL = Input is not valid, an error, or not applicable.
InstanceDefaultDataPath	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Name of the default path to the instance data files.
InstanceDefaultLogPath	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Name of the default path to the instance log files.
InstanceName	Name of the instance to which the user is connected. Returns NULL if the instance name is the default instance, if the input is not valid, or error. NULL = Input is not valid, an error, or not applicable.
IsClustered	Server instance is configured in a failover cluster. 1 = Clustered. 0 = Not Clustered. NULL = Input is not valid, an error, or not applicable.
IsFullTextInstalled	1 = Full-text and semantic indexing components are installed. 0 = Full-text and semantic indexing components are not installed. NULL = Input is not valid, an error, or not applicable.
IsHadrEnabled	Applies to: SQL Server 2012 through SQL Server 2016 . Always On Availability Groups is enabled on this server instance. 0 = The Always On Availability Groups feature is disabled. 1 = The Always On Availability Groups feature is enabled. NULL = Input is not valid, an error, or not applicable.
IsIntegratedSecurityOnly	1 = Integrated security (Windows Authentication) 0 = Not integrated security. (Both Windows Authentication and SQL Server Authentication.)
IsLocalDB	Server is an instance of SQL Server Express LocalDB.
IsPolybaseInstalled	Returns whether the server instance has the PolyBase feature installed. 0 = PolyBase is not installed. 1 = PolyBase is installed.
IsSingleUser	Server is in single-user mode. 1 = Single user. 0 = Not single user
IsXTPSupported	Applies to: SQL Server ( SQL Server 2014 through SQL Server 2016 ), SQL Database . Server supports In-Memory OLTP. 1= Server supports In-Memory OLTP. 0= Server does not supports In-Memory OLTP.
MachineName	Windows computer name on which the server instance is running. For a clustered instance, an instance of SQL Server running on a virtual server on Microsoft Cluster Service, it returns the name of the virtual server.
ProcessID	Process ID of the SQL Server service. ProcessID is useful in identifying which Sqlservr.exe belongs to this instance.
ProductBuild	Applies to: SQL Server 2014 beginning October, 2015. The build number.
ProductBuildType	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Type of build of the current build. Returns one of the following: OD = On Demand release a specific customer. GDR = General Distribution Release released through windows update.
ProductLevel	Level of the version of the instance of SQL Server . Returns one of the following: 'RTM' = Original release version 'SPn' = Service pack version 'CTPn', = Community Technology Preview version
ProductMajorVersion	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The major version.
ProductMinorVersion	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. The minor version.
ProductUpdateLevel	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. Update level of the current build. CU indicates a cumulative update. Returns one of the following: CUn = Cumulative Update
ProductUpdateReference	Applies to: SQL Server 2012 through current version in updates beginning in late 2015. KB article for that release.
ProductVersion	Version of the instance of SQL Server , in the form of 'major.minor.build.revision'.
ResourceLastUpdateDateTime	Returns the date and time that the Resource database was last updated.
ResourceVersion	Returns the version Resource database.
ServerName	Both the Windows server and instance information associated with a specified instance of SQL Server .
FilestreamShareName	The name of the share used by FILESTREAM.

Difference between Suspect Mode and Recovery Pending

Whenever a database restarted it must go through the recovery process to bring it online.

If a database status is "Recovery Pending", then it mean recovery process could not start. It might be due to incorrect file path, access denied on the database file path for SQL Server services.

If a Database status is "Suspect", then it means recovery process started but it had problems in one of the recovery process stages(Analysis, Redo, Undo).

Cluster Setup Error

Recently I had to setup SQL Server 2012 cluster (Active-Passive) for my client.
We had given 2 physical boxes which are placed in 2 different DCs and storage has zonned so that storage can be used by both the boxes and fail over can happen without any issues between the two DCs.
Windows cluster build completed and handed over to me for SQL Server setup. This cluster was built with 2 cluster shared disks. I have started building SQL Server but out of 2 cluster disks only 1 disk was detected by SQL Server setup. I was with no clue what might be the problem. I checked with wintel person who built the windows cluster, but response is "Issue is with your SQL Server not from my side.". Again I started digging the problem and for its solution. After some research, I got an Idea to check the 2  cluster disk drives and properties, here I found one drive built with NTFS and other drive is built with ReiserFS file systems. The drive which built with NTFS file system detected by SQL Server setup but other drive which built with ReiserFS file system is not detected by SQL Server setup.

I have asked the wintel team to change the file system to NTFS. Once this change is done, SQL Server setup was able to detect both the disks.

After this in the next steps setup went smoothly but at the last step of setup encountered below error:

This error is due to insufficient permissions at OU level in AD and description is like below. 

The user credentials of the currently logged on user who is creating the Failover Cluster will be used to create the computer objects in Active Directory.  The user must have Create Computer Objects permissions to the OU to create the computer objects.

http://technet.microsoft.com/en-us/library/dn466519.aspx" 

For this issue I have contacted AD team to provide create object permissions at OU level in AD and AD team checked whether is there any computer object with SQL Cluster name which I have used for cluster setup, which is not found in the computer object list of AD.

After providing proper permissions I have started a fresh setup again but same error repeated. Again I was with no clue. Again contacted the respective team. Now they are able to find the SQL Cluster name in the computer objects of AD, but they are not sure about the repeated error.
I did checked the windows logs but nothing was found. So thought to check the cluster logs also.
When I checked the Cluster logs found below error:

Cluster network name resource 'SQL Network Name (Name of the SQL Cluster)' failed to create its associated computer object in domain 'Domain name' for the following reason: Resource online.

The associated error code is: -1073741790

Please work with your domain administrator to ensure that:

- The cluster identity 'Windows Cluster name' can create computer objects. By default all computer objects are created in the 'Computers' container; consult the domain administrator if this location has been changed.

- The quota for computer objects has not been reached.

- If there is an existing computer object, verify the Cluster Identity 'Windows Cluster name' has 'Full Control' permission to that computer object using the Active Directory Users and Computers tool.

This issue is because "Windows Cluster name" didn't have full access on "SQL Cluster name"  at AD Level.

Our domain guy has provided "Windows Cluster name"  full access on "SQL Cluster name" computer object at AD Level. Then I can able to bring the SQL Server resources online and failover also from fail-over cluster manager. I am happy. Cluster setup is the one task which comes rarely to most of the DBAs and everybody want to get the exposure to it. I got the chance and that too with some learning. Before also I did setup the SQL cluster on previous versions. I think we had the enough permissions at that moment so we were able to set it up without any issues. But now I think I did not provided enough privileges which made me to learn a new thing(at least new to me).

So here I had 3 issues while Building the SQL Cluster.

1. Disks are not built with NTFS file system.

2. I was not having the create objects permissions at OU Level in AD.

3.  Didn't had full control for cluster 'Windows Cluster name’ on SQL Cluster name.

After working on all these hurdles I was able to build the SQL Server cluster without any issues.