Come configurare la replica di MS SQL Server

Microsoft SQL Server è un software di gestione di database installabile sui sistemi operativi Windows Server. I database sono utilizzati dalle aziende di tutti i settori e molte soluzioni software si avvalgono di database, sia centralizzati che distribuiti. La disponibilità dei database e la coerenza dei dati sono fondamentali per le aziende, rendendo indispensabili il backup e la replica dei database.
Scopri i tipi di replica di SQL Server, come funziona la replica in SQL Server e come eseguire la replica di SQL Server.

NAKIVO per il backup su Windows

Backup rapido di server e workstation Windows su supporto onsite, remoto e nel cloud. Ripristino completo di macchine e oggetti in pochi minuti, per tempi di ripristino (RTO) ridotti e massima operatività.

RILEVA LA SOLUZIONE

Che cos’è la replica in SQL Server?

La replica in MS SQL Server è il processo di copia dei dati da un database a un altro, inclusi specifici oggetti del database, e di mantenimento di una copia sincronizzata di tali dati tra il database di origine e quello di destinazione. Grazie alla replica in SQL Server, è possibile creare una copia identica del database primario e sincronizzare le modifiche tra i due database, mantenendo al contempo la coerenza e l’integrità dei dati.

La terminologia utilizzata per la replica in MS SQL Server

Prima di approfondire come configurare e impostare la replica in MS SQL Server, esaminiamo brevemente i termini principali e i modelli di replica.
Gli articoli sono le unità di base da replicare, quali tabelle, procedure, funzioni e viste. Gli articoli possono essere scalati verticalmente o orizzontalmente utilizzando i filtri. È possibile creare più articoli per lo stesso oggetto.
Una pubblicazione è un insieme logico di articoli. Si tratta dell’insieme finale di entità del database designate per la replica.
Un filtro è un insieme di condizioni relative a un articolo. La replica di MS SQL Server consente di utilizzare filtri e selezionare entità personalizzate per la replica, riducendo così il traffico, la ridondanza e la quantità di dati memorizzati in una replica del database. Ad esempio, è possibile selezionare solo le tabelle e i campi più critici utilizzando i filtri e quindi replicare solo questi dati.
Gli agenti sono componenti di MS SQL Server che possono fungere da servizi in background per i sistemi di gestione di database relazionali e vengono utilizzati per pianificare l’esecuzione automatizzata di lavori, come il backup e la replica del database MS SQL. Esistono cinque tipi di agenti: Snapshot Agent, Log Reader Agent, Distribution Agent, Merge Agent e Queue Reader Agent.
I metadati sono i dati utilizzati per descrivere le entità del database. Esiste un’ampia gamma di funzioni integrate relative ai metadati che consentono di ottenere informazioni sull’istanza di MS SQL Server, sulle istanze di database e sulle entità del database.

Ruoli nella replica del database SQL

Esistono tre ruoli principali nella replica del database MS SQL: distributore, editore e sottoscrittore.

• Un distributore è un’istanza di database MS SQL configurata per raccogliere le transazioni dalle pubblicazioni e distribuirle ai sottoscrittori. Un distributore funge da database per l’archiviazione delle transazioni replicate.

  Un database distributore può essere considerato contemporaneamente sia come editore che come distributore. Nel modello con distributore locale, un’unica istanza di MS SQL Server esegue sia l’editore (Publisher) che il distributore (Distributor). È possibile utilizzare un modello con distributore remoto quando si desidera che gli abbonati siano configurati per utilizzare un’unica istanza di MS SQL Server per ricevere diverse pubblicazioni (distribuzione centralizzata). In questo modello, l’editore e il distributore vengono eseguiti su server diversi.

• Un editore è la copia principale del database su cui è configurata la pubblicazione, che rende i dati disponibili ad altri server MS SQL configurati per essere utilizzati nel processo di replica. L’editore può avere più di una pubblicazione.
• Un abbonato è un database che riceve i dati replicati da una pubblicazione. Un singolo abbonato può ricevere dati da più di un editore e da più pubblicazioni. Il modello a singolo abbonato viene utilizzato quando è presente un solo abbonato. Un modello multi-sottoscrittore viene utilizzato quando più sottoscrittori sono collegati a una singola pubblicazione.

  In abbonamento è una richiesta di una copia di una pubblicazione che deve essere consegnata al sottoscrittore. L’abbonamento viene utilizzato per definire i dati della pubblicazione che devono essere ricevuti e dove e quando tali dati saranno ricevuti. Esistono due tipi di abbonamenti:

  • Abbonamento push : I dati modificati vengono trasmessi in modo forzato da un distributore a un database sottoscrittore. Non è necessaria alcuna richiesta da parte dell’abbonato.
  • Abbonamento pull : i dati modificati sull’editore vengono richiesti dall’abbonato. L’agente viene eseguito sul lato dell’abbonato.

  Un database in abbonamento è un database di destinazione nel modello di replica di MS SQL.

  

Nel modello «più editori – più abbonati» , l’editore può fungere da abbonato su uno dei server MS SQL. Assicurarsi di evitare potenziali conflitti di aggiornamento quando si utilizza questo modello di replica di MS SQL Server.

Tipi di replica di MS SQL Server

La replica di MS SQL Server è una tecnologia che consente di copiare e sincronizzare i dati tra database in modo continuo o a intervalli regolari prestabiliti. Per quanto riguarda la direzione della replica, la replica di MS SQL Server può essere unidirezionale, uno-a-molti, bidirezionale e molti-a-uno. Esistono quattro tipi di replica di MS SQL Server: replica snapshot, replica transazionale, replica peer-to-peer e replica di unione.

Replica snapshot

Replica snapshot viene utilizzata per replicare i dati esattamente così come appaiono nel momento in cui viene creato lo snapshot del database. Questo tipo di replica è adatto per dati che non subiscono modifiche frequenti, quando il fatto che la replica del database sia più vecchia del database master non rappresenta un problema critico, oppure quando viene effettuato un volume elevato di modifiche in un breve lasso di tempo. Il tracciamento delle modifiche non viene utilizzato con la replica snapshot.
Ad esempio, la replica snapshot può essere utilizzata quando i tassi di cambio o i listini prezzi vengono aggiornati una volta al giorno e devono essere distribuiti dal server principale ai server delle filiali.

Replica transazionale

La replica transazionale è una replica periodica automatizzata in cui i dati vengono distribuiti da un database master a una replica del database in tempo reale (o quasi in tempo reale). La replica transazionale è più complessa della replica snapshot. Vengono replicate tutte le transazioni effettuate, nonché lo stato finale del database, il che consente di monitorare l’intera cronologia delle transazioni sulla replica.
All’inizio del processo di replica transazionale, viene applicato uno snapshot al sottoscrittore, dopodiché i dati vengono trasferiti continuamente dal database master a una replica del database man mano che vengono apportate modifiche a tali dati. La replica transazionale è ampiamente utilizzata come replica unidirezionale.

Caso d’uso della replica transazionale:

• Creazione di un server di database con una replica da utilizzare per il failover in caso di guasto del server di database principale.
• Ricezione di report sulle operazioni eseguite nelle filiali utilizzando più editori nelle filiali e un unico sottoscrittore nella sede centrale.
• Replica delle modifiche non appena vengono apportate.
• I dati nel database di origine cambiano frequentemente.

Replica peer-to-peer

Replica peer-to-peer viene utilizzata per replicare i dati del database a più destinatari contemporaneamente. Questo tipo di replica di MS SQL Server può essere utilizzato quando i server di database sono distribuiti in tutto il mondo. Le modifiche possono essere apportate su uno qualsiasi dei server di database. Le modifiche vengono propagate a tutti i server di database. La replica peer-to-peer può aiutare a migliorare la scalabilità di un’applicazione che utilizza un database. Il principio di funzionamento principale si basa sulla replica transazionale.

Di seguito è possibile vedere come la replica peer-to-peer di MS SQL Server possa essere utilizzata tra server di database distribuiti in tutto il mondo.

Replica di unione

Replica di unione è un tipo di replica bidirezionale solitamente utilizzata in ambienti server-client per sincronizzare i dati tra server di database quando questi non possono rimanere costantemente connessi. Quando viene stabilita la connessione di rete tra entrambi i server di database, gli agenti di replica di unione rilevano le modifiche apportate su entrambi i database e modificano i database per sincronizzarne e aggiornarne lo stato. La replica di unione è simile alla replica transazionale, ma i dati vengono replicati dall’editore (Publisher) al sottoscrittore (Subscriber) e viceversa.

Questo tipo di replica di database è il più complesso tra tutti i tipi di replica di MS SQL Server e viene utilizzato raramente. Ad esempio, la replica di unione può essere utilizzata da più punti vendita alla pari che operano con un magazzino condiviso. A ciascun punto vendita è consentito modificare le informazioni nel database del magazzino e, allo stesso tempo, tutti i punti vendita devono disporre dello stato aggiornato dei propri database dopo la spedizione delle merci o la consegna delle forniture al magazzino. La replica di unione può essere utilizzata nei casi in cui le informazioni aggiornate debbano essere disponibili contemporaneamente sia per il database principale (o centrale) che per i database delle filiali.

Requisiti per la replica di MS SQL Server

Le seguenti porte devono essere aperte per il traffico in entrata:

