Artículo
12/15/2010

Usar el proveedor de SQL Server PowerShell

El proveedor de SQL Server Windows PowerShell expone la jerarquía de objetos de SQL Server en rutas de acceso similares a las rutas de acceso al sistema de archivos. Puede utilizar las rutas de acceso con el fin de buscar un objeto y, a continuación, usar los métodos de los modelos de Objetos de administración de SQL Server (SMO) para realizar acciones en los objetos.

Jerarquía de proveedores de SQL Server

Los productos cuyos datos o modelos de objetos se pueden representar en una jerarquía usan proveedores de Windows PowerShell para exponer las jerarquías. La jerarquía se expone mediante el uso de una unidad y una estructura parecidas a las que usa el sistema de archivos de Windows.

Cada proveedor de Windows PowerShell implementa una o varias unidades. Cada unidad es el nodo raíz de una jerarquía de objetos relacionados. El proveedor de SQL Server implementa la unidad SQLSERVER:. Esta unidad tiene cuatro carpetas principales. Cada carpeta y sus subcarpetas representan el conjunto de objetos a los que se pueden obtener acceso usando un modelo de objetos de administración de SQL Server. Cuando se centra en una subcarpeta de una ruta de acceso que se inicia con una de estas carpetas principales, se pueden usar los métodos del modelo de objetos asociado para realizar las acciones en el objeto representado por el nodo. En la tabla siguiente se muestran las carpetas de Windows PowerShell que implementa el proveedor de SQL Server 2008.

Carpeta	Espacio de nombres del modelo de objetos de SQL Server	Objetos
SQLSERVER:\SQL	Microsoft.SqlServer.Management.Smo Microsoft.SqlServer.Management.Smo.Agent Microsoft.SqlServer.Management.Smo.Broker Microsoft.SqlServer.Management.Smo.Mail	Objetos de base de datos, como tablas, vistas y procedimientos almacenados.
SQLSERVER:\SQLPolicy	Microsoft.SqlServer.Management.Dmf Microsoft.SqlServer.Management.Facets	Objetos de administración basada en directivas, como directivas y facetas.
SQLSERVER:\SQLRegistration	Microsoft.SqlServer.Management.RegisteredServers Microsoft.SqlServer.Management.Smo.RegSvrEnum	Objetos de servidor registrado, como los grupos de servidores y los servidores registrados.
SQLSERVER:\DataCollection	Microsoft.SqlServer.Management.Collector	Objetos de recopilador de datos, como conjuntos de recopilación y almacenes de configuración.

Por ejemplo, puede usar la carpeta SQLSERVER:\SQL para iniciar rutas de acceso que puedan representar cualquier objeto admitido por el modelo de objetos SMO. La parte inicial de una ruta de acceso SQLSERVER:\SQL es SQLSERVER:\SQL\nombreDeEquipo\nombreDeInstancia. Se debe especificar un nombre de equipo. Puede especificar el host local o `(local`) para el equipo local. Siempre debe especificar el nombre de instancia, incluso para las instancias predeterminadas. Para las instancias predeterminadas, especifique DEFAULT. Los nodos que van a continuación del nombre de instancia alternan entre clases de objeto (como Database o View) y nombres de objeto (como AdventureWorks). Los esquemas no se representan como clases de objeto. Cuando se especifica el nodo para un objeto de nivel superior en un esquema, como una tabla o una vista, se debe especificar el nombre de objeto en el formato nombreDeEsquema.nombreDeObjeto.

Esta es la ruta de acceso de la tabla Vendor en el esquema Purchasing de la base de datos AdventureWorks en una instancia predeterminada del Database Engine (Motor de base de datos) en el equipo local:

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

Para obtener más información acerca de la jerarquía del modelo de objetos SMO, vea Diagrama del modelo de objetos de SMO.

Los nodos de la clase de objetos de una ruta se asocian con una clase de colecciones del modelo de objetos asociado. Los nodos de nombre de objeto se asocian con una clase de objetos del modelo de objetos asociado, como en la tabla siguiente.

