SQLShack

De hoeveelheid gegevens die tegenwoordig wordt bewaard, beheerd en benaderd, is ongekend. Bedrijven verwachten dat de IT-afdeling gegevens voor onbepaalde tijd online en toegankelijk houdt, wat een enorme druk legt op de databases die nodig zijn om deze gegevens op te slaan en te beheren. Om aan de hedendaagse behoeften te voldoen, moeten we verouderde en inefficiënte legacy processen vervangen door nieuwe, meer agile technieken. SQL Server Replicatie is een van de technieken om aan dergelijke eisen te voldoen.

  • Note: Voor meer informatie over het repliceren van databases, lees Verschillende belanghebbenden, verschillende visies: Why Database Management Requires a Systematic Approach artikel.

In dit artikel krijgt u inzicht in de volledige topografie van SQL Server replicatie, inclusief componenten, internals en de SQL om dit alles aan elkaar te binden. Nadat u dit artikel heeft gelezen, zult u het volgende begrijpen:

  • SQL Server replicatie, in het algemeen
  • Componenten van transactionele SQL Server replicatie, in het bijzonder
  • Hoe distributeureigenschappen te verkrijgen
  • Hoe de uitgever te vinden die dezelfde distributeur gebruikt
  • Wat zijn de databases die voor SQL Server-replicatie worden gebruikt
  • De algemene topologie van een replicatieomgeving
  • Wat zijn de artikelen die zijn gekoppeld aan het type SQL Server-replicatiemodel
  • Hoe publicatie details
  • Hoe verkrijg ik abonnementsdetails
  • SQL Server Replication agents
  • En meer…

Replicatie

SQL Server replicatie is een technologie voor het kopiëren en distribueren van data en database objecten van de ene database naar de andere en vervolgens het synchroniseren tussen databases om de consistentie en integriteit van de data te behouden. In de meeste gevallen is replicatie een proces van het reproduceren van de gegevens op de gewenste doelen. SQL Server replicatie wordt gebruikt voor het kopiëren en synchroniseren van gegevens continu of het kan ook worden gepland om te draaien op vooraf bepaalde intervallen. Er zijn verschillende replicatie technieken die een variëteit aan data synchronisatie benaderingen ondersteunen; one-way; one-to-many; many-to-one; en bi-directioneel, en verschillende datasets met elkaar in sync houden.

Transactionele SQL Server replicatie componenten

Het volgende diagram geeft de componenten van transactionele SQL Server replicatie weer.

Inclusief de SQL Server replicatie …

  • Publisher
  • Publication database
  • Publication

    • Artikelen

  • Distributeur
  • Distribution database
  • Subscriber
  • Subscription database
  • Abonnement
  • Replicatieagenten

SQL Server replicatiediagram

Artikel

Een artikel is de basiseenheid van SQL Server Replicatie. Een artikel kan bestaan uit tabellen, stored procedures en views. Het is mogelijk om het artikel te schalen, horizontaal en verticaal met behulp van een filter optie. We kunnen ook meerdere artikelen maken op hetzelfde object met enkele beperkingen en restricties.

Met behulp van de wizard Nieuwe publicatie kan door het artikel worden genavigeerd. Het stelt ons in staat om de eigenschappen van een artikel te bekijken en biedt opties om eigenschappen voor de artikelen in te stellen. In sommige gevallen kunnen de eigenschappen worden ingesteld tijdens het maken van de publicatie en is het een read-only eigenschap.

Na het maken van een SQL Server replicatie publicatie, bijvoorbeeld, als een bepaalde eigenschap een wijziging vereist, zal het, op zijn beurt, vereisen dat een nieuwe replicatie snapshot wordt gegenereerd. Als de publicatie een of meer abonnementen heeft, vereist de wijziging dat alle abonnementen opnieuw worden geïnitialiseerd. Voor meer informatie, zie het artikel Hoe artikelen toevoegen/verwijderen aan/van bestaande publicatie in SQL Server.

Om een lijst te maken van alle artikelen die zijn gepubliceerd, voert u de volgende 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.

Om de details van artikelen in transactionele of samengevoegde SQL Server-replicatie in een gepubliceerde database te krijgen, voert u de volgende T-SQL uit.

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 of st.is_merge_published = 1 of 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;

Om gedetailleerde informatie over een artikel in de vermelde uitgever te krijgen, voert u de volgende 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

Om details op kolomniveau te krijgen, voert u de volgende T-SQL

1
2
3
USE MES_PROD_AP
GO
sp_helparticlecolumns @publication = N’PROD_HIST_Pub’ , @article = ‘tb_Branch_Plant’

Om een lijst te maken van de kolommen die zijn gepubliceerd in de transactionele replicatie in de publicatiedatabase, voert u de volgende T-SQL

1
SELECT object_name(object_id) , name FROM sys.columns sc WHERE sc.is_replicated = 1;

Publicaties

