Articolo
12/15/2010

Utilizzo del provider PowerShell SQL Server

Il provider SQL Server Windows PowerShell espone la gerarchia degli oggetti di SQL Server in percorsi simili ai percorsi del file system. È possibile utilizzare i percorsi per individuare un oggetto, quindi utilizzare metodi dei modelli SMO (SQL Server Management Object) per eseguire azioni sugli oggetti.

Gerarchia del provider SQL Server

I prodotti i cui dati o modelli di oggetti possono essere rappresentati in una gerarchia utilizzano i provider Windows PowerShell per esporre le gerarchie. La gerarchia viene esposta utilizzando una struttura di unità e percorsi simile a quelle utilizzate dal file system di Windows.

Ciascun provider Windows PowerShell implementa una o più unità. Ciascuna unità è il nodo radice di una gerarchia di oggetti correlati. Il provider SQL Server implementa un'unità SQLSERVER:. Tale unità include quattro cartelle principali. Ogni cartella e le relative sottocartelle rappresentano il set di oggetti a cui è possibile accedere utilizzando un modello SMO (SQL Server Management Object). Quando si seleziona una sottocartella in un percorso che inizia con una di queste cartelle principali, è possibile utilizzare i metodi del modello di oggetti associato per eseguire azioni sull'oggetto rappresentato dal nodo. Le cartelle Windows PowerShell implementate dal provider SQL Server 2008 sono elencate nella tabella seguente:

Cartella	Spazio dei nomi del modello a oggetti di SQL Server	Oggetti
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent Microsoft.SqlServer.Management.Smo.Broker Microsoft.SqlServer.Management.Smo.Mail	Oggetti di database, come tabelle, viste e stored procedure.
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf Microsoft.SqlServer.Management.Facets	Oggetti di gestione basata sui criteri, come criteri e facet.
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers Microsoft.SqlServer.Management.Smo.RegSvrEnum	Oggetti server registrati, come gruppi di server e server registrati.
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	Oggetti dell'agente di raccolta dati, come insiemi di raccolta e archivi di configurazione.

È ad esempio possibile utilizzare la cartella SQLSERVER:\SQL per iniziare percorsi che possono rappresentare qualsiasi oggetto supportato dal modello di oggetti SMO. La parte iniziale di un percorso SQLSERVER:\SQL è SQLSERVER:\SQL\NomeComputer\NomeIstanza. È necessario specificare un nome di computer. È possibile specificare localhost o "(local)" per il computer locale. È necessario specificare sempre il nome delle istanze, anche per le istanze predefinite. Per le istanze predefinite, specificare DEFAULT. I nodi dopo il nome dell'istanza possono essere classi di oggetti, ad esempio Database o View, e nomi di oggetti, ad esempio AdventureWorks. Gli schemi non sono rappresentati come classi di oggetti. Quando si specifica il nodo per un oggetto di livello principale in uno schema, ad esempio una tabella o una vista, è necessario specificare il nome dell'oggetto nel formato NomeSchema.NomeOggetto.

Di seguito è ad esempio riportato il percorso della tabella Vendor nello schema Purchasing del database AdventureWorks in un'istanza predefinita del Motore di database nel computer locale:

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

Per ulteriori informazioni sulla gerarchia del modello di oggetti SMO, vedere Diagramma del modello di oggetti SMO.

I nodi delle classi di oggetti in un percorso sono associati a una classe di raccolte nel modello di oggetti associato. I nodi dei nomi di oggetti sono associati a una classe di oggetti nel modello di oggetti associato, come indicato nella tabella seguente.

Percorso	Classe SMO
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases	DatabaseCollection
SQLSERVER:\SQL\MyComputer\DEFAULT\Databases\AdventureWorks	Database

Quando si fa riferimento a un'istanza del Motore di database in un percorso, il provider SQL Server utilizza SMO per aprire una connessione di autenticazione di Windows all'istanza. La connessione viene effettuata utilizzando le credenziali dell'account di Windows che esegue la sessione di Windows PowerShell. Il provider SQL Server non utilizza l'autenticazione di SQL Server.

