WIDBA

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

Thursday, September 22, 2011

Quoting Star Wars out loud on your PC (with Powershell)

I came across this great blog post at beefycode.com called "stupid Powershell tricks" and one trick is using Powershell and the text to speech COM object to have it translate text to voice.  The author used it for a much more interesting application than I have thus far, but this was too much fun.  My kids love Star Wars so I thought I would whip up a quick randomizer in Powershell to say various sayings from the movie.

  $voice = New-Object -ComObject SAPI.SPVoice
  $voice.Rate = -4
  function invoke-speech
  {
      param([Parameter(ValueFromPipeline=$true)][string] $say )
      process
      {
         $voice.Speak($say) | out-null;   
     }
 }
 
 if(!(Test-Path alias:\out-voice)){
     new-alias -name out-voice -value invoke-speech;
}   

[array]$quotes = @("I find your lack of faith disturbing"
    ,"Use the Force, Luke"
    ,"You don’t need to see his identification … These aren’t the droids you’re looking for … He can go about his business … Move along."
    ,"Help me Obi-Wan Kenobi. You’re my only hope"
    ,"May the Force be with you"
    ,"When I left you, I was but the learner, now I am the master"
    ,"Only a master of evil, Darth")

$random = New-Object  System.Random

$quotes[$random.next(0,$quotes.Length)] | out-voice

Monday, June 13, 2011

Trust but Verify (changing the Page_Verify to checksum)

