Koppeling oplossen - Azure SQL Managed Instance

Koppelingsbewaking is beschikbaar vanaf SQL Server Management Studio (SSMS) 21.0 (preview).

Volg deze stappen om de koppelingsstatus in SSMS te controleren:

1. Maak verbinding met een replica die als host fungeert voor de koppeling.

2. Vouw in ObjectverkennerAlwaysOn Hoge beschikbaarheid uit en vouw vervolgens Beschikbaarheidsgroepen uit.

3. Klik met de rechtermuisknop op de naam van de koppeling en selecteer Eigenschappen om het venster Koppelingseigenschappen te openen:

   

4. In het venster Eigenschappen van koppeling wordt nuttige informatie weergegeven over de koppeling, zoals replicagegevens, koppelingsstatus en de vervaldatum van het eindpuntcertificaat:

   

Gebruik T-SQL om de status van de koppeling te bepalen tijdens de seedingfase of nadat de gegevenssynchronisatie is gestart.

Gebruik de volgende T-SQL-query om de status van de koppeling te bepalen tijdens de seedingfase op de SQL Server of SQL Managed Instance die als host fungeert voor de database die via de koppeling wordt geseed:

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

Als de query geen resultaten retourneert, is het seedingproces niet gestart of is het al voltooid.

Gebruik de volgende T-SQL-query op het primaire-exemplaar om de status van de koppeling te controleren zodra de gegevenssynchronisatie is gestart:

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

De query retourneert de volgende mogelijke waarden:

• geen resultaat: de query is uitgevoerd op het secundaire exemplaar.
• HEALTHY: de koppeling is in orde en gegevens worden gesynchroniseerd tussen de replica's.
• NOT_HEALTHY: de koppeling is beschadigd en gegevens worden niet gesynchroniseerd tussen de replica's.

Gebruik Get-AzSqlInstanceLink om statusinformatie over koppelingen op te halen met PowerShell.

Voer de volgende voorbeeldcode uit in Azure Cloud Shell of installeer de Az.SQL-module lokaal.

$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

Gebruik az sql mi link show om informatie over de linkstatus te verkrijgen met de 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  

Voer de volgende stappen uit om de netwerkverbinding tussen SQL Server en SQL Managed Instance in SSMS te testen:

1. Maak verbinding met het exemplaar dat als primaire replica in SSMS fungeert.

2. Vouw in Objectverkennerdatabases uit en klik met de rechtermuisknop op de database die u met de secundaire database wilt koppelen. Selecteer Taken >Azure SQL Managed Instance link> Verbinding testen om de Netwerkcontrole-wizard te openen.

   

3. Selecteer Volgende op de Inleidingspagina van de wizard Netwerkcontrole.

4. Als aan alle vereisten wordt voldaan op de pagina Voorwaarden, selecteert u Volgende. Los anders eventuele onvervulde vereisten op en selecteer vervolgens Validatie opnieuw uitvoeren.

5. Op de Aanmelden pagina, selecteer Aanmelden om verbinding te maken met het andere exemplaar, dat als de secundaire replica zal dienen. Selecteer Volgende.

6. Controleer de details op de pagina Netwerkopties opgeven en geef indien nodig een IP-adres op. Selecteer Volgende.

7. Controleer op de pagina Samenvatting de acties die de wizard uitvoert en selecteer vervolgens Voltooien om de verbinding tussen de twee replica's te testen.

8. Controleer de pagina resultaten om te controleren of er connectiviteit bestaat tussen de twee replica's en selecteer vervolgens sluiten om te voltooien.

Als u T-SQL wilt gebruiken om connectiviteit te testen, moet u de verbinding in beide richtingen controleren. Test eerst de verbinding van SQL Server met SQL Managed Instance en test vervolgens de verbinding van SQL Managed Instance naar SQL Server.

Verbinding van SQL Server met SQL Managed Instance testen

Gebruik SQL Server Agent op SQL Server om connectiviteitstests van SQL Server naar SQL Managed Instance uit te voeren.

1. Maak verbinding met SQL Managed Instance en voer het volgende script uit om parameters te genereren die u later nodig hebt:

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

   De resultaten moeten eruitzien als in het volgende voorbeeld:

    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'
   

   Sla de resultaten op om de volgende stappen te gebruiken. Aangezien deze parameters na een failover kunnen worden gewijzigd, moet u deze indien nodig opnieuw genereren.

