SQL Server 7.0 Réplication personnalisée

Si l’on en croit les « SQL Server Books Online (BOL) », la réplication copie
et diffuse les données et les objets base de données d’une base de données vers
une autre, et certains types conservent les copies en accord avec les sources.
La réplication par capture instantanée effectue des copies statiques de la sélection
de tables, colonnes et enregistrements ; la réplication transactionnelle commence
par une réplication par capture instantanée, puis copie périodiquement les modifications
intervenues dans les tables sources vers les tables cibles : elle devient ainsi
dynamique. Parce que les réplications par capture instantanée ne sont pas mises
à  jour régulièrement, elles n’ont généralement aucun problème de performances.
Cependant, la réplication transactionnelle conserve en permanence un processus
de lecture de l’historique.
Ce processus effectue un suivi des modifications répercutées sur les cibles et
des modifications en attente de la prochaine mise à  jour. Etant donné que le processus
de lecture de l’historique utilise les ressources système, plus les utilisateurs
apportent de modifications aux tables sources, plus la charge que le processus
de lecture de l’historique impose au système est lourde.

Le coding de ses propres procédures présente plusieurs avantages par rapport à
l’utilisation d’Enterprise Manager

Pour le transfert des données, on peut créer des procédures cataloguées personnalisées
associant quelques-unes des meilleures fonctions des réplications par capture
instantanée et transactionnelles, tout en évitant certaines de leurs lacunes.
Je qualifie ces procédures personnalisées de transferts deltas car le transfert
des seules modifications apportées aux données constitue l’une de leurs fonctions
les plus importantes. Lorsqu’on écrit et qu’on maintient des procédures cataloguées
personnalisées, on utilise du code T-SQL, à  l’inverse de l’utilisation de la réplication
standard par le biais d’Enterprise Manager, dans laquelle on exécute directement
l’une des nombreuses procédures de réplication de SQL Server 7.0.

Le coding de ses propres procédures présente plusieurs avantages par rapport à
l’utilisation d’Enterprise Manager ou des procédures système pour définir ou maintenir
ses besoins de réplication pour des tables cibles en lecture seule (sauf dans
les cas nécessitant une réplication fusion).

Avant tout, à  l’instar de la réplication transactionnelle, les procédures personnalisées
transfert-delta ne déplacent que les données modifiées. Cependant, comme dans
la réplication par capture instantanée, les procédures de transfert-delta induisent
une surcharge minime sur le serveur. (Les procédures personnalisées n’ont pas
besoin d’un processus de lecture du journal car elles n’ont pas à  maintenir les
informations concernant les enregistrements déplacés ou à  déplacer).

Ensuite, tout comme la synchronisation, ces procédures peuvent synchroniser tous
les enregistrements à  chaque fois que la procédure est exécutée. La réplication
transactionnelle s’assure uniquement que les enregistrements sont déplacés une
seule fois. C’est peut-être exactement ce dont vous avez besoin. Toutefois, si
les enregistrements cibles doivent correspondre exactement à  la source à  tout
instant, la réplication transactionnelle ne fonctionnera pas. Si les enregistrements
sont modifiés ou supprimés dans la table cible, la réplication transactionnelle
ne synchronise pas les enregistrements modifiés dans la table cible avec la source,
et ne remplace pas non plus les enregistrements supprimés dans la table cible.

Les tables sources peuvent subir des modifications de structure sans nécessiter
une administration lourde de la réplication, et cela constitue un autre avantage
des procédures de transfert-delta personnalisées. En outre, on peut contrôler
les mises à  jour par le biais d’une planification classique des tâches.

Bien entendu, le code personnalisé n’est pas toujours meilleur que les autres
types de réplication. L’avantage en termes de performances de ces procédures personnalisées
par rapport à  la réplication transactionnelle s’amenuise à  mesure que la fréquence
du transfert des données augmente. Si par exemple, on a besoin de mises à  jour
en permanence sur un serveur de secours à  chaud, l’utilisation répétée de procédures
personnalisées constitue une alternative peu intéressante aux performances de
l’agent de lecture de l’historique utilisé par la réplication transactionnelle.

