Guide de synchronisation des données dans Microsoft SQL Server

Le partage d'informations connexes entre des systèmes isolés est devenu de plus en plus important pour les organisations, car il leur permet d'améliorer la qualité et la disponibilité des données. Il existe de nombreuses situations dans lesquelles il est utile de disposer d'un ensemble de données disponible et cohérent sur plusieurs serveurs d'annuaire. C'est pourquoi il est important de connaître les méthodes courantes de synchronisation des données SQL Server.

La disponibilité et la cohérence des données peuvent être obtenues par des processus de réplication et de synchronisation des données. La réplication de données est le processus de création d'une ou plusieurs copies redondantes d'une base de données à des fins de tolérance aux pannes ou d'amélioration de l'accessibilité. La synchronisation des données est le processus d'établissement de la cohérence des données entre deux bases de données ou plus, et les mises à jour continues ultérieures pour maintenir ladite cohérence.

Dans de nombreuses organisations, effectuer la synchronisation des données sur divers systèmes est à la fois souhaitable et difficile. Nous pouvons trouver de nombreux cas d'utilisation où nous devons effectuer la synchronisation des données :

• Migration de base de données
• Synchronisation régulière entre les systèmes d'information
• Importer des données d'un système d'information dans un autre
• Déplacer des ensembles de données entre différentes étapes ou environnements
• Importation de données à partir d'une source autre qu'une base de données

Il n'existe pas de manière unique ou de méthode unanimement convenue pour la synchronisation des données. Cette tâche diffère d'un cas à l'autre, et même des synchronisations de données qui devraient être simples à première vue peuvent être compliquées, en raison de la complexité des structures de données. Dans des scénarios réels, la synchronisation des données consiste en de nombreuses tâches complexes, dont l'exécution peut prendre beaucoup de temps. Lorsqu'une nouvelle exigence survient, les spécialistes des bases de données doivent généralement réimplémenter l'ensemble du processus de synchronisation. Puisqu'il n'y a pas de moyens standard de le faire, outre la réplication, les implémentations de la synchronisation des données sont rarement optimales. Il en résulte une maintenance difficile et des dépenses plus élevées. La mise en œuvre et la maintenance de la synchronisation des données est un processus qui prend tellement de temps qu'il peut s'agir d'un travail à temps plein en soi.

Nous pouvons implémenter manuellement l'architecture des tâches de synchronisation des données, éventuellement à l'aide de Microsoft Sync Framework, ou nous pouvons bénéficier de solutions déjà créées dans les outils de gestion de Microsoft SQL Server. Nous allons essayer de décrire les méthodes et les outils les plus courants qui peuvent être utilisés pour résoudre la synchronisation des données sur les bases de données Microsoft SQL Server et essayer de donner quelques recommandations.

En fonction de la structure de la source et de la destination (par exemple, bases de données, tables), nous pouvons différencier les cas d'utilisation lorsque les structures sont similaires ou différentes.

La source et la destination ont des structures très similaires

C'est très souvent le cas lorsque nous utilisons des données à différentes étapes du cycle de vie du développement logiciel. Par exemple, la structure des données dans les environnements de test et de production est très similaire. L'exigence commune est de comparer les données entre la base de données de test et de production et d'importer les données de la production dans la base de données de test.

La source et la destination ont des structures différentes

Si les structures sont différentes, la synchronisation est plus compliquée. Il s'agit également d'une tâche récurrente plus fréquente. Un cas courant est l'importation d'une base de données dans une autre. Le cas le plus courant est celui où un logiciel doit importer des données à partir d'un autre logiciel géré par une autre société. En règle générale, les importations doivent s'exécuter automatiquement sur une base planifiée.

La méthode utilisée dépend des préférences personnelles et de la complexité du problème que vous devez résoudre.

Quelle que soit la similitude des structures, nous pouvons choisir quatre manières différentes de résoudre la synchronisation des données :

