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 d'avoir un ensemble de données disponible et cohérent dans plusieurs serveurs d'annuaire. C’est pourquoi connaître les méthodes courantes d’exécution serveur SQL la synchronisation des données est importante.

La disponibilité et la cohérence des données peuvent être obtenues grâce à 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 ou plusieurs bases de données, et les mises à jour continues ultérieures pour maintenir cette cohérence.

Dans de nombreuses organisations, la synchronisation des données entre 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éplacement des ensembles de données entre différentes étapes ou environnements
• Importation de données à partir d'une source autre que la base de données

Il n'y a pas de méthode unique ou de méthode unanimement acceptée pour la synchronisation des données. Cette tâche diffère d'un cas à l'autre, et même les 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, qui peuvent prendre beaucoup de temps à exécuter. Lorsqu'une nouvelle exigence se présente, 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 méthode standard pour ce faire, outre la réplication, les implémentations de la synchronisation des données sont rarement optimales. Cela entraîne un entretien difficile et des dépenses plus élevées. La mise en œuvre et la maintenance de la synchronisation des données est un processus si chronophage qu'il peut être un travail à plein temps en soi.

Nous pouvons implémenter manuellement l'architecture pour les tâches de synchronisation des données, éventuellement en utilisant 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 outils les plus courants pouvant ê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 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 entreprise. 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 méthodes différentes pour 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 - nécessite 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 réalisé par des outils libres 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 s'exécuter périodiquement en tant que travail 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 allons faire 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 ne possède pas de clé primaire à incrémentation automatique (identité), vous pouvez exécuter le script de synchronisation suivant.

dans quelle langue sont écrits les bots discord

-- 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 des 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 à 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 généralement 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 des données RedGate SQL
• Diff des données Apex SQL

Dans la première étape, les données sont lues, ou simplement les sommes de contrôle de données plus volumineuses 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 qui sont nécessaires pour la synchronisation des données:

comment écrire un bot pour la discorde

Clé de synchronisation

Par défaut, la clé primaire ou un UNIQUE la contrainte 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 associer les lignes de la source avec les lignes de la cible.

Couplage de table

Par défaut, les tables sont associées par nom. Vous pouvez changer cela et les associer 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 colonnes de même nom sont comparées. Tous les outils prennent en charge les noms de colonnes et de tables de mappage. De plus, il est possible d'exclure IDENTITY (auto-incrémentation) ou pour effectuer des 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 existe un script SQL avec des 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, et non la logique de la manière de 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 la synchronisation automatique des données futures.

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

Diff des données ApexSQL

Comparaison RedGate SQL

Modifier la liste dans dbForge Data Compare

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 les bases de données. Les performances de cette méthode sont bien meilleures que celles de la méthode de comparaison de 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, couplage et mappage). Il existe une fonction de générateur de requêtes graphique pour configurer tous les paramètres.

Avantages

• Une connaissance approfondie 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.

Benchmarks de performance

Cas de test

Deux bases de données (A et B), chacune contenant une table de 2 000 000 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 lignes et la table cible est vide. La synchronisation doit fournir de nombreux INSERTS. 2) Les tables source et cible contiennent 2 000 000 de 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» figurent les durées des étapes de synchronisation répertoriées ci-dessus.

	Cas A. de nombreux INSERTs	Cas A. de nombreux INSERT (étapes individuelles)	Cas B.MISE À JOUR d'une ligne	Cas B.MISE À JOUR d'une ligne (étapes individuelles)
SQL Database Studio	47		5
Comparaison des données RedGate	317	13 + 92 + 212	2. 3	22 + 0 + 1
Diff des données ApexSQL	188	18 + 170	26	25+

Plus bas c'est mieux.

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

	Cas A. de nombreux INSERTs	Cas A. de nombreux INSERT (étapes individuelles)	Cas B.MISE À JOUR d'une ligne	Cas B.MISE À JOUR d'une ligne (étapes individuelles)
SQL Database Studio	78		44
Comparaison des données RedGate	288	17 + 82 + 179	25	24 + 0 + 1
Diff des données ApexSQL	203	18 + 185	25	24 + 1
Comparaison des données dbForge	326	11 + 315	16	16 + 0

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 sont 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 en plusieurs petites tables liées.

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

javascript obtient la date actuelle sans heure

Les données de SourceData peuvent être similaires à celles de l'image ci-dessous.

Utilisation de scripts SQL créés manuellement

Tableau des continents 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 ContryId correctement.

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

guide d'étude c++

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 à maintenir.

Utilisation d'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 capacité simple 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 existe un élément appelé Editor for Data Synchronization dans SQL Database Studio.

L'éditeur ressemble au générateur de requêtes 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) se trouvent les colonnes du tableau City (pour les autres tables, 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 une référence pour le tableau.
• Nom - Cette colonne est remplie à partir de la colonne Ville du tableau source (tableau large).

Colonnes CountryId et Name sont choisis 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 la clé primaire Id comme clé de synchronisation car elle ne figure pas dans la table source.

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

Dans l'exemple ci-dessus, il y avait une large table comme source. Il existe également un scénario courant lorsque les données source sont stockées dans plusieurs tables associé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 colonnes uniques. Si cela n'est pas possible, vous pouvez définir des alias pour ces colonnes.

Avantages

• Création simple et rapide
• 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

Comparaison des solutions

La synchronisation des données consiste en une séquence de INSERT, UPDATE ou DELETE commandes. 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 examiné les outils suivants:

• Comparaison de données dbForge pour SQL Server
• Comparaison des données RedGate SQL
• Diff des données Apex SQL
• SQL Database Studio

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 avantageux 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 dans 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 contenant une logique de synchronisation et non des modifications. Les performances sont également excellentes, car tout le travail est effectué directement sur le serveur de base de données, aucun transfert de données entre le serveur de base de données et l'outil de synchronisation n'est donc 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
• Importer à partir de sources externes XML, CSV, MS Excel

---

En relation: Guide de migration d'Oracle vers SQL Server et SQL Server vers Oracle

Comprendre les bases

Quels outils sont disponibles pour la synchronisation des bases de données?

Vous pouvez utiliser des outils tels que RedGate Data Compare, ApexSQL Data Diff et dbForge Data Compare, qui utilisent la méthode de comparaison de données. Vous pouvez utiliser SQL Database Studio, qui génère automatiquement des scripts SQL réutilisables.

Comment comparer les données de la base de données?

La comparaison basée sur des données peut être effectuée automatiquement à l'aide d'outils tiers, qui ont un prix. Vous pouvez également écrire des scripts SQL comparatifs qui vous montrent les différences, mais cela prend beaucoup plus de temps et est moins réutilisable.

Comment synchroniser les bases de données dans SQL Server?

Vous pouvez synchroniser manuellement les bases de données SQL en écrivant des scripts SQL (INSERT, DELETE, UPDATE) ou vous pouvez utiliser des outils tiers qui ont un prix. Les outils tiers fonctionnent en comparant les données et en générant des scripts SQL de synchronisation, ou vous pouvez configurer une logique de synchronisation et générer des scripts SQL basés sur celle-ci.