PowerShell is a new command-line shell and scripting language. It offers more power and functionality than VBScript and the Windows command prompt. PowerShell will be available in Windows Server 2008 technologies/ management/ powershell/ download.mspx) for other versions of Windows. PowerShell is designed to make it easy for vendors and developers to create providers that add value to PowerShell users. SQL Server 2008 provides a PowerShell provider that enables users to easily access SQL Server instances and SMO objects, and manage database policies within the PowerShell environment.
First, we’ll provide a brief introduction to PowerShell in Windows, and then explain how to use the SQL Server PowerShell provider.
PowerShell was designed to make it easier for information technology administrators to manage the Windows environment. So why should we learn yet another scripting language and command line? PowerShell is the result of years of effort by the Windows team at Microsoft. They took a hard look at the current scripting experience, customer feedback, and the user experience with other, more robust scripting environments. With the knowledge obtained, they created a powerful scripting and command shell environment that finally competes with ones found in other operating systems.
The functionality of PowerShell revolves around what are called cmdlets (pronounced “commandlets”). You can think of cmdlets as executables that perform a specific task. PowerShell provides more than 120 cmdlets out of the box. If that is not enough, you can write your own cmdlets easily using .NET, or you can purchase third-party cmdlets.
One of the great features of PowerShell is consistency. PowerShell cmdlets always have the same format of verb-noun. Issuing the statement Get-Command would result in a list of all the available cmdlets.
Some PowerShell Cmdlets
Get-Command provides a basic definition for each cmdlet. Get-Help will return more information about a specific command. For example, issuing Get-Help Get-ChildItem gives these results:NAME
The Get-Childitem cmdlet gets the items in one or more specified locations. If the item is a container, it gets the items inside the container, known as child items. You can use the Recurse parameter to get items in all child containers. A location can be a file system location, such as a directory, or a location exposed by another provider, such as a registry hive or a certificate store.
As you can see, some information is missing here, like the definition and accepted values of parameters. You can obtain more information, including examples, by using the –default or –full option when displaying help.
As an example, let’s create a script that deletes our backup files (any file that has a .BAK extension) if they are more than ten days old. The delete_backups.#Define our variables to be used in our script
PowerShell scripts have the .ps1 extension. To run this script in PowerShell, simply type the filename at the PowerShell command line—in this case, delete_backups.ps1. As you can see from the script, the concept of an object is heavily used, without the need to define a specific datatype for each variable.
If you start working with PowerShell, you may notice that familiar command-line statements like DIR, CD, MKDIR, and RMDIR work perfectly. These are not the actual executables that you would find in a regular Windows command prompt. Rather, in PowerShell they are simply aliases to similar cmdlets that perform the task. For example, the command DIR is really an alias for Get-ChildItem. You can create your own aliases as well.
At the time of this writing, PowerShell version 2.0 is in beta form and expected to be released in the near future. Version 2.0 will add even more capabilities:
PowerShell is a powerful addition to the administrative capabilities of the Windows operating system. Not only does SQL Server 2008 provide out-of-the box integration, but other products like Microsoft Exchange already have support.
Using SQL Server PowerShell
SQL Server 2008 contains a PowerShell provider out of the box. To launch SQL Server PowerShell, you can run SQLPS from a command line. Alternatively, you can select Start PowerShell from the context menu of a server instance in SQL Server Mangement Studio.
Those who have used the Windows command line will immediately notice the familiar text-based user interface experience. Just like the command line, PowerShell has the requirement that all providers must support a drive-qualified path. if you want to perform work on your C drive on your local file system, you would access it by using the drive letter C:. And if you wanted to navigate through your SQL Server machine, you would use SQL: for the drive-qualified path.
If you launch SQL Server PowerShell from Management Studio, it automatically defaults the drive to the instance where you launched PowerShell.launching it from a default instance yields the drive path to be SQL:<server name>DEFAULT.
Issuing Get-PSDrive at the command line results in an enumeration of the available drives in PowerShell. you can see that three drives come with the SQL Server PowerShell provider: SQLSERVER, SQL, and SQLPolicy.
SQL Server PowerShell showing the results of a Get-PSDrive statement
The SQLSERVER drive is considered the root path, and beneath it are two objects: SQLPolicy and SQL. SQLPolicy is used to navigate and manage Policy Management framework objects. SQL is used to navigate SQL Server objects via the SMO hierarchy.
Using the familiar commands CD and DIR, you can easily navigate your way around the database objects. For example, to enumerate the AdventureWorks table list.CD DatabasesAdventureworks
and then this:DIR Tables
Remember that the CD and DIR statements are not actual PowerShell commands. Rather, they are aliases to Set-Location and Get-ChildItem, respectively.
The real power of PowerShell comes with its seamless integration across multiple applications and the operating system itself. Currently, Microsoft Exchange Server 2007 and SQL Server 2008 have native providers for PowerShell. This allows you to easily obtain information within a script across these various platforms. To help illustrate this concept, let’s create a simple script that obtains job information from the Windows Job Scheduler service as well as SQL Server Agent jobs, and insert this information into a table to be used for reporting. The following is the code for this example:#Use WMI to obtain the AT Scheduler job list
This example assumes we have a table in the master database, defined as follows:CREATE TABLE JobReports
After running our code, the JobReports table would be filled with entries of both the Windows AT scheduled jobs and SQL Server Agent jobs.
In the real world, we would probably want to add much more information about the jobs, like their run times, and perhaps add the ability to obtain this information across all job servers in the enterprise.However, the main point of this example is to show how we can seamlessly obtain information from a variety of sources.
The sample script obtains Agent job information by enumerating jobs within the jobs container. The properties exposed here are the same properties you would find from the Jobs object in the Microsoft.SQLServer.Management.SMO.Agent namespace. To see a list of available properties.Get-Item <object_name> | get-member -MemberType property
where object_name is the name of a job in our example. This command works with any object in PowerShell. Issuing this command against one of our Agent jobs yields:
Five cmdlets are currently available in the SQL Server PowerShell provider:
Once you have a useful script, you can run it on demand by calling it from the SQL PowerShell command line, or you can use SQL Server Agent to run it. In SQL Server 2008, Agent has a new subsystem called PowerShell. This subsystem allows users to create job steps of type PowerShell. Having a PowerShell Agent job step allows users to run PowerShell scripts as part of their jobs.
After reading about SQL Server PowerShell, you may be wondering why you should bother learning PowerShell if a T-SQL script can handle your current situation. Although T-SQL may work for you, SQL PowerShell gives you additional capabilities that may benefit you in the long term. For example, SQL PowerShell provides access to all of the capabilities of SMO, the registry, registered servers, and the Policy Management framework. Also, users of Windows or Exchange will find it really easy to integrate SQL within their management scripts. Microsoft is making a big bet with PowerShell as the future scripting engine of Windows.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.