Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 290

7. Using the SQL Server Agent service, all the PowerShell scripts can be scheduled to

run automatically based on the schedule. To obtain more information on the method
to call scripts, run the powershell.exe /? from the PowerShell console or sqlps.
exe /? from the Windows command prompt. he default install location of scripts
are at:
T 64-bit: %:\Program Files (x86)\Microsoft SQL Server\100\
Tools\Binn\
32-bit: %:\Program Files\Microsoft SQL Server\100\Tools\Binn\

8. To obtain a status about a Windows service within the SQLPS window, type Get-
Service and press Enter .
9. To obtain more information on a specific cmdlet type help and press Enter to display
help about PowerShell cmdlets and concepts.

This completes the steps to implementing and running PowerShell for SQL Server
provider features.

How it works...

PowerShell is an internal component of the Windows operating system and for SQL Server
based providers. SQL Server service provides an additional six providers from SQL Server
2008 R2.

The methodology for PowerShell implements the cmdlets to navigate the provider hierarchies
and perform the required operations on the current object. Internally, the commands are
a set of aliases that map the cmdlets to a similar command prompt. For the PowerShell,
the hierarchy of objects are classified as server-level on top, then on the list is databases,
and finally, database objects. By default, when a reference of an instance of the database
engine is specific in the PowerShell cmdlet , SQL Server Provider uses SMO to open a
windows authentication connection to the instance. In supplement to this, the SQLSERVER:\
SQL hierarchy uses these SMO namespaces: Microsoft.SqlServer.Management.
Smo , Microsoft.SqlServer.Management.Smo.Agent , Microsoft.SqlServer.
Management.Smo.Broker and Microsoft.SqlServer.Management.Smo.Mail . Then
the connection is made using the credentials of the Windows account running the Windows
PowerShell session. The SQL Server authentication will not be used by SQL Server provider in
any case.

The SQL Server PowerShell provider includes the cmdlet ,
Invoke-SqlCmd , which is a useful method for executing
adhoc queries and stored procedures from PowerShell.