Zelfstudie: Transactionele replicatie configureren tussen Azure SQL Managed Instance en SQL Server

Van toepassing op:Azure SQL Managed Instance

Met transactionele replicatie kunt u gegevens van de ene database repliceren naar een andere die wordt gehost op SQL Server of Azure SQL Managed Instance. SQL Managed Instance kan een uitgever, distributeur of abonnee zijn in de replicatietopologie. Zie transactionele replicatieconfiguraties voor beschikbare configuraties.

In deze handleiding leer je hoe je:

Deze zelfstudie is bedoeld voor een ervaren doelgroep en gaat ervan uit dat de gebruiker bekend is met het implementeren en maken van verbinding met zowel met SQL beheerde exemplaren als SQL Server-VM's in Azure.

Vereiste voorwaarden

Zorg ervoor dat u aan de volgende vereisten voldoet om de zelfstudie te voltooien:

• Een Azure-abonnement.
• Ervaring met het implementeren van twee met SQL beheerde exemplaren binnen hetzelfde virtuele netwerk.
• Een SQL Server-abonnee, on-premises of op een Azure-VM. In deze zelfstudie wordt gebruikgemaakt van een Virtuele Azure-machine.
• SQL Server Management Studio (SSMS) 18.0 of hoger.
• De nieuwste versie van Azure PowerShell.
• Poorten 445 en 1433 staan SQL-verkeer toe op zowel de Azure-firewall als de Windows Firewall.

Vereiste namen en netwerkinstellingen

In deze handleiding worden de volgende bronnamen en -instellingen gebruikt:

De resourcegroep maken

Gebruik het volgende PowerShell-codefragment om een nieuwe resourcegroep te maken.

Stel uw variabelen in:

$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

Maak de resourcegroep:

New-AzResourceGroup -Name $ResourceGroupName -Location $Location

Controleer of de resourcegroep is gemaakt:

Get-AzResourceGroup -Name $ResourceGroupName | Select-Object ResourceGroupName, Location

Twee met SQL beheerde exemplaren maken

Maak twee met SQL beheerde exemplaren binnen deze nieuwe resourcegroep met behulp van Azure Portal.

• De naam van het beheerde sql-exemplaar van de uitgever moet zijn sql-mi-publisher (samen met een paar tekens voor randomisatie) en de naam van het virtuele netwerk moet zijn vnet-sql-mi-publisher.

• De naam van het beheerde SQL-exemplaar van de distributeur moet (samen met een paar tekens voor randomisatie) zijn sql-mi-distributor en moet zich in hetzelfde virtuele netwerk bevinden als het beheerde exemplaar van de uitgever van SQL.

  

Zie voor meer informatie over het maken van een SQL Managed Instance Quickstart: Azure SQL Managed Instance maken.

Een SQL Server-VM maken

Maak een virtuele SQL Server-machine met behulp van Azure Portal. De virtuele SQL Server-machine moet de volgende kenmerken hebben:

• Naam: sql-vm-subscriber
• Afbeelding: SQL Server-versie die transactionele replicatie ondersteunt met Azure SQL Managed Instance, volgens de ondersteuningsmatrix
• Resourcegroep: hetzelfde als het beheerde SQL-exemplaar
• Virtueel netwerk: sql-vm-subscriber-vnet

Zie Quickstart: SQL Server maken op een virtuele Windows-machine in Azure voor meer informatie over het implementeren van een SQL Server VM in Azure.

VNet-peering configureren

Configureer VNet-peering om communicatie mogelijk te maken tussen het virtuele netwerk van de twee met SQL beheerde exemplaren en het virtuele netwerk van SQL Server.

Stel uw variabelen in:

$SubscriptionId = '<SubscriptionID>'
$resourceGroup = 'SQLMI-Repl'
$pubvNet = 'sql-mi-publisher-vnet'
$subvNet = 'sql-vm-subscriber-vnet'
$pubsubName = 'Pub-to-Sub-Peer'
$subpubName = 'Sub-to-Pub-Peer'

