Utilizzo del provider PowerShell per SQL Server

Gerarchia di SQL Server PowerShell

I prodotti i cui dati o modelli a 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 a oggetti per la gestione di SQL Server. Quando si seleziona una sottocartella in un percorso che inizia con una di queste cartelle principali, è possibile utilizzare i metodi del modello a oggetti associato per eseguire azioni sull'oggetto rappresentato dal nodo. Le cartelle Windows PowerShell implementate dal provider SQL Server 2008 R2 sono elencate nella tabella seguente:

È ad esempio possibile utilizzare la cartella SQLSERVER:\SQL per iniziare percorsi che possono rappresentare qualsiasi oggetto supportato dal modello a 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 AdventureWorks2008R2. 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 AdventureWorks2008R2 in un'istanza predefinita del Motore di database nel computer locale:

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

Per ulteriori informazioni sulla gerarchia del modello a 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 a oggetti associato. I nodi dei nomi di oggetti sono associati a una classe di oggetti nel modello a oggetti associato, come indicato nella tabella seguente.

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.

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

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 a 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à

$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

Utilizzo di metodi e proprietà

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

Definizione di unità personalizzate

Windows PowerShell consente agli utenti di definire unità virtuali, definite come unità di PowerShell. 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 AdventureWorks2008R2, è possibile creare un'unità AWDB:

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

È quindi possibile utilizzare l'unità AWDB: per abbreviare il percorso degli oggetti di AdventureWorks2008R2, 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 al 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 sicurezza 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 viene illustrato come utilizzare una classe ManagedComputer per arrestare 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"

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*

Vedere anche

Concetti

Altre risorse