使用以下 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
$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
# 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
首先，确认你处于裂脑应用场景中。 可以使用 SQL Server Management Studio (SSMS) 或 Transact-SQL (T-SQL) 来执行此操作。
在 SSMS 中连接到 SQL Server 和 SQL 托管实例，然后在对象资源管理器中，展开“Always On 高可用性”中“可用性组”节点下的“可用性副本”。 如果两个不同的副本列为“(主要)”，则表明你处于裂脑应用场景中。
或者，可以在 SQL Server 和 SQL 托管实例上运行以下 T-SQL 脚本来检查副本的角色：
-- Execute on SQL Server and SQL Managed Instance
USE master
DECLARE @link_name varchar(max) = '<DAGName>'
SELECT
ag.name [Link name],
rs.role_desc [Link role]
FROM
sys.availability_groups ag
JOIN sys.dm_hadr_availability_replica_states rs
ON ag.group_id = rs.group_id
WHERE
rs.is_local = 1 AND ag.is_distributed = 1 AND ag.name = @link_name
GO
若要使用 T-SQL 测试连接，必须沿两个方向检查连接。 首先，测试从 SQL Server 到SQL 托管实例的连接，然后测试从 SQL 托管实例到 SQL Server 的连接。
测试从 SQL Server 到 SQL 托管实例的连接
使用 SQL Server 上的 SQL Server 代理运行从 SQL Server 到 SQL 托管实例的连接测试。
连接到 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';
-- 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 测试终结点。
-- 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;
Azure SQL 托管实例