Haal de virtuele netwerken op:

$virtualNetwork1 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $pubvNet
$virtualNetwork2 = Get-AzVirtualNetwork -ResourceGroupName $resourceGroup -Name $subvNet

VNet-peering configureren van uitgever naar abonnee:

Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

VNet-peering configureren van abonnee naar uitgever:

Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

Controleer de peeringstatus op het VNet van de uitgever (zou moeten retourneren Connected):

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $pubvNet | Select-Object PeeringState

Controleer de peeringstatus op het abonnee-VNet (zou Connected moeten weergeven):

Get-AzVirtualNetworkPeering -ResourceGroupName $resourceGroup -VirtualNetworkName $subvNet | Select-Object PeeringState

Zodra VNet-peering tot stand is gebracht, test u de connectiviteit door SQL Server Management Studio (SSMS) op de SQL Server-host te starten en verbinding te maken met beide met SQL beheerde exemplaren. Zie SSMS gebruiken om verbinding te maken met sql Managed Instance voor meer informatie over het maken van verbinding met een met SQL beheerd exemplaar met behulp van SSMS.

Een privé-DNS-zone maken

Een privé-DNS-zone staat DNS-routering toe tussen de beheerde SQL-exemplaren en SQL Server.

Een privézone maken

1. Meld u aan bij de Azure Portal.

2. Selecteer Een resource maken om een nieuwe Azure-resource te maken.

3. private dns zone Zoek op Azure Marketplace.

4. Kies de privé-DNS-zoneresource die door Microsoft is gepubliceerd en selecteer vervolgens Maken om de DNS-zone te maken.

5. Kies het abonnement en de resourcegroep in de vervolgkeuzelijst.

6. Geef een willekeurige naam op voor uw DNS-zone, zoals repldns.com.

   

7. Kies Beoordelen + creëren. Controleer de parameters voor uw privé-DNS-zone en selecteer vervolgens Maken om uw resource te maken.

Een A-record maken

1. Ga naar uw nieuwe privé-DNS-zone en selecteer Overzicht.

2. Selecteer + Recordset om een nieuwe A-record te maken.

3. Geef de naam van uw SQL Server-VM en het persoonlijke interne IP-adres op.

   

4. Selecteer OK om de A-record te maken.

Het virtuele netwerk koppelen

1. Ga naar de nieuwe privé-DNS-zone en selecteer virtuele netwerkkoppelingen.

2. Selecteer + Toevoegen.

3. Geef een naam op voor de koppeling, zoals Pub-link.

4. Selecteer uw abonnement in de vervolgkeuzelijst en selecteer vervolgens het virtuele netwerk voor het beheerde EXEMPLAAR van uw uitgever.

5. Schakel het selectievakje naast Automatische registratie inschakelen in.

   

6. Selecteer OK om uw virtuele netwerk te koppelen.

7. Herhaal deze stappen om een koppeling toe te voegen voor het virtuele netwerk van de abonnee, met een naam zoals Sub-link.

Een Azure-opslagaccount maken

Maak een Azure-opslagaccount voor de werkmap en maak vervolgens een bestandsshare in het opslagaccount.

Opslagconfiguratiewaarden

U hebt de volgende waarden nodig bij het configureren van distributie:

• Indeling van werkmappad: \\<storage-account-name>.file.core.windows.net\<file-share-name>
• Indeling van opslagverbindingsreeks: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=<key>;EndpointSuffix=core.windows.net

Voorbeeldwaarden die in deze zelfstudie worden gebruikt:

Zie Toegangssleutels voor opslagaccounts beheren voor meer informatie.

Een database maken

Maak een nieuwe database in het beheerde exemplaar van de uitgever van SQL. Voer hiervoor de volgende stappen uit:

1. Start SQL Server Management Studio op SQL Server.
2. Maak verbinding met het beheerde exemplaar van de uitgever (sql-mi-publisher).
3. Open een venster Nieuwe query en voer de volgende T-SQL-query's uit.

