The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.
Basically, you can now open up PowerShell, and change directory to the PowerShell drive “SQL:”. Then change directories through the instances, databases, tables, and so on. At any point, try something like “dir | gm” (gm is Get-Member, dir is an alias for Get-ChildItem), to find out what properties and methods are available on the objects in that folder.
Whilst T-SQL will probably remain the preferred environment for many, this scripting seems to be a step up from using SMO through PowerShell. However, it seems to be just a wrapper for SMO – when I tried to find out information about the Script() method on tables, it turns out to take a parameter of type Microsoft.SqlServer.Management.Smo.ScriptingOptions – I found this a little disappointing, and I haven’t figured out the best way of getting help on methods that are on these objects either.
It’s definitely a good start though – should be a very nice feature of SQL Server 2008.
This Post Has 2 Comments
I find it intriguing that finally PowerShell provides an opportunity to pipe AND tee output.
One scenario I can think of is as follows. An SSIS package encounters an error. If I tee the output, relevant details can be logged. Simultaneously, based on severity, a case statement might fire off to send an SMS if it is very high priority, or it can send out an email so that the issue can be dealt with at a more sedate pace…
Hi, I became a fan of SQLPS and am trying to work with the SQLPS and/or PowerShell to achieve the following task.
Scenario:
. I have SQL2008 installed on Win2003 SP2
. The SQL service & agent runs under a local service user named KAPRAVI rather than the local Administrator (company standards)
. Using SQLPS or Power shell as Administrator user is not allowing me to access the SQL 2008. Where as with the service user KAPRAVI all goes excellent and my PS1 files get executed.
. There is a central tool that runs a script against our enterprise database servers for some status reporting purpose; that always runs as the Local Administrator to query the SQL 2008 status. Due to the previous observation, this tool is reporting errors.
My questions are:
1. How to know the user account who owns the SQL 2008 instance?
2. How to run SQLPS or PowerShell as that user from with in a VBScript that is being executed as the Local Administrator