Risoluzione dei problemi relativi al collegamento - Azure SQL Managed Instance

Applica a:Azure SQL Managed Instance

Questo articolo illustra come monitorare e risolvere i problemi relativi a un link tra SQL Server e Azure SQL Managed Instance.

È possibile controllare lo stato del collegamento con Transact-SQL (T-SQL), Azure PowerShell o il Azure CLI. Se si verificano problemi, è possibile usare i codici di errore per risolvere il problema.

Molti problemi relativi alla creazione del collegamento possono essere risolti controllando la rete tra le due istanze e convalidando che l'ambiente sia stato preparato correttamente per il collegamento.

Inserimento iniziale

Quando si stabilisce un collegamento tra SQL Server e Azure SQL Managed Instance, è presente una fase iniziale di seeding prima dell'avvio della replica dei dati. La fase iniziale del seeding è la parte più lunga e più costosa dell'operazione. Una volta completato il seeding iniziale, i dati vengono sincronizzati e vengono replicate solo le modifiche ai dati successive. Il tempo necessario per il completamento del seeding iniziale dipende dalle dimensioni dei dati, dall'intensità del carico di lavoro nei database primari e dalla velocità del collegamento tra reti delle repliche primarie e secondarie.

Se la velocità del collegamento tra le due istanze è più lenta rispetto a quella necessaria, è probabile che il tempo di inizializzazione sia notevolmente influenzato. È possibile usare la velocità di seeding dichiarata, le dimensioni totali dei dati e la velocità di collegamento per stimare il tempo necessario per la fase iniziale di seeding prima dell'avvio della replica dei dati. Ad esempio, per un singolo database da 100 GB, la fase di inizializzazione iniziale richiederà circa 1,2 ore se il collegamento è in grado di eseguire il push di 84 GB all'ora e se non sono presenti altri database sottoposti a seeding in un collegamento diverso. Se il collegamento può trasferire solo 10 GB all'ora, il seeding di un database da 100 GB può richiedere circa 10 ore. Se sono presenti più database da replicare tramite più collegamenti, il seeding verrà eseguito in parallelo e, in combinazione con una velocità di collegamento lenta, la fase di seeding iniziale potrebbe richiedere molto più tempo, soprattutto se il seeding parallelo dei dati di tutti i database supera la larghezza di banda del collegamento disponibile.

La fase di seeding iniziale non è resiliente a interruzioni di rete né a operazioni di manutenzione dell'istanza o di failover. Se la connettività bidirezionale tra SQL Server e SQL Managed Instance viene temporaneamente persa o se SQL Server o SQL Managed instance viene riavviata o ne viene eseguito il failover durante la fase di seeding iniziale, il seeding viene riavviato.

Importante

La fase iniziale di seeding può richiedere giorni con collegamenti a velocità estremamente bassa o molto occupati. In questo caso, la creazione del collegamento può andare in timeout. La creazione del collegamento viene annullata automaticamente dopo 6 giorni.

Controllare lo stato del collegamento

Se si verificano problemi con un collegamento, è possibile usare SQL Server Management Studio (SSMS), Transact-SQL (T-SQL), Azure PowerShell o il Azure CLI per ottenere informazioni sullo stato corrente del collegamento.

Usare T-SQL per informazioni rapide sullo stato del collegamento e quindi usare Azure PowerShell o il Azure CLI per informazioni complete sullo stato corrente del collegamento.

Il monitoraggio dei collegamenti è disponibile a partire da SQL Server Management Studio (SSMS) 21.0 (anteprima).

Per controllare lo stato del collegamento in SSMS, seguire questa procedura:

Connettersi a una replica che ospita il collegamento.
In Object Explorer, espandi Always On High Availability, quindi espandi Gruppi di disponibilità.
Fare clic con il pulsante destro del mouse sul nome del collegamento e quindi scegliere Proprietà per aprire la finestra Proprietàcollegamento :
Nella finestra Proprietà collegamento vengono visualizzate informazioni utili sul collegamento, ad esempio informazioni sulla replica, stato del collegamento e data di scadenza del certificato dell'endpoint:

Usare T-SQL per determinare lo stato del collegamento durante la fase di seeding o dopo l'inizio della sincronizzazione dei dati. La DMV sys.dm_hadr_physical_seeding_stats può essere usata per tenere traccia dello stato iniziale del seeding. La estimate_time_complete_utc colonna è basata sulle dimensioni dei dati rimanenti correnti transfer_rate_bytes_per_second e non compresse (quando is_compression_enabled = 0). Per il collegamento Managed Instance, viene utilizzata la compressione dei dati, quindi ci si aspetta che estimate_time_complete_utc sia una sovrastima.

Usare la query T-SQL seguente per determinare lo stato del collegamento durante la fase di seeding del SQL Server o SQL Managed Instance 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 il 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

Il valore replicaState descrive il collegamento corrente. Se lo stato include anche Errore, si è verificato un errore durante l'operazione elencata nello stato . Ad esempio, LinkCreationError indica che si è verificato un errore durante la creazione del collegamento.

Alcuni possibili valori replicaState sono:

CreatingLink: seeding iniziale
LinkSynchronizing: La replica dei dati è in corso
LinkFailoverInProgress: Il failover è in corso

Per un elenco completo delle proprietà dello stato del collegamento, vedere il comando Distributed Availability Groups - GET API REST.

Timeout del failover pianificato

Se la replica secondaria non riesce a tenere il passo con le modifiche alla replica primaria e rimane indietro, il failover pianificato può andare in timeout e fallire con un errore.

Per risolvere il problema, seguire la procedura seguente:

Controllare il ritardo di replica tra le due istanze.
Se il ritardo di replica è elevato, attendere che la replica secondaria raggiunga lo stesso stato della replica primaria. Potrebbe essere necessario eseguire passaggi aggiuntivi per la risoluzione dei problemi se il ritardo persiste, ad esempio la sospensione dei carichi di lavoro nella replica primaria, il miglioramento della velocità effettiva di rete tra le due istanze o l'aumento della capacità delle risorse nella replica secondaria.
- Il modo più semplice per arrestare i carichi di lavoro in una replica primaria SQL Server consiste nel tagliare le connessioni dell'applicazione all'istanza.
Dopo che la replica secondaria ha raggiunto la replica primaria, provare nuovamente a eseguire il pianificato failover.

Errori durante l'inizializzazione di un collegamento

L'errore seguente può verificarsi durante l'inizializzazione di un collegamento (stato del collegamento: LinkInitError):

Errore 41962: Operazione interrotta perché il collegamento non è stato avviato entro 5 minuti. Verificare la connettività di rete e riprovare.
Error 41973: Impossibile stabilire il collegamento perché il certificato d'endpoint di SQL Server non è stato importato correttamente in Azure SQL Managed Instance.
Error 41974: impossibile stabilire il collegamento perché il certificato dell'endpoint da SQL Managed Instance non è stato importato correttamente in SQL Server.
Errore 41976: il gruppo di disponibilità non risponde. Controllare i nomi e i parametri di configurazione e riprovare.
Errore 41986: Impossibile stabilire il collegamento perché la connessione non è riuscita o la replica secondaria non risponde. Controllare i nomi, i parametri di configurazione e la connettività di rete , quindi riprovare.
Errore 47521: Impossibile stabilire il collegamento perché il server secondario non ha ricevuto la richiesta. Verificare che il gruppo di disponibilità e i database siano integri nel server primario e riprovare.

Errori durante la creazione di un collegamento

Durante la creazione di un collegamento (stato collegamento) possono verificarsi gli errori seguenti: LinkCreationError:

Errore 41977: il database di destinazione non risponde. Controllare i parametri di collegamento e riprovare.
Troncamento del log prematuro: se il log delle transazioni viene troncato prima del completamento del seeding iniziale, è probabile che venga visualizzato uno degli errori seguenti:
- Errore 1408: la replica nel database remoto non è riuscita e non può essere ripristinata a causa di una sovrapposizione di file di log mancante tra la replica primaria e quella secondaria. Eliminare il collegamento esistente e creare un nuovo collegamento per riavviare la replica.
- Errore 1412: La replica nel database remoto non è riuscita e non può essere ripristinata a causa di una mancata corrispondenza delle dimensioni del file di log con la replica primaria. Eliminare il collegamento esistente e creare un nuovo collegamento per riavviare la replica.

Errore 1412

