So konfigurieren Sie die MS SQL Server-Replikation

Microsoft SQL Server ist eine Datenbankverwaltungssoftware, die auf Windows Server-Betriebssystemen installiert werden kann. Datenbanken werden von Unternehmen aller Branchen genutzt, und viele Softwarelösungen greifen auf Datenbanken zurück – sowohl auf zentralisierte als auch auf verteilte Datenbanken. Die Verfügbarkeit der Datenbanken und die Datenkonsistenz sind für Unternehmen von entscheidender Bedeutung, weshalb das Backup und die Replikation von Datenbanken unerlässlich sind.
Erfahren Sie mehr über die Replikationstypen in SQL Server, wie die Replikation in SQL Server funktioniert und wie Sie die SQL Server-Replikation durchführen.

NAKIVO für Windows-Backups

Schnelles Backup von Windows-Servern und -Arbeitsstationen vor Ort, außerhalb des Unternehmens und in der Cloud. Wiederherstellung kompletter Systeme und Objekte innerhalb weniger Minuten für kurze RTOs und maximale Verfügbarkeit.

ERKENNEN SIE DIE LÖSUNG

Was ist die SQL Server-Replikation?

Die MS SQL Server-Replikation ist der Vorgang, bei dem Daten – einschließlich bestimmter Datenbankobjekte – von einer Datenbank in eine andere kopiert werden und eine synchronisierte Kopie dieser Daten zwischen der Quelle und der Zieldatenbank aufrechterhalten wird. Mit der Replikation in SQL Server können Sie eine identische Kopie Ihrer Primärdatenbank erstellen und Änderungen zwischen den beiden Datenbanken synchronisieren, wobei die Datenkonsistenz und -integrität gewahrt bleibt.

Die für die MS SQL Server-Replikation verwendete Terminologie

Bevor wir uns mit der Konfiguration und Einrichtung der MS SQL Server-Replikation befassen, wollen wir zunächst kurz die wichtigsten Begriffe und die Replikationsmodelle durchgehen.
Artikel sind die zu replizierenden Grundeinheiten, wie beispielsweise Tabellen, Prozeduren, Funktionen und Ansichten. Artikel können mithilfe von Filtern vertikal oder horizontal skaliert werden. Für dasselbe Objekt können mehrere Artikel erstellt werden.
Eine Publikation ist eine logische Sammlung von Artikeln. Dies ist die endgültige Menge an Entitäten aus der Datenbank, die für die Replikation vorgesehen sind.
Ein Filter ist eine Reihe von Bedingungen für einen Artikel. Die MS SQL Server-Replikation ermöglicht es Ihnen, Filter zu verwenden und benutzerdefinierte Entitäten für die Replikation auszuwählen, was wiederum den Datenverkehr, Redundanzen und die in einer Datenbankreplik gespeicherte Datenmenge reduziert. Beispielsweise können Sie mithilfe von Filtern nur die wichtigsten Tabellen und Felder auswählen und anschließend nur diese Daten replizieren.
Agenten sind MS SQL Server-Komponenten, die als Hintergrunddienste für relationale Datenbankmanagementsysteme fungieren können und dazu dienen, die automatisierte Ausführung von Aufträgen wie der Sicherung und Replikation von MS SQL-Datenbanken zu planen. Es gibt fünf Arten von Agenten: Snapshot-Agent, Log-Reader-Agent, Verteilungsagent, Merge-Agent und Queue-Reader-Agent.
Metadaten sind die Daten, die zur Beschreibung von Entitäten der Datenbank verwendet werden. Es gibt eine Vielzahl integrierter Metadatenfunktionen, mit denen Sie Informationen über die MS SQL Server-Instanz, Datenbankinstanzen und Datenbankentitäten abrufen können.

Rollen bei der SQL-Datenbankreplikation

Bei der MS-SQL-Datenbankreplikation gibt es drei Hauptrollen: Distributor, Publisher und Subscriber.

• Ein Distributor ist eine MS-SQL-Datenbankinstanz, die dafür konfiguriert ist, Transaktionen aus Publikationen zu sammeln und an Subscriber zu verteilen. Ein Distributor fungiert als Datenbank zum Speichern replizierter Transaktionen.

  Eine Distributor-Datenbank kann gleichzeitig als Publisher und als Distributor betrachtet werden. Im lokalen Distributor-Modell werden sowohl der Publisher als auch der Distributor auf einer einzigen MS SQL Server-Instanz ausgeführt. Ein Remote-Distributor-Modell kann verwendet werden, wenn Sie möchten, dass Abonnenten so konfiguriert werden, dass sie eine einzige MS SQL Server-Instanz nutzen, um verschiedene Publikationen abzurufen (zentralisierte Verteilung). In diesem Modell werden der Publisher und der Distributor auf unterschiedlichen Servern ausgeführt.

• Ein Publisher ist die Hauptdatenbankkopie, auf der die Publikation konfiguriert ist, wodurch Daten für andere MS SQL-Server verfügbar gemacht werden, die für die Verwendung im Replikationsprozess konfiguriert sind. Der Publisher kann über mehr als eine Publikation verfügen.
• Ein Abonnent ist eine Datenbank, die die replizierten Daten aus einer Publikation empfängt. Ein Abonnent kann Daten von mehr als einem Publisher und mehr als einer Publikation empfangen. Ein Einzelabonnenten-Modell wird verwendet, wenn es nur einen Abonnenten gibt. Ein Modell mit mehreren Abonnenten wird verwendet, wenn mehrere Abonnenten mit einer einzigen Publikation verbunden sind.

  Abonnement ist eine Anforderung einer Kopie einer Publikation, die an den Abonnenten geliefert werden muss. Das Abonnement dient dazu, die zu empfangenden Publikationsdaten sowie den Ort und den Zeitpunkt des Empfangs dieser Daten festzulegen. Es gibt zwei Arten von Abonnements:

  • Push-Abonnement : Geänderte Daten werden zwangsweise von einem Verteiler an eine Abonnentendatenbank übertragen. Eine Anfrage seitens des Abonnenten ist nicht erforderlich.
  • Pull-Abonnement : Geänderte Daten, die auf dem Herausgeber vorgenommen wurden, werden vom Abonnenten angefordert. Der Agent läuft auf der Seite des Abonnenten.

  Eine Abonnementdatenbank ist eine Zieldatenbank im MS-SQL-Replikationsmodell.

  

