Microsoft Windows PowerShell and SQL Server 2008 AMO

As you probably know,
Windows PowerShell is the new command shell and scripting language that
provides a command line environment for interactive exploration and
administration of computers. In addition, it provides an opportunity to script
these commands so that we can schedule and run these scripts multiple times.

Windows PowerShell
depends on .NET framework 2.0 or higher.

SQL Server Analysis
Management Objects, known as AMO, is an object model for SQL Server Analysis
Service and its configuration settings. AMO-based applications use .NET
Framework languages to program against this in-memory object model.

In this article series, I
am going to illustrate the power of Windows PowerShell in conjunction with SQL
Server 2008.

Part I of this series is
going to illustrate how to install and use a simple PowerShell command and a
simple AMO command.

Assumption

a. The machine you
use already has .NET 2.0 or higher installed

b. The machine you
use already has SQL Server 2008 client installed.

Download and Install Microsoft PowerShell

a.  Download
Microsoft PowerShell “WindowsXP-KB926139-x86-ENU.exe” from http://download.microsoft.com. You could install PowerShell 2.0 CTP2 instead of
PowerShell 1.0.

Note: In this article, I
am using PowerShell 2.0 CTP2.

b. If you install
PowerShell 1.0, then you will see the following screens.

Step 1: Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable. [Refer Figure 1.0]

Double click on the “WindowsXP-KB926139-x86-ENU.exe’ executable

Figure 1.0

Step 2: Click “Run”. [Refer Figure 1.1]

Click Run

Figure 1.1

Step 3: Click “Next”. [Refer Figure 1.2]

Click Next

Figure 1.2

Step 4: Select the option “I agree”.
[Refer Figure 1.3]

Select the option I agree

Figure 1.3

Step 5: Watch the progress of the installation. [Refer Figure 1.4]

Watch the progress of the installation

Figure 1.4

Step 6: Click Finish. [Refer Figure 1.5]

Click Finish

Figure 1.5

c. If
you opt to install PowerShell 1.0, then you will see the following screens. [Refer
Figure 1.6, Figure 1.7, Figure 1.8, Figure 1.9, Figure 1.10 and Figure 1.11]

Step 1: Click Run

Click Run

Figure 1.6

Step2: Click Next

Click Ne3xt

Figure 1.7

Step 3: Accept the agreement

Accept the agreement\

Figure 1.8

Step 4: Click Install

Start installation

Figure 1.9

Installing Windows PowerShell

Figure 1.10

Step
5:
Click Finish.

Completing Windows PowerShell Setup Wizard

Figure 1.11

Launch PowerShell

There are few ways to launch PowerShell. One method is to
go to the command prompt and type the following command. [Refer Figure 1.12]

PowerShell

Launch PowerShell from the command prompt

Figure 1.12

After a short pause, the PowerShell prompt appears. [Refer
Figure 1.13]

The PowerShell prompt appears

Figure 1.13

If you use
Windows PowerShell 2.0 CTP2, then the screen will look like Figure 1.14.

PowerShell 2.0 CTP2 screen

Figure 1.14

Alternatively, you can start PowerShell by selecting
Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer Figure 1.15]

start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell

Figure 1.15

Command Help

Inside Windows PowerShell, you can access the command list
by typing the command:

Get-command

This displays all of the commands available in PowerShell.
[Refer Figure 1.16]

Add-Content
Add-History
Add-Member
Add-PSSnapin
Clear-Content
Clear-Item
Clear-ItemProperty
Clear-Variable
Compare-Object
ConvertFrom-SecureString
Convert-Path
ConvertTo-Html
ConvertTo-SecureString
Copy-Item
Copy-ItemProperty
Export-Alias
Export-Clixml
Export-Console
Export-Csv
ForEach-Object
Format-Custom
Format-List
Format-Table
Format-Wide
Get-Acl
Get-Alias
Get-AuthenticodeSignature
Get-ChildItem
Get-Command
Get-Content
Get-Credential
Get-Culture
Get-Date
Get-EventLog
Get-ExecutionPolicy
Get-Help
Get-History
Get-Host
Get-Item
Get-ItemProperty
Get-Location
Get-Member
Get-PfxCertificate
Get-Process
Get-PSDrive
Get-PSProvider
Get-PSSnapin
Get-Service
Get-TraceSource
Get-UICulture
Get-Unique
Get-Variable
Get-WmiObject
Group-Object
Import-Alias
Import-Clixml
Import-Csv
Invoke-Expression
Invoke-History
Invoke-Item
Join-Path
Measure-Command
Measure-Object
Move-Item
Move-ItemProperty