• TCP 1433, 1434, 2383, 2382, 135, 80, 443
• UDP 1434

Assicurarsi di configurare il firewall di Windows e abilitare le porte appropriate per il traffico in entrata su ciascun host prima di installare MS SQL Server. Gli host coinvolti nella replica di MS SQL devono risolversi a vicenda tramite un nome host.
Prima di configurare la replica di MS SQL Server, è necessario installare il seguente software per MS SQL Server:

• .NET Framework – un insieme di librerie
• MS SQL Server – il software del server di database
• MS SQL Server Management Studio (SSMS) – software per la gestione dei database MS SQL tramite l’interfaccia grafica (GUI).

NOTA: In questo articolo viene utilizzato MS SQL Server 2016 per la configurazione. È possibile applicare lo stesso principio per configurare la replica in versioni più recenti di SQL Server.
Tenete presente che se installate MS SQL Server 2016 sul primo computer in cui si trova l’ubicazione del database di origine, dovrete installare MS SQL Server 2016 anche sul secondo computer affinché il database funzioni correttamente. Ad esempio, se si desidera configurare la replica transazionale di MS SQL, è possibile utilizzare il secondo server di database (su cui è configurato il Subscriber) di una versione che non differisca di più di due versioni rispetto al server di database di origine su cui è configurato l’Publisher. Se la versione dell’editore su MS SQL Server è la 2016, il distributore può essere configurato sulle versioni 2016, 2017, 2019 e 2022, mentre l’abbonato può essere configurato su MS SQL Server 2012, 2014, 2016, 2017 e 2019. La versione del distributore non può essere inferiore a quella dell’editore. La replica non funzionerà se, ad esempio, si installa MS SQL Server 2008 sul secondo computer.

Raccomandazioni di base per la replica di database MS SQL

Prima di configurare l’ambiente per MS SQL Server, ecco alcuni fattori da considerare:

• Esistono limitazioni relative ai campi di identità e ai trigger.
• Le pubblicazioni possono contenere solo tabelle con chiave primaria.
• Si raccomanda di non utilizzare la pianificazione della creazione di snapshot per database di grandi dimensioni, al fine di evitare un consumo eccessivo di risorse di calcolo.
• Prestare attenzione quando si modificano i dati nella replica del database presente sul sottoscrittore. Quando è in arrivo una transazione che modifica i dati e tali dati sono stati modificati o eliminati, la replica potrebbe arrestarsi fino alla risoluzione del problema.

Configurazione dell’ambiente

Quando si configura la replica di MS SQL per la prima volta, si consiglia di farlo inizialmente in un ambiente di test. Ad esempio, configuriamo la replica su server SQL in esecuzione su VM. In questo tutorial vengono utilizzati due host con Windows Server 2016 e MS SQL Server 2016 per illustrare la replica di MS SQL Server.
Diamo un’occhiata alla configurazione dell’ambiente di test utilizzato per la stesura di questo articolo del blog, al fine di comprendere meglio la configurazione della replica di MS SQL Server.
Host 1

• Indirizzo IP: 192.168.101.101
• Nome host: MSSQL01
• ID istanza di MS SQL Server: MSSQLSERVER1

Host 2

• Indirizzo IP: 192.168.101.102
• Nome host: MSSQL02
• ID istanza di MS SQL Server: MSSQLSERVER2

Entrambe le macchine presentano il disco C: e il disco D: nella propria configurazione dei dischi.
È possibile disabilitare temporaneamente il firewall di Windows durante l’installazione di MS SQL Server per esercitarsi nella configurazione della replica di MS SQL Server. Questo articolo del blog non approfondisce le modalità di installazione di MS SQL Server, poiché il presente tutorial si concentra sulla configurazione della replica di MS SQL Server. In questo esempio, entrambi i server MS SQL sono installati senza PolyBase.
Una volta completata l’installazione di MS SQL Server, verificare di aver installato le funzioni obbligatorie per la replica di MS SQL Server. Si noti che i servizi del motore di database, come la replica di SQL Server e R-Services, devono essere selezionati durante l’installazione di MS SQL Server. In questo esempio viene utilizzato il percorso di installazione predefinito (C:Program FilesMicrosoft SQL Server).

Altre impostazioni:

• Modalità di autenticazione mista (autenticazione Windows e autenticazione MS SQL Server)
• Directory radice dei dati: D:MSSQL_Server
• Directory del database di sistema: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directory del database degli utenti: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directory del log del database degli utenti: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directory di backup: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup

Una volta installati MS SQL Server 2016 e SQL Server Management Studio sui computer, è possibile preparare i server MS SQL per la replica del database.

Preparazione alla replica di MS SQL Server