Im Modell „Mehrere Herausgeber – mehrere Abonnenten“ kann der Herausgeber auf einem der MS-SQL-Server als Abonnent fungieren. Achten Sie darauf, mögliche Aktualisierungskonflikte bei der Verwendung dieses MS-SQL-Server-Replikationsmodells zu vermeiden.

MS-SQL-Server-Replikationstypen

Die MS-SQL-Server-Replikation ist eine Technologie zum Kopieren und Synchronisieren von Daten zwischen Datenbanken – entweder kontinuierlich oder regelmäßig im Rahmen des Planens. Was die Replikationsrichtung betrifft, kann die MS-SQL-Server-Replikation einseitig, eins-zu-viele, bidirektional und viele-zu-eins erfolgen. Es gibt vier MS-SQL-Server-Replikationstypen: Schnappschuss-Replikation, transaktionale Replikation, Peer-to-Peer-Replikation und Merge-Replikation.

Snapshot-Replikation

Snapshot-Replikation wird verwendet, um Daten genau so zu replizieren, wie sie zum Zeitpunkt der Erstellung des Datenbank-Schnappschusses vorliegen. Diese Art der Replikation eignet sich für Daten, die sich nicht häufig ändern, wenn es kein kritisches Problem darstellt, dass die Datenbankreplik älter ist als die Master-Datenbank, oder wenn innerhalb kurzer Zeit eine große Anzahl von Änderungen vorgenommen wird. Bei der Snapshot-Replikation wird keine Verfolgung der Änderungen verwendet.
Die Snapshot-Replikation kann beispielsweise eingesetzt werden, wenn Wechselkurse oder Preislisten einmal täglich aktualisiert und vom Hauptserver an Server in Zweigstellen verteilt werden müssen.

Transaktionale Replikation

Die transaktionale Replikation ist eine periodische, automatisierte Replikation, bei der die Daten in Echtzeit (oder nahezu in Echtzeit) von einer Master-Datenbank an eine Datenbankreplik verteilt werden. Die transaktionale Replikation ist komplexer als der Schnappschuss. Es werden alle durchgeführten Transaktionen sowie der Endzustand der Datenbank repliziert, wodurch es möglich ist, den gesamten Transaktionsverlauf auf dem Replikat zu überwachen.
Zu Beginn des Prozesses der transaktionalen Replikation wird ein Schnappschuss auf den Abonnenten angewendet, und anschließend werden Daten kontinuierlich von der Master-Datenbank an ein Replikat übertragen, sobald Änderungen an diesen Daten vorgenommen werden. Die transaktionale Replikation wird häufig als Einwegreplikation eingesetzt.

Verwendungsfälle für die transaktionale Replikation:

• Einrichtung eines Datenbankservers mit einem Datenbankreplikat, das bei einem Ausfall des Hauptdatenbankservers für den Failover genutzt wird.
• Empfang von Berichten über Vorgänge in Zweigstellen durch den Einsatz mehrerer Publisher in den Zweigstellen und eines Abonnenten in der Zentrale.
• Replikation von Änderungen, sobald diese erfolgen.
• Die Daten in der Quelle ändern sich häufig.

Peer-to-Peer-Replikation

Peer-to-Peer-Replikation wird verwendet, um Datenbankdaten gleichzeitig an mehrere Abonnenten zu replizieren. Dieser MS SQL Server-Replikationstyp kann eingesetzt werden, wenn Ihre Datenbankserver weltweit verteilt sind. Änderungen können auf jedem der Datenbankserver vorgenommen werden. Die Änderungen werden an alle Datenbankserver weitergegeben. Die Peer-to-Peer-Replikation kann dabei helfen, eine Anwendung, die eine Datenbank nutzt, horizontal zu skalieren. Das grundlegende Funktionsprinzip basiert auf der transaktionalen Replikation.

Im Folgenden sehen Sie, wie die MS SQL Server-Peer-to-Peer-Replikation zwischen weltweit verteilten Datenbankservern eingesetzt werden kann.

Merge-Replikation

Merge-Replikation ist eine Art der bidirektionalen Replikation, die in der Regel in Server-zu-Client-Umgebungen zur Synchronisierung von Daten zwischen Datenbankservern eingesetzt wird, wenn diese nicht ständig miteinander verbunden sein können. Sobald die Netzwerkverbindung zwischen beiden Datenbankservern hergestellt ist, erkennen die Merge-Replikationsagenten die an beiden Datenbanken vorgenommenen Änderungen und passen die Datenbanken an, um ihren Status zu synchronisieren und zu aktualisieren. Die Merge-Replikation ähnelt der transaktionalen Replikation, allerdings werden die Daten sowohl vom Publisher zum Subscriber als auch in umgekehrter Richtung repliziert.

Diese Art der Datenbankreplikation ist die komplexeste aller MS SQL Server-Replikationsarten und wird nur selten verwendet. Beispielsweise kann die Merge-Replikation von mehreren gleichrangigen Filialen genutzt werden, die mit einem gemeinsamen Lager arbeiten. Jede Filiale darf die Informationen in der Lagerdatenbank ändern, und gleichzeitig müssen alle Filialen nach dem Warenversand oder der Anlieferung von Lieferungen an das Lager über den aktualisierten Zustand ihrer Datenbanken verfügen. Die Merge-Replikation kann in Fällen eingesetzt werden, in denen die aktualisierten Informationen gleichzeitig für die Haupt- (oder zentrale) Datenbank und die Zweigstellendatenbanken verfügbar sein müssen.

