문제 해결 링크 - Azure SQL Managed Instance

링크 모니터링은 SSMS(SQL Server Management Studio) 21.0(미리 보기)부터 사용할 수 있습니다.

SSMS에서 링크 상태를 확인하려면 다음 단계를 수행합니다.

1. 링크를 호스트하는 복제본에 연결합니다.

2. 개체 탐색기에서 Always On 고가용성을 확장한 다음 가용성 그룹을 확장합니다.

3. 링크의 이름을 마우스 오른쪽 단추로 클릭한 다음 속성을 선택하여 링크 속성 창을 엽니다.

   

4. 링크 속성 창에는 복제본 정보, 링크 상태 및 엔드포인트 인증서 만료 날짜와 같은 링크에 대한 유용한 정보가 표시됩니다.

   

T-SQL을 사용하여 시드 단계 중 또는 데이터 동기화가 시작된 후 링크의 상태를 확인합니다.

다음 T-SQL 쿼리를 사용하여 링크를 통해 시드된 데이터베이스를 호스트하는 SQL Server 또는 SQL Managed Instance의 시드 단계 중에 링크의 상태를 확인합니다.

SELECT
    ag.local_database_name AS 'Local database name',
    ar.current_state AS 'Current state',
    ar.is_source AS 'Is source',
    ag.internal_state_desc AS 'Internal state desc',
    ag.database_size_bytes / 1024 / 1024 AS 'Database size MB',
    ag.transferred_size_bytes / 1024 / 1024 AS 'Transferred MB',
    ag.transfer_rate_bytes_per_second / 1024 / 1024 AS 'Transfer rate MB/s',
    ag.total_disk_io_wait_time_ms / 1000 AS 'Total Disk IO wait (sec)',
    ag.total_network_wait_time_ms / 1000 AS 'Total Network wait (sec)',
    ag.is_compression_enabled AS 'Compression',
    ag.start_time_utc AS 'Start time UTC',
    ag.estimate_time_complete_utc as 'Estimated time complete UTC',
    ar.completion_time AS 'Completion time',
    ar.number_of_attempts AS 'Attempt No'
FROM sys.dm_hadr_physical_seeding_stats AS ag
    INNER JOIN sys.dm_hadr_automatic_seeding AS ar
    ON local_physical_seeding_id = operation_id

-- Estimated seeding completion time
SELECT DISTINCT CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, start_time_utc, estimate_time_complete_utc) ,0), 108) as 'Estimated complete time'
FROM sys.dm_hadr_physical_seeding_stats

쿼리가 결과를 반환하지 않으면 시드 프로세스가 시작되지 않았거나 이미 완료된 것입니다.

기본 인스턴스에서 다음 T-SQL 쿼리를 사용하여 데이터 동기화가 시작되면 링크의 상태를 확인합니다.

DECLARE @link_name varchar(max) = '<DAGname>'
SELECT
   rs.synchronization_health_desc [Link sync health]
FROM
   sys.availability_groups ag 
   join sys.dm_hadr_availability_replica_states rs 
   on ag.group_id = rs.group_id 
WHERE 
   rs.is_local = 0 AND rs.role = 2 AND ag.is_distributed = 1 AND ag.name = @link_name 
GO

쿼리는 다음과 같은 가능한 값을 반환합니다.

• 결과 없음: 쿼리가 보조 인스턴스에서 실행되었습니다.
• HEALTHY: 링크가 정상이며 복제본 간에 데이터가 동기화되고 있습니다.
• NOT_HEALTHY: 링크가 비정상이며 복제본 간에 데이터가 동기화되지 않습니다.

Get-AzSqlInstanceLink 사용하여 PowerShell과 연결 상태 정보를 가져옵니다.

Azure Cloud Shell에서 다음 샘플 코드를 실행하거나 로컬로 Az.SQL 모듈을 설치합니다.

$ManagedInstanceName = "<ManagedInstanceName>" # The name of your linked SQL Managed Instance
$DAGName = "<DAGName>" # distributed availability group name

# Find out the resource group name 
$ResourceGroupName = (Get-AzSqlInstance -InstanceName $ManagedInstanceName).ResourceGroupName 

# Show link state details 
(Get-AzSqlInstanceLink -ResourceGroupName $ResourceGroupName -InstanceName $ManagedInstanceName -Name $DAGName).Databases

Azure CLI를 사용하여 az sql mi link show 명령을 통해 연결 상태 정보를 확인하십시오.