È necessario configurare i server prima di poter avviare la replica del database. Nel nostro esempio, verrà utilizzato un unico account Windows per gli agenti di replica di MS SQL Server.

1. Creare l’utente mssql su entrambi i server e impostare la stessa password.
2. L’utente mssql in questo esempio è membro dei seguenti gruppi:
   • Administrators (amministratori locali sui computer locali, non amministratori di dominio)
   • SQLRUserGroupMSSQLSERVER1
   • SQLServer2005SQLBrowserUser$MSSQL01
3. È possibile modificare utenti e gruppi premendo Win+R , aprendo CMD ed eseguendo il comando lusrmgr.msc .

I due computer Windows Server utilizzati in questo esempio non fanno parte di Active Directory. Se si utilizza Active Directory, è possibile creare l’utente mssql sul controller di dominio.

Connessione a MS SQL Server

1. Eseguire SQL Server Management Studio.
2. Accedere (vedere la schermata) come sa utilizzando l’autenticazione di SQL Server.
   • MSSQL01MSSQLSERVER1 è il nome host e il nome dell’istanza di MS SQL sul primo server.
   • MSSQL02MSSQLSERVER2 è il nome host e il nome dell’istanza di MS SQL sul secondo server.

   

Allo stesso modo, è possibile connettersi dal secondo server (MSSQL02) alla seconda istanza di MS SQL Server (MSSQLSERVER2). È inoltre possibile connettersi alla seconda istanza di MS SQL Server (MSSQLSERVER2) dal primo MS SQL Server (MSSQL01) inserendo le credenziali in SQL Server Management Studio. È possibile connettersi a entrambe le istanze di MS SQL Server (MSSQL01 e MSSQL02) in un’unica istanza di SQL Server Management Studio.
Per farlo, nell’Esplora oggetti, fare clic su Connetti > Motore di database . In questo tutorial, ci collegheremo a MSSQLSERVER1 da MSSQL01 e a MSSQLSERVER2 da MSSQL02 utilizzando SQL Server Management Studio per configurare i server MS SQL.

Avvio dell’Agente

Una volta effettuato l’accesso all’istanza di MS SQL Server, noterete che l’Agente non è in esecuzione. Per impostazione predefinita, SQL Server Agent non si avvia automaticamente. È possibile avviare questo servizio manualmente, ma è preferibile configurarlo in modo che si avvii automaticamente all’avvio di Windows.

Per configurare il servizio SQL Server Agent in modo che si avvii automaticamente:

1. Premere Win+R , eseguire cmd, ed eseguire il comando services.msc .
2. Aprire le proprietà del servizio SQL Server Agent e impostare il tipo di avvio su Automatico .

   

Configurazione degli utenti per MS SQL Server

Dopo essersi connessi all’istanza MSSQLSERVER1 in SQL Server Management Studio, è necessario configurare gli utenti:

1. Andare su Esplora oggetti e aprire Sicurezza > Accessi .
2. Fare clic con il tasto destro del mouse su Accessi e selezionare Nuovo accesso . Selezionare Autenticazione Windows .
3. Immettere il nome di accesso mssql nella sezione Generale .
4. Fare clic su Cerca , quindi selezionare Verifica nomi per confermare e fare clic due volte su OK per salvare le impostazioni.

   

5. Ora l’utente MSSQL01mssql di Windows è stato aggiunto all’elenco degli utenti autorizzati ad accedere al database (analogamente, aggiungere l’utente mssql a logins sul secondo computer MSSQL02 in SQL Server Management Studio).
6. Aggiungere l’utente mssql ai ruoli di server sysadmins nella configurazione Sicurezza del database in SQL Server Management Studio.
7. Vai su MSSQL01MSSQLSERVER1 > Ruoli del server , fai clic con il tasto destro su sysadmin e apri Proprietà .
8. Nella pagina Membri , fai clic su Aggiungi , inserisci il nome del tuo utente mssql, e fai clic su Verifica nomi .
9. Seleziona la casella di controllo del nome utente MSSQL01mssql e fai clic su OK .

   

10. Eseguire la stessa configurazione sul secondo computer (in questo caso MSSQL02).
11. Riavvia entrambi i computer.

    Ora puoi accedere utilizzando l’autenticazione di Windows su entrambi i server.

    

Importazione di un database da un backup

Importiamo un database di esempio da un backup e poi replichiamo il database dal primo computer al secondo. Il database AdventureWorks2016 viene utilizzato come database di esempio in questo esempio.

1. Copia il file di backup del database AdventureWorks2016.bak nella tua directory di backup di MSSQL. Nel nostro caso, questa directory sul primo server è D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. Importare un database di esempio. Sul primo computer, in SQL Server Management Studio, andare su MSSQL01MSSQLSERVER1 , fare clic con il tasto destro del mouse su Databases, e selezionare Restore Database dal menu contestuale.

   