Verwijder de database als deze bestaat en maak een nieuwe:

USE [master];
GO

IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial;
END
GO

CREATE DATABASE [ReplTutorial];
GO

Maak de testtabel voor replicatie:

USE [ReplTutorial];
GO

CREATE TABLE ReplTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT getdate()
);
GO

Voorbeeldgegevens invoegen:

USE [ReplTutorial];
GO

INSERT INTO ReplTest (ID, c1) VALUES (6, 'pub');
INSERT INTO ReplTest (ID, c1) VALUES (2, 'pub');
INSERT INTO ReplTest (ID, c1) VALUES (3, 'pub');
INSERT INTO ReplTest (ID, c1) VALUES (4, 'pub');
INSERT INTO ReplTest (ID, c1) VALUES (5, 'pub');
GO

Controleer of de gegevens zijn ingevoegd:

SELECT * FROM ReplTest;
GO

Verwachte uitvoer: 5 rijen met id's 2, 3, 4, 5 en 6.

Distributie configureren

Zodra de verbinding tot stand is gebracht en u een voorbeelddatabase hebt, kunt u distributie configureren op uw door SQL beheerde instantie (sql-mi-distributor).

Configuratieparameters voor distributie

Verzamel de volgende waarden voordat u distributie configureert:

De distributie-instantie configureren

1. Start SQL Server Management Studio op SQL Server.
2. Maak verbinding met het beheerde SQL-exemplaar (sql-mi-distributor).
3. Open een venster Nieuwe query en voer de volgende opdrachten uit.

Voeg de distributeur toe:

EXECUTE sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

Maak de distributiedatabase:

EXECUTE sp_adddistributiondb @database = N'distribution';

Controleer of de distributiedatabase is gemaakt:

SELECT name FROM sys.databases WHERE name = 'distribution';

Voeg de uitgever toe aan de distributeur:

EXECUTE sp_adddistpublisher
    @publisher = 'sql-mi-publisher.b6bf57.database.windows.net',
    @distribution_db = N'distribution',
    @security_mode = 0,
    @login = N'azureuser',
    @password = N'<publisher_password>',
    @working_directory = N'\\replstorage.file.core.windows.net\replshare',
    @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net';

Registreer de distributeur bij de uitgever

1. Maak verbinding met het beheerde exemplaar van de uitgever (sql-mi-publisher).
2. Open een venster Nieuwe query en voer de volgende opdracht uit om de distributeur te registreren:

USE master;
GO

EXECUTE sys.sp_adddistributor
    @distributor = 'sql-mi-distributor.b6bf57.database.windows.net',
    @password = '<distributor_admin_password>';

Controleer of de distributeur is geregistreerd:

SELECT * FROM sys.servers WHERE is_distributor = 1;

De publicatie maken

Zodra de distributie is geconfigureerd, kunt u de publicatie maken. Voer hiervoor de volgende stappen uit:

1. Start SQL Server Management Studio op SQL Server.

2. Maak verbinding met het beheerde exemplaar van de uitgever (sql-mi-publisher).

3. Vouw in Objectverkenner het replicatieknooppunt uit en klik met de rechtermuisknop op de map Lokale publicatie . Selecteer Nieuwe publicatie....

4. Selecteer Volgende om voorbij de welkomstpagina te gaan.

5. Selecteer op de pagina Publicatiedatabase de ReplTutorial database die u eerder hebt gemaakt. Kies Volgende.

6. Selecteer op de pagina Publicatietypede optie Transactionele publicatie. Kies Volgende.

7. Schakel op de pagina Artikelen het selectievakje naast Tabellen in. Kies Volgende.

8. Selecteer Volgende op de pagina Rijen filteren zonder filters toe te voegen.

