Solucionar problemas de conexão – Instância Gerenciada de SQL do Azure

O monitoramento de link está disponível a partir do SSMS (SQL Server Management Studio) 21.0 (versão prévia).

Para verificar o estado do link no SSMS, siga estas etapas:

1. Conecte-se a uma réplica que hospeda o link.

2. No Pesquisador de Objetos, expanda Alta Disponibilidade AlwaysOn e, em seguida, expanda Grupos de Disponibilidade.

3. Clique com o botão direito do mouse no nome do link e selecione Propriedades para abrir a janela Propriedades do Link :

   

4. A janela Propriedades do Link exibe informações úteis sobre o link, como informações de réplica, estado do link e data de validade do certificado do ponto de extremidade:

   

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 de 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 resultados, 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 para verificar a integridade do link assim que a sincronização de dados começar:

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: o link não está íntegro e os dados não estão sincronizando entre as réplicas.

Use Get-AzSqlInstanceLink para obter informações de estado de 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

Use az sql mi link show para obter informações de estado de link com 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, execute estas etapas:

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

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

   

3. Selecione Avançar na página Introdução do assistente Verificador de Rede.

4. Se todos os requisitos forem atendidos na página Pré-requisitos, selecione Avançar. Caso contrário, resolva os pré-requisitos não atendidos e selecione Executar novamente a validação.

5. Na página Logon, selecione Logon para se conectar à outra instância que será a réplica secundária. Selecione Próximo.

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

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

8. Examine a página Resultados para validar a conectividade existente 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 conectividade do SQL Server para a Instância Gerenciada de SQL

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

1. Conecte-se à Instância Gerenciada de SQL e execute o seguinte script para gerar os parâmetros de que você precisará 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 seguinte exemplo:

    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 nas próximas etapas. Como esses parâmetros podem ser alterados após qualquer failover, certifique-se de gerá-los novamente, se necessário.

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

3. Abra um novo período 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, @port e @serverName pelos valores obtidos na primeira etapa.

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

   

6. Verifique os resultados:

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

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

   • Há regras no firewall de rede e no 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 de SQL.
   • Há uma regra de NSG, que permite comunicação na porta 5022, para a rede virtual hospedando a Instância Gerenciada de SQL.

Testar conectividade da Instância Gerenciada de SQL para o SQL Server

Para verificar se Instância Gerenciada de SQL pode alcançar o SQL Server, crie primeiro um teste de ponto de extremidade. Em seguida, use o SQL Server Agent para executar um script do PowerShell com o comando tnc executando ping do SQL Server na porta 5022 por meio da instância gerenciada de SQL.

Para criar um teste de ponto de extremidade, 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á recebendo conexões na porta 5022, execute o seguinte comando do PowerShell no sistema operacional do host da instância do SQL Server:

tnc localhost -port 5022

Um teste bem-sucedido mostrará 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 por meio da instância gerenciada do SQL.

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

• <SQL_SERVER_IP_ADDRESS> com o endereço IP do SQL Server que pode ser acessado por meio da instância gerenciada.

-- 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 ajudará a executar o cargo e obter os resultados da investigação de rede. Execute o seguinte script T-SQL na instância gerenciada 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;

Realize a seguinte consulta na instância gerenciada para aplicar 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 for bem-sucedida, o log mostrará True. Se a conexão não for bem-sucedida, o log mostrará False.

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

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

Depois de resolver os problemas, execute novamente a investigação de rede do NetHelper executando EXEC ExecuteNetHelper na instância gerenciada.

Por fim, depois que o teste de rede for bem-sucedido, remova o ponto de extremidade de teste e o certificado no SQL Server usando os seguintes comandos T-SQL:

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