Just a quick one, if you manage lots of servers and have noticed that the PAGE_VERIFY flag is set to NONE or TORN PAGE DETECTION and want to switch it to the generally accepted proper setting "CHECKSUM".  Here is a script to do it.  Just add the names of your SQL Servers to the $sqlInstanceList and hit the button. (Test it first, it's just something I whipped up today and tested quickly on a few servers).  Also, be sure you understand what you are changing, it makes sense in my environment, but may not in yours. 


[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlInstanceList = ("SQLServerName","SQLServerName2")
   
    foreach($sqlInstance in $sqlInstanceList)
    {
        $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance
   
        if($sqlServer.VersionMajor -ge 10){
            foreach($db in $sqlServer.Databases | Where-Object{$_.PageVerify -ne "Checksum"}){
                $db.Name
                $db.PageVerify = "Checksum";
                $db.Alter();
            }
        }   
    }

Thursday, June 2, 2011

Gathering physical and SQL counters with Powershell

Lately, I have been doing lots of load testing bursts with a trace and subsequent perfmon collections.  Since I like to keep things easy, I wrote a Powershell script to aid in the collection of data.  I push the files to a share so the application teams can grab them and look for themselves.  This is nice because it allows lesser experienced folks to use it and hand over results with a test.  The counters are some I picked based on a recent load test and are not a definite nor all encompassing list of counters.

$serverToMonitor = "computerName"
$sqlServerToMonitor = "SQLInstanceforcomputerName"
$file = "C:\PerfMonResults\PerfmonResults.csv"
$samplesToTake = 150
$sampleInterval = 5

$counters= ("\[instancekey]:Access Methods\Worktables Created/sec") `
,("\[instancekey]:Access Methods\Workfiles Created/sec")`
,("\[instancekey]:Buffer Manager\Page life expectancy") `
,("\Processor(_Total)\% Processor Time") `
,("\[instancekey]:SQL Statistics\Batch Requests/sec") `
,("\PhysicalDisk(*)\Avg. Disk sec/Read") `
,("\PhysicalDisk(*)\Avg. Disk sec/Write")

## Takes a counter array and replaces a token
[instancekey] with SQL Specific Name
Function Get-SQLCounters{
    param([string] $SQLServerToMonitor, $counters)
    $counterArray = @() # holds the instance specific counters array to pass into get-counter
   
    # Generate a counter path friendly name (SQLServer (default instance) or MSSQL$InstanceName)
    [int]$instPos = $SQLServerToMonitor.IndexOf("\");
    if($instPos -gt 0){
        $instPos += 1;
        $instancekey = "MSSQL$" + $SQLServerToMonitor.Substring($instPos,($SQLServerToMonitor.Length - $instPos))
    } else { # Default Instance
        $instancekey = "SQLServer"
    }
    ## Rebuilds Counter array with SQL Specific counters
    foreach($cnter in $counters) {
        $counterArray += $cnter.Replace("[instancekey]",$instancekey)
    }
     return $counterArray;
}

## Build SQLServer specific counter array
$counterArray = Get-SQLCounters -SQLServerToMonitor $sqlServerToMonitor -counters $counters

Get-Counter -ComputerName $serverToMonitor -Counter $counterArray -MaxSamples -$samplesToTake -SampleInterval $sampleInterval |
    %{$_.CounterSamples | Select-Object -Property Path, CookedValue, Timestamp; } |
    Export-Csv -Path $file -NoTypeInformation

#If Windows 7 - you can push to perfmon file and open in perfmon after collection is complete (read up on export-counter)
Invoke-Item $file
 

Friday, May 20, 2011

Cycling with Drugs, err Powershell

If you manage a large quantity of SQL Servers and want nothing to do with jobs on every one for things like log cycling, here is something you can run for each SQL Instance you manage.  It cycles the log based on the size.  Be sure to change the Invoke-SQLCMD3 to whatever "function" you use for running SQL commands via Powershell. 

function Invoke-SQLLogCycler2
{
[cmdletbinding()]
param([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer, [int] $MBLimit = 30)

    try{
        $currentLog = $sqlServer.EnumErrorLogs() | Where-Object {$_.ArchiveNo -eq 0} |
            Select-Object -Property "Size"
        $currentLogSizeMB = [Math]::Truncate($currentLog.Size / 1MB);
       
        Write-Verbose "SQL Log for $SmoSqlServer is $currentLogSizeMB";
       
        if($currentLogSizeMB -gt $MBLimit){
            Invoke-Sqlcmd3 -Query "sp_cycle_errorlog" -ServerInstance $SmoSqlServer.Name
           
            return "SQL Log cycled for $SmoSqlServer with a size of $currentLogSizeMb MB (Threshold $MBLimit MB)";
        } else {
            return "SQL Log NOT cycled $SmoSqlServer with a size of $currentLogSizeMb MB (Threshold $MBLimit MB)";
        }   
    }
    catch{
        Write-Host " SQL Log Cycle Failed for " + $SmoSqlServer.Name + " " + $_.Error;
    }
}

Invoke-SQLLogCycler2 -SmoSqlServer $sqlServer -MBLimit 25 | Out-GridView

Powershell Monitoring drive space - improvements

 Here is an improvement on finding drives that are running low on space.  It returns the drive, size, free space and the amount of MB of space you want minimally (based on the $thresholdMultiplier).


## Return any physical drive on server(note $filter) that has less than 20% free space ($thresholdMultiplier)
function Get-DriveSpace
{
    [cmdletbinding()]
    param( [Parameter(Mandatory=$true)]$computer , [int]$thresholdMultiplier=.20 )
    [string]$filter = "DriveType = '3'"

    try{
        Get-WmiObject -class win32_logicaldisk -filter $filter -comp $computer |
            Select-Object Name,@{label="SizeMB";Expression={[Math]::Truncate($_.Size / 1MB)}},
            @{label="FreeMB";Expression={[Math]::Truncate($_.FreeSpace / 1MB)}},
            @{label="WarningMB";Expression={[Math]::Truncate(($_.Size * $thresholdMultiplier) /1MB)}}|
            Where-Object{$_.FreeMB -le $_.WarningMB}
    } catch {
        Write-Verbose "Error $Error in getting disk space for $computer"
    }       
}

Wednesday, May 18, 2011

Powershell bondage, or at least binding.

I was out hunting around for best practices on writing Powershell commandlets and realized I was making one big mistake up front.  This article by Don Jones really explains the concept well.  I was really just writing functions, you don't have a commandlet until you have at least used cmdletbinding.  I think in the .Net world they call this "decorating" your function. 

For a straightforward example noting the two components in bold, when you use [CmdletBinding()] you can now use the -verbose switch, etc to add more value to your commandlets:

Function Get-CustomEventLog
{
  [CmdletBinding()]
  param([parameter(Mandatory=$true)][string]$computerName
              ,[string]$logName="Application", [string]$entryFilter="Error", [int]$rowsToReturn=25)

  Write-Verbose "Calling Get-EventLog for $computerName with $rowsToReturn rows"
  if($rowsToReturn -gt 100) {Write-Warning("Large Rowset potentially coming back");}
 
  Get-EventLog -ComputerName $computerName -LogName $logName -EntryType $entryFilter -Newest $rowsToReturn
}

Get-CustomEventLog -computerName "Consulting" -rowsToReturn 125 -verbose

Thursday, May 5, 2011

Finding all the databases with an owner other than SA

 Just an odd tidbit - If you use a standard DB owner and want to check to see that all databases are setup properly - you can use this Powershell script to find any DB that is not properly "owned".  Easy to change if you are searching for a specific owner by changing Where{$_.owner -ne "sa"} .  Enjoy!


Clear-Host
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$logFile = "C:\Powershell\Files\DBOwner_NonCompliant.txt"
if(Test-Path $logFile) {Remove-Item $logFile}

$server_list = @("Server\Merlot","Server\Cabernet")

foreach($sqlInstance in $server_list)
{
    $sqlInstance  | Out-File $logFile -Append

    $sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") $sqlInstance
    $sqlServer.Databases | Where{$_.owner -ne "sa"} | Select-Object name, owner,LastBackupDate,Size |
        Out-File $logFile -Append

}   

Invoke-Item $logFile

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. 

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

# 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
    try{
    $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
       
        try{
            $traceFlags = $startupParameters.split(';') | Where { $_.StartsWith('-T') }
            foreach($flag in $traceFlags){
                $serverStartupParms += ,@($counter, $server,$sqlServer.Name,$flag) # 
                $counter++;
            }   
        }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\"
$SSISPkgs
= Get-ChildItem -path $BaseFolder -recurse -include *.dtsx |
                               
Select-Object Directory,Name

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

foreach
($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();
}

Tuesday, March 29, 2011

Deadlocks: There are too many of them!

Back in the day, deadlock tracing was a bit more painful than it is now.  Relegated to dumping them to the SQL Error log and parsing typically.  A bunch of years ago, Bart Duncan wrote a series on troubleshooting deadlocks that is fantastic.  I couldn't possibly add anything to what he produced, but I did create something to help, especially if you have several deadlocks and are searching for specific ones.  Now that the deadlock is stored in the ring buffer, you can grab out the data and use XQuery to get a bit more out the deadlock graph so you do not have to open each deadlock XML individually.  Local MVP Jason Strate put together a great series of blog posts on the subject of XQuery, in case that is not your strong suit.

So, if you have lots of inconsistent data access patterns, you too might need a script to mine out a few of the key attributes of the deadlock to find the one you are looking for. 

This works on SQL 2008 and specifically have SP1 CU #6 installed.  (There is a workaround to prior installs, I think it is on SQLServerCentral.com.)

This will pull several columns out of the deadlock graph, the datetime of the deadlock, the logins involved and the types of locks involved (Page, Object, Key, etc). 

WITH deadlocks(DeadLockGraph)
AS
(
    SELECT CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') as xml) as DeadlockGraph
    FROM
     (select CAST(target_data as xml) as TargetData
      from sys.dm_xe_session_targets st
        join sys.dm_xe_sessions s on s.address = st.event_session_address
      where name = 'system_health') AS Data
    CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    where XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report'
)
SELECT DeadLockGraph.query('data(deadlock/process-list/process[1]/@lasttranstarted)').value('.', 'datetime') as DDate
    , DeadLockGraph.query('data(deadlock/victim-list/victimProcess/@id)').value('.', 'varchar(128)') VictimPID
    , DeadLockGraph.query('data(deadlock/process-list/process/@id)').value('.', 'varchar(128)') as PIDS
    , DeadLockGraph.query('data(deadlock/process-list/process/@waitresource)').value('.', 'varchar(128)') as Waits
    , DeadLockGraph.query('data(deadlock/process-list/process/@lockMode)').value('.', 'varchar(128)') as Mode
    , DeadLockGraph.query('data(deadlock/process-list/process/@loginname)').value('.', 'varchar(128)') as Logins
    , DeadLockGraph.query('count(deadlock/process-list/process)').value('.','int') as ProcNum
    , DeadLockGraph.query('deadlock/resource-list/child::node()') as ResourceList
    ,DeadLockGraph
FROM deadlocks 
WHERE DeadLockGraph.query('data(deadlock/process-list/process[1]/@lasttranstarted)').value('.', 'datetime') > '2010-12-03 9:00'


Hope this helps you find the deadlocks you are looking for a bit quicker, when there are many of them, parsing out a few pieces can make identification easier.

Friday, March 18, 2011

Speaking at Twin Cities Code Camp 4/9/2011

On April 9th and 10th, the Twin Cities Code Camp will be held at the University of Minnesota.  The sessions lean towards various programming languages and patterns.  If you are a DBA, stop by and break out of your shell.  Or as I say in a tongue in cheek way "Get to know your enemy".  I will be there on Saturday, hoping to catch a few sessions before I speak after lunch on Powershell monitoring.  Our local SQL rockstar, MVP Jason Strate will also be speaking on Extended Events

My session link is here if you want to attend.  I will skip some of my typical SQL stuff and cover more general server monitoring, jobs and perfmon collection strategies.

Powershell Eye for the Monitoring Guy

Hope to see you there!

Tuesday, March 15, 2011

Shrinkage: How low do I need to go to fix VLF Fragmentation?

VLF(virtual log file) fragmentation has been written about quite a bit in the past few years, in which I learned about it from SQLSkills .  One thing I did was to add a field on the end of the result set that shows you a running total of megabytes for each VLF.  This features is nice if you need to know how far to shrink your log to achieve a desired number of VLFs.  Note: There is a great deal out there about why shrinking is bad, etc please be sure to tackle the root issue before spending lots of time fixing this.  Small growth rates on the log file, bad backup schedules, etc are usually to blame.


SET NOCOUNT ON
DECLARE @beginlsn numeric(22,0);
DECLARE @increment bigINT;
    SET @increment = 1;

IF (OBJECT_ID('tempdb..#vlf') IS NOT NULL)
    DROP TABLE #VLF

CREATE TABLE #vlf(fileid int, filesize bigint, startoffset numeric(13,0), fseqno int, status smallint, parity int, createlsn numeric(22,0));

DECLARE @db varchar(128);
SET @db =  DB_NAME()

INSERT INTO #vlf
EXEC ('DBCC LOGINFO('''+ @db +''')');

ALTER TABLE #VLF 
    ADD growIncrement INT,row_id INT IDENTITY(1,1);

SET @beginlsn = (SELECT TOP 1 createlsn
                 FROM #vlf
                 WHERE growIncrement is null
                 ORDER BY startoffset);

-- Set growth increment (shows how many vlfs were created in a single growth)
WHILE EXISTS(SELECT 1 FROM #vlf WHERE growIncrement is null)
BEGIN
    UPDATE #vlf SET growIncrement = @increment WHERE createlsn = @beginlsn;
    SET @beginlsn = (SELECT TOP 1 createlsn FROM #vlf WHERE growIncrement is null order by startoffset)
    SET @increment = @increment + 1;
END

SELECT  fileid,fseqno,status,parity,growincrement,row_id as VLF_Number
    , (SELECT (SUM(filesize) / 1048576) FROM #vlf v2 WHERE v2.row_id <= v.row_id) as RunningTotalInMB
FROM #vlf v
ORDER BY row_id desc

Wednesday, March 9, 2011

Putting on your SOX - Services

I had originally set out to publish a few scripts for other DBAs to help make their SOX audits a bit easier.  Currently, my team is going through the process with a third party and they are asking for the services running on a server, and what do they run as.  If you are experienced in Powershell, this is pretty easy.  The call comes down to Get-WMIObject and the win32_service class.  The key thing was adding the "StartName" property which translates to "Log On As", thanks to this site for the help.  You may need to filter on just SQL Services, or a list and filter on that using  -contains or -like.  Hope this helps take the screen shots out of your audit.


$OutputFile = "C:\Powershell\ServerServices.htm"
$title = "<h1> Services SOX2011</h1>"
$title | Out-File $OutputFile

# server list
$arr =  @("Server1"),("Server2")

# Loop for each Path
foreach($Server in $arr)
{
    "<h2>$Server</h2>" | out-File  $OutputFile -append
    Get-WmiObject -ComputerName $Server -Class "win32_service" |
        Where-Object {$_.State -eq "Running"} |
        Select-Object Name, StartMode, State, Status,StartName |
        ConvertTo-Html |
        Out-File -FilePath $OutputFile -Append
}
"<h3>GMT Run Date:" + (Get-Date).ToUniversalTime() + "</h3>" | Out-File $OutputFile -Append

Monday, March 7, 2011

Putting on your SOX - Registry ACLs

In the second installment of the Powershell SOX series, we examine reading registry entries on servers being audited.  This script has some extra code in it because registry paths very between SQL 2000 and SQL 2008 in my environment and even depending on which registry area you are in.  If you are asked to take screenshots by the auditors in your company, consider seeing if they will take this and save yourself some time, especially after the first audit.


$OutputFile = "C:\SOX2011\SQL_SOX_ACL_SOX2011.htm"
$type = [Microsoft.Win32.RegistryHive]::LocalMachine
# Create 2d array of server and the "instancename" (depends on version of SQL)
$Srv = @("Server","MSSQL10.Brewers"),("Server2","MSSQL10.Packers")

$headerString = "-------------------------------------------------------------------------<br/>"
$title = "<h1>SQL Registry ACL SOX 2011</h1>"
$title | Out-File $OutputFile

Foreach($server in $Srv) {
    $serverName = $server[0].ToString();
    $instanceName = $server[1].ToString();
   
    # Instance Name is different in the System\CurrentControlSet.. for some reason
    # (in System is MSSQL$, in Software its MSSQL10.)
    $altInstancename = $instanceName.Replace("10.","$");
   
    $header = "<h2>Registry ACL for:" + $serverName  + "</h2>"
    $header   | out-File $OutputFile  -append
   
    # Note registry paths may be different between versions, etc
    $arr = @("SOFTWARE\MICROSOFT\MSSQLSERVER")`
        ,("SOFTWARE\MICROSOFT\MICROSOFT SQL SERVER\$instanceName") `
        ,("SYSTEM\CurrentControlSet\Services\$altInstanceName")

    # Loop each registry key path in $arr
    foreach($key in $arr)
    {
        $subHeader = "<h4>" + $key + "</h4>"
        $subHeader | out-File  $OutputFile -append
        $headerString | out-File $OutputFile -append   
        Write-Host "Key:" $key
       
        # Read Key from registry using .NET
        $regKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey($type, $serverName )
        $regKey = $regKey.OpenSubKey($key)

        if(!$regKey) {   
            Write-Host "The key does not exist!";
        } else {       
        $regKey.GetAccessControl().GetAccessRules($true,$true,[System.Security.Principal.NTAccount]) |
            Where-Object -FilterScript { $_.RegistryRights -notlike "[-,0-9][0-9][0-9]*"}   |
            ConvertTo-HTML |
            out-File $OutputFile -append   
        }   
    } #Foreach Key
} # Foreach Server   

Next up we will get into accounts and services on the server.  Have a nice audit.

Saturday, March 5, 2011

Putting on your SOX with Get-Acl

It is that time of year, when a few college graduates hired by the auditing company show up at the office.  Their mission is to make a name for themselves find folks who are not following our Sarbanes-Oxley policies.  The general idea is not all that bad, but in practice much of the audit is hokum.  They request permissions on one folder, but not the folder beneath it with the critical data.  They ask about who has access to change a job, but not who can edit financial data.  Anyway, I got my detailed audit request this past week and thus I utilized some powershell scripts that I created last year.  Last year I fought the screenshot vs powershell battle and won.  I would provided the report and the script I used so they could independently verify if they so chose.  This gives us consistency each year despite the auditors being changed out quicker than kegs at the tavern during a Packers game.

This script creates a HTML report of the Access Control Lists (ACL) for specific folders on the database server.  The report is far from pretty but creates something viewable with a little formatting.  It utilizes the Get-Acl cmdlet with some filters to return the users. 

Clear-Host
$OutputFile = "C:\SOX2011\SQL_ACL_SOX2011.htm"
$headerString = "<br/>-------------------------------------------------------------------------<br/>"
$title = "<h1>SQL SOX2011</h1>"
$title | Out-File $OutputFile

# Build array of the paths to be audited for each server
$arr =  @("\\server\c$\Program Files\Microsoft SQL Server\")`
        ,("\\server\c$\Program Files\Microsoft SQL Server\MSSQL10.Brewers")`
        ,("\\server2\c$\Program Files\Microsoft SQL Server\")`
        ,("\\server2\c$\Program Files\Microsoft SQL Server\MSSQL10.Packers")

# Loop for each Path
foreach($BasePath in $arr)
{
    $subHeader = "<h2>" + $BasePath + "</h2>"
    $headerString | out-File  $OutputFile -append

    Get-Acl -path $BasePath | select -expand access |
        Where-Object -FilterScript { $_.FileSystemRights -notlike "[-,0-9][0-9][0-9]*"}   |
        Select-Object IdentityReference,AccessControlType,IsInherited,FileSystemRights  |
        ConvertTo-HTML -body $subHeader | Out-File $OutputFile -append      

This mini-series will have four parts on the scripts used to get through a SOX audit each year.

Wednesday, March 2, 2011

Looking for trouble in all the right places with Powershell - Keeping a Clean Log

Part of monitoring sometimes is making sure the stuff you are checking is not so big it takes a long time to check it.  This function allows you to centrally manage your SQL Logs so they are cycled based on a number of days.  Many folks just implement this with a SQL job, but I am moving towards central management and less localized stuff.  The function below cycles based on the $DaysToCycle number.  An alternative implementation could be to read the current log and have it rollover on a specified number of entries.

Here is the function, taking an SMO SQL object which would already be created and the number of days.


Function Invoke-SQLLogCycler([Microsoft.SqlServer.Management.Smo.Server] $SmoSqlServer, [int] $DaysToCycle)
{
    # Get the second newest log - the date stamped there is the last reboot date.
    $lastSQLLog = $SmoSqlServer.EnumErrorLogs() `
        | Where-Object {$_.ArchiveNo -eq 1} | Select-Object -Property "CreateDate"

    [DateTime] $lastLogRolloverDate = $lastSQLLog.CreateDate
   
    if($DaysToCycle -gt 0) {$DaysToCycle = $DaysToCycle * -1};


    # Check that last log cycle was more than specified number of days out (isPriorTo function below)
    if(isPriorTo -days $DaysToCycle -Date $lastLogRolloverDate)
    {
        try{
            $SmoSqlServer.JobServer.CycleErrorLog
            Write-Host "Error Cycled for " + $SmoSqlServer.Name;
        }
        catch{
            Write-Host " SQL Log Cycle Failed for " + $SmoSqlServer.Name;
        }
    }
    else
    {
        Write-Host "SQL Log was last cycled on " + $lastLogRolloverDate.ToLongDateString();
    }
}

# Helper function - makes code a bit more readable
Function isPriorTo([int]$days, [datetime]$Date)
{
    [DateTime]::Now.AddDays($days).Date -gt $Date.Date
}

Just a quick diversion from monitoring - but a nice one when you have several servers and no current management method.  

Tuesday, March 1, 2011

Looking for trouble in all the right places with Powershell - Drive Space

In continuing with my series on Powershell monitoring (Post 1), I will move on to one of the more prevalent examples out there, collecting drive space information.  This function looks for any drives that have less free space than a factor I pass in.  When doing monitoring on several servers, you really need to keep the output to the problems, this can easily be changed to create an inventory if needed.  This particular script will not work with mount points as I have read.  Someday I will have some time to create one that does both.  I learned from several others on this by reading and changed up to create my own, particularly so I could change the multiplier for specific servers if I so chose.   


function Get-DriveSpace
{
    param( [Parameter(Mandatory=$true)]$computer , [int]$thresholdMultiplier=.10 )
    [string]$filter = "DriveType = '3'"

    $wmi = Get-WmiObject -class win32_logicaldisk -filter $filter -comp $computer |
        Where-Object {$_.size -gt 0} |   
        Where-Object {$_.freespace -le ($_.size * $thresholdMultiplier)} |
        Select-Object Name,Size,Freespace
    return $wmi;
    }

Note the line Where-Object {$_.freespace -le ($_.size * $thresholdMultiplier)} .  This is where I want to only return drives that have limited space available.

# Here is a sample call that you can add to your loop for each server to monitor
try{
        Get-DriveSpace -computer $server | FT -AutoSize
        } catch { Write-Host "Drive Space Error:$server-" $_  }


Well, that is it.  Not a particularly complex function and its widely published, but the end game is to provide a complete set of functions you can use to create a useful monitoring script in your environment.  Next up is starting to learn to collect perfmon counters and store them, all from a central script.

Saturday, February 26, 2011

Looking for trouble in all the right places with Powershell

Sometime in early 2010 I got into using powershell at work.  It started with MS Operations Manager not being particularly useful in the environment and then Buck Woody starting to post little bits and bites on using powershell for monitoring.  The first function I wrote wrapped the "commandlet" (cmdlet) Get-EventLog into something I could call from various monitoring scripts.  Here is the start of a series of functions I wrote to help find out about issues, hopefully before any user does.


function Get-FilteredEventLog([string] $physicalServer, `
            [string] $logName, `
            [string] $entryTypeFilter, `
            [datetime] $eventLogDate,`
            [string] $eventExclusionList )
{Get-eventlog -ComputerName $physicalServer `
    -logname $logName `
    -After $eventLogDate `
    | Where-Object { $_.entryType -eq  $entryTypeFilter } `
    | Where-Object {$eventExclusionList -notcontains $_.EventID} `
    | Select-Object MachineName,EventID,TimeGenerated,Message
}

A sample function call with comments for each parameter.

Get-FilteredEventLog -physicalServer $Server ` # Name of server to check.
    -logName "System" `                      # System (could be Application or Security, etc)
    -entryTypeFilter "Error" `                # Errors in log only
    -eventLogDate "2010-09-14" `       # Exclude Events prior to eventLogDate
    -eventExclusionList "1111,1112" `  # Exclude EventID 1111,1112
    | Out-GridView  # Push to a Grid View

1. If you have more than a server or two, you may wish to use Out-File and push the results to a text file.
2. This method is not fast, if want speed, eliminate some of the filters and just look for errors or the latest 100 events using -Newest 100

[edit: removed Format-Table in function - separate function from presentation.

Wednesday, February 23, 2011

SQL Saturday 47

I am just winding down from a nice vacation in Phoenix AZ and a good time at SQL Saturday 47.  Had a full room for my presentation on Powershell Monitoring, here is a link -> sqlsat47 .  The room had a few users and many folks with lesser experience.  I ended up having to move to fast through the content because I had too much and not quite enough time to cover the advanced concepts thoroughly.  Had a nice conversation afterword and some good laughs with several folks.  The organizers did a nice job and things moved smoothly.  This was my second SQL Saturday and I can't wait for the next one to attend.