Transaktionsartikel – Festlegen, wie Änderungen weitergegeben werden

Gilt für:SQL ServerAzure SQL Managed Instance

Bei der Transaktionsreplikation können Sie angeben, wie Datenänderungen vom Verleger an den Abonnenten weitergegeben werden. Für jede veröffentlichte Tabelle können Sie eine von vier Möglichkeiten angeben, wie jeder Vorgang (INSERT, UPDATEoder DELETE) an den Abonnenten weitergegeben werden soll:

• Geben Sie an, dass die Transaktionsreplikation eine gespeicherte Prozedur als Skript erstellen und anschließend aufrufen soll, um Änderungen an die Abonnenten weiterzugeben (Standardeinstellung).

• Geben Sie an, dass die Änderung mithilfe einer INSERT-, UPDATE- oder DELETE-Anweisung weitergegeben werden soll (Standard für Nicht-SQL-Server-Abonnenten).

• Angeben, dass eine benutzerdefinierte gespeicherte Prozedur verwendet wird.

• Angeben, dass diese Aktion auf keinem Abonnenten ausgeführt wird. Transaktionen dieses Typs werden nicht repliziert.

Standardmäßig gibt die Transaktionsreplikation Änderungen an Abonnenten mithilfe einer Reihe gespeicherter Prozeduren weiter, die auf jedem Abonnenten gespeichert sind. Wenn beim Publisher ein Einfüge-, Aktualisierungs- oder Löschvorgang in einer Tabelle erfolgt, wird der Vorgang in einen Aufruf einer gespeicherten Prozedur beim Subscriber umgesetzt. Die gespeicherte Prozedur nimmt Parameter an, die den Spalten der Tabelle entsprechen, sodass diese Spalten beim Subscriber geändert werden können.

Informationen zum Festlegen der Propagierungsmethode für Datenänderungen an Transaktionsartikeln finden Sie unter Festlegen der Propagierungsmethode für Datenänderungen an Transaktionsartikeln.

Standardmäßige und benutzerdefinierte gespeicherte Prozeduren

Die folgenden drei Prozeduren werden von der Replikation standardmäßig für jeden Tabellenartikel erstellt:

• sp_MSins_<Tabellenname>, das INSERT-Vorgänge verarbeitet.

• sp_MSupd_<Tabellenname>, die für Updates zuständig ist.

• sp_MSdel_<Tabellenname>, das Löschvorgänge behandelt.

Der in der Prozedur verwendete <Tabellenname> hängt davon ab, wie der Artikel der Veröffentlichung hinzugefügt wurde und ob die Abonnementdatenbank eine Tabelle mit demselben Namen und einem anderen Besitzer enthält.

Jede dieser Prozeduren kann durch eine benutzerdefinierte Prozedur ersetzt werden, die Sie beim Hinzufügen eines Artikels zur Veröffentlichung angeben. Benutzerdefinierte Prozeduren werden verwendet, wenn eine Anwendung benutzerdefinierte Logik erfordert, beispielsweise das Einfügen von Daten in eine Audit-Tabelle, wenn eine Zeile bei einem Abonnenten aktualisiert wird. Weitere Informationen zum Angeben von benutzerdefinierten gespeicherten Prozeduren finden Sie in den oben aufgeführten Themen.

Wenn Sie die Standardreplikationsprozeduren oder benutzerdefinierten Prozeduren angeben, geben Sie auch eine Aufrufsyntax für jede Prozedur an (bei Verwendung von Standardprozeduren werden diese Prozeduren von der Replikation ausgewählt). Die Aufrufsyntax legt die Struktur der für die Prozedur bereitgestellten Parameter fest und welche Informationen bei jeder Datenänderung an den Abonnenten gesendet werden. Weitere Informationen finden Sie im Abschnitt zur Aufrufsyntax für gespeicherte Prozeduren in diesem Thema.

Überlegungen zum Verwenden benutzerdefinierter gespeicherter Prozeduren

Berücksichtigen Sie bei der Verwendung benutzerdefinierter gespeicherter Prozeduren die folgenden Überlegungen:

• Sie müssen den Support für die Logik der gespeicherten Prozedur selbst übernehmen. Microsoft stellt für benutzerdefinierte Logik keinen Support bereit.

• Um Konflikte mit den von der Replikation verwendeten Transaktionen zu vermeiden, sollten in benutzerdefinierten Prozeduren keine expliziten Transaktionen verwendet werden.

