Discussion :

[Archi89]

Bonjour à tous,

Je vous sollicite à nouveau (après tant d’années) pour vous exposer un problème de taille.
Il s’agit du thème de la réplication en multi-sites.

Notre principe de fonctionnement actuel :
Nous travaillons sur un logiciel, qui utilise un server de fichier et une base SQL server. En gros, nos fichiers sont caractérisés par nos tables SQL. Pour l’instant, nos clients travaillent avec une seule base commune et là tout va bien.

Notre problématique :
Il y a maintenant des clients qui veulent travailler en multi-sites (par exemple : un en Chine, l’autre en Argentine). Et là, vient le problème de l’envoie de données. En effet, la connexion d’un site à l’autre est beaucoup trop lente. Actuellement, ils ont décidé de travailler avec deux bases différentes et nous avons développé une commande d’exportation de données qui exporte les fichiers d’une base à l’autre. Mais les vas et viens provoquent une certaine divergence dans le temps entre les deux bases.

Ce que nous voulons :
Avoir une seule base commune aux deux en optimisant les temps de transfert bien sûr. Mais si on imagine que la connexion internet n’est plus possible, un des sites ne peut plus travailler et c’est inacceptable.

Les solutions :
Voilà que je me tourne vers vous, pour, dans un premier temps, exposer les solutions qui existent. Nous avons notamment exploré les sujets de réplications entre base (sql mirroring, groupes de disponibilité, …). Le gros challenge est que la lecture et l’écriture de données puissent être possible dans les bases "secours" et surtout le "merge" de la base secours vers la base maitresse.

Ps : Je tiens à vous remercier d’avance et je voulais vous remercier aussi pour la qualité de votre site car même si je ne pose pas souvent de questions, je viens souvent lire ici et c’est une mine d’or (mieux qu’à l’école pour apprendre ).

[mikedavem]

Hello,

En prenant un compte ton besoin, vu de loin la réplication de fusion (MERGE) est un bon candidat à ton besoin et spécifiquement pour les points suivants:

- Multi master possible (lecture / écriture)
- Les sites peuvent être en autonomie en cas de coupure internet
- Réplication qui s'effectue au fil du temps (via exécution d'agents de réplication au travers de travaux SQL Server planifiés)

Juste prendre en compte l'impact de la réplication sur l'application (qui peut parfois exclure cette solution en fonction du contexte). En effet la réplication merge nécessite quelques adaptations plus ou moins non négligeables (liste non exhaustive):

- Ajout d'une colonne UNIQUEIDENTIFIER
- Ajout de triggers de réplication sur chaque table concernée par la réplication
- L'application doit aussi prendre en compte la réplication (quid des filtres de partitions, gestion des conflits etc ...)
- L'impact sur l'infrastructure qui doit évoluer (ajout de composants) avec la notion de publicateur, abonné et distributeur
- L'impact sur l'administration / monitoring de cette infrastructure
- L'impact des mises à jour (faire attention si des gros volumes de données doivent être répliqués d'un coup .. la réplication peut avoir du mal à les absorber surtout si les liens réseaux sont lents ...)

++

[SQLpro]

...Nous avons notamment exploré les sujets de réplications entre base (sql mirroring, groupes de disponibilité, …). Le gros challenge est que la lecture et l’écriture de données puissent être possible dans les bases "secours" et surtout le "merge" de la base secours vers la base maitresse.

sql mirroring, groupes de disponibilité
Comme son nom l'indique les groupes de disponibilité sont dédiés à la haute disponibilité, pas du tout à copier des données d'une base à l'autre en synchronisant les écritures faites de part et d'autre. Idem pour le mirroring.


il faut distinguer ce qui est de la
DUPLICATION des données => base strictement identique à l'originale, attendant d'être exploité en cas de perte de l'original, assurant les services de haute disponibilité…
de la
REPLICATION des données => différentes bases qui n'ont pas forcément les mêmes tables ni les même informations et dans lesquelles on propage certaines informations entre elles. Ceci permettant d'assurer du "scale out" c'est à dire étendre le service des données, soit par topologie (des serveurs à Paname, Xanadu et Tonbouctou), soit par emplacement (par exemple, sur un même lieu, un serveur ayant les client de A à M et l'autre de N à Z afin d'absorber plus de charge).

Vous êtes bien évidemment dans le 2e cas : réplication de données.

Comme vous voulez assurer une réplication de données de type "full duplex" (mise à jour possible sur tous les serveurs), les outils pour ce faire sont :

• la réplication de type MERGE
• service broker.

A +

[Archi89]

Merci pour vos réponses.

En réalité, on veut seulement écrire dans la base maître, les identifiants auto incrémentés doivent pointer sur un objet unique (nos fichiers).
Là par exemple, en cas de coupure internet, la base secondaire devient accessible en écriture, il va y avoir une divergence donc (par ex l'id 560, en base secondaire, pointera sur l'objet patate, tandis que l’objet 560, sur la base maître, sera une tomate).
Ensuite lorsque la connexion sera redevenue possible, on exportera l'objet patate sur la base maître et son id sera 587 par ex.
A ce moment, la base secondaire sera supprimée puis la base maître sera copiée à l'identique (on re-converge).
Je ne sais pas si j'ai été clair.

Nous pensions qu'une utilisation des groupes de synchro serait une bonne approche. Vous en pensez quoi avec ce fonctionnement ?

Bonne soirée.
A +

[SQLpro]

Nous pensions qu'une utilisation des groupes de synchro serait une bonne approche. Vous en pensez quoi avec ce fonctionnement ?

Totalement foireux !

la réplication de fusion (MERGE) répond à 100 % de vos besoins. Commencez par l'étudier.

A +

[Invité]

Il faut redéfinir l'objectif. Notre objectif est d'obtenir une meilleure disponibilité dans une configuration multi-site aux quatre coins du globe, il ne s'agit pas de répondre à une problématique de merge entre bases de données. Nous souhaitons éviter les problématiques de merge car elles sont très compliquées à résoudre, nous avons plus de 80 tables avec des relations complexes. Nous disposons déjà de notre propre système de transport des données de base de données à base de données, ce système permet d'effectuer les merges, c'est un système compliqué, le recoder sous la forme d'un merge SQL serait une sacré travail. D'autant plus que la problématique n'est pas "SQL Server only", le merge a des conséquences sur les fichiers fonctionnant en parallèle de la base de données. SQL Server ne peut pas gérer en même temps les conséquences en terme de merge sur des fichiers qui sont stockés sur des serveurs de fichiers distants (en accès TCP, FTP).

L'objectif principal est de s'affranchir des problématiques de latence qu'impose une connexion sur de grandes distances, un ping défavorable de 200ms nous pose problème pour assurer de bonnes performances dans le logiciel. D'où la solution de haute disponibilité consistant à avoir une base primaire en écriture sur un site et x bases secondaires en lecture seule disponibles sur les autres sites (Always on Availability Groups - groupes de disponibilité). Ceci permet de faire disparaître le problème de la latence sur plus de >80% des requêtes, car >80% des requêtes sont en lecture. Pour les requêtes en écriture nous acceptons de payer le coût du ping au site lointain. La question que nous nous posons, c'est le prix de SQL Server Enterprise pour obtenir les groupes de disponiblités (10,000 $ à rajouter par rapport à SQL Server Standard). C'est peut-être non bloquant car nous allons adresser des multi-nationales qui ont les moyens. Mais nous commençons à nous demander s'il ne faudrait pas envisager en plus un autre système de réplication moins cher, en développant notre propre SQL Agent de réplication par exemple (une réplication basique des tables, strictement à l'identique, sans merge). Sachant qu'il faudra que cette réplication s'effectue rapidement, nous pouvons accepter un certain décalage de 1 minute dans la réplication, mais il faut pas que ça aille au delà, après ça deviendra trop pénalisant car les utilisateurs travaillent en collaboratif.

[SQLpro]

Il faut redéfinir l'objectif. Notre objectif est d'obtenir une meilleure disponibilité dans une configuration multi-site aux quatre coins du globe, il ne s'agit pas de répondre à une problématique de merge entre bases de données. Nous souhaitons éviter les problématiques de merge car elles sont très compliquées à résoudre, nous avons plus de 80 tables avec des relations complexes. Nous disposons déjà de notre propre système de transport des données de base de données à base de données, ce système permet d'effectuer les merges, c'est un système compliqué, le recoder sous la forme d'un merge SQL serait une sacré travail. D'autant plus que la problématique n'est pas "SQL Server only", le merge a des conséquences sur les fichiers fonctionnant en parallèle de la base de données. SQL Server ne peut pas gérer en même temps les conséquences en terme de merge sur des fichiers qui sont stockés sur des serveurs de fichiers distants (en accès TCP, FTP).