Anforderungen für die MS SQL Server-Replikation

Die folgenden Ports müssen für eingehenden Datenverkehr geöffnet sein:

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

Stellen Sie sicher, dass Sie die Windows-Firewall konfigurieren und die entsprechenden Ports für eingehenden Datenverkehr auf jedem Host aktivieren, bevor Sie MS SQL Server installieren. Die an der MS SQL-Replikation beteiligten Hosts müssen sich gegenseitig über einen Hostnamen auflösen können.
Vor der Konfiguration der MS SQL Server-Replikation muss die folgende Software für MS SQL Server installiert sein:

• .NET Framework – eine Sammlung von Bibliotheken
• MS SQL Server – die Datenbankserver-Software
• MS SQL Server Management Studio (SSMS) – Software zum Verwalten von MS SQL-Datenbanken über die GUI (grafische Benutzeroberfläche).

HINWEIS: In diesem Beitrag wird MS SQL Server 2016 für die Konfiguration verwendet. Sie können nach dem gleichen Prinzip die Replikation in neueren Versionen von SQL Server konfigurieren.
Beachten Sie, dass Sie, wenn Sie MS SQL Server 2016 auf dem ersten Rechner installieren, auf dem sich die Quelle befindet, auch auf dem zweiten Rechner MS SQL Server 2016 installieren müssen, damit die Datenbank ordnungsgemäß funktioniert. Wenn Sie beispielsweise die transaktionale Replikation von MS SQL konfigurieren möchten, können Sie den zweiten Datenbankserver (auf dem der Abonnent konfiguriert ist) einer Version verwenden, die maximal zwei Versionen von der Version der Quelle abweicht, auf der der Herausgeber konfiguriert ist. Wenn die Publisher-Version auf dem MS SQL Server 2016 ist, kann der Distributor auf den Versionen 2016, 2017, 2019 und 2022 konfiguriert werden, und der Subscriber kann auf MS SQL Server 2012, 2014, 2016, 2017 und 2019 konfiguriert werden. Die Version des Distributors darf nicht niedriger sein als die Version des Publishers. Die Replikation funktioniert beispielsweise nicht, wenn Sie auf dem zweiten Rechner MS SQL Server 2008 installieren.

Grundlegende Empfehlungen für die MS SQL-Datenbankreplikation

Bevor Sie die Umgebung für MS SQL Server konfigurieren, sollten Sie folgende Faktoren berücksichtigen:

• Es gibt Einschränkungen hinsichtlich Identitätsfeldern und Triggern.
• Veröffentlichungen dürfen nur Tabellen mit Primärschlüssel enthalten.
• Es wird empfohlen, bei großen Datenbanken nicht zu planen, um den Verbrauch großer Mengen an Rechenressourcen zu vermeiden.
• Seien Sie vorsichtig, wenn Sie Daten in der auf dem Abonnenten befindlichen Datenbankreplik ändern. Wenn eine Transaktion zur Änderung von Daten ansteht und diese Daten bereits bearbeitet oder gelöscht wurden, kann die Replikation unterbrochen werden, bis dieses Problem behoben ist.

Konfiguration der Umgebung

Wenn Sie die MS SQL-Replikation zum ersten Mal konfigurieren, wird empfohlen, dies zunächst in einer Testumgebung durchzuführen. Wir konfigurieren die Replikation beispielsweise auf SQL-Servern, die auf Virtuellen Maschinen laufen. In diesem Tutorial werden zwei Hosts mit Windows Server 2016 und MS SQL Server 2016 verwendet, um die MS SQL Server-Replikation zu erläutern.
Werfen wir einen Blick auf die Konfiguration der Testumgebung, die für die Erstellung dieses Blogbeitrags verwendet wurde, um die Konfiguration der MS-SQL-Server-Replikation besser zu verstehen.
Host 1

• IP-Adresse: 192.168.101.101
• Hostname: MSSQL01
• MS-SQL-Server-Instanz-ID: MSSQLSERVER1

Host 2

• IP-Adresse: 192.168.101.102
• Hostname: MSSQL02
• MS SQL Server-Instanz-ID: MSSQLSERVER2

Beide Rechner verfügen in ihrer Festplattenkonfiguration über die Festplatten C: und D:.
Sie können die Windows-Firewall vorübergehend deaktivieren, wenn Sie MS SQL Server installieren, um die Konfiguration der MS SQL Server-Replikation zu üben. Dieser Blogbeitrag erklärt nicht, wie man MS SQL Server installiert, da sich dieses Tutorial auf die Konfiguration der MS SQL Server-Replikation konzentriert. In diesem Beispiel werden beide MS SQL Server ohne PolyBase installiert.
Überprüfen Sie, ob Sie alle erforderlichen Funktionen für die MS SQL Server-Replikation installiert haben, sobald die MS SQL Server-Installation abgeschlossen ist. Beachten Sie, dass während der MS SQL Server-Installation die Datenbank-Engine-Dienste wie SQL Server-Replikation und R-Services ausgewählt werden müssen. In diesem Beispiel wird der Standardinstallationspfad verwendet (C:Program FilesMicrosoft SQL Server).

Andere Einstellungen:

• Gemischter Authentifizierungsmodus (Windows-Authentifizierung und MS SQL Server-Authentifizierung)
• Stammverzeichnis der Daten: D:MSSQL_Server
• Systemdatenbankverzeichnis: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Benutzerdatenbankverzeichnis: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Benutzerdatenbank-Protokollverzeichnis: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLData
• Backup-Verzeichnis: D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup

Sobald MS SQL Server 2016 und das SQL Server Management Studio auf den Maschinen installiert sind, können Sie Ihre MS SQL Server für die Datenbankreplikation vorbereiten.

Vorbereitung auf die MS SQL Server-Replikation

