cjsommer.com

So here we are, week 2 of the #SQLNewBlogger Challenge. This is a follow on to last weeks post Monitoring SQL Agent Jobs when you work for Mr Krabs where I showed you how to go about monitoring SQL Server agent jobs using PowerShell and SMO. This can be very helpful if you are on a limited budget and can’t afford any fancy monitoring tools. 

For those of you who don’t know who Mr. Krabs is, he is a character in the TV show Sponge Bob Square Pants. Mr. Krabs owns the Krusty Krab restaurant and is a very frugal business owner. Every decision he makes is driven completely on how it will impact his bottom line.

Thinking about Mr. Krabs reminds me of one of my first SQL Server DBA jobs. I was starting just as the old DBA was leaving and there wasn’t a lot of time for turnover. I was the only DBA on staff, my servers were old, the CPU’s slow, the disk drives were small, and none of it was being monitored! Yikes! Monitoring was one thing I knew I had to address right away, but I didn’t have a budget to work with! The business just wasn’t willing to spend the money on a fancy monitoring application. They were my Mr. Krabs!

March 26th, 2015, a date which will live in infamy!

Well, at least for me it will. March 26th, 2015 marks the date I gave my first presentation at my local SQL PASS user group meeting. Here is the picture as proof!

I was very excited to be able to share some of my knowledge with everyone who showed up. I hope to be able to do it again some time.

I like to initialize my arrays by strongly typing my variable as an array, and setting it to null. PowerShell doesn’t require strongly typed variables, so why would I want to do this?

The primary reason is because it allows me to deal with an expected object type later in the code. For arrays it gives me access to a count property, which I don’t get with strings, int’s or other non-collection type objects.

The question was this:

This is just a quick/fast snippet in response to that question, but I can think of a ton of enhancements that could made. Hopefully it will be useful as a starting point for someone to customize for their own use.

function Convert-FriendlyTime
{
    param (
        [string]$FriendlyTime 
    )
    
    $Converted = $FriendlyTime.Split(" ")
    
    switch ($Converted[1])
    {
    "days"    { $parms = @{"days"=$Converted[0]}; break ;}
    "hours"   { $parms = @{"hours"=$Converted[0]}; break ;}
    "minutes" { $parms = @{"minutes"=$Converted[0]}; break ;}
    "seconds" { $parms = @{"seconds"=$Converted[0]}; break ;}
    "default" { $parms = $null; break ;}
    }
    if ($parms) {
        New-TimeSpan @parms
    } else {
        Throw "Invalid unit of time '$FriendlyTime'. Please use days, hours, minutes or seconds."
    }
}

Convert-FriendlyTime -FriendlyTime "2 days"
Convert-FriendlyTime -FriendlyTime "68 hours"
Convert-FriendlyTime -FriendlyTime "68 InvalidUnits"

Saw this on twitter and thought I would throw it up on the blog. Easier than responding on twitter.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;

$SQLServerInstance = "MSSQLSERVER"

$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $SQLServerInstance
$srv.JobServer.MaximumHistoryRows = 1002
$srv.JobServer.Alter()

You can manipulate most if not all SQL Server configurations through SMO. This simple post has inspired me a bit. I think it would be useful to show how I came to find the information I needed to modify this setting. The cool thing about PowerShell is that it’s in there. All the tools you need already exist. Look back for an expansion of this post in the near future.

My cheap and dirty PowerShell script for dumping the disk space usage for a Windows machine. It was originally some snippets I found on the Internet, which I threw into a script and made it my own.

It uses a simple WMI call to return a set of objects for you to use as you see fit. Most of the time I just pipe it to Format-Table to see the output, but it could be used for other things. Throw in a pipe to Where-Object and you could use it to monitor low disk space. Feel free to bind, spindle or mutilate as you see fit.

I’ve been a problem solver as far back as I can remember, always looking for a new challenge. I love staying current in technology, pushing the software to its limits, as well as finding its boundaries. For me there is satisfaction in knowing the answer to something because you have tried it out yourself. So a bit about me…

I’ve worked with Unix, Windows, Informix, Oracle, SQL Server. I’ve worked in small shops. I’ve worked in large shops. I’ve made lots of coffee. I’ve drank lots of coffee. I’ve had good on call weeks, and I’ve had bad on call weeks. Okay, you probably get it; I have a diverse background. Do I know everything? Absolutely not! What I do know is that I still have that hunger for knowledge.

So I was reading the following blog post by Brent Ozar and I think it finally hit me right between the eyes.

I think from a DBA perspective I would have to consider myself in the DevOPS realm, but more generally, I am an automation specialist. It’s what I truly love to do and where I shine. Whether it’s a simple information gathering exercise, or deploying database code, I enjoy automating it. Automation brings simplicity, scalability, consistency and reliability to a potentially complex process.