Veröffentlichen der Ausführung von gespeicherten Prozeduren in der Transaktionsreplikation

Wenn Sie gespeicherte Prozeduren besitzen, die auf dem Verleger ausgeführt werden und sich auf die veröffentlichten Tabellen auswirken, sollten Sie darüber nachdenken, diese gespeicherten Prozeduren als Artikel für die Ausführung einer gespeicherten Prozedur in Ihre Publikation aufzunehmen. Die Definition der Prozedur (die CREATE PROCEDURE-Anweisung) wird beim Initialisieren des Abonnements auf den Abonnenten repliziert. Wenn die Prozedur dann auf dem Verleger ausgeführt wird, führt die Replikation auch die entsprechende Prozedur auf dem Abonnenten aus. Dies kann in den Fällen, in denen große Batchoperationen ausgeführt werden, zu einer deutlichen Leistungssteigerung führen, da nur die Ausführung der Prozedur repliziert wird und sich das Replizieren der einzelnen Änderungen für jede Zeile erübrigt. Nehmen wir z. B. an, Sie erstellen in der Publikationsdatenbank die folgende gespeicherte Prozedur:

CREATE PROC give_raise AS
UPDATE EMPLOYEES SET salary = salary * 1.10

Mit dieser Prozedur erhält jeder der 10.000 Angestellten in Ihrem Unternehmen eine Gehaltserhöhung von 10 %. Wenn Sie diese gespeicherte Prozedur auf dem Verleger ausführen, aktualisiert sie die Gehälter aller Angestellten. Ohne die Replikation der Ausführung der gespeicherten Prozedur würde die Aktualisierung an die Abonnenten als eine große Transaktion mit mehreren Schritten gesendet:

BEGIN TRAN
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 1'
UPDATE EMPLOYEES SET salary = salary * 1.10 WHERE PK = 'emp 2'

Dieser Vorgang würde sich dann für 10.000 Aktualisierungen wiederholen.

Mit der Replikation der Ausführung der gespeicherten Prozedur sendet die Replikation lediglich den Befehl zum Ausführen der gespeicherten Prozedur auf dem Abonnenten. Sie schreibt also nicht alle Aktualisierungen in die Verteilungsdatenbank, um sie dann über das Netzwerk an den Abonnenten zu senden:

EXEC give_raise

Wichtig:

Die Replikation von gespeicherten Prozeduren eignet sich nicht für alle Anwendungen. Falls ein Artikel horizontal gefiltert wird, sodass auf dem Verleger andere Zeilen als auf dem Abonnenten vorhanden sind, führt das Ausführen ein und derselben gespeicherten Prozedur auf dem Abonnenten und dem Verleger zu unterschiedlichen Ergebnissen. Unterschiedliche Ergebnisse beim Ausführen ein und derselben gespeicherten Prozedur auf dem Verleger und dem Abonnenten entstehen aber auch dann, wenn die Aktualisierung auf einer Unterabfrage einer anderen, nicht replizierten Tabelle basiert.

So veröffentlichen Sie die Ausführung einer gespeicherten Prozedur

• SQL Server Management Studio: Vorgehensweise: Veröffentlichen der Ausführung einer gespeicherten Prozedur in einer Transaktionspublikation (SQL Server Management Studio)
• Replikationsprogrammierung mit Transact-SQL: Führen Sie sp_addarticle (Transact-SQL) aus, und geben Sie dann für den @type-Parameter den Wert 'serializable proc exec' (empfohlen) oder 'proc exec' an. Weitere Informationen zum Definieren von Artikeln finden Sie unter How to: Define an Article (Replication Transact-SQL Programming).

Ändern der Prozedur auf dem Abonnenten

Standardmäßig wird die Definition der gespeicherten Prozedur auf dem Verleger an jeden Abonnenten weitergegeben. Sie können aber auch die gespeicherte Prozedur auf dem Abonnenten ändern. Dies ist dann sinnvoll, wenn auf dem Verleger und dem Abonnenten eine unterschiedliche Logik ausgeführt werden soll. Gehen wir als Beispiel von sp_big_delete aus, einer gespeicherten Prozedur auf dem Verleger mit zwei Funktionen: Zum einen löscht die Prozedur 1.000.000 Zeilen aus der replizierten big_table1-Tabelle, zum anderen aktualisiert sie die nicht replizierte big_table2-Tabelle. Um den Bedarf an Netzwerkressourcen zu reduzieren, sollten Sie das Löschen der 1 Million Zeilen als gespeicherte Prozedur weitergeben, indem Sie sp_big_delete veröffentlichen. Auf dem Abonnenten können Sie sp_big_delete so ändern, dass nur die 1 Million Zeilen gelöscht werden, ohne eine nachfolgende Aktualisierung von big_table2 auszuführen.

Hinweis:
Standardmäßig werden alle Änderungen, die mithilfe von ALTER PROCEDURE auf dem Verleger vorgenommen werden, an den Abonnenten weitergegeben. Wenn Sie dies verhindern möchten, deaktivieren Sie die Weitergabe der Schemaänderungen, bevor Sie ALTER PROCEDURE ausführen. Informationen zu Schemaänderungen finden Sie unter Vornehmen von Schemaänderungen in Publikationsdatenbanken.

