Veröffentlichen der Ausführung von gespeicherten Prozeduren in der TransaktionsreplikationPublishing Stored Procedure Execution in Transactional Replication

GILT FÜR: jaSQL Server jaAzure SQL-Datenbank (nur verwaltete Instanz) neinAzure SQL Data Warehouse neinParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure SQL Data Warehouse noParallel Data Warehouse

Wenn Sie gespeicherte Prozeduren verwenden, 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 Veröffentlichung aufzunehmen.If you have one or more stored procedures that execute at the Publisher and affect published tables, consider including those stored procedures in your publication as stored procedure execution articles. 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.The definition of the procedure (the CREATE PROCEDURE statement) is replicated to the Subscriber when the subscription is initialized; when the procedure is executed at the Publisher, replication executes the corresponding procedure at the Subscriber. Dies kann in den Fällen, in denen umfangreiche Batchvorgänge 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.This can provide significantly better performance for cases where large batch operations are performed, because only the procedure execution is replicated, bypassing the need to replicate the individual changes for each row. Nehmen wir z. B. an, Sie erstellen in der Veröffentlichungsdatenbank die folgende gespeicherte Prozedur:For example, assume you create the following stored procedure in the publication database:

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 %.This procedure gives each of the 10,000 employees in your company a 10 percent pay increase. Wenn Sie diese gespeicherte Prozedur auf dem Verleger ausführen, aktualisiert sie die Gehälter aller Angestellten.When you execute this stored procedure at the Publisher, it updates the salary for each employee. Ohne die Replikation der Ausführung der gespeicherten Prozedur würde das Update an die Abonnenten als eine große Transaktion mit mehreren Schritten gesendet:Without the replication of stored procedure execution, the update would be sent to Subscribers as a large, multi-step transaction:

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 Updates wiederholen.And this repeats for 10,000 updates.

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 Updates in die Verteilungsdatenbank, um sie dann über das Netzwerk an den Abonnenten zu senden:With the replication of stored procedure execution, replication sends only the command to execute the stored procedure at the Subscriber, rather than writing all the updates to the distribution database and then sending them over the network to the Subscriber:

EXEC give_raise  

Wichtig

Die Replikation von gespeicherten Prozeduren eignet sich nicht für alle Anwendungen.Stored procedure replication is not appropriate for all applications. 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.If an article is filtered horizontally, so that there are different sets of rows at the Publisher than at the Subscriber, executing the same stored procedure at both returns different results. Unterschiedliche Ergebnisse beim Ausführen ein und derselben gespeicherten Prozedur auf dem Verleger und dem Abonnenten entstehen aber auch dann, wenn das Update auf einer Unterabfrage einer anderen, nicht replizierten Tabelle basiert.Similarly, if an update is based on a subquery of another, nonreplicated table, executing the same stored procedure at both the Publisher and Subscriber returns different results.

So veröffentlichen Sie die Ausführung einer gespeicherten ProzedurTo publish the execution of a stored procedure

Ändern der Prozedur auf dem AbonnentenModifying the Procedure at the Subscriber

Standardmäßig wird die Definition der gespeicherten Prozedur auf dem Verleger an jeden Abonnenten weitergegeben.By default, the stored procedure definition at the Publisher is propagated to each Subscriber. Sie können aber auch die gespeicherte Prozedur auf dem Abonnenten ändern.However, you can also modify the stored procedure at the Subscriber. Dies ist dann sinnvoll, wenn auf dem Verleger und dem Abonnenten eine unterschiedliche Logik ausgeführt werden soll.This is useful if you want different logic to be executed at the Publisher and Subscriber. Gehen wir als Beispiel von sp_big_deleteaus, 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.For example, consider sp_big_delete, a stored procedure at the Publisher that has two functions: it deletes 1,000,000 rows from the replicated table big_table1 and updates the nonreplicated table big_table2. Um den Bedarf an Netzwerkressourcen zu reduzieren, sollten Sie das Löschen der 1 Million Zeilen als gespeicherte Prozedur weitergeben, indem Sie sp_big_deleteveröffentlichen.To reduce the demand on network resources, you should propagate the 1 million row delete as a stored procedure by publishing sp_big_delete. Auf dem Abonnenten können Sie sp_big_delete so ändern, dass nur die 1 Million Zeilen gelöscht werden, ohne ein nachfolgendes Update von big_table2auszuführen.At the Subscriber, you can modify sp_big_delete to delete only the 1 million rows and not perform the subsequent update to big_table2.

Hinweis

Standardmäßig werden alle Änderungen, die mithilfe von ALTER PROCEDURE auf dem Verleger vorgenommen werden, an den Abonnenten weitergegeben.By default, any changes made using ALTER PROCEDURE at the Publisher are propagated to the Subscriber. Wenn Sie dies verhindern möchten, deaktivieren Sie die Weitergabe der Schemaänderungen, bevor Sie ALTER PROCEDURE ausführen.To prevent this, disable the propagation of schema changes before executing ALTER PROCEDURE. Weitere Informationen zu Schemaänderungen finden Sie unter Vornehmen von Schemaänderungen in Veröffentlichungsdatenbanken.For information about schema changes, see Make Schema Changes on Publication Databases.