9. Schakel op de pagina Momentopnameagent het selectievakje naast Momentopname maken onmiddellijk in en houd de momentopname beschikbaar om abonnementen te initialiseren. Kies Volgende.

10. Selecteer Beveiligingsinstellingen op de pagina Agentbeveiliging.... Geef sql Server-aanmeldingsreferenties op die moeten worden gebruikt voor de momentopnameagent en om verbinding te maken met de uitgever. Selecteer OK om de pagina Security van de momentopnameagent te sluiten. Kies Volgende.

    

11. Kies op de pagina Acties van de wizard de publicatie maken en (optioneel) kies ervoor om een scriptbestand te genereren met stappen om de publicatie te maken als u dit script later wilt opslaan.

12. Geef de publicatie een naam op ReplTest en selecteer Volgende om de publicatie te maken.

13. Nadat de publicatie is gemaakt, vernieuwt u het replicatieknooppunt in Objectverkenner en vouwt u Lokale publicaties uit om de nieuwe publicatie te zien.

Het abonnement maken

Zodra de publicatie is gemaakt, kunt u het abonnement maken. Voer hiervoor de volgende stappen uit:

1. Start SQL Server Management Studio op SQL Server.
2. Maak verbinding met het beheerde exemplaar van de uitgever (sql-mi-publisher).
3. Open een venster Nieuwe query en voer de volgende Transact-SQL opdrachten uit. Gebruik de DNS-naam die is geconfigureerd in de privé-DNS-zone als onderdeel van de abonneenaam.

Abonnementsparameters

Voeg het abonnement toe:

USE [ReplTutorial];
GO

EXEC sp_addsubscription
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @destination_db = N'ReplSub',
    @subscription_type = N'Push',
    @sync_type = N'automatic',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0;

Voeg de pushabonnementsagent toe:

EXEC sp_addpushsubscription_agent
    @publication = N'ReplTest',
    @subscriber = N'sql-vm-subscriber.repldns.com',
    @subscriber_db = N'ReplSub',
    @job_login = N'azureuser',
    @job_password = '<Complex Password>',
    @subscriber_security_mode = 0,
    @subscriber_login = N'azureuser',
    @subscriber_password = '<Complex Password>',
    @dts_package_location = N'Distributor';
GO

Controleer of het abonnement is gemaakt:

SELECT * FROM distribution.dbo.MSsubscriptions;

Replicatie testen

Zodra de replicatie is geconfigureerd, kunt u deze testen door nieuwe items in de uitgever in te voegen en de wijzigingen door te geven aan de abonnee.

Initiële gegevens van de abonnee weergeven

Maak verbinding met de SQL Server-abonnee en voer de volgende query uit:

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest;

Verwachte uitvoer: 5 rijen met id's 2, 3, 4, 5 en 6 (de eerste gegevens van de uitgever).

Nieuwe gegevens toevoegen bij de uitgever

Maak verbinding met het beheerde SQL-exemplaar van de uitgever (sql-mi-publisher) en voeg een nieuwe rij in:

USE ReplTutorial;
GO

INSERT INTO ReplTest (ID, c1) VALUES (15, 'pub');

Controleer het invoegsel bij de uitgever.

SELECT * FROM ReplTest WHERE ID = 15;

Replicatie naar de abonnee controleren

Maak na enkele ogenblikpen verbinding met de abonnee en controleer of de nieuwe rij is gerepliceerd:

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest WHERE ID = 15;

Verwachte uitvoer: 1 rij met id 15 en c1 waarde 'pub'.

De hulpbronnen opschonen

