Get SQL Server TCP Connections from Operating System Level

Problem

Our systems (server) administrator who got local administrator rights to our SQL Server wanted to get the remote TCP connections on SQL Server instance.

This can be done by connecting to the instance and use the Dynamic Mangement View (DMV) called dm_exec_connections to query the information. This will also require us to give VIEW SERVER STATE permission to the user. We want to give "just enough" permission to the user that needs it and by providing VIEW SERVER STATE you give the user permission to all DMVs.

Solution

Knowing that the server admins already have local administrator rights to our servers, this solution can be achieved without giving them access to the database instance.

This solution can be achieved in 3 steps using PowerShell:

1. Get all the process that are running in the server using Get-Process and filter all the SQL Server related processes.

PowerShell

Get-Process -ComputerName $servername | Where-Object {$_.ProcessName -in ('smss','SQLAGENT','sqlceip','sqlservr','sqlwriter')}

2. Get all the TCP connection of the processes that were returned on step 1 using Get-NetTCPConnection. You need to relate the Id from Get-Process with OwningProcess from Get-NetTCPConnection. This will return the IP addresses of the remote TCP connections. 

PowerShell

Get-NetTCPConnection -OwningProcess "$SQLServerProcessID"

3. Get hostname of the remote TCP connections returned on step 2 by using Resolve-DnsName. 

PowerShell

Resolve-DnsName "$TCPRemoteAddress"

Now you can combine all these steps to create a function that will return SQL Server's remote TCP connection without accessing the database instance.

You can download the function Get-SQLServerTCPConnectionsOSLevel from github.

Format-Volume Bug or Feature?

Problem

We just recently added an additional node to our existing Availability Group cluster. 
Our team uses a PowerShell script to easily format multiple drives in a specific allocation unit size.

The issue happened when we formatted the drive on the new node. It is supposed to only format the drive on the server where it was ran. But for some reason, it also formatted the drive on the existing nodes.

This wiped out the drives on the existing nodes. The existing system went down and we have to restore everything from our backup.

We have to do a RCA (Root Cause Analysis) because it affected our production system.

Root Cause Analysis

We have to build a similar environment to simulate the issue.

To cut the story short, we were able to pin point the issue on Format-Volume command in PowerShell.

This particular issue only affects servers that are part of Windows cluster and have same drive letters on each node.

For SQL Server Always On:

• This does NOT affect Failover Cluster Instance (FCI) since it is using shared storage.
• This only affects the Availability Group configuration because each node have it's own storage and they can have same drive letters.

As a test we ran the Format-Volume to format the Z drive on node1 (of a 3 node cluster).

It formatted the Z drive on of all the nodes instead of just formatting the drive on node1.

Solution

Open a case with Microsoft to check if this is a bug or if there is a different method that we can do to run the Format-Volume only on the node where it is executed. (At the time of this writing, we have not heard back from Microsoft.)

As a precautionary measure for our team, we only use this feature on the following situation:

• Standalone servers
• Always On Failover Cluster Instance servers
• New server builds of Always On Availability Group

Get SQL Server Windows Cluster Information

Problem

I have already identified that there is a SQL Server installed on a server. Using PowerShell, I wanted to know if the server is using windows clustered WITHOUT connecting the database instance. 

This is an option that system administrators since they have admin rights on the Operating System but don't have access to the SQL Server instance.

I need to know the following:

1. I need to know if the server is clustered or not.

2. If the server is clustered, I need to know why type of clustering is configured on the server. 

    SQL Server have 2 types of Windows Clustering:

• Failover Cluster Instance
• Availability Group

    You can also combine Failover Cluster Instance with Availability Group.

    I also wanted to know if this type of configuration is configured on the server.

3. If the server is clustered, I also need to know if the server is active or passive node

Solution

One of the requirements to be able to get windows clustering information is to ensure that you have FailoverClusters PowerShell module installed on the machine that you are using to run the PowerShell commands that are specified below.

Let us step through the requirements:

1. How to check if the server is clustered or not?

    Using HKEY_LOCAL_MACHINE check if there is a "Cluster" entry in the registry

PowerShell

Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\Cluster}

    To check a server remotely you need to use Invoke-Command

PowerShell

Invoke-Command -Computer $servername -ScriptBlock {Get-ItemProperty -Path Registry::HKEY_LOCAL_MACHINE\Cluster}

    If no value is returned, the SQL Server is standalone.

    If the command returns a value, then SQL Server is clustered using windows clustering.

