Windows PowerShell with SQL Server 2008

What is Windows PowerShell?

Windows PowerShell is a command-line shell and scripting environment that allows you to automate administrative and development tasks by creating robust scripts.
Unlike the most command line system that accept and return text string, the windows PowerShell supports and uses XML and objects (.Net Objects).

Why PowerShell?

• PowerShell enables easy integration between “On Premise” and “Hosted” services
• PowerShell scripts are repeatable and reusable
• PowerShell script libraries can be developed and enhanced over time, and used at multiple customer locations
• PowerShell can access data stores such as the registry, the certificate store, IIS 7 or Active Directory as if they were the file system. 
• …..

History of PowerShell

 What the system administrators need:

• Productivity – ease of use, easy to learn
• Repeatability – reuse scripts – especially for routine tasks
• Power - Ability to manipulate all server components and their settings – users, applications, files, permissions, etc.

Features of PowerShell:

Environment for running PowerShell:

• Shell Environment
• Integrated Scripting Environment (support debugging)
• And others like Idera’s PowerShell Plus editor/console.

PowerShell v2 is included in Windows 7 and Windows 2008 R2 – No need to download.

And which PowerShell cmdlets are available..?

Windows Server 2008 (AD, ADFS, Hyper-V, IIS, etc…), Exchange Server 2007, ILM 2007 Sync Engine, SQL Server 2008, Outlook Live…

Now how to use both technologies together:
When you install SQL Server 2008, it's by default installed the PowerShell 1.0, Sql server PowerShell provider and others utilities allow you to run the Sqlcmd with PowerShell environment.

Note: SQLPS.exe is a PS mini-shell built on PS v1 which means that PowerShell V2 additional cmdlets and features wouldn’t be available from within SQLPS. For this reason you can’t use PowerShell V2 scripts in a SQL Agent job. you can use PS V2 to extend management on your SQL box by some reconfiguration.

Benefits of using Windows PowerShell:

• You can invoke Sqlcmd through windows PowerShell and executed.
• You can run script that scheduled by Sql server agent jobs.
•  …..

You can run the Sql Server PowerShell by using two methods:

1. From SSMS by using start PowerShell option
2. From cmd by using sqlps
3. From Shell Envirnment

Note:
In SQL Server 2008, Windows PowerShell support is limited to the SMOs relating to the Database Engine and Service Broker. A Windows PowerShell provider for SQL Server Analysis Services (SSAS) is available on the CodePlex Web site at http://www.codeplex.com/ powerSSAS.

SQL Server uses a hierarchy to represent how objects are related to each other within a server. The root node of Sql server is SQLSERVER: drive and under this folder there are:

1. SQLSERVER:\SQL Contains database objects
2. SQLSERVER:\SQLPolicy Contains policy-based management objects, such as policies and facets.
3. SQLSERVER:\SQLRegistration Contains registered server objects, such as server groups and registered servers

For example if we want to refer to Production.Product in AdventureWorks database :
SQLSERVER:\SQL\VAIOPC\SQL2008\DataBases\AdventureWorks\Production.Product

Note:

If your instance named as default so in this case you refered as DEFAULT:

SQLSERVER:\SQL\VAIO-PC\DEFAULT\DataBases\AdventureWorks\Production.Product

You can see the above path by do the following steps:

1. Right click on AdventureWorks database from SSMS
2. Choose start PowerShell
3. Then note the path as this image:

Cmdlets are small compiled pieces of functionality that provide a single piece of functionality. They have a verb-noun syntax e.g. Get-Help

SQL SERver 2008 include a list of cmdlets with its alias and Notices PowerShell cmdlets can be referred to with their full names or with any of a number of aliases.

Get-Location Returns the current node name. Aliases: gl, pwd.

Set-Location Changes the current node. Aliases: sl, cd, chdir.

Get-ChildItem Lists the objects stored at the current node. Aliases: gci, dir, ls.

Get-Item Returns the properties of the current item. Alias: gu.

Move-Item Moves an item. Aliases: mi, move, mv.

Rename-Item Renames an object. Aliases: rni, rn, ren,

Remove-Item Deletes an object. Aliases: ri, del, rd, rm, rmdir.

Tips to simplify the typing on the command line:

• Use cd.. to return to previous folder
• Use cls to clear the screen
• Use tab to complete the partial word
• Use Up and Down to scroll the previous commands
• Use the –force parameter to view system objects such as the sys schema and the objects in it.

What about I need help??

There is a command –lets called "Get-Help" Provides help information about each cmdlet.

For example try to run the following:

Get-Help rename


Finally, let's see an example how we can run a query by using Invoke-Sqlcmd:

1. Connect to SSMS
2. Go to AdventureWorks and right click on it
3. Choose start PowerShell
4.  Write the following code :
   
   Invoke-Sqlcmd –Query "SELECT TOP 10 * FROM Production.Product" then press enter and you will get the following result: