Replica transazionale con Istanza gestita di SQL di Azure

Articolo
10/06/2024

Si applica a: Istanza gestita di SQL di Azure SQL

La replica transazionale è una funzionalità di Istanza gestita di SQL di Azure e SQL Server che consente di replicare i dati da una tabella in Istanza gestita di SQL di Azure e da un'istanza di SQL Server verso tabelle posizionate in database remoti. Questa funzionalità consente di sincronizzare più tabelle in database diversi.

Panoramica

È possibile usare la replica transazionale per eseguire il push delle modifiche apportate in un'istanza gestita di SQL di Azure per:

Un database SQL Server (locale o in una macchina virtuale di Azure)
Un database in Database SQL di Azure
Un database dell'istanza in Istanza gestita di SQL di Azure

Nota

Per usare tutte le funzionalità di Istanza gestita di SQL di Azure, è necessario usare le versioni più recenti di SQL Server Management Studio (SSMS) e SQL Server Data Tools (SSDT).

Componenti

I componenti chiave della replica transazionale sono Server di pubblicazione, Server di distribuzione e Sottoscrittore, come illustrato nell'immagine seguente:

Diagramma della replica con Azure SQL.

Ruolo	Database SQL di Azure	Istanza gestita di SQL di Azure
Autore	No	Sì
Database di distribuzione	No	Sì
Sottoscrittore pull	No	Sì
Sottoscrittore push	Sì	Sì

Il server di pubblicazione pubblica le modifiche apportate in alcune tabelle (articoli) inviando gli aggiornamenti al server di distribuzione. Il server di pubblicazione può essere un'istanza gestita di SQL di Azure o un'istanza di SQL Server.

Il server di distribuzione raccoglie le modifiche negli articoli da un server di pubblicazione e li distribuisce ai sottoscrittori. Il server di distribuzione può essere un'istanza gestita di SQL di Azure o un'istanza di SQL Server (qualsiasi versione purché uguale o superiore alla versione del server di pubblicazione).

Il sottoscrittore riceve le modifiche apportate nel server di pubblicazione. Un'istanza di SQL Server e un'istanza gestita di SQL di Azure possono essere sia sottoscrittori push che pull, anche se una sottoscrizione pull non è supportata quando il server di distribuzione è un'istanza gestita di SQL di Azure e il sottoscrittore non lo è. Un database in database SQL di Azure può solo essere un sottoscrittore push.

Istanza gestita di SQL di Azure può supportare la funzione di sottoscrittore dalle versioni seguenti di SQL Server:

SQL Server 2016 e versioni successive
SQL Server 2014 RTM CU10 (12.0.4427.24) o SP1 CU3 (12.0.2556.4)
SQL Server 2012 SP2 CU8 (11.0.5634.1) o SP3 (11.0.6020.0) o SP4 (11.0.7001.0)

Nota

Per altre versioni di SQL Server che non supportano la pubblicazione in oggetti in Azure, è possibile usare il metodo di ripubblicazione dei dati per spostare i dati in versioni più recenti di SQL Server.

Se si cerca di configurare la replica usando una versione meno recente, potrebbero essere generati gli errori MSSQL_REPL20084 (il processo non è riuscito a connettersi al sottoscrittore) e MSSQL_REPL40532 (impossibile aprire il server <nome> richiesto dall'account di accesso. L'accesso non è riuscito).

Tipi di replica

Esistono diversi tipi di replica:

Replica	Database SQL di Azure	Istanza gestita di SQL di Azure
Transazionale standard	Sì (solo come sottoscrittore)	Sì
Snapshot	Sì (solo come sottoscrittore)	Sì
Replica di tipo merge	No	No
Peer-to-peer	No	No
Bidirezionale	No	Sì
Sottoscrizioni aggiornabili	No	No

Matrice di supportabilità

La matrice di supportabilità della replica transazionale per Istanza gestita di SQL di Azure corrisponde a quella per SQL Server.

Autore	Database di distribuzione	Sottoscrittore
SQL Server 2022	SQL Server 2022	SQL Server 2022 SQL Server 2019 SQL Server 2017
SQL Server 2019	SQL Server 2022 SQL Server 2019	SQL Server 2022 SQL Server 2019 SQL Server 2017 SQL Server 2016
SQL Server 2017	SQL Server 2022 SQL Server 2019 SQL Server 2017	SQL Server 2022 SQL Server 2019 SQL Server 2017 SQL Server 2016 SQL Server 2014
SQL Server 2016	SQL Server 2022 SQL Server 2019 SQL Server 2017 SQL Server 2016	SQL Server 2019 SQL Server 2017 SQL Server 2016 SQL Server 2014 SQL Server 2012
SQL Server 2014	SQL Server 2022 SQL Server 2019 SQL Server 2017 SQL Server 2016 SQL Server 2014	SQL Server 2017 SQL Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008
SQL Server 2012	SQL Server 2022 SQL Server 2019 SQL Server 2017 SQL Server 2016 SQL Server 2014 SQL Server 2012	SQL Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008
SQL Server 2008 R2 SQL Server 2008	SQL Server 2022 SQL Server 2019 SQL Server 2017 SQL Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008	SQL Server 2014 SQL Server 2012 SQL Server 2008 R2 SQL Server 2008

