Artículos transaccionales: especificar cómo se propagan los cambios

Se aplica a:SQL ServerAzure SQL Managed Instance

La replicación transaccional permite especificar cómo se propagan los cambios de datos del publicador a los suscriptores. Para cada tabla publicada, puede especificar una de las cuatro maneras en que cada operación (INSERT, UPDATEo DELETE) se debe propagar al suscriptor:

• Especifique que la replicación transaccional debe generar scripts y, posteriormente, llamar a un procedimiento almacenado para propagar los cambios a los suscriptores (el valor predeterminado).

• Especifique que el cambio se debe propagar mediante una instrucción INSERT, UPDATE o DELETE (el valor predeterminado para suscriptores que no sean de SQL Server).

• Especifique que debe utilizarse un procedimiento almacenado personalizado.

• Especifique que esta acción no debe realizarse en ningún suscriptor. Las transacciones de este tipo no se replican.

De forma predeterminada, la replicación transaccional propaga los cambios a los suscriptores a través de una serie de procedimientos almacenados que se instalan en cada suscriptor. Cuando se produce una inserción, una actualización o una eliminación en una tabla del publicador, la operación se convierte en una llamada a un procedimiento almacenado en el suscriptor. El procedimiento almacenado acepta parámetros que se corresponden con las columnas de la tabla, lo que permite modificar los valores de esas columnas en el suscriptor.

Para establecer el método de propagación para el cambio de datos en artículos transaccionales, vea Establecer el método de propagación para cambios de datos en artículos transaccionales.

Procedimientos almacenados predeterminados y personalizados

La replicación crea tres procedimientos almacenados por defecto para cada artículo de tabla:

• sp_MSins_<nombreDeTabla>, que controla las inserciones.

• sp_MSupd_<nombreDeTabla>, que controla las actualizaciones.

• sp_MSdel_<tablename>, que controla las operaciones de eliminación.

El nombre > de <la tabla utilizado en el procedimiento depende de cómo se añada el artículo a la publicación y de si la base de datos de suscripción contiene una tabla con el mismo nombre pero con un propietario diferente.

Puedes sustituir cualquiera de estos procedimientos por uno personalizado que especifiques al añadir un artículo a una publicación. Utiliza procedimientos personalizados si tu aplicación requiere lógica personalizada, como insertar datos en una tabla de auditoría cuando una fila se actualiza en un Suscriptor. Para más información sobre cómo especificar procedimientos almacenados personalizados, consulte los artículos de instrucciones listados en la sección anterior.

Cuando especificas los procedimientos de replicación por defecto o procedimientos personalizados, también especificas la sintaxis de llamadas para cada procedimiento. La replicación selecciona la sintaxis predeterminada de las llamadas si usas los procedimientos por defecto. La sintaxis de llamada determina la estructura de los parámetros proporcionados al procedimiento y la cantidad de información que se envía al suscriptor con cada cambio de datos. Para más información, consulte la sección "Sintaxis de llamada para procedimientos almacenados" en este artículo.

Consideraciones para el uso de procedimientos almacenados personalizados

Tenga en cuenta los siguientes aspectos cuando utilice procedimientos almacenados personalizados:

• Debes soportar la lógica en el procedimiento almacenado; Microsoft no ofrece soporte para lógica personalizada.

• Para evitar conflictos con las transacciones utilizadas por la replicación, no utilices transacciones explícitas en procedimientos personalizados.

• El esquema en el Suscriptor suele ser idéntico al esquema del Publisher, pero también puede ser un subconjunto del esquema del Publisher si se utiliza filtrado por columnas. Si necesitas transformar el esquema a medida que los datos se mueven para que el esquema en el Suscriptor no sea un subconjunto del esquema en el Publisher, usa SQL Server 2019 Integration Services (SSIS). Para más información, vea SQL Server Integration Services.

• Si haces cambios en el esquema de una tabla publicada, regenera los procedimientos personalizados. Para más información, vea Volver a generar procedimientos transaccionales personalizados para reflejar cambios de esquema.

