Il volume di dati conservati, gestiti e accessibili oggi è senza precedenti. Le aziende si aspettano che il dipartimento IT mantenga i dati online e accessibili a tempo indeterminato, mettendo una forte pressione sui database necessari per memorizzarli e gestirli. Per soddisfare le esigenze di oggi, è necessario sostituire i processi legacy obsoleti e inefficienti con tecniche nuove e più agili. La replica di SQL Server è una delle tecniche per soddisfare tali esigenze.
-
Nota: Per saperne di più sulla replica dei database, leggere Different Stakeholders, Different Views: Why Database Management Requires a Systematic Approach article.
In questo articolo, vi farà comprendere l’intera topografia della replica di SQL Server, inclusi i componenti, gli interni e l’SQL per legare il tutto. Dopo aver completato la lettura di questo articolo, capirete:
- Replica di SQL Server, in generale
- Componenti della replica transazionale di SQL Server, in particolare
- Come ottenere le proprietà del distributore
- Come trovare l’editore che usa lo stesso distributore
- Quali sono i database usati per la replica di SQL Server
- La topologia generale di un ambiente di replica
- Quali sono gli articoli che sono mappati al tipo di modello di replica di SQL Server
- Come ottenere la pubblicazione dettagli della pubblicazione
- Come ottenere i dettagli dell’abbonamento
- Gli agenti di replica di SQL Server
- E altro ancora…
Replicazione
La replica di SQL Server è una tecnologia per copiare e distribuire dati e oggetti del database da un database all’altro e poi sincronizzare tra i database per mantenere la coerenza e l’integrità dei dati. Nella maggior parte dei casi, la replicazione è un processo di riproduzione dei dati sui target desiderati. La replica di SQL Server è usata per copiare e sincronizzare i dati continuamente o può anche essere programmata per essere eseguita a intervalli predeterminati. Ci sono diverse tecniche di replicazione che supportano una varietà di approcci di sincronizzazione dei dati; one-way; one-to-many; many-to-one; e bi-direzionale, e mantengono diversi set di dati sincronizzati tra loro.
Componenti della replica transazionale di SQL Server
Il seguente diagramma mostra i componenti della replica transazionale di SQL Server.
Compresa la replica di SQL Server …
- Editore
- Database della pubblicazione
- Pubblicazione
- Articoli
- Distributore
- Database della distribuzione
- Sottoscrittore
- Database della sottoscrizione
- Abbonamento
- Agenti di replica
Diagramma di replica di SQL Server
Articolo
Un articolo è l’unità base della replica di SQL Server. Un articolo può consistere di tabelle, stored procedure e viste. È possibile scalare l’articolo, orizzontalmente e verticalmente usando un’opzione di filtro. Possiamo anche creare più articoli sullo stesso oggetto con alcune restrizioni e limitazioni.
Utilizzando il wizard New Publication, l’articolo può essere navigato. Ci permette di visualizzare le proprietà di un articolo e di fornire opzioni per impostare le proprietà degli articoli. In alcuni casi, le proprietà possono essere impostate durante la creazione della pubblicazione ed è una proprietà di sola lettura.
Dopo la creazione di una pubblicazione di replica di SQL Server, per esempio, se qualche proprietà richiede una modifica, richiederà, a sua volta, la generazione di un nuovo snapshot di replica. Se la pubblicazione ha una o più sottoscrizioni, allora la modifica richiede la reinizializzazione di tutte le sottoscrizioni. Per maggiori informazioni, vedi Come aggiungere/togliere articoli a/da una pubblicazione esistente in SQL Server articolo.
Per elencare tutti gli articoli pubblicati, esegui il seguente T-SQL
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SELECT
Pub.
,Art.
,Art.
,Art.
,Art.
FROM
.. Art
INNER JOIN .. Pub
ON Art. = Pub.
ORDER BY
Pub., Art.
|
Per ottenere i dettagli degli articoli nella replica transazionale o merge SQL Server in un database pubblicato, eseguire il seguente T-SQL.
|
1
2
3
4
5
6
7
8
|
SELECT st.name , st.schema_id, st.is_published , st.is_merge_published, is_schema_published
FROM sys.tables st WHERE st.is_published = 1 or st.is_merge_published = 1 or st.is_schema_published = 1
UNION
SELECT sp.name, sp.schema_id, 0, 0, sp.is_schema_published
FROM sys.procedures sp WHERE sp.is_schema_published = 1
UNION
SELECT sv.name, sv.schema_id, 0, 0, sv.is_schema_published
FROM sys.views sv WHERE sv.is_schema_published = 1;
|
Per ottenere informazioni dettagliate su un articolo nell’editore elencato, eseguire il seguente T-SQL
|
1
2
3
4
5
6
7
|
DECLARE @publication AS sysname;
SET @publication = N’PROD_HIST_Pub’;
USE MES_PROD_AP
EXEC sp_helparticle
@publication = @publication;
GO
|
Per ottenere dettagli a livello di colonna, eseguire il seguente T-SQL
|
1
2
3
|
USE MES_PROD_AP
GO
sp_helparticlecolumns @publication = N’PROD_HIST_Pub’ , @article = ‘tb_Branch_Plant’
|
Per elencare le colonne che sono pubblicate nella replica transazionale nel database di pubblicazione, eseguire il seguente T-SQL
|
1
|
SELECT object_name(object_id) , nome da sys.columns sc WHERE sc.is_replicated = 1;
|
Pubblicazioni
Una pubblicazione è una collezione logica di articoli di un database. L’entità ci permette di definire e configurare le proprietà degli articoli al livello superiore in modo che le proprietà siano ereditate a tutti gli articoli di quel gruppo.
|
1
|
EXEC sp_helppublication;
|
Database publisher
Il publisher è un database che contiene una lista di oggetti che sono designati come articoli di replica di SQL Server sono conosciuti come database di pubblicazione. Il publisher può avere una o più pubblicazioni. Ogni editore definisce un meccanismo di propagazione dei dati creando diverse stored procedure interne di replica.
|
1
2
3
|
USE Distribution
GO
selezionare * from MSpublications
|
Publisher
Il Publisher è un’istanza di database che rende i dati disponibili ad altre posizioni attraverso la replica di SQL Server. Il Publisher può avere una o più pubblicazioni, ognuna delle quali definisce un insieme logicamente correlato di oggetti e dati da replicare.
Distributore
Il distributore è un database che funge da magazzino per la replica di dati specifici associati a uno o più Publisher. In molti casi, il distributore è un singolo database che agisce sia come Publisher che come Distributore. Nel contesto della replica di SQL Server, questo è comunemente noto come “distributore locale”. D’altra parte, se è configurato su un server separato, allora è noto come “distributore remoto”. Ogni Publisher è associato a un singolo database noto come “database di distribuzione” o “distributore”.
Il database di distribuzione identifica e memorizza i dati di stato della replica di SQL Server, i metadati sulla pubblicazione e, in alcuni casi, funge da coda per i dati che si spostano dal Publisher ai Subscribers.
A seconda del modello di replica, il distributore potrebbe anche essere responsabile di notificare agli abbonati che hanno sottoscritto una pubblicazione che un articolo è cambiato. Inoltre, il database di distribuzione mantiene l’integrità dei dati.
Database di distribuzione
Ogni distributore deve avere almeno un database di distribuzione. Il database di distribuzione consiste di dettagli dell’articolo, meta-dati di replica e dati. Un distributore può avere più di un database di distribuzione; tuttavia, tutte le pubblicazioni definite su un singolo editore devono usare lo stesso database di distribuzione.
Per scoprire se …
-
un server è un distributore o no?
1SELECT @@ServerName Servername, case when is_distributor=1 then ‘Sì’ else ‘No’ end status FROM sys.servers WHERE name=’repl_distributor’ AND data_source=@@nome_server;
-
un database di distribuzione installato o no?
1SELECT name FROM sys.databases WHERE is_distributor = 1 -
Un editore sta usando questo distributore o no?
1EXEC sp_get_distributor
-
o semplicemente per interrogare varie proprietà del database dei distributori e della distribuzione?
123EXEC sp_helpdistributor;EXEC sp_helpdistributiondb;EXEC sp_helpdistpublisher;
Subscriber
Un’istanza di database che consuma dati di replica di SQL Server da una pubblicazione è chiamata Subscriber. Il sottoscrittore può ricevere dati da uno o più editori e pubblicazioni. Il sottoscrittore può anche passare le modifiche dei dati all’editore o ripubblicare i dati ad altri sottoscrittori a seconda del tipo di disegno e modello di replica.
|
1
|
EXEC sp_helpsubscriberinfo;
|
Sottoscrizioni
Una sottoscrizione è una richiesta per una copia di una pubblicazione da consegnare ad un abbonato. L’abbonamento definisce quali dati della pubblicazione saranno ricevuti, dove e quando.
Ci sono due tipi di sottoscrizioni: sottoscrizioni push e sottoscrizioni pull
- Sottoscrizione push: Il distributore aggiorna direttamente i dati nel database dell’abbonato
- Abbonamento pull: l’abbonato è programmato per controllare regolarmente presso il distributore se sono disponibili nuove modifiche, e quindi aggiorna i dati nel database dell’abbonamento stesso.
|
1
|
EXEC sp_helpsubscription;
|
Base di sottoscrizione
Un database di destinazione di un modello di replica è chiamato database di sottoscrizione.
Agenti di replica
La replica di SQL Server utilizza un insieme predefinito di programmi ed eventi autonomi, noti come agenti, per eseguire i compiti associati ai dati. Per impostazione predefinita, gli agenti di replica di SQL Server vengono eseguiti come lavori programmati sotto SQL Server Agent. Gli agenti di replica possono anche essere eseguiti dalla linea di comando e da applicazioni che utilizzano Replication Management Objects (RMO). Gli agenti di replica di SQL Server possono essere monitorati e amministrati usando Replication Monitor e SQL Server Management Studio.
Replication snapshot Agent
Il Replication snapshot Agent viene usato con tutti i tipi di tecnologia di replica di SQL Server in quanto fornisce il set di dati necessario per eseguire la sincronizzazione iniziale dei dati del database di pubblicazione con il database di sottoscrizione. Prepara lo schema e i dati iniziali degli articoli pubblicati, i file snapshot e registra le informazioni sul tipo di sincronizzazione nel database di distribuzione.
Log Reader Agent
Il Log Reader Agent è usato solo con la replica transazionale. Sposta le transazioni di replica dal log delle transazioni online del database di pubblicazione al database di distribuzione.
Distribution Agent
Il Distribution Agent è usato solo con Replication snapshot e replica transazionale di SQL Server. Questo agente applica lo snapshot di replica iniziale al database di sottoscrizione e più tardi, i cambiamenti dei dati sono tracciati e registrati nel database di distribuzione e applicati al database di sottoscrizione.
Merge Agent
Il Merge Agent è usato con il modello di replica merge. Per impostazione predefinita, il Merge Agent carica le modifiche dal Subscriber al Publisher e poi scarica le modifiche dal Publisher al Subscriber. Ogni sottoscrizione ha il proprio Merge Agent che si connette sia al Publisher che al Subscriber e li aggiorna entrambi. Il Merge Agent viene eseguito dal Distributore per le sottoscrizioni push o dal Sottoscrittore per le sottoscrizioni pull. Qui, la sincronizzazione è bidirezionale. I conflitti di dati sono gestiti da una serie di trigger che supportano l’intero processo
Sommario
Finora, abbiamo visto una passeggiata attraverso alcuni dei concetti importanti della replica di SQL Server. Inoltre, sono stati mostrati gli script T-SQL per interrogare le tabelle di sistema e le stored procedure di replica per rispondere alla maggior parte delle domande più comuni sulla replica di SQL Server.
Discuterò di più sulla replica di SQL Server nei prossimi articoli. Se pensi che qualcosa possa essere migliorato in questo articolo, sentiti libero di lasciare il tuo commento qui sotto…
Tabella dei contenuti
Replica di SQL Server: Panoramica dei componenti e topografia
Replicazione SQL: Impostazione e configurazione di base
Come aggiungere/togliere articoli da pubblicazioni esistenti in SQL Server
Come fare un rapido confronto stimato dei dati in due grandi database di SQL Server per vedere se sono uguali
Replica transazionale di SQL Server: Come reinizializzare un abbonamento usando un backup del database SQL Server
Come impostare un modello personalizzato di replica transazionale SQL Server con un abbonato centrale e database multipli di editori
Come impostare una replica transazionale SQL Server personalizzata con un editore centrale e database multipli di abbonati
Come impostare una soluzione di replica transazionale DDL e DML del database SQL Server
Come impostare una replica transazionale cross-come impostare la replica transazionale trasversale di SQL Server per il reporting di database su Linux
Migrazioni di database SQL Server con zero perdita di dati e zero downtime
Utilizzo della replica transazionale dei dati per riprodurre e testare i carichi di produzione su un server di staging
Come impostare la replica del database SQL Server per un server di reporting
Replica transazionale di SQL Server: Come reinizializzare una sottoscrizione utilizzando un “supporto di replica solo” -TBA
Monitoraggio della replica di SQL Server e impostazione degli avvisi utilizzando PowerShell -TBA
- Autore
- Post recenti
La mia specialità sta nel progettare & implementare soluzioni di alta disponibilità e migrazioni DB multipiattaforma. Le tecnologie su cui lavoro attualmente sono SQL Server, PowerShell, Oracle e MongoDB.
Vedi tutti i post di Prashanth Jayaram
- Una rapida panoramica del controllo dei database in SQL – 28 gennaio, 2021
- Come impostare Azure Data Sync tra i database Azure SQL e SQL Server on-premises – 20 gennaio 2021
- Come eseguire operazioni di importazione/esportazione di database Azure SQL utilizzando PowerShell – 14 gennaio 2021