Condividi tramite


Esercitazione: Configurare la replica transazionale tra Istanza Gestita di Azure SQL e SQL Server

Si applica a:Istanza gestita di SQL di Azure SQL

La replica transazionale consente di replicare i dati da un database a un altro ospitato in SQL Server o in un'istanza gestita di SQL di Azure. Istanza gestita di SQL può essere un server di pubblicazione, un server di distribuzione o un sottoscrittore nella topologia di replica. Per le configurazioni disponibili, vedere Configurazioni di replica transazionale.

In questa esercitazione apprenderai a:

  • Configurare un'istanza gestita di SQL come server di pubblicazione di replica.
  • Configurare un'istanza gestita di SQL come server di distribuzione di replica.
  • Configurare SQL Server come sottoscrittore.

Diagramma che mostra la replica tra un server di pubblicazione di istanza gestita di SQL, un server di distribuzione di istanza gestita di SQL e un sottoscrittore di SQL Server.

Questa esercitazione è destinata a un pubblico esperto e presuppone che l'utente abbia familiarità con la distribuzione e la connessione alle istanze gestite di SQL e alle macchine virtuali di SQL Server all'interno di Azure.

Nota

Questo articolo descrive l'uso della replica transazionale nell'istanza gestita di SQL di Azure. Non è correlato ai gruppi di failover, una funzionalità di Istanza gestita di SQL di Azure che consente di creare repliche leggibili complete di singole istanze. Per la configurazione della replica transazionale con i gruppi di failover, è necessario tenere presenti alcune considerazioni aggiuntive.

Prerequisiti

Per completare questa esercitazione, verificare di avere i prerequisiti seguenti:

Nomi obbligatori e impostazioni di rete

Questa esercitazione usa i nomi e le impostazioni delle risorse seguenti:

Conto risorse Nome Note
Istanza SQL gestita del server di pubblicazione sql-mi-publisher Aggiungere caratteri casuali per l'univocità
Rete virtuale Publisher vnet-sql-mi-publisher Ospita l'istanza del server di pubblicazione
Istanza SQL gestita del distributore sql-mi-distributor Deve trovarsi nella stessa rete virtuale del server di pubblicazione
Macchina Virtuale di SQL Server (abbonato) sql-vm-subscriber Usare una versione supportata di SQL Server in base alla matrice di supporto
Rete virtuale utente sql-vm-subscriber-vnet Richiede il peering VNet per la VNet del publisher
Zona DNS privato repldns.com Nome arbitrario per il routing DNS
Porte richieste 445 (SMB), 1433 (SQL) Deve essere aperto in Firewall di Azure e Windows Firewall

Creare il gruppo di risorse

Usare il frammento di codice di PowerShell seguente per creare un nuovo gruppo di risorse.

Imposta le tue variabili:

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

Creare il gruppo di risorse:

New-AzResourceGroup -Name $ResourceGroupName -Location $Location

Verificare che il gruppo di risorse sia stato creato:

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

Creare due istanze gestite di SQL

Creare due istanze gestite di SQL all'interno di questo nuovo gruppo di risorse usando il portale di Azure.

  • Il nome dell'istanza gestita di SQL del server di pubblicazione deve essere sql-mi-publisher (insieme ad alcuni caratteri per la casualizzazione) e il nome della rete virtuale deve essere vnet-sql-mi-publisher.

  • Il nome dell'istanza gestita di SQL del server di distribuzione deve essere sql-mi-distributor (insieme ad alcuni caratteri per la casualizzazione) e deve trovarsi nella stessa rete virtuale dell'istanza gestita di SQL del server di pubblicazione.

    Screenshot che mostra l'uso della rete virtuale del server di pubblicazione per il server di distribuzione.

Per altre informazioni sulla creazione di un'istanza gestita di SQL, vedere Avvio rapido: Creare un'istanza gestita di SQL di Azure.

Nota

Per semplicità, questa esercitazione inserisce l'istanza gestita di SQL del server di distribuzione nella stessa rete virtuale del server di pubblicazione. Tuttavia, è possibile creare il distributore in una rete virtuale separata con il peering di VNet appropriato.

Creare una macchina virtuale di SQL Server

Creare una macchina virtuale di SQL server usando il portale di Azure. La macchina virtuale di SQL Server deve avere le caratteristiche seguenti:

  • Nome: sql-vm-subscriber
  • Immagine: versione di SQL Server che supporta la replica transazionale con Istanza gestita di SQL di Azure, in base alla matrice di supporto
  • Gruppo di risorse: uguale all'istanza gestita di SQL
  • Rete virtuale: sql-vm-subscriber-vnet

Per altre informazioni sulla distribuzione di una macchina virtuale di SQL Server in Azure, vedere Avvio rapido: Creare SQL Server in una macchina virtuale Windows nel portale di Azure.

