Come configurare la replica di MS SQL Server

Microsoft SQL Server è un software di gestione di database che può essere installato sui sistemi operativi Windows Server. I database sono utilizzati dalle aziende di tutti i settori e molte soluzioni software utilizzano database sia centralizzati che distribuiti. La disponibilità dei database e la coerenza dei dati sono fondamentali per le aziende, rendendo necessari 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 for Windows Backup

Fast backup of Windows servers and workstations to onsite, offiste and cloud. Recovery of full machines and objects in minutes for low RTOs and maximum uptime.

DISCOVER SOLUTION

Che cos’è la replica di SQL Server?

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

La terminologia utilizzata per la replica di MS SQL Server

Prima di approfondire la configurazione e l’impostazione della replica di MS SQL Server, esaminiamo brevemente i termini principali e i modelli di replica.Articoli sono le unità di base da replicare, come 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 è una raccolta logica di articoli. Si tratta dell’insieme finale di entità del database designate per la replica.Un filtro è un insieme di condizioni per 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.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.Metadati sono i dati utilizzati per descrivere le entità del database. Esiste un’ampia gamma di funzioni di metadati integrate che consentono di restituire informazioni sull’istanza MS SQL Server, sulle istanze del database e sulle entità del database.

Ruoli nella replica del database SQL

Nella replica del database MS SQL esistono tre ruoli principali: distributore, editore e sottoscrittore.

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

  Un database distributore può essere considerato contemporaneamente come editore e distributore. Nel modello distributore locale, una singola istanza MS SQL Server esegue sia l’editore che il distributore. È possibile utilizzare un modello distributore remoto quando si desidera che gli abbonati siano configurati per utilizzare una singola istanza MS SQL Server per ottenere 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 sottoscrittore può ricevere dati da più di un editore e da più pubblicazioni. Il modello a sottoscrittore singolo viene utilizzato quando è presente un solo sottoscrittore. Il modello a sottoscrittori multipli viene utilizzato quando più sottoscrittori sono collegati a una singola pubblicazione.

  In abbonamento è una richiesta di copia di una pubblicazione che deve essere consegnata all’abbonato. L’abbonamento in 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 in abbonamento:

  • Abbonamento push: I dati modificati vengono trasmessi forzatamente dal distributore al database dell’abbonato. Non è necessaria alcuna richiesta da parte dell’abbonato.
  • Annulla abbonamento: I dati modificati su Publisher vengono richiesti da un Subscriber. L’agente viene eseguito sul lato del Subscriber.

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

  

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

Tipi di replica di MS SQL Server

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

Replica snapshot

Replica snapshot viene utilizzato per replicare i dati esattamente come appaiono al momento della creazione dell’istantanea del database. Questo tipo di replica è adatto per dati che non cambiano frequentemente, quando avere una replica del database più vecchia del database master non è un problema critico o quando viene apportato un grande volume di modifiche in un breve periodo 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

Replica transazionale è una replica automatizzata periodica in cui i dati vengono distribuiti da un database master a una replica del database in tempo reale (o quasi reale). La replica transazionale è più complessa della replica snapshot. Tutte le transazioni effettuate e lo stato finale del database vengono replicati, il che rende possibile il monitoraggio dell’intera cronologia delle transazioni sulla replica.All’inizio del processo di replica transazionale, viene applicato uno snapshot al sottoscrittore, quindi i dati vengono trasferiti continuamente dal database master a una replica del database man mano che vengono apportate modifiche a questi dati. La replica transazionale è ampiamente utilizzata come replica unidirezionale.Casi d’uso della replica transazionale:

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

Replica peer-to-peer

Replica peer-to-peer viene utilizzato per replicare i dati del database su più sottoscrittori contemporaneamente. Questo tipo di replica MS SQL Server può essere utilizzato quando i server di database sono distribuiti in tutto il mondo. Le modifiche possono essere apportate su qualsiasi 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 può essere utilizzata tra server di database distribuiti in tutto il mondo.

Replica di unione