Ruta de acceso	Clase SMO
SQLSERVER:\SQL\MiPC\DEFAULT\Databases	DatabaseCollection
SQLSERVER:\SQL\MiPC\DEFAULT\Databases\AdventureWorks	Database

Cada vez que haga referencia a una instancia de Database Engine (Motor de base de datos) en una ruta de acceso, el proveedor de SQL Server utiliza SMO para abrir una conexión de autenticación de Windows con la instancia. La conexión se realiza utilizando las credenciales de la cuenta de Windows que ejecuta la sesión de Windows PowerShell. El proveedor de SQL Server no utiliza la autenticación de SQL Server.

Navegar por las rutas de acceso de SQL Server

Windows PowerShell implementa cmdlets para navegar por las jerarquías del proveedor y realizar operaciones básicas en el objeto actual. Dado que los cmdlets se utilizan con frecuencia, tienen alias canónicos cortos. También hay un conjunto de alias que asigna los cmdlets a comandos del símbolo del sistema similares, y otro conjunto para los comandos shell de UNIX.

El proveedor de SQL Server implementa un subconjunto de los cmdlets de proveedor, que se muestran en la tabla siguiente.

cmdlet	Alias canónico	Alias de cmd	Alias de shell de UNIX	Descripción
Get-Location	gl	pwd	pwd	Obtiene el nodo actual.
Set-Location	sl	cd, chdir	cd, chdir	Cambia el nodo actual.
Get-ChildItem	gci	dir	ls	Enumera los objetos almacenados en el nodo actual.
Get-Item	gi			Devuelve las propiedades del elemento actual.
Move-Item	mi	move	mv	Mueve un objeto.
Rename-Item	rni	rn	ren	Cambia el nombre de un objeto.
Remove-Item	ri	del, rd	rm, rmdir	Quita un objeto.

Por ejemplo, puede utilizar uno de los conjuntos de cmdlets o alias siguientes para recuperar una lista de las instancias de SQL Server disponibles para navegar a la carpeta SQLSERVER:\SQL y solicitar la lista de elementos secundarios de la misma:

Mediante nombres de cmdlet completos:

Set-Location SQLSERVER:\SQL
Get-ChildItem

Mediante alias canónicos:
```
sl SQLSERVER:\SQL
gci
```
Mediante alias de cmd:
```
cd SQLSERVER:\SQL
dir
```

Mediante alias de shell de UNIX:

cd SQLSERVER:\SQL
ls

Importante
Algunos identificadores de SQL Server (nombres de objeto) contienen caracteres que Windows PowerShell no admite en los nombres de ruta de acceso. Para obtener más información sobre cómo utilizar nombres que contengan estos caracteres, vea Usar los identificadores de SQL Server en PowerShell.

Usar Get-ChildItem

La información devuelta por Get-ChildItem (o sus alias dir y ls) depende de la ubicación en una ruta de acceso de SQLSERVER:.

Ubicación de la ruta de acceso	Resultados de Get-ChildItem
SQLSERVER:\SQL	Devuelve el nombre del equipo local. Si ha utilizado SMO o WMI para conectarse a las instancias de Database Engine (Motor de base de datos) en otros equipos, esos equipos también se enumeran.
SQLSERVER:\SQL\nombreDeEquipo	Lista de instancias del Database Engine (Motor de base de datos) en el equipo.
SQLSERVER:\SQL\nombreDeEquipo\nombreDeInstancia	Lista de tipos de objeto de nivel superior en la instancia, como Extremos, Certificados y Bases de datos.
Nodo de clase de objeto, como Databases	Lista de objetos de ese tipo, como la lista de bases de datos: maestra, model, AdventureWorks.
Nodo de nombre de objeto, como AdventureWorks	Lista de los tipos de objeto contenidos en el objeto. Por ejemplo, una base de datos mostraría tipos de objeto como tablas y vistas.

De forma predeterminada, Get-ChildItem no muestra ningún objeto del sistema. Use el parámetro Force para ver los objetos del sistema, como los objetos del esquema sys.

En este ejemplo se muestra el equipo local y cualquier equipo con el que se haya realizado una conexión SMO o WMI:

Set-Location SQLSERVER:\SQL
Get-ChildItem

