PowerShell Returns, The SQL
Automating SQL Server administration tasks is easy with PowerShell, especially with the SQL-related snapins.
- By Jeffery Hicks
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
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.
Jeffery Hicks is an IT veteran with over 25 years of experience, much of it spent as an IT infrastructure consultant specializing in Microsoft server technologies with an emphasis in automation and efficiency. He is a multi-year recipient of the Microsoft MVP Award in Windows PowerShell. He works today as an independent author, trainer and consultant. Jeff has written for numerous online sites and print publications, is a contributing editor at Petri.com, and a frequent speaker at technology conferences and user groups.