# type "az" to use Azure CLI
managedInstanceName = "<ManagedInstanceName>" # The name of your linked SQL Managed Instance
dagName = "<DAGName>" # distributed availability group name
rgName = "<RGName>" # the resource group for the linked SQL Managed Instance  

# Print link state details 
az sql mi link show –-resource-group $rgName –-instance-name $managedInstanceName –-name $dagName  

SSMS에서 SQL Server와 SQL Managed Instance 간의 네트워크 연결을 테스트하려면 다음 단계를 수행합니다.

1. SSMS의 주 복제본이 될 인스턴스에 연결합니다.

2. 개체 탐색기데이터베이스를 확장하고 보조 데이터베이스와 연결하려는 데이터베이스를 마우스 오른쪽 단추로 클릭합니다. 작업>Azure SQL Managed Instance 링크>연결 테스트 선택하여 네트워크 검사기 마법사를 엽니다.

   

3. 네트워크 검사기 마법사의 소개 페이지에서 다음을 선택합니다.

4. 필수 구성 요소 페이지에서 모든 요구 사항이 충족되면 다음선택합니다. 미충족된 필수 조건을 해결한 후, 유효성 검사를 다시 실행할선택하세요.

5. 로그인 페이지에서 로그인 선택하여 보조 복제본이 될 다른 인스턴스에 연결합니다. 다음선택합니다.

6. 네트워크 옵션 지정 페이지에서 세부 정보를 확인하고 필요한 경우 IP 주소를 제공합니다. 다음선택합니다.

7. 요약 페이지에서 마법사가 수행하는 작업을 검토한 다음 마침 선택하여 두 복제본 간의 연결을 테스트합니다.

8. 결과 페이지를 검토하여 두 복제본 간에 연결이 존재하는지 확인하고 닫기를 선택하여 완료합니다.

T-SQL을 사용하여 연결을 테스트하려면 양방향으로 연결을 확인해야 합니다. 먼저 SQL Server에서 SQL Managed Instance로의 연결을 테스트한 다음 SQL Managed Instance에서 SQL Server로의 연결을 테스트합니다.

SQL Server에서 SQL Managed Instance로의 연결 테스트

SQL Server의 SQL Server 에이전트를 사용하여 SQL Server에서 SQL Managed Instance로 연결 테스트를 실행합니다.

1. SQL Managed Instance에 연결하고 다음 스크립트를 실행하여 나중에 필요한 매개 변수를 생성합니다.

   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
       INNER JOIN sys.dm_hadr_fabric_replicas fr
           ON fp.partition_id = fr.partition_id
       INNER 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';
   

   결과는 다음 샘플과 같습니다.

    DECLARE @node NVARCHAR(512) = N'DB123.tr123456.west-us.worker.database.windows.net'
    DECLARE @port NVARCHAR(512) = N'11002'
    DECLARE @serverName NVARCHAR(512) = N'contoso-instance.12345678.database.windows.net'
   

   결과를 저장하여 다음 단계를 사용합니다. 이러한 매개 변수는 장애 조치(failover) 후에 변경될 수 있으므로 필요한 경우 다시 생성해야 합니다.

2. SQL Server 인스턴스에 연결합니다.

