Powershell

For many years I didn’t really think about the implications of how I was retrieving data from my SQL Servers in PowerShell. I was just happy that I was able to retrieve the data! As I learned more about SQL Server I started to think of things like SQL injection and using parameterized queries to promote plan reuse.

I went back and looked at some of the old PowerShell scripts that I had written and found that I was way off! Most of the old scripts would be prime candidates for SQL Injection. I wasn’t really concerned early on because I was the one running my scripts and passing in the parameters, but as my scripts became more automated and database driven, they became more vulnerable to SQL Injection. And parameterized queries? What the heck is a parameterized query? I had no clue when I first started out.

PowerShell stores errors that it encounters in a circular error buffer variable named $Error. $Error also contains a count property to count the number of errors in the buffer. To figure out how many errors we could hold was pretty straight forward. All I had to do was create a script that generated an error and see how high $error.count got. At this point I didn’t even know if there was a maximum or not.

I have nothing but wonderful things to say about the SQL Saturday 386 leadership and the army of volunteers they brought along. It was a very well run event. The venue was nice, everything was very well organized, the people were amazingly helpful and friendly, and it made the perfect place for me to turn my presentation up a notch. It’s amazing how much of a calming effect can be felt when you are surrounded by such an inviting and amazing group of people. That’s really something that I love about the SQL Server community in general.

Here is a link to the official #tsql2sday invitation from Andy Yun’s Blog. This month’s subject is “Just Say No to Defaults”. If you’ve read my blog at all you will know that I have a slight obsession with SQL Server and PowerShell. As far as I am concerned PowerShell is the new gold standard when it comes to scripting and automation in the Windows environment. Add a sprinkle of SQL Server with the SQL Server PowerShell module (SQLPS) and you have a deadly combination as a DBA.

This is the 3rd installment in a small series of blog posts on how to create a PowerShell wrapper for running SQL Server Agent Jobs. Here are the links to the 2 previous posts and I recommend reading them because all of the posts build on the previous one.

Creating a SQL Agent Job Wrapper with PowerShell and SMO - Part 1 SQL Agent Job Wrapper Part 2 – Adding Error Generation to the Cmdlet

Last week I posted Creating a SQL Agent Job Wrapper with PowerShell and SMO. In that post I created a couple PowerShell scripts that run a SQL Agent job and wait for it to complete before exiting. That process could be called from the command line, or even from a 3rd party job scheduler if you were so inclined. I recommend checking it out before you continue, because this is a continuation of that post.

I’ve seen this question asked a number of times over the past couple weeks and I thought I would share an approach using PowerShell and SMO. But first, let’s take a step back and try to understand why someone would want to do this. What’s wrong with SQL Agent?

Well there’s really nothing wrong with SQL Agent. The main reason I have seen people asking this question is because their company is looking into using an enterprise job scheduler. An enterprise job scheduler gives an operations group a single location to manage jobs across their whole environment. It allows them to see all the moving parts from a batch processing perspective, even across dissimilar platforms. It also allows them to create more complex workflows across multiple platforms.

So here we are at week 4 of the #SQLNewBlogger challenge. Earlier in the week I responded to the following post on Twitter #sqlhelp, and after I posted my response I thought that this would make a fun blog post. I have used PowerShell to export SQL Server tables to CSV files before so I know that my suggestion works, but I was wondering if I could determine how performance would be for a larger table.

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!