WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Tuesday, April 19, 2011

Getting SQL Server Startup Trace Flags with Powershell

Have you ever wanted to review what parameters you had set on your SQL Servers for startup?  Perhaps you wanted to standardize your trace flags?  The question was asked on twitter and a few folks responded.  Argenis Fernandez responded with a blog post http://www.sqlps.com/blog/?cat=5 on how to do it.  I have typically used the registry to get at this data but his method seemed cleaner.  In addition, if you are using newer equipment, you can query the WMI class "SqlServiceAdvancedProperty" but this doesn't seem to work in all cases.  The method I have worked up will inventory all the servers you choose and return a multi-dimensional array with the results.  It does not work with SQL 2000 Servers. 


# Does not work with SQL 2000 Instances( could use Get-Content or read from DB to get list)
$servers = @("Server1","Server2","Server3");

$serverStartupParms = @(); # 4D array (Sequence, Server, SQLInstance, TraceFlag)
$counter = 0;

Foreach($server in $servers) {

    # Get SQL Servers that are running on server
    $SQLServices = Get-WmiObject -ComputerName $server -Class win32_service -Filter "State='Running'" |
        where {$_.name -like "MSSQL$*" -or $_.name -eq "MSSQLSERVER"}       
    } catch{
        Write-Host "Error in Getting SQL Services:" $Error
    $wmiserver = New-Object "Microsoft.SqlServer.Management.Smo.WMI.ManagedComputer" $server

    foreach($sqlServer in $SQLServices) {
        Write-Host "$server Getting Parms for "$sqlServer.Name
        $startupParameters = $wmiserver.Services[$sqlServer.Name].StartupParameters
            $traceFlags = $startupParameters.split(';') | Where { $_.StartsWith('-T') }
            foreach($flag in $traceFlags){
                $serverStartupParms += ,@($counter, $server,$sqlServer.Name,$flag) # 
        }catch {Write-Host $server "-" $sqlServer.Name " is not returning StartupParameters"}   

# Loop through each startup parm (EAV Style - 1 flag per "row") and push to screen
foreach($entry in $serverStartupParms){
    $serv = $entry.GetValue(1);
    $sqlServ = $entry.GetValue(2);
    $flag = $entry.GetValue(3);
    Write-Host $serv $sqlServ $flag

Friday, April 8, 2011

SSIS Package Versions with Powershell

If you have a larger SSIS implementation and you want to keep track of versions of the packages stored on a server, this script will help.  I thought I had found the base portion of this script on the web and then added to it, but I cannot find the source at this point.  I will only take credit for adding on to it.

It simply loops through a file structure and gives you the package name along with the Version attributes from the package.  Also shows how to read XML in Powershell.  Enjoy!

$BaseFolder = "C:\SSIS\Packages\"
= Get-ChildItem -path $BaseFolder -recurse -include *.dtsx |
Select-Object Directory,Name

= @{ dts = 'www.microsoft.com/SqlServer/Dts' }
= '//dts:Property[@dts:Name="VersionBuild"]'
= '//dts:Property[@dts:Name="VersionMajor"]'
= '//dts:Property[@dts:Name="VersionMinor"]'

($pkg in $SSISPkgs)

string]$SSISPkgPath = $pkg.Directory.ToString().Trim() +'\'+ $pkg.Name.ToString().Trim()

$BuildNode= [xml](get-content $SSISPkgPath) | Select-Xml $build -Namespace $ns | %{$_.Node}
$VersionMajor= [xml](get-content $SSISPkgPath) | Select-Xml $vMajor -Namespace $ns | %{$_.Node}
$VersionMinor= [xml](get-content $SSISPkgPath) | Select-Xml $vMinor -Namespace $ns | %{$_.Node}
Write-host $SSISPkgPath " - Version: " $BuildNode.get_InnerXml() " Major:" $VersionMajor.get_InnerXml() " Minor:" + $VersionMinor.get_InnerXml();