Replica di unione è un tipo di replica bidirezionale che viene solitamente utilizzata in ambienti server-client per sincronizzare i dati tra server di database quando questi non possono essere collegati in modo continuo. 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 dal publisher al subscriber e viceversa.Questo tipo di replica del 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ù archivi peer che lavorano con un magazzino condiviso. Ogni archivio è autorizzato a modificare le informazioni nel database del magazzino e, allo stesso tempo, tutti gli archivi devono avere lo 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 devono essere disponibili contemporaneamente per il database principale (o centrale) e 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 MS SQL devono risolversi reciprocamente tramite un nome host.Prima di configurare la replica MS SQL Server, è necessario installare il seguente software per MS SQL Server:

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

NOTA: In questo post viene utilizzato MS SQL Server 2016 per la configurazione. È possibile utilizzare lo stesso principio per configurare la replica in SQL Server delle versioni più recenti.Tenere presente che se si installa MS SQL Server 2016 sul primo computer in cui si trova il database di origine, è necessario installare MS SQL Server 2016 anche sul secondo computer affinché il database funzioni correttamente.Ad esempio, se si desidera configurare la replica transazionale MS SQL, è possibile utilizzare il secondo server di database (in cui è configurato il sottoscrittore) di una versione compresa tra le due versioni del server di database di origine su cui è configurato l’editore. Se la versione dell’editore su MS SQL Server è 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 Distributor non può essere inferiore alla versione dell’Publisher. La replica non funzionerà se si installa MS SQL Server 2008 sul secondo computer, ad esempio.

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 la chiave primaria.
• Si consiglia di non pianificare la 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 residente sul sottoscrittore. Quando è in corso una transazione che modifica i dati e tali dati sono stati modificati o eliminati, la replica può interrompersi fino alla risoluzione del problema.

Configurazione dell’ambiente

Quando si configura la replica MS SQL per la prima volta, si consiglia di farlo prima in un ambiente di prova. Ad esempio, configuriamo la replica in server SQL in esecuzione su VM. In questo tutorial vengono utilizzati due host con Windows Server 2016 e MS SQL Server 2016 per spiegare la replica MS SQL Server.Diamo un’occhiata alla configurazione dell’ambiente di prova utilizzato per scrivere questo post sul blog per comprendere meglio la configurazione della replica MS SQL Server.Host 1

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

Host 2

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

Entrambe le macchine hanno il disco C: e il disco D: nella loro configurazione disco.È 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 post del blog non approfondisce come installare MS SQL Server perché questo tutorial si concentra sulla configurazione della replica di MS SQL Server. In questo esempio, entrambi i server MS SQL sono installati senza PolyBase.Verificare di aver installato le funzioni obbligatorie per la replica di MS SQL Server una volta completata l’installazione 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 principale dei dati: D:MSSQL_Server
• Directory del database di sistema: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directory del database utente: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Directory del log del database utente: 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 account Windows per gli agenti di replica MS SQL Server.

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

Le due macchine Windows Server utilizzate in questo esempio non sono in Active Directory. Se si utilizza Active Directory, è possibile creare il mssql utente sul controller di dominio.

Connetti a MS SQL Server

1. Esegui SQL Server Management Studio.
2. Accedi (vedi screenshot) come sa utilizzando l’autenticazione SQL Server.
   • MSSQL01MSSQLSERVER1 è il nome host e il nome dell’istanza MS SQL sul primo server.
   • MSSQL02MSSQLSERVER2 è il nome host e il nome dell’istanza MS SQL sul secondo server.

   

Allo stesso modo, è possibile connettersi al secondo server (MSSQL02) alla seconda istanza di MS SQL Server (MSSQLSERVER2). È anche possibile connettersi alla seconda istanza di MS SQL Server (MSSQLSERVER2) dal primo MS SQL Server (MSSQL01) immettendo 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.A tal fine, in Esplora oggetti, fare clic su Connetti > Motore di databaseIn 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 MS SQL Server, vedrai che l’Agent 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 Agent in modo che si avvii automaticamente:

1. Stampa Win+R, esegui cmd, ed eseguire il services.msc comando.
2. Aprire le impostazioni del servizio SQL Server Agent e impostare Startup Digita Automatico.

   

