SQL Server Administration [PDF]

Zitiervorschau

---

Administration de SQL Server

Administration à l’aide de SQL Server. Objectif modulaire A l’issue de ce module, le stagiaire doit être capable d’exécuter et automatiser les tâches administratives et créer des outils d'administration personnalisés. Critères généraux de performance : ▪ Gestion correcte des automatisations. ▪ Sécurisation et restauration correcte des BD. ▪ Automatisation correcte des taches administratives.

Objectifs intermédiaires

Critères particuliers

de performance Présenter les éléments Présentation d’administration SQL Server. correcte.

Eléments contenus ❑

Elément d’administration de SQL Server • Utilitaire OSQL et BCP. • Outil de gestion de SQL Server.

Implémenter un mode d’authentification.

Définir les autorisations.

Mode d’authentification correctement implémenté. Définition correcte.

❑

• Tache d’administration. Implémentation d’un mode d’authentification.

❑

Rappel sur la sécurité de SQL Server.

❑

Traitement de l’authentification.

• Sélection d’un mode d’authentification. ❑

• Création des comptes de connexions. Gestion des autorisations.

❑

Définition d’autorisation.

❑

Types d’autorisation

Octroi, refus et révocation d’autorisation.

Réalisation d’une sauvegarde sauvegarde de bases de correctement réalisé. données.

❑

❑ ❑ ❑ ❑ ❑

Restauration des sauvegardes de données.

Restauration correctement réalisée.

❑ ❑ ❑ ❑ ❑

Surveiller les performances de SQL Server.

Surveillance correctement réalisée..

❑ ❑ ❑ ❑

❑

Sauvegarde des bases de données. Protection contre la perte. Sauvegarde de SQL Server. Exécution de sauvegarde. Méthode de sauvegarde. Planification d’une stratégie de sauvegarde. Restauration de bases de données. Processus de récupération de SQL Server. Préparation à la restauration. Restauration de sauvegarde. Restauration de BD système endommagées.

Surveillance des performances de SQL Server. Raisons justifiant la surveillance de SQL Server. Surveillance et ajustement des performances. Outils de surveillances de SQL Server. Tâches contraintes liées à la surveillance.

Elément d’administration de SQL Server

• Utilitaire OSQL et BCP. • Outil de gestion de SQL Server (Sql Server Management Studio). • Tache d’administration.

Qu’est ce qu’un SGBDR ? SQL Server est un Système de Gestion de Base de Données Relationnelle (SGBDR), ce qui lui confère une très grande capacité à gérer les données tout en conservant leur intégrité et leur cohérence. SQL Server est chargé de : ● stocker les données, ● vérifier les contraintes d’intégrité définies, ● garantir la cohérence des données qu’il stocke, même en cas de panne (arrêt brutal) du système, ● assurer les relations entre les données définies par les utilisateurs. Ce produit est complètement intégré à Windows et ce à plusieurs niveaux : • Observateur des événements : le journal des applications est utilisé pour consigner les erreurs générées par SQL Server. La gestion des erreurs est centralisée par Windows, ce qui facilite le diagnostic.

● Analyseur de performances : par l’ajout de nouveaux compteurs, il est facile de détecter les goulots d’étranglement et de mieux réagir, pour éviter ces problèmes. On utilise toute la puissance de l’analyseur de performances, et il est possible au sein du même outil de poser des compteurs sur SQL Server et sur Windows et ainsi d’être à même de détecter le vrai problème. ● Traitements parallèles : SQL Server est capable de tirer profit des architectures mutiprocesseurs. Chaque instance SQL Server dispose de son propre processus d’exécution et des threads Windows ou bien des fibres (si l’option est activée) sont exécutés afin d’exploiter au mieux l’architecture matérielle disponible. Chaque instance SQL Server exécute toujours plusieurs threads Windows. Pour prendre en charge tous les processeurs présents sur le système, le paramètre de configuration max degree of parallelism doit conserver la valeur 0. Il s’agit de la valeur par défaut. Pour empêcher la génération de plan d’exécution parallèle, il suffit d’affecter la valeur 1 à ce paramètre. Enfin en lui affectant une valeur comprise entre 1 et le nombre de processeurs, il est possible de limiter le degré de parallélisme. La valeur maximale supportée par ce paramètre est 64.

Sécurité : SQL Server est capable de s’appuyer intégralement sur la sécurité gérée par Windows, afin de permettre aux utilisateurs finaux de ne posséder qu’un nom d’utilisateur et un seul mot de passe. Néanmoins SQL Server gère son propre système de sécurité pour tous les clients non Microsoft. ● Les services Windows sont mis à contribution pour exécuter les composants logiciels correspondant au serveur. La gestion du serveur (arrêt, démarrage et suspension) est facilitée et il est possible de profiter de toutes les fonctionnalités associées aux services de Windows (démarrage automatique, exécution dans le contexte d’un compte d’utilisateur du domaine...). Active Directory : les serveurs SQL 2008 et leurs propriétés sont automatiquement enregistrés dans le service d’annuaire Active Directory. Il est ainsi possible d’effectuer des recherches dans Active Directory pour localiser les instances SQL Server qui fonctionnent.

