リンクのトラブルシューティング - Azure SQL Managed Instance

リンクの監視は、SQL Server Management Studio (SSMS) 21.0 (プレビュー) 以降で使用できます。

SSMS でリンクの状態を確認するには、次の手順に従います。

1. リンクをホストするレプリカに接続します。

2. オブジェクト エクスプローラーで、[AlwaysOn 高可用性]、[可用性グループ] の順に展開します。

3. リンクの名前を右クリックし、[ プロパティ ] を選択して [リンクの プロパティ ] ウィンドウを開きます。

   

4. [リンクのプロパティ] ウィンドウには、レプリカ情報、リンクの状態、エンドポイント証明書の有効期限など、リンクに関する有用な情報が表示されます。

   

T-SQL を使用して、シード処理フェーズ中、またはデータ同期の開始後にリンクの状態を確認します。 sys.dm_hadr_physical_seeding_stats DMV を使用して、初期シード処理の状態を追跡できます。 estimate_time_complete_utc列は、現在のtransfer_rate_bytes_per_secondと圧縮されていない残りのデータ サイズ (is_compression_enabled = 0 の場合) に基づいています。 Managed Instance リンクの場合、データ圧縮が使用されるため、 estimate_time_complete_utc は過大評価であることが予想されます。

リンクを介してシード処理されたデータベースをホストする SQL Server または SQL Managed Instance 上で、次の T-SQL クエリを使用して、シード処理フェーズ中のリンクの状態を確認します。

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: リンクは異常であり、レプリカ間でデータが同期していません。

PowerShell でリンク状態情報を取得するには、Get-AzSqlInstanceLink を使用します。

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. [の概要] ページでウィザードが実行する操作を確認し、[完了] を選択して、2 つのレプリカ間の接続をテストします。

8. 結果 ページを確認して、2つのレプリカ間に接続が存在することを確認した後、[閉じる] を選択して完了します。

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'
   

   結果を保存して次の手順を使用します。 これらのパラメーターはフェールオーバー後に変更される可能性があるため、必要に応じて必ずもう一度生成してください。

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 で接続を受信していることを確認するには、次の PowerShell コマンドを SQL Server インスタンスのホスト オペレーティング システムで実行します。

tnc localhost -port 5022

テストが成功すると、TcpTestSucceeded : True と表示されます。 その後、SQL マネージド インスタンス上で SQL Server エージェント ジョブの作成に進み、SQL マネージド インスタンスからポート 5022 で SQL Server テスト エンドポイントをテストしてみることができます。

次に、SQL マネージド インスタンス上で次の T-SQL スクリプトを実行して、SQL マネージド インスタンス上に NetHelper という SQL Server エージェント ジョブを作成します。 次のように置換します。

• SQL マネージド インスタンスからアクセスできる SQL Server の IP アドレスを使用して、<SQL_SERVER_IP_ADDRESS> を設定します。

-- 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