Lien de dépannage – Azure SQL Managed Instance

La surveillance des liens est disponible à partir de SQL Server Management Studio (SSMS) 21.0 (préversion).

Pour vérifier l’état du lien dans SSMS, procédez comme suit :

1. Connectez-vous à une réplique qui héberge le lien.

2. Dans l’Explorateur d’objets, développez Haute disponibilité Always On, puis Groupes de disponibilité.

3. Cliquez avec le bouton droit sur le nom du lien, puis sélectionnez Propriétés pour ouvrir la fenêtre Propriétés du lien :

   

4. La fenêtre Propriétés du lien affiche des informations utiles sur le lien, telles que les informations de réplica, l’état du lien et la date d’expiration du certificat de point de terminaison :

   

Utilisez T-SQL pour déterminer l’état du lien pendant la phase d’amorçage ou après le début de la synchronisation des données.

Utilisez la requête T-SQL suivante pour déterminer l’état du lien pendant la phase d’amorçage sur SQL Server ou SQL Managed Instance qui héberge la base de données amorçage via le lien :

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

Si la requête ne retourne aucun résultat, le processus d’amorçage n’a pas démarré ou n’a pas déjà terminé.

Utilisez la requête T-SQL suivante sur l’instance principale pour vérifier l’intégrité du lien une fois la synchronisation des données commencée :

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 requête retourne les valeurs possibles suivantes :

• aucun résultat : la requête a été exécutée sur l’instance secondaire.
• HEALTHY: Le lien est sain et les données sont synchronisées entre les répliques.
• NOT_HEALTHY: le lien n’est pas sain et les données ne se synchronisent pas entre les réplicas.

Utilisez Get-AzSqlInstanceLink pour obtenir des informations d’état de lien avec PowerShell.

Exécutez l’exemple de code suivant dans Azure Cloud Shell ou installez le module Az.SQL localement.

$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

Utilisez az sql mi link show pour obtenir des informations d’état de lien avec 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  

Pour tester la connectivité réseau entre SQL Server et SQL Managed Instance dans SSMS, procédez comme suit :

1. Connectez-vous à l’instance qui sera le réplica principal dans SSMS.

2. Dans l’Explorateur d’objets, développez les bases de données et faites un clic droit sur la base de données que vous souhaitez lier à la réplica secondaire. Sélectionnez Tâches>liaison Azure SQL Managed Instance> Tester la connexion pour ouvrir l’Assistant Network Checker :

   

3. Sélectionnez Suivant sur la page Introduction de l’Assistant Network Checker.

4. Si toutes les conditions requises sont remplies sur la page Conditions préalables, sélectionnez Suivant. Dans le cas contraire, résolvez toutes les conditions préalables non remplies, puis sélectionnez Réexécuter la validation.

5. Sur la page Connexion, sélectionnez Connexion pour vous connecter à l’autre instance qui sera la réplica secondaire. Cliquez sur Suivant.

6. Vérifiez les détails de la page Spécifier les options de réseau et fournissez une adresse IP, si nécessaire. Cliquez sur Suivant.

7. Sur la page Résumé, passez en revue les actions effectuées par l’Assistant, puis sélectionnez Terminer pour tester la connexion entre les deux réplicas.

8. Passez en revue la page Résultats pour valider la connectivité qui existe entre les deux réplicas, puis sélectionnez Fermer pour terminer.

Pour utiliser T-SQL afin de tester la connectivité, vous devez vérifier la connexion dans les deux sens. Testez d’abord la connexion de SQL Server à SQL Managed Instance, puis la connexion de SQL Managed Instance à SQL Server.

Tester la connexion entre SQL Server et SQL Managed Instance

Utilisez SQL Server Agent sur SQL Server pour exécuter des tests de connectivité de SQL Server vers SQL Managed Instance.

1. Connectez-vous à SQL Managed Instance et exécutez le script suivant pour générer les paramètres dont vous aurez besoin ultérieurement :

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

   Les résultats devraient ressembler à l’échantillon suivant :

    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'
   

   Enregistrez les résultats à utiliser dans les étapes suivantes. Comme ces paramètres peuvent changer après un basculement, veillez à les générer à nouveau, si nécessaire.