SQL Server peut gérer deux types de bases de données différentes : ● les bases OLTP (OnLine Transactional Processing) qui correspondent à des bases dans lesquelles les informations sont stockées de façon directe afin de réutiliser plus tard ces informations telles qu’elles ont été stockées. ● les bases OLAP (OnLine Analytical Processing) qui contiennent des informations statistiques afin d’être capable d’extraire les informations sous forme de cube multidimensionnel dans un but d’aide à la décision par exemple. Les statistiques contenues dans des bases OLAP s’appuient sur des informations contenues dans une base OLTP.

Les composants de SQL Server Le moteur de base de données de SQL Server ou Database Engine est composé de plusieurs logiciels. Certains s’exécutent sous forme de services alors que d’autres possèdent une interface utilisateur graphique ou en ligne de commande. Composants Serveur SQL Server s’exécute sous forme de services Windows. Suivant les options d’installation choisies, il peut y avoir plus de services. Les principaux services sont : SQL Server : c’est le serveur de base de données à proprement parlé. Si ce service n’est pas démarré, il n’est pas possible d’accéder aux informations. C’est par l’intermédiaire de ce service que SQL Server assure la gestion des requêtes utilisateurs. Ce service est référencé sous le nom MSSQLSERVER pour l’instance par défaut et MSSQLSERVER $nomInstance dans le cas d’une instance nommée.

SQL Server Agent : ce service prend en charge l’exécution de tâches planifiées, la surveillance de SQL Server et le suivi des alertes. Il est directement lié à une instance de SQL Server. Il est référencé dans le gestionnaire de service sous le nom SQL Server Agent(MSSQLSERVER) pour l’instance par défaut et SQL Server Agent(nomInstance) dans le cas d’une instance nommée.

Microsoft Full Text Search : ce service propose de gérer l’indexation des documents de type texte stockés dans SQL Server et gère également les recherches par rapport aux mots clés.

Il est possible d’installer plusieurs instances de SQL Server sur le même poste.

Connectivité Client L’installation des composants de connectivité sur les postes clients permet de prendre en charge la gestion du réseau, la DB Library pour les programmes en accès natif, le support OLEDB et ODBC. Outils de gestion Les réalisations des tâches d’administration sont possibles par l’utilisation d’outils. Ces outils possèdent pour la plupart une interface graphique conviviale et d’utilisation intuitive. Cependant, les tâches administratives doivent être réfléchies avant leur réalisation. L’utilisation de certains outils suppose que le composant serveur correspondant est installé.

Ces outils sont : ● SQL Server Management Studio pour réaliser toutes les opérations au niveau du serveur de base de données. ● SQL Server Configuration Manager pour gérer les services liés à SQL Server. ● SQL Server Profiler pour suivre et analyser la charge de travail d’une instance SQL Server. ● Database Engine Tuning Advisor pour permettre une optimisation du fonctionnement du serveur de base de données.

En plus de ces outils, SQL Server propose Business Intelligence Development Studio pour la programmation de travaux qui vont s’inscrire dans un cadre d’analyse multidimensionnelle des données.

Les composants

Les différentes briques logicielles fournies par SQL Server s’articulent toujours autour du moteur de base de données relationnelles qui traite de façon performante les informations stockées au format relationnel et au format xml. SQL Server Analysis Service permet une analyse poussée des cubes de données définis par l’intermédiaire du Business Intelligence Development Studio. ● SQL Server Integration Service (SSIS) est un outil d’importation et d’exportation de données facile à mettre en place tout en étant fortement paramétrable. ● Reporting Services permet de mettre en place des rapports d’analyse des données. ● La réplication des données sur différentes instances permet de positionner les données au plus près des utilisateurs et de réduire les temps de traitement. ● Service Broker permet un travail en mode asynchrone et facilite ainsi la gestion des pics de forte activité en stockant les demandes de travail avant de les traiter.

L’intégration du CLR dans SQL Server permet de développer procédures et fonctions en utilisant les langages VB.Net et C#. L’intégration du CLR ne vient pas se substituer au Transact SQL mais se présente comme un complément afin de pouvoir réaliser un codage simple et performant pour l’ensemble des fonctionnalités qui doivent être présentes sur le serveur. Les points de terminaison http permettent à SQL Server d’héberger ses propres services et de faciliter ainsi l’intégration du serveur dans un contexte hétérogène.