Quando si crea un collegamento, la prima parte del processo esegue il seeding di un backup completo del database dalla replica primaria alla replica secondaria. Al termine del seeding del backup completo, il collegamento inizia a replicare i dati applicando dati differenziali dalla replica primaria alla replica secondaria. Questo processo continua a tempo indeterminato fino a quando non viene eseguito un comando di failover o il collegamento viene rimosso.

Se si verifica un backup del log delle transazioni nella replica primaria durante l'inizializzazione del seeding del backup completo, il log delle transazioni viene troncato. La creazione del collegamento ha esito negativo e viene visualizzato l'errore 1412 perché i dati nel log delle transazioni necessari per il seeding iniziale non sono più disponibili. Se viene visualizzato l'errore 1412 nel log degli errori di SQL Server Azure SQL Managed Instance, è necessario drop e ricreare il collegamento.

Per evitare preventivamente questo problema, sospendere i backup del log delle transazioni durante la fase iniziale di seeding.

Se i backup del log delle transazioni sono necessari durante la fase iniziale di seeding, in particolare per i database di dimensioni molto grandi, è possibile scegliere di impedire manualmente il troncamento del log o automatizzare il processo con uno script T-SQL per sospendere automaticamente i backup del log in fasi critiche e quando è sicuro.

Prevenzione manuale del troncamento del log

I passaggi di questa sezione illustrano come aprire una transazione su una tabella fittizia per impedire il troncamento del log durante la fase iniziale di seeding. Questo processo richiede il monitoraggio manuale dello stato di avanzamento del seeding e un'attenta tempistica per garantire che il troncamento del log venga impedito fino al completamento del seeding.

Monitorare i progressi del processo di seeding. È possibile usare la query T-SQL seguente per monitorare lo stato di avanzamento del seeding iniziale:
```
SELECT * FROM sys.dm_exec_requests WHERE database_id = @dbId AND command = 'VDI_CLIENT_WORKER'
```
Quando il seeding si avvicina al 90 % di completamento, eseguire un BEGIN TRAN comando su una tabella fittizia senza un commit, mantenendo aperta una transazione e impedire il troncamento del log.
Monitorare attentamente lo spazio su disco del log delle transazioni per assicurarsi che non superi la capacità di archiviazione mentre la transazione è aperta.
Quando il seeding raggiunge 100%, completare la transazione con COMMIT TRAN.

Ad esempio, ogni volta che il seeding iniziale raggiunge 90%, eseguire il comando seguente per creare una tabella fittizia allo scopo di evitare l'errore 1412:

-- Create table
CREATE TABLE Prevent1412
(
    Id        INT,
    CreatedAt DATETIME
);

Quindi, quando il seeding si avvicina al completamento, eseguire il comando seguente per evitare il troncamento dei log:

BEGIN TRAN;

INSERT INTO Prevent1412 (Id, CreatedAt)
VALUES (1, GETDATE());

Attenzione

Dopo l'inizio della transazione, il log delle transazioni non viene più troncato, quindi monitorate attentamente lo spazio su disco del log delle transazioni affinché non superi la capacità di archiviazione mentre la transazione è aperta.

Al termine del seeding, è possibile eseguire il commit della transazione per troncare il log:

COMMIT TRAN;

Al termine della migrazione, è possibile eliminare la tabella fittizia:

DROP TABLE Prevent1412;

Automatizzare il backup dei log con pausa automatica

In alternativa, è possibile automatizzare il processo per sospendere automaticamente i backup del log in fasi critiche quando è sicuro con uno script T-SQL.

È possibile eseguire lo script seguente prima dell'inizio della migrazione:

-- Get last backup date
SELECT TOP 1 @lastBackupTime = b.backup_finish_date
FROM master.sys.sysdatabases d 
LEFT OUTER JOIN msdb..backupSET b
ON b.database_name = d.name
AND b.type = 'L'
WHERE d.name = @dbName
ORDER BY backup_finish_date DESC
SELECT @diffInMins = DATEDIFF(minute, @lastBackupTime, CURRENT_TIMESTAMP);

-- Get database id and group database id
SELECT @agDbId = group_database_id, @dbId = database_id FROM sys.databases WHERE name = @dbName

