PowerShell in SQL Server 2008

  • Article

At the time of writing SQL Server 2008 is currently at the Community Technology Preview (CTP) stage. CTP 6 was delivered in February 2008 and includes a PowerShell provider. In this article I will explain what PowerShell is, why a PowerShell provider is useful and provide some examples of how you can use PowerShell with SQL Server.

PowerShell is the .NET based automation engine that Microsoft shipped in November 2006. It can have a MMC layered over the top as in Exchange 2007, it can be embedded into .NET applications but is usually used as a command line shell and scripting language. There has been over 2 million downloads of the PowerShell install package since it became available. PowerShell install packages are available in 32 bit and 64 bit versions for Windows 2003, Windows XP and Windows Vista. It is an installable feature in Windows Server 2008 i.e. it is part of the operating system install. It is incorporated into a number of Microsoft products including Exchange 2007, a number of System Center products including Operations Manager 2007, Data Protection Manager 2007 and Virtual Machine Manager. A number of third party vendors including Quest, Special Operations Software, SDM software, IBM, Citrix and VMware have produced PowerShell adds for their products or incorporated PowerShell directly into their products. PowerShell is now part of Microsoft’s Common Engineering Criteria and will be incorporated into all major products.

A second CTP of PowerShell version 2 was made available in May 2008. This includes some major enhancements to the functionality. PowerShell V2 is a complete replacement for PowerShell V1 and is not currently compatible with all applications that use PowerShell.

PowerShell has a number of features that need to be understood before working with it:

  • Any PowerShell command will run interactively or in a script. If it can be performed on the command line it can be pasted into a script to be run as required.
  • Cmdlets are small compiled pieces of functionality that provide a single piece of functionality. They have a verb-noun syntax e.g. Get-Help. They are usually named to be as self describing as possible. They are analogous to the utility tools found in the traditional Windows command shell such as ping or ipconfig. Cmdlets output .NET objects rather than the text that more traditional utilities provide.
  • PowerShell, like all good shells, has a pipeline feature. This enables cmdlets to be linked together via a pipeline i.e. the output of one cmdlets is passed into the next cmdlet in the pipeline. As PowerShell is .NET based it means that .NET objects that are passed along the pipeline rather than text.
  • PowerShell can access data stores such as the registry, the certificate store, IIS 7 or Active Directory as if they were the file system. This access is performed by a PowerShell provider.
  • PowerShell can be extended by additional cmdlets or providers being made available through PowerShell snapins. This is the case for products like Exchange 2007 or the PowerShell provider for IIS 7. Alternatively functions can be written and stored in memory for use interactively or via scripts.
  • Though PowerShell is .NET based it does not load all of the .NET assemblies automatically. There are a number of methods available to load additional assemblies. If a particular assembly is required on a frequent basis it can be loaded by the PowerShell profile which is a script that runs when PowerShell is started.
  • PowerShell is case insensitive by default.
  • Aliases can be defined for PowerShell cmdlets and functions to make a short form of their name available. This can significantly reduce the typing required to construct a line of PowerShell code.
  • PowerShell can utilise ADO.NET to access data in SQL Server.

SQL Server 2005 saw the introduction of Server Management Objects (SMO). As these objects are .NET objects they can be utilised by PowerShell. Install the SQL Server 2005 tools on a machine with PowerShell, load the SMO assemblies into PowerShell and the functionality is available for use in scripts. As an example this script lists the databases available on a server.

## load SMO assemblies

$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")

$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

## set SMO variable

$Smo = "Microsoft.SqlServer.Management.Smo."

$server = new-object ($Smo + 'server') "SQL1"

$server.databases | Select Name | Format-Table

We need to start by loading the SMO assemblies. In the script anything that starts with $ is a variable. Using $null when loading the assemblies suppresses the load messages. Setting

$Smo = "Microsoft.SqlServer.Management.Smo."

enables us to reuse this if the script is extended (saves a bit of typing!). SMO is a hierarchy of objects with the server object being the topmost object in the hierarchy. The new-object cmdlet is used to create an instance of the server object. The server object has as a property a collection of the databases on that server. This is accessed as shown on the last line of the script.

$server.databases | Select Name | Format-Table

We pass the collection of databases onto the pipeline. We then select just the database name and then format the data and display in a table. Note that Select is an alias for the Select-Object cmdlet.

With SQL Server 2008 we still have the ability to use this functionality. In addition we have native support for PowerShell through the PowerShell provider that is installed, some SQL Server specific cmdlets and that fact that it is possible to use PowerShell in SQL Server Agent job steps. The use of PowerShell in SQL Server jobs will not be covered in this article. PowerShell (and .NET 2.0) is now a pre-requisite for the installation of SQL Server 2008. If you install SQL Server 2008 onto Windows Server 2008 the PowerShell feature will be automatically installed.

The SQL Server team have approached PowerShell support in a different manner to other Microsoft products. They have chosen to create a SQL Server specific version of PowerShell in which the SQL Server functionality is pre-installed. This means that there will be two versions of PowerShell on any machine with SQL Server 2008 installed:

  • The original PowerShell
  • The SQL Server specific version including the SQL Server provider

There are a number of points that must be remembered regarding the SQL Server PowerShell console:

The SQL Server PowerShell console is closed. It is not possible to use Add-PSSnapin to add additional functionality.

  • The provider is based on SMO so it is designed for management rather than data access.
  • Some of the collection names e.g. Databases are case sensitive.
  • The SQL Server provider does not support the New-Item cmdlet. This means that item creation e.g. a new database must still be handled through scripting.
  • Remove-item is supported – so we can delete Tables, Databases etc.
  • Custom formatters are provided which mean that the default view of the object’s properties will not necessarily show everything. It is possible to explicitly state the properties you wich to see.
  • By default the system databases are not shown in the provider’s database collection. Use Get-Item . to make them visible.

A PowerShell provider exposes a data store – in this case the SQL Server SMO object hierarchy – as if it was the file system this means that we can navigate around it as if it was the file system using cd etc. PowerShell expands on the concept of file system drives to expose the data stores.

Let’s start by navigating into the provider. There are actually two providers. The other exposes SQL Server 2008 policies. That one will be left for another article.

cd SQL:

dir

We will see the available machines.

cd sql2008

dir

We start by getting the server object. Compare this to how we did it in the script. Get-Item is a standard cmdlet for working with providers. We would use exactly the same cmdlet to access a file in the file system.

$server = get-item default

Once we have the server object we can start to work with it. These are the standard properties exposed in SQL Server. We can see the same properties in the SQL Server Management Studio.

$Server.Information.Properties | Select-Object Name, Value | Format-Table -auto

cls

$Server.Settings.Properties | Select-Object Name, Value | Format-Table -auto

cls

$Server.UserOptions.Properties | Select-Object Name, Value | Format-Table -auto

cls

$Server.Configuration.Properties | Select-Object DisplayName, ConfigValue, RunValue, Description | Format-Table -auto

In each case we are selecting particular properties to view and then displaying the information in a table. The –auto parameter automatically adjusts the column widths to fit the console width.

Still working with the server object we can easily view database information.

$server.Databases

To view database size information we need a little piece of code. We start by creating a header row. The `n tells PowerShell to throw a blank line before displaying the data. The syntax is PowerShell’s usage of .NET formatted strings. The parts in the {} determine the content and the width of the field so {0,20} means take the first piece of data after the –f and put it into a right justified field 20 characters wide.

We then loop through all of the databases displaying the name, the size, the space available, the log size and the log used space. Notice we are dividing by 1KB – that does mean kilobytes. PowerShell interprets KB, MB and GB as Kilobyte, Megabyte and Gigabyte respectively and can work with them as values in calculations.

"`n {0,20} {1,15} {2,15} {3,15} {4,15}" -f "Name", "Size", "Available", "Log size", "Log Used";

foreach ($db in $Server.Databases) {"`n {0,20} {1,15:n} {2,15:n} {3,15:n} {4,15:n}" -f $db.Name, $db.Size, $($db.SpaceAvailable/1KB), $($db.LogFiles[0].Size/1KB), $($db.LogFiles[0].UsedSpace/1KB)}

One of the restrictions of the SQL Server provider is that we cannot create objects using the normal provider methods. We need to resort to code. These steps could be run interactively or could be put into a script. As it stands the code will create a database with all of the default settings.

$Server = new-object Microsoft.SqlServer.Management.Smo.Server("SQL2008")

#$db = New-Object Microsoft.SqlServer.Management.Smo.Database($server, "TestDB")

#$db.Create()

This takes us into the default SQL Server instance on the machine.

cd default

dir

We can move into the databases. Notice that it is case sensitive!

cd Databases

We can view the mounted databases

dir | Select Name | Format-Table

Compare this to what we were doing at the server level.

To view the system databases and see who has sysadmins

Get-Item .

cd master

Invoke-Sqlcmd -Query "SELECT * FROM syslogins" | Where{$_.Sysadmin -eq 1} | Select Loginname

At this point we are in the Master database so we need to move back up a level – again exactly as if we were navigating through a folder hierarchy.

cd ..

We have created a database – now we will create a table in the Adventureworks database.

$script = New-Object -Type System.Collections.Specialized.StringCollection

$script.Add("SET ANSI_NULLS On")

$script.Add("SET QUOTED_IDENTIFIER ON")

$script.Add("CREATE TABLE [dbo].[Test1]([TestID] [int] NOT NULL, [Col1] [int] NOT NULL, [Col2] [int] NOT NULL) ON [PRIMARY]")

$db = $server.Databases["AdventureWorks"]

$extype = [Microsoft.SqlServer.Management.Common.ExecutionTypes]::ContinueOnError

$db.ExecuteNonQuery($script, $extype)

In effect we are putting together the TSQL we would use and then running it. To view the table we navigate into the database.

cd AdventureWorks

dir Tables

cd Tables

cd dbo.Test1

dir Columns

A table is no use without some data. As well as the provider there are a handful of cmdlets provided with SQL Server. One of them allows us to run SQL queries

Invoke-Sqlcmd -Database AdventureWorks -Query "Insert INTO dbo.test1 values (1,2,3), (4,5,6)"

Invoke-Sqlcmd -Database AdventureWorks -Query "Select * from dbo.test1"

We can back up to the database level and remove the table.

cd ..

Remove-Item dbo.Test1

This brief overview gives a flavour of what can be achieved using the PowerShell provider. As well as databases other objects within SQL Server are exposed by SMO including logins, jobs, backups and SQL Server 2008 policies. In order to make life easier the SQL Server team have added a PowerShell item to the context menu in SQL Server Management Studio. Just right click an object such as a database and select PowerShell to have the shell open at that point in the SMO hierarchy.

PowerShell is the automation and integration technology for Microsoft products and provides a very powerful addition to the DBA’s toolkit.