3. Nella finestra Ripristina database , selezionare i parametri necessari:
   • Origine: Dispositivo .
   • Fare clic sui tre puntini per sfogliare il file di backup del database.
     • Nella finestra Seleziona dispositivi di backup , selezionare il tipo di supporto di backup: file .
     • Fare clic su Aggiungi .
   • Selezionare il file .bak necessario – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Premere OK , quindi premere OK ancora una volta.
4. Il database AdventureWorks2016 è stato ripristinato con successo.

   

È possibile importare il database da un backup sul secondo computer, dove sarà in esecuzione la replica del database. Questo approccio consente di ridurre il traffico di rete poiché la replica inizierà copiando le modifiche apportate dopo la creazione del backup, senza copiare l’intero contenuto del database in un database vuoto.
Ripristinare il database da un backup sul secondo server e rinominare il database in AdventureWorks2016r , dove “r” sta per “replica”.
Infine, abbiamo:

Dopo aver importato il database, è necessario eseguire alcune operazioni di ottimizzazione per preparare i server MS SQL

1. Sul MSSQL01 computer, andare su MSSQL01MSSQLSERVER1 > Sicurezza > Logins , selezionare MSSQL01mssql . Fare clic con il tasto destro del mouse (o fare doppio clic) su mssql utente e selezionare Proprietà .
2. In Ruoli del server , selezionare la casella di controllo accanto al ruolo dbcreator .

   

3. Nella pagina Mappatura utenti , selezionare gli utenti mappati a questo accesso e spuntare la casella di controllo database AdventureWorks2016 (selezionare AdventureWorks2016r sul secondo server di conseguenza).
4. Nella sezione Appartenenza ai ruoli del database , spuntare la casella di controllo db_owner .

   

5. Fare clic su OK per salvare le impostazioni.

Eseguire la stessa configurazione sul computer MSSQL02. Successivamente, è possibile configurare i componenti di MS SQL Server necessari per la replica del database.

Trova il piano più adatto al tuo ambiente

Scopri le edizioni e i modelli di licenza flessibili di NAKIVO, pensati per garantire una protezione dei dati di livello Enterprise a un costo adeguato al tuo budget.

Visualizza i prezzi

Configurazione della replica del database

La configurazione della replica in modalità grafica è il metodo più pratico. La configurazione descritta di seguito viene eseguita in SQL Server Management Studio. In questo esempio viene illustrata la replica transazionale del database, poiché si tratta di uno dei tipi di replica più utilizzati in MS SQL Server.
La schermata riportata di seguito mostra la visualizzazione sul server del database principale (MSSQL01MSSQLSERVER1) e quella sul secondo server (MSSQL02MSSQLSERVER2) in SQL Server Management Studio.

Configurazione della distribuzione

La distribuzione può essere utilizzata per più editori e sottoscrittori. In questo esempio, la distribuzione è configurata sul server principale su cui è archiviato il database di origine. Sul server principale (MSSQL01MSSQLSERVER1), fare clic con il tasto destro del mouse su Replica e, nel menu contestuale, selezionare Configura distribuzione .

Si apre la Procedura guidata di configurazione della distribuzione .

1. Distributore . In questo esempio, selezionare l’istanza di database corrente in esecuzione sul server principale (MSSQL01MSSQLSERVER1) affinché funga da distributore. Fare clic su Avanti ogni volta per passare alla fase successiva della procedura guidata.
2. Avvio di SQL Server Agent . Se non è stato configurato l’avvio automatico di MS SQL Server Agent, come spiegato in precedenza, verrà visualizzato il seguente messaggio. Selezionare Sì, configura il servizio SQL Server Agent per l’avvio automatico .

   

3. Cartella delle istantanee . È possibile mantenere il percorso predefinito. È necessaria un’istantanea per l’inizializzazione della replica. Assicurarsi che vi sia spazio libero sufficiente sul disco alla posizione in cui si trova la directory degli snapshot. La quantità di spazio libero deve corrispondere almeno alla dimensione del database replicato.
4. Database di distribuzione . Immettere il nome del database di distribuzione. È possibile lasciare il nome predefinito ( distribution ) e le cartelle per il file del database di distribuzione e il file di log.

   

5. Editori . Definire gli editori di replica di MS SQL Server che possono accedere al distributore. Selezionare la casella di controllo accanto al nome del database di distribuzione sull’istanza primaria di MS SQL Server (che ospita un database di origine che verrà replicato). In questo esempio, si tratta dell’istanza MSSQL01MSSQLSERVER1 e il nome del database di distribuzione è distribution .
6. Azioni della procedura guidata . Selezionare Configura distribuzione per configurare la distribuzione durante la fase finale della procedura guidata. In questo esempio, non genereremo un file di script da eseguire in un secondo momento.

   

