排查链接问题 - Azure SQL 托管实例

使用 T-SQL 确定种子设定阶段或数据同步开始后链接的状态。

使用以下 T-SQL 查询来确定 SQL Server 或 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：链接运行不正常，数据在副本之间未同步。

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

使用 az sql mi link show 通过 Azure CLI 获取链接状态信息。

# 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 托管实例之间的网络连接，请执行以下步骤：

1. 连接到 SSMS 中将用作主要副本的实例。

2. 在“对象资源管理器”中，展开数据库，然后右键单击要链接到次要副本的数据库。 选择“任务”>“Azure SQL 托管实例链接”>“测试连接”，以打开“网络检查器”向导：

   

3. 在“网络检查器”向导的“简介”页上，选择“下一步”。

4. 如果满足“先决条件”页上的所有要求，请选择“下一步”。 否则，请解决任何未满足的先决条件，然后选择“重新运行验证”。

5. 在“登录”页上，选择“登录”以连接到将作为次要副本的其他实例。 选择下一步。

6. 检查“指定网络选项”页上的详细信息，并在必要时提供 IP 地址。 选择下一步。

7. 在“摘要”页上，查看向导执行的操作，然后选择“完成”以测试两个副本之间的连接。

8. 查看“结果”页，验证两个副本之间是否存在连接，然后选择“关闭”以完成。

若要使用 T-SQL 测试连接，必须沿两个方向检查连接。 首先，测试从 SQL Server 到SQL 托管实例的连接，然后测试从 SQL 托管实例到 SQL Server 的连接。

使用 SQL Server 上的 SQL Server 代理运行从 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.china-east.worker.database.chinacloudapi.cn'
    DECLARE @port NVARCHAR(512) = N'11002'
    DECLARE @serverName NVARCHAR(512) = N'contoso-instance.12345678.database.chinacloudapi.cn'
   

   保存要在后续步骤中使用的结果。 由于这些参数可以在任何故障转移后更改，因此如有必要，请务必再次生成这些参数。

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 范围的规则。
   • 托管 SQL 托管实例的虚拟网络是否有一个允许在端口 5022 上进行通信的 NSG 规则。

若要检查 SQL 托管实例是否可以访问 SQL Server，请首先创建一个测试终结点。 然后使用 SQL Server 代理运行 PowerShell 脚本，并通过 tnc 命令从 SQL 托管实例对端口 5022 上的 SQL Server 执行 ping。

若要创建测试终结点，请连接到 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 Server 代理作业，以尝试从 SQL 托管实例测试端口 5022 上的 SQL Server 测试终结点。

接下来，通过在 SQL 托管实例上运行以下 T-SQL 脚本，在 SQL 托管实例上创建名为 NetHelper 的 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 托管实例的虚拟网络是否有一个允许在端口 5022 上进行通信的 NSG 规则。
• 如果 SQL Server 实例位于 Azure VM 上，那么托管该 VM 的虚拟网络是否有一个允许在端口 5022 上进行通信的 NSG 规则。
• SQL Server 是否正在运行。
• SQL Server 上是否存在测试终结点。

解决问题后，通过在托管实例上运行 EXEC ExecuteNetHelper 来重新运行 NetHelper 网络探测。

最后，在网络测试成功完成后，请在 SQL Server 上使用以下 T-SQL 命令删除测试终结点和证书：

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