Quando utilizzare

La replica transazionale è utile negli scenari seguenti:

Pubblicare le modifiche apportate in una o più tabelle in un database e distribuirle in uno o più database in un'istanza di SQL Server o database SQL di Azure che ha sottoscritto le modifiche.
Mantenere più database distribuiti in stato sincronizzato.
Eseguire la migrazione di database da un'istanza di SQL Server o da Istanza gestita di SQL di Azure a un altro database pubblicando continuamente le modifiche.

Confrontare la sincronizzazione dati con la replica transazionale

Category	Sincronizzazione dei dati	Replica transazionale
Vantaggi	- Supporto attivo/attivo - Bidirezionale tra database locali e database SQL di Azure	- Latenza inferiore - Coerenza delle transazioni - Riutilizzo topologia esistente dopo la migrazione
Svantaggi	- Nessuna coerenza delle transazioni - Maggiore impatto sulle prestazioni	- Impossibilità di pubblicare da Database SQL di Azure - Alti costi di manutenzione

Configurazioni comuni

In generale, il server di pubblicazione e il database di pubblicazione devono entrambi essere nel cloud o locali. Sono supportate le configurazioni seguenti:

Server di pubblicazione con server di distribuzione locale in Istanza gestita di SQL

Singola istanza come server di pubblicazione e database di distribuzione.

Server di pubblicazione e server di distribuzione sono configurati all'interno di una singola istanza gestita di SQL e distribuiscono le modifiche a un'altra istanza gestita di SQL, a un database SQL o a un'istanza di SQL Server.

Server di pubblicazione con server di distribuzione remoto in Istanza gestita di SQL

In questa configurazione, un'istanza gestita di SQL pubblica le modifiche in un server di distribuzione posizionato in un'altra istanza gestita di SQL, in grado di servire molte istanze gestite di SQL di origine e di distribuire le modifiche in una o più destinazioni in database SQL di Azure, Istanza gestita di SQL di Azure o SQL Server.

Istanze separate per server di pubblicazione e database di distribuzione.

I database di pubblicazione e distribuzione sono configurati in due istanze gestite. Con questa configurazione, esistono alcuni vincoli:

Le due istanze gestite sono nella stessa rete virtuale.
Le due istanze gestite sono nella stessa posizione.

Server di pubblicazione/di distribuzione locale con sottoscrittore remoto

Database SQL di Azure come sottoscrittore.

In questa configurazione, un database in database SQL di Azure o Istanza gestita di SQL di Azure è un sottoscrittore. Questa configurazione supporta la migrazione dal database locale al database di Azure. Se un sottoscrittore è un database in database SQL di Azure, questo deve essere in modalità push.

Requisiti

Usare l'autenticazione SQL per la connettività tra i partecipanti alla replica.
Usare una condivisione di account di archiviazione di Azure per la directory di lavoro usata dalla replica.
Aprire la porta TCP in uscita 445 nelle regole di sicurezza della subnet per accedere alla condivisione file di Azure.
Aprire la porta TCP in uscita 1433 quando l'istanza gestita di SQL è il server di pubblicazione/di distribuzione e il sottoscrittore non lo è. Potrebbe anche essere necessario modificare la regola di sicurezza NSG in uscita dell'istanza gestita di SQL per allow_linkedserver_outbound per la porta 1433 Tag del servizio di destinazione da virtualnetwork a internet.
Posizionare sia il server di pubblicazione che il server di distribuzione nel cloud, oppure entrambi in locale.
Configurare il peering VPN tra le reti virtuali dei partecipanti alla replica, se le reti virtuali sono diverse.

Nota

È possibile che si verifichi l'errore 53 durante la connessione a un file di Archiviazione di Azure se la porta gruppo di sicurezza di rete (NSG) in uscita 445 è bloccata quando il server di distribuzione è un database di Istanza gestita di SQL di Azure e il sottoscrittore è locale. Aggiornare il gruppo di sicurezza di rete della rete virtuale per risolvere il problema.

Sicurezza

Accedere a `replAgentUser`

