Vínculo de solución de problemas: Instancia administrada de Azure SQL

La supervisión de vínculos está disponible a partir de SQL Server Management Studio (SSMS) 21.0 (versión preliminar).

Para comprobar el estado del vínculo en SSMS, siga estos pasos:

1. Conéctese a una réplica que hospede el vínculo.

2. En el Explorador de objetos, expanda Alta disponibilidad AlwaysOn y después, expanda Grupos de disponibilidad.

3. Haga clic con el botón derecho en el nombre del vínculo y seleccione Propiedades para abrir la ventana Propiedadesdel vínculo :

   

4. La ventana Propiedades del vínculo muestra información útil sobre el vínculo, como la información de réplica, el estado del vínculo y la fecha de expiración del certificado de punto de conexión:

   

Use T-SQL para determinar el estado del vínculo durante la fase de propagación o después de que se inicie la sincronización de datos.

Utilice la siguiente consulta de T-SQL para determinar el estado del vínculo durante la fase de inicialización en SQL Server o Instancia Administrada de SQL que hospeda la base de datos sembrada a través del vínculo.

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 consulta no devuelve ningún resultado, el proceso de propagación no se ha iniciado o ya se ha completado.

Use la siguiente consulta de T-SQL en la instancia principal para comprobar el estado del vínculo una vez que comience la sincronización de datos:

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 consulta devuelve los siguientes valores posibles:

• sin resultado: la consulta se ejecutó en la instancia secundaria.
• HEALTHY: el enlace está en buen estado y los datos se están sincronizando entre las réplicas.
• NOT_HEALTHY: El vínculo es deficiente y los datos no se sincronizan entre las réplicas.

Use Get-AzSqlInstanceLink para obtener información de estado de vínculo con PowerShell.

Ejecute el código de ejemplo siguiente en Azure Cloud Shell o instale el módulo Az.SQL localmente.

$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

Use az sql mi link show para obtener información de estado de vínculo con la CLI de Azure.

# 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  

Para probar la conectividad de red entre SQL Server y SQL Managed Instance en SSMS, siga estos pasos:

1. Conéctese a la instancia que será la réplica principal en SSMS.

2. En Explorador de objetos, expanda bases de datos y haga clic con el botón derecho en la base de datos que quiere vincular con la base de datos secundaria. Seleccione el vínculo Tareas>Azure SQL Managed Instance>Prueba de conexión para abrir el asistente Comprobador de red:

   

3. Seleccione Siguiente en la página Introducción del asistente de Network Checker.

4. Si se cumplen todos los requisitos en la página Requisitos previos, seleccione Siguiente. De lo contrario, resuelva los requisitos previos no satisfechos y, a continuación, seleccione Volver a ejecutar validación.

5. En la página Inicio de sesión, seleccione Iniciar sesión para conectarse a la otra instancia que será la réplica secundaria. Seleccione Siguiente.

6. Compruebe los detalles de la página Especificar opciones de red y proporcione una dirección IP, si es necesario. Seleccione Siguiente.

7. En la página Resumen, revise las acciones que realiza el asistente y, a continuación, seleccione Finalizar para probar la conexión entre las dos réplicas.

8. Revise la página Resultados para validar que existe conectividad entre las dos réplicas y, a continuación, seleccione Cerrar para finalizar.

Para usar T-SQL para probar la conectividad, debe comprobar la conexión en ambas direcciones. En primer lugar, pruebe la conexión de SQL Server a SQL Managed Instance y, a continuación, pruebe la conexión de SQL Managed Instance a SQL Server.

Prueba de conexión desde SQL Server a SQL Managed Instance

Use el Agente SQL Server en SQL Server para ejecutar pruebas de conectividad de SQL Server a SQL Managed Instance.

1. Conéctese a SQL Managed Instance y ejecute el siguiente script para generar algunos parámetros que necesitaremos más adelante:

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

   Los resultados deberían parecerse a lo siguiente:

    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'
   

   Guarde los resultados para utilizarlos en los pasos siguientes. Dado que estos parámetros pueden cambiar después de cualquier conmutación por error, asegúrese de generarlos de nuevo, si es necesario.