Sie müssen die Server konfigurieren, bevor Sie die Datenbankreplikation starten können. In unserem Beispiel wird ein Windows-Konto für die MS SQL Server-Replikationsagenten verwendet.

1. Erstellen Sie den mssql Benutzer auf beiden Servern und setzen Sie dasselbe Passwort.
2. Der mssql Benutzer ist in diesem Beispiel Mitglied der folgenden Gruppen:
   • Administratoren (lokale Administratoren auf lokalen Maschinen, keine Domänenadministratoren)
   • SQLRUserGroupMSSQLSERVER1
   • SQLServer2005SQLBrowserUser$MSSQL01
3. Sie können Benutzer und Gruppen bearbeiten, indem Sie Win+R drücken, CMD öffnen und den lusrmgr.msc Befehl ausführen.

Die zwei in diesem Beispiel verwendeten Windows Server-Maschinen sind nicht im Active Directory. Wenn Sie Active Directory verwenden, können Sie den mssql Benutzer auf dem Domänencontroller erstellen.

Verbindung zu MS SQL Server herstellen

1. Führen Sie das SQL Server Management Studio aus.
2. Melden Sie sich an (siehe Screenshot) als sa mit der SQL Server-Authentifizierung.
   • MSSQL01MSSQLSERVER1 ist der Hostname und der Instanzname der MS-SQL-Instanz auf dem ersten Server.
   • MSSQL02MSSQLSERVER2 ist der Hostname und der Instanzname der MS-SQL-Instanz auf dem zweiten Server.

   

Ebenso können Sie auf dem zweiten Server (MSSQL02) mit dem zweiten MS-SQL-Server verbunden werden (MSSQLSERVER2). Sie können sich auch vom ersten MS SQL-Server (MSSQL01) aus mit der zweiten MS SQL-Server-Instanz (MSSQLSERVER2) verbinden, indem Sie die Anmeldeinformationen in SQL Server Management Studio eingeben. Sie können in einer einzigen Instanz von SQL Server Management Studio eine Verbindung zu beiden MS SQL-Server-Instanzen (MSSQL01 und MSSQL02) herstellen.
Klicken Sie dazu im Objekt-Explorer auf , dann auf „Verbinden“ > „Datenbank-Engine“ und schließlich auf . In diesem Tutorial verbinden wir uns mithilfe von SQL Server Management Studio von MSSQL01 zu MSSQLSERVER1 und von MSSQL02 zu MSSQLSERVER2, um die MS-SQL-Server zu konfigurieren.

Starten des Agenten

Sobald Sie sich bei der MS-SQL-Server-Instanz angemeldet haben, werden Sie feststellen, dass der Agent nicht ausgeführt wird. Standardmäßig wird der SQL Server-Agent nicht automatisch gestartet. Sie können diesen Dienst manuell starten, es ist jedoch besser, ihn so zu konfigurieren, dass er nach dem Windows-Boot automatisch gestartet wird.

So konfigurieren Sie den Agent-Dienst für den automatischen Start:

1. Drücken Sie Win+R , führen Sie cmd aus, und führen Sie den Befehl services.msc aus.
2. Öffnen Sie die Eigenschaften des SQL Server Agent-Dienstes und setzen Sie den Starttyp auf Automatisch .

   

Benutzer für MS SQL Server konfigurieren

Nach dem Verbinden mit der Instanz „MSSQLSERVER1“ im SQL Server Management Studio müssen Sie die Benutzer konfigurieren:

1. Wechseln Sie zu Objekt-Explorer und öffnen Sie Sicherheit > Anmeldungen .
2. Klicken Sie mit der rechten Maustaste auf Anmeldungen und wählen Sie Neue Anmeldung . Wählen Sie Windows-Authentifizierung .
3. Geben Sie den Benutzername mssql im Abschnitt Allgemein ein.
4. Klicken Sie auf Suchen, wählen Sie anschließend Namen prüfen zur Bestätigung und klicken Sie zweimal auf OK, um die Einstellungen zu speichern.

   

5. Jetzt ist der MSSQL01mssql Windows-Benutzer zur Liste der Benutzer hinzugefügt worden, die sich in die Datenbank einloggen können (fügen Sie auf ähnliche Weise den mssql Benutzer zu Anmeldungen auf der zweiten Maschine MSSQL02 im SQL Server Management Studio hinzu).
6. Fügen Sie den mssql Benutzer zu den sysadmins Serverrollen in der Sicherheit Konfiguration der Datenbank im SQL Server Management Studio hinzu.
7. Gehen Sie zu MSSQL01MSSQLSERVER1 > Serverrollen , klicken Sie mit der rechten Maustaste auf sysadmin , und öffnen Sie Eigenschaften .
8. Klicken Sie auf der Seite Mitglieder auf Hinzufügen , geben Sie den Namen Ihres Benutzers mssql ein, und klicken Sie auf Namen überprüfen .
9. Wählen Sie das Kontrollkästchen des Benutzernamens MSSQL01mssql und klicken Sie auf OK .

   

10. Führen Sie dieselbe Konfiguration auf Ihrer zweiten Maschine (MSSQL02 in diesem Fall) durch.
11. Starten Sie beide Maschinen neu.

    Jetzt können Sie sich mit Windows-Authentifizierung auf beiden Servern einloggen.

    

Importieren einer Datenbank aus einem Backup

Lassen Sie uns eine Beispieldatenbank aus einem Backup importieren und dann die Datenbank von der ersten auf die zweite Maschine replizieren. Die AdventureWorks2016 Datenbank wird in diesem Beispiel als Beispieldatenbank verwendet.

1. Kopieren Sie die AdventureWorks2016.bak Datenbanksicherungsdatei in Ihr MSSQL-Sicherungsverzeichnis. In unserem Fall ist dieses Verzeichnis auf dem ersten Server D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackup
2. Importieren Sie eine Beispieldatenbank. Gehen Sie auf der ersten Maschine im SQL Server Management Studio zu MSSQL01MSSQLSERVER1 , klicken Sie mit der rechten Maustaste auf Datenbanken, und wählen Sie Datenbank wiederherstellen im Kontextmenü.

   

