How to get SQL Server instance information

As known, the Microsoft SQL Server is a relational database management system with the function of storing and retrieving data requested by other corresponding software applications.

Knowing the configuration of a SQL Server is key to determine its edition, the sum of the memory that is being allocated for that instance, build number, and many more of the valuable information.

Let’s now consider one common situation in which there is a SQL Server DBA team that supports and maintains 100+ SQL Server instances, ranging from the SQL Server 2005 up to the latest SQL Server 2019 and all with different editions, service packs, and authentication modes. And what if they’ve been tasked with the critical mission to generate a report in Word, Excel, or PDF to show the details of basic information, configuration settings, and server objects of those instances.

This feat can be accomplished in various ways by using the SQL Server Management Solution (SSMS), but this approach can be exhausting and overwhelming since there is no way to access all valuable information in one place.

Having the option to view all SQL Server instance details at one easily accessible place without hassling with enormous T-SQL scripts or an endless stream of “button clicking” is the desired goal for any DBA.

The following article will shed some light on a few ways SQL instance details could be extracted within the SSMS and then provide an insight into a simpler and more productive alternative by using the 3^rd party software.

View instance details using SSMS

Once the connection to the desired SQL Server is made, let’s move on to seeing its basic information. To accomplish this, right-click on the instance name and from the menu choose Properties:

Under the General tab of the Server Properties window, some basic SQL Server Instance information is displayed:

• Product – the name of the product and its bit version
• Operating system – information about the operating system the instance is installed on
• Platform – the platform product belongs to
• Version – version of the product
• Language – the language of the product
• Memory – memory allocated to the server
• Processors – number of processors allocated to the server
• Root directory – the server’s installation folder
• Server collation – the name of the default collation for the server
• Is Clustered – shows if server instance is configured in a failover cluster
• Is XTP Supported – shows if server instance supports In-Memory OLTP
• Is HADR Enabled – shows if Always On availability groups is enabled on the SQL Server instance

In the Advanced tab a few more information regarding a server instance can be found:

• Containment – shows if the server instance is having contained databases, i.e., ones that are isolated from the instance itself
• FILESTREAM – a SQL Server feature that stores data in the file system
• Miscellaneous – some miscellaneous information related to the instance
• Network – displays configuration settings related to the Network Packet Size and the Remote Login timeout
• Parallelism – a SQL Server feature that divides a big task into smaller tasks to increase speed and performance

Get SQL Server information using system views

To pull SQL instance information by using system views, please refer to the Discovering SQL server instance information using system views article.

Get SQL Server information using T-SQL scripts

The following are few examples of using T-SQL scripts to get some information regarding a SQL instance.

Example 1

The following script will pull some basic information that can also be found in the General tab of the Server Properties window in SQL Server Management Studio:

USE master
GO

EXEC xp_msver "ProductName"
  ,"ProductVersion"
  ,"Language"
  ,"Platform"
  ,"WindowsVersion"
  ,"PhysicalMemory"
  ,"ProcessorCount"
GO

The output of this script is:

Example 2

The following T-SQL script will pull the SQL Server Instance information by executing stored procedures:

DECLARE @version VARCHAR(4)

SELECT @version = substring(@@version, 22, 4)

IF CONVERT(SMALLINT, @version) >= 2012
  EXEC (
      'SELECT 
    SERVERPROPERTY(''ServerName'') AS [Instance Name],
    CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4) 
      WHEN ''11.0'' THEN ''SQL Server 2012''
      WHEN ''12.0'' THEN ''SQL Server 2014''
      ELSE ''Newer than SQL Server 2014''
    END AS [Version Build],
    SERVERPROPERTY (''Edition'') AS [Edition],
    SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
    CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'') 
      WHEN 0 THEN ''SQL Server and Windows Authentication mode''
      WHEN 1 THEN ''Windows Authentication mode''
    END AS [Server Authentication],
    CASE SERVERPROPERTY(''IsClustered'') 
      WHEN 0 THEN ''False''
      WHEN 1 THEN ''True''
    END AS [Is Clustered?],
    SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
    SERVERPROPERTY(''Collation'') AS [ SQL Collation],
    [cpu_count] AS [CPUs],
    [physical_memory_kb]/1024 AS [RAM (MB)]
  FROM  
    [sys].[dm_os_sys_info]'
      )
ELSE IF CONVERT(SMALLINT, @version) >= 2005
  EXEC (
      'SELECT 
    SERVERPROPERTY(''ServerName'') AS [Instance Name],
    CASE LEFT(CONVERT(VARCHAR, SERVERPROPERTY(''ProductVersion'')),4) 
      WHEN ''9.00'' THEN ''SQL Server 2005''
      WHEN ''10.0'' THEN ''SQL Server 2008''
      WHEN ''10.5'' THEN ''SQL Server 2008 R2''
    END AS [Version Build],
    SERVERPROPERTY (''Edition'') AS [Edition],
    SERVERPROPERTY(''ProductLevel'') AS [Service Pack],
    CASE SERVERPROPERTY(''IsIntegratedSecurityOnly'') 
      WHEN 0 THEN ''SQL Server and Windows Authentication mode''
      WHEN 1 THEN ''Windows Authentication mode''
    END AS [Server Authentication],
    CASE SERVERPROPERTY(''IsClustered'') 
      WHEN 0 THEN ''False''
      WHEN 1 THEN ''True''
    END AS [Is Clustered?],
    SERVERPROPERTY(''ComputerNamePhysicalNetBIOS'') AS [Current Node Name],
    SERVERPROPERTY(''Collation'') AS [ SQL Collation],
    [cpu_count] AS [CPUs],
    [physical_memory_in_bytes]/1048576 AS [RAM (MB)]
  FROM  
    [sys].[dm_os_sys_info]'
      )
ELSE
  SELECT 'This SQL Server instance is running SQL Server 2000 or lower! You will need alternative methods in getting the SQL instance level information.'

The result of this script:

Can this be simpler?

In short, yes.

Let’s be honest, no one wants to hassle all day long with enormous T-SQL scripts to get some basic server information, especially when there is another solution, a different approach.

You don’t need to type anything, no T-SQL scripts are needed, or required cause all SQL Server instance information is just one click away and all mentioned info, and even more, is stored in one easily accessible place.

3^rd party solution

Below is a look into a process of viewing SQL instance information by using a 3^rd party SQL manage instance tool, ApexSQL Manage.

Before initializing the mentioned process, one first needs to add SQL instance in the SQL manage instance tool.

There are numerous different ways to add SQL instance in the SQL Manage instance tool.

To choose the one that suits your needs the best, please consult the following guide: Different ways of adding SQL Server instances in a SQL manage instance software

Once an instance is added, it will be shown in the main grid of the Inventory tab. Select it and click the Instance button:

All the SQL Server information is now easily accessible on the left side of the application window and is separated into two groups:

• Server
• Server objects

Simply pick your preferred category on the left, and its details will be shown on the right.

For a detailed guide on what each of the categories represents and what information it carries, please check the How to view SQL instance details in ApexSQL Manage article.

Conclusion

When in a rush to quickly pull all the accessible SQL Server instance information, there is no need to execute enormous T-SQL scripts or trying to find some basic information that is buried somewhere into SSMS property windows, especially when those don’t return all that is required.

This valuable information is stored in one centralized place within the SQL manage instance tool and can be easily accessed with a minimal amount of effort.