Prof. Powershell

PowerShell Returns, The SQL

Automating SQL Server administration tasks is easy with PowerShell, especially with the SQL-related snapins.

• By Jeffery Hicks
• 05/10/2011

We've been looking at ways you can leverage and extend all the PowerShell knowledge you've gleaned over the course of this column. I want to wrap up with a peek at what you can do with SQL Server 2008 R2. I recommend you download and install the free Express edition on your Windows 7 desktop.

When you install the latest version of SQL Server, you should also get a set of PowerShell providers. When you install on your desktop, you can use the providers to manage your local SQL install or any SQL server on your network. In this lesson I'm going to demonstrate using a local instance. I believe many admins should move from the days of Excel spreadsheets and text files and begin using SQL 2008 Express.

First off, there are two SQL-related snapins that get installed:

PS C:\> Get-PSSnapin SQL*

Name        : SqlServerCmdletSnapin100
PSVersion   : 2.0
Description : This is a PowerShell snap-in that includes various
              SQL Server cmdlets.

Name        : SqlServerProviderSnapin100
PSVersion   : 2.0
Description : SQL Server Provider

The Provider snapin creates a PSDrive and the cmdlet snapin offers a single, yet amazing, cmdlet. Let's load both snapins:

PS C:\> Add-PSSnapin SQL*

The provider snapin creates a new PSDrive called SQLSERVER which makes it easy to work with configuration and database information:

PS C:\> dir SQLSERVER:\sql\SERENITY | Select Name,Edition,Product*,Version

Name         : SERENITY\SQLEXPRESS
Edition      : Express Edition (64-bit)
Product      : Microsoft SQL Server
ProductLevel : SP2
Version      : 10.0.4000
PS C:\> CD SQLSERVER:\sql\Serenity\SQLExpress\databases
PS SQLSERVER:\sql\Serenity\SQLExpress\databases> dir | select name

Name
----
MDT
MySampleDB
ProfPowerShell
VeeamBackup
vServer

But I think the real power is with the cmldet provider. You can use the Invoke-SQCmd cmdlet to execute just about any T-SQL query:

PS C:\> Invoke-Sqlcmd -query "Select * from Workstations where Make='Toshiba'" -Database MySampleDB -ServerInstance ".\SQLExpress"

SerialNumber : ABC9889
Description  : primary
Make         : Toshiba
Model        : Qosmio
Computername : Serenity

With this command I selected all entries from the Workstations table in the MySampleDB database where the Make column is equal to “Toshiba”. The result is an object written to the pipeline. What I love is the ability to integrate SQL into my administration and management tasks. For example, here's a pretty typical WMI-based command:

PS C:\> $os=Get-WMIObject -Class Win32_Operatingsystem -ComputerName "Serenity"

But because I have a database set up to record this information, I can dump it into SQL usng Invoke-SQLcmd:

PS C:\> invoke-sqlcmd -query "INSERT INTO Computers (Computername,ReportDate,PhysicalMemoryBytes,OS, ServicePack,RegisteredOwner,RegisteredOrganization) Values ('$($os.csname)','$(get-date)','$(($os.TotalVisibleMemorySize * 1kb))','$($os.caption)','$($os.ServicePackMajorVersion)',' $($os.RegisteredUser)', '$($os.Organization)')" -serverinstance ".\SQLExpress" -database ComputerData

I know it looks a little complicated and what I would typically do is put this into a script or function. But it works and I can verify it with another command:

PS C:\> Invoke-Sqlcmd -query "Select * from Computers where ComputerName='Serenity'" -Database Computerdata -ServerInstance ".\SQLExpress"

Computername           : SERENITY
ReportDate             : 3/28/2011 4:50:00 PM
PhysicalMemoryBytes    : 8577855488
OS                     : Microsoft Windows 7 Ultimate
ServicePack            : 1
RegisteredOwner        : Jeffery Hicks
RegisteredOrganization : JDH Information Technology Solutions

I don't think working with a database has ever been any easier and PowerShell has the strengths to make this a real game-changer for how administrators get the job done.