3. Wählen Sie im Fenster Datenbank wiederherstellen die benötigten Parameter aus:
   • Quelle: Gerät .
   • Klicken Sie auf die drei Punkte , um die Datenbanksicherungsdatei zu durchsuchen.
     • Wählen Sie im Fenster Sicherungsgeräte auswählen den Sicherungsmedientyp: Datei .
     • Klicken Sie auf Hinzufügen .
   • Wählen Sie die benötigte .bak Datei – D:MSSQL_ServerMSSQL13.MSSQLSERVER1MSSQLBackupAdventureWorks2016.bak
   • Drücken Sie OK , und drücken Sie dann erneut OK .
4. Die AdventureWorks2016 Datenbank wurde erfolgreich wiederhergestellt.

   

Sie können die Datenbank aus einem Backup auf dem zweiten Rechner importieren, auf dem die Datenbankreplik ausgeführt wird. Mit diesem Ansatz können Sie den Netzwerkverkehr reduzieren, da die Replikation damit beginnt, die seit der Erstellung des Backups vorgenommenen Änderungen zu kopieren, ohne die gesamten Datenbankdaten in eine leere Datenbank zu kopieren.
Stellen Sie die Datenbank aus einem Backup auf dem zweiten Server wieder her und benennen Sie die Datenbank um in AdventureWorks2016r , wobei „r“ für „Replik“ steht.
Schließlich haben wir:

Nach dem Import der Datenbank müssen Sie einige Optimierungen vornehmen, um Ihre MS SQL Server vorzubereiten

1. Auf dem MSSQL01 Rechner gehen Sie zu MSSQL01\MSSQLSERVER1 > Sicherheit > Anmeldungen , wählen Sie MSSQL01\mssql . Klicken Sie mit der rechten Maustaste (oder doppelklicken Sie) auf mssql Benutzer und wählen Sie Eigenschaften .
2. Unter Serverrollen aktivieren Sie das Kontrollkästchen neben der dbcreator Rolle.

   

3. Auf der Seite Benutzerzuordnung wählen Sie die diesem Login zugeordneten Benutzer aus und aktivieren Sie das Kontrollkästchen für die AdventureWorks2016 Datenbank (wählen Sie auf dem zweiten Server entsprechend AdventureWorks2016r aus).
4. Im Abschnitt database role membership aktivieren Sie das Kontrollkästchen db_owner .

   

5. Klicken Sie auf OK , um die Einstellungen zu speichern.

Führen Sie dieselbe Konfiguration auf dem Rechner MSSQL02 durch. Anschließend können Sie die für die Datenbankreplikation erforderlichen MS SQL Server-Komponenten konfigurieren.

Finden Sie den richtigen Plan für Ihre Umgebung

Entdecken Sie die flexiblen NAKIVO-Editionen und Modelle der Lizenzierung, die darauf ausgelegt sind, Datensicherheit auf Enterprise-Niveau zu einem Preis zu bieten, der zu Ihrem Budget passt.

PREISE ANSEHEN

Konfiguration der Datenbankreplikation

Die Konfiguration der Replikation im grafischen Modus ist die bequemste Methode. Die weitere Konfiguration wird in SQL Server Management Studio durchgeführt. In diesem Beispiel wird die transaktionale Datenbankreplikation erläutert, da sie zu den am häufigsten verwendeten Replikationstypen von MS SQL Server gehört.
Das Ansehen auf dem Hauptdatenbankserver (MSSQL01MSSQLSERVER1) und das Ansehen auf dem zweiten Server (MSSQL02MSSQLSERVER2) in SQL Server Management Studio sind im folgenden Screenshot dargestellt.

Konfiguration der Verteilung

Die Verteilung kann für mehrere Publisher und Subscriber verwendet werden. In diesem Beispiel wird die Verteilung auf dem Hauptserver konfiguriert, auf dem die Quelle gespeichert ist. Klicken Sie auf dem Hauptserver (MSSQL01MSSQLSERVER1) mit der rechten Maustaste auf Replikation und wählen Sie im Kontextmenü Verteilung konfigurieren aus.

Der Assistent zum Konfigurieren der Verteilung wird geöffnet.

1. Verteiler . Wählen Sie in diesem Beispiel die aktuelle Datenbankinstanz aus, die auf dem Hauptserver (MSSQL01MSSQLSERVER1) ausgeführt wird, damit diese als Verteiler fungiert. Klicken Sie mal auf Weiter , um zum nächsten Schritt im Assistenten zu gelangen.
2. SQL Server Agent starten . Wenn Sie den MS SQL Server Agent nicht wie oben beschrieben für den automatischen Start konfiguriert haben, wird die folgende Meldung angezeigt. Wählen Sie Ja, den SQL Server Agent-Dienst für den automatischen Start konfigurieren .

   

3. Snapshot-Ordner . Hier können Sie den Standardpfad beibehalten. Für die Initialisierung der Replikation ist ein Schnappschuss erforderlich. Stellen Sie sicher, dass auf der Festplatte, auf der sich Ihr Snapshot-Verzeichnis befindet, ausreichend freier Speicherplatz vorhanden ist. Der freie Speicherplatz muss mindestens der Größe der replizierten Datenbank entsprechen.
4. Verteilungsdatenbank . Geben Sie den Datenbanknamen ein. Sie können den Standardnamen ( distribution ) sowie die Ordner für die Verteilungsdatenbankdatei und die Protokolldatei beibehalten.

   

