Chapter 26. PowerShell in SQL Server
PowerShell is the automation engine that Microsoft is now building into all of its major products. It started with Exchange Server 2007, Windows Server 2008, and various members of the System Center family, and is now available in SQL Server 2008. You can expect to see it appearing in even more products in the future, as PowerShell is part of Microsoft’s Common Engineering Criteria. Other vendors such as Quest, Special Operations Software, Citrix, and IBM are building PowerShell support into their products. Version 1 became available in November 2006. PowerShell Version 2 is in CTP as of the time of writing. It’s expected to ship with the release of Windows 7 and Windows Server 2008 R2, in which it’ll be installed by default.
So what’s PowerShell and what can we do with it?
PowerShell overview
PowerShell is usually exposed as a command shell and scripting language. It’s .NET based and can access the .NET framework.
Note
You do NOT have to be a .NET programmer to learn or use PowerShell!
PowerShell is designed to be used interactively and as a scripting language. Anything that can be performed at the command prompt can be performed interactively and vice versa. One of the design goals of PowerShell is that it should be easily extensible. The PowerShell team provides the core PowerShell engine and other product teams build PowerShell snap-ins with the functionality that they require.
PowerShell features
The most striking PowerShell features are cmdlets, providers, and the pipeline. The scripting language has the looping, branching, and control features that you’d expect. Its syntax is similar to that of C#.
Cmdlets
Cmdlets are self-contained commands that perform a single task; for instance GetService returns a list of the services installed on the machine and includes information such as the status. Cmdlets can be thought of as being analogous to command-line utilities such as ping and ipconfig which we’ve used for years.
Cmdlets have a verb-noun naming convention. The verb part should adhere to the standard verb set,[1] and the noun is descriptive of the returned data. They may have an alias defined—for instance get-childitem is aliased to dir and ls. Cmdlets return .NET objects that may be altered to a greater or lesser extent by the cmdlet. It’s possible to access the base object if required. When typing at the command line, it’s possible to partially type the name of a cmdlet or parameter and press the Tab key to cycle through the available options that would complete the name.
1 See http://msdn.microsoft.com/en-us/library/ms714428(VS.85).aspx for more information about the Microsoft standard verb set.
Providers
In PowerShell terms, a provider is a way to expose a data store as if it were the filesystem. A command such as dir c:scripts is common on Windows systems. Being able to type dir hklm:softwaremicrosoft and see registry keys opens a lot of functionality. The standard cmdlets for working with files and the standard navigation techniques used in the filesystem will work (with exceptions) in the other providers.
PowerShell ships with providers for the filesystem, registry, certificate store, and internal PowerShell features. Providers are available for other data stores, including Active Directory and IIS 7. SQL Server 2008 also exposes PowerShell functionality as a provider.
Pipeline
We’ve been using pipelines since the days of DOS—for example dir | more. The PowerShell pipeline passes .NET objects rather than text, so that the functionality of the object is exposed:
Get-Service *sql* | Foreach {if($_.Status –eq "Stopped"){start-service $_.Name -whatif}}
In this example, we get services that include sql in their name. You can use wildcards throughout PowerShell. We then loop through each object and check its status. The $_ symbol represents the object moving along the pipeline. If the service is stopped, we start it. The –whatif parameter means that we’ll show what would’ve happened if we’d performed the action. For any cmdlet that will alter the state of something, consider using the –whatif parameter first. It can save a lot of grief!
PowerShell issues and solutions
PowerShell has a number of issues that can trip up new users. Table 1 lists a few of them.
Table 1. PowerShell issues
|
Solution |
|
|---|---|
|
Can’t run scripts when first installed |
Open PowerShell as administrator and run the following: Set-ExecutionPolicy -executionPolicy RemoteSigned SQL Server PowerShell (SQLPS) has its own execution policy independent of PowerShell itself. |
|
Can’t run scripts in the current folder |
Use this syntax: ./script_name.ps1 |
|
Can’t double-click to run script |
By design. Don’t change—this is a security feature to help prevent the automatic execution of malware, as has been seen with VBScript. |
|
PowerShell only works on local machine |
Remoting is in Version 2. WMI and .NET can be used for remote administration. Some providers do give remoting capability. |
|
Not all .NET assemblies are loaded in PowerShell |
Load ss required. |
Having briefly looked at PowerShell itself, how can we use it with SQL Server?
PowerShell with SQL Server 2000 and 2005
PowerShell can work with SQL Server 2000 and 2005 in two ways. We can use ADO.NET within PowerShell to read, create, and modify data. Alternatively we can use Server Management Objects (SMO) to administer our servers. These two techniques can also be used with SQL Server 2008.
Data access
PowerShell is built using .NET, so we use ADO.NET to access data in SQL Server. If you’ve programmed ADO.NET in C# then listing 1 should appear familiar.
Listing 1. Accessing SQL Server data with PowerShell
The starting point of any code to access a SQL Server database is to create a connection string 
. This supplies the information to identify the server and database, as well as authenticate the user (in this case using
an integrated logon). Once we have that information, we can create a connection 
to the database server.
Note
As with many things in the .NET world, there are a number of ways to achieve a given goal and everyone has their own favorite code syntax. I use this syntax and I know it works. The other variations will work equally as well.
Next on the agenda is to create the command we’ll execute 
. In this case we’re performing a select, though it could be any valid T-SQL command, including inserts, updates, and deletes.
Stored procedures can also be used, though with slightly different syntax. When we retrieve our data, we need somewhere to
put it, so we create a data table 
. This is a .NET object that represents the data. After opening the connection 
we can select our data 
and load our data table 
. We’d better close the connection as well, so the DBAs don’t get upset with us 
. Now the data can finally be displayed 
. PowerShell v2 has an Out-Gridview cmdlet that’ll display the data in a nice filterable GUI.
In addition to accessing user data, we can also use these techniques to read system information and Dynamic Management Views. The results could be piped into PowerGadgets, which enables you to produce real-time updatable charts that can be used as Vista sidebar gadgets if required.
Administration
Being able to access the data is only part of the picture. We need to be able to administer SQL Server as well. PowerShell is an automation engine that lends itself to working across multiple servers. We need to be able to use SMO to perform the administrative tasks. SMO is covered in chapter 27, “Automating SQL Server management using SMO,” but for the purposes of our discussion it’s a hierarchical set of .NET objects, introduced with SQL Server 2005, that exposes the functionality we see in the SQL Server Management Suite.
PowerShell doesn’t know anything about SMO, though the assemblies are automatically loaded by SQLPS, so we need to explicitly need to load the .NET assemblies into PowerShell when we want to use this functionality. We can perform the load in the PowerShell profile if we’re going to be using SMO a lot, as shown in listing 2.
Listing 2. Accessing SMO with PowerShell
The first three lines load the SMO assemblies into PowerShell. The SMO object hierarchy starts with a server object . It’s always created first, and other objects such as databases, logins, and jobs are accessed from the server object. In
this case, I’ve defined a string to hold the first part of the class name and then use PowerShell’s New-Object cmdlet to create the object. The only required parameter is a server name. If you use integrated security, the authentication
is handled automatically.
The server object can then be used to access the databases collection. In this case, we’re only accessing the name , but all of the database properties available in the GUI management console are accessible through these objects.
Note
The SMO documentation doesn’t include examples for PowerShell. The C# examples can usually be translated. Be prepared to work through SQL Server Books Online and MSDN documentation for the full details.
PowerShell gives you the ability to access and manipulate data from the command line and to administer your servers using SMO. In SQL Server 2008, PowerShell is built into the product and provides even more functionality.
PowerShell with SQL Server 2008
PowerShell is a prerequisite for installing SQL Server 2008, as with many of the latest versions of Microsoft products. The PowerShell functionality is exposed through sqlps.exe, a provider for SMO, and some cmdlets.
SQLPS
Sqlps.exe is a PowerShell mini-shell. It’s a version of PowerShell that has been specially compiled to incorporate SQL Server functionality and remove some standard PowerShell functionality, such as the following:
- The ability to add snap-ins— The *-pssnapin cmdlets aren’t available
- The ability to create a console file— The SQLPS configuration can’t be exported for importing into another PowerShell session. This is different from the Exchange PowerShell configuration, for instance.
Use get-command | select noun -Unique | sort noun both in SQLPS and the standard PowerShell to see the differences. That still leaves the majority of PowerShell functionality available. This means we can administer services, processes, and event logs on our SQL Server machines, as shown in figure 1.
Figure 1. Service dependencies in SQL Server
One slight oddity is that SQLPS doesn’t appear on the Start menu when SQL Server 2008 is installed. I recommend creating an icon on the desktop or the Start menu. SQLPS can be found at C:Program FilesMicrosoft SQL Server100ToolsBinnSQLPS.exe.
During startup, SQLPS will attempt to run any PowerShell profiles that it finds. If the profile contains any commands that SQLPS can’t process, an error will occur and the profile processing may halt. If an error doesn’t occur, SQLPS will be pointing at the root of the SQL Server drive. SQLPS can also be started by right-clicking an object in SQL Server Management Studio (SSMS) and selecting Start PowerShell from the context menu.
Provider
As mentioned earlier, a provider exposes a data store as if it were the filesystem. In this case, it exposes the SMO hierarchy for SQL Server. The provider only gives access to databases and tables for administrative purposes. It’s not possible to do a dir and scroll through the data in your table.
The provider is accessed as if it were another drive on your system—in this case, the sqlserver: drive. The normal navigation commands such as cd are used within the provider. When you open up the SQL Server mini-shell and perform dir sqlserver: the results are the following:
Name Root Description
---- ---- -----------
SQL SQLSERVER:SQL SQL Server Database Engine
SQLPolicy SQLSERVER:SQLPolicy SQL Server Policy Management
SQLRegistration SQLSERVER:SQLRegistration SQL Server Registrations
DataCollection SQLSERVER:DataCollection SQL Server Data Collection
If you use the following to step into the database engine and display the contents
cd sqlserver:sql
dir
all you’ll see is the current machine. You can access remote machines by typing cd server_name and you’ll be connected to them. This also works in SSMS, where you can right-click on a remote registered server and point the provider to that particular server, including SQL Server 2005 instances. In effect, SQLPS has its own remoting system.
You can use the dir command, as in the next example, to list the features available in the database engine:
(cf)dir sqlserver:sqlsql08default
Here are the results for my server:
Audits
BackupDevices
Credentials
CryptographicProviders
Databases
Endpoints
JobServer
Languages
LinkedServers
Logins
ResourceGovernor
Roles
ServerAuditSpecifications
SystemDataTypes
SystemMessages
Triggers
UserDefinedMessages
We can use the Get-Item cmdlet to access the information:
cd sqlserver:sqlsql08
$server = get-item default
$Server.Information.Properties | Select-Object Name, Value |
Format-Table -auto
Databases on the system be found by running the following:
cd sqlserver:sqlsql08defaultDatabases
dir | Select Name | Format-Table
To work with the system databases we need to use Get-Item:
Get-Item .
cd master
Invoke-Sqlcmd -Query "SELECT * FROM syslogins" | Where{$_.Sysadmin -eq 1} | Select Loginname
The Invoke-Sqlcmd cmdlet can be used to access data. The one thing we can’t do in the provider is use New-Item to create a database, table, or any other object. We need to revert to using SMO in PowerShell for creating objects. The provider allows us to access the configuration information in SQL Server easily.
Cmdlets
In SQL Server 2008, there are five cmdlets shipped with the provider:
- Convert-UrnToPath
- Decode-SqlName
- Encode-SqlName
- Invoke-PolicyEvaluation
- Invoke-Sqlcmd
Convert-UrnToPath converts an SMO URN to a PowerShell provider path. The URN indicates where we are in the object hierarchy. The next two in the list are concerned with translating names between PowerShell and SMO. Invoke-PolicyEvaluation evaluates one or more SQL Server Policy-Based Management policies against a target set, either reporting the compliance level of the set or bringing the set into compliance.
The final cmdlet, Invoke-Sqlcmd, enables us to send a T-SQL command to the server and access or manipulate data, for example:
Invoke-Sqlcmd -Database AdventureWorks2008
-Query "Insert INTO dbo.test1 values (1,2,3), (4,5,6)"
Invoke-Sqlcmd -Database AdventureWorks2008
-Query "Select * from dbo.test1"
We can now replace listing 1 with the code from listing 3.
Listing 3. Accessing data with PowerShell cmdlet
Invoke-Sqlcmd -ServerInstance "SQL08" -Database "AdventureWorks2008"
-Query "SELECT * FROM Production.Product Where Color = 'Silver'" |
Format-Table
The -ServerInstance gives us the server to which we’ll connect. It’s assumed that integrated security is used. The query is exactly the same as used in listing 1. The listing ends by piping the results into a Format-Table that’ll display the output as a neatly formatted table. The results could be read into a variable to enable further analysis if required.
Using this cmdlet is a lot easier than using ADO.NET, especially for nonprogrammers. It gives us easy-to-use command-line access to the data in our database.
Summary
PowerShell is an automation engine Microsoft is now building into all of its major products. The data in SQL Server can be accessed using scripts or cmdlets shipping with SQL Server 2008. SMO-based scripts can be used to administer all versions since SQL Server 2000. PowerShell providers enable us to administer and configure SQL Server. The new policy management features can also be accessed from PowerShell.
About the author
Richard Siddaway is a technical infrastructure architect with SCH plc. With more than 20 years of experience in various aspects of IT, Richard is currently concentrating on the Microsoft environment at an architectural level, especially around Active Directory, Exchange, and SQL Server. His initial programming background is still useful, as Richard is an experienced scripter always looking for the opportunity to automate a process. Richard is a PowerShell MVP who founded and currently leads the UK PowerShell User Group. Richard frequently presents on PowerShell at conferences and user groups. He can be contacted via his blog at http://richardsiddaway.spaces.live.com/.