2. How to check what type of clustering is configured on the server?
    This is where the FailoverClusters module will come-in handy.
    We need to get/check the following:
    a. Check WMIObject MSCluster_ResourceGroup for root\mscluster namespace
    b. Get-Cluster information of the server
    c. Get-ClusterResource information of the server

    Check root\mscluster namespace

PowerShell

Get-WMIObject -Class MSCluster_ResourceGroup -Namespace root\mscluster}

    To check a server remotely you need to use Invoke-Command

PowerShell

Invoke-Command -Computer $servername -ScriptBlock {Get-WMIObject -Class MSCluster_ResourceGroup -Namespace root\mscluster}

    Get the cluster and cluster resource information of the server

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource

    If both root\mscluster namespace and cluster resource does not return a value then the server is configured as a "single node" cluster.

    If both root\mscluster namespace and cluster resource returns a value, we need to figure out what type of SQL Server windows clustering is configured.

3. How to check if "Availability Group" is configured?
    Using the cluster resource information, check if there is a resource type called "SQL Server Availability Group".

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object ResourceType -eq 'SQL Server Availability Group'

    If no results were returned then, the server is configured with "Failover Cluster Instance".

    If one result is returned, the server is configured with "Availability Group/Listener".
    If more than one result is returned, the server is configured with multiple "Availability Group/Listener".

4. How to check if "FCI with Availability Group" is configured?
    First you need to make sure that the server is configured with "Availability Group". 
    Using the cluster resource information, check if there is a resource type called "SQL Server Availability Group" or resource type called "Network Name" and resource name that starts with "SQL Network Name".

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object { (($_.ResourceType -eq 'Network Name') -and ($_.Name -Like 'SQL Network Name *')) -or ($_.ResourceType -eq 'SQL Server Availability Group')  }

    If 2 different resource types are returned, the server is configured with "FCI with Availability Group".

    NOTE: Ensure that you filter the uniqueness of the returned resource type value.

5. How to check if the server is the active or passive node?
    Using the cluster resource information, get the OwnerNode for resource type called "SQL Server".

PowerShell

Get-Cluster -Name $servername | Get-ClusterResource | Where-Object ResourceType -eq 'SQL Server' | Select-Object OwnerNode

    If the returned OwnerNode value is the same as the $servername, then it is the "active" node, otherwise it is the passive node.

Now you can combine all of the commands above to create a function that will return the SQL Server Windows Cluster Information.

You can now download the Get-SQLServer-WindowsClusterInfo function from github.

Create External Data Source to Reference a Readable Secondary Replica of Always On Availability Group

Problem

We need to create an external table that uses and external data source. The external data source needs to query the (read_only) readable secondary node of an availability group.

Pretty simple, just follow the Microsoft document:

CREATE EXTERNAL DATA SOURCE (Transact-SQL) - SQL Server | Microsoft Docs

There are several requirements:

1. The external data source needs to be configured as AlwayOn Availability Group with Read Only Routing List configured.  Configure read-only routing for an availability group - SQL Server Always On | Microsoft Docs
2. CONNECTION_OPTIONS argument for External Data Source is only available on SQL Server 2019.
3. Declare the Application_Intent=ReadOnly on CONNECTION_OPTIONS
4. Run a query on external table and execute it multiple times. This will give you time to check where the session is being executed. 
5. Verify the connection on both primary and secondary server. The session should be running in the secondary server.

Solution

First Try:

1. Configured/created the external data source

CREATE EXTERNAL DATA SOURCE [XDSName]

WITH (LOCATION=n'sqlserver://AGListenerName',

CONNECTION_OPTIONS='ApplicationIntent=ReadOnly',

CREDENTIAL=[dbuser])

GO

2. Configured/created external data table

CREATE EXTERNAL TABLE [XTable] ([column1] INT not null...)
WITH (LOCATION=N'[dbname].[schemaname].[tablename]',

DATA_SOURCE=[XDSName])

GO

3. Run a query against the external data table and repeated it 1000 times

SELECT * FROM [XTable]

GO 1000

4. Verify both the primary and secondary server. The query should run in secondary server.

 

With my first test, the query ran in the primary server. 

After doing some investigation, I found out that the "default database" of the credential that I used is pointed to the "master" database.

I modified "default database" of the credential I used to point to database name that is being replicated by the Availability Group. I tested again by running a query on the external data table multiple time, this time the session is running on the secondary node.

But we are not allowed to change the "default database" of the user.

The "default database" was changed back to "master"

Second Try:

1. Configured/created the external data source, but this time the database needs to be declared.

CREATE EXTERNAL DATA SOURCE [XDSName]

WITH (LOCATION=n'sqlserver://AGListenerName',

CONNECTION_OPTIONS='ApplicationIntent=ReadOnly; Database=dbname',

CREDENTIAL=[dbuser])

GO

2. Configured/created external data table

3. Run a query against the external data table and repeated it 1000 times

4. Verify both the primary and secondary server. The query should run in secondary server.

The initial test failed because I used a wrong keyword "DatabaseName". 

A co-worker of mine gave me a sample of his external data source that he has configured and already working against a secondary server.

After comparing the 2 definition, I found out that the keyword should be "Database" and not "DatabaseName"

I tested again and it worked because the "database" keyword is defined in the CONNECTION_OPTIONS which override the "default database" of the credential.

Allowed connection string keywords that can be used on CONNECTION_OPTIONS

Using connection string keywords - SQL Server Native Client | Microsoft Docs

Since there is no example on Microsoft's document, I submitted a pull request and added an example G. Create external data source to reference a readable secondary replica of Always On Availability Group

SQL Server Agent Job - $RawUI.CursorPosition Error

Problem

I had this issue a long time ago, but did not have time to write about this.

I am trying to run some PowerShell script to cleanup old backup files in the database server using SQL Server Agent job.

But when I tried running the job, I got this error below.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

Solution

Let's walk through my initial troubleshooting steps:

1. I read the first 2 sentences and it seems to be obvious that the error is in line 3.

    Line 3 of my code just gets the current date and puts it in a variable:

    

    $currdate = Get-Date

    It seems straight forward, I ran it in Powershell and it runs fine.

    I isolated the code by adding Write-Output to display the value right after getting the date.

Write-Output "Current Date: $currdate"

    I also deleted the rest of my code after the Write-Output since the error is just in line 3.

    

2. I ran the job again and I still got the same error.

    Now I tried to read and understand the error and read the rest of the error message.

    I tried to understand what each sentence.

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script.

The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. 

Correct the script and reschedule the job. 

The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  

The step failed.    

    Looking at the code, there is no command that seems to be prompting a user and waiting for user interaction.

    The only command that seems to be interacting with the PowerShell Console or ISE is Write-Output which I added in step 1.

3. I tried running the job again with 3 the job again and I still got the same error on line 3.

    I decided to just remove the Get-Date code, just for the heck of it.

4. I ran the SQL Server Agent job with just "clear" on the code. 

clear

    I still got the same error:

Executed as user: <sql server agent account>. A job step received an error at line 3 in a PowerShell script. The corresponding line is '$RawUI.CursorPosition = @{X=0;Y=0}  '. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Exception setting "CursorPosition": "A command that prompts the user failed because the host program or the command type does not support user interaction. Try a host program that supports user interaction, such as the Windows PowerShell Console or Windows PowerShell ISE, and remove prompt-related commands from command types that do not support user interaction, such as Windows PowerShell workflows."  '.  Process Exit Code -1.  The step failed.

    There is no line 3 in the code but it is still telling me that the error is in line 3.

    

    This was just frustrating because the error message is not giving the right information.

    The "clear" command is not prompting a user, but it is somewhat interacting with the console and ISE.

    The command "clear" is and alias for "Clear-Host". Another alias for "Clear-Host is "cls". 

5. I removed the "clear" command and tested again the Get-Date and Write-Output code, and it ran without error.

$currdate = Get-Date

Write-Output "Current Date: $currdate" 

6. I added back the rest of my code to complete the SQL Agent job that will clean old backup files.

Lesson's learned: Don't forget to remove the "Clear-Host", "clear" or "cls" when running your PowerShell commands in SQL Server Agent job.

Get SQL Server Cluster Information - Stored Procedure

Problem

I always need to get cluster information of my server whenever I am investigating a database server. 

I tried searching in internet as someone might have already posted something similar but could not find one. 

I decided to create a stored procedure so that I can deploy it on all of my servers and be able to retrieve the cluster information quicker.

Below are cluster information that I need:

1. Instance Name?

2. Server (Cluster Node) Name/s

3. Is it Standalone or Clustered?

4. If Clustered, is it using Always On Availability Group (AG), Failover Cluster Instance (FCI) or combination of FCI and AG?

5. If Clustered, which node is Active/Primary and Passive/Secondary

6. If using Availability Group, what is the Availability Group Name and Listener Name being used?

7. What is the SQL Server version and patch level? are the patch level the same on all nodes?

8. Lastly, I need to make sure it works on older versions of SQL Server. I have tested this in SQL Server 2008 R2 and higher (SQL Server 2019 is the latest version at the time of this writing).

Solution

I will try to explain a few sections of the script that I used to return some of the requirements above.

Check SQL Server version:

Below is the query I used to check the SQL Server version, if value is less than 11, the version is below SQL Server 2012:

SELECT @SQLServerMajorBuildVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR))-1)

The sys.dm_os_cluster_nodes table for SQL Server 2008 R2 does not contain the is_current_owner column. The query to determine the primary node is different.

Check if the Database Instance is Clustered:

Below is the query I used to check if the the database server is clustered:

To check if failover cluster instance is being used, check this server property if the value is 1:

SELECT SERVERPROPERTY('IsClustered')

To check if availability group is being used, check this server property if the value is 1:

SELECT SERVERPROPERTY('IsHadrEnabled')

If both property values are 1, the database server is configure to use both failover cluster instance and availability group.

SELECT SERVERPROPERTY('IsHadrEnabled')) = 1 AND (SELECT SERVERPROPERTY('IsClustered')) = 1

Check Primary Node for Failover Cluster Instance:

I queried the sys.dm_os_cluster_nodes to get the nodes that are included in the cluster.

For SQL Server 2008 R2, I used this case statement:

CASE UPPER(NodeName)

    WHEN UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) THEN 'PRIMARY'

    ELSE 'SECONDARY'

END AS HighAvailabilityRoleDesc

For SQL Server 2012 and higher, I used this case statement:

CASE is_current_owner 

    WHEN 1 THEN 'PRIMARY'

    ELSE 'SECONDARY'

END AS HighAvailabilityRoleDesc

Check Primary Node for Availability Group:

To get the primary node for Availability Group, you need to use the sys.dm_hadr_availability_group_states table and the primary_replica column. I have to join this with the sys.availability_replicas table and use a case statement to hardcode the values as PRIMARY or SECONDARY if I wanted it to run on any nodes.

The sys.dm_hadr_availability_replica_states table only returns multiple values when being queried in the primary server but only returns one row when querying the secondary node. Microsoft have stated this as an important information in their documentation.

To obtain information about every replica in a given availability group, query sys.dm_hadr_availability_replica_states on the server instance that is hosting the primary replica. When queried on a server instance that is hosting a secondary replica of an availability group, this dynamic management view returns only local information for the availability group.

SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName

,UPPER(ar.replica_server_name) AS ServerName

,SERVERPROPERTY('IsClustered') AS IsClustered

,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

,ag.name AS AvailabilityGroupName

,agl.dns_name AS AvailabilityGroupListenerName

,CASE ar.replica_server_name WHEN hags.primary_replica THEN 'PRIMARY'

ELSE 'SECONDARY'

END AS HighAvailabilityRoleDesc

,'AG' AS SQLServerClusteringMethod

,@@VERSION AS SQLServerVersion

FROM sys.availability_groups AS ag

LEFT OUTER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id

LEFT OUTER JOIN sys.availability_group_listeners AS agl ON ag.group_id = agl.group_id

LEFT OUTER JOIN sys.dm_hadr_availability_group_states as hags ON ag.group_id = hags.group_id

ORDER BY 5,2; 

Below is the entire code of the stored procedure:

SET ANSI_NULLS ON;

SET ANSI_PADDING ON;

SET ANSI_WARNINGS ON;

SET ARITHABORT ON;

SET CONCAT_NULL_YIELDS_NULL ON;

SET QUOTED_IDENTIFIER ON;

SET STATISTICS IO OFF;

SET STATISTICS TIME OFF;

GO

IF OBJECT_ID('dbo.sp_ClusterInfo') IS NULL

  EXEC ('CREATE PROCEDURE dbo.sp_ClusterInfo AS RETURN 0;');

GO

ALTER PROCEDURE dbo.sp_ClusterInfo

AS

SET NOCOUNT ON;

SET STATISTICS XML OFF;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @SQLServerMajorBuildVersion AS INT 

SELECT @SQLServerMajorBuildVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR), CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR))-1)

IF @SQLServerMajorBuildVersion < 11

BEGIN

       IF (SELECT SERVERPROPERTY('IsClustered')) = 1

       BEGIN

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

              ,UPPER(NodeName) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,NULL AS AvailabilityGroupName

              ,NULL AS AvailabilityGroupListenerName

              ,CASE UPPER(NodeName)

                     WHEN UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) THEN 'PRIMARY'

                     ELSE 'SECONDARY'

              END AS HighAvailabilityRoleDesc

              ,'FCI' AS SQLServerClusteringMethod

              ,@@VERSION AS SQLServerVersion  

              FROM sys.dm_os_cluster_nodes

              ORDER BY NodeName;  

       END

       ELSE

       BEGIN

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

              ,UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,NULL AS AvailabilityGroupName

              ,NULL AS AvailabilityGroupListenerName

              ,NULL AS HighAvailabilityRoleDesc

              ,'Standalone' AS SQLServerClusteringMethod  

              ,@@VERSION AS SQLServerVersion  

       END

END

ELSE

BEGIN

       IF (SELECT SERVERPROPERTY('IsHadrEnabled')) = 1 AND (SELECT SERVERPROPERTY('IsClustered')) = 1

       BEGIN

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName

              ,UPPER(ar.replica_server_name) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,ag.name AS AvailabilityGroupName

              ,agl.dns_name AS AvailabilityGroupListenerName

                       ,CASE ar.replica_server_name WHEN dhags.primary_replica THEN 'PRIMARY'

                       ELSE 'SECONDARY'

              END AS HighAvailabilityRoleDesc

              ,'FCI/AG' AS SQLServerClusteringMethod

              ,@@VERSION AS SQLServerVersion  

              FROM sys.availability_groups AS ag

              LEFT OUTER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id

              LEFT OUTER JOIN sys.availability_group_listeners AS agl ON ag.group_id = agl.group_id

                       LEFT OUTER JOIN sys.dm_hadr_availability_group_states as dhags ON ag.group_id = dhags.group_id

              WHERE UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) <> ar.replica_server_name

              UNION ALL

              SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

              ,UPPER(NodeName) AS ServerName

              ,SERVERPROPERTY('IsClustered') AS IsClustered

              ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

              ,NULL AS AvailabilityGroupName

              ,NULL AS AvailabilityGroupListenerName

              ,CASE is_current_owner 

                     WHEN 1 THEN 'PRIMARY'

                     ELSE 'SECONDARY'

              END AS HighAvailabilityRoleDesc

              ,'FCI/AG' AS SQLServerClusteringMethod

              ,@@VERSION AS SQLServerVersion  

              FROM sys.dm_os_cluster_nodes

              ORDER BY 5,2;  

       END

       ELSE

       BEGIN

              IF (SELECT SERVERPROPERTY('IsHadrEnabled')) = 1 

              BEGIN

   SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName

   ,UPPER(ar.replica_server_name) AS ServerName

   ,SERVERPROPERTY('IsClustered') AS IsClustered

   ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

   ,ag.name AS AvailabilityGroupName

   ,agl.dns_name AS AvailabilityGroupListenerName

   ,CASE ar.replica_server_name WHEN dhags.primary_replica THEN 'PRIMARY'

   ELSE 'SECONDARY'

   END AS HighAvailabilityRoleDesc

   ,'AG' AS SQLServerClusteringMethod

   ,@@VERSION AS SQLServerVersion

   FROM sys.availability_groups AS ag

   LEFT OUTER JOIN sys.availability_replicas AS ar ON ag.group_id = ar.group_id

   LEFT OUTER JOIN sys.availability_group_listeners AS agl ON ag.group_id = agl.group_id

   LEFT OUTER JOIN sys.dm_hadr_availability_group_states as dhags ON ag.group_id = dhags.group_id

   ORDER BY 5,2;  

              END

              ELSE 

              BEGIN

                     IF (SELECT SERVERPROPERTY('IsClustered')) = 1

                     BEGIN

                           SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

                           ,UPPER(NodeName) AS ServerName

                           ,SERVERPROPERTY('IsClustered') AS IsClustered

                           ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

                           ,NULL AS AvailabilityGroupName

                           ,NULL AS AvailabilityGroupListenerName

                           ,CASE is_current_owner 

                                  WHEN 1 THEN 'PRIMARY'

                                  ELSE 'SECONDARY'

                           END AS HighAvailabilityRoleDesc

                           ,'FCI' AS SQLServerClusteringMethod

                           ,@@VERSION AS SQLServerVersion  

                           FROM sys.dm_os_cluster_nodes

                           ORDER BY NodeName;  

                     END

                     ELSE

                     BEGIN

                           SELECT UPPER(CAST(SERVERPROPERTY('ServerName') AS VARCHAR)) AS InstanceName  

                           ,UPPER(CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)) AS ServerName

                           ,SERVERPROPERTY('IsClustered') AS IsClustered

                           ,SERVERPROPERTY ('IsHadrEnabled') AS IsAvailabilityGroupEnabled

                           ,NULL AS AvailabilityGroupName

                           ,NULL AS AvailabilityGroupListenerName

                           ,NULL AS HighAvailabilityRoleDesc

                           ,'Standalone' AS SQLServerClusteringMethod

                           ,@@VERSION AS SQLServerVersion;  

                     END

              END

       END

END;

Update: You can now download the latest version from github.