Partager via

Articles transactionnels - Régénérer des procédures personnalisées pour refléter les modifications de schéma

S’applique à :SQL ServerAzure SQL Managed Instance

Par défaut, la réplication transactionnelle effectue toutes les modifications de données sur les abonnés via des procédures stockées qui sont générées par des procédures internes pour chaque article de table de la publication. Les trois procédures (une pour les insertions, les mises à jour et les suppressions) sont copiées sur l’Abonné et exécutées lorsqu’une insertion, une mise à jour ou une suppression est répliquée sur l’Abonné.

Lorsqu’une modification de schéma est apportée à une table sur un serveur de publication SQL Server, la réplication régénère automatiquement ces procédures en appelant le même ensemble de procédures de script internes afin que les nouvelles procédures correspondent au nouveau schéma (la réplication des modifications de schéma n’est pas prise en charge pour les serveurs de publication Oracle).

Vous pouvez également spécifier des procédures personnalisées pour remplacer une ou plusieurs des procédures par défaut. Les procédures personnalisées doivent être modifiées si la modification du schéma affecte la procédure. Par exemple, si une procédure référence une colonne qui est supprimée dans une modification de schéma, les références à la colonne doivent être supprimées de la procédure.

Il y a deux façons pour la réplication de propager une nouvelle procédure personnalisée vers les Abonnés :

  • Utiliser une procédure de script personnalisée pour remplacer les valeurs par défaut utilisées par la réplication
  • Utiliser un script qui contient une nouvelle définition de procédure personnalisée

Utiliser une procédure de script personnalisée pour remplacer les valeurs par défaut utilisées par la réplication

Lorsque vous exécutez sp_addarticle, vérifiez que le bit @schema_option0x02 est à true.

Remarque

La définition de procédure stockée personnalisée doit être scriptée à l’aide de Transact-SQL dynamique, à l’intérieur d’une procédure stockée wrapper. Cette procédure stockée d'enchâssement doit également inclure un paramètre @artid de type int pour s'assurer qu'elle est créée sur l'abonné.

Exécutez sp_register_custom_scripting et spécifiez la valeur insert, updateou delete pour le paramètre @type , ainsi que le nom de la procédure de script personnalisée pour le paramètre @value .

La prochaine fois qu'une modification de schéma est effectuée, la réplication appelle cette procédure stockée pour générer le script de la définition de la nouvelle procédure stockée personnalisée définie par l'utilisateur, puis propage la procédure à chaque Abonné.

Exemple :

Dans cet exemple, supposons que le Publisher et le Subscriber sont déjà configurés et que vous souhaitez créer une procédure stockée personnalisée delete.

  1. Dans le composant Abonné, créez une table pour présenter le script de suppression personnalisé.

    USE [SubscriberDB];
GO

CREATE TABLE DeleteLogging (id INT PRIMARY KEY);
GO

  2. Ajoutez l'article depuis l'éditeur. Notez les valeurs des @schema_optionparamètres : @ins_cmd@upd_cmd@del_cmd

    USE [PublisherDB];

EXECUTE sp_addarticle
    @publication = N'PubName1',
    @article = N'Purchases',
    @source_owner = N'dbo',
    @source_object = N'Purchases',
    @type = N'logbased',
    @description = NULL,
    @creation_script = NULL,
    @pre_creation_cmd = N'drop',
    @schema_option = 0x000000000803509F,
    @identityrangemanagementoption = N'manual',
    @destination_table = N'Purchases',
    @destination_owner = N'dbo',
    @vertical_partition = N'false',
    @ins_cmd = N'CALL sp_MSins_dboPurchases',  -- default
    @del_cmd = N'CALL custom_delete',          -- custom
    @upd_cmd = N'SCALL sp_MSupd_dboPurchases'; -- default
GO

  3. Créez une procédure stockée qui génère le script de la procédure stockée custom_delete que vous souhaitez utiliser sur l’abonné. Il s’agit de la procédure stockée enveloppe, comme indiqué précédemment.

    Le renvoi de valeurs non nulles à partir de cette procédure stockée entraîne que custom_delete n’est pas créé sur l’abonné. La propriété SELECT doit retourner la définition complète CREATE de la procédure stockée qui sera utilisée sur l’abonné.

    Notez l’utilisation du paramètre requis @artid .

    USE [PublisherDB];
GO

CREATE OR ALTER PROCEDURE script_custom_delete (@artid INT)
AS
BEGIN
    SELECT 'CREATE OR ALTER PROCEDURE custom_delete
              @pkc1 INT
          AS
          BEGIN
              INSERT INTO DeleteLogging (id) VALUES (@pkc1)
          END';
    RETURN 0;
END
GO

  4. Inscrivez le script personnalisé sur le serveur de publication.

    USE [PublisherDB];
GO

EXECUTE sp_register_custom_scripting
    @type = 'delete',
    @value = 'script_custom_delete',
    @publication = 'PubName1',
    @article = 'Purchases';
GO

  5. Ajoutez un abonnement. Dans cet exemple, le paramètre @sync_type est défini sur replication support only, donc aucun instantané n’est utilisé.

    USE [PublisherDB];
GO

EXECUTE sp_addsubscription
    @publication = N'PubName1',
    @subscriber = @@SERVERNAME,
    @destination_db = N'SubscriberDB',
    @subscription_type = N'Push',
    @sync_type = N'replication support only',
    @article = N'all',
    @update_mode = N'read only',
    @subscriber_type = 0;
GO

Utiliser un script qui contient une nouvelle définition de procédure personnalisée

Lorsque vous exécutez sp_addarticle, définissez le bit @schema_option0x02 sur false pour que la réplication ne génère pas automatiquement de procédures personnalisées sur l’abonné.

Avant chaque modification de schéma, créez un fichier de script et inscrivez le script avec la réplication en exécutant sp_register_custom_scripting. Spécifiez une valeur pour le paramètre custom_script@type, ainsi que le chemin d'accès au script sur le Publisher pour le paramètre @value.

La prochaine fois qu'une modification de schéma pertinente est effectuée, ce script s'exécutera sur chaque Abonné au sein de la même transaction que la commande DDL. Quand la modification de schéma est effectuée, le script est désinscrit. Vous devez réinscrire le script pour qu’il s’exécute après une modification de schéma ultérieure.

Contenu connexe