リンクのために環境を準備する - Azure SQL Managed Instance

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 Agent を使用して、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';
   

   結果は次の例のようになります。

   

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

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 Agent を使用して、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 Agent ジョブの作成へと進んで、SQL マネージド インスタンスからポート 5022 で SQL Server テスト エンドポイントをテストすることができます。

次に、NetHelper という SQL マネージド インスタンスに対して SQL Server Agent ジョブを作成します。それには、SQL マネージド インスタンスに対して次の T-SQL スクリプトを実行します。 置換前のコード:

• 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