I’m the kind of guy who can’t resist praising PowerShell, whether it’s applied to SQL Server or  some other administrative task. Unfortunately, PowerShell presents the user with an initial hurdle to jump: the initial learning effort that’s required. Thankfully, there is a very easy way to overcome that hurdle, as pointed out to me by Shay Levy ( Twitter| blog) when I asked him for advice on the best way to learn PowerShell:

“Just use it”

And although it sounds facetious, it’s the honest truth; you will only begin to understand PowerShell if you start using it. More than that, you have to spend hours reading, testing the code, looking to improve it and not be ashamed to ask for pointers. This might sound like a real trial, but it’s actually deeply satisfying, and I’ve found the community which has sprung up to be warm and welcoming.

My little knowledge of PowerShell is applied purely to SQL Server, and for this purpose it is not difficult to learn. However, that doesn’t change the fact that I’m always looking for ways to improve the code I’ve written, and I’ve seen some examples that truly make my brain hurt! Yet with a little patience, a good IDE and, of course, good contacts with the community, no challenge is too great (or as we say in Brazil, “the lion is dead”).

To show just how easy PowerShell can be if you have a bit of determination, I got some of my unpublished blog posts together and decided to use them as demonstrations in this article. We’ll see some solutions using just PowerShell, PowerShell with SQL Server and also SQLPSX. I hope you find it useful!

SSIS and the T-SQL Hammer

I firmly believe that you have to read Chad Miller’s Blog Post entitled “The T-SQL Hammer“. It is a fantastic example of productivity in PowerShell, and I took the liberty of using the same title here because, after reading his words…

“The over-reliance on a familiar tool is best described with the quote, ‘if all you have is hammer, everything looks like nail’, and for database professionals this means using or sometimes misusing T-SQL. Whenever database administrators are presented with a scripting problem they instinctively reach for good-old-familiar T-SQL.  And why not? In many cases T-SQL provides an ideal solution to SQL Server administration scripting problems, however there are certain scenarios where another tool, PowerShell, provides a  more elegant solution.”

…I believe we can demonstrate another great argument in favor of using PowerShell. As you may have guessed, PowerShell might be my own ‘hammer’, but nevertheless, I feel that it offers remarkably elegant solutions to many of the challenges I face, and can do the same for you.

I’d like to draw your attention to a great article by Rodney Landrum, showing a very interesting solution for consolidating SQL Server Error Logs. He shows how to use SSIS and some T-SQL to neatly handle this operation, and I decided to tinker with his solution. Before I carry on, you should either go and read it, or just accept what I’m saying on faith! Rodney’s solution is excellent, make no mistake; the only thing I feel I’m adding to it is ease-of-use and perhaps a little more elegance with respect to my own environment (prompted, perhaps, by Chad’s blog post). You’ll have to be the judge when it comes to which works better for you.

To start with, one of the things I’ve changed in my version is that I don’t use the MERGE statement, for a reason that become clear in a moment. I have one Server and Database repository, one table repository to save the logs to, and I created another control table called ServersInfo  with only two columns, Server Name (ServerName) and  last collection Date (DateLastLogErrorImported)

In his example, Rodney uses the Merge statement and SSIS to record the data that is brought across in the log for each server. In my case, rather than using an SSIS package to control which logs I store, I record the date of the most recent logs in this ServersInfo table, and only insert data collected after this date. This last table is handy; it means that, when I access the error log, I can always filter my information by date.

But let’s see the code:

First, we need some T-SQL to create the repository table and Server/date control table. Obviously, you will create these tables on your server and database repository.

Now let’s populate the Servers table (Serversinfo) with the names of all the servers from which you want to have the error logs collected.

After that’s sorted, let’s look at the PowerShell code (remember, I am using SQLPSX)

I’m not handling errors at this stage, but that would be very simple to implement. With just a Try-Catch before the SQLPSX functions, we cover all error possibilities, logging them into a file for later inspection. To make your life even easier, you can find this code in my Simple-Talk articles.

With that , the data is neatly collected and saved into a SQL Server table. We can schedule this script to run once a day and, as I said earlier, we only insert data from the date of the last collection, so avoiding having to work with huge volumes of data. Naturally, with this collection system in place, you can apply summations, aggregates, filters and whatever else is necessary to set up your monitoring. To quote Chad, PowerShell provides a more elegant solution.

Searching and Logging with PowerShell

Not too long ago, I had the pleasure of helping a friend (and great Microsoft community  influencer) Jorge Segarra (Twitter| blog), in another PowerShell challenge. Jorge  needed the following:

A folder with several txt files (i.e. flat files) should be filtered for files that had the most recent change date (LastWriteTime) in a determined period, searched for whether they contain a particular string, and the result this search should be stored in a newly-created file. The results in this file should show the path and file name, as well as the line number corresponding to the string search result, and text of this line.

It sounds fairly complicated, but it really is quite simple in PowerShell. I’ve created two txt files to illustrate:

File1.txt contains this content:

And File2.txt contains:

You can see that in red & bold is the word “Error“, which is the string that I want to look for.

Let’s go to the code:

If you look closely at the PowerShell above, you’ll see that the process of finding and filtering results, and the saving of the final file is all done in one line. The rest are input parameters, help and function configuration.

Let’s say I want to search for the string “Error” in all files under the path C:\temp, with a filter focusing on files changed between the dates 01/01/2010 and 01/05/2010 (dd/mm/yyyy), and the save the results into the file C:\temp\result.txt.

…And the file result will look like this, showing the file name (with path), line number of the result, and string searched:

Figure 1. Search and Filter results

A Simpler Way to Get Values from SQL Server

This is a very quick demonstration of something which surprises me. In many code snippets and articles on the web, I see this method (or something similar) of returning values from a query (either a simple query, stored procedure etc. ..) from SQL Server to PowerShell:

It’s correct, for sure, but why not simplify the code? We could rewrite it to just:

We can even put a condition on the return; let’s say we only want to display rows where the value of the column number is 2…

…Or even run a stored procedure.

PowerShell provides many ways to do this, and you can choose the best method for yourself.

Scripting SQL Server Objects with SQLPSX

I’ve recently started using the SQLPSX library, and I am constantly finding gems buried inside it. One of my personal favorites, and one which I use a lot, is a very easy method for generating scripts of objects . For example, if we want to generate a script from a table, use:

And to output to a text file:

What if I want all the tables in my database, and not just a specific one? Simple; just delete the -name parameter in Get-SQLtable:

The same (i.e. how to generalize your scripts) can be applied to all other objects, such as stored procedures:

To see a complete list of the functions of SQLPSX, and read more about how to use them, go to SQL Server PowerShell Extensions Help.

How PowerShell Custom Tables Saved the day

This article’s final showcase of PowerShell Proclivity came about because a friend contacted me on MSN, and said:

“Laerte, I saw the hint you gave via twitter to Jorge Segarra, and want to see if you can help me. I need to output a result of a proc to an XML document every day (as is described in the Jorge ´s blog post), but the result comes from a third-party procedure, and I need to insert a description of a flag. The problem is that the Stored Procedure only returns the code, and I cannot change the procedure (because it’s third-party). Can you help me?”

Well, enumerating the problems before us:

1. We need to add one more column to the Stored procedure output,
2. This column is a description of another column: a flag,
3. We cannot change the Stored Procedure to add this column,

If I told you that we could solve this whole problem with only one line of PowerShell, you believe me? No? Well… Let’s see.

First, let’s create the scenario. I created a table and a stored procedure with the code below, and also included some names in the table. This table has a column called InsanityState and the content is a code and describe as:

Column InsanityState Char(1)  – can be  I – Insane, O – Out of Control, W – Without Hope, D – He has been a DBA

The T-SQL Code:

If we execute the proc, you will see the Flag that it needs to display as a description:

Figure 2. The results of the Stored Procedure.

As I said, we cannot change the Stored Procedure to return the description of InsanityState, so we need to use PowerShell Custom Tables. First, let’s see just the code to return all these crazy people with their Insanity State descriptions, but not the code to output to XML.

To repair this situation, I use a custom table, calling a function to convert the ID of the InsanityState flag, and display the description.

…And look at the output:

Figure 3. InsanityStates, clarified.

Now, for the output to an XML file, just use the ConvertTo-XML cmdlet and the save property:

…And the XML will look like:

Figure 4 – The Insane XML Output

Now you just need to schedule this to run, and be happy!

Summary

Well my friends, I have described a few of the uses that I’ve put PowerShell to whilst working as a DBA. I realize I’ve presented a mixed bag here, but that reflects the wide spectrum of problems that a working DBA comes across that PowerShell can help to solve. I chose them because they can be used in a lot of different ways by people who are taking the first steps with the language. I hope that you, like me, look at the productivity gain that can be found in working with this fantastic  tool.

A big hug to everyone, and here are some links for further reading:

• Creating Custom Tables
• ConvertTo-XML
• CodePlex- SQLPSX SQL Server PowerShell  Extensions