Configurazione degli utenti per MS SQL Server

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

1. Vai a Esplora oggetti e aperto Sicurezza > Accessi.
2. Clic destro Accessi e seleziona Nuovo login. Seleziona Autenticazione Windows.
3. Inserisci il nome utente mssql nel Generale sezione.
4. Clicca Cerca, quindi premi Controlla i nomi per confermare, e clicca OK due volte per salvare le impostazioni.

   

5. Ora il MSSQL01mssql L’utente Windows viene aggiunto all’elenco degli utenti che possono accedere al database (allo stesso modo, aggiungere il mssql utente a accessi sulla seconda macchina MSSQL02 in SQL Server Management Studio).
6. Aggiungi il mssql utente al amministratori di sistema ruoli server nel Sicurezza configurazione del database in SQL Server Management Studio.
7. Vai a MSSQL01MSSQLSERVER1 > Ruoli server, clicca con il tasto destro del mouse amministratore di sistema, e apri Proprietà.
8. Nel Membri pagina, clicca Aggiungi, inserisci il nome del tuo utente mssql, e clicca Controlla i nomi.
9. Seleziona la casella di controllo del nome utente MSSQL01mssql e clicca OK.

   

10. Esegui la stessa configurazione sul secondo computer (in questo caso MSSQL02).
11. Riavvia entrambe le macchine.

    Ora è possibile accedere utilizzando l’autenticazione 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 dalla prima macchina alla seconda. Il AdventureWorks2016 Il database viene utilizzato come database di esempio in questo esempio.

1. Copia il AdventureWorks2016.bak file di backup del database nella directory di backup MSSQL. Nel nostro caso, questa directory sul primo server è D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. Importa un database di esempio. Sul primo computer in SQL Server Management Studio, vai a MSSQL01MSSQLSERVER1, clicca con il tasto destro del mouse Banche dati, e seleziona Ripristina database nel menu contestuale.

   

3. Nel Ripristina database finestra, selezionare i parametri necessari:
   • Origine: Dispositivo.
   • Clicca sul tre puntini per sfogliare il file di backup del database.
     • Nel Seleziona i dispositivi di backup finestra, selezionare il tipo di supporto di backup: file.
     • Clicca Aggiungi.
   • Seleziona il necessario .bak file – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Colpisci OK, quindi premi OK ancora una volta.
4. Il AdventureWorks2016 Il database è stato ripristinato con successo.

   

È possibile importare il database da un backup sul secondo computer, dove verrà eseguita la replica del database. Questo approccio consente di ridurre il traffico di rete perché la replica inizierà copiando le modifiche apportate dopo la creazione del backup, senza copiare tutti i dati del database in un database vuoto.Ripristinare il database da un backup sul secondo server e rinominare il database in AdventureWorks2016r, dove “r” significa “replica”.Infine, abbiamo:

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

1. Sul MSSQL01 macchina, vai a MSSQL01MSSQLSERVER1 > Sicurezza > Accessi, seleziona MSSQL01mssql. Clicca con il tasto destro (o doppio clic) mssql utente e seleziona Proprietà.
2. In Ruoli server, seleziona la casella di controllo accanto a dbcreator ruolo.

   

3. Sul Mappatura utenti pagina, seleziona gli utenti associati a questo login e spunta la casella AdventureWorks2016 casella di controllo database (selezionare AdventureWorks2016r sul secondo server di conseguenza).
4. Nel appartenenza al ruolo del database sezione, seleziona il db_owner casella di controllo.

   

5. Clicca OK per salvare le impostazioni.

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

Configurazione della replica del database

La configurazione della replica in modalità grafica è il metodo più conveniente. La configurazione avanti viene eseguita in SQL Server Management Studio. In questo esempio viene illustrata la replica del database transazionale, poiché è uno dei tipi di replica MS SQL Server più utilizzati.La vista sul server database principale (MSSQL01MSSQLSERVER1) e la vista sul secondo server (MSSQL02MSSQLSERVER2) in SQL Server Management Studio sono mostrate nella schermata sottostante.

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 pulsante destro del mouse Replica e, nel menu contestuale, seleziona Configura la distribuzione.Il Configura procedura guidata di distribuzione si apre.