5. Publisher . Definieren Sie die MS SQL Server-Replikations-Publisher, die auf den Distributor zugreifen dürfen. Aktivieren Sie das Kontrollkästchen neben dem Namen der Verteilungsdatenbank auf der primären MS SQL Server-Instanz (auf der sich eine zu replizierende Quelle befindet). In diesem Beispiel ist dies die Instanz „MSSQL01MSSQLSERVER1“, und der Datenbankname lautet „ distribution “.
6. „Aktionen“ . Wählen Sie Verteilung konfigurieren , um die Verteilung im letzten Schritt des Assistenten zu konfigurieren. In diesem Beispiel werden wir keine Skriptdatei generieren, die später ausgeführt werden soll.

   

7. Assistent abschließen . Überprüfen Sie die Zusammenfassung der Verteilungskonfiguration und klicken Sie auf Fertigstellen , um den Verteiler zu erstellen.

   

8. Der Erfolgs -Status sollte angezeigt werden, wenn der Verteiler erfolgreich erstellt und konfiguriert wurde.

   

Wenn Sie sehen, dass ein Fehler aufgetreten ist, wenn der SQL Server Agent so konfiguriert wurde, dass er automatisch startet, gehen Sie zur Servicekonfiguration und überprüfen Sie den Startmodus des SQL Server Agent (siehe, wie man den Agentenstart oben in diesem Blogbeitrag konfiguriert).
Sie können auch die Eigenschaften des SQL Server Agent im SQL Server Management Studio öffnen und den Dienststatus und die Neustartoptionen überprüfen. Rechtsklicken Sie auf SQL Server-Agent am Ende der Liste im ObjektExplorer und klicken Sie auf Eigenschaften , um die Agent-Eigenschaften anzuzeigen oder zu bearbeiten.

Den Herausgeber konfigurieren

Sobald die Verteilung konfiguriert ist, können Sie den Herausgeber konfigurieren. Der Herausgeber sollte auf dem Hauptserver konfiguriert werden (MSSQL01MSSQLSERVER1), wo die zu replizierende Master-Datenbank gespeichert ist. Wählen Sie Replikation , rechtsklicken Sie auf Lokale Publikationen und wählen Sie im Kontextmenü Neue Publikation .

Der Neue Publikation Assistent öffnet sich.

1. Publikationsdatenbank . Wählen Sie die Datenbank, die Sie replizieren möchten ( AdventureWorks2016 in diesem Fall). Klicken Sie bei jedem Schritt im Assistenten auf Weiter , um fortzufahren.

   

2. Publikationstyp . Für diesen Schritt können Sie MS SQL Server-Replikationstypen für eine Datenbank auswählen. Lassen Sie uns eine transaktionale Publikation auswählen, die ein weit verbreiteter Replikationstyp ist.
3. Artikel . Wählen Sie die benötigten Objekte aus, wie Tabellen, Prozeduren, Ansichten, indizierte Ansichten und benutzerdefinierte Funktionen, um diese als Artikel zu veröffentlichen. Es ist möglich, die Replikation der benutzerdefinierten Felder in den Tabellen auszuwählen und bei Bedarf Artikel-Eigenschaften auszuwählen. In diesem Beispiel sind einige Tabellen ausgewählt.

   

4. Tabellenzeilen filtern . In diesem Beispiel werden keine Filter hinzugefügt (dies ist die Standardkonfiguration der Filter). Sie können bei Bedarf Filter hinzufügen.
5. Snapshot-Agent . Geben Sie an, wann der Snapshot-Agent ausgeführt werden soll. Konfigurieren Sie den Agenten, um sofort ausgeführt zu werden. Wählen Sie Erstellen Sie einen Schnappschuss sofort und halten Sie den Schnappschuss verfügbar, um Abonnements zu initialisieren .

   

6. Agent Sicherheit . Wählen Sie Verwenden Sie die Sicherheitseinstellungen des Snapshot-Agenten . Klicken Sie auf die Schaltfläche Sicherheitseinstellungen , um das Konto auszuwählen, unter dem der Agent ausgeführt wird.

   Geben Sie im geöffneten Fenster Snapshot-Agent-Sicherheit die Anmeldeinformationen des mssql Windows-Benutzers ein, den Sie zuvor erstellt haben. Wählen Sie Verbindung zum Publisher durch Immitieren des Prozesskontos . Klicken Sie auf OK, um die Einstellungen zu speichern und zum Assistenten zurückzukehren.

   

   Nachdem Sie den benötigten Benutzer definiert haben, können Sie diesen Benutzer in den Abschnitten Snapshot-Agent und Log Reader Agent sehen.

   

7. Assistentenaktionen . Aktivieren Sie das obere Kontrollkästchen, um die Veröffentlichung im letzten Schritt des Assistenten zu erstellen.
8. Beenden Sie den Assistenten . Überprüfen Sie Ihre Veröffentlichungs-Konfiguration und klicken Sie auf Fertigstellen , um eine neue Veröffentlichung zu erstellen.

   

Im Fenster Veröffentlichung erstellen können Sie den Fortschritt der Erstellung einer neuen Veröffentlichung überwachen. Warten Sie eine Weile, und Sie sollten den Erfolgsstatus sehen, wenn alles korrekt ausgeführt wurde.

Die Veröffentlichung ist jetzt erstellt und Sie können die Veröffentlichung im Objektexplorer sehen, indem Sie zu Replikation > Lokale Veröffentlichungen gehen.

Konfigurieren des Abonnenten

Wie Sie sich erinnern, kann die MS SQL Server-Replikation entweder eine Pull- oder eine Push-Replikation sein. Wenn Sie eine Push-Replikation konfigurieren, sollten Sie den Abonnenten konfigurieren, um Agenten auf dem Hauptdatenbankserver (MSSQL01 in diesem Fall) auszuführen. Wenn Sie eine Pull-Replikation konfigurieren, muss der Abonnent so konfiguriert werden, dass er Agenten auf der zweiten Maschine (MSSQL02), auf der das Datenbankreplikat erstellt wird, ausführt.
Konfigurieren wir eine Push-Replikation und erstellen Sie ein neues Abonnement auf dem ersten MS SQL Server (MSSQL01MSSQLSERVER1), auf dem sich die Masterdatenbank befindet.
Gehen Sie im Objektexplorer zu Replikation , klicken Sie mit der rechten Maustaste auf Lokale Abonnements und wählen Sie im Kontextmenü Neue Abonnements .

