Pour transférer des données, il existe différentes méthodes, différentes les unes des autres.
Toutes ces méthodes ont pour objectifs de transférer des données, mais ont des périmètres d’utilisation différents.
Nous allons voir les différentes méthodes de transfert de données avec des bases SQL Server, mais gardons à l’esprit que tout SGDB qui se respecte fournit le même genre d’outil.
L’utilitaire bcp
Bcp, pour bulk copy program, est un utilitaire de ligne de commande qui permet l’importation ou l’exportation rapide des données à partir ou vers un fichier.
Cet utilitaire a 2 principales limites :
- Il possède des fonctionnalités ETL limitées.
- Il possède des fonctionnalités de gestions des erreurs limitées.
Malgré cela, il reste un utilitaire très performant et adapté si le besoin est uniquement de charger/décharger des données, étant données qu’il peut être intégré à un script Windows, et ensuite configuré pour s’exécuter périodiquement.
Avant d’exécuter bcp sur une table, assurez vous d’avoir au moins les autorisations select, insert et alter table sur cette table.
Pour plus d’explication sur l’utilitaire bcp, et notamment la liste des paramètres, suivez le lien suivant : http://msdn2.microsoft.com/en-us/library/aa337544.aspx
|
Exemple d’exécution de bcp sur la base AdventureWorks pour exporter des données sur les employés.
|
La commande Bulk Insert
La commande bulk insert est la version T-SQL de l’utilitaire bcp.
Cependant, bulk insert n’est pas capable d’exporter des données et se limite donc à l’importation (Insert).
L’avantage de cette commande est le fait qu’elle puisse être utilisée dans un script T-SQL, et donc directement depuis une fenêtre de commande de SQL Server, ou lors de chargement de base de données par l’intermédiaire de script.
Du point de vue de la sécurité, au niveau SQL Server, il faut posséder des permissions Insert et Administer Bulk Operation, ainsi que Alter Table, pour pouvoir utiliser Bulk Insert avec succès, et côté système de fichier, si le mode d’authentification est Windows dans SQL Server, les privilèges sur le fichiers sont les privilèges du compte de l’utilisateur ; cependant si le mode d’authentification est SQL Server, les privilèges sur le fichier seront ceux du compte utilisateur par SQL Server (par défaut Local System).
Pour plus d’informations sur la commande Bulk Insert, suivez le lien :
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
|
Exemple d’insertion de données avec bulk insert.
|
La fonction OpenRowSet()
SQL Server propose plusieurs options pour l’importation de documents xml. Ici nous allons étudier la méthode la plus commune, la fonction OpenRowSet(), mais il existe également des procédures stockées, la fonction OpenXml() ,XQuery, XPath…
La fonction OpenRowSet() peut être utilisée comme référence de table pour des instructions Select, Insert, Update ou Delete.
Son principal avantage est de faire apparaitre un fichier xml directement comme une table, et de pouvoir le modifier, sans avoir à le charger au préalable dans la base.
Pour plus d’informations sur la fonction OpenRowSet(), suivez le lien :
http://msdn2.microsoft.com/en-us/library/ms175915.aspx
|
Exemple d’utilisation de la fonction OpenRowSet().
|
La brique SSIS
SSIS, pour SQL Server Integration Services, est la méthode la plus robuste pour importer ou exporter des données. SSIS est le nouvel outil ETL de SQL Server 2005, et arrive en remplacement de dts (Data Transformation Services), présent dans les versions précédentes.
Il existe plusieurs façons d’accéder à SSIS, tout dépend de ce que vous voulez en faire.
L’emploi de SSIS est intéressant quand il s’agit de transférer des données entre sources de données hétérogènes, et en plus, quand il faut leur appliquer des transformations.
A partir de Management Studio
Vous pouvez accéder à SQL Server depuis Management Studio en exécutant l’assistant d’import/export de données, en cliquant droit sur une base de données et en choisissant exporter ou importer des données, ensuite l’assistant vous aide à la création d’un package simple que vous pourrez ensuite exécuter.
|
Assistant pour la création d’un package SSIS.
|
A partir de l’invite de commande
A partir de l’invite de commande, vous pourrez exécuter l’utilitaire dtswizard.exe pour lancer l’assistant précédent.
|
Utilisation de dtswizard.
|
A partir de visual studio (BIDS : BI Development Studio)
A partir de visual studio ou de BIDS, vous allez avoir accès à toute la puissance de SSIS pour pouvoir créer des packages complets et fonctionnels.
|
Interface Graphique d’un projet SSIS.
|
Les méthodes de sauvegarde/restauration des données
Les méthodes de sauvegarde sont surtout destinées à être utilisée dans un processus de restauration des données, dans le cas d’une corruption des données, d’une panne matérielle ou encore d’une erreur humaine sur un serveur de base de données.
Cependant, dans le cadre d’un transfert d’une base de données toute entière, il peut être intéressant de passer directement par le processus de sauvegarde/restauration de la base, surtout si des copies de sauvegarde sont déjà disponibles.
Les différentes méthodes de sauvegarde
Avec SQL Server, il existe 6 méthodes de sauvegarde de données.
Les 2 premières sont bien connues des administrateurs système, puisqu’il s’agit de la sauvegarde complète et de la sauvegarde différentielle des données.
Avec la sauvegarde complète, toutes les données sont sauvegardées. Pour réaliser ce type de sauvegarde, on peut utiliser l’assistant graphique, pour passer par une commande T-SQL.
Le but de la sauvegarde complète est de réduire le temps de restauration de la base, étant donné qu’on ne restaure qu’une seule sauvegarde ; cependant le temps de sauvegarde peut être long.
|
Assistant de sauvegarde complète/différentielle des données.
|
|
Script de sauvegarde complète de la base AdventureWorks.
|
La sauvegarde différentielle, quant à elle, capture toutes les données qui ont été modifiée depuis la dernière sauvegarde complète. Le but des sauvegardes différentielles est de trouver un bon compromis entre le temps de sauvegarde et celui de récupération. En effet une sauvegarde différentielle est moins longue qu’une sauvegarde complète, mais une restauration à partir de cette sauvegarde sera plus longue, car il faut commencer par appliquer la dernière sauvegarde complète de la base, suivie de la dernière sauvegarde différentielle.
RQ : Une sauvegarde différentielle s’effectue toujours en complément d’une sauvegarde complète.
Pour effectuer une sauvegarde différentielle, on peut utiliser l’utilitaire graphique précédent, ou utiliser un script T-SQL.
|
Script de sauvegarde différentielle de la base AdventureWorks.
|
Les autres méthodes de sauvegarde sont :
- Sauvegarde du journal de transaction, qui ne sauvegarde aucune donnée, mais uniquement toutes les requêtes de modification de la base, afin de pouvoir les rejouer lors de la restauration,
- Sauvegarde de groupe de fichiers, qui permet de sauvegarder uniquement un sous ensemble de données, ce qui permet de minimiser à la fois la sauvegarde et la restauration, étant donné qu’on se base sur un ensemble restreint de valeurs.
- Sauvegarde miroir, qui permet de créer plusieurs jeux de sauvegarde simultanément (jusqu’à 4) appelés miroirs.
- Sauvegarde partielle : Dans le cas ou une base de données contient des groupes de fichiers en lecture/écriture et d’autres en lecture seule, la clause READ_WRITE_FILEGROUPS indique au moteur de sauvegarde de sauter tout groupe de fichier marqué en lecture seule afin d’accélérer la sauvegarde des données.
Les différentes méthodes de déplacement de données
Outre le fait de passer par une sauvegarde/restauration pour déplacer des bases de données, il existe 2 autres façons de procéder.
La méthode de détachement/attachement des données est parfois plus intéressante que la sauvegarde/restauration, car dans ce cas, les données ne sont pas recopiées ; le SGDB supprime simplement la référence de la base de données qui devient donc inexistante pour le moteur de base de données lors du détachement, mais les fichiers sur disque ne sont pas affectés.
Le processus d’attachement quant à lui crée tout simplement une nouvelle référence de la base de données dans le moteur de bases de données, en pointant sur les fichiers sur disque. Ce processus est extrêmement performant, car quasiment aucune opération sur disque n’est nécessaire.
|
Scripts de détachement/attachement de la base de données AdventureWorks.
|
Une autre méthode est l’emploi de l’assistant copie de base de données, qui est en fait un package SSIS préconfiguré. Avec cette méthode, nous pouvons choisir de déplacer ou de copier la base de données, mais aussi de mettre la base hors ligne, ou de la maintenir en ligne durant le processus.
|
Etape de l’assistant.
|
Le processus de réplication
Le processus de réplication est très intéressant quand on travaille dans un environnement distribué, car le SGDB est capable de détecter et de gérer les conflits sans intervention humaine.
De plus, on peut choisir soit d’envoyer les données à partir du serveur à tous les abonnés (méthode push), soit de demander les données à partir des clients (méthode pull).
Avec SQL Server 2005, il existe3 types de réplication :
- La réplication de capture instantanée ; A des moments précis, le serveur effectue une copie de l’ensemble de ses données à répliquer, à destination des abonnés.
- La réplication transactionnelle ; Initialement, le serveur duplique l’ensemble de ses données à destination des abonnés, puis par la suite, il ne réplique que les données modifiés en utilisant le journal des transactions.
- La réplication de fusion ; elle permet de prendre en charge des modifications aussi bien sur les bases de l’éditeur que sur celles des abonnés.
Dans le cadre de la réplication sur PDA, une seule méthode de réplication est disponible, c’est la réplication de fusion, déclinée en 2 versions :
- RDA, qui reprend la réplication de fusion, mais sans la résolution de conflit
- La réplication de fusion, telle qu’expliquée ci-dessus.
Le seul avantage de RDA est la rapidité.