• Das Schema beim Abonnenten ist in der Regel identisch mit dem Schema beim Publisher, kann jedoch auch eine Teilmenge des Schemas des Publishers sein, wenn Spaltenfilterung verwendet wird. Wenn Sie jedoch das Schema während der Datenverschiebung so umwandeln müssen, dass das Schema beim Abonnenten keine Teilmenge des Schemas beim Publisher ist, ist SQL Server 2019 Integration Services (SSIS) die empfohlene Lösung. Weitere Informationen finden Sie unter SQL Server Integration Services.

• Wenn Sie Schemaänderungen an einer veröffentlichten Tabelle vornehmen, müssen die benutzerdefinierten Prozeduren neu generiert werden. Weitere Informationen finden Sie unter Erneutes Generieren von Transaktionsprozeduren zur Erfassung von Schemaänderungen.

• Wenn Sie einen höheren Wert als 1 für den -SubscriptionStreams -Parameter des Verteilungs-Agent verwenden, müssen Sie sicherstellen, dass Updates an der Primärschlüsselspalte erfolgreich sind. Zum Beispiel:

  update ... set pk = 2 where pk = 1 -- update 1  
  update ... set pk = 3 where pk = 2 -- update 2  
  

  Wenn der Verteilungs-Agent mehrere Verbindungen verwendet, werden diese beiden Updates gegebenenfalls über verschiedene Verbindungen repliziert. Wird Update 1 zuerst angewendet, gibt es kein Problem. Wird Update 2 zuerst angewendet, wird '0 Zeilen betroffen' zurückgegeben, da Update 1 noch nicht stattgefunden hat. Diese Situation wird von den Standardprozeduren beantwortet. Dabei wird ein Fehler ausgelöst, wenn von einem Update keine Zeilen betroffen sind:

  if @@rowcount = 0  
      if @@microsoftversion>0x07320000  
          exec sys.sp_MSreplraiserror 20598  
  

  Das Auslösen dieses Fehlers zwingt den Verteilungs-Agenten, die Aktualisierungen über eine einzelne Verbindung erneut auszuführen, was erfolgreich sein wird. Benutzerdefinierte gespeicherte Prozeduren müssen eine ähnliche Logik einschließen.

Aufrufsyntax für gespeicherte Prozeduren

Es gibt fünf Optionen für die Syntax, mit der die von der Transaktionsreplikation verwendeten Prozeduren aufgerufen werden:

• CALL-Syntax. Diese Syntax kann für Einfügungen, Updates und Löschungen verwendet werden. Die Replikation verwendet diese Syntax standardmäßig für Einfügungen und Löschungen.

• SCALL-Syntax. Kann nur für Aktualisierungen verwendet werden. Die Replikation verwendet diese Syntax standardmäßig für Updates.

• MCALL-Syntax. Kann nur für Aktualisierungen verwendet werden.

• XCALL-Syntax. Kann für Aktualisierungen und Löschvorgänge verwendet werden.

• VCALL. Wird für aktualisierbare Abonnements verwendet. Nur zur internen Verwendung.

Die einzelnen Methoden unterscheiden sich in der Datenmenge, die an den Abonnenten weitergegeben wird. SCALL gibt z. B. Werte nur für die Spalten weiter, die tatsächlich von einem Update betroffen sind. XCALL dagegen erfordert alle Spalten (unabhängig davon, ob sie von einem Update betroffen sind) und alle alten Datenwerte für jede Spalte. In vielen Fällen eignet sich SCALL für Updates. Erfordert die Anwendung jedoch alle Datenwerte bei einem Update, empfiehlt sich die Verwendung von XCALL.

CALL-Syntax

INSERT Gespeicherte Prozeduren
Gespeicherten Prozeduren, die INSERT-Anweisungen verarbeiten, werden die eingefügten Werte für alle Spalten übergeben:

c1, c2, c3,... cn  

UPDATE Gespeicherte Prozeduren
Gespeicherte Prozeduren, die UPDATE-Anweisungen verarbeiten, erhalten die aktualisierten Werte für alle im Artikel definierten Spalten, gefolgt von den ursprünglichen Werten für die Primärschlüsselspalten (wobei nicht versucht wird festzustellen, welche Spalten geändert wurden.):

c1, c2, c3,... cn, pkc1, pkc2, pkc3,... pkcn  