3. 새 쿼리 창을 열고 다음 스크립트를 붙여넣습니다.

   --START
   -- Parameters section
   DECLARE @node NVARCHAR(512) = N''
   DECLARE @port NVARCHAR(512) = N''
   DECLARE @serverName NVARCHAR(512) = N''
   
   --Script section
   IF EXISTS (
           SELECT job_id
           FROM msdb.dbo.sysjobs_view
           WHERE name = N'TestMILinkConnection'
           )
       EXEC msdb.dbo.sp_delete_job @job_name = N'TestMILinkConnection',
           @delete_unused_schedule = 1
   
   DECLARE @jobId BINARY (16),
       @cmd NVARCHAR(MAX)
   
   EXEC msdb.dbo.sp_add_job @job_name = N'TestMILinkConnection',
       @enabled = 1,
       @job_id = @jobId OUTPUT
   
   SET @cmd = (N'tnc ' + @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'
   
   SET @cmd = (N'tnc ' + @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
   
   EXEC msdb.dbo.sp_start_job @job_name = N'TestMILinkConnection'
   GO
   
   --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
       INNER JOIN [msdb].[dbo].[sysjobs] J
           ON JH.job_id = J.job_id
       WHERE J.name = N'TestMILinkConnection'
           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_status],
       [run_duration],
       [message]
   FROM [msdb].[dbo].[sysjobhistory] JH
   INNER JOIN [msdb].[dbo].[sysjobs] J
       ON JH.job_id = J.job_id
   WHERE J.name = N'TestMILinkConnection'
       AND step_id <> 0
       --END
   

4. @node, @port및 @serverName 매개 변수를 첫 번째 단계에서 얻은 값으로 바꿉니다.

5. 스크립트를 실행하고 결과를 확인합니다. 다음 예제와 같은 결과가 표시됩니다.

   

6. 결과를 확인합니다.

   • TcpTestSucceeded에서 각 테스트의 결과는 TcpTestSucceeded : True이어야 합니다.
   • RemoteAddresses는 SQL Managed Instance 서브넷의 IP 범위에 속해야 합니다.

   응답이 실패하면 다음 네트워크 설정을 확인합니다.

   • 네트워크 방화벽 및 SQL Server 호스트 OS(Windows/Linux) 방화벽에 SQL Managed Instance의 전체 서브넷 IP 범위에 대한 트래픽을 허용하는 규칙이 있습니다.
   • SQL Managed Instance를 호스트하는 가상 네트워크에 대해 포트 5022에서 통신을 허용하는 NSG 규칙이 있습니다.

SQL Managed Instance에서 SQL Server로의 연결 테스트

SQL Managed Instance가 SQL Server에 연결할 수 있는지 확인하려면 먼저 테스트 엔드포인트를 만듭니다. 그런 다음 SQL Server 에이전트를 사용하여 SQL 관리되는 인스턴스의 포트 5022에서 SQL Server를 ping하는 tnc 명령으로 PowerShell 스크립트를 실행합니다.

테스트 엔드포인트를 만들려면 SQL Server에 연결하고 다음 T-SQL 스크립트를 실행합니다.

-- Run on SQL Server
-- Create the certificate needed for the test endpoint
USE MASTER
CREATE CERTIFICATE TEST_CERT
WITH SUBJECT = N'Certificate for SQL Server',
EXPIRY_DATE = N'3/30/2051'
GO

-- Create the test endpoint on SQL Server
USE MASTER
CREATE ENDPOINT TEST_ENDPOINT
    STATE=STARTED
    AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)
    FOR DATABASE_MIRRORING (
        ROLE=ALL,
        AUTHENTICATION = CERTIFICATE TEST_CERT,
        ENCRYPTION = REQUIRED ALGORITHM AES
    )

SQL Server 엔드포인트가 포트 5022에서 연결을 수신하고 있는지 확인하려면 SQL Server 인스턴스의 호스트 운영 체제에서 다음 PowerShell 명령을 실행합니다.

tnc localhost -port 5022

테스트에 성공하면 TcpTestSucceeded : True표시됩니다. 그런 다음 SQL 관리되는 인스턴스에서 SQL Server 에이전트 작업을 만들어 SQL 관리형 인스턴스에서 포트 5022에서 SQL Server 테스트 엔드포인트를 테스트해 볼 수 있습니다.

다음으로, SQL 관리되는 인스턴스에서 다음 T-SQL 스크립트를 실행하여 NetHelper이라는 SQL 관리형 인스턴스에서 SQL Server 에이전트 작업을 만듭니다. 교체하다

• <SQL_SERVER_IP_ADDRESS> SQL 관리형 인스턴스에서 액세스할 수 있는 SQL Server의 IP 주소를 사용합니다.

-- Run on SQL managed instance
-- SQL_SERVER_IP_ADDRESS should be an IP address that could be accessed from the SQL Managed Instance host machine.
DECLARE @SQLServerIpAddress NVARCHAR(MAX) = '<SQL_SERVER_IP_ADDRESS>'; -- insert your SQL Server IP address in here
DECLARE @tncCommand NVARCHAR(MAX) = 'tnc ' + @SQLServerIpAddress + ' -port 5022 -InformationLevel Quiet';
DECLARE @jobId BINARY(16);

IF EXISTS (
        SELECT *
        FROM msdb.dbo.sysjobs
        WHERE name = 'NetHelper'
        ) THROW 70000,
    'Agent job NetHelper already exists. Please rename the job, or drop the existing job before creating it again.',
    1
    -- To delete NetHelper job run: EXEC msdb.dbo.sp_delete_job @job_name=N'NetHelper'
    EXEC msdb.dbo.sp_add_job @job_name = N'NetHelper',
        @enabled = 1,
        @description = N'Test SQL Managed Instance to SQL Server network connectivity on port 5022.',
        @category_name = N'[Uncategorized (Local)]',
        @owner_login_name = N'sa',
        @job_id = @jobId OUTPUT;