1. Navigeer naar uw resourcegroep in Azure Portal.
2. Selecteer de beheerde SQL-exemplaren en selecteer vervolgens Verwijderen. Typ yes het tekstvak om te bevestigen dat u de resource wilt verwijderen en selecteer vervolgens Verwijderen. Het kan enige tijd duren voordat dit proces op de achtergrond is voltooid. Totdat dit is voltooid, kunt u het virtuele cluster of andere afhankelijke resources niet verwijderen. Controleer de verwijdering op het tabblad Activiteit om te bevestigen dat uw beheerde SQL-exemplaar is verwijderd.
3. Nadat het beheerde SQL-exemplaar is verwijderd, verwijdert u het virtuele cluster door het te selecteren in uw resourcegroep en vervolgens Verwijderen te kiezen. Typ yes het tekstvak om te bevestigen dat u de resource wilt verwijderen en selecteer vervolgens Verwijderen.
4. Verwijder alle resterende resources. Typ yes het tekstvak om te bevestigen dat u de resource wilt verwijderen en selecteer vervolgens Verwijderen.
5. Verwijder de resourcegroep door resourcegroep verwijderen te selecteren, de naam van de resourcegroep myResourceGroupte typen en vervolgens Verwijderen te selecteren.

Bekende fouten

Windows-aanmeldingen worden niet ondersteund

Exception Message: Windows logins are not supported in this version of SQL Server.

De agent is geconfigureerd met een Windows-aanmelding en moet in plaats daarvan een SQL Server-aanmelding gebruiken. Gebruik de pagina Agentbeveiliging van de publicatie-eigenschappen om de aanmeldingsreferenties te wijzigen in een SQL Server-aanmelding.

Kan geen verbinding maken met Azure Storage

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 53.
2019-11-19 02:21:05.07 Obtained Azure Storage Connection String for replstorage
2019-11-19 02:21:05.07 Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare'
2019-11-19 02:21:31.21 Failed to connect to Azure Storage '' with OS error: 53.

Dit komt waarschijnlijk doordat poort 445 is gesloten in de Azure-firewall, Windows Firewall of beide.

Connecting to Azure Files Storage '\\replstorage.file.core.windows.net\replshare' Failed to connect to Azure Storage '' with OS error: 55.

Het gebruik van een slash in plaats van backslash in het bestandspad voor de bestandsshare kan deze fout ook veroorzaken.

• Dit is acceptabel: \\replstorage.file.core.windows.net\replshare
• Dit kan een os 55-fout veroorzaken: \\replstorage.file.core.windows.net/replshare

Kan geen verbinding maken met abonnee

The process could not connect to Subscriber 'SQL-VM-SUBSCRIBER
Could not open a connection to SQL Server [53].
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Mogelijke oplossingen:

• Zorg ervoor dat poort 1433 open is.
• Zorg ervoor dat TCP/IP is ingeschakeld voor de abonnee.
• Controleer of de DNS-naam is gebruikt bij het maken van de abonnee.
• Controleer of uw virtuele netwerken correct zijn gekoppeld in de privé-DNS-zone.
• Controleer of uw A-record juist is geconfigureerd.
• Controleer of uw VNet-peering juist is geconfigureerd.

Geen publicaties waarvoor u zich kunt abonneren

Wanneer u een nieuw abonnement toevoegt met behulp van de wizard Nieuw abonnement , ziet u op de pagina Publicatie mogelijk dat er geen databases en publicaties worden vermeld als beschikbare opties. Mogelijk ziet u het volgende foutbericht:

There are no publications to which you can subscribe, either because this server has no publications or because you do not have sufficient privileges to access the publications.

Hoewel dit foutbericht mogelijk juist is en er geen publicaties beschikbaar zijn in de uitgever waarmee u verbinding hebt gemaakt of u onvoldoende machtigingen hebt, kan een oudere versie van SQL Server Management Studio deze fout ook veroorzaken. Voer een upgrade uit naar SQL Server Management Studio 18.0 of hoger om dit uit te sluiten als hoofdoorzaak.

Verwante inhoud

• Wat is Azure SQL Managed Instance?
• Aan de slag met azure SQL Managed Instance-controle
• Altijd versleuteld
• Advanced Threat Protection configureren in Azure SQL Managed Instance
• Dynamische gegevensmaskering
• Beveiliging op rijniveau
• TDE (Transparent Data Encryption)