SQL Server PowerShell Extensions

Posted by Jonathan Allen on Jun 19, 2008

Sections

Development,

Operations & Infrastructure

Topics

.NET ,

Scripting ,

SQL Server

Tags

SQL Server 2008 ,

PowerShell

SQL Server PowerShell Extensions

PowerShell is quite possibly going to be the most important language for Windows administrators over the next few years. This is all the more evident when you look at the extensive PowerShell support being added to Microsoft server products such as SQL Server.

Michiel Wories lists the key features planned for the first version:

• SQLPS – a minishell that gives you a complete pre-configured Powershell with all of SQL Server’s extensions preloaded.
• SQL Server Agent integration – A new job subsystem for Powershell
• SQL Server Management Studio Integration – context menus on every applicable node in Object Explorer (with connection context reuse. Including SQL security!)
• Four new Providers! – new providers for SQL Server relational engine, Registered Servers, Data Collection, and SQL Server Policy Management
• SQLCMD integration – SQLCMD compatible script execution within Powershell (reuses the SQL Server connection context, and even database context of the provider!)
• SQL Server Policy Management integration – Allows evaluation of any Policy
• Various other cmdlets – support the provider, such as conversion of a SMO Urn to a Powershell path, encoding and decoding of SQL identifiers.
• SQL Server Powershell redist – allows you to install SQL Server Powershell with your application or on any machine you need to have it on (this still being built so with the caveat it may be shipped later, or being cut altogether – don’t flame me yet).
• SQLPS, the minishell mentioned above, will be replacing the venerable SQLCMD. A minishell is an instance of PowerShell that has been preconfigured for a specific purpose. It is a bit of a trade off, while having everything you need for SQL pre-loaded, it does not support the loading of other snap-ins. In the future this limitation will be removed.

As an alternative to SQLPS, administrators can use a normal PowerShell instance. In this case they would have to either load the extensions manually or via a initialization script. A sample initialization script is provided by Michiel.

• Older >