• Si utilizas un valor mayor que 1 para el parámetro -SubscriptionStreams del Agente de distribución, asegúrate de que las actualizaciones en las columnas clave primarias tengan éxito. Por ejemplo:

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

  Si el Agente de distribución utiliza más de una conexión, estas dos actualizaciones podrían replicarse a través de conexiones diferentes. Si se aplica primero la actualización 1, no hay problema. Si se aplica primero la actualización 2, vuelve 0 rows affected porque la actualización 1 aún no se ha realizado. Los procedimientos por defecto gestionan esta situación generando un error si ninguna fila se ve afectada en una actualización:

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

  Provocar el error obliga al Agente de distribución a volver a intentar las actualizaciones mediante una única conexión, lo que da resultado. Los procedimientos almacenados personalizados deben incluir una lógica similar.

Sintaxis de llamada para procedimientos almacenados

Puedes usar cinco opciones de sintaxis diferentes para llamar a los procedimientos que utiliza la replicación transaccional:

• Sintaxis de CALL. Utiliza esta sintaxis para insertos, actualizaciones y eliminaciones. De forma predeterminada, la replicación utiliza esta sintaxis para las inserciones y las eliminaciones.

• Sintaxis de SCALL. Utiliza esta sintaxis solo para actualizaciones. De forma predeterminada, la replicación utiliza esta sintaxis para las actualizaciones.

• Sintaxis MCALL Utiliza esta sintaxis solo para actualizaciones.

• La sintaxis de XCALL. Utiliza esta sintaxis para actualizaciones y eliminaciones.

• VCALL. Utiliza esta sintaxis para suscripciones actualizables. Solo para uso interno.

Cada método difiere en la cantidad de datos que envía al Suscriptor. Por ejemplo, SCALL solo pasa valores para las columnas que una actualización realmente afecta. XCALL requiere todas las columnas, ya sea que una actualización las afecte o no, y todos los valores de datos antiguos de cada columna. En muchos casos, SCALL es adecuado para actualizaciones, pero si tu aplicación requiere todos los valores de datos durante una actualización, XCALL soporta esta necesidad.

Sintaxis de CALL

INSERT procedimientos almacenados
Los procedimientos almacenados que gestionan INSERT sentencias reciben los valores insertados para todas las columnas:

c1, c2, c3,... cn  

UPDATE procedimientos almacenados
Los procedimientos almacenados que gestionan UPDATE sentencias reciben los valores actualizados de todas las columnas definidas en el artículo, seguidos de los valores originales de las columnas clave primarias. El proceso no intenta determinar qué columnas cambiaron:

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

DELETE procedimientos almacenados
Los procedimientos almacenados que gestionan DELETE sentencias reciben valores para las columnas clave primarias:

pkc1, pkc2, pkc3,... pkcn  

Sintaxis de SCALL

UPDATE procedimientos almacenados
Los procedimientos almacenados que gestionan UPDATE sentencias reciben los valores actualizados solo para aquellas columnas que han cambiado, seguidos de los valores originales para las columnas clave primarias, y luego un parámetro de máscara de bits (binary(n)) que indica las columnas cambiadas. En el siguiente ejemplo, la columna 2 (c2) no cambió:

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

Sintaxis MCALL

UPDATE procedimientos almacenados
Los procedimientos almacenados que gestionan UPDATE sentencias reciben los valores actualizados para todas las columnas definidas en el artículo, seguidos de los valores originales para las columnas clave primarias, y luego un parámetro de máscara de bits (binary(n)) que indica las columnas modificadas:

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

Sintaxis XCALL

UPDATE procedimientos almacenados
Los procedimientos almacenados que gestionan UPDATE sentencias reciben los valores originales (la imagen anterior) para todas las columnas definidas en el artículo, seguidos de los valores actualizados (la imagen posterior) para todas las columnas definidas en el artículo:

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

DELETE procedimientos almacenados
Los procedimientos almacenados que gestionan DELETE sentencias reciben los valores originales (la imagen anterior) para todas las columnas definidas en el artículo:

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

Ejemplos

A continuación se indican los procedimientos predeterminados creados por la Vendor Table en la base de datos de ejemplo de Adventure Works.

--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  

Contenido relacionado

• Opciones de artículo para la replicación transaccional