• Synchronisation à l'aide de scripts SQL créés manuellement
• Synchronisation à l'aide de la méthode de comparaison de données (ne peut être utilisée que lorsque la source et la cible ont une structure similaire)
• Synchronisation à l'aide de scripts SQL générés automatiquement - besoin d'un produit commercial

La source et la destination ont des structures identiques ou très similaires

Utilisation de scripts SQL créés manuellement

La solution la plus simple et la plus fastidieuse consiste à écrire manuellement des scripts SQL pour la synchronisation.

Avantages

• Peut être effectué par des outils gratuits et open source (FOSS).
• Si la table a des index, c'est très rapide.
• Le script SQL peut être enregistré dans une procédure stockée ou exécuté périodiquement en tant que tâche pour SQL Server.
• Peut être utilisé comme importation automatique, même sur des données modifiées en permanence.

Désavantages

• La création d'un tel script SQL est assez fastidieuse, car trois scripts sont généralement nécessaires pour chaque table : INSERT , UPDATE et DELETE .
• Vous ne pouvez synchroniser que les données disponibles via des requêtes SQL, vous ne pouvez donc pas importer à partir de sources telles que des fichiers CSV et XML.
• Il est difficile à maintenir - lorsque la structure de la base de données est modifiée, il est nécessaire de modifier deux ou trois scripts ( INSERT , UPDATE , et parfois aussi DELETE ).

Exemple

Nous ferons la synchronisation entre la table Source , avec les colonnes ID et Value , et la table Target , avec les mêmes colonnes.

Si les tables ont la même clé primaire et que la table cible n'a pas de clé primaire à incrémentation automatique (identité), vous pouvez exécuter le script de synchronisation suivant.

 -- insert INSERT INTO Target (ID, Value) SELECT ID, Value FROM Source WHERE NOT EXISTS (SELECT * FROM Target WHERE Target.ID = Source.ID); -- update UPDATE Target SET Value = Source.Value FROM Target INNER JOIN Source ON Target.ID = Source.ID -- delete DELETE FROM Target WHERE NOT EXISTS (SELECT * FROM Source WHERE Target.ID = Source.ID)

Utilisation de la méthode de comparaison de données

Dans cette méthode, nous pouvons utiliser un outil pour comparer les données source et cible. Le processus de comparaison génère des scripts SQL qui appliquent les différences de la base de données source dans la base de données cible.

Il existe un certain nombre de programmes pour la comparaison et la synchronisation des données. Ces programmes utilisent pour la plupart la même approche. L'utilisateur sélectionne la source et la base de données cible, mais d'autres alternatives peuvent être une sauvegarde de base de données, un dossier avec des scripts SQL ou même une connexion à un système de contrôle de source.

Vous trouverez ci-dessous les outils les plus populaires qui utilisent l'approche de comparaison de données :

• Comparaison de données dbForge pour SQL Server
• Comparaison de données RedGate SQL
• Différence de données SQL Apex

Dans la première étape, les données sont lues, ou simplement des sommes de contrôle de données plus volumineuses provenant de la source et de la cible sont lues. Ensuite, le processus de comparaison est exécuté.

Ces outils offrent également des paramètres supplémentaires pour la synchronisation.

Nous devons configurer les options de configuration suivantes nécessaires à la synchronisation des données :

Clé de synchronisation

Par défaut, la clé primaire ou une contrainte UNIQUE est utilisée. S'il n'y a pas de clé primaire, vous pouvez choisir une combinaison de colonnes. La clé Sync est utilisée pour coupler les lignes de la source avec les lignes de la cible.

Appariement de table

Par défaut, les tables sont appariées par nom. Vous pouvez changer cela et les coupler selon vos propres besoins. Dans le logiciel dbForge Data Compare, vous pouvez choisir la requête SQL comme source ou destination.

Processus de synchronisation