Administration Le langage naturel de SQL Server est le Transact SQL. Il est donc nécessaire de lui transmettre les instructions dans ce langage. Comme ce langage n’est pas forcément naturel pour l’utilisateur, il est possible de composer l’instruction de façon graphique par SQL Server Management Studio, puis de provoquer son exécution sur le serveur à l’aide des boutons OK, Appliquer... Les outils graphiques utilisent la bibliothèque SMO (SQL Server Management Object) pour établir un dialogue efficace avec le serveur. Il est donc possible d’écrire des scripts Transact SQL pour exécuter des opérations administratives sous forme de traitement batch.

Programmation

Le développement d’applications clientes pour visualiser les données contenues dans le serveur peut s’appuyer sur différentes technologies.

Base de données SQL Server Objets de base de données

Les bases de données contiennent un certain nombre d’objets logiques. Il est possible de regrouper ces objets en trois grandes catégories : ● Gestion et stockage des données : tables, type de données, contraintes d’intégrité, valeur par défaut, règles et index. ● Accès aux données : vues et procédures stockées. ● Gestion de l’intégrité complexe : déclencheur (procédure stockée s’exécutant automatiquement lors de l’exécution d’un ordre SQL modifiant le contenu d’une table : INSERT, UPDATE et DELETE). Le déclencheur est toujours associé à une table et à une instruction SQL. Il permet de mettre en place des règles d’intégrité complexes à cheval sur plusieurs tables ou de maintenir des données non normalisées.

Nom complet des objets La règle appliquée pour nommer les objets permet une parfaite identification. Le nom complet est composé comme suit : serveur.nomBase.propriétaire.objet.

Bases de données système et tables système Pour gérer l’ensemble des données stockées, SQL Server s’utilise lui même. Il existe donc des bases de données système et sur chaque base utilisateur, quelques tables système. L’insertion et la mise à jour de données dans ces tables ne s’effectuent jamais directement, mais via des commandes Transact SQL ou des procédures stockées.

Les noms des bases de données et des tables systèmes sont fixés et connus par SQL Server. Il ne faut donc pas renommer une table ou une base système.

Master C’est la base de données principale de SQL Server. L’ensemble des données stratégiques pour le bon fonctionnement du serveur y est stocké (comptes de connexion, options de configuration, l’existence des bases de données utilisateurs et les références vers les fichiers qui composent ces bases...). Model Cette base contient l’ensemble des éléments inscrits dans toute nouvelle base utilisateur. Par défaut, il n’y a que les tables système, mais il est possible de rajouter des éléments. Tempdb La base Tempdb est un espace temporaire de stockage partagé. Il permet de gérer les tables temporaires locales ou globales, les tables de travail intermédiaires pour faire des tris par exemple, mais aussi les jeux de résultats des curseurs. La base Tempdb est recréée, avec sa taille initiale, lors de chaque démarrage de l’instance. Ainsi, aucune information ne peut être conservée de façon persistante à l’intérieur de la base Tempdb. Les objets temporaires sont, quant à eux, supprimés lors de la déconnexion de leur propriétaire.

Msdb Elle contient les informations utilisées par le service SQL Server Agent pour déclencher une alerte, prévenir un opérateur ou exécuter une tâche planifiée. Msdb contient également l’historique de l’exécution des tâches. Ressource

Cette base en lecture seule contient la définition de tous les nouveaux éléments définis à partir de SQL Server 2005. Les objets systèmes y sont définis bien que logiquement ils apparaissent dans le schéma de l’utilisateur sys. Avec cette base, la migration de SQL Server 2000 vers SQL Server 200x est facilitée, car l’ajout simple de la base ressource permet d’obtenir l’ensemble des objets définis dans SQL Server 2005 sans qu’il soit nécessaire de toucher à la base master.

Les tâches de l’administrateur

Les tâches de l’administrateur L’administrateur de bases de données a pour objectif principal d’améliorer le fonctionnement de la base de données. Bien que SQL Server possède de nombreux outils et algorithmes d’auto optimisation, il reste de nombreuses tâches à l’administrateur. Les principales tâches de l’administrateur sont : • • • • • •

Gérer les services SQL Server ; Gérer les instances SQL Server ; Mettre en place le processus de sauvegarde et de restauration ; Configurer une disponibilité des données en accord avec la politique d’entreprise ; Gérer les configurations réseaux ; Importer et exporter des données

En plus des compétences système que doit posséder l’administrateur pour être capable de gérer au mieux l’instance SQL Server, il est important pour lui de connaître les possibilités offertes par SQL Server pour l’automatisation des tâches avec SQL Agent.

Pour mesurer le résultat de son travail et comparer les différents choix de configuration qu’il peut être amené à faire, l’administrateur doit être en mesure d’utiliser les outils et méthodes liés à SQL Server. Enfin et c’est sans doute un point crucial, l’administration de la base doit être inscrite dans un processus plus global qui intègre l’administrateur dès la conception de la base, de façon à effectuer les meilleurs choix en terme d’architecture dès la conception. L’administrateur pourra ainsi intervenir sur la création de la base et les choix faits comme, par exemple, le nombre de groupe de fichiers à utiliser, les index, les vues, les procédures stockées à définir de façon à optimiser le trafic réseau mais aussi pour simplifier la gestion des droits d’accès. C’est également l’administrateur qui va pouvoir conseiller sur le partitionnement ou non d’une table.

