Risoluzione dei problemi del collegamento - Istanza gestita di SQL di Azure

Usare T-SQL per determinare lo stato del collegamento durante la fase di seeding o dopo l'inizio della sincronizzazione dei dati.

Usare la query T-SQL seguente per determinare lo stato del collegamento durante la fase di seeding in SQL Server o Istanza gestita di SQL che ospita il database di cui è stato eseguito il seeding tramite il collegamento:

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

Se la query non restituisce risultati, il processo di seeding non è stato avviato o è già stato completato.

Usare la seguente query T-SQL nell'istanza primaria di per verificare l'integrità del collegamento una volta avviata la sincronizzazione dei dati.

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

La query restituisce i valori possibili seguenti:

• nessun risultato: la query è stata eseguita nell'istanza secondaria.
• HEALTHY: il collegamento è integro e i dati vengono sincronizzati tra le repliche.
• NOT_HEALTHY: il collegamento non è integro e i dati non vengono sincronizzati tra le repliche.

Usare Get-AzSqlInstanceLink per ottenere informazioni sullo stato del collegamento con PowerShell.

Eseguire il codice di esempio seguente in Azure Cloud Shell o installare il modulo Az.SQL in locale.

$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

Usare az sql mi link show per ottenere informazioni sullo stato del collegamento con 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  

Per testare la connettività di rete tra SQL Server e Istanza gestita di SQL in SSMS, seguire questa procedura:

1. Connettersi all'istanza che sarà la replica primaria in SSMS.

2. In Esplora oggettiespandere i database e fare clic con il pulsante destro del mouse sul database che si intende collegare al database secondario. Selezionare attivitàcollegamento Istanza gestita di SQL di Azuretest connessione per aprire la guidata controllo di rete :

   

3. Selezionare Avanti nella pagina Introduzione della procedura guidata di controllo della rete .

4. Se tutti i requisiti vengono soddisfatti nella pagina Prerequisiti, selezionare Avanti. In caso contrario, risolvere eventuali prerequisiti non soddisfatti e quindi selezionare Ripeti la convalida.

5. Nella pagina di accesso selezionare Login per connettersi all'altra istanza che fungerà da replica secondaria. Selezionare Avanti.

6. Controllare i dettagli nella pagina Specificare le opzioni di rete e specificare un indirizzo IP, se necessario. Selezionare Avanti.

7. Nella pagina riepilogo esaminare le azioni eseguite dalla procedura guidata e quindi selezionare Fine per testare la connessione tra le due repliche.

8. Esaminare la pagina Risultati per convalidare l'esistenza della connettività tra le due repliche e quindi selezionare Chiudi per completare.

Per usare T-SQL per testare la connettività, è necessario controllare la connessione in entrambe le direzioni. Prima di tutto, testare la connessione da SQL Server a Istanza gestita di SQL e quindi testare la connessione da Istanza gestita di SQL a SQL Server.

Testare la connessione da SQL Server a un'istanza gestita di SQL

Usare SQL Server Agent su SQL Server per eseguire test di connettività da SQL Server a Istanza Gestita di SQL.

1. Connettersi a Istanza gestita di SQL ed eseguire lo script seguente per generare i parametri necessari in un secondo momento:

   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';
   

   I risultati dovrebbero essere simili all'esempio seguente:

    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'
   

   Salvare i risultati per usare i passaggi successivi. Poiché questi parametri possono cambiare dopo qualsiasi failover, assicurarsi di generarli di nuovo, se necessario.

2. Connettersi all'istanza di SQL Server.

3. Aprire una nuova finestra di query e incollare lo script seguente:

   --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. Sostituire i parametri @node, @porte @serverName con i valori ottenuti dal primo passaggio.

5. Eseguire lo script e controllare i risultati. Verranno visualizzati risultati come l'esempio seguente:

   

6. Verificare i risultati:

   • Il risultato di ogni test in TcpTestSucceeded deve essere TcpTestSucceeded : True.
   • RemoteAddresses devono appartenere all'intervallo IP della subnet di SQL Managed Instance.

   Se la risposta non riesce, verificare le impostazioni di rete seguenti:

   • Esistono regole sia nel firewall di rete che il firewall del sistema operativo host di SQL Server (Windows/Linux) che consente il traffico verso l'intero intervallo IP della subnet di Istanza gestita di SQL.
   • Esiste una regola del gruppo di sicurezza di rete che consente la comunicazione attraverso la porta 5022 per la rete virtuale che ospita SQL Managed Instance.

Testare la connessione da Istanza gestita di SQL a SQL Server

Per verificare che Istanza gestita di SQL possa raggiungere SQL Server, creare prima di tutto un endpoint di test. Usa quindi SQL Server Agent per eseguire uno script di PowerShell con il comando tnc che esegue il ping di SQL Server sulla porta 5022 a partire dall'istanza gestita di SQL.

Per creare un endpoint di test, connettersi a SQL Server ed eseguire lo script T-SQL seguente:

-- 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
    )

Per verificare che l'endpoint di SQL Server riceva le connessioni sulla porta 5022, eseguire il comando di PowerShell seguente nel sistema operativo host dell'istanza di SQL Server:

tnc localhost -port 5022

Un test riuscito mostra il TcpTestSucceeded : True. È quindi possibile procedere alla creazione di un processo di SQL Server Agent nell'istanza gestita di SQL per provare a testare l'endpoint di test di SQL Server sulla porta 5022 dall'istanza gestita di SQL.

Creare quindi un processo di SQL Server Agent nell'istanza gestita di SQL denominata NetHelper eseguendo lo script T-SQL seguente nell'istanza gestita di SQL. Sostituire:

• <SQL_SERVER_IP_ADDRESS> con l'indirizzo IP di SQL Server a cui è possibile accedere dall'istanza gestita di 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)';

Creare quindi una stored procedure ExecuteNetHelper che consente di eseguire l'attività e ottenere i risultati dalla sonda di rete. Eseguire lo script T-SQL seguente nell'istanza gestita di 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;

Esegui la seguente query sull'istanza gestita di SQL per eseguire la stored procedure che avvierà il job dell'agente NetHelper e mostrerà il log risultante:

-- Run on managed instance
EXEC ExecuteNetHelper;

Se la connessione ha esito positivo, il log visualizza True. Se la connessione non è riuscita, il log visualizza False.

Se la connessione non è riuscita, verificare gli elementi seguenti:

• Il firewall nell'istanza di SQL Server host consente la comunicazione in ingresso e in uscita sulla porta 5022.
• Una regola del gruppo di sicurezza di rete per la rete virtuale che ospita un'istanza SQL gestita consente la comunicazione sulla porta 5022.
• Se l'istanza di SQL Server si trova in una VM di Azure, una regola del gruppo di sicurezza di rete consente la comunicazione sulla porta 5022 nella rete virtuale che ospita la VM.
• SQL Server è in esecuzione.
• Esiste un endpoint di test in SQL Server.

Dopo aver risolto i problemi, eseguire di nuovo il probe di rete NetHelper eseguendo EXEC ExecuteNetHelper nell'istanza gestita.

Infine, dopo aver completato il test di rete, eliminare l'endpoint di test e il certificato in SQL Server usando i comandi T-SQL seguenti:

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