Typen von Artikeln für die Ausführung einer gespeicherter ProzedurTypes of Stored Procedure Execution Articles

Es gibt zwei verschiedene Möglichkeiten, mit denen die Ausführung einer gespeicherten Prozedur veröffentlicht werden kann: serialisierbare Prozedurausführungsartikel und Prozedurausführungsartikel.There are two different ways in which the execution of a stored procedure can be published: serializable procedure execution article and procedure execution article.

  • 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.The serializable option is recommended because it replicates the procedure execution only if the procedure is executed within the context of a serializable transaction. 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.If the stored procedure is executed from outside a serializable transaction, changes to data in published tables are replicated as a series of DML statements. Dieses Verhalten trägt dazu bei, dass die Daten auf dem Abonnenten mit den Daten auf dem Verleger konsistent sind.This behavior contributes to making data at the Subscriber consistent with data at the Publisher. Dies erweist sich vor allem für Batchvorgänge, wie z. B. umfangreiche Cleanupvorgänge, als hilfreich.This is especially useful for batch operations, such as large cleanup operations.

  • Bei der Option zum Ausführen von Prozeduren kann die Prozedurausführung auf alle Abonnenten repliziert werden, und zwar unabhängig davon, ob die einzelnen Anweisungen in der gespeicherten Prozedur erfolgreich waren.With the procedure execution option, it is possible that execution could be replicated to all Subscribers regardless of whether individual statements in the stored procedure were successful. 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.Furthermore, because changes made to data by the stored procedure can occur within multiple transactions, data at the Subscribers might not be consistent with data at the Publisher. Zur Behebung dieser Probleme müssen Abonnenten schreibgeschützt sein. Außerdem ist es erforderlicht, dass Sie eine Isolationsstufe verwenden, die größer ist als READ UNCOMMITTED.To address these issues, it is required that Subscribers are read-only and that you use an isolation level greater than read uncommitted. Wenn Sie READ UNCOMMITTED verwendet, werden Änderungen an Daten in veröffentlichten Tabellen als Reihe von DML-Anweisungen repliziert.If you use read uncommitted, changes to data in published tables are replicated as a series of DML statements.

Im folgenden Beispiel werden die Gründe veranschaulicht, warum das Replizieren von Prozeduren als serialisierbare Prozedurartikel empfehlenswert ist.The following example illustrates why it is recommended that you set up replication of procedures as serializable procedure articles.

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.In the previous example, it is assumed that the SELECT in transaction T1 happens before the INSERT in transaction T2.

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.If the procedure is not executed within a serializable transaction (with isolation level set to SERIALIZABLE), transaction T2 will be allowed to insert a new row within the range of the SELECT statement in T1 and it will commit before T1. Das bedeutet auch, dass sie vor T1 auf den Abonnenten angewendet wird.This also means that it will be applied at the Subscriber before T1. 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.When T1 is applied at the Subscriber, the SELECT can potentially return a different value than at the Publisher and can result in a different outcome from the UPDATE.

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.If the procedure is executed within a serializable transaction, transaction T2 will not be allowed to insert within the range covered by the SELECT statement in T2. Sie wird dann so lange blockiert, bis T1 einen Commit ausführt und so dieselben Ergebnisse auf dem Abonnenten sichergestellt sind.It will be blocked until T1 commits ensuring the same results at the Subscriber.

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.Locks will be held longer when you execute the procedure within a serializable transaction and may result in reduced concurrency.

Die XACT_ABORT-EinstellungThe XACT_ABORT Setting

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.When replicating stored procedure execution, the setting for the session executing the stored procedure should specify XACT_ABORT ON. 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.If XACT_ABORT is set to OFF, and an error occurs during execution of the procedure at the Publisher, the same error will occur at the Subscriber, causing the Distribution Agent to fail. 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.Specifying XACT_ABORT ON ensures that any errors encountered during execution at the Publisher cause the entire execution to be rolled back, avoiding the Distribution Agent failure. Weitere Informationen zum Festlegen von XACT_ABORT finden Sie unter SET XACT_ABORT (Transact-SQL).For more information about setting XACT_ABORT, see 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.If you require a setting of XACT_ABORT OFF, specify the -SkipErrors parameter for the Distribution Agent. Auf diese Weise kann der Agent auch dann die Änderungen auf den Abonnenten anwenden, wenn es zu einem Fehler kommt.This allows the agent to continue applying changes at the Subscriber even if an error is encountered.

Weitere InformationenSee Also

Article Options for Transactional ReplicationArticle Options for Transactional Replication