Usar el proveedor de SQL Server PowerShell

Jerarquía de SQL Server PowerShell

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 puede 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 SQL Server 2008 R2.

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 AdventureWorks2008R2). 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 AdventureWorks2008R2 en una instancia predeterminada del Motor de base de datos en el equipo local:

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

Cada vez que haga referencia a una instancia de 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.

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

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 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 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 master y model:

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

$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

Usar métodos y propiedades

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

Definir unidades personalizadas

Windows PowerShell deja que los usuarios definan las unidades virtuales, a las que se hace referencia como unidades de PowerShell. 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 AdventureWorks2008R2, puede crear una unidad AWDB:

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

Después puede utilizar la unidad AWDB: para acortar la ruta de acceso a los objetos de AdventureWorks2008R2, 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 el 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 con 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 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 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"

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*

Vea también

Conceptos

Otros recursos