SQL Server 2008 PowerShell SnapIn

re: SQL Server 2008 PowerShell SnapIn

Rob Farley — Thu, 12 Mar 2009 01:34:34 GMT

Yes Mark, you're correct. SQLPS isn't without its uses, but all too often I want to hook into slightly more than what I have available.

And I'm often running PowerShell anyway, which makes the snapins way more useful.

Rob

re: SQL Server 2008 PowerShell SnapIn

MarkTab — Wed, 11 Mar 2009 20:09:55 GMT

By the way -- I will also provide a document quotation on why use SQLPS at all if anybody could simply add the PS PlugIn. SQLPS is a "Power Shell Mini-Shell":

"By default, sqlps runs with the scripting execution policy set to Restricted, which prevents running any PowerShell scripts. You can use the Set-ExecutionPolicy cmdlet to enable running signed scripts, or any scripts. Only run scripts from trusted sources, and secure all input and output files using the appropriate NTFS permissions."

from msdn.microsoft.com/.../cc281962.aspx

Powershell is, well, powerful, and a main concern for a SQL Server admin is inadvertently running something against a shared-database environment.

SQLPS is therefore the default window when right-clicking an object in SSMS.

The link I referenced provides a script for adding SQL Server snap-ins to any PowerShell environment (it is more than just three lines since it includes conditional registration, for example). Also provided is a script to load SMO only (if, for example, the intention is to port to .NET and not use the SQL provider).

re: SQL Server 2008 PowerShell SnapIn

MarkTab — Wed, 11 Mar 2009 19:50:22 GMT

Thanks for this post.

For the TOTAL newbie, when I type:

get-pssnapin -reg

I receive:

Name : SqlServerCmdletSnapin100

PSVersion : 1.0

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

Name : SqlServerProviderSnapin100

PSVersion : 1.0

Description : SQL Server Provider

Then, I should do (based on the "Name"):

Add-PSSnapin SqlServerProviderSnapin100

Add-PSSnapin SqlServerCmdletSnapin100

Learning PowerShell in SQL Server 2008 « Home of the Scary DBA

Learning PowerShell in SQL Server 2008 « Home of the Scary DBA — Wed, 22 Oct 2008 15:29:05 GMT

Pingback from Learning PowerShell in SQL Server 2008 « Home of the Scary DBA

PowerShell changes in SQL Server 2008 RC0

PowerShell changes in SQL Server 2008 RC0 — Tue, 24 Jun 2008 03:24:45 GMT

Pingback from PowerShell changes in SQL Server 2008 RC0

PowerShell changes in SQL Server 2008 RC0

Rob Farley — Thu, 12 Jun 2008 02:48:49 GMT

Regular readers of my blog will remember my post about how to get PowerShell for SQL into your regular

re: SQL Server 2008 PowerShell SnapIn

Rob Farley — Tue, 29 Apr 2008 01:57:09 GMT

Goodwin - there are some great books around. Pop down to a bookshop and glance through a few to find one that suits your learning style. But also read blogs like blogs.msdn.com/powershell to stay learning!

Aaron - I'm not sure, because I don't run Exchange locally. However, if there's a PowerShell provider at all (through this Exchange Shell you mention), you should be able to see if it's treated as a snapin using "Get-PSSnapin -reg" like Darren and I did for the SQL stuff. And post back here to let me know how you go.

re: SQL Server 2008 PowerShell SnapIn

Aaron Guilmette — Tue, 29 Apr 2008 01:50:11 GMT

Does anyone know if this works for the Exchange 2007 Command Shell? For those of us that admin multiple platforms and applications, the prospect of PS bloat is demoralizing.

re: SQL Server 2008 PowerShell SnapIn

G D Milner — Tue, 22 Apr 2008 05:54:37 GMT

In my opinion, the best book on Powershell is and remains Bruce Payette's Powershell in Action. Also very good is the Windows PowerShell Cookbook by Lee Holmes.

I just started reading Pro Windows PowerShell by Hristo Deshev, and that one looks pretty good, too.

If you only buy one book, however, get Powershell in Action.

Cheers

re: SQL Server 2008 PowerShell SnapIn

Goodwin Robinson — Thu, 17 Apr 2008 09:33:26 GMT

Hi Rob and everyone!

Great article, I DBA managing just over 60 SQL Server 2000/2005 Instances, supporting over 300 databses and it seems my day will be much more productive if I can script in PowerShell.

I know the basis in VBScript via WSH, so some knowledge base on my part to work with, but will like to take on PowerShell.

So, Please where would you suggest I start - books and web resource etc I could use to get up to speed.

Thanks

Goodwin

PowerShell - Where is It?

