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.

De resourcegroep maken

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

# set variables
$ResourceGroupName = "SQLMI-Repl"
$Location = "East US 2"

# Create a new resource group
New-AzResourceGroup -Name  $ResourceGroupName -Location $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 Een met SQL beheerd exemplaar maken in de portal voor meer informatie over het maken van een met SQL beheerd exemplaar.

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 2016 of hoger
• Resourcegroep: hetzelfde als het beheerde SQL-exemplaar
• Virtueel netwerk: sql-vm-subscriber-vnet

Zie de quickstart: Een SQL Server-VM maken 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. Gebruik hiervoor dit PowerShell-codefragment:

# Set variables
$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'

$virtualNetwork1 = Get-AzVirtualNetwork `
  -ResourceGroupName $resourceGroup `
  -Name $pubvNet

 $virtualNetwork2 = Get-AzVirtualNetwork `
  -ResourceGroupName $resourceGroup `
  -Name $subvNet

# Configure VNet peering from publisher to subscriber
Add-AzVirtualNetworkPeering `
  -Name $pubsubName `
  -VirtualNetwork $virtualNetwork1 `
  -RemoteVirtualNetworkId $virtualNetwork2.Id

# Configure VNet peering from subscriber to publisher
Add-AzVirtualNetworkPeering `
  -Name $subpubName `
  -VirtualNetwork $virtualNetwork2 `
  -RemoteVirtualNetworkId $virtualNetwork1.Id

# Check status of peering on the publisher VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $pubvNet `
 | Select PeeringState

# Check status of peering on the subscriber VNet; should say connected
Get-AzVirtualNetworkPeering `
 -ResourceGroupName $resourceGroup `
 -VirtualNetworkName $subvNet `
 | Select 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é-DNS-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.

Kopieer het bestandssharepad in de indeling van: \\storage-account-name.file.core.windows.net\file-share-name

Voorbeeld: \\replstorage.file.core.windows.net\replshare

Kopieer de verbindingsreeks voor de toegangssleutel voor opslag in de indeling van: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=****;EndpointSuffix=core.windows.net

Voorbeeld: DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

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 uit om de database te maken.

-- Create the databases
USE [master]
GO

-- Drop database if it exists
IF EXISTS (SELECT * FROM sys.sysdatabases WHERE name = 'ReplTutorial')
BEGIN
    DROP DATABASE ReplTutorial
END
GO

-- Create new database
CREATE DATABASE [ReplTutorial]
GO

-- Create table
USE [ReplTutorial]
GO
CREATE TABLE ReplTest (
   ID INT NOT NULL PRIMARY KEY,
   c1 VARCHAR(100) NOT NULL,
   dt1 DATETIME NOT NULL DEFAULT getdate()
)
GO

-- Populate table with data
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
SELECT * FROM ReplTest
GO

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). Voer hiervoor de volgende stappen uit:

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 Transact-SQL code uit om distributie te configureren op het beheerde EXEMPLAAR van de distributeur:

   EXEC sp_adddistributor @distributor = 'sql-mi-distributor.b6bf57.database.windows.net', @password = '<distributor_admin_password>'
   
   EXEC sp_adddistributiondb @database = N'distribution'
   
   EXEC sp_adddistpublisher @publisher = 'sql-mi-publisher.b6bf57.database.windows.net', -- primary publisher
        @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'<storage_connection_string>'
        -- example: @storage_connection_string = N'DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net'
   

   Opmerking

   Zorg ervoor dat u alleen backslashes (\) gebruikt voor de @working_directory parameter. Het gebruik van een slash (/) kan een fout veroorzaken bij het maken van verbinding met de bestandsshare.

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

5. Open een venster Nieuwe query en voer de volgende Transact-SQL code uit om de distributeur te registreren bij de uitgever:

   Use MASTER
   EXEC sys.sp_adddistributor @distributor = 'sql-mi-distributor.b6bf57.database.windows.net', @password = '<distributor_admin_password>'
   

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 ReplTesteen naam op op de pagina Wizard voltooien 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 nieuw queryvenster en voer de volgende Transact-SQL code uit om het abonnement en de distributieagent toe te voegen. Gebruik de DNS als onderdeel van de naam van de abonnee.

use [ReplTutorial]
exec sp_addsubscription
@publication = N'ReplTest',
@subscriber = N'sql-vm-subscriber.repldns.com', -- include the DNS configured in the private DNS zone
@destination_db = N'ReplSub',
@subscription_type = N'Push',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0

exec sp_addpushsubscription_agent
@publication = N'ReplTest',
@subscriber = N'sql-vm-subscriber.repldns.com', -- include the DNS configured in the private DNS zone
@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

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.

Voer het volgende T-SQL-fragment uit om de rijen op de abonnee weer te geven:

Use ReplSub
select * from dbo.ReplTest

Voer het volgende T-SQL-fragment uit om extra rijen in te voegen op de uitgever en controleer de rijen opnieuw op de abonnee.

Use ReplTutorial
INSERT INTO ReplTest (ID, c1) VALUES (15, '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.

T 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
• Always Encrypted
• Advanced Threat Protection configureren in Azure SQL Managed Instance
• Dynamische gegevensmaskering
• Beveiliging op rijniveau
• TDE (Transparent Data Encryption)