Chapitre intéressant mais beaucoup trop long pour être entièrement détaillé ici.
Juste sql serveur 2005, la seule façon d'auditer les modifications est d'utiliser des triggers.
Cependant, cela ne permet pas d'auditer les lectures (par exemple qui à fait une selection sur la table des salaires)... cela imposant l'utilisation d'un modèle sécuritaire très contraignant.
Depuis SQL 2005, il existe aussi les DDL triggers qui permettent d'auditer, voir d'avorter (via un rollback) les modifications sur les schémas de DB.
Finallement, il reste encore que Profiler Sql qui permet de prendre une copie des opérations sql. Cependant Sql Profiler n'est pas un outil d'audit a proprement parlé (il peut rater des évènements) et consume des ressources de façon non négligeable.
Il existe un outil d'audit a par entière intégré au coeur de sql 2008. Cet outil est conçu pour consommer peu de ressources et préserver les performances de sql.
Avec cet outil, il est possible de surveiller les modification de schéma, de données et même les tentatives de lecture des informations sensible. Il sera par exemple possible de savoir si des tentatives de login sql ont échoués.
Le contrôle des modifications de schéma pouvent également faire l'objet d'opérations d'audit. Toutes les opérations d'audit pouvant être stockées dans une table, un fichier ou l'event log.
En Sql 2005, ces modifications étaient auditées à l'aide Trigger DDL, un rollback permettant d'avorter la modification le cas échéant si une exigeance sécuritaire l'imposait.
En SQL Serveur 2008, la configuration et la mise en place de l'audit est beaucoup plus facile et conviviale.
Et concernant l'avortement d'opérations non autorisées, Sql serveur 2008 dispose du "policies managements", également configurable beaucoup plus facilement, imposant des contraintes strictes (avortement de l'opération) ou non (simple log de l'opération) lors de la manipulation DDL d'objets sql.
Les développeurs modifient souvent leurs DB de développement manuellement.
S'ils ne prennent pas note de leurs modifications ou s'ils ne suivent pas à la lettre une procédure de développement; dans ce cas, retrouver toutes les modifications depuis une version donnée peut devenir un véritable casse tête.
La mises-à-jour de DB de production peut s'avérer difficile, c'est d'ailleurs un problème assez commun.
C'est là que les DDL triggers sont utiles!
En effet, si les DB de développements sont équipées de DDL triggers, il est alors possible de logger et retracer toutes les modifications faites sur le schéma de DB.
Sql serveur propose toute une série d'outils pour exporter et importer facilement des données.
Parmis ces éléments l'on retrouve:
- bcp.exe: Bulk Copy Program est un utilitaire en ligne de commande permettant d'exporter et d'importer des données dans des fichiers textes (ou format natif) à une vitesse tout bonnement incroyable.
- Bulk Insert T-Sql statement: une commande en TSql permettant d'importer le contenu de fichier textes directement dans une table. Très utile pour charger des données sur demande.
- SSIS: Sql Serveur Integration Service est un service et un environnement de développement (basé sur visual studio) permettant de définir des tâches et flux de données. Cet outil est plus particulièrement destiné à l'automatisation des importation/exportation et transfertde données. SSIS fait l'objet d'une section un peu plus détaillée.
- Sql Management Studio: Peut le savent mais lorsque l'on selectionne une DB dans management studio, il est possible de démarrer une tâche d'import/export via le popup menu. Cette action demarre un wizard permettant de sélectionner les tables à exporter et la destination (comme une feuille Excel par exemple). Ce wizard permet même de générer un package SSIS directement à partir des sélections faites dans le wizard.
Cet outil permet de concevoir des packages (à l'aide de visual studio ou le business inteligence studio de sql serveur) effectuant des tâches ETL (Extract Transform Load).
Cet outil est basé sur un gestionnaire de flux enchaînent les tâches les plus diverses (envois d'émail, manupulation de fichiers, Ftp, execution de code VB.Net, etc).
Certaines tâches pouvant être du type "Data Flow", ces dernières prenant en charge les activités ETL dans un designer spécifique.
Les opérations tâches et data flow interagissant avec le monde extérieur, SSIS utilise un module appelé "connections manager". C'est dans ce module que l'on définit toutes les connections avec le monde extérieur (mail, fichiers, connectique DB, etc).
Les différentes tâches et opérations passe systématiquement par les connections définies dans le "connections manager".
L'avantage de cette centralisation des connections est que par la suite, il sera possible d'utiliser le "configuration manager" (voir menu SSIS dans Visual Studio) pour stocker et initialiser les paramètres de ces connections depuis un fichier/table de configuration... cela facilitant grandement le déploiement des packages sur des serveurs de production.
Il est par ailleurs également possible de définir des variables au sein de SSIS.
SSIS pourrait faire l'objet d'une semaine entière de formation tellement cet outil est vaste.
Notice that the tasks workflow include a "Data Flow" meaning that data must be extracted from one point, transformed and stored into another location.
Voici une petite lecture recommendée par le formateur pour approfondir ses connaissances sur SSIS.
"Professional Microsoft Sql Serveur 2008 Integration Service" aux éditions Wrox.
Database Mail
Juste une petite note pour parler de "database mail", le remplacant de "Sql Mail".
Database mail permet de configurer des profiles e-mail sans avoir besoin du client outlook (ce qui était obligatoire pour SqlMail et étant par ailleurs la cause de brèches de sécurité).
Database Mail permet entre autre:
- D'envoyer des e-mail de façon asynchrone depuis la stored proc sp_send_mail (via une queue).
- De permettre au Job Agent d'envoyer des e-mails aux opérateurs (très utile d'une façon générale).
(server) | management | database mail | popup menu | configure database mail.
Un profile database mail contient toute l'information nécessaire permettant Sql Serveur de contacter un serveur SMTP.
La définition de profile de type "public" permet à tous les utilisateurs de Sql d'utiliser database mail.
La definition de profile de type "privé" permet de restraindre l'utilisation du profile database mail (ps: utiliser de préférence le user NT/SYSTEM).
Database mail et Job agent
L'utilisation de database mail par le Job Agent require une configuration un peu plus pointilleuse (sécurité oblige).
- Activer database mail pour le Job Agent.
(server)/Sql agent | popup menu | properties
Select page "Alert System"
Mail system= Database Mail (ne pas sélectionner SqlMail)
Selectionner le mail profile (déjà défini dans "database mail configuration"
Restart the Job Agent!!
1 commentaire:
Suis-je bête ou pourrait-on utilisé les tâches de l'ETL pour directement faire un flat export ou un Export SAS voir CDISC.
Ou bien existe-t-il l'inverse du Bulk-Insert, pour par exemple créer une vue sur les crfs et exporter le tout dans des fichiers texte ou un fichier xml ?
Ces actions pourrais je pense être commandé par un service ?
En tout cas, bravo pour ton initiative de placer ces infos sur ton blogue qui devient une vraie mine d'or. Le seul bémol, c'est qu'il faut quand même s'accrocher quand on a pas suivi le cours ;-)
A bientôt,
Enregistrer un commentaire