2. Maak verbinding met uw SQL Server-exemplaar.

3. Open een nieuw queryvenster en plak het volgende 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. Vervang de parameters @node, @porten @serverName door de waarden die u hebt verkregen uit de eerste stap.

5. Voer het script uit en controleer de resultaten. U zou resultaten moeten zien zoals het volgende voorbeeld:

   

6. Controleer de resultaten:

   • Het resultaat van elke test bij TcpTestSucceeded moet worden TcpTestSucceeded : True.
   • De RemoteAddresses moeten deel uitmaken van het IP-bereik voor het subnet sql Managed Instance.

   Als het antwoord mislukt, controleert u de volgende netwerkinstellingen:

   • Er zijn regels in zowel de netwerkfirewall als de FIREWALL van het SQL Server-host-besturingssysteem (Windows/Linux) waarmee verkeer naar het volledige IP-adresbereik van subnet van SQL Managed Instance wordt toegestaan.
   • Er is een NSG-regel die communicatie op poort 5022 toestaat voor het virtuele netwerk dat als host fungeert voor SQL Managed Instance.

Verbinding van SQL Managed Instance naar SQL Server testen

Als u wilt controleren of SQL Managed Instance SQL Server kan bereiken, maakt u eerst een testeindpunt. Vervolgens gebruikt u de SQL Server Agent om een PowerShell-script uit te voeren met de tnc-opdracht die SQL Server op poort 5022 pingt vanuit het beheerde SQL-exemplaar.

Als u een testeindpunt wilt maken, maakt u verbinding met SQL Server en voert u het volgende T-SQL-script uit:

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

Als u wilt controleren of het SQL Server-eindpunt verbindingen ontvangt op poort 5022, voert u de volgende PowerShell-opdracht uit op het hostbesturingssysteem van uw SQL Server-exemplaar:

tnc localhost -port 5022

Een geslaagde test toont TcpTestSucceeded : True. Vervolgens kunt u doorgaan met het maken van een SQL Server Agent-taak op het met SQL beheerde exemplaar om het SQL Server-testeindpunt te testen op poort 5022 vanuit het beheerde SQL-exemplaar.

Maak vervolgens een SQL Server Agent-taak in het met SQL beheerde exemplaar met de naam NetHelper door het volgende T-SQL-script uit te voeren op het beheerde SQL-exemplaar. Vervangen:

• <SQL_SERVER_IP_ADDRESS> met het IP-adres van SQL Server dat toegankelijk is vanuit een beheerd SQL-exemplaar.

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

Maak vervolgens een opgeslagen procedure ExecuteNetHelper waarmee de taak kan worden uitgevoerd en resultaten worden verkregen van de netwerktest. Voer het volgende T-SQL-script uit op een met SQL beheerd exemplaar:

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

Voer de volgende query uit op sql Managed Instance om de opgeslagen procedure uit te voeren waarmee de NetHelper-agenttaak wordt uitgevoerd en het resulterende logboek wordt weergegeven:

-- Run on managed instance
EXEC ExecuteNetHelper;

Als de verbinding is geslaagd, wordt in het logboek Trueweergegeven. Als de verbinding is mislukt, wordt in het logboek Falseweergegeven.

Als de verbinding mislukt, controleert u de volgende items:

• De firewall op het SQL Server-hostexemplaar staat binnenkomende en uitgaande communicatie toe op poort 5022.
• Een NSG-regel voor het virtuele netwerk dat als host fungeert voor SQL Managed Instance, staat communicatie toe op poort 5022.
• Als uw SQL Server-exemplaar zich op een Azure-VM bevindt, staat een NSG-regel communicatie toe op poort 5022 op het virtuele netwerk waarop de VIRTUELE machine wordt gehost.
• SQL Server draait.
• Er bestaat een testeindpunt in SQL Server.

Nadat u problemen hebt opgelost, voert u de NetHelper-netwerktest opnieuw uit door EXEC ExecuteNetHelper uit te voeren op een beheerd exemplaar.

Als de netwerktest is geslaagd, verwijdert u ten slotte het testeindpunt en -certificaat op SQL Server met behulp van de volgende T-SQL-opdrachten:

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