Je pense que vous n'avez pas compris et toujours pas étudié les papiers que l'on vous a référencés. Il ne s'agit pas de la commande SQL MERGE, mais d'une réplication de fusion dite "MERGE REPLICATION" qui se base sur un ensemble de mécanismes intégrés à SQL Server depuis la version 7 de SQL Server et qui a été développé par Microsoft sous la houlette de Jean-Yves DEVANT avec un équipe de plusieurs dizaines de développeur de 1998 à 2008. Ceci fonctionne très bien et est utilisé par de nombreux gros client comme FNAC.com (plusieurs To de data dans différentes bases répliquées - plusieurs centaines de tables...) ou encore Eurocopter (devenu AIRBUS hélicopter) pour la base de maintenance mondiale (réplication sur plusieurs dizaines de pays...)

Les éventuels conflits de réplication (car il y en aura toujours) peuvent être traités de manière automatique par la mise en place de règles du genre :

• Le dernier qui met à jour est prioritaire
• Un ordre de priorité est défini pour chaque serveur (par exemple Serveur de paris, priorité 10, Lyon priorité 7, Marseille priorité 5...)
• etc.

Toute la mécanique (données à prendre en compte, propagation des informations, vecteur de communication...etc) est géré par les outils MS. Vous n'avez qu'à faire la mise en place de la chose.

Il semble donc, que par méconnaissance vous ayez réinventé la roue, mais une roue peu fiable et qui aujourd'hui vous montre ses limites. C'est dommage car avec un peu de formation initiale, vous auriez économisé énormément de travail, maintenance et déboires !

L'objectif principal est de s'affranchir des problématiques de latence qu'impose une connexion sur de grandes distances, un ping défavorable de 200ms nous pose problème pour assurer de bonnes performances dans le logiciel. D'où la solution de haute disponibilité consistant à avoir une base primaire en écriture sur un site et x bases secondaires en lecture seule disponibles sur les autres sites (Always on Availability Groups - groupes de disponibilité). Ceci permet de faire disparaître le problème de la latence sur plus de >80% des requêtes, car >80% des requêtes sont en lecture.

Votre latence va sans doute disparaître pour les lectures, mais vos lectures ne verrons que les données acquises ce qui risque de conduire à des problématiques plus grave encore...
En effet effectuer des écritures sur un serveur et des lectures sur un autre répliqué en mode asynchrone, va vous réserver quelques surprises.
Exemple :

• L'utilisateur rentre un nouveau client => INSERT sur serveur distant
• L'utilisateur affiche ensuite la liste des clients => SELECT sur serveur local (à la suite de l'INSERT)
• Compte tenu du mode asynchrone de propagation dans AlwaysOn dans votre cas de figure, le nouveau client n'apparait pas.
• Que fait l'utilisateur ?
• Il le recrée ne le voyant pas !

Vous avez pourri votre base !

Dans une application interactive qui attaque une base de données, toutes les lectures et écritures doivent se faire sur la même base, les lectures pouvant être effectuées sur un réplicas synchrone. Seul les "batchs" peuvent être effectué sur une machine déportée en mode asynchrone (ce que nous avons fait par exemple pour GEODIS, pour tous les documents de transport qui sont édités - impression - sur une grappe de serveur en réplicas asynchrone ALwaysOn).

Pour les requêtes en écriture nous acceptons de payer le coût du ping au site lointain. La question que nous nous posons, c'est le prix de SQL Server Enterprise pour obtenir les groupes de disponiblités (10,000 $ à rajouter par rapport à SQL Server Standard). C'est peut-être non bloquant car nous allons adresser des multi-nationales qui ont les moyens. Mais nous commençons à nous demander s'il ne faudrait pas envisager en plus un autre système de réplication moins cher, en développant notre propre SQL Agent de réplication par exemple (une réplication basique des tables, strictement à l'identique, sans merge). Sachant qu'il faudra que cette réplication s'effectue rapidement, nous pouvons accepter un certain décalage de 1 minute dans la réplication, mais il faut pas que ça aille au delà, après ça deviendra trop pénalisant car les utilisateurs travaillent en collaboratif.

Le mode de réplication de fusion peut descendre jusqu'à "au fil de l'eau". mais cela n'est pas conseillé si vous avez beaucoup de trafic. Personnellement j'invite à y mettre 10 à 15 secondes.

Enfin, vous pouvez aussi étudier la transactionnelle "peer to peer" (traduit par réplication transactionnelle d'égal à égal) qui peut se combiner avec la répication de fusion. En gros, si les données de vos clients restent propre à chaque client et que, seules, quelques tables doivent être fusionnées, alors c'est vers cette combinaisons de réplication qu'il faut aller.

A +