Der Assistent Neues Abonnement öffnet sich.

1. Veröffentlichung . Wählen Sie die Veröffentlichung aus, für die Sie ein neues Abonnement erstellen möchten. In unserem Beispiel lautet der Name des Publishers MSSQL01MSSQLSERVER1, und der Name der (zuvor erstellten) Publikation ist AdvWorks_Pub . Klicken Sie bei jedem Schritt des Assistenten auf Weiter , um fortzufahren.
2. Speicherort des Verteilungs-Agenten . Wählen Sie den Replikationstyp aus, indem Sie entweder das Push-Abonnement oder das Pull-Abonnement auswählen. In unserem Beispiel sollen alle Agenten auf der Seite des Quellservers ausgeführt werden, daher wird die erste Option ausgewählt, um ein Push-Abonnement zu erstellen. So können Sie die MS SQL Server-Replikation zentral verwalten.

   

3. Abonnenten . Standardmäßig wird der Server, auf dem Sie den Assistenten ausführen (in diesem Fall MSSQL01MSSQLSERVER1), als Abonnent angezeigt, und die Abonnementdatenbank ist nicht definiert. Fügen wir einen neuen Abonnenten hinzu und wählen wir eine Abonnementdatenbank aus, die sich auf dem zweiten Datenbankserver (MSSQL01MSSQLSERVER2) befindet. Klicken Sie auf Abonnenten hinzufügen und wählen Sie im Kontextmenü SQL Server-Abonnenten hinzufügen aus.
   • Geben Sie im Popup-Fenster die Anmeldedaten für die zweite MSSQL-Server-Instanz (in unserem Fall MSSQL01MSSQLSERVER2) ein und klicken Sie auf Verbinden .

     

   • Aktivieren Sie das Kontrollkästchen Ihres zweiten Servers, auf dem Ihr Datenbankreplikat gespeichert werden soll (MSSQL02MSSQLSERVER2), und wählen Sie im Dropdown-Menü Abonnementdatenbank eine neue Datenbank oder eine aus einem Backup wiederhergestellte vorhandene Datenbank aus, die als Datenbankreplikat verwendet werden soll.

     In unserem Beispiel wurde die Datenbank AdventureWorks2016r auf dem zweiten Server erstellt, indem die Haupt-(Quell-)Datenbank AdventureWorks2016 aus einem Backup wiederhergestellt wurde, um die Replikation zu starten. Die Replikation beginnt mit der Replikation ausschließlich neuer Daten und nicht mit dem Kopieren der gesamten Datenbank nach dem Start des Replikationsprozesses. Daher wird in diesem Beispiel AdventureWorks2016r als Abonnementdatenbank ausgewählt.

     

4. Sicherheit des Verteilungs-Agenten . Klicken Sie auf die Schaltfläche mit den drei Punkten (…) und wählen Sie den Benutzer sowie weitere Sicherheitsoptionen für den Verteilungs-Agenten aus.

   Legen Sie im daraufhin geöffneten Fenster Sicherheit des Verteilungs-Agenten fest, dass der Verteilungs-Agent auf dem Host MSSQL01 unter dem Benutzerkonto mssql ausgeführt wird. Geben Sie das Passwort für den Windows-Benutzer mssql ein. Wählen Sie Verbinden Sie sich mit dem Distributor, indem Sie das Prozesskonto imitieren, und wählen Sie Verbinden Sie sich mit dem Abonnenten, indem Sie das Prozesskonto imitieren. Drücken Sie OK , um die Einstellungen

   zu speichern.

   

   Jetzt sind Ihre Abonnement-Eigenschaften festgelegt.

   

5. Synchronisierungszeitplan . Wählen Sie den Agenten, der sich auf dem Distributor befindet, um kontinuierlich auszuführen für den aktuellen Abonnenten.
6. Abonnements initialisieren . Wählen Sie das Initialisieren Kontrollkästchen und im Dropdown-Menü wählen Sie Sofort aus, um festzulegen, wann das Abonnement initialisiert werden soll. Sie können auch die Speicheroptimierte Option auswählen, wenn nötig.

   

7. Assistentenaktionen . Wählen Sie das obere Kontrollkästchen, um das/die Abonnement(e) am Ende des Assistenten zu erstellen.
8. Assistent abschließen . Sie können Ihre Abonnementeinstellungen überprüfen und auf Fertigstellen klicken, um das Abonnement zu erstellen.

   

9. Warten Sie, bis das Abonnement erstellt wird. Wenn Sie den Erfolg Status sehen, bedeutet das, dass das Abonnement erfolgreich erstellt wurde.

   

10. Nach der Konfiguration der Replikation im SQL Server werden drei Jobs im Objekt-Explorer angezeigt, und Sie können sie sehen, indem Sie zu SQL Server Agent > Aufträge gehen.

    

Abschluss der Replikationskonfiguration

Sobald Sie den Distributor, den Publisher und den Abonnenten konfiguriert haben, können Sie den Replikationsstatus von MS SQL Server überprüfen.

1. Auf dem ersten Server (MSSQL01MSSQLSERVER1) starten Sie den Replikationsmonitor, um den Replikationsstatus von MS SQL Server zu sehen. In SQL Server Management Studio wählen Sie Ihre MS SQL Server-Instanz (MSSQLSERVER1) aus, gehen zu Replikation , klicken mit der rechten Maustaste auf Lokale Publikationen und wählen im Kontextmenü Replikationsmonitor starten .

   

2. In unserem Fall gibt es einen Log Reader Agent Fehler. Um Fehlerdetails zu sehen, wählen Sie die Quellendatenbank (den Publisher) im linken Bereich aus, wählen den Agenten Tab im rechten Bereich und doppelklicken auf den Fehlernamen.

   