7. Completa la procedura guidata . Verificare il riepilogo della configurazione della distribuzione e fare clic su Termina per creare il distributore.

   

8. Lo stato Successo dovrebbe apparire se il distributore è stato creato e configurato correttamente.

   

Se viene segnalato un errore durante la configurazione dell’avvio automatico di SQL Server Agent, accedere alla configurazione dei servizi e verificare la modalità di avvio di SQL Server Agent (vedere come configurare l’avvio di Agent più sopra in questo post del blog).
È inoltre possibile aprire le proprietà di SQL Server Agent in SQL Server Management Studio e verificare lo stato del servizio e le opzioni di riavvio. Fare clic con il tasto destro del mouse su SQL Server Agent alla fine dell’elenco in Esplora oggetti e selezionare Proprietà per visualizzare o modificare le proprietà dell’agente.

Configurazione dell’editore

Una volta configurata la distribuzione, è possibile configurare l’editore. L’editore deve essere configurato sul server principale (MSSQL01MSSQLSERVER1) in cui è archiviato il database master da replicare. Selezionare Replica , fare clic con il tasto destro del mouse su Pubblicazioni locali e, nel menu contestuale, selezionare Nuova pubblicazione .

Si apre la Procedura guidata Nuova pubblicazione .

1. Database di pubblicazione . Selezionare il database che si desidera replicare ( AdventureWorks2016 in questo caso). Fare clic su Avanti in ogni passaggio della procedura guidata per procedere.

   

2. Tipo di pubblicazione . Per questo passaggio, è possibile selezionare i tipi di replica di MS SQL Server per un database. Selezioniamo una pubblicazione transazionale, che è un tipo di replica ampiamente utilizzato.
3. Articoli . Selezionare gli oggetti necessari, quali tabelle, procedure, viste, viste indicizzate e funzioni definite dall’utente da pubblicare come articoli. È possibile selezionare la replica dei campi personalizzati nelle tabelle e impostare le proprietà degli articoli, se necessario. In questo esempio sono state selezionate alcune tabelle.

   

4. Filtra righe della tabella . In questo esempio non sono stati aggiunti filtri (questa è la configurazione predefinita dei filtri). È possibile aggiungere filtri se necessario.
5. Agente snapshot . Specificare quando eseguire l’agente snapshot. Configuriamo l’Agent affinché venga eseguito immediatamente. Selezionare Crea immediatamente uno snapshot e mantieni lo snapshot disponibile per inizializzare le sottoscrizioni .

   

6. Sicurezza dell’Agent . Selezionare Utilizza le impostazioni di sicurezza dello Snapshot Agent . Fare clic sul pulsante Impostazioni di sicurezza per selezionare l’account con cui verrà eseguito l’Agent.

   Nella finestra Sicurezza dello Snapshot Agent che si apre, inserire le credenziali dell’utente Windows mssql creato in precedenza. Selezionare “Connetti all’editore” impersonando l’account di processo . Fare clic su OK per salvare le impostazioni e tornare alla procedura guidata.

   

   Dopo aver definito l’utente necessario, è possibile visualizzarlo nelle sezioni “Snapshot Agent” e “Log Reader Agent” .

   

7. “Azioni della procedura guidata” . Selezionare la casella di controllo in alto per creare la pubblicazione durante il passaggio finale della procedura guidata.
8. Completare la procedura guidata . Verifica la configurazione della pubblicazione e fai clic su Termina per creare una nuova pubblicazione.

   

Nella finestra Creazione pubblicazione puoi effettuare il monitoraggio dello stato di avanzamento della creazione di una nuova pubblicazione. Attendi qualche istante e, se tutto è stato eseguito correttamente, dovresti vedere lo stato di completamento.

La pubblicazione è ora creata e puoi visualizzarla in Esplora oggetti andando su Replica > Pubblicazioni locali .

Configurazione del sottoscrittore

Come ricorderai, la replica di MS SQL Server può essere di tipo pull o push. Se si configura la replica push, è necessario configurare il sottoscrittore in modo che esegua gli agenti sul server del database principale (in questo caso MSSQL01). Se si configura la replica pull, il sottoscrittore deve essere configurato per eseguire gli agenti sulla seconda macchina (MSSQL02), ovvero la macchina su cui verrà creata la replica del database.
Configuriamo la replica push e creiamo una nuova sottoscrizione sul primo MS SQL Server (MSSQL01MSSQLSERVER1) dove risiede il database master.
In Esplora oggetti, andare su Replica , fare clic con il tasto destro del mouse su Sottoscrizioni locali e, nel menu contestuale, selezionare Nuove sottoscrizioni .