Configurare il peering VNet

Configurare il peering reti virtuali per abilitare la comunicazione tra la rete virtuale delle due istanze gestite di SQL e la rete virtuale di SQL Server.

Imposta le variabili:

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

Recuperare le reti virtuali:

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

Configurare il peering VNet dal publisher al subscriber:

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

Configurare il peering di VNet dall'abbonato all'editore:

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

Verificare lo stato del peering nella rete virtuale del server di pubblicazione (deve restituire Connected):

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

Verificare lo stato del peering nella rete virtuale del sottoscrittore (deve restituire Connected):

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

Dopo aver stabilito il peering reti virtuali, testare la connettività avviando SQL Server Management Studio (SSMS) nell'host di SQL Server e connettendosi a entrambe le istanze gestite di SQL. Per altre informazioni sulla connessione a un'istanza gestita di SQL tramite SSMS, vedere Usare SSMS per connettersi a Istanza gestita di SQL.

Screenshot che mostra come testare la connettività alle istanze gestite di SQL.

Creare una zona DNS privato

Una zona DNS privata consente il routing DNS tra le istanze gestite di SQL e SQL Server.

Creare una zona privata

  1. Accedere al portale di Azure.

  2. Selezionare Crea una risorsa per creare una nuova risorsa di Azure.

  3. Cerca private dns zone su Azure Marketplace.

  4. Scegliere la risorsa zona DNS privato pubblicata da Microsoft e quindi selezionare Crea per creare la zona DNS.

  5. Scegliere la sottoscrizione e il gruppo di risorse dall'elenco a discesa.

  6. Specificare un nome arbitrario per la zona DNS, ad esempio repldns.com.

    Screenshot che mostra la finestra Crea zona DNS privata nel portale di Azure.

  7. Selezionare Rivedi e crea. Esaminare i parametri per la zona DNS privata e quindi selezionare Crea per creare la risorsa.

Creare un record A

  1. Passare alla nuova zona DNS privato e selezionare Panoramica.

  2. Selezionare + Set di record per creare un nuovo record A.

  3. Specificare il nome della VM di SQL Server oltre all'indirizzo IP interno privato.

    Screenshot che mostra come configurare un record A.

  4. Selezionare OK per creare il record A.

  1. Passare alla nuova zona DNS privato e selezionare Collegamenti di rete virtuale.

  2. Seleziona + Aggiungi.

  3. Specificare un nome per il collegamento, ad esempio Pub-link.

  4. Selezionare la sottoscrizione dall'elenco a discesa e quindi selezionare la rete virtuale per l'istanza gestita di SQL di pubblicazione.

  5. Fare clic sulla casella accanto a Abilita registrazione automatica.

    Screenshot che mostra come creare un collegamento di rete virtuale.

  6. Selezionare OK per collegarsi alla rete virtuale.

  7. Ripetere questi passaggi per aggiungere un collegamento per la rete virtuale del server di sottoscrizione, con un nome come Sub-link.

Creare un account di archiviazione di Azure

Creare un account di archiviazione di Azure per la directory di lavoro e quindi creare una condivisione file al suo interno.

Valori di configurazione dell'archiviazione

Per la configurazione della distribuzione sono necessari i valori seguenti:

  • Formato percorso directory di lavoro: \\<storage-account-name>.file.core.windows.net\<file-share-name>
  • Formato della stringa di connessione di archiviazione: DefaultEndpointsProtocol=https;AccountName=<Storage-Account-Name>;AccountKey=<key>;EndpointSuffix=core.windows.net

Importante

Usare solo le barre rovesciate (\) nel percorso della directory di lavoro. Le barre oblique (/) causano errori di connessione.

Valori di esempio usati in questa esercitazione:

Parametro Valore di esempio
Directory di lavoro \\replstorage.file.core.windows.net\replshare
stringa di connessione DefaultEndpointsProtocol=https;AccountName=replstorage;AccountKey=123456789aBcDeFgHiJkLmNoPqRsTuVwXyZ==;EndpointSuffix=core.windows.net

Per altre informazioni, vedere Gestire le chiavi di accesso dell'account di archiviazione.

Creare un database

Creare un nuovo database nell'istanza gestita di SQL del server di pubblicazione. A tale scopo, effettuare i passaggi seguenti:

  1. Avvia SQL Server Management Studio su SQL Server.
  2. Connettersi all'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher).
  3. Aprire una finestra Nuova query ed eseguire le query T-SQL seguenti.

Eliminare il database se esistente e crearne uno nuovo:

USE [master];
GO

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

CREATE DATABASE [ReplTutorial];
GO

Creare la tabella di test della replica:

USE [ReplTutorial];
GO

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

Inserisci dati di esempio:

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

Verificare che i dati siano stati inseriti:

SELECT * FROM ReplTest;
GO