Si les mises à  jour ne sont pas trop fréquentes et que l’on souhaite essayer une
réplication personnalisée, il vaut mieux choisir tout d’abord entre envoyer et
extraire les enregistrements, et créer un lien pour le serveur distant. Si on
exécute les procédures cataloguées sur le serveur contenant les tables sources,
on envoie les modifications ; si on les exécute sur le serveur recevant les enregistrements,
on extrait les modifications. Créez le lien vers le serveur distant en utilisant
sp_addlinkedserver ou Enterprise Manager pour ajouter ce serveur à  la liste des
serveurs liés au serveur local (SQL Server Group/servername/Sécurité/Serveurs
liés). On peut placer ses tables sources et cibles sur le même serveur, mais les
exemples de cet article supposent que l’on dispose de serveurs distincts.

Une procédure transfert-delta typique comprend trois parties. La première supprime
les enregistrements obsolètes des tables cibles, la seconde modifie les enregistrements
modifiés, et la troisième ajoute de nouveaux enregistrements. Cet ordre est important,
car il empêche la procédure d’évaluer inutilement les enregistrements obsolètes
ou nouveaux.

Prenons un exemple illustrant chacune des trois parties. Le listing 1 rassemble
les trois parties dans une procédure complète. L’exemple utilise une table source,
appelée Publishers, située dans une base de données locale (la table Publishers
de la base de données Pubs de Microsoft) et une table cible, appelée également
Publishers, située dans une base de données distante sur un serveur appelé Server2.
La table dispose d’une colonne de clé primaire, pub_id, et de quatre colonnes
d’attributs : pub_name, cible, state et country.

La première partie de la procédure est composée d’une requête DELETE permettant
de supprimer tout enregistrement existant dans la table cible mais n’existant
plus dans la table source. Si une table dispose de plus d’une colonne dans sa
clé primaire, cette requête DELETE doit comparer chaque colonne de la clé primaire
à  la colonne correspondante de la table cible. On peut également utiliser NOT
IN avec la sous-requête associée, mais cette possibilité rend les situations de
clé à  plusieurs colonnes plus complexes. Si on souhaite que la table cible serve
d’historique et non de copie, il suffit de ne pas inclure la requête DELETE.

La seconde partie est composée d’une requête UPDATE. Si les tables source et cible
disposent d’une colonne d’horodatage (réactualisée à  chaque fois que la table
source est mise à  jour), cette requête doit simplement comparer la clé primaire
et les colonnes d’horodatage pour vérifier si les enregistrements cibles doivent
être mis à  jour. Si la table ne dispose d’aucune colonne d’horodatage, la requête
doit comparer chaque colonne d’attribut de la table source à  sa colonne correspondante
dans la table cible (comme cela est illustré dans l’une des sections de commentaires
du listing 1). Si la valeur de la colonne d’horodatage (ou la valeur d’une colonne
d’attribut) ne correspond pas à  son équivalent, la requête met à  jour la valeur
pour toutes les colonnes d’attribut et la colonne d’horodatage le cas échéant.

Si les tables source et cible possèdent des clés primaires composées de plusieurs
colonnes, laissez tomber la clause JOIN/ON (comme l’indique le listing 1), séparez
les noms des tables par des virgules et ajoutez des instructions de comparaison
de colonnes clé à  la clause WHERE. Le listing 2 illustre des exemples de tables
avec et sans colonnes d’horodatage.

Lorsqu’on utilise une procédure cataloguée transfert-delta pour une table d’intersection
(plusieurs-à -plusieurs) dont la clé primaire comporte deux colonnes (chacune une
clé étrangère) et aucune colonne d’attribut, on n’a pas à  utiliser la requête
UPDATE, car seules les colonnes d’attribut sont mises à  jour. De la même manière,
si la clé contient des colonnes d’attribut, il faut effectuer une requête UPDATE.

La troisième et dernière partie d’une procédure transfert-delta est une instruction
INSERT, qui copie les enregistrements dans la table cible si la table source dispose
d’un enregistrement avec une clé primaire qui ne figure pas dans la table cible.
L’instruction copie chaque colonne, clé primaire ou attribut, et doit utiliser
chaque colonne de la clé primaire pour comparer les tables source et cible. Lorsque
la clé primaire est composée de plusieurs colonnes, on peut les concaténer pour
les comparer toutes, comme cela est indiqué dans le listing 3.

On peut modifier la version de base du listing 1 en variantes pour répondre à
la plupart des situations. Par exemple, le listing 3 est une procédure d’extraction
utilisant la fonction CAST et la concaténation pour associer deux colonnes de
clé primaire contenant des types de données différents. CAST (et CONVERT) modifient
le type de données d’origine d’une colonne en un type de données différent dans
l’ensemble de résultats. La concaténation combine deux ou plusieurs valeurs en
une valeur unique. Toutefois, les types de données doivent être compatibles. Par
exemple, pour concaténer les valeurs d’une colonne date, une colonne numérique
et une chaîne de texte, il faut modifier les dates et les valeurs numériques en
texte en utilisant CAST ou CONVERT.