EXEC msdb.dbo.sp_add_jobstep @job_id = @jobId,
    @step_name = N'TNC network probe from SQL MI to SQL Server',
    @step_id = 1,
    @os_run_priority = 0,
    @subsystem = N'PowerShell',
    @command = @tncCommand,
    @database_name = N'master',
    @flags = 40;

EXEC msdb.dbo.sp_update_job @job_id = @jobId,
    @start_step_id = 1;

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

그런 다음, 작업을 실행하는 데 도움이 되는 저장 프로시저 ExecuteNetHelper 만들고 네트워크 프로브에서 결과를 가져옵니다. SQL 관리형 인스턴스에서 다음 T-SQL 스크립트를 실행합니다.

-- Run on managed instance
IF EXISTS(SELECT * FROM sys.objects WHERE name = 'ExecuteNetHelper')
    THROW 70001, 'Stored procedure ExecuteNetHelper already exists. Rename or drop the existing procedure before creating it again.', 1
GO
CREATE PROCEDURE ExecuteNetHelper AS
-- To delete the procedure run: DROP PROCEDURE ExecuteNetHelper
BEGIN
    -- Start the job.
    DECLARE @NetHelperstartTimeUtc DATETIME = GETUTCDATE();
    DECLARE @stop_exec_date DATETIME = NULL;

    EXEC msdb.dbo.sp_start_job @job_name = N'NetHelper';

    -- Wait for job to complete and then see the outcome.
    WHILE (@stop_exec_date IS NULL)
    BEGIN
        -- Wait and see if the job has completed.
        WAITFOR DELAY '00:00:01'

        SELECT @stop_exec_date = sja.stop_execution_date
        FROM msdb.dbo.sysjobs sj
        INNER JOIN msdb.dbo.sysjobactivity sja
            ON sj.job_id = sja.job_id
        WHERE sj.name = 'NetHelper'

        -- If job has completed, get the outcome of the network test.
        IF (@stop_exec_date IS NOT NULL)
        BEGIN
            SELECT sj.name JobName,
                sjsl.date_modified AS 'Date executed',
                sjs.step_name AS 'Step executed',
                sjsl.log AS 'Connectivity status'
            FROM msdb.dbo.sysjobs sj
            LEFT JOIN msdb.dbo.sysjobsteps sjs
                ON sj.job_id = sjs.job_id
            LEFT JOIN msdb.dbo.sysjobstepslogs sjsl
                ON sjs.step_uid = sjsl.step_uid
            WHERE sj.name = 'NetHelper'
        END

        -- In case of operation timeout (90 seconds), print timeout message.
        IF (datediff(second, @NetHelperstartTimeUtc, getutcdate()) > 90)
        BEGIN
            SELECT 'NetHelper timed out during the network check. Please investigate SQL Agent logs for more information.'

            BREAK;
        END
    END
END;

SQL 관리형 인스턴스에서 다음 쿼리를 실행하여 NetHelper 에이전트 작업을 실행하고 결과 로그를 표시하는 저장 프로시저를 실행합니다.

-- Run on managed instance
EXEC ExecuteNetHelper;

연결에 성공하면 로그에 True표시됩니다. 연결에 실패하면 로그에 False표시됩니다.

연결에 실패한 경우 다음 항목을 확인합니다.

• 호스트 SQL Server 인스턴스의 방화벽은 포트 5022에서 인바운드 및 아웃바운드 통신을 허용합니다.
• SQL Managed Instance를 호스트하는 가상 네트워크에 대한 NSG 규칙은 포트 5022에서 통신을 허용합니다.
• SQL Server 인스턴스가 Azure VM에 있는 경우 NSG 규칙은 VM을 호스트하는 가상 네트워크의 포트 5022에 대한 통신을 허용합니다.
• SQL Server가 실행 중입니다.
• SQL Server에 테스트 엔드포인트가 있습니다.

문제를 해결한 후 관리되는 인스턴스에서 EXEC ExecuteNetHelper 실행하여 NetHelper 네트워크 프로브를 다시 실행합니다.

마지막으로 네트워크 테스트가 성공하면 다음 T-SQL 명령을 사용하여 SQL Server에서 테스트 엔드포인트 및 인증서를 삭제합니다.

-- Run on SQL Server
DROP ENDPOINT TEST_ENDPOINT;
GO
DROP CERTIFICATE TEST_CERT;
GO