Een publicatie is een logische verzameling van artikelen uit een database. De entiteit stelt ons in staat om artikel eigenschappen te definiëren en te configureren op een hoger niveau, zodat de eigenschappen worden overgeërfd op alle artikelen in die groep.

1
EXEC sp_helppublication;

Publisher-database

De publisher is een database die een lijst met objecten bevat die worden aangeduid als SQL Server-replicatie-artikelen die bekend staan als publicatiedatabase. De uitgever kan een of meer publicaties hebben. Elke publisher definieert een data propagation mechanisme door het creëren van een aantal interne replicatie stored procedures.

1
2
3
USE Distribution
GO
select * from MSpublications

Publisher

De Publisher is een database-instantie die gegevens beschikbaar maakt voor andere locaties via SQL Server-replicatie. De Publisher kan een of meer publicaties hebben, die elk een logisch gerelateerde set van objecten en gegevens definiëren om te repliceren.

Distributeur

De distributeur is een database die fungeert als een opslagplaats voor replicatiespecifieke gegevens die zijn gekoppeld aan een of meer Publishers. In veel gevallen is de distributeur een enkele database die fungeert als zowel de Publisher als de Distributor. In de context van SQL Server replicatie, is dit algemeen gekend als een “lokale verdeler”. Aan de andere kant, als het is geconfigureerd op een aparte server, dan is het bekend als een “remote distributeur”. Elke Publisher is gekoppeld aan een enkele database die bekend staat als een “distributie database” oftewel de “Distributeur”.

In de distributiedatabase worden SQL Server-statusgegevens over replicatie en metagegevens over de publicatie geïdentificeerd en opgeslagen, en in sommige gevallen fungeert de database als wachtrij voor gegevens die van de Publisher naar de Subscribers worden verplaatst.

Afhankelijk van het replicatie model, kan de Distributeur ook verantwoordelijk zijn voor het informeren van de Abonnees die zich hebben geabonneerd op een publicatie dat een artikel is gewijzigd. Ook handhaaft de distributiedatabase de integriteit van de gegevens.

Distributiedatabases

Elke Distributeur moet ten minste één distributiedatabase hebben. De distributie database bestaat uit artikel details, replicatie meta-data en data. Een distributeur kan meer dan één distributiedatabase hebben; alle publicaties die op één Publisher zijn gedefinieerd, moeten echter dezelfde distributiedatabase gebruiken.

