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]
Figure 1.0
Step 2: Click “Run”. [Refer Figure 1.1]
Figure 1.1
Step 3: Click “Next”. [Refer Figure 1.2]
Figure 1.2
Step 4: Select the option “I agree”.
[Refer Figure 1.3]
Figure 1.3
Step 5: Watch the progress of the installation. [Refer Figure 1.4]
Figure 1.4
Step 6: Click Finish. [Refer Figure 1.5]
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
Figure 1.6
Step2: Click Next
Figure 1.7
Step 3: Accept the agreement
\
Figure 1.8
Step 4: Click Install
Figure 1.9
Figure 1.10
Step
5: Click Finish.
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
Figure 1.12
After a short pause, the PowerShell prompt appears. [Refer
Figure 1.13]
Figure 1.13
If you use
Windows PowerShell 2.0 CTP2, then the screen will look like Figure 1.14.
Figure 1.14
Alternatively, you can start PowerShell by selecting
Programs-Windows PowerShell 1.0-Windows PowerShell. [Refer Figure 1.15]
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 |
New-Alias |
\
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”)
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 :
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.