1. Distributore. Selezionare l’istanza di database corrente in esecuzione sul server principale (MSSQL01MSSQLSERVER1) affinché funga da distributore in questo esempio. Fare clic su Avanti ogni volta per procedere al passaggio successivo della procedura guidata.
2. Avvio di SQL Server Agent. Se non avete configurato MS SQL Server Agent per l’avvio automatico, come spiegato sopra, verrà visualizzato il seguente messaggio. Selezionate Sì, configurare il servizio SQL Server Agent in modo che si avvii automaticamente..

   

3. Cartella Snapshot. È possibile lasciare il percorso predefinito. Per inizializzare la replica è necessaria un’istantanea. Assicurarsi che vi sia spazio libero sufficiente sul disco alla sua ubicazione. Lo spazio libero deve corrispondere almeno alla dimensione del database replicato.
4. Database di distribuzione. Inserisci il nome del database di distribuzione. Puoi lasciare il nome predefinito (distribuzione) e cartelle per il file di database di distribuzione e il file di log.

   

5. Editori. Definire gli editori di replica MS SQL Server che possono accedere al distributore. Selezionare la casella di controllo accanto al nome del database di distribuzione nell’istanza MS SQL Server primaria (che ospita un database di origine che verrà replicato). In questo esempio, si tratta dell’istanza MSSQL01MSSQLSERVER1 e il nome del database di distribuzione è distribuzione.
6. Azioni del mago. Seleziona Configurare la 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. Controlla il riepilogo della configurazione della distribuzione e clicca su Termina per creare il Distributore.

   

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

   

Se si verifica un errore durante la configurazione di SQL Server Agent per l’avvio automatico, accedere alla configurazione dei servizi e controllare la modalità di avvio di SQL Server Agent (vedere come configurare l’avvio di Agent sopra in questo post del blog).È anche possibile aprire le proprietà di SQL Server Agent in SQL Server Management Studio e controllare lo stato del servizio e le opzioni di riavvio. Fare clic con il pulsante destro del mouse Agente SQL Server alla fine dell’elenco in Esplora oggetti e colpito 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, clicca con il tasto destro del mouse Pubblicazioni locali e, nel menu contestuale, seleziona Nuova pubblicazione.Il Procedura guidata per nuove pubblicazioni si apre.

1. Database delle pubblicazioni. Selezionare il database che si desidera replicare (AdventureWorks2016 in questo caso). Premi Avanti ad ogni passaggio della procedura guidata per procedere.

   

2. Tipo di pubblicazione. Per questo passaggio, è possibile selezionare i tipi di replica 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 selezionare le proprietà dell’articolo, se necessario. In questo esempio, sono state selezionate alcune tabelle.

   

4. Filtra righe della tabella. In questo esempio non vengono aggiunti filtri (questa è la configurazione predefinita dei filtri). È possibile aggiungere filtri se necessario.
5. Agente Snapshot. Specificare quando eseguire Snapshot Agent. Configuriamo l’agente in modo che venga eseguito immediatamente. Selezionare Crea immediatamente un snapshot e mantienilo disponibile per inizializzare gli abbonamenti..

   

6. Sicurezza degli agenti. Seleziona Utilizza le impostazioni di sicurezza dello Snapshot Agent. Clicca sul Impostazioni di sicurezza per selezionare l’account con cui verrà eseguito l’agente.

   Nel Sicurezza dell’agente Snapshot finestra che si apre, inserisci le credenziali del mssql Utente Windows 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 visualizzare tale utente nella Agente Snapshot e Agente di lettura dei log sezioni.

   

7. Azioni del mago. Selezionare la casella di controllo superiore per creare la pubblicazione durante la fase finale della procedura guidata.
8. Completa la procedura guidata. Controlla la configurazione della tua pubblicazione e clicca su Termina per creare una nuova pubblicazione.

   