Les outils

Les outils SQL Server dispose de nombreux outils. Ces outils sont complémentaires et chacun d’entre eux est adapté à un type de problème ou d’action. Il est important de posséder une vue d’ensemble sur l’objectif visé par chacun de ces outils afin de savoir lequel employer pour résoudre un problème bien précis. Il est possible de faire ici une analogie avec le bricolage, si vous ne possédez qu’un tournevis vous allez essayer de tout faire avec, alors qu’au contraire, si votre caisse à outils est bien garnie il y a de forte chance d’y trouver l’outil qui répond exactement au problème rencontré. Pour SQL Server l’approche est similaire. Il est bien entendu possible de faire quasiment tout en Transact SQL, mais SQL Server propose des outils graphiques autres pour permettre de solutionner des problèmes bien précis. L’utilisation de la plupart d’entres eux va être détaillée tout au long de cet ouvrage. Un regard global permet cependant d’avoir une meilleure vision de l’intérêt présenté par chacun.

SQL Server Management Studio Il s’agit de l’outil principal de SQL Server et il est destiné aussi bien aux développeurs qu’aux administrateurs. SQL Server Management Studio (SSMS) est la console graphique d’administration des instances SQL Server. Il est possible d’administrer plusieurs instances locales et/ou distantes depuis cet outil. SQL Server Management Studio est également l’outil principal des développeurs de bases de données qui vont l’utiliser pour définir les scripts de création des tables, des vues, des procédures, des fonctions, des déclencheurs de base de données…

Cet utilitaire peut être démarré depuis la ligne de commande par l’intermédiaire de l’application ssms.

Gestionnaire de Configuration SQL Server Le gestionnaire de configuration de SQL Server permet de gérer l’ensemble des éléments relatifs à la configuration des services et du réseau côté client et côté server.

La configuration des services Les différents services relatifs à SQL Server peuvent être administrés directement depuis cet outil. En plus des opérations classiques d’arrêt et de démarrage, il est possible de configurer le type de démarrage (automatique, manuel, désactivé) ainsi que le compte de sécurité au sein duquel le service doit s’exécuter.

Configuration de réseau SQL Server Le gestionnaire de configuration permet également de gérer quels sont les protocoles pris en charge au niveau du serveur. Il est également possible à ce niveau de modifier les propriétés spécifiques à chaque protocole comme le numéro du port d’écoute du protocole TCP/IP.

Configuration de SQL Native Client 10.0 Cette fois-ci la configuration porte sur les outils client installés localement et plus exactement de définir précisément les protocoles à leur disposition pour entrer en contact avec le serveur, mais aussi, lorsque cela s’avère nécessaire, la possibilité de définir des alias. Cette fonctionnalité est particulièrement intéressante lorsque le nom du serveur est enregistré dans une application et qu’il n’est pas possible ou bien facile de modifier cet enregistrement. La définition d’un alias permet de rediriger toutes les demandes vers un serveur distinct.

La configuration

Avant de mettre en service le serveur SQL, en le rendant accessible par tous les utilisateurs, il est important de réaliser un certain nombre d’opérations de configuration du serveur et des outils d’administration client afin de se prémunir contre toute opération sensible. 1. Les services

Les différents composants serveur s’exécutent sous forme de service. Il est donc nécessaire que ces services soient démarrés afin de pouvoir travailler avec le serveur. Ces services peuvent être gérés avec le gestionnaire de configuration de SQL Server mais ils peuvent également être gérés comme tous les services Windows. Depuis le gestionnaire de configuration, il est simple de visualiser l’état du service ainsi que de modifier ses propriétés.

Comme tous les services Windows, ils peuvent être gérés de façon centrale au niveau du serveur Windows.

Enfin, il est possible d’agir sur ces services directement en ligne de commandes par l’intermédiaire des commandes net start et net stop. Lors d’un démarrage en ligne de commande, il est possible d’outrepasser la configuration par défaut du service en spécifiant la configuration à utiliser sous forme de paramètres. Par exemple, l’option m (net start mssqlserver m) permet de démarrer le serveur en mode mono utilisateur.

Les différents états des services Démarré

Suspendu

Arrêté

