Wednesday, September 23, 2009

Venturing into Powershell

As you probably know if you read my blog regularly, I recently put together a presentation called, Dive into the Default Trace, and as part of that presentation I wanted to provide examples of different ways to archive the Default Trace files.  Here are the option I considered:

  1. fn_trace_gettable to load the data into a table.
  2. SSIS to archive the files.
  3. PowerShell to archive the files.
  4. xp_cmdshell and the copy command.

I immediately eliminated #4 because I don’t have xp_cmdshell enabled and because Chad Miller (@cmille19) had said in his Powershell talk that anything that requires xp_cmdshell is a good candidate for Powershell.  So I sent Chad an email asking him where to start and he was kind enough to help out.  I got a “working” script together and sent it off to Chad for review and he kindly told me my errors (there were several) and included a modified script that cleaned up my code.  I did make a couple of minor modifications after receiving the script from Chad.  The idea and starting point were mine, even if much of the finished product is Chad’s.  So here’s the code (please comment if you have any improvements):

# Written with much Assistance from Chad Miller 
# (http://chadwickmiller.spaces.live.com/Blog)
param ($serverName
)

#######################
function
Get-SqlData
{
param([string]$serverName=$(throw 'serverName is required.'
),
[
string]$databaseName=$(throw 'databaseName is required.'
),
[
string]$query=$(throw 'query is required.'
))

Write-Verbose
"Get-SqlData serverName:$serverName
databaseName:$databaseName query:$query"

$connString =
"Server=$serverName;Database=$databaseName;
Integrated Security=SSPI;"
$da = New-Object "System.Data.SqlClient.SqlDataAdapter" ($query,$connString
)
$dt = New-Object
"System.Data.DataTable"
[void]$da.fill($dt
)
$dt

}
#Get-SqlData

#######################
function
Get-TraceInfo
{
param($serverName
)

$qry =
@"
SELECT
RIGHT(T.path, CharIndex('\',
Reverse(T.path))-1) as current_file,
Substring(T.path, 1, Len(T.Path) -
CharIndex('\', Reverse(T.path))+ 1) as trace_path
FROM
sys.traces T
WHERE
T.is_default = 1
"@

Get-SqlData $serverName 'master'
$qry

}
#Get-TraceInfo

#######################
# MAIN
#######################

if ($serverName -eq $null
)
{
$serverName =
$env:COMPUTERNAME
}

$result = Get-TraceInfo
$serverName
$copy_path
= $result.trace_path +
'Archive'

if ((Test-Path -Path $copy_path) -eq $false
)
{
New-Item -Path $result.trace_path -Name 'Archive' -ItemType
directory
}

Get-ChildItem -Path $result.trace_path -Exclude $result
.current_file |
where {$_.name -like "*.trc"} | Move-Item -destination
$copy_path


Once I knew what cmdlet’s I needed the PowerShell was probably the second easiest one to implement.  I know SSIS fairly well and it got a bit complex because of having to exclude the file currently in use.  Using fn_trace_gettable is simple because it allows me to use T-SQL which I have many years experience in.



Using PowerShell is really a matter of knowing what cmdlet’s you need, once you have that, the built in help (get-help) is very good, so you can just about do anything.  PowerShell is very powerful and flexible.  It can actually do so much that I think you need to be careful.  For example, the New-Item cmdlet can create just about any item depending on the ItemType you use.  '



So while I definitely recommend that any DBA learn some PowerShell I also recommend being very careful and Test, Test, Test!

3 comments:

  1. Jack, you are a WISE MAN for encouraging DBAs to use PowerShell. Also, I found Chad's quote that anything using xp_cmdshell is a good candidate for PowerShell very interesting. I have been thinking how to replace xp_cmdshell calls in some older utility sprocs and this statement just confirms my thinking that PowerShell is the way to go. Good post.

    ReplyDelete
  2. Jack, looks good and I'm glad I could help. A couple of other suggestions you can handle defaults for parameters like this:
    param ($serverName=$env:computername)
    As far as testing I would add most cmdlet's support the -whatif parameter that show you what action would be taken, but does not perform the action. Also since Powershell is a shell you can develop script using interactive loop i.e. run each statement individually to verfiy they work and then create a script. The use of interactive loop is one of things that sets Powershell apart from some other scripting languages where you have to write complete script before you can actually run something.

    ReplyDelete
  3. Ron,

    Thanks for the comment and encouragement. Looking forward to seeing you at SQLSaturday.

    Chad,

    I certainly appreciate your help and comments. I wasn't sure there was a way to do defaults for parameters, so I went with something I knew. Thanks for the tip(s). Looking forward to seeing you at SQLSaturday as well.

    You guys coming for the speaker party?

    ReplyDelete

So what do you think I am?