Nel Creazione di una pubblicazione finestra, è possibile effettuare il monitoraggio dello stato di avanzamento della creazione di una nuova pubblicazione. Attendere qualche istante e, se tutto è stato eseguito correttamente, dovrebbe apparire lo stato di completamento.La pubblicazione è stata creata ed è ora possibile visualizzarla in Esplora oggetti andando su Replica > Pubblicazioni locali.

Configurazione dell’abbonato

Come ricorderete, la replica MS SQL Server può essere di tipo pull o push. Se configurate la replica push, dovete configurare il sottoscrittore in modo che esegua gli agenti sul server database principale (in questo caso MSSQL01). Se configurate la replica pull, il sottoscrittore deve essere configurato in modo da 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) in cui risiede il database master.In Esplora oggetti, andate su Replica, clicca con il tasto destro del mouse Abbonamenti locali e, nel menu contestuale, seleziona Nuovi abbonamenti in abbonamento.Il Procedura guidata per la nuova sottoscrizione in abbonamento si apre.

1. Pubblicazione. Selezionare la pubblicazione per la quale creare un nuovo abbonamento. Nel nostro esempio, il nome dell’editore è MSSQL01MSSQLSERVER1 e il nome della pubblicazione (creata in precedenza) è AdvWorks_Pub. Clicca Avanti ad ogni passaggio della procedura guidata per continuare.
2. Ubicazione dell’agente di distribuzione. Selezionare il tipo di replica scegliendo tra abbonamento push o abbonamento pull. Nel nostro esempio, vogliamo che tutti gli agenti vengano eseguiti sul lato server di origine, quindi viene selezionata la prima opzione per creare un abbonamento push. Ciò consente di gestire la replica di MS SQL Server in modo centralizzato.

   

3. Abbonati. 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 abbonato e, nel menu contestuale, seleziona Aggiungi sottoscrittore SQL Server.
   • Nella finestra popup, inserisci le credenziali per la seconda istanza di MSSQL Server (MSSQL01MSSQLSERVER2 nel nostro caso) e fai clic su Connetti.

     

   • Seleziona la casella di controllo del secondo server su cui verrà archiviata la replica del database (MSSQL02MSSQLSERVER2) e, nel campo Database degli abbonamenti menu a tendina, selezionare un nuovo database o un database esistente ripristinato da un backup da utilizzare come replica del database.

     Nel nostro esempio, il AdventureWorks2016r è stato creato sul secondo server ripristinando l’origine (fonte) AdventureWorks2016 database 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 è selezionato come database in abbonamento nell’esempio corrente.

     

4. Sicurezza degli agenti di distribuzione. Clicca sul pulsante con i tre puntini (…), quindi selezionare l’utente e le altre opzioni di sicurezza per l’agente di distribuzione.

   Nel Sicurezza degli agenti di distribuzione finestra che si apre, impostare l’agente di distribuzione in modo che venga eseguito su MSSQL01 ospitare sotto il mssql account utente. Inserisci la password per il mssql Utente Windows. Selezionare Connetti al distributore impersonando l’account di processo e seleziona Connetti all’abbonato impersonando l’account di processo. Colpisci OK per salvare le impostazioni.

   

   Ora le proprietà dell’abbonamento sono impostate.

   

5. Programma di sincronizzazione che pianifica. Selezionare l’agente che si trova sul distributore per Esegui continuamente per l’attuale abbonato.
6. Inizializza abbonamenti. Selezionare il Inizializza casella di controllo e nel menu a tendina, seleziona Immediatamente per quando inizializzare l’abbonamento. È anche possibile selezionare l’opzione Memoria ottimizzata opzione, se necessario.

   

7. Azioni del mago. Selezionare la casella di controllo superiore per creare gli abbonamenti alla fine della procedura guidata.
8. Completa la procedura guidata. Puoi controllare le impostazioni dell’abbonamento e cliccare su Termina per creare l’abbonamento in abbonamento.

   