2. Connectez-vous à votre instance SQL Server.

3. Ouvrez une nouvelle fenêtre Requête et collez le script suivant :

   --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. Remplacez les paramètres @node, @port et @serverName avec les valeurs obtenues à la première étape.

5. Exécutez le script et vérifiez les résultats. Vous devriez obtenir des résultats comme ceux de l’échantillon suivant :

   

6. Vérifiez les résultats :

   • Le résultat de chaque test doit être TcpTestSucceeded : True pour TcpTestSucceeded.
   • Les adresses RemoteAddresses doivent être comprises dans la plage d’adresses IP du sous-réseau SQL Managed Instance.

   Si la réponse échoue, vérifiez les paramètre réseau suivants :

   • Il existe des règles dans le pare-feu réseau et le pare-feu du système d’exploitation (Windows/Linux) hôte de SQL Server qui autorisent le trafic vers la plage d’adresses IP de sous-réseau entière de SQL Managed Instance.
   • Il existe une règle NSG qui autorise la communication sur le port 5022 pour le réseau virtuel qui héberge SQL Managed Instance.

Tester la connexion entre SQL Managed Instance et SQL Server

Pour vérifier que SQL Managed Instance peut atteindre SQL Server, créez d’abord un point de terminaison de test. Ensuite, utilisez SQL Server Agent pour exécuter un script PowerShell avec la commande tnc qui effectue un test Ping sur SQL Server sur le port 5022 à partir de SQL Managed Instance.

Pour créer un point de terminaison de test, connectez-vous à SQL Server et exécutez le script (T-SQL) suivant :

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

Pour vérifier que le point de terminaison SQL Server reçoit des connexions sur le port 5022, exécutez la commande PowerShell suivante sur le système d’exploitation hôte de votre instance de SQL Server :

tnc localhost -port 5022

Les tests réussis affichent TcpTestSucceeded : True. Vous pouvez ensuite créer une tâche SQL Server Agent sur SQL Managed Instance pour essayer de tester le point de terminaison de test SQL Server sur le port 5022 à partir de SQL Managed Instance.

Puis, créez une tâche SQL Server Agent sur SQL Managed Instance appelée NetHelper en exécutant le script T-SQL suivant sur SQL Managed Instance. Remplacez :

• <SQL_SERVER_IP_ADDRESS> par l’adresse IP de SQL Server accessible à partir de SQL Managed Instance.

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

Ensuite, créez une procédure stockée ExecuteNetHelper qui facilite l’exécution du travail et recueille les résultats de la sonde réseau. Exécutez le script T-SQL suivant sur SQL Managed Instance :

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

Exécutez la requête suivante sur SQL Managed Instance pour exécuter la procédure stockée qui exécutera la tâche de l’agent NetHelper, et affichera le journal obtenu :

-- Run on managed instance
EXEC ExecuteNetHelper;

Si la connexion aboutit, le journal affiche True. Si la connexion n’aboutit pas, le journal affiche False.

Si la connexion n’aboutit pas, vérifiez les points suivants :

• Le pare-feu sur l’instance de SQL Server hôte autorise les communications entrantes et sortantes sur le port 5022.
• Une règle NSG pour le réseau virtuel qui héberge l’instance SQL Managed Instance autorise la communication sur le port 5022.
• Si votre instance de SQL Server se trouve sur une machine virtuelle Azure, une règle NSG autorise la communication sur le port 5022 sur le réseau virtuel qui héberge la machine virtuelle.
• SQL Server est en cours d’exécution.
• Il existe un point de terminaison de test sur SQL Server.

Après avoir résolu les problèmes, réexécutez la sonde réseau NetHelper en exécutant EXEC ExecuteNetHelper sur une instance gérée.

Enfin, une fois test réseau réussi, supprimez le point de terminaison de test et le certificat sur SQL Server en utilisant les commandes T-SQL suivantes :

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