Utilisation du fournisseur PowerShell SQL Server

Hiérarchie PowerShell SQL Server

Les produits dont les données ou modèles objets peuvent être représentés dans une hiérarchie utilisent des fournisseurs Windows PowerShell pour exposer les hiérarchies. La hiérarchie est exposée à l'aide d'une structure de chemin d'accès semblable à celle utilisée par le système de fichiers Windows.

Chaque fournisseur Windows PowerShell implémente un ou plusieurs lecteurs. Chaque lecteur est le nœud racine d'une hiérarchie d'objets connexes. Le fournisseur SQL Server implémente un lecteur SQLSERVER:. Le lecteur SQLSERVER: comprend quatre dossiers principaux. Chaque dossier et ses sous-dossiers représentent l'ensemble d'objets auxquels il est possible d'accéder à l'aide d'un modèle SMO (SQL Server Management Objects). Si vous vous trouvez au niveau d'un sous-dossier dans un chemin d'accès qui commence par l'un de ces dossiers principaux, vous pouvez utiliser les méthodes du modèle objet associé pour effectuer des actions sur l'objet représenté par ce nœud. Les dossiers Windows PowerShell implémentés par le fournisseur SQL Server 2008 R2 sont répertoriés dans le tableau suivant.

Par exemple, vous pouvez utiliser le dossier SQLSERVER:\SQL pour commencer des chemins d'accès pouvant représenter tout objet pris en charge par le modèle objet SMO. La partie de tête d'un chemin d'accès SQLSERVER:\SQL est SQLSERVER:\SQL\ComputerName\InstanceName. Vous devez spécifier un nom d'ordinateur. Vous pouvez spécifier soit localhost soit `(local`) pour l'ordinateur local. Vous devez toujours spécifier le nom de l'instance, même pour les instances par défaut. Pour ces dernières, spécifiez DEFAULT. Les nœuds après le nom de l'instance alternent entre des classes d'objets (telles que Database ou View) et des noms d'objets (tels qu'AdventureWorks2008R2). Les schémas ne sont pas représentés en tant que classes d'objets. Lorsque vous spécifiez le nœud pour un objet de niveau supérieur dans un schéma, tel qu'une table ou une vue, vous devez spécifier le nom d'objet au format SchemaName.ObjectName.

Voici le chemin d'accès à la table Vendor dans le schéma Purchasing de la base de données AdventureWorks2008R2 dans une instance par défaut du Moteur de base de données sur l'ordinateur local :

SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables\Purchasing.Vendor

Pour plus d'informations sur la hiérarchie du modèle objet SMO, consultez Diagramme du modèle objet SMO.

Les nœuds de classes d'objets dans un chemin d'accès sont associés à une classe de collection dans le modèle objet associé. Les nœuds de noms d'objets sont associés à une classe d'objet dans le modèle objet associé, comme indiqué dans le tableau suivant.

Chaque fois que vous référencez une instance du moteur de base de données dans un chemin d'accès, le fournisseur SQL Server utilise SMO pour ouvrir une connexion via l'authentification Windows à l'instance. La connexion est établie à l'aide des informations d'identification du compte Windows qui exécute la session Windows PowerShell. Le fournisseur SQL Server n'utilise pas l'authentification SQL Server.

Navigation dans des chemins d'accès SQL Server

Windows PowerShell implémente des applets de commande pour naviguer dans des hiérarchies de fournisseur et effectuer des opérations de base sur l'objet actuel. Étant donné que les applets de commande sont fréquemment utilisées, elles ont des alias canoniques courts. Il existe également un ensemble d'alias qui mappent les applets de commande à des commandes semblables destinées à l'invite de commandes, et un autre ensemble pour les commandes de l'interpréteur de commandes UNIX.

Le fournisseur SQL Server implémente un sous-ensemble des applets de commande du fournisseur, comme indiqué dans le tableau suivant.

Par exemple, vous pouvez utiliser l'un des jeux d'applets de commande ou ensembles d'alias suivants pour récupérer la liste des instances SQL Server disponibles si vous accédez au dossier SQLSERVER:\SQL et si vous demandez la liste des éléments enfants de ce dossier :

• En utilisant des noms d'applets de commande complets :

  Set-Location SQLSERVER:\SQL
  Get-ChildItem
  

• En utilisant des alias canoniques :

  sl SQLSERVER:\SQL
  gci
  