Ai fini della replica transazionale, un'istanza gestita di SQL ha un account di accesso creato in modo preliminare con il nome replAgentUser. Questo account di accesso è un membro del ruolo del server sysadmin e viene usato dagli agenti di replica che devono connettersi a un'istanza gestita di SQL che partecipa alla configurazione della replica transazionale.

Se la replica transazionale non viene utilizzata, l'account di accesso replAgentUser può essere disabilitato. Può essere riabilitato in un secondo momento se si decide di iniziare a usare la replica transazionale.

Limiti

La replica transazionale presenta alcune limitazioni che sono specifiche per Istanza gestita di SQL di Azure. In questa sezione sono disponibili altre informazioni su queste limitazioni.

I file di snapshot non vengono eliminati dall'account di archiviazione di Azure

Istanza gestita di SQL di Azure usa l'account di archiviazione di Azure configurato dall'utente per i file di snapshot usati per la replica transazionale. A differenza di SQL Server nell'ambiente locale, Istanza gestita di SQL di Azure non elimina i file di snapshot dall'account di archiviazione di Azure. Una volta che i file non sono più necessari, eliminarli. Questa operazione può essere eseguita tramite l'interfaccia di Archiviazione di Azure in portale di Azure, Microsoft Azure Storage Explorer o tramite client della riga di comando (Azure PowerShell o interfaccia della riga di comando) o API REST di Gestione di Archiviazione di Azure.

Ecco un esempio di come eliminare un file e di come eliminare una cartella vuota.

CLI

az storage file delete-batch --source <file_path> --account-key <account_key> --account-name <account_name>
az storage directory delete --name <directory_name> --share-name <share_name> --account-key <account_key> --account-name <account_name>

Numero di agenti di distribuzione in esecuzione continua

Il numero di agenti di distribuzione configurati per l'esecuzione continua è limitato a 30 in Istanza gestita di SQL di Azure. Per avere più agenti di distribuzione, è necessario eseguirli su richiesta o con una pianificazione definita. La pianificazione può essere definita con frequenza giornaliera e occorrenza ogni 10 secondi (o più), quindi anche se non è continua, è comunque possibile che il server di distribuzione introduca una latenza solo di alcuni secondi. Quando è necessario un grande numero di server di distribuzione, è consigliabile usare la configurazione pianificata e non continua.

Con gruppi di failover

È supportato l’uso della replica transazionale con istanze incluse in un gruppo di failover. Tuttavia, se si configura la replica prima di aggiungere l'istanza gestita di SQL in un gruppo di failover, la replica viene sospesa quando si inizia a creare il gruppo di failover, e il monitoraggio della replica mostra lo stato di Replicated transactions are waiting for the next log backup or for mirroring partner to catch up. La replica riprende una volta completata la creazione del gruppo di failover.

Se un’istanza gestita di SQL di pubblicazione o distribuzione si trova in un gruppo di failover, l'amministratore dell'istanza gestita di SQL deve pulire tutte le pubblicazioni nel database primario precedente e riconfigurarle nel nuovo database primario dopo che si verifica un failover. In questo scenario sono necessarie le seguenti attività:

Se presenti, arrestare tutti i processi di replica in esecuzione nel database.
Eliminare i metadati delle sottoscrizioni dal server di pubblicazione eseguendo lo script seguente nel database del server di pubblicazione. Sostituire i valori <name of publication> e <name of subscriber>:

SQL
```
EXEC sp_dropsubscription @publication = '<name of publication>',
    @article = 'all',
    @subscriber = '<name of subscriber>'
```
Eliminare i metadati delle sottoscrizioni dal sottoscrittore. Eseguire il seguente script nel database di sottoscrizione nell'istanza gestita di SQL sottoscrittore. Sostituire il valore <full DNS of publisher>. Ad esempio, example.ac2d23028af5.database.windows.net:

SQL
```
EXEC sp_subscription_cleanup
   @publisher = N'<full DNS of publisher>',
   @publisher_db = N'<publisher database>',
   @publication = N'<name of publication>';
```
Eliminare forzatamente tutti gli oggetti di replica dal server di pubblicazione eseguendo il seguente script nel database pubblicato:

SQL
```
EXEC sp_removedbreplication;
```
Eliminare forzatamente il server di distribuzione precedente dall'istanza gestita di SQL primaria originale (se si esegue il failover in un database primario precedente che aveva un server di distribuzione). Eseguire il seguente script nel database master nell'istanza gestita di SQL del server di distribuzione precedente:

SQL
```
EXEC sp_dropdistributor 1, 1;
```