Om uit te vinden of …

  • een server een distributeur is of niet?

    1
    SELECT @@ServerName Servername, case when is_distributor=1 then ‘Yes’ else ‘No’ end status FROM sys.servers WHERE name=’repl_distributor’ AND data_source=@@servername;

  • een distributiedatabase geïnstalleerd of niet?

    1
    SELECT name FROM sys.databases WHERE is_distributor = 1
  • p>maakt een uitgever gebruik van deze distributeur of niet?
    1
    EXEC sp_get_distributor

  • of alleen maar om verschillende eigenschappen van de distributeur- en distributiedatabase op te vragen?

    1
    2
    3
    EXEC sp_helpdistributor;
    EXEC sp_helpdistributiondb;
    EXEC sp_helpdistpublisher;

    • Subscriber

    Een database-instantie die SQL Server-replicatiegegevens van een publicatie verbruikt, wordt een Subscriber genoemd. De abonnee kan gegevens ontvangen van een of meer uitgevers en publicaties. De abonnee kan ook gegevenswijzigingen teruggeven aan de uitgever of de gegevens opnieuw publiceren naar andere abonnees, afhankelijk van het type van het replicatieontwerp en -model.

    1
    EXEC sp_helpsubscriberinfo;

    Abonnementen

    Een abonnement is een verzoek om een exemplaar van een publicatie aan een abonnee te leveren. Het abonnement bepaalt welke publicatiegegevens worden ontvangen, waar en wanneer.

    Er zijn twee soorten abonnementen: push-abonnementen en pull-abonnementen

    • Push-abonnement: Distributeur werkt de gegevens direct bij in de database van de abonnee
    • Pull-abonnement: de abonnee wordt ingepland om regelmatig bij de distributeur te controleren of er nieuwe wijzigingen zijn, en werkt vervolgens zelf de gegevens bij in de database van het abonnement.
    1
    EXEC sp_helpsubscription;

    Replicatiedatabases

    Een doeldatabase van een replicatiemodel wordt een abonnementsdatabase genoemd.

    Replicatieagenten

    SQL Server replicatie gebruikt een voorgedefinieerde set van standalone programma’s en gebeurtenissen, bekend als agenten, om de taken uit te voeren die aan gegevens zijn gekoppeld. Standaard worden SQL Server replicatie agents uitgevoerd als geplande taken onder SQL Server Agent. Replicatieagenten kunnen ook worden uitgevoerd vanaf de opdrachtregel en door toepassingen die gebruik maken van Replication Management Objects (RMO). SQL Server replicatieagenten kunnen worden bewaakt en beheerd met behulp van Replication Monitor en SQL Server Management Studio.

    Replication snapshot Agent

    De Replication snapshot Agent wordt gebruikt met alle soorten SQL Server replicatietechnologie, omdat het de vereiste dataset levert om de initiële datasynchronisatie van de publicatiedatabase met de subscription database uit te voeren. Het bereidt schema en initiële gegevens van gepubliceerde artikelen voor, snapshot bestanden, en registreert informatie over het synchronisatie type in de distributie database.

    Log Reader Agent

    De Log Reader Agent wordt alleen gebruikt bij transactionele replicatie. Het verplaatst replicatie transacties van het online transactielogboek van de publicatie database naar de distributie database.

    Distribution Agent

    De Distribution Agent wordt alleen gebruikt bij Replication snapshot en Transactional SQL Server replication. Deze agent past de initiële replicatie snapshot toe op de subscription database en later worden de data wijzigingen bijgehouden en vastgelegd in de distributie database en toegepast op de subscription database.

    Merge Agent

    De Merge Agent wordt gebruikt bij het merge replicatiemodel. Standaard uploadt de Merge Agent wijzigingen van de abonnee naar de uitgever en downloadt vervolgens wijzigingen van de uitgever naar de abonnee. Elk abonnement heeft zijn eigen Merge Agent die verbinding maakt met zowel de Publisher als de Subscriber en beide bijwerkt. De Merge Agent draait bij de Distributeur voor push abonnementen of bij de Abonnee voor pull abonnementen. De synchronisatie is hier bidirectioneel. De data conflicten worden afgehandeld door een set triggers die het gehele proces ondersteunt

    Samenvatting

    Tot zover hebben we een walk-through gezien van enkele van de belangrijke concepten van SQL Server replicatie. Ook zijn T-SQL scripts getoond om systeemtabellen en replicatie stored procedures te bevragen om de meeste van de veelgestelde vragen over SQL Server replicatie te beantwoorden.

    Ik zal in de komende artikelen meer vertellen over SQL Server replicatie. Als u vindt dat er iets verbeterd kan worden aan dit artikel, laat dan gerust uw commentaar hieronder achter…

    Inhoudsopgave

    SQL Server replicatie: Overzicht van componenten en topografie

    SQL Replicatie: Basisinstellingen en configuratie

    Hoe artikelen toevoegen/verwijderen uit bestaande publicaties in SQL Server

    Hoe een snelle geschatte vergelijking maken van gegevens in twee grote SQL Server-databases om te zien of ze gelijk zijn

    SQL Server-replicatie met transacties: Hoe een abonnement opnieuw te initialiseren met behulp van een SQL Server database back-up

    Hoe een aangepast SQL Server transactiereplicatiemodel op te zetten met een centrale abonnee- en meerdere Publisher-databases

    Hoe aangepaste SQL Server transactiereplicatie op te zetten met een centrale publisher en meerdere abonnee-databases

    Hoe een DDL en DML SQL Server database transactionele replicatieoplossing op te zetten

    Hoe cross-platform transactionele SQL Server replicatie op te zetten met een centrale publisher en meerdere abonnee-databases

    Hoe een DDL en DML SQL Server database transactionele replicatieoplossing op te zetten

    Hoe cross-platform transactionele replicatie op te zetten.platform-overschrijdende SQL Server-replicatie voor databaserapportage op Linux

    SQL Server-datamigraties zonder gegevensverlies en zonder downtime

    Transactionele gegevensreplicatie gebruiken om productieladingen op een staging server na te spelen en te testen

    SQL Server-database replicatie instellen voor een rapportageserver

    SQL Server-replicatie voor transacties: Hoe een abonnement te herinitialiseren met behulp van een “Replication support only” -TBA

    SQL Server Replication Monitoring en het instellen van waarschuwingen met behulp van PowerShell -TBA

    • Auteur
    • Recent Posts
    Prashanth Jayaram
    Ik ben een Database technoloog met 11+ jaar rijke, hands-on ervaring op Database technologieën. Ik ben Microsoft Certified Professional en heb een graad in Master of Computer Application.
    Mijn specialiteit ligt in het ontwerpen & implementeren van High availability oplossingen en cross-platform DB Migratie. De technologieën waar ik momenteel aan werk zijn SQL Server, PowerShell, Oracle en MongoDB.
    Bekijk alle berichten van Prashanth Jayaram

    Prashanth Jayaram
    Laatste berichten van Prashanth Jayaram (zie alle)
    • Een snel overzicht van database audit in SQL – 28 januari, 2021
    • Hoe Azure Data Sync instellen tussen Azure SQL-databases en on-premises SQL Server – 20 januari 2021
    • Hoe Azure SQL-database import-/exportbewerkingen uitvoeren met PowerShell – 14 januari 2021