• En utilisant des alias cmd :

  cd SQLSERVER:\SQL
  dir
  

• En utilisant des alias de l'interpréteur de commandes UNIX :

  cd SQLSERVER:\SQL
  ls
  

  Important

  Certains identificateurs (noms d'objets) SQL Server contiennent des caractères que Windows PowerShell ne prend pas en charge dans les noms de chemins d'accès. Pour plus d'informations sur l'utilisation de noms qui contiennent ces caractères, consultez Utilisation d'identificateurs SQL Server dans PowerShell.

Utilisation de Get-ChildItem

Les informations retournées par Get-ChildItem (ou ses alias dir et ls) dépendent de votre emplacement dans un chemin d'accès SQLSERVER:.

Par défaut, Get-ChildItem ne répertorie pas d'objets système. Utilisez le paramètre Force pour afficher les objets système, tels que les objets dans le schéma sys.

Cet exemple répertorie l'ordinateur local et tout ordinateur avec lesquels vous avez établi une connexion SMO ou WMI :

Set-Location SQLSERVER:\SQL
Get-ChildItem

L'exemple suivant répertorie les instances du moteur de base de données sur l'ordinateur local :

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

L'exemple suivant répertorie les classes principales des objets disponibles dans une instance par défaut du moteur de base de données. Cette liste inclut des noms tels que Points de terminaison, Certificats et Bases de données :

Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem

L'exemple suivant répertorie les bases de données disponibles dans une instance par défaut du moteur de base de données. Le paramètre Force est utilisé pour inclure les bases de données système telles que master et model :

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force

Exécution d'actions sur des nœuds de chemin d'accès

Après avoir accédé à un nœud dans un chemin d'accès Windows PowerShell, vous pouvez effectuer deux types d'actions :

• Vous pouvez exécuter des applets de commande Windows PowerShell qui s'appliquent à des nœuds, telles que Rename-Item.

• Vous pouvez appeler les méthodes du modèle objet SMO (SQL Server Management Objects) associé, tel que SMO. Par exemple, si vous accédez au nœud Databases dans un chemin d'accès, vous pouvez utiliser les méthodes et propriétés de la classe Database.

Le fournisseur SQL Server est utilisé pour gérer les objets dans une instance du moteur de base de données. Il n'est pas utilisé pour travailler avec les données de bases de données. Si vous avez accédé à une table ou une vue, vous ne pouvez pas utiliser le fournisseur pour sélectionner, insérer, mettre à jour ou supprimer des données. Utilisez l'applet de commande Invoke-Sqlcmd pour interroger ou modifier des données dans des tables et des vues à partir de l'environnement Windows PowerShell. Pour plus d'informations, consultez Utilisation de l'applet de commande Invoke-Sqlcmd.

Affichage de la liste des méthodes et des propriétés

$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member –Type Methods
$MyDBVar | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2
Get-Item . | Get-Member -Type Properties

Utilisation des méthodes et des propriétés

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar.Parent = (Get-Item ..)
$MyDBVar.Name = "NewDB"
$MyDBVar.Create()
$MyDBVar.State

Définition de lecteurs personnalisés

Windows PowerShell permet aux utilisateurs de définir des lecteurs virtuels appelés lecteurs PowerShell, qui sont mappés aux nœuds de démarrage d'une instruction de chemin d'accès. Ils sont généralement utilisés pour raccourcir les chemins d'accès fréquemment tapés. Les chemins d'accès SQLSERVER: peuvent devenir longs, occupant beaucoup de place dans la fenêtre Windows PowerShell et nécessitant beaucoup de saisie. Si vous allez beaucoup travailler sur un nœud de chemin d'accès particulier, vous pouvez définir un lecteur Windows PowerShell personnalisé qui mappe à ce nœud. Par exemple, si vous travaillez beaucoup dans la base de données AdventureWorks2008R2, vous pouvez créer un lecteur AWDB :

New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks2008R2

Vous pouvez ensuite utiliser le lecteur AWDB: pour raccourcir le chemin d'accès à des objets AdventureWorks2008R2, tels que la table Purchasing.Vendor :

Set-Location AWDB:\Tables\Purchasing.Vendor

Gestion des connexions via l'authentification SQL Server

Par défaut, le fournisseur SQL Server utilise le compte Windows sous lequel il s'exécute pour établir une connexion via l'authentification Windows avec le Moteur de base de données. Pour établir une connexion via l'authentification SQL Server, vous devez associer les informations d'identification de connexion SQL Server à un lecteur virtuel, puis exécuter la commande de changement de répertoire (cd) pour passer à ce lecteur. Dans Windows PowerShell, les informations d'identification de sécurité peuvent être associées uniquement à des lecteurs virtuels.

Ce script crée une fonction nommée sqldrive que vous pouvez utiliser pour créer un lecteur virtuel associé à la connexion via l'authentification SQL Server et à l'instance spécifiées.

function sqldrive
{
    param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
    $pwd = read-host -AsSecureString -Prompt "Password"
    $cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
    New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
}

Vous pouvez ensuite créer un lecteur virtuel nommé SQLAuth: en exécutant cette commande :

sqldrive SQLAuth

La fonction sqldrive vous invite à entrer le mot de passe de votre connexion, en masquant celui-ci à mesure que vous le tapez. Ensuite, chaque fois que vous exécutez la commande de changement de répertoire (cd) pour vous connecter à un chemin d'accès via le lecteur SQLAuth:, toutes les opérations sont effectuées en utilisant les informations d'identification de la connexion via l'authentification SQL Server que vous avez fournies lors de la création du lecteur.

Utilisation de l'espace de noms Microsoft.SqlServer.Managment.Smo.Wmi

En plus des espaces de noms du modèle SMO (SQL Server Management Objects) associés aux dossiers \SQL, \SQLPolicy et \SQLRegistration, vous pouvez utiliser des classes dans l'espace de noms Microsoft.SqlServer.Management.Smo.Wmi. Cet espace de noms est le plus souvent utilisé pour interroger et gérer l'état des services qui sont implémentés par chaque instance du moteur de base de données ou un magasin de stratégies.

Cet exemple montre comment utiliser la classe ManagedComputer pour arrêter et démarrer le service exécutant une instance par défaut du moteur de base de données.

# Get a reference to the ManagedComputer class.
cd SQLSERVER:\SQL\localhost
$Wmi = (get-item .).ManagedComputer
# Display the object properties.
$Wmi
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services["MSSQLSERVER"]
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop(); write-host "Stopped"
# Refresh the cache and look at the state.
$DfltInstance.Refresh(); $DfltInstance
# Start the service again.
$DfltInstance.Start(); write-host "Started"

Gestion de la saisie semi-automatique par tabulation

La saisie semi-automatique par tabulation de Windows PowerShell réduit la quantité de caractères que vous devez taper. Lorsque vous avez tapé une partie d'un chemin d'accès ou d'un nom d'applet de commande, vous pouvez appuyer sur la touche TAB pour obtenir la liste des éléments dont les noms correspondent à ce que vous avez déjà tapé. Vous pouvez alors sélectionner l'élément souhaité dans la liste sans avoir à taper le reste du nom.

Si vous travaillez dans une base de données qui contient beaucoup d'objets, les listes de saisie semi-automatique par tabulation peuvent devenir très longues. Certains types d'objets SQL Server, tels que les vues, contiennent également de nombreux objets système.

Les composants logiciels enfichables SQL Server introduisent trois variables système que vous pouvez utiliser pour contrôler la quantité d'informations présentées via la saisie semi-automatique par tabulation et Get-ChildItem.

• **$SqlServerMaximumTabCompletion =**n
  Spécifie le nombre maximal d'objets à inclure dans une liste de saisie semi-automatique par tabulation. Si vous sélectionnez la touche TAB sur un nœud de chemin d'accès qui contient plus de n objets, la liste de saisie semi-automatique par tabulation est tronquée à n, n étant un entier. Le paramètre par défaut, 0, signifie que le nombre d'objets répertoriés est illimité.

• **$SqlServerMaximumChildItems =**n
  Spécifie le nombre maximal d'objets affichés par Get-ChildItem. Si Get-ChildItem est exécuté sur un nœud de chemin d'accès qui contient plus de n objets, la liste est tronquée à n, n étant un entier. Le paramètre par défaut, 0, signifie que le nombre d'objets répertoriés est illimité.

• $SqlServerIncludeSystemObjects = { $True | $False }
  Si la valeur est $True, les objets système sont affichés par tabulation et Get-ChildItem. Si la valeur est $ False, aucun objet système n'est affiché. Le paramètre par défaut est $False.

L'exemple suivant définit les trois variables et répertorie leurs paramètres :

$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*

Voir aussi

Concepts

Autres ressources