3. In dem sich öffnenden Fenster können Sie die Agenten-Historie und Fehlermeldungen sehen. Die Fehlermeldungen sind:
   • Der Prozess konnte sp_replcmds auf MSSQL01MSSQLSERVER1 nicht ausführen. Quelle: MSSQl_REPL. Fehlernummer: MSSQL_REPL20011).
   • Kann nicht als der Datenbankbenutzer ausgeführt werden, da der Benutzer „dbo“ nicht existiert, dieser Benutzertyp nicht nachgeahmt werden kann oder Sie keine Berechtigung haben. (Quelle: MSSQLServer, Fehlernummer: 15517).

   

   Die zweite Fehlermeldung deutet darauf hin, dass eine Art von Berechtigung fehlt. Lassen Sie uns diesen Fehler beheben.

4. Erstellen Sie eine neue Abfrage in MS SQL Management Studio und führen Sie diese Abfrage aus. Klicken Sie im Hauptfenster auf die Schaltfläche Neue Abfrage .
5. Geben Sie im SQL-Abfragebereich des Hauptfensters folgende Abfrage ein:

   USE AdventureWorks2016

   GO

   EXEC sp_changedbowner 'sa'

   GO

   Klicken Sie auf die Schaltfläche Ausführen .

   

   Die Befehle wurden erfolgreich abgeschlossen.

6. Gehen Sie als nächstes zu MSSQL01MSSQLSERVER1 > Replikation > Lokale Publikationen > [AdventureWorks2016]: AdvWorks_Pub . Klicken Sie mit der rechten Maustaste auf den Publikationsnamen und wählen Sie im Kontextmenü Snapshot-Agent-Status anzeigen . Sie können auf Aktion > Aktualisieren klicken, um den Status zu aktualisieren, und Alle Abonnements erneut initialisieren , um einen Snapshot auf jeden Abonnenten anzuwenden.

   Jetzt ist alles gelöst, keine Fehler werden angezeigt, und die MS SQL Server Replikation sollte funktionieren.

   

Überprüfen, wie die Replikation funktioniert

Lassen Sie uns die MS SQL Server Replikation in Aktion sehen. Sehen Sie sich den Inhalt einer Tabelle der AdventureWorks2016 -Datenbank auf dem ersten MS SQL-Server ( MSSQL01MSQLSERVER1 ) an. In unserem Beispiel werden wir alle Daten aus der Person.AddressType -Tabelle auswählen. Um dies zu tun, führen Sie die Abfrage aus:
USE AdventureWorks2016;
GO
SELECT *
FROM Person.AddressType
;
Das Ergebnis der Abfrage wird im unten stehenden Screenshot angezeigt:

Führen Sie eine ähnliche Abfrage auf dem zweiten Server aus, um alle Daten der Person.AddressType der Datenbank AdventureWorks2016r auf MSSQL02MSSQLSERVER2 anzuzeigen.
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Wenn Sie die obigen und unteren Screenshots vergleichen, sind die Inhalte der Person.AddressType in beiden Datenbanken identisch (eine Quelldatenbank auf dem ersten Server und die Zieldatenbank, die eine Datenbankreplikation auf dem zweiten Server ist).

Lassen Sie uns eine Zeile in der PersonAddressType -Tabelle aus der AdventureWorks2016 -Datenbank (Quelle) auf dem ersten Server (MSSQL01MSSQLSERVER1) löschen. Führen Sie die Abfrage aus, um eine Zeile zu löschen, die „Billing“ im Namen enthält, und um anschließend den Inhalt der Tabelle anzuzeigen:
DELETE FROM Person.AddressType WHERE Name='Billing';
SELECT * FROM Person.AddressType;

Wie Sie sehen können, wurde die erste Zeile mit der AddressTypeID 1 und dem Namen „Billing“ aus der Person.AddressType Tabelle in der AdventureWorks2016 Datenbank auf dem MSSQL01 Rechner gelöscht.
Die transaktionale Replikation läuft. Sehen wir uns den Inhalt der Tabelle Person.AddressType in der Datenbank AdventureWorks2016r auf dem Rechner MSSQL02 an. Führen Sie erneut eine ähnliche Abfrage wie oben aus, um den Inhalt der Tabelle anzuzeigen:
USE AdventureWorks2016r;
GO
SELECT *
FROM Person.AddressType
;
Infolge der Replikation wurde die erste Zeile auch aus der Tabelle Person.AddressType in der sekundären Datenbank gelöscht, die als Datenbankreplik fungiert ( AdventureWorks2016r ). Die Ergebnisse sehen Sie im folgenden Screenshot.

Die Datenbankreplikation in SQL Server arbeitet ordnungsgemäß.

Fazit

Es gibt vier Arten der MS SQL Server-Replikation: Schnappschuss-, Transaktions-, Peer-to-Peer- und Merge-Replikation. Da die Transaktionsreplikation weit verbreitet ist, haben wir in diesem Blogbeitrag diesen MS SQL Server-Replikationstyp konfiguriert. Der Distributor, der Publisher und der Subscriber müssen konfiguriert werden, damit die Datenbankreplikation funktioniert. Der Subscriber kann auf einem Server der Quelle (Push-Replikation) und auf einem Server des Ziels (Pull-Replikation) konfiguriert werden.
Sie sollten jedoch in Betracht ziehen, sowohl die Replikation als auch Backup von MS-SQL-Datenbanken zu nutzen, um die Chancen auf eine erfolgreiche Datenwiederherstellung von Datenbankdaten zu erhöhen.

Probieren Sie NAKIVO Backup & Replication aus

Nutzen Sie die kostenlose Testversion, um alle Funktionen der Datensicherheit der Lösung kennenzulernen. 15 Tage kostenlos. Keine Einschränkungen hinsichtlich Funktionen oder Kapazitäten. Keine Kreditkarte erforderlich.

HÄUFIG GESTELLTE FRAGEN