使用下列 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 和 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 Agent 來執行從 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 受控實例上建立 SQL Server Agent 作業，以嘗試從 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 受控實例