Après confirmation, l'outil compare les données source et cible. L'ensemble du processus consiste à télécharger toutes les données source et cible et à les comparer en fonction de critères spécifiés. Par défaut, les valeurs des tables et des colonnes portant le même nom sont comparées. Tous les outils prennent en charge le mappage des noms de colonne et de table. De plus, il est possible d'exclure les colonnes IDENTITY (auto-incrémentation) ou d'effectuer certaines transformations avant de comparer les valeurs (arrondir les types flottants, ignorer la casse des caractères, traiter NULL comme une chaîne vide, etc.) Le téléchargement des données est optimisé. Si le volume de données est important, seules les sommes de contrôle sont téléchargées. Cette optimisation est utile dans la plupart des cas, mais le temps requis pour effectuer les opérations augmente avec le volume de données.

Dans l'étape suivante, il y a un script SQL avec les migrations générées. Ce script peut être enregistré ou exécuté directement. Pour être sûr, nous pouvons même faire une sauvegarde de la base de données avant d'exécuter ce script. L'outil ApexSQL Data Diff peut créer un programme exécutable qui exécute le script sur une base de données sélectionnée. Ce script contient des données qui doivent être modifiées, pas la logique pour les modifier. Cela signifie que le script ne peut pas être exécuté automatiquement pour fournir une importation récurrente. C'est le plus gros inconvénient de cette approche.

Avantages

• Une connaissance avancée de SQL n'est pas requise et peut être effectuée via l'interface graphique.
• Vous avez la possibilité de vérifier visuellement les différences entre les bases de données avant la synchronisation.

Désavantages

• C'est une fonctionnalité avancée des produits commerciaux.
• Les performances diminuent lors du transfert d'énormes volumes de données.
• Le script SQL généré ne contient que des différences et ne peut donc pas être réutilisé pour synchroniser automatiquement les données futures.

Ci-dessous, vous pouvez voir l'interface utilisateur typique de ces outils.

Synchroniser avec SQL généré automatiquement

Cette méthode est très similaire à la méthode de comparaison de données. La seule différence par rapport à la méthode précédente est qu'il n'y a pas de comparaison de données et que le script SQL généré ne contient pas de différences de données, mais une logique de synchronisation. Le script généré peut être facilement enregistré dans une procédure stockée et peut être exécuté périodiquement (par exemple, chaque nuit). Cette méthode est utile pour les importations automatiques entre bases de données. Les performances de cette méthode sont bien meilleures que celles de la méthode de comparaison des données.

La synchronisation par SQL généré automatiquement n'est fournie que par SQL Database Studio.

SQL Database Studio fournit une interface similaire à la méthode de comparaison de données. Nous devons sélectionner la source et la cible (bases de données ou tables). Ensuite, nous devons configurer les options (clés de synchronisation, appairage et mappage). Il existe une fonctionnalité de générateur de requête graphique pour configurer tous les paramètres.

Avantages

• Une connaissance avancée de SQL n'est pas requise.
• Vous pouvez tout configurer dans une interface graphique assez rapidement.
• Le script SQL résultant peut être enregistré dans une procédure stockée.
• Peut être utilisé comme importation automatique - comme travail pour SQL Server.

Désavantages

• C'est une fonctionnalité avancée des produits commerciaux.
• Les différences ne peuvent pas être vérifiées manuellement avant la synchronisation, car l'ensemble du processus est exécuté en une seule étape.

Repères de performances

Cas de test

Deux bases de données (A et B), contenant chacune une table de 2 000 000 de lignes. Les tables se trouvent dans deux bases de données différentes sur le même serveur SQL. Ce test couvre deux cas extrêmes : 1) La table source contient les 2 000 000 de lignes et la table cible est vide. La synchronisation doit fournir de nombreux INSERTS . 2) Les tables source et cible contiennent 2 000 000 lignes. La différence n'est que sur une ligne. La synchronisation ne doit fournir qu'un seul UPDATE .

