How-to test failover group connectivity between primary and secondary SQL Managed Instances

This article and script have been tremendously helpful in helping our organization solve some network issues affecting failover replication for our SQL Server Managed Instance failover groups.  Thank you very much to NikoNeugebauer and VitorTomaz for putting this together and sharing.  However, there are some errors in the original script, including some misnamed variables, etc., which I corrected in order to use this in practice.  A copy of my updated script including added comments and formatting is below:

-- Ref: https://techcommunity.microsoft.com/t5/azure-sql-blog/how-to-test-failover-group-connectivity-between-primary-and/ba-p/3058443

-- When running on the primary instance (not currently failed-over to the secondary),
-- execute the first section below on the SECONDARY instance.
-- This is a SELECT statement only which will return 3 rows of data that will be used
-- in the next section.
-----------------------------
-- BEGIN SELECT PARAMETERS (run this on the **SECONDARY** instance if not currently failed-over)
SELECT 
	'DECLARE @serverName NVARCHAR(512) = N'''+ value + ''''
FROM
	sys.dm_hadr_fabric_config_parameters
WHERE
	parameter_name  = 'DnsRecordName'
UNION
	SELECT 
		'DECLARE @node NVARCHAR(512) = N'''+ NodeName + '.' + Cluster + ''''
	FROM (
			SELECT 
				SUBSTRING(replica_address,0, CHARINDEX('\', replica_address)) as NodeName,
				RIGHT(service_name, CHARINDEX('/', REVERSE(service_name))-1) AppName, JoinCol = 1
			FROM 
				sys.dm_hadr_fabric_partitions fp
				JOIN sys.dm_hadr_fabric_replicas fr ON fp.partition_id = fr.partition_id
				JOIN sys.dm_hadr_fabric_nodes fn ON fr.node_name = fn.node_name
			WHERE 
				service_name like '%ManagedServer%' and replica_role = 2
	) t1
	LEFT JOIN (
		SELECT 
			value as Cluster, JoinCol = 1
		FROM 
			sys.dm_hadr_fabric_config_parameters
		WHERE 
			parameter_name  = 'ClusterName'
	) t2 ON (t1.JoinCol = t2.JoinCol)
	INNER JOIN (
		SELECT 
			[value] AS AppName
		FROM 
			sys.dm_hadr_fabric_config_parameters
		WHERE 
			section_name = 'SQL' and parameter_name = 'InstanceName'
	) t3 ON (t1.AppName = t3.AppName)
UNION
	SELECT 
		'DECLARE @port NVARCHAR(512) = N'''+ value + ''''
	FROM 
		sys.dm_hadr_fabric_config_parameters
	WHERE 
		parameter_name = 'HadrPort';

-- END SELECT PARAMETERS --
-----------------------------

-- The query above will return 3 rows that look like this:

--DECLARE @node NVARCHAR(512) = N'DB8C0.tr1234.westus1-a.worker.database.windows.net'
--DECLARE @port NVARCHAR(512) = N'11007'
--DECLARE @serverName NVARCHAR(512) = N'sql-mi-secondary.e0120bdf3e8f.database.windows.net'

-- Copy and paste these 3 rows (declare statements) at the beginning of the query below: 

-- When running on the primary instance (i.e., not currently failed-over),
-- run the following on the **PRIMARY** instance.
-- This will create a SQL Server Agent Job named 'TestFoGConnection'.
-- If a job with this name already exists, the existing job will be deleted first.

-----------------------------
-- BEGIN CREATE SQL JOB
-- Run this entire section as a whole.
-- (typically this is run on the **PRIMARY** instance)

-- Values returned from secondary instance (from query above)
-- ** PASTE THE ROWS FROM QUERY ABOVE HERE: **
DECLARE @node NVARCHAR(512) = N'DB8C0.tr1234.westus1-a.worker.database.windows.net'
DECLARE @port NVARCHAR(512) = N'11007'
DECLARE @serverName NVARCHAR(512) = N'sql-mi-secondary.e0120bdf3e8f.database.windows.net'

-- Delete any existing job named 'TestFoGConnection'
IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'TestFoGConnection')
EXEC msdb.dbo.sp_delete_job @job_name = N'TestFoGConnection', @delete_unused_schedule=1

-- Create new job named 'TestFoGConnection'
DECLARE @jobId BINARY(16), @cmd NVARCHAR(MAX)
EXEC msdb.dbo.sp_add_job 
	@job_name = N'TestFoGConnection', 
	@enabled=1, 
	@job_id = @jobId OUTPUT, 
	@owner_login_name = 'mysqladminlogin' -- **NOTE** Hard-coded owner of the job. Replace this with a SQL Server login name on your system 

-- Add first step to the newly created job.  Note that this step is a PowerShell script that will be executed by the SQL Server Agent.
SET @cmd = (N'Test-NetConnection ' + @serverName + N' -Port 5022 | select ComputerName, RemoteAddress, TcpTestSucceeded | Format-List')
EXEC msdb.dbo.sp_add_jobstep 
	@job_id = @jobId, 
	@step_name = N'Test Port 5022',
	@step_id = 1, 
	@cmdexec_success_code = 0, 
	@on_success_action = 3, 
	@on_fail_action = 3,
	@subsystem = N'PowerShell', 
	@command = @cmd, 
	@database_name = N'master'

-- Add second step to the newly created job.  Note that this step is a PowerShell script that will be executed by the SQL Server Agent.
SET @cmd = (N'Test-NetConnection ' + @node + N' -Port ' + @port +' | select ComputerName, RemoteAddress, TcpTestSucceeded | Format-List')
EXEC msdb.dbo.sp_add_jobstep 
	@job_id = @jobId, 
	@step_name = N'Test HADR Port',
	@step_id = 2, 
	@cmdexec_success_code = 0, 
	@subsystem = N'PowerShell', 
	@command = @cmd, 
	@database_name = N'master'

EXEC msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
GO

-- END CREATE SQL JOB
-----------------------------

-- Run the following to start the job.
-- (Or, find the job in SSMS and start it from there.)
EXEC msdb.dbo.sp_start_job @job_name = N'TestFoGConnection'
GO

-- The job will run once, execute the steps and stop.
-- You can then check the job history in SSMS to see the results,
-- or view the results using the queries below.

--Check status every 5 seconds
DECLARE @RunStatus INT 
SET @RunStatus=10
WHILE (@RunStatus >= 4) BEGIN
	SELECT 
		distinct @RunStatus = run_status
	FROM 
		[msdb].[dbo].[sysjobhistory] JH 
		JOIN [msdb].[dbo].[sysjobs] J ON JH.job_id = J.job_id 
	WHERE 
		J.name= N'TestFoGConnection' and step_id = 0
	
	WAITFOR DELAY '00:00:05'; 
END

--Get logs once job completes
SELECT 
	[step_name],
		SUBSTRING([message], CHARINDEX('TcpTestSucceeded',[message]), CHARINDEX('Process Exit', [message])-CHARINDEX('TcpTestSucceeded',[message])) as TcpTestResult,
		SUBSTRING([message], CHARINDEX('RemoteAddress',[message]), CHARINDEX ('TcpTestSucceeded',[message])-CHARINDEX('RemoteAddress',[message])) as RemoteAddressResult,
		[run_date], [run_time], [run_status] ,[run_duration], [message]
FROM 
	[msdb].[dbo].[sysjobhistory] JH 
	JOIN [msdb].[dbo].[sysjobs] J ON JH.job_id= J.job_id
WHERE 
	J.name = N'TestFoGConnection' and step_id <> 0

SELECT
	*
FROM 
	[msdb].[dbo].[sysjobhistory] JH
	JOIN [msdb].[dbo].[sysjobs] J ON JH.job_id= J.job_id
WHERE 
	J.name = N'TestFoGConnection' and step_id <> 0