Fun with SQL, Excel, and Powershell

SQL Server MVP Deep Dives 2, Chapter 10

Discover your servers with Powershell and SMO – by Joe Webb (B|T)

I’m finally back to my chapter reviews and I restarted with a fun one. I know enough to be dangerous with powershell, but I don’t use it enough to keep it fresh in my brain. Right off I liked the idea of this chapter because I got to play with scripting and do something a little different (at least for me). I know there are other ways of gathering this type of data us ing T-SQL (Brent Ozar’s sp_blitz being the first one that comes to mind), but I haven’t done it with powershell before and I certainly never used powershell with Excel before.

There is more code than text in this chapter, which makes for a quick read away from the keyboard. I would have liked to see a little more explanation of the powershell, but he does a good job of explaining everything so a powershell novice can implement this pretty easily.  I liked the fact that he did not gloss over the importance of adding in the SMO reference library. I’ve seen several powershell articles that assume you know how to set up your script or just tell you to add the line of code in and don’t explain.

Once I started creating the report it was cool to see the spreadsheet open and populate from my local instance of SQL Server. (There are some hints on how to set up a script to import a list of servers, but it was beyond the scope of this chapter.) I liked the simplicity of the report that was created and he even took time to add in formating for the headers.

One of the nice featuers of the script is that is does some conditional checks for common best practtices and highlights them in the spreadsheet. for example it reports when the last backup was taken and if  the database hasn’t been backed up in the last day the cell is shaded red.

if (((get-date) - $db.LastBackupDate).days -gt 1) {
 $fgColor = 3; 
} else { 
$fgColor = 0;
$Sheet.Cells.Item($Row,14) = $db.LastBackupDate; 
$Sheet.Cells.item($Row,14).Interior.ColorIndex = $fgColor;

Since I don’t use the SQL Server on this machine for anything but a sandbox I don’t take very regular backups.

He has basic checks on most of the columns and it is easy to see how this could be expanded to other items and rules that you may have in your own database. I am not sure how much I will end up using this script, but I learned some new tricks in powershell and this was definitely the most hands on chapter in the book so far.

(I should note that if you have a copy of the book and try this yourself I think there are couple of bugs in the scripting. I believe there should be another row increment ($row++) after the column headers for the server information (page 97, listing 4) and also after the column headers for the job information (page 99, listing 6). And further on in the script in the section when changing the color for dbspace available and LAstBackupdate there are references to $intRow that I think should just be $row.)

Chapter Ten SQL Server MVP:

Joe Webb (B|T) has served as Chief Operating Manager for WebbTech Solutions, a Nashville-based consulting company, since its inception. He has more than 15 years of industry experience and has consulted extensively with companies in the areas of business process analysis and improvements, database design and architecture, software development, and technical training. In addition to helping his consulting clients, Joe enjoys writing and speaking at technical conferences. He has spoken at conferences in Europe and North America and has authored or coauthored three books. Joe is a seven-time recipient of the prestigious Microsoft MVP Award for demonstrating a willingness to share his expertise with the SQL Server Community.

If you want to discover SQL Server MVP Deep Dives Volume 2 you can get a copy at Manning Books.  I don’t get anything out of it if you buy the book, but you might.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s