Partager via

Procédure : utiliser un système de suivi des modifications personnalisé

  • Article

De nombreuses applications exigent que les modifications fassent l'objet d'un suivi dans la base de données serveur afin que ces modifications puissent être remises aux clients lors d'une session de synchronisation ultérieure. Cette rubrique décrit la configuration requise pour un système de suivi des modifications et vous indique comment créer un système personnalisé que Sync Framework peut utiliser. Dans certains cas, le suivi des modifications personnalisé est approprié. Toutefois, gardez à l'esprit qu'il présente une certaine complexité et peut affecter les performances de la base de données serveur. Si vous utilisez SQL Server 2008, il est conseillé d'utiliser la fonctionnalité de suivi des modifications de SQL Server. Pour plus d'informations, consultez Procédure : utiliser le suivi des modifications de SQL Server.

Spécifications du serveur pour les scénarios de synchronisation

Sync Framework est conçu pour avoir une incidence minimale sur la base de données serveur. Par conséquent, les modifications qui sont requises pour le suivi des modifications dans la base de données serveur sont proportionnelles au niveau de fonctionnalités souhaité dans une application. Gardez à l'esprit les points suivants :

  • Au minimum, il s'agit d'un instantané par téléchargement uniquement des données (aucune modification n'est requise).

  • Au maximum, il s'agit d'effectuer une synchronisation bidirectionnelle avec un suivi des modifications et une détection de conflit.

Le tableau suivant récapitule les différents modes d'utilisation de Sync Framework et identifie les spécifications correspondantes pour la base de données serveur.

Scénario

Clé primaire ou colonne unique 1

Suivi de l'heure de la mise à jour

Suivi de l'heure de l'insertion

Suivi de l'heure de la suppression

Suivi de l'ID client pour les mises à jour

Suivi de l'ID client pour les insertions

Suivi de l'ID client pour les suppressions

Téléchargement d'un instantané des données sur le client.

Non

Non

Non

Non

Non

Non

Non

Téléchargement des insertions et mises à jour incrémentielles sur le client.

Oui

Oui

Oui2

Non

Non

Non

Non

Téléchargement des insertions, mises à jour et suppressions incrémentielles sur le client.

Oui

Oui

Oui2

Oui

Non

Non

Non

Téléchargement des insertions sur le serveur.

Oui

Non

Non

Non

Non

Non3

Non

Téléchargement des insertions et mises à jour sur le serveur.

Oui

Non

Non

Non

Non3

Non3

Non

Téléchargement des insertions, mises à jour et suppressions sur le serveur.

Oui

Non

Non

Non

Non3

Non3

Non3

Insertions et mises à jour bidirectionnelles avec détection de conflit.

Oui

Oui

Oui2

Non

Oui4

Oui4

Non

Insertions, mises à jour et suppressions bidirectionnelles avec détection de conflit.

Oui

Oui

Oui2

Oui

Oui4

Oui4

Oui4

1 Les clés primaires doivent être uniques sur tous les nœuds et ne doivent pas être réutilisées : si une ligne est supprimée, la clé primaire de cette ligne ne doit pas être utilisée pour une autre ligne. Les colonnes d'identité ne sont généralement pas appropriées à des environnements distribués. Pour plus d'informations sur les clés primaires, consultez Sélection d'une clé primaire appropriée pour un environnement distribué.

2 Requis si vous voulez faire la distinction entre les insertions et les mises à jour. Pour plus d'informations, consultez la section « Identification des modifications de données à télécharger sur un client » ci-après dans cette rubrique.

3 Requis si plusieurs clients sont susceptibles de modifier une ligne et que vous voulez identifier le client ayant effectué la modification. Pour plus d'informations, consultez la section « Identification du client ayant effectué une modification de données » dans cette rubrique.

4 Requis si vous ne voulez pas répercuter les modifications sur le client qui les a effectuées. Pour plus d'informations, consultez la section « Identification du client ayant effectué une modification de données » dans cette rubrique.

Notes

En complément des modifications décrites ci-avant, vous créerez probablement des procédures stockées pour l'accès aux données. La plupart des exemples de cette documentation utilisent SQL Inline, car il est plus facile d'indiquer ce qui se produit dans le code. Dans les applications de production, les procédures stockées doivent être utilisées pour les raisons suivantes : elles encapsulent le code, elles sont généralement plus efficaces et peuvent assurer une sécurité renforcée par rapport à SQL Inline si elles sont écrites correctement.

Identification des modifications de données à télécharger sur un client

Pour la synchronisation par téléchargement uniquement et la synchronisation bidirectionnelle, vous devez suivre les modifications sur le serveur afin que Sync Framework puisse déterminer les modifications à télécharger sur les clients. Bien que Sync Framework ne définisse pas précisément comment gérer le suivi des modifications, il existe une méthode courante. Pour chaque table à synchroniser, vous pouvez procéder comme suit :

  • Ajoutez une colonne qui identifie la date d'insertion d'une ligne dans la base de données serveur.

  • Ajoutez une colonne (et dans certains cas, un déclencheur) qui identifie la date de dernière mise à jour d'une ligne de la base de données serveur.

  • Ajoutez une table tombstone et un déclencheur qui identifient la date de suppression d'une ligne de la base de données serveur. Si vous ne voulez pas supprimer les données du serveur mais que vous devez envoyer les suppressions au client, il est possible de suivre les suppressions logiques dans la table de base : utilisez une colonne (généralement de type bit) pour indiquer qu'une ligne est supprimée, et une autre colonne pour indiquer la date de la suppression.

Ces colonnes et ces tables tombstone sont utilisées avec des ancres pour déterminer les insertions, les mises à jour et les suppressions devant être téléchargées. Une ancre est simplement une limite dans le temps qui permet de définir un ensemble de modifications à synchroniser. Prenons les requêtes suivantes :

  • Requête que vous spécifiez pour la propriété SelectIncrementalInsertsCommand. Cette requête télécharge les insertions incrémentielles à partir de la table Sales.Customer de l'exemple de base de données Sync Framework, comme suit :

    SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor
AND InsertTimestamp <= @sync_new_received_anchor

    Pour plus d'informations sur cette propriété et sur les propriétés associées aux commandes de synchronisation, consultez Procédure : spécifier la synchronisation par instantané, par téléchargement, par téléchargement ascendant et bidirectionnelle.

  • Requête que vous spécifiez pour la propriété SelectNewAnchorCommand. Cette requête extrait une valeur jusqu'à une date et heure. La colonne InsertTimestamp stocke les valeurs d'horodatage. Par conséquent, la requête utilise la fonction Transact-SQLMIN_ACTIVE_ROWVERSION, introduite dans SQL Server 2005 Service Pack 2, pour extraire une valeur d'horodatage à partir de la base de données serveur, comme suit :

    SELECT @sync_new_received_anchor = MIN_ACTIVE_ROWVERSION - 1

    MIN_ACTIVE_ROWVERSION retourne la valeur timestamp active la plus faible (également appelée rowversion) dans la base de données actuelle. Une valeur timestamp est active si elle est utilisée dans une transaction qui n'a pas encore été validée. Si la base de données ne contient aucune valeur active, MIN_ACTIVE_ROWVERSION retourne la même valeur que @@DBTS + 1. La fonction MIN_ACTIVE_ROWVERSION est utile dans les scénarios, tels que la synchronisation des données, qui utilisent des valeurs timestamp pour regrouper des ensembles de modifications. Si une application utilise @@DBTS dans ses commandes d'ancre au lieu de MIN_ACTIVE_ROWVERSION, il est possible que des modifications actives au moment de la synchronisation ne soient pas détectées.

Lors de la première synchronisation de la table Sales.Customer, le processus suivant se produit.

  1. La nouvelle commande d'ancre est exécutée. La commande retourne la valeur 0x0000000000000D49. Cette valeur est stockée dans la base de données client. La table n'a jamais été synchronisée. Par conséquent, aucune valeur d'ancre n'a été stockée dans la base de données client à partir d'une synchronisation antérieure. Dans ce cas, Sync Framework utilise la valeur la plus faible qui est disponible pour le type de données SQL Servertimestamp : 0x0000000000000000. La requête qui est exécutée par Sync Framework est la suivante. Cette requête télécharge le schéma et toutes les lignes de la table.

    exec sp_executesql N'SELECT CustomerId, CustomerName, SalesPerson,
CustomerType FROM Sales.Customer WHERE (InsertTimestamp >
@sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)',N'@sync_last_received_anchor timestamp,
@sync_new_received_anchor timestamp',
@sync_last_received_anchor=0x0000000000000000,
@sync_new_received_anchor=0x0000000000000D49

  2. Au cours de la deuxième synchronisation, la nouvelle commande d'ancre est exécutée. Des lignes ont été insérées depuis la dernière synchronisation. Par conséquent, la commande retourne la valeur 0x0000000000000D4C. La table a déjà été synchronisée. Sync Framework peut donc récupérer la valeur d'ancre 0x0000000000000D49. Cette valeur a été stockée dans la base de données client lors de la synchronisation précédente. La requête qui est exécutée est la suivante. La requête télécharge uniquement les lignes de la table qui ont été insérées entre les deux valeurs d'ancre.

    exec sp_executesql N'SELECT CustomerId, CustomerName, SalesPerson,
CustomerType FROM Sales.Customer WHERE (InsertTimestamp >
@sync_last_received_anchor AND InsertTimestamp <=
@sync_new_received_anchor)', N'@sync_last_received_anchor timestamp,
@sync_new_received_anchor timestamp',
@sync_last_received_anchor=0x0000000000000D49,
@sync_new_received_anchor=0x0000000000000D4C

Pour obtenir des exemples de commandes de mise à jour et de suppression, consultez Procédure : télécharger des modifications de données incrémentielles sur un client et Procédure : échanger des modifications de données incrémentielles bidirectionnelles entre un client et un serveur.

Comme nous l'avons indiqué, la commande utilisée pour extraire les valeurs d'ancre dépend du type de données des colonnes de suivi dans la base de données serveur. Les exemples présentés dans cette documentation utilisent la valeur SQL Servertimestamp, également appelée rowversion. Si une colonne SQL Serverdatetime est utilisée, la requête de la nouvelle commande d'ancre est la suivante :

SELECT @sync_new_received_anchor = GETUTCDATE()

Pour sélectionner le type de données à utiliser comme ancre, vous devez évaluer les spécifications de l'application et déterminer si vous pouvez facilement modifier le schéma de la base de données serveur. Si la base de données est en cours de création, vous pouvez spécifier précisément les colonnes et les déclencheurs à ajouter. En revanche, dans le cas d'une base de données de production, vos options peuvent être plus limitées. Gardez en mémoire les consignes suivantes :

  • Toutes les tables d'un groupe de synchronisation doivent utiliser le même type de données et la même nouvelle commande d'ancre. Si possible, utilisez le même type de données et la même commande pour tous les groupes.

  • Le type de données datetime est facile à comprendre, et les tables comportent souvent une colonne qui suit la date de modification d'une ligne. Toutefois, ce type de données peut poser problème si les clients n'appartiennent pas au même fuseau horaire. Si vous utilisez ce type de données, il est possible que des transactions soient omises lorsque des modifications incrémentielles sont sélectionnées.

  • Le type de données timestamp est précis et ne dépend pas de l'heure de l'horloge. Toutefois, chaque table d'une base de données SQL Server peut contenir une seule colonne de ce type de données. Par conséquent, si vous devez faire la distinction entre les insertions et les mises à jour, vous pouvez ajouter une colonne d'un type de données différent (tel que binary(8)) et stocker les valeurs d'horodatage dans cette colonne. Pour obtenir un exemple, consultez Scripts d'installation pour les rubriques de procédures sur le fournisseur de bases de données. Le type de données timestamp peut poser problème si la base de données serveur est restaurée à partir d'une sauvegarde. Pour plus d'informations, consultez Sauvegarde et restauration d'une base de données. Comme indiqué précédemment, il est conseillé d'utiliser MIN_ACTIVE_ROWVERSION dans la commande qui sélectionne une nouvelle ancre.

Identification du client ayant effectué une modification de données

Il est important d'identifier le client qui a modifié des données, et ce pour deux raisons :

  • Pour prendre en charge la détection et la résolution de conflits lors de la synchronisation par téléchargement ascendant uniquement et de la synchronisation bidirectionnelle.

    Si le serveur et le client, ou plusieurs clients, ont la possibilité de modifier une ligne spécifique, il peut être utile d'identifier la personne qui a effectué la modification. Ces informations vous permettent d'écrire du code, par exemple, qui stipule la priorité d'une modification par rapport à une autre. Sans ces informations, la dernière modification apportée à la ligne est conservée.

  • Pour éviter de répercuter les modifications sur le client lors de la synchronisation bidirectionnelle.

    Sync Framework télécharge en premier lieu les modifications sur le serveur, et ensuite sur le client. Si vous n'identifiez pas le client qui a effectué une modification, cette dernière sera téléchargée sur le serveur, puis téléchargée à nouveau sur le client lors de la même session de synchronisation. Cette répercussion des modifications est acceptable dans certains cas, mais pas dans tous les types de scénarios.

Sync Framework ne définit pas précisément comment prendre en charge le suivi de l'identité (tout comme pour le suivi des modifications) ; cependant, il existe une méthode courante. Pour chaque table à synchroniser, vous pouvez procéder comme suit :

  • Ajoutez une colonne à la table de base, qui permet d'identifier qui a effectué chaque insertion.

  • Ajoutez une colonne à la table de base, qui permet d'identifier qui a effectué chaque mise à jour.

  • Ajoutez une colonne à la table tombstone, qui permet d'identifier qui a effectué chaque suppression.

Ces colonnes et ces tables sont utilisées avec la propriété ClientId pour déterminer les clients qui ont effectué les différentes insertions, mises à jour ou suppressions. La première fois qu'une table est synchronisée à l'aide d'une méthode autre que la synchronisation par instantané, Sync Framework stocke une valeur GUID sur le client qui identifie ce client. Cet ID est transmis à l'objet DbServerSyncProvider de sorte qu'il puisse être utilisé par les requêtes de sélection et de mise à jour dans chaque objet SyncAdapter. La valeur d'ID est disponible par le biais de la propriété ClientId. Prenons la requête Transact-SQL suivante :

SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM
Sales.Customer WHERE InsertTimestamp > @sync_last_received_anchor AND
InsertTimestamp <= @sync_new_received_anchor AND InsertId <>
@sync_client_id

Cette requête est similaire à la requête précédente qui assure le suivi des insertions effectuées sur le serveur. L'instruction dans la clause WHERE garantit que seules les insertions qui n'ont pas été effectuées par le client en cours de synchronisation sont téléchargées.

Sync Framework permet également aux applications d'identifier les clients en utilisant un entier au niveau du serveur plutôt qu'une valeur GUID. Pour plus d'informations, consultez Procédure : utiliser des variables de session.

Exemples de préparation d'un serveur

Les exemples suivants indiquent comment configurer la table Sales.Customer de l'exemple de base de données Sync Framework avec l'infrastructure de suivi permettant de gérer les scénarios d'application les plus complexes : opérations de suppression, de mise à jour et d'insertion bidirectionnelles avec détection de conflits. Les scénarios moins complexes ne nécessitent pas l'infrastructure complète. Pour plus d'informations, consultez la section « Spécifications de serveur pour les scénarios de synchronisation » ci-avant dans cette rubrique. Pour obtenir un script complet qui crée les objets de cet exemple et d'autres objets, consultez Scripts d'installation pour les rubriques de procédures sur le fournisseur de bases de données. Pour plus d'informations sur l'utilisation de ces objets, consultez Procédure : spécifier la synchronisation par instantané, par téléchargement, par téléchargement ascendant et bidirectionnelle.

Les exemples de cette section réalisent les procédures suivantes lors de la préparation d'un serveur :

  1. Vérification du schéma Sales.Customer. Déterminez si la table possède une clé primaire et des colonnes susceptibles d'être utilisées pour le suivi des modifications.

  2. Ajout de colonnes pour identifier quand et où les insertions et les mises à jour sont effectuées.

  3. Création d'une table tombstone et ajout d'un déclencheur à la table Sales.Customer pour remplir la table tombstone.

Vérification du schéma Sales.Customer

L'exemple de code suivant présente le schéma de la table Sales.Customer. La table possède une clé primaire sur la colonne CustomerId et ne comporte aucune colonne susceptible d'être utilisée pour le suivi des modifications.

    CREATE TABLE SyncSamplesDb.Sales.Customer(
        CustomerId uniqueidentifier NOT NULL PRIMARY KEY DEFAULT NEWID(), 
        CustomerName nvarchar(100) NOT NULL,
        SalesPerson nvarchar(100) NOT NULL,
        CustomerType nvarchar(100) NOT NULL)

Ajout de colonnes pour assurer le suivi des opérations d'insertion et de mise à jour

L'exemple de code suivant ajoute quatre colonnes : UpdateTimestamp, InsertTimestamp, UpdateId et InsertId. La colonne UpdateTimestamp est une colonne SQL Servertimestamp. Cette colonne est automatiquement mise à jour lorsque la ligne est mise à jour. Comme il a été indiqué, une table peut comporter une seule colonne timestamp. Par conséquent, la colonne InsertTimestamp est une colonne de type binary(8) dont la valeur par défaut est @@DBTS + 1. L'exemple ajoute la valeur qui est retournée par @@DBTS afin que les colonnes UpdateTimestamp et InsertTimestamp possèdent la même valeur après une insertion. Dans le cas contraire, on pourrait croire que chaque ligne a été mise à jour après l'insertion.

L'ID que Sync Framework crée pour chaque client est un GUID ; par conséquent, les deux colonnes ID sont des colonnes uniqueidentifier. Les colonnes ont une valeur par défaut de 00000000-0000-0000-0000-000000000000. Cette valeur indique que le serveur a effectué la mise à jour ou l'insertion. Un exemple ultérieur inclut une colonne DeleteId dans la table tombstone.

    ALTER TABLE SyncSamplesDb.Sales.Customer 
        ADD UpdateTimestamp timestamp
    ALTER TABLE SyncSamplesDb.Sales.Customer 
        ADD InsertTimestamp binary(8) DEFAULT @@DBTS + 1
    ALTER TABLE SyncSamplesDb.Sales.Customer 
        ADD UpdateId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'
    ALTER TABLE SyncSamplesDb.Sales.Customer 
        ADD InsertId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000'

Maintenant que les colonnes ont été ajoutées, l'exemple de code suivant ajoute des index. Ces index et les autres index dans l'exemple de code sont créés sur des colonnes qui sont interrogées lors de la synchronisation. Les index sont ajoutés pour souligner l'importance des index lorsque vous assurez un suivi des modifications dans la base de données serveur. Veillez à trouver un juste équilibre entre les performances du serveur et les performances de la synchronisation.

    CREATE NONCLUSTERED INDEX IX_Customer_UpdateTimestamp
    ON Sales.Customer(UpdateTimestamp)
    
    CREATE NONCLUSTERED INDEX IX_Customer_InsertTimestamp
    ON Sales.Customer(InsertTimestamp)
    
    CREATE NONCLUSTERED INDEX IX_Customer_UpdateId
    ON Sales.Customer(UpdateId)
    
    CREATE NONCLUSTERED INDEX IX_Customer_InsertId
    ON Sales.Customer(InsertId)

Ajout d'une table tombstone pour assurer le suivi des opérations de suppression

L'exemple de code suivant crée une table tombstone qui possède un index cluster et un déclencheur pour remplir la table. Lorsqu'une opération de suppression se produit dans la table Sales.Customer, le déclencheur insère une ligne dans la table Sales.Customer_Tombstone. Avant de déclencher une opération d'insertion, le déclencheur vérifie si la table Sales.Customer_Tombstone contient déjà une ligne possédant la clé primaire d'une ligne supprimée. Cela se produit lorsqu'une ligne a été supprimée de la table Sales.Customer, réinsérée, puis supprimée de nouveau. Si une ligne de ce type est détectée dans la table Sales.Customer_Tombstone, le déclencheur supprime la ligne et la réinsère. La colonne DeleteTimestamp de Sales.Customer_Tombstone peut également être mise à jour.

    CREATE TABLE SyncSamplesDb.Sales.Customer_Tombstone(
        CustomerId uniqueidentifier NOT NULL PRIMARY KEY NONCLUSTERED, 
        CustomerName nvarchar(100) NOT NULL,
        SalesPerson nvarchar(100) NOT NULL,
        CustomerType nvarchar(100) NOT NULL,
        DeleteId uniqueidentifier NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
        DeleteTimestamp timestamp)

    CREATE TRIGGER Customer_DeleteTrigger 
    ON SyncSamplesDb.Sales.Customer FOR DELETE 
    AS 
    BEGIN 
        SET NOCOUNT ON
        DELETE FROM SyncSamplesDb.Sales.Customer_Tombstone 
            WHERE CustomerId IN (SELECT CustomerId FROM deleted)
        INSERT INTO SyncSamplesDb.Sales.Customer_Tombstone (CustomerId, CustomerName, SalesPerson, CustomerType) 
        SELECT CustomerId, CustomerName, SalesPerson, CustomerType FROM deleted
        SET NOCOUNT OFF
    END

    CREATE CLUSTERED INDEX IX_Customer_Tombstone_DeleteTimestamp
    ON Sales.Customer_Tombstone(DeleteTimestamp)
    
    CREATE NONCLUSTERED INDEX IX_Customer_Tombstone_DeleteId
    ON Sales.Customer_Tombstone(DeleteId)

Voir aussi

Autres ressources

Suivi des modifications dans la base de données serveur