Se un'istanza gestita di SQL del sottoscrittore si trova in un gruppo di failover, la pubblicazione deve essere configurata per connettersi all'endpoint listener del gruppo di failover per l'istanza gestita di SQL sottoscrittore. In caso di failover, l'azione successiva dell'amministratore dell'istanza gestita di SQL dipende dal tipo di failover che si è verificato:

In un failover senza perdita di dati, la replica continuerà a funzionare dopo il failover.
In un failover con perdita di dati, anche la replica funziona. Replica di nuovo le modifiche perse.
Per un failover con perdita di dati che però non rientra nel periodo di conservazione del database di distribuzione, l'amministratore dell'istanza gestita di SQL deve reinizializzare il database di sottoscrizione.

Risolvere i problemi comuni

Log delle transazioni e replica transazionale

In circostanze consuete, il log delle transazioni viene usato per registrare le modifiche dei dati all'interno di un database. Le modifiche vengono registrate nel log delle transazioni e questo fa aumentare il consumo di archiviazione dei log. Esiste anche un processo automatico che consente il troncamento sicuro del log delle transazioni, e questo processo riduce lo spazio di archiviazione usato per il log. Quando è configurata la pubblicazione per la replica transazionale, il troncamento del log delle transazioni viene impedito fino a quando le modifiche nel log non vengono elaborate dal processo di lettura log. In alcune circostanze, l'elaborazione del log delle transazioni viene effettivamente bloccata e questo stato può causare la compilazione dell'intera risorsa di archiviazione riservata per il log delle transazioni. Quando non c'è spazio disponibile per il log delle transazioni e non c'è più spazio per l'aumento del log delle transazioni, si verifica un log delle transazioni pieno. In questo stato, il database non può più elaborare alcun carico di lavoro di scrittura e diventa a tutti gli effetti un database di sola lettura.

Agente di lettura log disabilitato

A volte la pubblicazione della replica transazionale è configurata per un database, ma l'agente di lettura log non è configurato per l'esecuzione. In tal caso, le modifiche si accumulano nel log delle transazioni e non vengono elaborate. Ciò comporta una crescita costante del log transazionale e alla fine al log delle transazioni completo. L'utente deve assicurarsi che esista e sia attivo il processo di lettura log. In alternativa, è possibile disabilitare la replica transazionale, se non necessaria.

Timeout delle query dell'agente di lettura log

In alcuni casi, il processo di lettura log non può fare progressi effettivi a causa di timeout ripetuti delle query. Un modo per correggere i timeout delle query consiste nell'aumentare l'impostazione di timeout delle query per il processo dell'agente di lettura log.

L'aumento del timeout delle query per il processo di lettura log può essere eseguito con SSMS. In Esplora oggetti, alla voce SQL Server Agent, trovare il processo che si vuole modificare. Per prima cosa, arrestarlo, quindi aprire le relative proprietà. Trovare step 2 e modificarlo. Accodare al valore del comando -QueryTimeout <timeout_in_seconds>. Per il valore di timeout della query, provare 21600 o superiore. Infine, riavviare il processo.

Le dimensioni di archiviazione dei log hanno raggiunto il limite massimo di 2 TB

Quando le dimensioni di archiviazione del log delle transazioni raggiungono il limite massimo, ovvero 2 TB, il log fisicamente non può aumentare oltre. In questo caso, l'unica mitigazione disponibile è contrassegnare tutte le transazioni che devono essere replicate come elaborate, per consentire il troncamento del log delle transazioni. Questo significa che le transazioni rimanenti nel log non verranno replicate ed è necessario reinizializzare la replica.

Nota

Dopo aver eseguito la mitigazione, sarà necessario reinizializzare la replica, ovvero replicare di nuovo l'intero set di dati. Si tratta di un'operazione di ridimensionamento dei dati e potrebbe essere a esecuzione prolungata, a seconda della quantità di dati da replicare.

Per eseguire la mitigazione, per prima cosa è necessario arrestare l'agente di lettura log nel server di distribuzione. È quindi necessario eseguire la stored procedure sp_repldone con il flag reset impostato su 1 nel database del server di pubblicazione per consentire il troncamento del log delle transazioni. Questo comando dovrebbe essere simile a EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1. Quindi, sarà necessario reinizializzare la replica.

Passaggi successivi

Per altre informazioni sulla configurazione della replica transazionale, vedere le esercitazioni seguenti:

Configurare la replica tra un server di pubblicazione e un sottoscrittore di Istanza gestita di SQL.
Configurare 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.
Creare una pubblicazione.
Creare una sottoscrizione push usando il nome del server come sottoscrittore (ad esempio, N'azuresqldbdns.database.windows.net) e il nome del database in database SQL di Azure come database di destinazione (ad esempio, Adventureworks).

Condividi tramite

Replica transazionale con Istanza gestita di SQL di Azure