Output previsto: 5 righe con ID 2, 3, 4, 5 e 6.

Configurare la distribuzione

Dopo aver stabilito la connettività e aver creato un database di esempio, è possibile configurare la distribuzione nell'istanza gestita di SQL del server di distribuzione (sql-mi-distributor).

Parametri di configurazione della distribuzione

Raccogliere i valori seguenti prima di configurare la distribuzione:

Parametro Description Example
Nome DNS del server di distribuzione FQDN dell'istanza del server di distribuzione sql-mi-distributor.b6bf57.database.windows.net
Nome DNS del server di pubblicazione FQDN dell'istanza del server di pubblicazione sql-mi-publisher.b6bf57.database.windows.net
@working_directory Percorso di condivisione di Azure Files (usare solo le barre rovesciate) \\replstorage.file.core.windows.net\replshare
@storage_connection_string Stringa di connessione dell'account di archiviazione DefaultEndpointsProtocol=https;AccountName=replstorage;...
@security_mode Modalità di autenticazione (0 = autenticazione SQL) 0
@login / @password Credenziali di accesso SQL azureuser

Configurare l'istanza del server di distribuzione

  1. Avvia SQL Server Management Studio su SQL Server.
  2. Connettersi all'istanza gestita di SQL del server di distribuzione (sql-mi-distributor).
  3. Aprire una finestra Nuova query ed eseguire i comandi seguenti.

Aggiungere il server di distribuzione:

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

Creare il database di distribuzione:

EXECUTE sp_adddistributiondb @database = N'distribution';

Verificare che il database di distribuzione sia stato creato:

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

Aggiungere il server di pubblicazione al server di distribuzione:

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

Nota

Usare solo barre rovesciate (\) per il parametro @working_directory. Le barre oblique (/) causano errori di connessione.

Registrare il distributore presso l'editore

  1. Connettersi all'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher).
  2. Aprire una finestra Nuova query ed eseguire il comando seguente per registrare il server di distribuzione:
USE master;
GO

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

Verificare che il server di distribuzione sia registrato:

SELECT * FROM sys.servers WHERE is_distributor = 1;

Creare la pubblicazione

Dopo aver configurato la distribuzione, è possibile creare la pubblicazione. A tale scopo, effettuare i passaggi seguenti:

  1. Avvia SQL Server Management Studio su SQL Server.

  2. Connettersi all'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher).

  3. In Esplora oggetti espandere il nodo Replica e fare clic con il pulsante destro del mouse sulla cartella Local Publication. Selezionare Nuova pubblicazione.

  4. Selezionare Avanti per uscire dalla pagina iniziale.

  5. Nella pagina Database di pubblicazione selezionare il database ReplTutorial creato in precedenza. Selezionare Avanti.

  6. Nella pagina Tipo di pubblicazione selezionare Pubblicazione transazionale. Selezionare Avanti.

  7. Nella pagina Articoli selezionare la casella accanto a Tabelle. Selezionare Avanti.

  8. Nella pagina Filtro righe tabella selezionare Avanti senza aggiungere alcun filtro.

  9. Nella pagina Agente snapshot selezionare la casella di controllo Crea uno snapshot immediatamente e mantieni lo snapshot disponibile per l'inizializzazione delle sottoscrizioni. Selezionare Avanti.

  10. Nella pagina Sicurezza agente selezionare Impostazioni di sicurezza.... Specificare le credenziali di accesso di SQL Server da usare per l'agente snapshot e connettersi al server di pubblicazione. Selezionare OK per chiudere la pagina Sicurezza dell'agente snapshot. Selezionare Avanti.

    Screenshot che mostra come configurare la sicurezza dell'agente snapshot.

  11. Nella pagina Azioni procedura guidata scegliere Crea la pubblicazione e, facoltativamente, Genera un file script con i passaggi per la creazione della pubblicazione, se si vuole salvare lo script per un uso successivo.

  12. Nella pagina Completamento procedura guidata assegnare alla pubblicazione ReplTestil nome e selezionare Avanti per creare la pubblicazione.

  13. Dopo aver creato la pubblicazione, aggiornare il nodo Replica in Esplora oggetti ed espandere Pubblicazioni locali per visualizzare la nuova pubblicazione.

Creare la sottoscrizione

Una volta creata la pubblicazione, è possibile creare la sottoscrizione. A tale scopo, effettuare i passaggi seguenti:

  1. Avvia SQL Server Management Studio su SQL Server.
  2. Connettersi all'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher).
  3. Aprire una finestra Nuova query ed eseguire i comandi Transact-SQL seguenti. Usare il nome DNS configurato nella zona DNS privata come parte del nome del sottoscrittore.

Parametri della sottoscrizione

