Discussion :

[StringBuilder]

Bonjour,

J'ai à plusieurs reprises été contraint de mettre en place des base "offline" synchronisées avec un serveur central (base embarquée sur un terminal qui n'a pas une connexion permanente au réseau).

Et souvent dans ces cas là, on ne veut pas se contenter d'une base de données "readonly", mais aussi pouvoir créer des données.

Imaginons donc une table "client".

Sur le serveur, j'ai le référentiel, dans une table simple, mettons :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
create table client
(
   id int not null primary key identity,
   nom varchar(50) not null,
   prenom varchar(50) not null,
   email varchar(256) not null unique
);

Si la table est créée telle qu'elle sur le serveur et sur les bases offline, je vais avoir des collisions d'ID dès que :
- plusieurs bases offline créent des clients entre deux synchrnonisation
- un client est créé sur le serveur central pendant qu'un client est créé sur une base offline

Et après c'est le drame, car toutes les données liées sont en conflit (commandes, etc.) et la synchronisation devient alors rapidement cauchemardesque.

J'ai jusqu'à présent vu ou imaginé cinq solutions permettant d'éviter ces conflits d'ID.

Même si j'ai une préférence pour la quatrième (je pense, de loin la plus propre et la plus fiable, en dépit du problème évident de performances lors de la synchronisation lorsqu'il y a beaucoup de données), j'aimerais savoir ce que vous en pensez.

Solution 1
Utiliser une clé de type GUID.
D'après le site de Microsoft, le type GUID est spécifié de telle sorte qu'il soit impossible de générer :
- deux fois le même numéro sur la même machine
- deux fois le même numéro sur deux machines distinctes
Sauf que chez moi, 5 × 10^36 (source Wikipedia), ça représente pas assez de valeur pour que ces deux affirmations puissent être absolument vraies.
Aussi, une clé sur 128 bits, je trouve ça un peu gros, et j'imagine est tout sauf optimal d'un point de vue performances.
Enfin, GUID étant aléatoire, cela implique une répartition homogène et aléatoire sur l'index de la clé primaire. Ainsi, l'utilisation de cette colonne comme clé CLUSTER pourrait s'avérer catastrophique en raison de la présence de trous tout au long de l'index et l'obligation de déplacer physiquement les données à chaque fois qu'un trou est bouché.
Bref, si cette solution est séduisante sur le papier, je ne suis vraiment pas convaincu pour une telle utilisation.

Solution 2
Utiliser une clé composée.
Une colonne "station_id" et une colonne "row_id" par exemple. Chaque base dispose d'un "station_id" propre. "row_id" peut alors avoir des doublons, le couple (station_id, row_id) reste unique.
Cette solution est la solution la plus proche de la démarche fonctionnelle, j'imagine. En revanche, il me semble que SQL Server n'aime pas trop (d'un point de vue performances) l'utilisation de clés composées pour l'index CLUSTER. Par conséquent, cette solution n'est pas optimale.
Aussi, tout comme GUID, lorsque la station 1 insèrre des données après la station 2, on va devoir décaller les données de la station 2 pour laisser de la place aux données de la station 1. Ceci peut rapidement devenir problématique j'imagine.
Enfin, ce qui ne me plait pas trop là dedans, c'est que la ligne reste rattachée à la notion de station, ce qui sémantiquement peut enduire d'erreur la personne qui consulte les données.
Un moyen "simple" pour pallier à ce problème (enfin...) serait de recalculer un "row_id" pour la station_id = 0 lors de la synchronisation, et mettre à jour les lignes avec ces nouveaux identifiants. Ainsi on se retrouve avec un index cluster sans trou. En revanche, on est obligé de faire des mises à jour d'identifiants, avec toutes les implications CASCADE CONSTRAINT qui peuvent en découler.

Solution 3
Idem à la solution 2, au détail qu'il s'agit de concaténer les deux id dans une seule colonne à l'aide d'une opération de masque : id = (station_id * 2^32) + row_id
Cette fois, l'index CLUSTER est content.
En revanche, on garde tous les autres défaut de a solution 2, en ajoutant un nouveau : l'ID est clairement illisible, même si en soit c'est pas censé être gênant.

Solution 4
Mettre en place une séquence avec une plage différente par base de données.
Ainsi les identifiants ne se chevauchent pas.
On se retrouve cependant toujours avec des problèmes de décalage physiques de données lorsque la station 1 insère des données alors qu'il existe des donnée pour les autres stations.

Solution 5
Attention, rechargez bien les piles de vos pacemakers...
Bon, alors mon problème avec les 4 solutions précédentes, c'est les décalage dans l'index CLUSTER : à chaque fois on a des trous et besoin de décaler des données en masse quand il n'y a plus de trou.
Du coup, me vient l'idée de la mort qui tue tout (surtout le lecteur du post) : une clé composite à base d'un DATETIME2 et d'un row_id.
Ainsi les données sont systématiquement ajoutée vers la fin de l'index CLUSTER, même si les volume de données est important.
Par contre... c'est pas ce qu'il y a de plus pratique à trimbaler comme clé, surtout dans les jointures, y'a de quoi se poser des questions quand on tombe sur une date... Bon pis voilà, niveau performances et tout le tralala, c'est tout sauf génial...

Mise à part d'éventuels mécanismes propres à SQL Server (je cherche une solution relativement portable, par exemple si la base embarquée n'est pas sous SQL Server mais Access, SQL Lite, etc.) quelles autres solutions vous viennent à l'esprit ? Laquelle vous semble la mieux ? (en considérant à la fois les problématiques de comprenette et de performances)

[hmira]

Normalement la solution préconisée dans ce genre des situation est la "Réplication de fusion"

Mais, si pour une raison ou une autre, la mise en œuvre de la réplication de fusion ne peut être envisagée, personnellement parmi les différentes solutions que vous avez énumérées, je choisirais la solution la plus simple à mettre en ouvre et la plus facile à maintenir, c.à.d. la solution n° 2, mais légèrement aménagée :
"Utiliser une clé composée.
Une colonne "station_id" et une colonne "row_id" par exemple. Chaque base dispose d'un "station_id" propre. "row_id" peut alors avoir des doublons, le couple (station_id, row_id) reste unique."

Et rien ne vous oblige à organiser la clé primaire (station_id, row_id) en Cluster.
Vous pouvez tout à fait, pour les raisons de performances que vous avez évoquées, définir la clé primaire de sorte qu'elle soit Non Cluster. Cela ne résoudra pas les problèmes de performance lorsque par exemple la station 1 insère des données après la station 2, puisque des événements de "Split" se produiront fatalement sur la clé primaire, mais l'impact sera moins important que si la clé primaire était organisée en cluster.

A+

[Kropernic]

Hello,

Je tombe par hasard sur cette discussion et je crois que certains liens pour être intéressant.

[SQLpro]

La solution 4 est la plus intéressante dans votre cas.
Je suppose que l'intégration des données des autres systèmes ne se fait pas en continu. Donc la fragmentation se fera par gros bloc et au final ne sera pas très importante. Mais il faudra penser à les défragmenter de toutes façon, comme tous les index !

Une variante est de jouer sur le modulo. Exemple avec 4 serveurs :

• le serveur 1 commence a 1 avec un pas de 4
• le serveur 2 commence a 2 avec un pas de 4
• le serveur 3 commence a 3 avec un pas de 4
• le serveur 4 commence a 4 avec un pas de 4

Avantage avec ce dernier système, vous identifiez la source avec la fonction modulo (%).

A +

A +