Ссылка для устранения неполадок — Управляемый экземпляр SQL Azure

Используйте 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  

Чтобы проверить сетевое подключение между SQL Server и управляемым экземпляром SQL в SSMS, выполните следующие действия.

1. Подключитесь к экземпляру, который будет основной репликой в SSMS.

2. В обозревателе объектов, разверните базы данных и щелкните правой кнопкой мыши базу данных, которую вы планируете связать со вторичной. Выберите ссылку Задачи>управляемого экземпляра Azure SQL>Тест подключения, чтобы открыть мастер Network Checker .

   

3. Выберите Далее на странице Введение мастера проверки сети.

4. Если все требования выполнены на странице предварительных требований , выберите Далее. В противном случае устраните все невыполненные предварительные условия, а затем выберите Повторная проверка.

5. На странице входа выберите Login для подключения к другому экземпляру, который будет вторичной репликой. Выберите Далее.

6. Проверьте сведения на странице Указание параметров сети и при необходимости укажите IP-адрес. Выберите Далее.

7. На странице Сводка просмотрите действия мастера, а затем нажмите кнопку Готово, чтобы проверить подключение между двумя репликами.

8. Просмотрите страницу результатов , чтобы проверить наличие соединения между двумя репликами, а затем нажмите кнопку Закрыть, чтобы завершить.

Чтобы использовать T-SQL для проверки подключения, необходимо проверить подключение в обоих направлениях. Сначала проверьте подключение из SQL Server к управляемому экземпляру SQL, а затем проверьте подключение из Управляемого экземпляра SQL к SQL Server.

Тестирование подключения из SQL Server к управляемому экземпляру SQL

Используйте агент SQL Server на SQL Server для выполнения тестов подключения к управляемому экземпляру SQL Server.

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 должен принадлежать диапазону IP-адресов для подсети Управляемого экземпляра SQL.

   Если ответ не выполнен, проверьте следующие параметры сети:

   • Существуют правила в брандмауэре сети и в брандмауэре ОС узла SQL Server (Windows/Linux), позволяющие передачу трафика для всего диапазона IP-адресов подсети Управляемого экземпляра SQL.
   • Существует правило NSG, разрешающее обмен данными через порт 5022 для виртуальной сети, в которую размещается управляемый экземпляр SQL.

Проверка подключения из Управляемого экземпляра SQL к SQL Server

Чтобы убедиться, что Управляемый экземпляр SQL может получить доступ к SQL Server, сначала создайте тестовую конечную точку. Затем вы используете агент SQL Server для запуска скрипта PowerShell с помощью команды tnc для проверки ping SQL Server через порт 5022 из управляемого экземпляра 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, выполните следующую команду PowerShell в операционной системе узла экземпляра SQL Server:

tnc localhost -port 5022

Успешный тест показывает TcpTestSucceeded : True. Затем можно перейти к созданию задания агента SQL Server в управляемом экземпляре SQL, чтобы попробовать проверить конечную точку тестирования SQL Server на порте 5022 из управляемого экземпляра SQL.

Затем создайте задание агента SQL Server в управляемом экземпляре SQL с именем NetHelper, выполнив следующий скрипт T-SQL в управляемом экземпляре SQL. Замените:

• <SQL_SERVER_IP_ADDRESS> с IP-адресом SQL Server, к которому можно получить доступ из управляемого экземпляра SQL.

-- 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, которая помогает запустить задание и получает результаты из сетевой пробы. Выполните следующий скрипт T-SQL в управляемом экземпляре 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.
• Правило NSG для виртуальной сети, в которую размещается управляемый экземпляр SQL, разрешает обмен данными через порт 5022.
• Если экземпляр SQL Server находится на виртуальной машине Azure, правило NSG разрешает обмен данными через порт 5022 в виртуальной сети, на котором размещена виртуальная машина.
• SQL Server запущен.
• В SQL Server существует тестовая конечная точка.

После устранения проблем повторно выполните проверку сети NetHelper, выполнив EXEC ExecuteNetHelper в управляемом экземпляре.

Наконец, после успешного выполнения сетевого теста удалите конечную точку теста и сертификат в SQL Server с помощью следующих команд T-SQL:

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