DELETE Gespeicherte Prozeduren
Gespeicherte Prozeduren, die DELETE-Anweisungen verarbeiten, werden Werte für die Primärschlüsselspalten übergeben:

pkc1, pkc2, pkc3,... pkcn  

SCALL-Syntax

UPDATE Gespeicherte Prozeduren
Gespeicherten Prozeduren, die UPDATE-Anweisungen verarbeiten, werden nur die aktualisierten Werte der geänderten Spalten übergeben, gefolgt von den ursprünglichen Werten der Primärschlüsselspalten und anschließend von einem Bitmaskenparameter (binary(n)), der die geänderten Spalten kennzeichnet. Im folgenden Beispiel wurde die Spalte 2 (c2) nicht geändert:

c1, , c3,... cn, pkc1, pkc2, pkc3,... pkcn, bitmask  

MCALL-Syntax

UPDATE Gespeicherte Prozeduren
Gespeicherten Prozeduren, die UPDATE-Anweisungen verarbeiten, werden die aktualisierten Werte für alle im Artikel definierten Spalten übergeben, gefolgt von den ursprünglichen Werten für die Primärschlüsselspalten und anschließend von einem Bitmaskenparameter (binary(n)), der die geänderten Spalten angibt:

c1, c2, c3,... cn, pkc1, pkc2, pkc3,... pkcn, bitmask  

XCALL Syntax

UPDATE Gespeicherte Prozeduren
Gespeicherte Prozeduren, die UPDATE-Anweisungen verarbeiten, werden die ursprünglichen Werte (die Vorversion) für alle im Artikel definierten Spalten übergeben, gefolgt von den aktualisierten Werten (die Nachversion) für alle im Artikel definierten Spalten:

old-c1, old-c2, old-c3,... old-cn, c1, c2, c3,... cn,  

DELETE Gespeicherte Prozeduren
Stored Procedures, die DELETE-Anweisungen verarbeiten, erhalten für alle im Artikel definierten Spalten die ursprünglichen Werte (das Vorabbild):

old-c1, old-c2, old-c3,... old-cn  

Beispiele

Bei den folgenden Prozeduren handelt es sich um Standardprozeduren, die für die Vendor Table in der Adventure Works-Beispieldatenbank erstellt wurden.

--INSERT procedure using CALL syntax  
create procedure [sp_MSins_PurchasingVendor]   
  @c1 int,@c2 nvarchar(15),@c3 nvarchar(50),@c4 tinyint,@c5 bit,@c6 bit,@c7 nvarchar(1024),@c8 datetime  
as   
begin   
insert into [Purchasing].[Vendor]([VendorID]  
,[AccountNumber]  
,[Name]  
,[CreditRating]  
,[PreferredVendorStatus]  
,[ActiveFlag]  
,[PurchasingWebServiceURL]  
,[ModifiedDate])  
values (   
 @c1  
,@c2  
,@c3  
,@c4  
,@c5  
,@c6  
,@c7  
,@c8  
 )   
end  
go  
  
--UPDATE procedure using SCALL syntax  
create procedure [sp_MSupd_PurchasingVendor]   
 @c1 int = null,@c2 nvarchar(15) = null,@c3 nvarchar(50) = null,@c4 tinyint = null,@c5 bit = null,@c6 bit = null,@c7 nvarchar(1024) = null,@c8 datetime = null,@pkc1 int  
,@bitmap binary(2)  
as  
begin  
update [Purchasing].[Vendor] set   
 [AccountNumber] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [AccountNumber] end  
,[Name] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [Name] end  
,[CreditRating] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [CreditRating] end  
,[PreferredVendorStatus] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [PreferredVendorStatus] end  
,[ActiveFlag] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [ActiveFlag] end  
,[PurchasingWebServiceURL] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [PurchasingWebServiceURL] end  
,[ModifiedDate] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [ModifiedDate] end  
where [VendorID] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end  
go  
  
--DELETE procedure using CALL syntax  
create procedure [sp_MSdel_PurchasingVendor]   
  @pkc1 int  
as   
begin   
delete [Purchasing].[Vendor]  
where [VendorID] = @pkc1  
if @@rowcount = 0  
    if @@microsoftversion>0x07320000  
        exec sp_MSreplraiserror 20598  
end   
go  

Zugehöriger Inhalt

• Artikeloptionen für die Transaktionsreplikation