SQL Server Management Studio SQL Server Management Studio est l’outil de gestion graphique de SQL Server qui permet de réaliser les tâches administratives et toutes les opérations de développement. L’utilisation du même outil permet de réduire la distinction entre les deux groupes d’utilisateurs que sont les administrateurs et les développeurs. En partageant le même outil, il est plus facile de connaître ce qu’il est possible de faire d’une autre façon. Pour pouvoir naviguer d’une instance à l’autre de SQL Server, éventuellement sur des serveurs différents, il est nécessaire d’enregistrer chaque serveur dans la console d’administration. Cette inscription n’est pas nécessaire pour l’instance locale de SQL Server, car lors de la création de l’instance, les informations relatives à cette instance ont été ajoutées dans SQL Server Management Studio

Inscrire un serveur

La fenêtre Serveurs Inscrits permet de connaître la liste des serveurs inscrits dans SQL Server Management Studio. Si cette fenêtre n’est pas visible, il est possible de demander son affichage par le menu Affichage Serveurs inscrits ou par le raccourci clavier [Ctrl][Alt] G. Les serveurs sont regroupés par type. Pour chaque type il est possible de définir des groupes de serveurs afin de les regrouper sur un autre critère, par exemple l’emplacement physique. Les groupes de serveurs n’ont aucune influence sur l’inscription du serveur. Il est possible de déplacer un serveur vers un groupe en sélectionnant l’option Tâches Déplacer vers depuis le menu contextuel associé au serveur. Par analogie, il est possible de comparer les groupes de serveurs à des dossiers et les serveurs inscrits à des fichiers. Les fichiers ne sont pas affectés lorsqu’ils sont déplacés d’un dossier à un autre. Il en est de même pour les inscriptions de serveur. Les dossiers sont définis pour regrouper les fichiers suivant une certaine logique, c’est exactement le rôle que jouent les groupes de serveurs.

Pour inscrire un nouveau serveur depuis SQL Server Management Studio, il faut sélectionner l’option Nouvelle inscription de serveur depuis le menu contextuel associé au nœud Local Server Groups dans la fenêtre Serveurs inscrits.

La boîte de dialogue qui permet de réaliser l’inscription est composée de deux onglets. Le premier onglet permet de compléter toutes les informations générales de l’inscription comme le nom du serveur, mais également le type d’authentification utilisé pour établir la connexion sur le serveur. Depuis SSMS il est possible d’inscrire des serveurs SQL Server, Analysis Services, SQL Server Compact Edition, Reporting Services et Integration Services. Le bouton Tester permet de s’assurer que la connexion choisie permet bien de travailler sur le serveur sélectionné. Il est possible d’enregistrer un serveur avec un nom différent de celui du serveur.

Pour des raisons de sécurité, il est préférable de choisir lorsque cela est possible le mode d’authentification Windows.

Configuration du serveur Avant d’ouvrir plus librement l’accès au serveur et de permettre aux utilisateurs de venir travailler sur le serveur, il convient de surveiller attentivement les deux points suivants : Mot de passe de l’administrateur Cette préoccupation concerne uniquement les serveurs qui sont configurés en mode de sécurité mixte. Si ce choix a été fait au cours de l’installation, il faut s’assurer que le mot de passe de l’administrateur SQL Server (sa) est suffisamment fort. Si ce n’est pas le cas, il est alors nécessaire de le modifier.

Lors de l’installation de SQL Server deux utilisateurs sont prédéfinis. Le premier est le groupe local des administrateurs (utilisé avec la sécurité Windows), le second est l’utilisateur sa. Ces deux utilisateurs présentent des droits d’administrateur du serveur SQL. L’utilisateur sa s’appuie sur le sécurité SQL Server et son mot de passe a été demandé durant la procédure d’installation.

Si lors de l’installation, seul le mode de sécurité Windows a été activé, alors la connexion sa est non active. Il est nécessaire de définir un mot de passe fort avant d’activer la connexion. Cependant la connexion ne pourra être utilisée que si le serveur est configuré en mode de sécurité mixte. Par SQL Server Management Studio

Par le transact SQL

Les Utilitaires

OSQL est un utilitaire de ligne de commande qui peut être utilisé pour interroger une base de données et effectuer d'autres tâches administratives. Il est accessible sur un serveur et vous permet d'émettre des commandes à un autre directement via une invite de commande, des étapes de travail de l'Agent SQL Server ou des fichiers de commandes.

L'utilitaire osql vous permet de saisir des instructions Transact-SQL, des procédures système et des fichiers de script. Cet utilitaire utilise ODBC pour communiquer avec le serveur.

Syntaxe: osql [-?] | [-L] | [ { {-Ulogin_id [-Ppassword]} | -E } [-Sserver_name[\instance_name]] [-Hwksta_name] [-ddb_name] [-ltime_out] [-ttime_out] [-hheaders] [-scol_separator] [-wcolumn_width] [-apacket_size] [-e] [-I] [-D data_source_name] [-ccmd_end] [-q "query"] [-Q"query"] [-n] [-merror_level] [-r {0 | 1}] [-iinput_file] [-ooutput_file] [-p] [-b] [-u] [-R] [-O] ]