9. Attendi fino alla creazione dell’abbonamento. Se vedi il Riuscito stato, 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 Agente SQL Server > 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), avvia il monitor di replica per visualizzare lo stato della replica di MS SQL Server. In SQL Server Management Studio, seleziona l’istanza di MS SQL Server (MSSQLSERVER1), vai a Replica, clicca con il tasto destro del mouse Pubblicazioni locali e, nel menu contestuale, seleziona Avvia Monitoraggio replica.

   

2. Esiste Agente di lettura dei log errore nel nostro caso. Per visualizzare i dettagli dell’errore, selezionare il database di origine (l’editore) nel riquadro sinistro, selezionare il Agenti scheda nel riquadro destro 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:
   • Il processo non è riuscito a eseguire sp_replcmds su MSSQL01MSSQLSERVER1. Origine: MSSQl_REPL. Numero errore: MSSQL_REPL20011).
   • Impossibile eseguire come principale del database perché il principale “dbo” non esiste, questo tipo di principale non può essere impersonato o non si dispone dell’autorizzazione. (Origine: MSSQLServer, numero di errore: 15517).

   

   Il secondo messaggio di errore suggerisce che manca qualche tipo di autorizzazione. Risolviamo questo errore.

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

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Clicca sul Esegui pulsante.

   

   Comando/i completato/i con successo.

6. Avanti, vai su MSSQL01MSSQLSERVER1 > Replica > Pubblicazioni locali > [AdventureWorks2016]: AdvWorks_Pub. Fare clic con il pulsante destro del mouse sul nome della pubblicazione e, nel menu contestuale, selezionare Visualizza lo stato dell’agente snapshot. You can click Azione > Aggiorna to refresh the status and Reinitialize All Subscriptions to apply a snapshot to each Subscriber.

   Now everything is resolved, no errors are displayed, and MS SQL Server replication should work.

   

Checking How Replication Works

Let’s see MS SQL Server replication in action. View the content of a table of the AdventureWorks2016 database stored on the first MS SQL server (MSSQL01MSQLSERVER1). In our example, we are going to select all data from the Person.AddressType table. In order to do this, execute the query:USE AdventureWorks2016;GOSELECT *FROM Person.AddressType;The result of executing the query is displayed in the screenshot below:Execute a similar query on the second server to display all data of the Person.AddressType of the AdventureWorks2016r database stored on MSSQL02MSSQLSERVER2.USE AdventureWorks2016r;GOSELECT *FROM Person.AddressType;If you compare the screenshots above and below, the contents of the Person.AddressType are identical on both databases (a source database on the first server and the target database that is a database replica on the second server).Let’s delete one row in the PersonAddressType table from the AdventureWorks2016 database (source) on the first server (MSSQL01MSSQLSERVER1). Run the query to delete a row that contains ‘Billing’ in the name and to display the contents of the table after that:DELETE FROM Person.AddressType WHERE Name='Billing';SELECT * FROM Person.AddressType;As you can see, the first row with the AddressTypeID 1 and name ‘Billing’ was deleted from the Person.AddressType table in the AdventureWorks2016 database on the MSSQL01 machine.Transactional replication is running. Let’s check the contents of the Person.AddressType table in the AdventureWorks2016r database on the MSSQL02 machine. Execute a similar query as above once again to see the contents of the table:USE AdventureWorks2016r;GOSELECT *FROM Person.AddressType;As a result of replication, the first line was also deleted from the Person.AddressType table in the secondary database that acts as the database replica (AdventureWorks2016r). You can see the results in the screenshot below.Database replication in SQL Server is working properly.

Conclusion

There are four types of MS SQL Server replication — snapshot, transactional, peer-to-peer and merge replication. As transactional replication is widely used, we have configured this MS SQL Server replication type in this blog post. The Distributor, the Publisher, and the Subscriber must be configured to make database replication work. The Subscriber can be configured on a source server (push replication) and target server (pull replication).However, you should consider using both replication and backup of MS SQL databases to increase the chances of successful database data recovery.

Try NAKIVO Backup & Replication

Get a free trial to explore all the solution’s data protection capabilities. 15 days for free. Zero feature or capacity limitations. No credit card required.

Try for Free