Spostamento nei percorsi di SQL Server

Windows PowerShell implementa cmdlet per lo spostamento tra le gerarchie del provider e per l'esecuzione di operazioni di base sull'oggetto corrente. Poiché vengono utilizzati frequentemente, i cmdlet dispongono di alias brevi e canonici. È inoltre presente un set di alias che esegue il mapping dei cmdlet a comandi simili del prompt dei comandi e un altro set per i comandi della shell di UNIX.

Il provider SQL Server implementa un subset di cmdlet del provider, illustrato nella tabella seguente.

cmdlet	Alias canonico	Alias cmd	Alias di shell di UNIX	Descrizione
Get-Location	gl	pwd	pwd	Consente di ottenere il nodo corrente.
Set-Location	sl	cd, chdir	cd, chdir	Consente di modificare il nodo corrente.
Get-ChildItem	gci	dir	ls	Consente di visualizzare un elenco degli oggetti archiviati nel nodo corrente.
Get-Item	gi			Restituisce le proprietà dell'elemento corrente.
Move-Item	mi	move	mv	Consente di spostare un oggetto.
Rename-Item	rni	rn	ren	Consente di rinominare un oggetto.
Remove-Item	ri	del, rd	rm, rmdir	Consente di rimuovere un oggetto.

È ad esempio possibile utilizzare uno dei set di cmdlet o alias seguenti per recuperare un elenco delle istanze di SQL Server disponibili passando alla cartella SQLSERVER:\SQL e richiedendo l'elenco di elementi figlio per la cartella:

Utilizzo di nomi di cmdlet completi:

Set-Location SQLSERVER:\SQL
Get-ChildItem

Utilizzo di alias canonici:
```
sl SQLSERVER:\SQL
gci
```
Utilizzo di alias cmd:
```
cd SQLSERVER:\SQL
dir
```

Utilizzo di alias della shell di UNIX:

cd SQLSERVER:\SQL
ls

Importante
Alcuni identificatori (nomi di oggetto) di SQL Server contengono caratteri non supportati da Windows PowerShell nei nomi dei percorsi. Per ulteriori informazioni sull'utilizzo dei nomi che contengono questi caratteri, vedere Utilizzo di identificatori di SQL Server in PowerShell.

Utilizzo di Get-ChildItem

Le informazioni restituite da Get-ChildItem o dai relativi alias dir e ls dipendono dalla posizione nel percorso SQLSERVER:.

Posizione nel percorso	Risultati di Get-ChildItem
SQLSERVER:\SQL	Restituisce il nome del computer locale. Se è stato utilizzato SMO o WMI per connettersi a istanze di Motore di database in altri computer, vengono elencati anche tali computer.
SQLSERVER:\SQL\NomeComputer	Elenco delle istanze di Motore di database nel computer.
SQLSERVER:\SQL\NomeComputer\NomeIstanza	Elenco dei tipi di oggetto di primo livello nell'istanza, ad esempio Endpoint, Certificati e Database.
Nodo della classe di oggetto, ad esempio Database	Elenco di oggetti del tipo, ad esempio l'elenco di database: master, model, AdventureWorks.
Nodo del nome dell'oggetto, ad esempio AdventureWorks	Elenco dei tipi di oggetto contenuti all'interno dell'oggetto. Ad esempio, un database elenca tipi di oggetto come tabelle e viste.

Per impostazione predefinita, tramite Get-ChildItem non vengono elencati gli oggetti di sistema. Utilizzare il parametro Force per visualizzare gli oggetti di sistema, ad esempio gli oggetti nello schema sys.

In questo esempio sono elencati il computer locale e i computer a cui è stata effettuata una connessione SMO o WMI:

Set-Location SQLSERVER:\SQL
Get-ChildItem

In questo esempio sono elencate le istanze del Motore di database nel computer locale:

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

In questo esempio sono elencate le classi principali di oggetti disponibili in un'istanza predefinita del Motore di database. Nell'elenco sono inclusi nomi come Endpoint, Certificati e Database:

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

