Instead of working, I blog.

I was asked to write a quick blog on what makes PS so awesome. And while it's a huge topic with many many examples, I'll give you a couple just to get you going on how cool it is.

1. It makes it really easy to add things to your process that you never could before. Let's take for example that you want to make a change to your DB but you want to backup the schema for an object or a set of objects first. This is so easy in PS it's not even funny. The code to script out objects is:

gci| %{$_.Script()}

That's it!!! And all you have to do is add a filter if you want to script only a certain schema, or a certain object. And the code doesn't change if you want to script tables, views, function, SPs, etc. All you have to do is change the node of the tree you're in... and for that you manage it like DOS. So to script out a table you would type this code:

cd sql:\sqlserver\servername\default\databases\MyDB\Tables
Then just run the script code above.

AND to script out the SPs, you just change the contest to StoredProcedures like this.

cd ..
cd StoredProcedures

That will put you in:
cd sql:\sqlserver\servername\default\databases\MyDB\StoredProcedures

Then just run the same script method from above.
So you can see that scripting objects is wicked easy. And now you can backup the schema of a table before you mess with it, or now you can automate the scripting of SPs before you deploy a new version so you can easily rollback.

2. Now since scripting things like we did above will just print the script out to the screen, you'll want to do something more permenant with them. Have you ever tried to write to a txt file from tsql? You know, you either have to mess with xp_cmdshell or go to one of the sp_OA procedures and make those external calls, right? Well PS solves that problem. So let's script out the objects from above and save them to a file.

gci| %{$_.Script()} | out-file c:\MyTables.txt

That's it!!! So now you can save those things to a file and you've got them to use again.

That's not all you can do with files. You can also get the stuff back out of the files pretty easily. Let's say that you have a list of servers you want to connect to and check that the services are running and you want to turn on the ones that aren't. I'm not going to show you the code for all of the operations here, but to use a txt file as a list of servers you have but to call the txt file and automatically cursor through the items. So here I'll just get the list of servers from a file and print them to the screen. Because what you do with them isn't nearly important here as being able to get them easily.

get-content c:\ServerList.txt | %{$_}

So really, I just listed the servers to the screen one at a time.
The % character is an alias for 'for-each'. So for each item in that txt, print it to the screen. Easy huh?

And just for completion, you can easily work with services by calling the get-service cmdlet or the wmi class for remote boxes.

3. Getting info for objects has never been easier. Let's say you want to get all of the sizes for all of the tables in the DB and do something based on the size. While we're not going to do the entire script, I'll show you how to get the size for all the tables.

First, switch to the tables node.

cd sql:\sqlserver\servername\default\databases\MyDB\Tables

Then run this code:

gci | %{$_.DataSpaceUsed}

That'll just print them out to the screen, or you could pipe them to a txt file like above, or you could continue working with them in the pipeline and manipulate certain tables based off of whatever you like.


There are so many more things you can do with it to make your DBA job so much easier that it would make your head spin. I don't know how we ever got along without it.

Ok, so before I go I'm gonna do just one more because I do this one a lot.
I'm not going to give the code right now, but I'll tell you about it.

Let's say you've got a new employee and you need to add his acct to a Windows group on 30 boxes, and you need to add him to SQL groups and give him access as well. So He's got to be able to TS into these 30 boxes and have certain rights in all DBs on these servers.

With PS I can create a list of servers and either keep them in a table, or put them in a txt file and iterate through them and add the user both to Windows groups, and to SQL groups in a single script. So when's the last time you were able to do that without logging into all the servers individually yourself? And it's really not that hard.

So PS is well worth your time getting to know. If you need help getting started with resources, then ping me and I'll be glad to hook you up.

I've got some really good stuff on PS on MidnightDBA so go out there and check them out.

Here's a overview vid I did back in 2008:
http://midnightdba.itbookworm.com/VidPages/PowerShellOverview/PowerShellOverview.aspx

And you can see most of my other PS vids on the powershell tab of this page:
http://midnightdba.itbookworm.com/Admin.aspx

Good Luck.

You think I’d learn after all this time in DBs not to take the data for granted.  Here I’ve been troubleshooting a complicated query because I had some anomalous results when it turns out that the data wasn’t categorized correctly and I spent all that time on the query for nothing.

I’m telling you this not only to remind you to not take your data for granted, but to show you that no matter who you are (like I’m anyone special) you can make these types of mistakes.  So don’t be so hard on yourself if it happens to you because truth be told, I still do far worse on a regular basis.

We also posted a new DBAs@Midnight vid here:

It’s all about how to break up with your boss when you find a new gig.

http://midnightdba.itbookworm.com/VidPages/DBAsAtMidnightBreakUpWithBoss1of4/DBAsAtMidnightBreakUpWithBoss1of4.aspx

Some of you may remember an obscure little man who used to take a shot at writing now and then.  His name was Ken Henderson.  And he wrote (among others) small series of books called the Guru’s Guide to SQL Server.  He also wrote a little tome on SQL internals.  Now, Ken isn’t with us anymore, but his legacy lives on.  I remember sitting in Ken’s office at MS a few times talking about his vision for what he wanted his books to be.  His goal was to teach at several levels.  What he wanted was for the beginners and the experienced alike to get something out of his books.  And he succeeded very well.  I can still go back and read some of the Guru’s books and understand things I didn’t get before.  I remember saying that to Ken once and he just smiled.  Then he said, finally somebody gets it.  I want people to be able to grow into my material.

So what brought all this on?  Well, as I do sometimes, I was looking through one of my favorite blogs to kinda keep up on things.  I typically go through the back posts to make sure there’s nothing I missed the first time, or see if there’s anything I’ve grown into since reading it the last time.  And by looking at the writing style, and the intent behind it, I see that this author has the same principles that Ken held.  He not only tells you the most intimate details of the inner workings of SQL, but he does it in a way that allows you to learn at different levels.  I don’t even think he does it on purpose because like Ken, he’s just being himself;  a true teacher.

And I’m not sucking up. The only reason I’m writing about this is because it really hit me tonight because I just finished going over an old chapter of Ken’s before I went through the blog.

So since I read his material the same way I read Ken’s, I’m naming Paul Tripp the new Ken Henderson.  Paul, you make me proud to be your friend.