Azure SQL Managed Instance 에서 외부 리소스 TCP 연결 체크

Azure SQL Managed Instnace 에서 다른 외부 리소스(Azure Storage, SMTP, SQL VM 등)에 연결이 필요한 경우 아래 테스트 쿼리를 통해 DNS 이름 풀이나 TCP 포트 연결 성공 여부를 자가 진단할 수 있습니다. 

테스트를 위해 SQL VM 을 만들고 VM의 NSG에서 1433 Inbound 포트를 오픈하였고 SQL MI 의 NSG에서 VM으로 연결되는 Outbound 패킷을 허용/차단하여 아래와 같이 각각 성공/실패 사례를 체크하였습니다. 

먼저 아래 스크립트 상단에 있는 @endpoint, @port 정보에 대해서만 연결 대상 정보로 변경합니다. 그리고 SQL MI 에 연결한 후 새쿼리를 통해 이 스크립트를 수행하면 TCP 연결 테스트 결과를 반환하게 됩니다. 

--START
-- Parameters
DECLARE @endpoint NVARCHAR(512) = N'52.155.000.14' -- 대상 IP 
DECLARE @port NVARCHAR(5) = N'1433' -- 대상 PORT 

--Script
DECLARE @jobName NVARCHAR(512) = N'TestTCPNetworkConnection', @jobId BINARY(16), @cmd NVARCHAR(MAX);

IF EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = @jobName)
BEGIN
	EXEC msdb.dbo.sp_delete_job @job_name=@jobName, @delete_unused_schedule=1;
END

EXEC msdb.dbo.sp_add_job @job_name=@jobName, @enabled=1, @job_id = @jobId OUTPUT;

DECLARE @stepName NVARCHAR(512) = @endpoint + N':' + @port;
SET @cmd = (N'tnc ' + @endpoint + N' -port ' + @port +' | select ComputerName,    RemoteAddress, TcpTestSucceeded | Format-List');

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name = @stepName, @step_id=1, @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)';
EXEC msdb.dbo.sp_start_job @job_name=@jobName;

--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=@jobName and step_id = 0;

	WAITFOR DELAY '00:00:05'; 
END

--Get logs once job completes
SELECT [step_name] AS [Endpoint]
		,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_status] ,[run_duration], [message]
	FROM [msdb].[dbo].[sysjobhistory] JH JOIN [msdb].[dbo].[sysjobs] J ON JH.job_id= J.   job_id
	WHERE J.name=@jobName and step_id <> 0;
--END

원본 아티클은 아래 링크에 소개되어 있습니다만 일부 컬럼명에 대한 스크립트에 오류가 있어 정상적으로 동작하는 수정된 스크립트를 이 블로그에 게시하였습니다. 

How-to test TCP connectivity from a SQL Managed Instance
https://techcommunity.microsoft.com/t5/azure-sql-blog/how-to-test-tcp-connectivity-from-a-sql-managed-instance/ba-p/3058458

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



작성자: Lai Go / 작성일자: 2022.01.26