Arguments -? Affiche un résumé de la syntaxe des commutateurs osql . -L Répertorie tous les serveurs configurés localement et les noms des serveurs émettant sur le réseau. -U login_id ID de connexion de l'utilisateur. Les ID de connexion respectent la casse. -P mot de passe Spécifie le mot de passe pour l'utilisateur. Si l’option -P n’est pas utilisée, osql invite à entrer un mot de passe. Si l’option -P est utilisée à la fin de la ligne de commande sans spécifier de mot de passe, osql emploie le mot de passe par défaut (NULL).

Pour se connecter à un serveur MS SQL Server avec osql : Avec un login SQL Server spécifique : C:\> osql -U -P -S Exemple : C:\> osql -Usa -Psa_pwd -SCGC\DBA_T1_MSQ

Avec une authentification NT (ou mode trusted connection), dans ce cas là aucun mot de passe n'est pas exigé : C:\> osql -E -S

Exemple : C:\> osql -E -SCGC\DBA_T1_MSQ

Les Commandes Une fois connecté sur le serveur SQL, OSQL possède quelques commandes qui lui sont propres et qui permettent de travailler avec cet outil. • GO : Lance l'exécution du script TSQL tapé • EXIT ou QUIT : Quitte OSQL • RESET : Supprime toutes les commandes tapées dans OSQL (le numéro de la ligne repasse alors à 1) • CTRL+C : Stoppe l'exécution en cours sans quitter OSQL

La commande ED lance l'éditeur de texte par défaut de la machine. Celui-ci est de base EDIT.COM, éditeur de texte en version MS-DOS. Pour changer cela il faut exécuter la commande "SET EDITOR=notepad" par exemple (pour avoir Notepad de base) dans une console MS-DOS.

Exercice1 : • Utiliser osql pour se connecter au serveur . • Utiliser osql pour implémenter la base de donnée Etude suivante : Etudiant (id_Etud, Nom_Etud, Prenom_Etud) Module (N°Module, Intitule_Mod) Suivre (#id_Etud, # N°Module, Heure_Deb, Heure_Fin) • 1. 2. 3. 4.

Utiliser osql pour exécuter les requêtes suivantes : Liste des Etudiants Liste des Modules L’intitulé des modules suivis par l’Etudiant « Aissani » « Omar » Heure début et heure de la fin du module « sql server » suivi par l’etudiant « Hakmi Taher »

Exemple :

• Exécuter un script SQL par l’intermédiaire de l’utilitaire osql : osql -S MOUKEN\SQLEXPRESS2008 -E -i C:\Users\mouke\Desktop\Cours\essai.sql

- E : connexion approuvé -i : fichier d’entrée

Enregistrement de la sortie dans un fichier texte La commande suivante se connecte à SQL Server à l'aide de l'authentification Windows, exécute le fichier après l'argument –i et enregistre les résultats d'exécution dans le fichier après l'argument –o. osql -S MOUKEN\SQLEXPRESS2008 -E -i essai2.sql -o C:\Users\mouke\Desktop\Cours\essai3.txt

Exercice2 : • Utiliser osql pour se connecter au serveur . • Crée un script sql (En Transact SQL) avec l’extension .sql pour implémenter la base de donnée précédente . • Utiliser la commande osql pour exécuter le script sur votre serveur • Utiliser la commande osql pour exécuter le script sur votre serveur et exporter le résultat des requêtes dans un fichier texte.

Utilitaire bcp

L’utilitaire bulk copy program (bcp) copie en bloc des données entre une instance de Microsoft SQL Server et un fichier de données dans un format spécifié par l’utilisateur. L’utilitaire bcp permet d’importer un grand nombre de nouvelles lignes dans des tables SQL Server ou d’exporter des données de tables dans des fichiers de données. Pour importer des données dans une table, vous devez utiliser un fichier de format créé pour cette table ou comprendre la structure de la table et les types de données valides pour ses colonnes. Notes Si vous utilisez bcp pour sauvegarder vos données, créez un fichier de format pour enregistrer le format de données. Les fichiers de données bcp n’incluent pas de schéma ni d’informations de format, ce qui fait que si une table ou une vue est supprimée et si vous n’avez pas de fichier de format, il se peut que vous ne soyez pas en mesure d’importer les données.

L'utilitaire bcp dans le client Microsoft SQL Server permet d'exporter et importer des données en mode natif ou en mode caractères.

Dans le jargon classique : • "bcp out" désigne l'export des données dans un fichier avec bcp. • "bcp in" correspond à l'import des données dans une base depuis un fichier avec bcp.

L'utilitaire bcp est disponible dans le répertoire C:\Program Files\Microsoft SQL Server\\tools\binn, répertoire inscrit dans la variable %PATH% de l'environnement Windows.

Syntaxe

bcp [database_name.] schema.{table_name | view_name | "query"} {in data_file | out data_file | queryout data_file | format nul}

[-a packet_size] [-b batch_size] [-c] [-C { ACP | OEM | RAW | code_page } ] [-d database_name] [-D] [-e err_file] [-E] [-f format_file] [-F first_row] [-G Azure Active Directory Authentication] [-h"hint [,...n]"] [-i input_file] [-k]

[-K application_intent] [-l login_timeout] [-L last_row] [-m max_errors] [-n] [-N] [-o output_file] [-P password] [-q] [-r row_term] [-R] [-S [server_name[\instance_name]] [-t field_term] [-T] [-U login_id] [-v] [-V (80 | 90 | 100 | 110 | 120 | 130 ) ] [-w] [-x]

Arguments data_file Chemin d'accès complet du fichier de données. Lors de l'importation en bloc de données vers SQL Server, le fichier de données contient les données à copier dans la table ou vue spécifiée. Lors de l'exportation en bloc de données à partir de SQL Server, le fichier de données contient les données provenant de la table ou vue. Le chemin d'accès peut compter entre 1 et 255 caractères. Le fichier de données peut contenir jusqu’à 2^63 - 1 lignes. database_name Nom de la base de données qui contient la table ou la vue spécifiée. Sans autre indication, il s'agit de la base de données par défaut de l'utilisateur. Vous pouvez aussi spécifier explicitement le nom de la base de données avec d- .

in data_file | out data_file | queryout data_file | format nul Direction de la copie en bloc : • in copie à partir d’un fichier dans une table ou une vue de la base de données. • out copie dans un fichier à partir d’une table ou d’une vue de la base de données. Si vous spécifiez un fichier existant, il est remplacé. Lors de l’extraction des données, notez que l’utilitaire bcp représente une chaîne vide comme une chaîne Null, et une chaîne Null comme une chaîne vide. • queryout copie à partir d’une requête et doit être spécifié uniquement lors d’une copie de données en bloc à partir d’une requête.

. format crée un fichier de format basé sur l’option spécifiée ( -n, -c, -wou -N) et les délimiteurs de table ou de vue. Lors d’une copie en bloc de données, la commande bcp peut se référer à un fichier de format, ce qui permet d’éviter de ressaisir les informations de format de manière interactive. L’option format nécessite l’option -f ;

Export de données bcp out La syntaxe pour exporter une table ou une vue dans un fichier fichier.txt est la suivante :

DOS> bcp basededonnees.proprietaire.matable out fichier.txt Sserveur [-Uutilisateur] [-Pmotdepasse] [-T] [-t separateur de champs] [-r separateur de ligne] [-c] [-n] Le mot clé "out" indique le mode export de données. Les options -c (mode caractères) et -n (mode natif) sont exclusives l'une de l'autre.

Exemple : Pour exécuter les commandes BCP suivantes sous votre base de données de test, vous pouvez devez la table des employés suivante. Modifiez le chemin d'accès au fichier en conséquence. CREATE TABLE dbo.Employee ( Id INT PRIMARY KEY, Name VARCHAR(255), Salary INT );

INSERT INTO dbo.Employee Values (1, 'Vish', 1000); INSERT INTO dbo.Employee Values (2, 'Shailesh', 5000); INSERT INTO dbo.Employee Values (3, 'Vikrant', 7000); INSERT INTO dbo.Employee Values (4, 'Chetan', 12000); SELECT * FROM Pilot2019.dbo.Employee;

Exporter les données d'une table vers un fichier de données

Considérons que nous avons une table Employee dans notre base de données de test. Nous allons exporter les données en utilisant la direction OUT. Un fichier texte sera créé sur notre disque dur avec les données de la table des employés. Si le nom de fichier existe déjà, il doit être remplacé. Sinon, un nouveau fichier sera créé. BCP Pilot2019.dbo.Employee OUT « F:\SQLServerFiles\EmployeeExport.txt" -T -S MOUKEN\SQLEXPRESS2008 -t, -c

Exporter les données d'une table vers un fichier de données délimité par des barres verticales : Dans l'exemple suivant, utilisation du nom de la base de données, du nom d'utilisateur et du mot de passe pour exporter la table des employés vers le fichier EmployeeExport.txt. N'oubliez pas que cela écrasera notre fichier séparé par des virgules généré dans l'exemple précédent. BCP Pilot2019.dbo.Employee OUT " F:\SQLServerFiles\EmployeeExport.txt" -T -S MOUKEN\SQLEXPRESS2008 –t "|" -c

Pour spécifier des séparateurs non standards, par exemple le point virgule comme séparateur de champs et #\r\n comme séparateur de lignes : utiliser les arguments -t et -r. BCP Pilot2019.dbo.Employee OUT " F:\SQLServerFiles\EmployeeExport.txt" -T -S MOUKEN\SQLEXPRESS2008 –t";" -r"#\r\n" -c

bcp out en mode authentification Windows Server:

Exporter les résultats d'une requête avec bcp et l'option queryout Le binaire bcp de SQL Server permet d'exporter des requêtes ou des jeux de résultats de procédures stockées avec l'option queryout. Cette option queryout permet notamment de s'affranchir de la création de vues ou d'exporter très simplement les résultats d'une procédure stockée effectuant des dénormalisations. DOS> bcp "requete" queryout fichier.txt -Sserveur [-Uutilisateur] [-Pmotdepasse] [-T] [-t separateur de champs] [-r separateur de ligne] [-c] [-n]

Quelques exemples très simples :

Exporter une table nommée par exemple AUM_PORTFOLIO avec des clauses WHERE DOS> bcp "select * from FinanceKiosk.dbo.AUM_PORTFOLIO where PortfolioID > 50000" queryout fichier.bcpc -S SRVWINFR1 -Usa P****** -t";" -c Travail à faire: Reprenez la base de donnée pilote 2019 et exporter sous forme de requête SQL le nom des employés dans fichier texte.

Import de données bcp in La syntaxe pour importer dans une table les données provenant d'un fichier fichier.txt est la suivante : DOS> bcp basededonnees.proprietaire.matable in fichier.txt -Sserveur [-Uutilisateur] [-Pmotdepasse] [-T] [-t separateur de champs] [-r separateur de ligne] [-b] [-E] [-c] [-n] Le mot clé "in" indique le mode import de données. Les options -c (mode caractères) et -n (mode natif) sont exclusives l'une de l'autre.

Comme pour l'export de données avec bcp out : • les séparateurs de colonnes et de lignes sont respectivement indiqués avec les options -t et -r si ceux ci ne sont pas les séparateurs par défaut (tabulation pour le séparateur de colonnes et \r\n pour le séparateur de lignes). • L'option -T (trusted connection) permet de réaliser l'opération en authentification intégrée Windows. • L'option -S s'écrit -S\ dans le cas d'une instance nommée (ex. : -SFRDMOS105\MOSS_DATA01). Pour importer par exemple dans la table AUM_PORTFOLIO les données provenant d'un fichier texte pour lequel le séparateur de champs est le point virgule et le séparateur de lignes #\r\n : fichier.bcpc1; MQ;1# 9; IPS;1#

DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -r"#\r\n" -c

Exemple: Jusqu'à présent, vous avez vu des exemples pour exporter des données d'une table vers un fichier. Dans l'exemple suivant, nous importons des données d'un fichier vers la table. Nous devons spécifier la direction comme IN à importer. BCP Pilot2019.dbo.Employee IN " F:\SQLServerFiles\EmployeeExport.txt" -T -S MOUKEN\SQLEXPRESS2008 -t"|"

bcp in et l'option -b (batch size) : éviter la saturation du journal (log full)

L'import avec bcp dans une table comportant des indexes, des colonnes de type text ou des déclencheurs (triggers) est journalisé. En fonction de la volumétrie à importer, la taille du journal de transactions de la base de données peut être insuffisante. DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -c 1000 rows sent to SQL Server. Total sent: 120000 1000 rows sent to SQL Server. Total sent: 121000 La saturation du journal est notifiée par le binaire bcp dans la sortie standard avec l'erreur 9002. SQLState = 42000, NativeError = 9002 Error = [Microsoft][SQL Native Client][SQL Server]The transaction log for database 'FinanceKiosk' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

L'option -b (batch size) de la commande bcp in permet d'éviter ces écueils et ordonne de lancer la commande commit toutes les n lignes importées. Pour lancer un commit toutes les 10000 lignes : spécifier -b 10000. DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -b10000 -c

Le dimensionnement du paramètre "batch size" -b va dépendre de la taille du journal de la base de données et de la structure de la table (indexes, colonnes text etc...).

bcp in et l'option -E (colonnes identity) Lorsque la table contient une colonne de type identity, l'option -E doit être indiquée dans la commande bcp in si l'on souhaite que les identifiants dans le fichier source soient conservés lors de l'import. Sans l'option -E, de nouvelles valeurs pour la colonne identity sont attribuées. DOS> bcp FinanceKiosk.dbo.AUM_PORTFOLIO in fichier.bcpc -S SRVWINFR1 -Usa -P****** -t";" -b10000 -E -c L'option -E équivaut à la commande Transact SQL set identity_insert

on | off. L'option -E peut être indiquée dans la commande bcp in même si la table ne contient pas de colonne de type identity. Depuis SQL Server 2005, si la commande bcp est exécutée avec un utilisateur disposant de droits plus restreints que le rôle dbo (database owner), le droit ALTER doit être donné à cet utilisateur sur la table contenant la colonne identity. Sans ce droit, la commande bcp avec l'option -E est en échec avec le message 1088.