New-Alias
New-Item
New-ItemProperty
New-Object
New-PSDrive
New-Service
New-TimeSpan
New-Variable
Out-Default
Out-File
Out-Host
Out-Null
Out-Printer
Out-String
Pop-Location
Push-Location
Read-Host
Remove-Item
Remove-ItemProperty
Remove-PSDrive
Remove-PSSnapin
Remove-Variable
Rename-Item
Rename-ItemProperty
Resolve-Path
Restart-Service
Resume-Service
Select-Object
Select-String
Set-Acl
Set-Alias
Set-AuthenticodeSignature
Set-Content
Set-Date
Set-ExecutionPolicy
Set-Item
Set-ItemProperty
Set-Location
Set-PSDebug
Set-Service
Set-TraceSource
Set-Variable
Sort-Object
Split-Path
Start-Service
Start-Sleep
Start-Transcript
Stop-Process
Stop-Service
Stop-Transcript
Suspend-Service
Tee-Object
Test-Path
Trace-Command
Update-FormatData
Update-TypeData
Where-Object
Write-Debug
Write-Error
Write-Host
Write-Output
Write-Progress
Write-Verbose
Write-Warning

all of the commands available in PowerShell\

Figure 1.16

A simple AMO in PowerShell

Let us query the server level information of the Analysis service.

Step 1

Go to the command prompt.
Start – run – cmd

Step 2

Start PowerShell by typing
the following command.

PowerShell

Step 3

Execute the following cmdlets,
one by one, as shown below.


[Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”)
$servername=New-Object Microsoft.AnalysisServices.Server
$servername.connect(“PowerServer3\SQL2008”)

Execute the following cmdlets

Figure 1.17

Note:
PowerServer3 is the hostname and SQL2008 is the Analysis Service instance name.

Step 4

Now, Execute
the following cmdlets to get the Server level property of the Analysis Service
on “PowerServer3\SQL2008”.

$Servername

The
result of the cmdlet is shown in Figure 1.18

Result


ConnectionString : PowerServer3\SQL2008
ConnectionInfo : Microsoft.AnalysisServices.ConnectionInfo
SessionID : F6F441C2-C152-43F9-BC31-5DA311FE299F
CaptureXml : False
CaptureLog : {}
Connected : True
SessionTrace : Microsoft.AnalysisServices.SessionTrace
Version : 10.0.1600.22
Edition : Evaluation
EditionID : 610778273
ProductLevel : RTM
Databases : {}
Assemblies : {System, VBAMDXINTERNAL, VBAMDX, ExcelMDX}
Traces : {FlightRecorder}
Roles : {Administrators}
ServerProperties : {DataDir, TempDir, LogDir, BackupDir…}
ProductName : Microsoft SQL Server code name “Katmai” Analysis Services
IsLoaded : True
CreatedTimestamp : 10/28/2008 11:06:19 PM
LastSchemaUpdate : 10/28/2008 11:06:19 PM
Description :
Annotations : {}
ID : POWERSERVER3\SQL2008
Name : POWERSERVER3\SQL2008
Site :
SiteID :
OwningCollection :
Parent :
Container :

Results

Figure 1.18

Conclusion

As I mentioned earlier, this
article series is going to illustrate the power of Windows PowerShell in
conjunction with SQL Server 2008 Analysis Service.

Part I of this series
illustrated how to install PowerShell and use a simple PowerShell command. It
also illustrated how to use PowerShell in conjunction with a simple SQL Server AMO
command to get the properties of Analysis Service.

»


See All Articles by Columnist
MAK

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles