APSQL

SQLPS ou le Power Shell pour SQL Server

Le power shell est intégré à Windows Server 2008 et il permet de réaliser des scripts afin d'automatiser les tâches administratives. Pour obtenir plus d'informations par rapport au power shell en lui même le plus simple est de se rendre directement sur le site www.powershell-scripting.com.

Le power shell en version standard permet donc de réaliser les différentes tâches administratives propres à Windows Server. Pour cela en plus des éléments de langage (définition de variable, structure de contrôles, …) des command-let ont était ajoutées pour permettre la gestion des objets spécifiques à la plateforme. Pour finir cette brève et rapide description du power shell il est nécessaire de spécifier qu'il s'appuie sur le framework .net (et donc il bénéficie de tous les apports du framework) et qu'il n'existe que des objets.

SQLPS

Ce power shell "de base" est donc enrichi afin de permettre la réalisation des opérations d'administration des Serveurs MSSQL ainsi que des bases. Pour réaliser ces tâches Power shell bénéficie bien sur de la bibliothèque SMO (car il s'appui sur le framework .Net) mais en plus 2 command-let ont était ajouté (…) ainsi qu'un fournisseur d'accès. Pour exécuter ce power shell adapté à SQL Server, il est nécessaire d'exécuter le programmer sqlps soit directement depuis l'invite de commande, soit depuis le menu Démarrer – exécuter soit enfin depuis SSMS (SQL Server Managament Studio).

Démarrer SQLPS depuis SSMS

L'avantage de lancer l'exécution depuis SSMS est que le power Shell se trouve positionné par défaut sur l'élément depuis lequel la demande d'exécution à était formulée.

Quelque soit la façon d'exécuter sqlps, il est configuré en mode restreint par défaut. Cela signifie que par défaut sqlps permet de travailler en mode interactif mais il n'offre pas la possibilité d'exécuter des scripts. Pour que cela soit possible, il est nécessaire de faire appel à l'applet Set-ExecutionPolicy ( http://technet.microsoft.com/en-us/library/dd347628.aspx ).

Travailler avec une instance

Lors de l'exécution de SQLPS , il est nécessaire dans un premier temps de se connecter à une instance afin de pourvoir y travailler. La première instruction à saisir est donc Get-ChildItem ou dir (qui est un alias pour la command-let Get-ChildItem). Le résultat observé est alors le suivant:

Exemple de Get-ChildItem

Les dossiers

Le fournisseur PowerShell pour SQL Server fournis donc 3 dossiers liés à la l'administration du server. Chaque dossier représente un ensemble d'objets qu'il est possible de manipuler avec SMO.

Dossier SQL

Ce dossier regroupe tous les objets des bases de données tels les tables, vues, procédures … . Pour travailler avec les objets disponibles sont ceux des espaces de noms: Microsoft.SqlServer.Management.Smo, Microsoft.SqlServer.Management.Smo.Agent, Microsoft.SqlServer.Management.Smo.Broker et Microsoft.SqlServer.Management.Smo.Mail.

Dossier SQLPolicy

Ce dossier représente toutes règles qui peuvent être définies dans le cadre d'une administration par les règles. Les espaces de noms SMO associés à ce dossier sont Microsoft.SqlServer.Management.Dmf et Microsoft.SqlServer.Management.Facets .

Dossier SQLRegistration

Ce dossier représente tous les éléments relatifs à l'enregistrement des instances SQL Server ainsi qu'à leur organisation en groupe. Pour manipuler ces informations les objets SMO des espaces de noms Microsoft.SqlServer.Management.RegisteredServers et Microsoft.SqlServer.Management.Smo.RegSvrEnum sont disponibles.

Se Connecter à une instance

Dans le cas présent pour se connecter à l'instance par défaut présente sur le serveur ARAVIS il est nécessaire d'utilisateur l'instruction Set-Location (l'alias de cette commande est cd). Pour établir la connexion à l'instance SQL Server, le contexte de sécurité utilisé est celui du compte Windows qui exécute l'outil sqlps.

SQLPS connexion à l'instance

La distinction entre majuscules et minuscules n'est pas prise en compte

Accéder plus rapidement aux informations avec un lecteur

Pour permettre de travailler efficacement et rapidement avec les différents composants du serveur, il est possible de définir des lecteurs. Un lecteur Power Shell va permettre d'identifier un nœud précis par un nom. Par exemple dans le cas présent, il est avantageux de définir un lecteur afin de se positionner directement sur l'instance SQL Server par défaut du serveur. Pour définir ce lecteur, le Power Shell utilise l'instruction New-PSDrive.

Par exemple l'instruction suivante permet de définir un lecteur correspondant à l'instance par défaut.

New-PSDrive -Name MSSQL -Root SQLSERVER:\Sql\Aravis\Default

Une fois ce lecteur définit pour se positionner sur la base PS il suffit par exemple d'exécuter:

Set-Location MSSQL:\Databases\PS

Quels sont les éléments accessibles?

Tous les éléments définis au niveau du serveur sont accessibles par l'intermédiaire de la bibliothèque SMO. De même il est possible de gérer l'ensemble des éléments de chaque base de données. Les données sont par contre manipulées grâce à l'applet Invoke-Sqlcmd. Cette applet permet d'exécuter les scripts sqlcmd mais aussi Transact SQL ou bien XQuery.

Pour illustrer les possibilités offertes par le Power Shell de SQL Server, le plus simple est de prendre quelques exemples précis:

Etablir la liste des connexions

Pour connaitre la liste des connexions (logins) définies sur un serveur, il est nécessaire d'explorer le dossier logins situé à la racine du serveur. Soit dans le cas présent:

Get-Item Logins | Get-ChildItem -Name

Etablir la liste des bases

En respectant le même principe de syntaxe il est possible d'obtenir la liste des bases à l'aide de l'instruction

Get-Item Databases | Get-ChildItem -Name

Cependant cette instruction ne permet pas de connaitre toutes les bases mais simplement les bases de données utilisateur. Pour établir la liste de toutes les bases (y compris les bases systèmes) il est nécessaire d'utiliser l'option force:

Get-Item Databases | Get-ChildItem –Name -force

Etablir la liste des tables

Vous l'avez compris se promener dans la structure d'un serveur ou d'une base avec Power Shell est très simple et s'effectue de façon quasi naturelle avec les applets Set-Location, Get-Item et Get-ChildItem. Pour accèder la liste des tables il est possible de saisir l'instruction suivante

Get-Item databases\adventureWorks2008\tables | Get-ChildItem

 

Ces quelques exemples permettent d'illustrer la puissance du power shell et la facilité avec laquelle il est possible de collecter des informations sur la structure d'un serveur.

La prochaine étape va donc consister à regarder de plus près comment il est possible de modifier/créer directement des composants sur le serveur.