Microsoft Windows PowerShell and SQL Server 2008 AMO

November 18, 2008

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]

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

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

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

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

Step 6: Click Finish. [Refer 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

Step2: Click Next

Step 3: Accept the agreement

Step 4: Click Install

Step 5: Click Finish.

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

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

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

Alternatively, you can start PowerShell by selecting Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer 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]

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

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        :

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