RedGate Data Compare nécessite 3 étapes :

• Comparer
• Générer un script
• Exécuter le script sur la base de données cible

ApexSQL Data Diff nécessite 2 étapes :

• Comparer
• Générer un script et exécuter un script en une seule étape

SQL Database Studio effectue toute la synchronisation en une seule étape. Vous trouverez ci-dessous les temps de synchronisation, en secondes. Dans la colonne intitulée "étapes individuelles", se trouvent les durées des étapes de synchronisation répertoriées ci-dessus.

Plus bas c'est mieux.

Le même test, mais les bases de données se trouvent sur différents serveurs SQL, qui ne sont pas connectés via un serveur lié.

Plus bas c'est mieux.

Sommaire

D'après les résultats, il est évident que RedGate et Apex ne se soucient pas de savoir si les bases de données se trouvent sur le même serveur SQL, car l'algorithme de synchronisation ne dépend pas de SQL Server. SQL Database Studio utilise les fonctions natives de SQL Server ; par conséquent, le résultat est meilleur lorsque les bases de données se trouvent sur le même serveur.

La source et la destination ont une structure différente

Il existe également des situations où une grande table doit être synchronisée dans plusieurs petites tables liées.

Cet exemple consiste en une large table SourceData qui doit être synchronisée dans de petites tables Continent , Country et City . Le schéma est donné ci-dessous.

Les données dans SourceData pourraient ressembler à celles de l'image ci-dessous.

Utilisation de scripts SQL créés manuellement

Table continentale de synchronisation de script

 INSERT INTO Continent (Name) SELECT SourceData.Continent FROM SourceData WHERE (SourceData.Continent IS NOT NULL AND NOT EXISTS (SELECT * FROM Continent tested WHERE tested.Name =SourceData.Continent )) GROUP BY SourceData.Continent;

Table de ville de synchronisation de script

 INSERT INTO City (Name, CountryId) SELECT SourceData.City, Country.Id FROM SourceData LEFT JOIN Continent ON SourceData.Continent = Continent.Name LEFT JOIN Country ON SourceData.Country = Country.Name AND Continent.Id = Country.ContinentId WHERE SourceData.City IS NOT NULL AND Country.Id IS NOT NULL AND NOT EXISTS (SELECT * FROM City tested WHERE tested.Name = SourceData.City AND tested.CountryId = Country.Id) GROUP BY SourceData.City, Country.Id;

Ce script est plus compliqué. C'est parce que les enregistrements dans les tables Country et Continent doivent être trouvés. Ce script insère les enregistrements manquants dans City et remplit correctement ContryId .

Les scripts UPDATE et DELETE peuvent également être écrits de la même manière si nécessaire.

Avantages

• Vous n'avez besoin d'aucun produit commercial.
• Le script SQL peut être enregistré dans une procédure stockée ou exécuté périodiquement en tant que travail pour SQL Server.

Désavantages

• La création d'un tel script SQL est difficile et compliquée (pour chaque table, trois scripts — INSERT , UPDATE et DELETE — sont généralement nécessaires).
• C'est très difficile à entretenir.

Utiliser des outils externes

Ce type de synchronisation (table large dans de nombreuses tables liées) ne peut pas être effectué avec la méthode de comparaison de données, car elle se concentre sur différents cas d'utilisation. Étant donné que la méthode de comparaison de données produit un script SQL avec des données à insérer, elle n'a pas la possibilité directe de rechercher des références dans des tables associées. Pour cette raison, les applications utilisant cette méthode ne peuvent pas être utilisées (dbForge Data Compare for SQL Server, RedGate SQL Data Compare, Apex SQL Data Diff).

Cependant, SQL Database Studio peut vous aider à créer automatiquement des scripts de synchronisation. Dans l'image ci-dessous, il y a un élément appelé Editor for Data Synchronization dans SQL Database Studio.