Si apre la Procedura guidata Nuova sottoscrizione .

1. Pubblicazione . Selezionare la pubblicazione per la quale creare una nuova sottoscrizione. Nel nostro esempio, il nome dell’editore è MSSQL01MSSQLSERVER1 e il nome della pubblicazione (creata in precedenza) è AdvWorks_Pub . Fare clic su Avanti in ogni fase della procedura guidata per proseguire.
2. Ubicazione dell’agente di distribuzione . Selezionare il tipo di replica scegliendo tra sottoscrizione push o sottoscrizione pull. Nel nostro esempio, vogliamo che tutti gli agenti vengano eseguiti sul lato del server di origine; pertanto, viene selezionata la prima opzione per creare una sottoscrizione push. Ciò consente di gestire centralmente la replica di MS SQL Server.

   

3. Sottoscrittori . Per impostazione predefinita, il server su cui si esegue la procedura guidata (in questo caso MSSQL01MSSQLSERVER1) viene visualizzato come Sottoscrittore e il database di sottoscrizione non è definito. Aggiungiamo un nuovo sottoscrittore e selezioniamo un database di sottoscrizione situato sul secondo server di database (MSSQL01MSSQLSERVER2). Fare clic su Aggiungi sottoscrittore e, nel menu contestuale, selezionare Aggiungi sottoscrittore SQL Server .
   • Nella finestra a comparsa, inserire le credenziali per la seconda istanza di MSSQL Server (MSSQL01MSSQLSERVER2 nel nostro caso) e fare clic su Connetti .

     

   • Selezionare la casella di controllo del secondo server su cui verrà archiviata la replica del database (MSSQL02MSSQLSERVER2) e, nel Database in abbonamento menu a discesa, selezionare un nuovo database o un database esistente ripristinato da un backup da utilizzare come replica del database.

     Nel nostro esempio, il database AdventureWorks2016r è stato creato sul secondo server ripristinando il database principale (di origine) AdventureWorks2016 da un backup per avviare la replica. La replica viene avviata replicando solo i nuovi dati, senza copiare l’intero database dopo l’avvio del processo di replica. Pertanto, AdventureWorks2016r viene selezionato come database in abbonamento nell’esempio corrente.

     

4. Sicurezza dell’agente di distribuzione . Fare clic sul pulsante con i tre puntini (…) e selezionare l’utente e le altre opzioni di sicurezza per l’agente di distribuzione.

   Nella finestra Sicurezza dell’agente di distribuzione che si apre, impostare l’agente di distribuzione in modo che venga eseguito sull’host MSSQL01 con l’account utente mssql . Immettere la password per l’utente Windows mssql . Selezionare Connettiti al distributore impersonando l’account di processo e selezionare Connettiti all’abbonato impersonando l’account di processo . Fare clic su OK per salvare le impostazioni.

   

   Ora le proprietà dell’abbonamento sono configurate.

   

5. Pianifica la sincronizzazione . Selezionare l’agente che si trova sul distributore per Esegui in modo continuo per l’abbonato corrente.
6. Inizializza abbonamenti . Selezionare la casella di controllo Inizializza e, nel menu a discesa, selezionare Immediatamente per specificare quando inizializzare la sottoscrizione. Se necessario, è anche possibile selezionare l’opzione Ottimizzata per la memoria .

   

7. Azioni della procedura guidata . Selezionare la casella di controllo in alto per creare le sottoscrizioni al termine della procedura guidata.
8. Completa la procedura guidata . È possibile verificare le impostazioni della sottoscrizione e fare clic su Fine per creare la sottoscrizione.

   

9. Attendere fino a quando l’abbonamento non viene creato. Se viene visualizzato lo stato Successo , significa che l’abbonamento è stato creato correttamente.

   

10. Dopo aver configurato la replica in SQL Server, in Esplora oggetti vengono visualizzati tre lavori, che è possibile visualizzare andando su SQL Server Agent > Lavori .

    

Finalizzazione della configurazione della replica

Una volta configurati il distributore, l’editore e l’abbonato, è possibile verificare lo stato della replica di MS SQL Server.

1. Sul primo server (MSSQL01MSSQLSERVER1), avviare il monitor di replica per visualizzare lo stato della replica di MS SQL Server. In SQL Server Management Studio, selezionare l’istanza di MS SQL Server (MSSQLSERVER1), andare su Replica , fare clic con il tasto destro del mouse su Pubblicazioni locali e, nel menu contestuale, selezionare Avvia monitor di replica .

   