In questo esempio sono elencati i database disponibili in un'istanza predefinita del motore di database. Il parametro Force viene utilizzato per includere i database di sistema, come master e model:

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

Esecuzione di azioni su nodi di percorso

Dopo lo spostamento su un nodo in un percorso di Windows PowerShell, è possibile eseguire due tipi di azioni:

È possibile eseguire i cmdlet di Windows PowerShell che operano sui nodi, ad esempio Rename-Item.
È possibile chiamare i metodi dal modello di oggetti di gestione di SQL Server associato, ad esempio SMO. Ad esempio, se si passa al nodo Databases in un percorso, è possibile utilizzare i metodi e le proprietà della classe Database.

Il provider SQL Server viene utilizzato per gestire gli oggetti in un'istanza del Motore di database. Non viene utilizzato per i dati nei database. Se si è passati a una tabella o una vista, non è possibile utilizzare il provider per selezionare, inserire, aggiornare o eliminare i dati. Utilizzare il cmdlet Invoke-Sqlcmd per eseguire una query o per modificare dati in tabelle e viste nell'ambiente di Windows PowerShell. Per ulteriori informazioni, vedere Utilizzo del cmdlet Invoke-Sqlcmd.

Elenco di metodi e proprietà

È possibile utilizzare il cmdlet Get-Member per visualizzare i metodi e le proprietà disponibili per oggetti o classi di oggetti specifici.

In questo esempio viene illustrato come visualizzare un elenco dei metodi della classe SMO Database:

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

In questo esempio viene impostata una variabile Windows PowerShell sulla classe SMO Database e vengono elencate le proprietà:

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

È inoltre possibile utilizzare Get-Member per visualizzare un elenco di metodi e proprietà associati al nodo finale di un percorso di Windows PowerShell.

In questo esempio si passa al nodo Databases in un percorso SQLSERVER: e vengono elencate le proprietà della raccolta:

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

In questo esempio si passa al nodo AdventureWorks in un percorso SQLSERVER: e vengono elencate le proprietà dell'oggetto:

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

Utilizzo di metodi e proprietà

È possibile fare riferimento alle proprietà SMO nei comandi di Windows PowerShell. In questo esempio viene utilizzata la proprietà dello schema SMO per ottenere un elenco delle tabelle dallo schema Sales di AdventureWorks:

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

In questo esempio viene utilizzato il metodo di script SMO per generare uno script che contiene le istruzioni CREATE VIEW necessarie per ricreare le viste in 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 }

In questo esempio viene utilizzato il metodo di creazione SMO per creare un database e viene quindi utilizzata la proprietà State per indicare se il database esiste:

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

Definizione di unità personalizzate

Windows PowerShell consente agli utenti di definire unità virtuali, definite come unità di Windows PowerShell o unità ps. Tali unità sono mappate ai nodi iniziali di un'istruzione di percorso. Tali unità vengono in genere utilizzate per abbreviare percorsi digitati con frequenza. I percorsi SQLSERVER: possono diventare lunghi, occupando spazio nella finestra di Windows PowerShell e richiedendo molta digitazione. Se si lavorerà molto in un particolare nodo del percorso, è possibile definire un'unità di Windows PowerShell personalizzata mappata a tale nodo. Se, ad esempio, si utilizza molto il database AdventureWorks, è possibile creare un'unità AWDB:

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

È quindi possibile utilizzare l'unità AWDB: per abbreviare il percorso degli oggetti di AdventureWorks, ad esempio la tabella Purchasing.Vendor:

Set-Location AWDB:\Tables\Purchasing.Vendor

Gestione delle connessioni tramite l'autenticazione di SQL Server

Per impostazione predefinita, il provider SQL Server utilizza l'account di Windows in cui viene eseguito per effettuare una connessione con autenticazione di Windows a Motore di database. Per stabilire una connessione con autenticazione di SQL Server, è necessario associare le credenziali di accesso di SQL Server a un'unità virtuale e quindi utilizzare il comando di modifica della directory (cd) per stabilire la connessione a tale unità. In Windows PowerShell le credenziali di protezione possono essere associate solo a unità virtuali.