Parametro Value Description
@subscriber sql-vm-subscriber.repldns.com Nome DNS del Sottoscrittore (dalla zona DNS privata)
@destination_db ReplSub Database degli abbonati
@subscription_type Push Il server di distribuzione esegue il push delle modifiche al sottoscrittore
@sync_type automatic Sincronizzazione iniziale automatica

Aggiungere la sottoscrizione:

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;

Installa l'agente di sottoscrizione push:

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

Verificare che la sottoscrizione sia stata creata:

SELECT * FROM distribution.dbo.MSsubscriptions;

Replica del test

Dopo aver configurato la replica, è possibile testarla inserendo nuovi elementi nel server di pubblicazione e osservando le modifiche che si propagano nel sottoscrittore.

Visualizzare i dati iniziali nel sottoscrittore

Connettersi al sottoscrittore di SQL Server ed eseguire la query seguente:

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest;

Output previsto: 5 righe con ID 2, 3, 4, 5 e 6 (i dati iniziali dall'editore).

Inserire nuovi dati nel server di pubblicazione

Connettersi all'istanza gestita di SQL del server di pubblicazione (sql-mi-publisher) e inserire una nuova riga:

USE ReplTutorial;
GO

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

Verificare l'inserimento nel server di pubblicazione:

SELECT * FROM ReplTest WHERE ID = 15;

Verificare la replica nel sottoscrittore

Dopo alcuni istanti, connettersi al sottoscrittore e verificare la nuova riga replicata:

USE ReplSub;
GO

SELECT * FROM dbo.ReplTest WHERE ID = 15;

Output previsto: 1 riga con ID 15 e valore c1 'pub'.

Pulire le risorse

  1. Accedi al gruppo di risorse nel portale di Azure.
  2. Selezionare le istanze gestite di SQL e quindi selezionare Elimina. Digitare yes nella casella di testo per confermare l'eliminazione della risorsa e quindi selezionare Elimina. Questo processo potrebbe richiedere del tempo per il completamento in background e fino a quando non viene completato, non sarà possibile eliminare il cluster virtuale o altre risorse dipendenti. Monitorare l'eliminazione nella scheda Attività per verificare che l'istanza gestita di SQL sia stata eliminata.
  3. Dopo aver eliminato l'istanza gestita di SQL, eliminare il cluster virtuale selezionandolo nel gruppo di risorse e quindi scegliendo Elimina. Digitare yes nella casella di testo per confermare l'eliminazione della risorsa e quindi selezionare Elimina.
  4. Eliminare eventuali risorse rimanenti. Digitare yes nella casella di testo per confermare l'eliminazione della risorsa e quindi selezionare Elimina.
  5. Eliminare il gruppo di risorse selezionando Elimina gruppo di risorse, digitando il nome del gruppo myResourceGroupdi risorse e quindi selezionando Elimina.

Errori noti

Accessi Windows non sono supportati

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

L'agente è stato configurato con un account di accesso di Windows ed è necessario usare invece un account di accesso di SQL server. Usare la pagina Sicurezza agente di Proprietà pubblicazione per sostituire le credenziali di accesso con quelle di SQL Server.

Non è possibile connettersi a Archiviazione di Azure

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.

Ciò è probabile perché la porta 445 è chiusa nel firewall di Azure, in Windows Firewall o in entrambi.

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

L'uso di una barra anziché una barra rovesciata nel percorso del file per la condivisione file può anche causare questo errore.

  • Questo è accettabile: \\replstorage.file.core.windows.net\replshare
  • Ciò può causare un errore 55 del sistema operativo: \\replstorage.file.core.windows.net/replshare

Impossibile connettersi al Sottoscrittore

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.

Soluzioni possibili:

  • Assicurarsi che la porta 1433 sia aperta.
  • Assicurarsi che TCP/IP sia abilitato nel sottoscrittore.
  • Verificare che sia stato usato il nome DNS quando è stato creato il sottoscrittore.
  • Verificare che le reti virtuali siano correttamente collegate nella zona DNS privato.
  • Verificare che il record A sia configurato correttamente.
  • Assicurati che il peering di reti virtuali sia configurato correttamente.

Nessuna pubblicazione disponibile per la sottoscrizione

Quando si aggiunge una nuova sottoscrizione tramite la creazione guidata Nuova sottoscrizione , nella pagina Pubblicazione potrebbe essere presente che non sono presenti database e pubblicazioni elencate come opzioni disponibili e potrebbe essere visualizzato il messaggio di errore seguente:

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.

Sebbene sia possibile che questo messaggio di errore sia accurato e che non siano disponibili pubblicazioni nel server di pubblicazione a cui ci si è connessi o non si dispone di autorizzazioni sufficienti, anche una versione precedente di SQL Server Management Studio potrebbe causare questo errore. Provare ad eseguire l'aggiornamento a SQL Server Management Studio 18.0 o versione successiva per escludere questo problema come causa radice.