En este ejemplo se enumeran las instancias de Database Engine (Motor de base de datos) en el equipo local:

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

En este ejemplo se enumeran las clases principales de objetos disponibles en una instancia predeterminada de Database Engine (Motor de base de datos). La lista incluye nombres como Extremos, Certificados y Bases de datos:

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

En este ejemplo se enumeran las bases de datos disponibles en una instancia predeterminada del motor de base de datos. El parámetro Force se usa para incluir las bases de datos del sistema, por ejemplo maestra y modelo:

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

Realizar acciones en los nodos de ruta de acceso

Después de haber navegado a un nodo en una ruta de acceso de Windows PowerShell, puede realizar dos tipos de acciones:

Puede ejecutar los cmdlets de Windows PowerShell que operen en los nodos, como Rename-Item.
Puede llamar a los métodos desde el modelo de objetos de administración de SQL Server asociado, como SMO. Por ejemplo, si navega al nodo Bases de datos de una ruta de acceso, puede utilizar los métodos y las propiedades de la clase Database.

El proveedor de SQL Server se utiliza para administrar los objetos en una instancia de Database Engine (Motor de base de datos). No se utiliza para trabajar con los datos de las bases de datos. Si ha navegado a una tabla o vista, no puede utilizar el proveedor para seleccionar, insertar, actualizar o eliminar datos. Utilice el cmdlet Invoke-Sqlcmd para consultar o cambiar los datos de las tablas y vistas del entorno de Windows PowerShell. Para obtener más información, vea Usar el cmdlet Invoke-Sqlcmd.

Enumerar métodos y propiedades

Puede utilizar el cmdlet Get-Member para ver los métodos y las propiedades disponibles para objetos o clases de objeto concretos.

En este ejemplo se demuestra cómo ver una lista de los métodos de la clase Database de SMO:

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

En este ejemplo se establece una variable de Windows PowerShell en la clase Database de SMO y se enumeran las propiedades:

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

También puede usar Get-Member para mostrar los métodos y propiedades asociados con el nodo final de una ruta de acceso de Windows PowerShell.

En este ejemplo se navega al nodo Databases de una ruta de acceso de SQLSERVER: y se muestran las propiedades de la colección:

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

En este ejemplo se navega al nodo AdventureWorks de una ruta de acceso de SQLSERVER: y se muestran las propiedades del objeto:

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

Usar métodos y propiedades

Puede hacer referencia a las propiedades de SMO en los comandos de Windows PowerShell. En este ejemplo se utiliza la propiedad Schema de SMO para obtener una lista de las tablas del esquema Sales en AdventureWorks:

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

En este ejemplo se utiliza el método Script de SMO para generar un script que contiene las instrucciones CREATE VIEW que se deben tener para volver a crear las vistas en 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 }

En este ejemplo se utiliza el método Create de SMO para crear una base de datos y, a continuación, se usa la propiedad State para mostrar si la base de datos 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

Definir unidades personalizadas

Windows PowerShell deja que los usuarios definan las unidades virtuales, a las que se hace referencia como unidades de Windows PowerShell o unidades ps. Estas se asignan en los nodos de inicio de una instrucción de ruta de acceso. Se suelen usar para acortar las rutas de acceso que se escriben con frecuencia. Las rutas de acceso de SQLSERVER: pueden requerir mucho tiempo y espacio en la ventana de Windows PowerShell, así como mucho esfuerzo para escribirlas. Si va a trabajar mucho en un nodo de ruta de acceso determinado, puede definir una unidad de Windows PowerShell personalizada que se asigne a ese nodo. Por ejemplo, si va a trabajar mucho en la base de datos AdventureWorks, puede crear una unidad AWDB:.

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

Después puede utilizar la unidad AWDB: para acortar la ruta de acceso a los objetos de AdventureWorks, como la tabla Purchasing.Vendor:

Set-Location AWDB:\Tables\Purchasing.Vendor

Administrar las conexiones de autenticación de SQL Server