Typen von Artikeln für die Ausführung einer gespeicherter Prozedur

Die Ausführung einer gespeicherten Prozedur kann sowohl in Form eines serialisierbaren Prozedurausführungsartikels als auch in Form eines einfachen Prozedurausführungsartikels veröffentlicht werden.

Die Verwendung der ersten Option wird empfohlen, weil dabei die Prozedurausführung nur dann repliziert wird, wenn die Prozedur im Kontext einer serialisierbaren Transaktion ausgeführt wird. Falls die gespeicherte Prozedur von außerhalb einer serialisierbaren Transaktion ausgeführt wird, werden Änderungen an den Daten in den veröffentlichten Tabellen als eine Reihe von DML-Anweisungen repliziert. Dieses Verhalten trägt dazu bei, dass die Daten auf dem Abonnenten mit den Daten auf dem Verleger konsistent sind. Dies erweist sich vor allem für Batchoperationen, wie z. B. umfangreiche Cleanupoperationen, als hilfreich.

Bei der zweiten Option wird die Prozedurausführung auf alle Abonnenten des Artikels repliziert, und zwar unabhängig davon, ob die einzelnen Anweisungen in der gespeicherten Prozedur erfolgreich waren. Da Änderungen, die von der gespeicherten Prozedur an den Daten vorgenommen wurden, innerhalb mehrerer Transaktionen auftreten können, ist außerdem nicht sichergestellt, dass die Daten auf den Abonnenten mit den Daten auf dem Verleger konsistent sind.

Im folgenden Beispiel werden die Gründe veranschaulicht, warum das Replizieren von Prozeduren als serialisierbare Prozedurartikel empfehlenswert ist.

BEGIN TRANSACTION T1
SELECT @var = max(col1) FROM tableA
UPDATE tableA SET col2 = <value> 
   WHERE col1 = @var 

BEGIN TRANSACTION T2
INSERT tableA VALUES <values>
COMMIT TRANSACTION T2

Im vorhergehenden Beispiel wurde davon ausgegangen, dass die SELECT-Anweisung in der T1-Transaktion vor der INSERT-Anweisung in der T2-Transaktion stattfindet.

Wird die Prozedur nicht in einer serialisierbaren Transaktion ausgeführt (beispielsweise mit einer auf SERIALIZABLE festgelegten Isolationsstufe), kann die T2-Transaktion innerhalb des Bereichs der SELECT-Anweisung in T1 eine neue Zeile einfügen und vor T1 ein Commit ausführen. Das bedeutet auch, dass sie vor T1 auf den Abonnenten angewendet wird. Wird T1 auf den Abonnenten angewendet, könnte die SELECT-Anweisung einen anderen Wert auf dem Verleger zurückgeben und zu einem unterschiedlichen Ergebnis der UPDATE-Anweisung führen.

Wenn die Prozedur in einer serialisierbaren Transaktion ausgeführt wird, darf die T2-Transaktion keine Zeile innerhalb des von der SELECT-Anweisung in T2 liegenden Bereichs einfügen. Sie wird dann so lange blockiert, bis T1 einen Commit ausführt und so dieselben Ergebnisse auf dem Abonnenten sichergestellt sind.

Sperren werden länger aufrecht erhalten, wenn Sie die Prozedur in einer serialisierbaren Transaktion ausführen, und können zu reduzierter Parallelität führen.

Die XACT_ABORT-Einstellung

Beim Replizieren der Ausführung einer gespeicherten Prozedur sollte die Einstellung XACT_ABORT für die Sitzung, die die gespeicherte Prozedur ausführt, ON angeben. Wenn für XACT_ABORT die Einstellung OFF festgelegt ist und während der Ausführung der Prozedur auf dem Verleger ein Fehler auftritt, kommt es auch auf dem Abonnenten zum selben Fehler, was wiederum zum Ausfall des Verteilungs-Agents führt. Durch Angeben von ON für XACT_ABORT wird sichergestellt, dass alle Fehler, zu denen es bei der Ausführung auf dem Verleger kommt, ein Rollback der gesamten Ausführung auslösen, sodass der Ausfall des Verteilungs-Agents vermieden wird. Weitere Informationen zum Festlegen von XACT_ABORT finden Sie unter SET XACT_ABORT (Transact-SQL).

Wenn für XACT_ABORT OFF angegeben werden muss, geben Sie den -SkipErrors-Parameter für den Verteilungs-Agent an. Auf diese Weise kann der Agent auch dann die Änderungen auf den Abonnenten anwenden, wenn es zu einem Fehler kommt. Weitere Informationen zu diesem Parameter finden Sie unter Überspringen von Fehlern in der Transaktionsreplikation.

Siehe auch

Konzepte

Artikeloptionen für die Transaktionsreplikation

Hilfe und Informationen

Informationsquellen für SQL Server 2005