準備您的環境以供連結：Azure SQL 受控執行個體

若要在 SSMS 中測試 SQL Server 與 SQL 受控執行個體之間的網路連線，請遵循下列步驟：

1. 在 SSMS 中連接到將作為主要複本的執行個體。

2. 在物件總管中，展開資料庫，並以滑鼠右鍵按一下您想要連結至次要資料庫的資料庫。 選取[作業]>[Azure SQL 受控執行個體連結]>[測試連線] 以開啟網路檢查程式精靈：

   

3. 在網路檢查程式精靈的 [簡介] 頁面上，選取 [下一步]。

4. 如果已符合必要條件上的所有需求，請選取 [下一步]。 否則，請解決任何未滿足的必要條件，然後選取 [重新執行驗證]。

5. 在 [登入] 頁面上，選取 [登入] 以連線到將次要複本的其他執行個體。 選取 [下一步]。

6. 檢查 [指定網路選項] 頁面上的詳細資料，並視需要提供 IP 位址。 選取 [下一步]。

7. 在 [摘要] 頁面上，檢閱精靈採取的動作，然後選取 [完成] 以測試兩個複本之間的連線。

8. 檢閱 [結果] 頁面，以驗證兩個複本之間的連線，然後選取 [關閉] 以完成。

若要使用 T-SQL 來測試連線能力，您必須檢查雙向的連線。 首先，測試從 SQL Server 到 SQL 受控執行個體的連線，然後測試從 SQL 受控執行個體到 SQL Server 的連線。

測試從 SQL Server 到 SQL 受控執行個體的連線

在 SQL Server 上使用 SQL Server Agent，執行從 SQL Server 到 SQL 受控執行個體的連線測試。

1. 連線至 SQL 受控執行個體並執行下列指令碼，以產生稍後需要用到的某些參數：

   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 受控執行個體子網路的 IP 範圍。

   如果回應失敗，請驗證下列網路設定：

   • 網路防火牆和 SQL Server 主機作業系統 (Windows/Linux) 防火牆都設有規則，允許流量流向 SQL 受控執行個體的整個子網路 IP 範圍。
   • 設有 NSG 規則允許裝載 SQL 受控執行個體的虛擬網路於連接埠 5022 上通訊。

測試從 SQL 受控執行個體到 SQL Server 的連線

若要檢查 SQL 受控執行個體是否可以連線到 SQL Server，請先建立測試端點。 然後使用 SQL Agent 執行 PowerShell 指令碼，搭配 tnc 命令從 SQL 受控執行個體的連接埠 5022 上 Ping 到 SQL Server。

若要建立測試端點，請連線至 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 Agent 作業，嘗試從 SQL 受控執行個體的連接埠 5022 上測試 SQL Server 測試端點。

接下來，請在 SQL 受控執行個體上執行下列 T-SQL 指令碼，以在 SQL 受控執行個體 NetHelper 上建立 SQL Server Agent 作業。 將：

• <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 受控執行個體的虛擬網路設有 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