-- If there is no group database id, no need for checks
IF (@agDbId IS NOT NULL)
BEGIN
              -- Get last seeding start time and check if backup (VDI client) is actually running
              SELECT TOP 1 @seedingStartTime = start_time, @state = current_state, @agDbId = ag_db_id FROM sys.dm_hadr_automatic_seeding ORDER BY start_time DESC

              IF (@state = 'PENDING' OR @state = 'CHECK_IF_SEEDING_NEEDED' OR @state = 'LIMIT_CONCURRENT_BACKUPS')
                             SET @seedingStarting = 1
              ELSE
                             SET @seedingStarting = 0
              
              SELECT @backupWorkers = COUNT(*) FROM sys.dm_exec_requests WHERE database_id = @dbId AND command = 'VDI_CLIENT_WORKER'

              -- Check if seeding is done by looking at remote replica state and health
              SELECT TOP 1 @db_state = synchronization_state_desc, @db_health = synchronization_health_desc FROM sys.dm_hadr_database_replica_states WHERE database_id = @dbId AND is_local = 0

              IF (@db_state = N'SYNCHRONIZING' AND @db_health = N'HEALTHY')
              SET @seedingDone = 1
              ELSE
              SET @seedingDone = 0
END

-- If X minutes has passed since last log backup, do it, we don't want to wait anymore
IF (@alreadyFailed = 1 or @diffInMins > {set_minutes})
BEGIN
              {do_log_backup}
              SET @alreadyFailed = 1
              CONTINUE;
END

-- If seeding has started and finished take log backups
IF ((@agDbId IS NOT NULL) AND (@seedingStartTime IS NOT NULL) AND (@startTime < @seedingStartTime) AND (@seedingDone = 1))
BEGIN
              {do_log_backup}
              SET @alreadyFailed = 1
              CONTINUE;
END

-- If database is not in ag or
-- If seeding has not started or
-- If seeding is ongoing 
-- Take log backups
IF ((@agDbId IS NULL) OR (@seedingStartTime IS NULL) OR (@startTime > @seedingStartTime) OR (@seedingStarting = 1) or (@backupWorkers > 0))
BEGIN
              {do_log_backup}
              CONTINUE;
END

Stato incoerente dopo il failover forzato

Dopo un failover forzato , è possibile che si verifichi uno scenario di split-brain in cui entrambe le repliche assumono il ruolo primario, lasciando il collegamento in uno stato incoerente. Ciò può verificarsi se si esegue il failover nella replica secondaria durante un disastro e quindi la replica primaria torna online.

Prima di tutto, verificare di essere in uno scenario split-brain. A tale scopo, è possibile usare SQL Server Management Studio (SSMS) o Transact-SQL (T-SQL).

Collegarsi sia a SQL Server che a un'istanza gestita di SQL in SSMS, quindi in Object Explorer espandere Repliche di disponibilità nel Availability group nel nodo Always On High Availability. Se due repliche diverse sono elencate come (primario), si è in uno scenario split-brain.

In alternativa, è possibile eseguire lo script T-SQL seguente in both SQL Server e SQL Managed Instance per controllare il ruolo delle repliche:

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

Se entrambe le istanze elencano PRIMARY nella colonna ruolo del collegamento, si è in uno scenario di split-brain.

Per risolvere lo stato del cervello diviso, eseguire prima un backup sulla replica primaria originale. Se il database primario originale è stato SQL Server, eseguire un backup del log tail. Se il database primario originale è stato SQL Managed Instance, eseguire un backup completo copy-only. Al termine del backup, impostare il gruppo di disponibilità distribuito sul ruolo secondario per la replica che era la primaria originale, ma che sarà ora la nuova replica secondaria.

Ad esempio, in caso di vera emergenza, presupponendo che sia stato forzato un failover del carico di lavoro SQL Server a Azure SQL Managed Instance e si intende continuare a eseguire il carico di lavoro in SQL Managed Instance, eseguire un backup della parte finale del log in SQL Servere quindi impostare il gruppo di disponibilità distribuito sul ruolo secondario in SQL Server, ad esempio l'esempio seguente:

--Execute on SQL Server 
USE master
ALTER AVAILABILITY GROUP [<DAGName>] 
SET (ROLE = SECONDARY) 
GO