2. Nel nostro caso è presente un errore dell’agente di lettura del log . Per visualizzare i dettagli dell’errore, selezionare il database di origine (il Publisher) nel riquadro di sinistra, selezionare la scheda Agenti nel riquadro di destra e fare doppio clic sul nome dell’errore.

   

3. Nella finestra che si apre, è possibile visualizzare la cronologia dell’agente e i messaggi di errore. I messaggi di errore sono:
   • Impossibile eseguire sp_replcmds su MSSQL01MSSQLSERVER1. Origine: MSSQL_REPL. Numero di errore: MSSQL_REPL20011).
   • Impossibile eseguire l’operazione come entità del database perché l’entità “dbo” non esiste, non è possibile impersonare questo tipo di entità oppure non si dispone dell’autorizzazione necessaria. (Origine: MSSQLServer, numero di errore: 15517).

   

   Il secondo messaggio di errore indica che manca un’autorizzazione. Risolviamo questo errore.

4. Crea una nuova query in MS SQL Management Studio ed esegui questa query. Nella finestra principale, fai clic sul pulsante Nuova query .
5. Nella sezione della query SQL della finestra principale, inserisci la seguente query:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Fai clic sul pulsante Esegui .

   

   Comandi completati con successo.

6. Successivamente, vai su MSSQL01MSSQLSERVER1 > Replica > Pubblicazioni locali > [AdventureWorks2016]: AdvWorks_Pub . Fai clic con il tasto destro del mouse sul nome della pubblicazione e, nel menu contestuale, seleziona Visualizza stato agente snapshot . Puoi fare clic su Azione > Aggiorna per aggiornare lo stato e su Reinizializza tutte le sottoscrizioni per applicare uno snapshot a ciascun sottoscrittore.

   Ora tutto è risolto, non vengono visualizzati errori e la replica di MS SQL Server dovrebbe funzionare.

   

Verifica del funzionamento della replica

Vediamo la replica di MS SQL Server in azione. Visualizza il contenuto di una tabella del database AdventureWorks2016 memorizzato sul primo server MS SQL ( MSSQL01MSQLSERVER1 ). Nel nostro esempio, selezioneremo tutto il contenuto della tabella Person.AddressType . A tal fine, eseguire la query:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
Il risultato dell’esecuzione della query è visualizzato nella schermata qui sotto:

Eseguire una query simile sul secondo server per visualizzare tutti i dati della tabella Person.AddressType del database AdventureWorks2016r archiviato su MSSQL02MSSQLSERVER2.
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Se si confrontano le schermate sopra e sotto, i contenuti della tabella Person.AddressType sono identici in entrambi i database (un database di origine sul primo server e il database di destinazione, che è una replica del database sul secondo server).

Eliminiamo una riga dalla tabella PersonAddressType del database AdventureWorks2016 (di origine) sul primo server (MSSQL01MSSQLSERVER1). Esegui la query per eliminare una riga che contenga ‘Billing’ nel nome e per visualizzare il contenuto della tabella al termine dell’operazione:
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;

Come puoi vedere, la prima riga con AddressTypeID 1 e nome ‘Billing’ è stata eliminata dalla tabella Person.AddressType nel database AdventureWorks2016 sul computer MSSQL01 .
La replica transazionale è in esecuzione. Verifichiamo il contenuto della tabella Person.AddressType nel database AdventureWorks2016r sul computer MSSQL02 . Eseguiamo nuovamente una query simile a quella precedente per visualizzare il contenuto della tabella:
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
A seguito della replica, la prima riga è stata eliminata anche dalla tabella Person.AddressType nel database secondario che funge da replica del database ( AdventureWorks2016r ). È possibile vedere i risultati nella schermata qui sotto.

La replica del database in SQL Server è attiva.

Conclusione

Esistono quattro tipi di replica in MS SQL Server: replica snapshot, transazionale, peer-to-peer e di unione. Poiché la replica transazionale è ampiamente utilizzata, in questo post del blog abbiamo configurato proprio questo tipo di replica in MS SQL Server. Per garantire il corretto funzionamento della replica del database, è necessario configurare il distributore, l’editore e l’abbonato. L’abbonato può essere configurato su un server di origine (replica push) o su un server di destinazione (replica pull).
Tuttavia, è consigliabile utilizzare sia la replica che backup dei database MS SQL per aumentare le probabilità di successo di ripristino dei dati dal database.

Prova NAKIVO Backup & Replication

Richiedi una prova gratuita per scoprire tutte le funzionalità di protezione dei dati offerte dalla soluzione. 15 giorni gratuiti. Nessuna limitazione in termini di funzionalità o capacità. Non è obbligatoria alcuna carta di credito.

PROVA GRATUITAMENTE