Editor ressemble au générateur de requête bien connu et fonctionne de manière très similaire. Chaque table doit avoir une clé de synchronisation définie, mais il existe également des relations définies entre les tables. Dans l'image ci-dessus, il y a également un mappage pour la synchronisation. Dans la liste des colonnes (partie inférieure de l'image) il y a les colonnes du tableau City (pour les autres tableaux c'est similaire).

Colonnes

• Id — Cette colonne n'est pas mappée car il s'agit de la clé primaire (générée automatiquement).
• CountryId — Cette colonne est définie comme référence pour la table.
• Nom — Cette colonne est remplie à partir de la colonne City dans la table source (table large).

Les colonnes CountryId et Name sont choisies comme clés de synchronisation. La clé de synchronisation est un ensemble de colonnes qui identifient de manière unique une ligne dans la table source et cible. Vous ne pouvez pas utiliser l' Id de clé primaire comme clé de synchronisation car il ne se trouve pas dans la table source.

Après la synchronisation, voici à quoi ressemblent les tables :

Dans l'exemple ci-dessus, il y avait une table large comme source. Il existe également un scénario courant lorsque les données source sont stockées dans plusieurs tables liées. Les relations dans SQL Database Studio ne sont pas définies à l'aide de clés étrangères, mais de noms de colonnes. De cette façon, il est également possible d'importer à partir de fichiers CSV ou Excel (le fichier est chargé dans une table temporaire et la synchronisation est exécutée à partir de cette table). Il est recommandé d'avoir des noms de colonne uniques. Si ce n'est pas possible, vous pouvez définir des alias pour ces colonnes.

Avantages

• Facile et rapide à créer
• Facile à maintenir
• Peut être enregistré dans une procédure stockée (la procédure stockée est enregistrée avec les données nécessaires pour ouvrir ultérieurement la synchronisation dans un éditeur)

Désavantages

• Solution commerciale

Comparer les solutions

La synchronisation des données consiste en une séquence de commandes INSERT , UPDATE ou DELETE . Il existe plusieurs façons de créer des séquences de ces commandes. Dans cet article, nous avons examiné trois options pour créer des scripts SQL de synchronisation. La première option consiste à tout créer manuellement. C'est faisable (mais prend trop de temps), cela nécessite une compréhension complexe de SQL, et c'est difficile à créer et à maintenir. La deuxième option consiste à utiliser des outils commerciaux. Nous avons regardé les outils suivants :

• Comparaison de données dbForge pour SQL Server
• Comparaison de données RedGate SQL
• Différence de données SQL Apex
• Studio de base de données SQL

Les trois premiers outils fonctionnent de manière très similaire. Ils comparent les données, permettent à l'utilisateur d'analyser les différences et peuvent synchroniser les différences sélectionnées (même automatiquement ou à partir de la ligne de commande). Ils sont bénéfiques pour ces scénarios d'utilisation :

• Les bases de données ne sont pas synchronisées en raison de diverses erreurs.
• Vous devez éviter la réplication lors du transfert de données entre les environnements.
• Des rapports de comparaison de données en Excel ou HTML sont nécessaires.

Chaque outil est apprécié pour une raison ou une autre : dbForge a une excellente interface utilisateur et de nombreuses options, ApexSQL fonctionne mieux que les autres et RedGate est le plus populaire.

Le quatrième outil, SQL Database Studio, fonctionne un peu différemment. Il génère des scripts SQL qui contiennent une logique de synchronisation, pas des modifications. Les performances sont également excellentes, car tout le travail est effectué directement sur le serveur de base de données, de sorte qu'aucun transfert de données entre le serveur de base de données et l'outil de synchronisation n'est nécessaire. Cet outil est utile pour les cas d'utilisation suivants :

• Migrations automatiques de bases de données où les bases de données ont une structure différente
• Importer dans plusieurs tables liées
• Importation depuis des sources externes XML, CSV, MS Excel