sauvegarde et restauration / jeu existant

[grinder59]

Bonjour,

j'utilise une base de données MS SQL Server 2005 qui commence à être bien trop grosse puisqu'elle comporte plusieurs années de données (99% inutiles au quotidien), il a donc été décidé d'établir une base de données de production contenant les données jour et une base d'archive contenant les données antérieures.

Le problème est que je ne suis pas un expert dans ce domaine et j'aimerai avoir votre avis concernant le scénario de backup / restore que je souhaite mettre en place :

- étape 1 : réalisation quotidienne d'un backup (*.bak) à partir de la base de données de production et ajout de ce backup au jeu existant (option disponible dans la partie options de la fenêtre Backup de SQL Server Management Studio)

- étape 2 : restauration du backup réalisé sur la base d'archive

- étape 3 : nettoyage de la base de production des données inutiles

Est-ce que cette façon de faire permettra de :
- disposer de la totalité des données sur le serveur d'archive (après restauration de la sauvegarde des données du jour) ?
- dans la mesure où je souhaite faire une sauvegarde quotidienne, ne vais-je pas être limité par le nombre de sauvegardes qu'il est possible d'ajouter sur le même jeu ?
- dans la mesure où il y a des données persistantes pendant quelques jours dans la base de données de production, ces données vont se retrouver sur différentes sauvegardes... Cela ne va-t-il pas poser de souci si je restaure une sauvegarde contenant déjà un objet précédemment sauvegardé ?

Merci de vos conseils !

[mikedavem]

Bonjour,

Combien de tables sont concernés par ce problème de place ? Les avez-vous identifiées ?

++

[grinder59]

La base contient une 50aine de tables dont une dizaine (identifiées) sont très volumineuses.

Nous souhaiterions mettre en place un processus global qui reste de général et ne rentre pas dans les tables, afin d'éviter les processus spécifiques à adapter à chaque évolution d'une table. En outre, même si certaine table ne pose pas problème, nous voudrions vraiment scinder nos données en 2 bases distinctes :
- la base de prod contenant les données du jour
- la base d'archive contenant les données antérieurs à aujourd'hui

[mikedavem]

Vous ne pourrez pas procéder de façon globale avec la méthode que vous voulez employer. En effet, les opérations de sauvegarde et de restauration ne pourront pas simplement compléter le jeu de données existant sur votre base d'archive.

Vous n'aurez pas d'autre choix que de créer un processus d'alimentation de vos tables d'archives et de nettoyage de vos tables de production. Je parle ici de processus de manière générale.

Quelle est la volumétrie de votre base de données ?

++

[grinder59]

arf, pas hyper glop comme perspective...

Existe-t-il des outils (outils externes, commandes, procédures stockées...) qui me permettraient d'éviter de construire des requêtes manipulant les champs ? le but étant de construire un fonctionnement pérenne qui ne soit pas à modifier à chaque évolution d'une des tables !

[zinzineti]

Envoyé par grinder59

arf, pas hyper glop comme perspective...

Existe-t-il des outils (outils externes, commandes, procédures stockées...) qui me permettraient d'éviter de construire des requêtes manipulant les champs ? le but étant de construire un fonctionnement pérenne qui ne soit pas à modifier à chaque évolution d'une des tables !

Il n'existe pas dans SQL SERVER une IHM pour réaliser ce genre d'opération vous devez écrire des procédures SQL. vous pouvez procéder de la manière suivante :

1) Faire le backup de la base de prod

2) Faire le backup de la base d'archive

3) créer un lien entre les deux serveurs (prod et archivage)

4) écrire la procédure SQL d'UPDATE des lignes existantes à la fois sur la prod et sur l'archivage (il faut déterminer l’identifiant des tables concernées)

5) écrire la procédure SQL d'INSERT de nouvelles données du serveur de prod vers le serveur d'archivage (il faut prendre en compte les PK et FK c-a-d les relations entre les tables PARENTS-ENFANTS)

6) supprimer les lignes des tables concernées dans la base de prod

7) planifier l'exécution quotidienne (à heure de non activité) de ces procédures via des jobs

Rappel :
il faut tester les procédures dans un environnement de test avant de les mettre en prod.

[grinder59]

ouch ! pas le choix que de faire un traitement enregistrement par enregistrement ? et ben ça promet !

Merci de vos réponses, même si, je ne vous le cache pas, ce n'est pas tout à fait ce que j'aurai aimé lire...

[Jinroh77]

Dans chacune des tables concernées, avez-vous au moins un champ d'IdDate ?
Vous pourriez alors imaginer un processus générique basé sur cette colonne et une table de Date pour gérer vos transfert de données.

[grinder59]

et bien non, justement je n'ai pas forcément de champs de date dans toutes les tables... En outre dans notre cas, ce n'est pas parce qu'un enregistrement comporte un champs Date qu'il sera plus facile d'identifier ce qu'il y a a mettre à jour car nous avons pas mal de statuts qui peuvent être updatés d'un jour sur l'autre. Donc il est nécessaire de ne pas archiver cet enregistrement que je ne devrais archiver qu'après mise à jour de tous les statuts (cela peut prendre plusieurs jours)...

Bref c'est un peu la merde quand même...

[Jinroh77]

J'ai du rater quelque chose alors... Qu'est-ce qui vous permet de définir qu'une ligne est à "archiver" ?

[zinzineti]

Envoyé par grinder59

arf, pas hyper glop comme perspective...

Existe-t-il des outils (outils externes, commandes, procédures stockées...) qui me permettraient d'éviter de construire des requêtes manipulant les champs ? le but étant de construire un fonctionnement pérenne qui ne soit pas à modifier à chaque évolution d'une des tables !

Autre méthode : archivage mensuel des principales tables.
Cette méthode permet de contourner les UPDATE.

0) créer le lien entre les deux serveurs

Tous les 1er de chaque mois à 00Heure:00minute:01seconde faire l'archivage des tables du mois précédent :

1) exécuter un script pour renommer les principales tables tous les 1er de chaque mois (exemple au 1er janvier 2012 la table TableClients devient TableClients_201112 ) sur la base de prod

2) exécuter le script de création des principales tables (y compris les index, les constraints, ...) sur la base de prod

3) exécuter le script de création des principales tables avec comme suffixe l'année et le mois précédent (AAAAMM) sur la base archive

4) exécuter le script d'INSERT des données des principales tables (_AAAAMM) de la base de prod vers les tables (_AAAAMM) de la base archive

5) supprimer les tables _AAAAMM de la base de prod

tout ceci peut être scheduler via un job sql. ici aussi il faut écrire des procédures SQL.

[grinder59]

J'ai du rater quelque chose alors... Qu'est-ce qui vous permet de définir qu'une ligne est à "archiver" ?

dans certaines tables il s'agit de la valeur d'un ou plusieurs statut(s), dans d'autres il s'agit d'une date...

Autre méthode : archivage mensuel des principales tables.
Cette méthode permet de contourner les UPDATE.

C'est une méthode à laquelle j'avais pensé mais qui ne convient pas car les enregistrements de certaines tables n'ont pas de "date de péremption"... Je ne peux pas garantir que le 1er de chaque mois, tous les enregistrements pourront être archivés. De plus nous devons pouvoir extraire des données en une seule fois sur une large plage de temps sans multiplier les connexions aux bases mensuelles.

[mikedavem]

A la vue de vos contraintes vous n'aurez donc pas le choix d'implémenter pour chaque cas une procédure d'archivage spécifique.

++