2. Conéctese a la instancia de SQL Server.

3. Abra una ventana de consulta nueva y pegue el script siguiente:

   --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. Reemplace los parámetros @node, @port y @serverName por los valores que obtuvo del primer paso.

5. Ejecute el script y compruebe los resultados. Debería ver resultados parecidos a este ejemplo:

   

6. Compruebe los resultados:

   • El resultado de cada prueba en TcpTestSucceededed debe ser TcpTestSucceeded : True.
   • RemoteAddresses debe pertenecer al intervalo IP de la subred SQL Managed Instance.

   Si la respuesta no es correcta, compruebe la siguiente configuración de red:

   • Hay reglas en el firewall de red y en el firewall del sistema operativo (Windows o Linux) del host de SQL Server que permiten el tráfico a todo el intervalo IP de la subred de SQL Managed Instance.
   • Hay una regla de NSG que permite la comunicación en el puerto 5022 para la red virtual que hospeda SQL Managed Instance.

Prueba de conexión desde SQL Managed Instance a SQL Server

Para comprobar que SQL Managed Instance puede acceder a SQL Server, primero debe crear un punto de conexión de prueba. A continuación, use el Agente SQL Server para ejecutar un script de PowerShell con el comando tnc que haga ping a SQL Server en el puerto 5022 desde la instancia administrada de SQL.

Para crear un punto de conexión de prueba, conéctese a SQL Server y ejecute el siguiente script de 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
    )

Para comprobar que el punto de conexión de SQL Server está recibiendo conexiones en el puerto 5022, ejecute el siguiente comando de PowerShell en el sistema operativo del host de la instancia de SQL Server:

tnc localhost -port 5022

Una prueba correcta muestra TcpTestSucceeded : True. A continuación, podemos continuar con la creación de un trabajo del Agente SQL Server en una instancia administrada de SQL para intentar probar el punto de conexión de prueba de SQL Server en el puerto 5022 desde la instancia administrada de SQL.

A continuación, cree un trabajo del Agente SQL Server en la instancia administrada de SQL llamada NetHelper mediante la ejecución del siguiente script de T-SQL en la instancia administrada de SQL. Reemplazar:

• <SQL_SERVER_IP_ADDRESS> con la dirección IP de SQL Server a la que se puede acceder desde la instancia administrada de 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)';

Después, cree un procedimiento almacenado ExecuteNetHelper que ayude a ejecutar el trabajo y obtener resultados del sondeo de red. Ejecute el siguiente script de T-SQL en la instancia administrada de 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;

Ejecute la siguiente consulta en la instancia administrada de SQL para ejecutar el procedimiento almacenado que ejecutará el trabajo del agente de NetHelper y mostrará el registro resultante:

-- Run on managed instance
EXEC ExecuteNetHelper;

Si la conexión ha sido correcta, el registro muestra True. Si la conexión no ha sido correcta, el registro muestra False.

Si la conexión no fue correcta, compruebe los siguientes elementos:

• El firewall del host de la instancia de SQL Server permite la comunicación entrante y saliente en el puerto 5022.
• Hay una regla de NSG para la red virtual que hospeda SQL Managed Instance que permite la comunicación en el puerto 5022.
• Si la instancia de SQL Server está en una máquina virtual de Azure, una regla de NSG permite la comunicación en el puerto 5022 en la red virtual que hospeda la máquina virtual.
• SQL Server se está ejecutando.
• Existe un punto de conexión de prueba en SQL Server.

Después de resolver problemas, vuelva a ejecutar el sondeo de red netHelper mediante la ejecución EXEC ExecuteNetHelper en la instancia administrada.

Por último, después de que la prueba de red re haya realizado correctamente, elimine el punto de conexión de prueba y el certificado de SQL Server con los siguientes comandos de T-SQL:

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