Les procédures cataloguées personnalisées transfert-delta peuvent également nettoyer
et fusionner les données au fur et à  mesure qu’elles sont déplacées. Pour un nettoyage
simple, visant à  rendre les formats des données cohérents, on peut utiliser des
fonctions de casting ou de conversion des types de données, concaténer plusieurs
colonnes en une seule, ou encore utiliser les instructions CASE pour remplacer
des valeurs sous certaines conditions. On peut faire une fusion simple en disposant
de plusieurs ensembles de tables sources et un ensemble de tables cibles et en
omettant les requêtes DELETE dans les procédures transfert-delta. Par exemple
: si on a deux bases de données Ventes que l’on souhaite conserver distinctes,
mais que l’on veuille conserver l’historique des enregistrements des ventes dans
une troisième table. On peut alors utiliser des procédures permettant de copier
les enregistrements des deux sources dans une table VentesCombinées. Si une table
source contient les noms des états en entier et que dans l’autre ils sont abrégés,
on peut utiliser une instruction CASE dans une procédure de transfert-delta pour
faire correspondre les données à  la convention de l’autre table.

Lorsqu’un ensemble de ces procédures existe pour chaque table, on peut les exécuter
toutes en appelant une procédure globale. Le listing 4 illustre un exemple de
procédure globale supposant que les procédures de table individuelles soient nommées
sp_TransferDelta_tablename. Cette approche peut s’avérer utile pendant le développement
et le déboguage. Cependant, lorsqu’on planifie une tâche pour automatiser les
mises à  jour, le dépannage est d’autant plus simple si on fait de chaque procédure
de transfert-delta une étape distincte au lieu d’appeler un ensemble de procédures
dans une procédure globale.

Lorsqu’on crée une nouvelle procédure de transfert-delta, on peut utiliser la
requête suivante pour générer une liste délimitée par des virgules de noms de
colonnes nécessaires aux requêtes INSERT :
SELECT column_name + ‘,’ FROM
information_schema.columns
WHERE table_name = ‘publishers’

Pour optimiser votre expérience et découvrir des possibilités plus avancées des
procédures cataloguées personnalisées, analysez les procédures cataloguées de
réplication du système et les procédures cataloguées étendues de la base de données
Master. On peut en générer une liste en exécutant la requête suivante :
« SELECT DISTINCT Name FROM SysObjects WHERE
(Type = ‘P’ OR Type = ‘X’) AND (Name LIKE ‘%agent%’
OR Name LIKE ‘%repl%’
OR Name LIKE ‘%dist%’
OR Name LIKE ‘%publ%’
OR Name LIKE ‘%merge%’
OR Name LIKE ‘%subs%’
OR Name LIKE ‘%sync%’) ORDER BY 1 »

Le développement de vos capacités pour aller au-delà  des assistants intégrés à
SQL Server vous apportera plus de souplesse dans le transfert des donnéesSi vous
ne pouvez pas consulter la base de données Master depuis d’Enterprise Manager,
vous pouvez la faire apparaître en cliquant avec le bouton droit de la souris
sur le nom du serveur, en sélectionnant Edit SQL Server Registration Properties,
puis en sélectionnant la case Show system databases and system objects et en cliquant
sur OK.

Veuillez noter que les procédures présentes dans le dossier Extended Stored Procedures
de la base de données Master sont des objets compilés (y compris certaines commençant
par sp_). Par conséquent, on ne peut pas consulter leur code source en double
cliquant dessus comme on pourrait le faire avec d’autres procédures cataloguées.
Heureusement, elles apparaissent toutes dans BOL. Vous pouvez ainsi vous perfectionner
en analysant ces procédures.

Vous pouvez créer des procédures de transfert-delta lorsque vous avez besoin des
avantages de la personnalisation, ou vous pouvez les utiliser comme point de départ
pour des procédures personnalisées encore plus sophistiquées. Le développement
de vos capacités pour aller au-delà  des assistants intégrés à  SQL Server vous
apportera plus de souplesse dans le transfert des données, et vous ouvrira d’autres
horizons afin de tirer profit des options lorsque vous avez besoin des avantages
de la réplication.

John D. Lambert est MCDBA, MCSE+I et MCSD. Il travaille pour In-Q-Tel.