Eseguire quindi un failover manuale pianificato da SQL Managed Instance a SQL Server usando il collegamento, ad esempio l'esempio seguente:

--Execute on SQL Managed Instance 
USE master
ALTER AVAILABILITY GROUP [<DAGName>] FAILOVER 
GO

Certificato scaduto

È possibile che il certificato usato per il collegamento scada. Se il certificato scade, il collegamento non riesce. Per risolvere questo problema, ruotare il certificato.

Problemi noti dopo la migrazione a SQL Managed Instance

Considerare i problemi noti seguenti dopo la migrazione a Azure SQL Managed Instance:

Errori dell'operazione di ripristino dopo la migrazione a SQL Managed Instance

Se si esegue la migrazione di un database ad Azure SQL Managed Instance da SQL Server 2019 e versioni successive con ripristino accelerato del database abilitato, ma configurato con l'archivio delle versioni permanente impostato su un valore diverso dal gruppo di file PRIMARY, potrebbero verificarsi errori durante le operazioni di ripristino nell'istanza gestita SQL di destinazione.

Per risolvere questo problema, assicurarsi di impostare l'archivio delle versioni persistent version store (PVS) su PRIMARY nel database di SQL Server di origine prima di eseguirne la migrazione a SQL Managed Instance. Se è già stata eseguita la migrazione del database senza impostare pvS su PRIMARY, è possibile impostarla nel database di origine SQL Server e quindi eseguire nuovamente la migrazione del database a SQL Managed Instance.

Impossibile usare il ripristino accelerato del database dopo la migrazione a SQL Managed Instance

A partire da SQL Server 2019, se si esegue la migrazione di un database su Azure SQL Managed Instance e il database di origine ha il ripristino di database accelerato disabilitato, non è possibile usare il ripristino di database accelerato nell'istanza gestita di destinazione.

Per risolvere questo problema, assicurarsi di abilitare il ripristino accelerato del database nel database di SQL Server di origine prima di eseguirne la migrazione a SQL Managed Instance. Se è già stata eseguita la migrazione del database senza abilitare il ripristino accelerato del database, è possibile abilitarlo nel database di SQL Server di origine e quindi eseguire nuovamente la migrazione del database all'istanza gestita di SQL.

SQL Server 2017 e versioni precedenti non supportano il ripristino accelerato del database, quindi questo problema non si applica ai database migrati da tali versioni di SQL Server.

Impossibile usare Service Broker dopo la migrazione a SQL Managed Instance

Se si esegue la migrazione di un database a Azure SQL Managed Instance e Service Broker è disabilitato nel database di origine, non è possibile usare Service Broker nell'istanza gestita di SQL di destinazione.

Per risolvere questo problema, assicurarsi di abilitare Service Broker nel database di origine SQL Server prima di eseguirne la migrazione a SQL Managed Instance. Se è già stata eseguita la migrazione del database senza abilitare Service Broker, è possibile abilitarlo nel database di SQL Server di origine e quindi eseguire nuovamente la migrazione del database a SQL Managed Instance.

Testare la connettività di rete

La connettività di rete bidirezionale tra SQL Server e SQL Managed Instance è necessaria per il funzionamento del collegamento. Dopo aver aperto le porte sul lato SQL Server e aver configurato una regola del gruppo di sicurezza di rete sul lato SQL Managed Instance, verificare la connettività usando SQL Server Management Studio (SSMS) o Transact-SQL.

Testare la rete creando un processo temporaneo di SQL Agent sia in SQL Server che in SQL Managed Instance per verificare la connessione tra le due istanze. Quando si utilizza lo strumento Verifica della Rete in SSMS, il job viene creato automaticamente e cancellato al completamento del test. È necessario eliminare manualmente il processo di SQL Agent se si testa la rete usando T-SQL.

Nota

L'esecuzione di script di PowerShell dal SQL Server Agent in SQL Server on Linux non è attualmente supportata, pertanto non è attualmente possibile eseguire Test-NetConnection dal processo di SQL Server Agent in SQL Server on Linux.

Per usare SQL Agent per testare la connettività di rete, sono necessari i requisiti seguenti:

L'utente che esegue il test deve avere autorizzazioni per creare un processo (sia come sysadmin che appartiene al ruolo SQLAgentOperator per msdb) sia su SQL Server che su SQL Managed Instance.
Il servizio SQL Server Agent deve essere in esecuzione su SQL Server. Poiché l'agente è attivato per impostazione predefinita in SQL Managed Instance, non è necessaria alcuna azione aggiuntiva.

Tenere presente quanto segue:

Per evitare falsi negativi, tutti i firewall lungo il percorso di rete devono consentire il traffico ICMP (Internet Control Message Protocol).
Per evitare falsi positivi, tutti i firewall lungo il percorso di rete devono consentire il traffico sul protocollo proprietario SQL Server UCS. Il blocco del protocollo può portare a un test di connessione riuscito, ma non riesce a creare il collegamento.
Le configurazioni avanzate del firewall con protezioni a livello di pacchetto devono essere configurate correttamente per consentire correttamente il traffico tra SQL Server e SQL Managed Instance.

SSMS
T-SQL

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

Connettersi all'istanza che sarà la replica primaria in SSMS.
In Object Explorer espandere i database e fare clic con il pulsante destro del mouse sul database che si intende collegare al database secondario. Selezionare Tasks>Azure SQL Managed Instance link>Test Connection per aprire la procedura guidata Network Checker:
Selezionare Avanti nella pagina Introduzione della procedura guidata di controllo della rete .
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.
Nella pagina di accesso selezionare Login per connettersi all'altra istanza che fungerà da replica secondaria. Selezionare Avanti.
Controllare i dettagli nella pagina Specificare le opzioni di rete e specificare un indirizzo IP, se necessario. Selezionare Avanti.
Nella pagina riepilogo esaminare le azioni eseguite dalla procedura guidata e quindi selezionare Fine per testare la connessione tra le due repliche.
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 SQL Managed Instance e quindi testare la connessione da SQL Managed Instance a SQL Server.

Testare la connessione da SQL Server a SQL Managed Instance

Usare SQL Server Agent su SQL Server per eseguire test di connettività da SQL Server a SQL Managed Instance.

Connettersi a SQL Managed Instance 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.

Connettiti all'istanza di SQL Server.

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

Sostituire i parametri @node, @porte @serverName con i valori ottenuti dal primo passaggio.
Eseguire lo script e controllare i risultati. Verranno visualizzati risultati come l'esempio seguente:
Verificare i risultati:
- Il risultato di ogni test in TcpTestSucceeded deve essere TcpTestSucceeded : True.
- RemoteAddresses deve appartenere all'intervallo IP per la subnet SQL Managed Instance.
Se la risposta non riesce, verificare le impostazioni di rete seguenti:
- Esistono regole nel firewall di rete e nel firewall del sistema operativo host SQL Server (Windows/Linux) che consentono il traffico verso l'intero intervallo IP della sottorete di SQL Managed Instance.
- Esiste una regola del gruppo di sicurezza di rete che consente la comunicazione sulla porta 5022 per la rete virtuale che ospita SQL Managed Instance.

Testare la connessione da SQL Managed Instance a SQL Server

Per verificare che SQL Managed Instance possa raggiungere SQL Server, creare prima di tutto un endpoint di test. Usare quindi il SQL Server Agent per eseguire uno script di PowerShell con il comando tnc che esegue il ping SQL Server sulla porta 5022 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 SQL Server riceva le connessioni sulla porta 5022, eseguire il comando 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 SQL Server Agent nell'istanza gestita di SQL per provare a testare l'endpoint di test SQL Server sulla porta 5022 dall'istanza gestita di SQL.

Creare quindi un processo 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)';

Suggerimento

Se è necessario modificare l'indirizzo IP del proprio SQL Server per il probe di connettività dall'istanza SQL gestita, eliminare il job NetHelper eseguendo EXEC msdb.dbo.sp_delete_job @job_name=N'NetHelper' e ricreare il job NetHelper usando lo script precedente.

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.

Screenshot che mostra l'output previsto del processo di NetHelper SQL Agent.

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.
La regola del NSG per la rete virtuale che ospita SQL Managed Instance consente la comunicazione sulla porta 5022.
Se l'istanza di SQL Server si trova in una macchina virtuale Azure, una regola del gruppo di sicurezza di rete consente la comunicazione sulla porta 5022 nella rete virtuale che ospita la macchina virtuale.
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