Questo script crea una funzione denominata sqldrive che è possibile utilizzare per creare un'unità virtuale associata all'account di accesso con autenticazione di SQL Server e all'istanza specificati.

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
}

È quindi possibile creare un'unità virtuale denominata SQLAuth: eseguendo il comando seguente:

sqldrive SQLAuth

La funzione sqldrive richiede l'immissione della password per l'accesso, mascherandola durante la digitazione. Quindi, quando si utilizza il comando di modifica della directory (cd) per connettersi a un percorso tramite l'unità SQLAuth:, tutte le operazioni vengono eseguite utilizzando le credenziali di accesso con autenticazione di SQL Server fornite al momento della creazione dell'unità.

Utilizzo dello spazio dei nomi Microsoft.SqlServer.Managment.Smo.Wmi

Oltre agli spazi dei nomi del modello SMO (SQL Server Management Objects) associati alle cartelle \SQL, \SQLPolicy e \SQLRegistration, è anche possibile utilizzare le classi nello spazio dei nomi Microsoft.SqlServer.Management.Smo.Wmi. L'utilizzo più comune di questo spazio dei nomi consiste nell'eseguire una query e gestire lo stato dei servizi che implementano ogni istanza dell'archivio criteri o del Motore di database.

In questo esempio è illustrato come utilizzare la classe ManagedComputer per interrompere e avviare il servizio che esegue un'istanza predefinita del Motore di database.

# 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"

[!NOTA]

Per utilizzare le classi in questo spazio dei nomi in computer remoti, è necessario configurare Windows Firewall per consentire le connessioni WMI DCOM. Per ulteriori informazioni, vedere Configurazione di Windows Firewall per consentire l'accesso a SQL Server.

Gestione del completamento con il tasto TAB

La funzione di completamento con il tasto TAB di Windows PowerShell consente di ridurre la digitazione. Dopo aver digitato parte del nome di un percorso o di un cmdlet, premere il tasto TAB per ottenere un elenco degli elementi il cui nome corrisponde a quanto digitato. È quindi possibile selezionare l'elemento desiderato dall'elenco senza digitare il resto del nome.

Se si utilizza un database che contiene molti oggetti, l'elenco di completamento alla pressione del tasto TAB può risultare molto lungo. Anche alcuni tipi di oggetto di SQL Server, ad esempio le viste, includono numerosi oggetti di sistema.

Gli snap-in di SQL Server introducono tre variabili di sistema che è possibile utilizzare per controllare la quantità di informazioni presentate dal completamento alla pressione del tasto TAB e da Get-ChildItem:

**$SqlServerMaximumTabCompletion =**n
Specifica il numero massimo di oggetti da includere in un elenco di completamento alla pressione del tasto TAB. Se si seleziona il tasto TAB in un nodo del percorso con più di n oggetti, l'elenco di completamento alla pressione del tasto TAB viene troncato in corrispondenza di n. n è un numero intero. L'impostazione predefinita è 0, che indica che non esiste alcun limite al numero di oggetti elencati.
**$SqlServerMaximumChildItems =**n
Specifica il numero massimo di oggetti visualizzati da Get-ChildItem. Se Get-ChildItem viene eseguito in un nodo del percorso con più di n oggetti, l'elenco viene troncato in corrispondenza di n. n è un numero intero. L'impostazione predefinita è 0, che indica che non esiste alcun limite al numero di oggetti elencati.
$SqlServerIncludeSystemObjects = { $True | $False }
Se $True, gli oggetti di sistema vengono visualizzati dal completamento della scheda e da Get-ChildItem. Se $ False, non viene visualizzato alcun oggetto di sistema. L'impostazione predefinita è $False.

Nell'esempio seguente vengono impostate le tre variabili e vengono elencate le impostazioni:

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

Cronologia modifiche

Aggiornamento del contenuto
Correzione di errori di sintassi nei blocchi di codice che fanno riferimento al database Smo.Database. Rimozione dei riferimenti all'unità SQL:, sostituita con l'unità SQLSERVER:.

Condividi tramite