Solucionar problemas de ligação - Instância SQL Gerida do Azure

Use o T-SQL para determinar o estado do link durante a fase de propagação ou após o início da sincronização de dados.

Use a seguinte consulta T-SQL para determinar o status do link durante a fase de propagação no SQL Server ou na Instância Gerenciada do SQL que hospeda o banco de dados semeado por meio do link:

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 a consulta não retornar nenhum resultado, o processo de propagação não foi iniciado ou já foi concluído.

Use a seguinte consulta T-SQL na instância primária de para verificar a integridade do link a partir do momento em que a sincronização de dados começa:

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

A consulta retorna os seguintes valores possíveis:

• sem resultado: a consulta foi executada na instância secundária.
• HEALTHY: O link está íntegro e os dados estão sendo sincronizados entre as réplicas.
• NOT_HEALTHY: A ligação não está estável e os dados não estão a sincronizar entre as réplicas.

Utilize Get-AzSqlInstanceLink para obter informações de estado do link com o PowerShell.

Execute o código de exemplo a seguir no Azure Cloud Shell ou instale o 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

Utilize az sql mi link show para obter informações sobre o estado do link usando a CLI do 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 testar a conectividade de rede entre o SQL Server e a Instância Gerenciada do SQL no SSMS, siga estas etapas:

1. Conecte-se à instância que será a réplica primária no SSMS.

2. No Explorador de Objetos, expanda os bancos de dados e clique com o botão direito no banco de dados que pretende vincular ao banco de dados secundário. Selecione Tarefas>Instância Gerenciada SQL do Azure>Testar Conexão para abrir o assistente Verificador de Rede:

   

3. Selecione Próximo na página de Introdução do assistente do Verificador de Rede.

4. Se todos os requisitos forem atendidos na página de Pré-requisitos , selecione Avançar. Caso contrário, resolva os pré-requisitos não atendidos e, em seguida, selecione Reexecutar Validação.

5. Na página de login , selecione a opção de login para se conectar à outra instância que será a réplica secundária. Selecione Avançar.

6. Verifique os detalhes na página Especificar Opções de Rede e forneça um endereço IP, se necessário. Selecione Avançar.

7. Na página Resumo, revise as ações executadas pelo assistente e selecione Concluir para testar a conexão entre as duas réplicas.

8. Revise a página Resultados para validar se existe conectividade entre as duas réplicas e selecione Fechar para concluir.

Para usar o T-SQL para testar a conectividade, você precisa verificar a conexão em ambas as direções. Primeiro, teste a conexão do SQL Server com a Instância Gerenciada do SQL e, em seguida, teste a conexão da Instância Gerenciada do SQL com o SQL Server.

Testar a conexão do SQL Server para o SQL Managed Instance

Use o SQL Server Agent no SQL Server para executar testes de conectividade do SQL Server para a Instância Gerenciada do SQL.

1. Conecte-se à Instância Gerenciada SQL e execute o seguinte script para gerar parâmetros necessários mais tarde:

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

   Os resultados devem ser semelhantes ao exemplo a seguir:

    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'
   

   Salve os resultados para usar as próximas etapas. Como esses parâmetros podem mudar após qualquer failover, certifique-se de gerá-los novamente, se necessário.

2. Conecte-se à sua instância do SQL Server.

3. Abra uma nova janela de consulta e cole o seguinte script:

   --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. Substitua os parâmetros @node, @porte @serverName pelos valores obtidos na primeira etapa.

5. Execute o script e verifique os resultados. Você deve ver resultados como o exemplo a seguir:

   

6. Verifique os resultados:

   • O resultado de cada teste no TcpTestSucceeded deve ser TcpTestSucceeded : True.
   • Os RemoteAddresses devem pertencer ao intervalo de IP da sub-rede da Instância Gerenciada SQL.

   Se a resposta não for bem-sucedida, verifique as seguintes configurações de rede:

   • Há regras no firewall de rede e firewall do sistema operacional host do SQL Server (Windows/Linux) que permitem o tráfego para todo o intervalo de IP da sub-rede da Instância Gerenciada do SQL.
   • Há uma regra NSG que permite a comunicação na porta 5022 para a rede virtual que hospeda a Instância Gerenciada SQL.

Testar a conexão da instância gerenciada do SQL para o SQL Server

Para verificar se a Instância Gerida do SQL consegue aceder ao SQL Server, comece por criar um ponto de extremidade de teste. Em seguida, use o SQL Server Agent para executar um script do PowerShell com o comando tnc para executar um ping ao SQL Server na porta 5022 a partir da instância gerida de SQL.

Para criar um ponto de extremidade de teste, conecte-se ao SQL Server e execute o seguinte script 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 verificar se o ponto de extremidade do SQL Server está a receber conexões na porta 5022, execute o seguinte comando do PowerShell no sistema operativo host da sua instância do SQL Server:

tnc localhost -port 5022

Um teste bem-sucedido mostra TcpTestSucceeded : True. Em seguida, você pode continuar a criar um trabalho do SQL Server Agent na instância gerenciada do SQL para tentar testar o ponto de extremidade de teste do SQL Server na porta 5022 da instância gerenciada do SQL.

Em seguida, crie um trabalho do SQL Server Agent na instância gerenciada do SQL chamada NetHelper executando o seguinte script T-SQL na instância gerenciada do SQL. Substituir:

• <SQL_SERVER_IP_ADDRESS> com o endereço IP do SQL Server que pode ser acessado a partir da instância gerenciada do 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)';

Em seguida, crie um procedimento armazenado ExecuteNetHelper que ajude a executar a tarefa e obtenha resultados da sonda de rede. Execute o seguinte script T-SQL na instância gerenciada do 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;

Execute a seguinte consulta na instância gerenciada SQL para executar o procedimento armazenado que executará o trabalho do agente NetHelper e mostrará o log resultante:

-- Run on managed instance
EXEC ExecuteNetHelper;

Se a conexão foi bem-sucedida, o log mostra True. Se a conexão não foi bem-sucedida, o log mostra False.

Se a conexão não foi bem-sucedida, verifique os seguintes itens:

• O firewall na instância do SQL Server host permite a comunicação de entrada e saída na porta 5022.
• Uma regra NSG para a rede virtual que hospeda a Instância Gerenciada SQL permite a comunicação na porta 5022.
• Se sua instância do SQL Server estiver em uma VM do Azure, uma regra NSG permitirá a comunicação na porta 5022 na rede virtual que hospeda a VM.
• O SQL Server está em execução.
• Existe um endpoint de teste no SQL Server.

Depois de resolver problemas, execute novamente a sonda de rede NetHelper executando EXEC ExecuteNetHelper na instância gerenciada.

Finalmente, após o teste de rede ser bem-sucedido, elimine o endpoint de teste e o certificado no SQL Server usando os seguintes scripts T-SQL:

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