De forma predeterminada, el proveedor de SQL Server usa la cuenta de Windows de ejecución para establecer una conexión de autenticación de Windows con Database Engine (Motor de base de datos). Para convertir una conexión de autenticación de SQL Server, debe asociar las credenciales de inicio de sesión de SQL Server a una unidad virtual y, a continuación, usar el comando de cambio de directorio (cd) para conectarse a esa unidad de disco. En Windows PowerShell, las credenciales de seguridad solo se pueden asociar a unidades virtuales.

Este script crea una función denominada sqldrive que puede usar para crear una unidad virtual asociada a la instancia e inicio de sesión de la autenticación de SQL Server especificados.

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
}

Después puede crear una unidad virtual denominada SQLAuth: ejecutando este comando:

sqldrive SQLAuth

La función sqldrive pide al usuario que escriba la contraseña de inicio de sesión, enmascarándola a medida que la escribe. A continuación, cuando usa el comando de cambio de directorio (cd) para conectarse a una ruta de acceso usando la unidad SQLAuth:, todas las operaciones se realizan mediante las credenciales de autenticación de SQL Server proporcionadas al crear la unidad.

Usar el espacio de nombres Microsoft.SqlServer.Managment.Smo.Wmi

Además de los espacios de nombres del modelo de objetos de administración de SQL Server asociados a las carpetas \SQL, \SQLPolicy y \SQLRegistration, también puede usar las clases del espacio de nombres Microsoft.SqlServer.Management.Smo.Wmi. El uso más habitual de este espacio de nombres es consultar y administrar el estado de los servicios que implementa cada instancia del Database Engine (Motor de base de datos) o un almacén de directivas.

En este ejemplo se muestra el uso de la clase ManagedComputer para detener e iniciar el servicio ejecutando una instancia predeterminada de Database Engine (Motor de base de datos).

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

Para usar las clases de este espacio de nombres en equipos remotos, debe configurar el Firewall de Windows para que permita conexiones WMI DCOM. Para obtener más información, vea Configurar Firewall de Windows para permitir el acceso a SQL Server.

Administrar la realización mediante tabulador

La finalización mediante el tabulador de Windows PowerShell reduce la cantidad de texto que se debe escribir. Cuando se ha escrito parte de una ruta de acceso o nombre de cmdlet, se puede presionar la tecla Tab para obtener una lista de los elementos cuyos nombres coincidan con lo que ya se ha escrito. Se puede seleccionar a continuación el elemento que desee en la lista sin tener que escribir el resto del nombre.

Si está trabajando en una base de datos que tiene muchos objetos, las listas para realización mediante tabulador pueden llegar a ser muy grandes. Algunos tipos de objeto de SQL Server, como las vistas, también tienen una gran cantidad de objetos de sistema.

Los complementos de SQL Server introducen tres variables del sistema que se pueden utilizar para controlar la cantidad de información presentada por la realización mediante tabulador y Get-ChildItem.

**$SqlServerMaximumTabCompletion =**n
Especifica el número máximo de objetos que se incluyen en una lista de realización mediante tabulador. Si selecciona Tab en un nodo de ruta de acceso que tiene más de n objetos, la lista de realización mediante tabulador se trunca en n. n es un número entero. El valor predeterminado es 0, es decir, no hay límite para el número de objetos que se muestran.
**$SqlServerMaximumChildItems =**n
Especifica el número máximo de objetos mostrados por Get-ChildItem. Si se ejecuta Get-ChildItem en un nodo de ruta de acceso que tiene más de n objetos, la lista se trunca en n. n es un número entero. El valor predeterminado es 0, es decir, no hay límite para el número de objetos que se muestran.
$SqlServerIncludeSystemObjects = { $True | $False }
Si es $True, los objetos del sistema se muestran con realización mediante tabulador y Get-ChildItem. Si es $False, no se muestran objetos del sistema. El valor predeterminado es $False.

En el ejemplo siguiente se establecen las tres variables y se enumeran sus valores:

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

Historial de cambios

Contenido actualizado
Se han corregido errores de sintaxis en bloques de código que hacen referencia a Smo.Database. Se quitaron referencias a la unidad SQL: que fue reemplazada por la unidad SQLSERVER:.

Compartir a través de