Carpe Datum — Wed, 16 Apr 2008 15:39:25 GMT

As you may know, we’ve created a SQL Server 2008 provider for PowerShell. With this very cool feature

re: SQL Server 2008 PowerShell SnapIn

Rob Farley — Tue, 25 Mar 2008 21:16:01 GMT

Sorry Steve, I actually haven't tried this! I doubt it'll work, but please try and let me know!

re: SQL Server 2008 PowerShell SnapIn

steve — Tue, 25 Mar 2008 12:02:50 GMT

So do you need to install SQL 2008 or can you just copy the SQLPS.exe to your local machine?

MSDN Blog Postings » SQL Server 2008 PowerShell SnapIn

MSDN Blog Postings » SQL Server 2008 PowerShell SnapIn — Thu, 20 Mar 2008 22:44:26 GMT

Pingback from MSDN Blog Postings » SQL Server 2008 PowerShell SnapIn

SQL Server 2008 PowerShell SnapIn

Contagious Curiosity — Thu, 20 Mar 2008 21:11:05 GMT

Oh, the things you find when you are writing a book. Now, the problem is to spend less time 'finding'

re: SQL Server 2008 PowerShell SnapIn

Rob Farley — Fri, 14 Mar 2008 20:59:45 GMT

But 'Invoke-SqlCmd' comes with the SQL PS Provider, so you don't need to roll your own.

And for backup times, you don't need to go back into T-SQL, you can just do it from SMO directly:

PS SQL:\rob-pc\default\Databases> gci | select-object name,lastbackupdate

And if you need to make SMO connections, you can. I guess it's no different really to using invoke-sqlcmd.

Of course, with SSMS 2008, you can do Local Server Group queries, which makes this much easier to do using T-SQL directly.

Rob

re: SQL Server 2008 PowerShell SnapIn

SQL_Guru — Fri, 14 Mar 2008 15:03:48 GMT

Thanks for figuring out the PSSnapins for SQL 2008. I wasn't looking forward to having 2 different flavors of Powershell open all the time. This makes it easier to integrate with my existing tools.

In terms of administrating SQL using PS. It is almost a must for a large environment. Just try to use SSMS to figure out when the last backups happened to all databases on all servers. It works ok on 1 server, but as the number goes up, so does the amount of time it takes. With a script/CmdLet you can do things like the following:

gc .\SQLServers.txt | % { Invoke-SQL.ps1 -Server $_ -InputFileQuery .\BackupStatus.sql }

Now you have a list of all backups on all servers and can go from there (After you've made the BackupStatus.sql script). This also makes a huge difference if you have a lot of databases. For example at last count I was checking the backup status of over 1100 databases using PowerShell. Try that with SSMS. You can do it, but it takes DAYS. Currently it takes me about 3 minutes to run the script and a few more minutes to review the results. Now you could do this with other scripting languages, but it isn't as easy to get access to the underlying objects/rows/columns from the command line.

Other Possible Uses:

Invoke-SQL -S Dev1 -Q "select @@servername as 'Server', getdate() as 'ServerTime'" # Check time on server

Invoke-SQL -S Dev1 -Q "exec sp_who2" # what's running on a server

Invoke-SQL -S Dev1 -Q "exec sp_helpdb" # Get Name and Size of databases

# Get number of blocked processes for one server or for all servers.

Invoke-SQL -S Dev1 -Query "select @@servername as 'Server', count(*) as 'Blocked' from master.dbo.sysprocesses where blocked <> 0"

gc .\SQLServers.txt | % { Invoke-SQL -Server $_ -Query "select @@servername as 'Server', count(*) as 'Blocked' from master.dbo.sysprocesses where blocked <> 0" }

#Insert data using a hashtable as the source for parameters. My Invoke-SQL.ps1 script adds parameters based on the hashtable passed in.

Invoke-Sql -S Dev1 -Database Test1 -Query "insert ServerList (ServerName, IPAddress) values (@ServerName, @IPAddress)" -Parameters @{'@ServerName'='ProdWeb01';'@IPAddress'='192.168.0.134'}

These are a few of the things I use almost every day, although my scripts are more complex. Hopefully it will give you an idea of what you can do with powershell and why it's usefull for administrating SQL Servers.

p.s. The Invoke-SQL.ps1 script in the examples isn't available online yet. It's something I cooked up myself.

re: SQL Server 2008 PowerShell SnapIn

NicoAtMicrosoft — Wed, 05 Mar 2008 20:38:24 GMT

I hear PowerShell is an improvement over VBScript, but I'm impartial either way. I wonder if there will be any improvements on this in the RTM of SQL Server 2008?

**************

Nico del Castillo

Microsoft 2008 Joint Launch Team