Partager via


Utilisation du fournisseur PowerShell SQL Server

Le fournisseur Windows PowerShell SQL Server présente la hiérarchie des objets SQL Server dans des chemins d'accès semblables aux chemins d'accès de système de fichiers. Vous pouvez utiliser les chemins d'accès pour localiser un objet, puis utiliser des méthodes des modèles objets de gestion SQL Server (SMO) pour effectuer des actions sur les objets.

Hiérarchie des fournisseurs 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 sont répertoriés dans le tableau suivant.

Dossier

Espace de noms du modèle objet SQL Server

Objets

SQLSERVER:\SQL

Microsoft.SqlServer.Management.Smo

Microsoft.SqlServer.Management.Smo.Agent

Microsoft.SqlServer.Management.Smo.Broker

Microsoft.SqlServer.Management.Smo.Mail

Objets de base de données, tels que les tables, les vues et les procédures stockées.

SQLSERVER:\SQLPolicy

Microsoft.SqlServer.Management.Dmf

Microsoft.SqlServer.Management.Facets

Objets de la Gestion basée sur des stratégies, tels que les stratégies et les facettes.

SQLSERVER:\SQLRegistration

Microsoft.SqlServer.Management.RegisteredServers

Microsoft.SqlServer.Management.Smo.RegSvrEnum

Objets de serveurs inscrits, tels que des groupes de serveurs et des serveurs inscrits.

SQLSERVER:\DataCollection

Microsoft.SqlServer.Management.Collector

Objets du collecteur de données tels que les jeux d'éléments de collecte et magasins de configuration.

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'AdventureWorks). 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 AdventureWorks dans une instance par défaut du moteur de base de données sur l'ordinateur local :

SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\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.

Chemin d'accès

Classe SMO

SQLSERVER:\SQL\MyComputer\DEFAULT\Databases

DatabaseCollection

SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks

Database

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.

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.

Applet de commande

Alias canonique

Alias cmd

Alias de l'interpréteur de commandes UNIX

Description

Get-Location

gl

pwd

pwd

Obtient le nœud actuel.

Set-Location

sl

cd, chdir

cd, chdir

Modifie le nœud actuel.

Get-ChildItem

gci

dir

ls

Répertorie les objets stockés sur le nœud actuel.

Get-Item

gi

Retourne les propriétés de l'élément actuel.

Move-Item

mi

move

mv

Déplace un objet.

Rename-Item

rni

rn

ren

Renomme un objet.

Remove-Item

ri

del, rd

rm, rmdir

Supprime un objet.

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:.

Emplacement de chemin d'accès

Résultats de Get-ChildItem

SQLSERVER:\SQL

Retourne le nom de l'ordinateur local. Si vous avez utilisé SMO ou WMI pour vous connecter aux instances du moteur de base de données sur d'autres ordinateurs, ces ordinateurs sont également répertoriés.

SQLSERVER:\SQL\ComputerName

Liste des instances du moteur de base de données sur l'ordinateur.

SQLSERVER:\SQL\ComputerName\InstanceName

Liste des types d'objets de niveau supérieur dans l'instance, tels que les points de terminaison, les certificats et les bases de données.

Nœud de classes d'objets, tels que Databases

Liste des objets de ce type, telle que la liste des bases de données : MASTER, Model, AdventureWorks.

Nœud de noms d'objets, tels qu'AdventureWorks

Liste des types d'objets contenus dans l'objet. Par exemple, une base de données répertorierait des types d'objets tels que les tables et les vues.

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

Vous pouvez utiliser l'applet de commande Get-Member pour afficher les méthodes et propriétés disponibles pour des objets ou classes d'objets spécifiques.

L'exemple suivant montre comment afficher la liste des méthodes de la classe Database SMO :

[Microsoft.SqlServer.Management.SMO.Database] | Get-Member -Type Methods

L'exemple suivant affecte à une variable Windows PowerShell la classe Database SMO et répertorie les propriétés :

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

Vous pouvez également utiliser Get-Member pour répertorier les méthodes et les propriétés associées au nœud de fin d'un chemin d'accès Windows PowerShell SQL Server.

L'exemple suivant accède au nœud Databases d'un chemin d'accès SQLSERVER: et répertorie les propriétés de collection :

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

L'exemple suivant accède au nœud AdventureWorks d'un chemin d'accès SQLSERVER: et répertorie les propriétés d'objet :

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

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

Vous pouvez référencer des propriétés SMO dans les commandes Windows PowerShell. L'exemple suivant utilise la propriété SMO Schema pour obtenir la liste des tables du schéma Sales dans AdventureWorks :

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

L'exemple suivant utilise la méthode SMO Script pour générer un script qui contient les instructions CREATE VIEW que vous devez avoir pour recréer les vues dans AdventureWorks :

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

L'exemple suivant utilise la méthode SMO Create pour créer une base de données, puis utilise la propriété State pour indiquer si la base de données existe :

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 Windows PowerShell ou lecteurs ps. Ces lecteurs 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 AdventureWorks, vous pouvez créer un lecteur AWDB: :

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

Vous pouvez ensuite utiliser le lecteur AWDB: pour raccourcir le chemin d'accès à des objets AdventureWorks, 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 au 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"

[!REMARQUE]

Pour utiliser les classes dans cet espace de noms sur des ordinateurs distants, vous devez configurer votre Pare-feu Windows de manière à autoriser les connexions WMI DCOM. Pour plus d'informations, consultez Configuration du Pare-feu Windows pour autoriser l'accès à SQL Server.

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*

Historique des modifications

Mise à jour du contenu

Correction des erreurs de syntaxe dans les blocs de code qui font référence à Smo.Database. Suppression des références au lecteur SQL: qui a été remplacé par le lecteur SQLSERVER:.