SQLvariations: SQL Server, a little PowerShell, maybe some Hyper-V Rotating Header Image

PowerShell

Is the Connect Feed for SQL Server in Your Reader?

Jan 6th, 2011
by Aaron Nelson.
3 comments

Last week Nicolas Cain ( blog | twitter ) Rob Farley ( blog | twitter ) Aaron Bertrand ( blog | twitter ) and I were all working together on a small skunk-works project to read in all of of the items on the SQL-Connect website (more in that project in a later post).  A few hours into this process I realized “hey, I don’t even have this RSS feed in my Reader!”  I’m not sure why I didn’t; maybe I was lulled into safety by Aaron Bertrand’s “Connect Digests” but unfortunately he doesn’t do those any more. Crying face

So I just wanted to encourage everyone who makes their living off of SQL Server to take a minute to add these two RSS feeds to your favorite feed-reader. 

https://connect.microsoft.com/rss/68/RecentFeedbackForConnection.xml

https://connect.microsoft.com/rss/68/RecentlyModifiedFeedbackForConnection.xml

If you haven’t been on Connect for a while and wonder what’s out there, well I’ve got a PowerShell script for you Open-mouthed smile 

Invoke-Sqlcmd -ServerInstance denali.db.5026258.hostedresource.com `
-Database denali -Username denaliconnect -Password Wide0pen -Query "
SELECT ID
        , Title
        , ItemStatus
        , UpVoteCount
        , DownVoteCount
        , ItemDescription
        , Author
        , Modified
        , ValidationCount
        , WorkAroundCount
        , URL
        , OpenedDate
  FROM denali.dbo.ConnectItems" | out-gridview

OK, you can totally take that login info and use your SQL Management Studio to connect to this database too but I wanted to show you a REALLY cool use for the Out-GridView command.  When run the PowerShell script and the window pops up Just Click on + Add Criteria > ItemDescription > Add.

Like this:

image

Then just type in a search term like “SSIS” and see what you get back…

image

Rob has also put together a way to search the data that is much better and where we hope to ultimately go with it.  Again, that’s all for another post.

Guest Posts for Hey Scripting Guy Blog on TechNet

Jan 4th, 2011
by Aaron Nelson.
Comments are off for this post

imageLate last year I contributed three guest blogger posts to a full week of posts about PowerShell and SQL Server. The posts were published a week before the PASS Summit. In them I tried to not only cover how useful PowerShell is for automating your every day SQL tasks but also the different approaches you have for accomplishing them: PowerShell cmdlet, WMI, SMO, SQL Provider, and .Net.

It’s a huge honor to get the chance to be a guest blogger on Hey Scripting Guy! and I hope these posts were able to make some people’s (o.k. A Lot of People’s) lives easier. Given the number of people checking out PowerShell for the first time based on the comments to my MCM Videos download script I figured I’d call these out today:

Use PowerShell to Obtain SQL Server Database Sizes

Use PowerShell to Change SQL Server Service Accounts

Use PowerShell to Script SQL Database Objects

image

I know that the Hey Scripting Guy! blog is the #1 blog on TechNet but I’m not allowed to know any specifics beyond that. I did however catch this in yesterday’s post: “In general, the articles we published on SQL Server and on SharePoint have been really popular.” So hopefully that means we’ll get to hear some more SQL voices there in the near future. Maybe even in time for SQL Rally, who knows? Winking smile

Quick Blog: What’s Missing From SQLPS

Dec 26th, 2010
by Aaron Nelson.
2 comments

imageLast week Norm Kelm ( blog | twitter ) asked me on twitter ‘What cmdlets/features are missing from SQLPS.exe? Get-Snapin, etc. Where’s a doc that explains?’   To get the cmdlet half of this answer is somewhat easy in PowerShell using Compare-Object but it made me realize this is one of the many PowerShell tips that I have failed to blog about yet!  I plan to fix that next year with a deluge of blog posts but why wait until next year right?

To get a very fast answer to this answer I opened up PowerShell.exe because while I normally demo in PowerShell_ISE.exe, I don’t use a profile in PowerShell.exe (more on that in a later post).  I ran this command to get the count of just the cmdlets.

(get-command -CommandType Cmdlet).count

image

Then I fired up SQLPS.exe (typically found in C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn) and ran the same command (after switching out of SQL Server and over to the C drive; no idea why that was necessary).

image

The only problem here is that it’s not really 236 – 130 = 106 because SQLPS.exe adds 5 cmdlets that I made sure to exclude (along with everything else I normally use) from PowerShell.exe. 

To get a more accurate list of the cmdlets and which one has what I decided to write a script that everyone con run on their own machine.  One of the many cool features of PowerShell is that you call call it’s executable and pass it a -command or even a –file.  I went ahead and threw together this simple command below so that you can see the differences yourself.

Note: I assume you are running Windows 7 which comes with PowerShell 2.0

$SQLPS=SQLPS.exe -command "CD C:\; get-command -CommandType Cmdlet | select CommandType, Name; exit";
$PSTwo=powershell.exe -command "get-command -CommandType Cmdlet | select CommandType, Name; exit";

Compare-Object -ReferenceObject $SQLPS -DifferenceObject $PSTwo|
Export-CSV C:\temp\SQLPSCommandDifferences.csv -NoTypeInformation

Now all that you have to do is open up that csv file that was just created (C:\temp\SQLPSCommandDifferences.csv) and have a look. 

Note: I could have just displayed the info inside of my PowerShell window by leaving off the last line and the pipe but then I would have needed to do something to format the output so I went with the CSV file instead.

For more information on this topic be sure to check out Chad Miller’s post on it.

PASS Summit 2010 Feedback Results Are In

Dec 23rd, 2010
by Aaron Nelson.
1 comment

I got my PASS Summit 2010 Feedback earlier this week and am blown away with the results! Actually, I’m humbled, very humbled. Before I bore you with the scores or anything: today is my brother Jason’s Birthday ( blog | twitter | flickr ) so I thought I’d share with you some pictures he took while he was out there with us at the PASS Summit. This one is taken from the Columbia Tower (the REALLY tall black building, well I guess it’s not actually that tall, I mean it’s not as tall as Bank of America Plaza in Atlanta Winking smile but anyways…). The Columbia tower is a much better deal than the Space Needle, it only costs $5 to go up and it’s MUCH taller.

Onto the boring part:
I put a lot of work into that session and even on the day of the event I was still second-guessing what scripts to put in and which to leave-out. Thankfully fellow [PowerShell] speaker Trevor Barkhouse ( blog | twitter ) sat through my entire presentation that morning and helped me carefully tweak the sequence of scripts. I had the 4th most well attended session of the 186 sessions at the PASS Summit on the official scoring list I was sent! My scores were nothing short of amazing considering I had never spoken on a stage this large before. Even if you leave the whole first-time-at-Summit-speaker thing out I still scored just above the midpoint for all speakers at the Summit. Not half bad for a n00b <—Literally!

I am not a professional speaker. In fact I have been speaking for less than a year still. I had a time budget in preparation and I had to make trade offs. I decided to focus vastly more time on demos and script flow than slides or things like inserting jokes in the session. I’ll definitely work to improve the things that people comment on and I’ll share those in a later post (short on time today).

The Scores:

How would you rate the… Score
amount of time allocated to cover the topic/session? 4.17
Speaker’s presentation skills? 4.33
quality of the presentation materials? 4.41
usefulness of the information presented in your day-to-day environment? 4.46
accuracy of the session title, description and experience level to the actual session? 4.5
Speaker’s knowledge of the subject? 4.62

Time allotted: Based on the comments from people who came up and talked to me afterwards at the Summit, they would have loved for me to have kept going for another 20 minutes. I don’t think Adam Machanic ( blog | twitter ) would have appreciated that much but I sure had the material for it! Smile

Presentation skills:I will definitely work on upping my presentation skills but this is the toughest competition I’ve ever been up against so not half bad!

Quality of the materials: This may have been lowered because of the many A/V related comments I received. Or maybe refining my presentation skills will help this too.

Usefulness in your day-to-day environment: is the entire reason I put the presentation together in the first place. My goal for this particular score is not a number but a note from the organizers mentioning the shocking number of people who scratched out the choices and wrote in a number above the range. I will not let people down the next time Winking smile

Accuracy of title: I have some ideas on how to tweak the abstract a little but I’m happy with this score.

Speaker’s knowledge: I’m happy with this score. I’m sure Adam (who was speaking right after me) got nothing but 5s on this score. Considering the company, my score was stellar. I will not spend any time thinking about let alone trying to improve this score.

I’ll post the comments I received in a separate post. Gotta run.

Oh btw… that table up there. Generated if off of an Excel spreadsheet using a single line of PowerShell code! (I’ll blog that soon, promise.)

PowerShell Script to Download SQL MCM Videos

Dec 20th, 2010
by Aaron Nelson.
25 comments

imageMicrosoft has made changes to the Microsoft Certified Master program for SQL Server to make it much more accessible to everyone.  In short if you have the required credentials (MCITP: DBA 2008, DBD 2008), the required skills, and $2,500; you too can become an MCM for SQL Server.  Microsoft has partnered with SQLskills to produce 40 hours of introductory MCM training material videos which they have made freely available to everyone. 

If you’d like more training than just the videos SQLskills has some classes that you can attend.

If you’re like me you’ve either already downloaded the videos or you’re not even reading this right now because you clicked the link and just started downloading the videos.  Smile  Well if you don’t want to spend the rest of your day downloading all of those videos by hand I’ve got a little PowerShell script I think you’ll enjoy.

$wc = new-object net.webclient
[regex]$regex ="(?<url>http://download.microsoft.com/download/./././[0-f]{8}-[0-f]{4}-[0-f]{4}-[0-f]{4}-[0-f]{12}/(?<file>[^>]*?wmv))"
[xml]$xml =  $wc.DownloadString("http://www.microsoft.com/feeds/TechNet/en-us/How-to-videos/SQL_Server_2008_Microsoft_Certified_Master_(MCM)_Readiness_Videos.xml")
$xml.rss.channel.item | foreach {
    if ($wc.DownloadString($_.link) -match $regex)
    {
        $url = $matches.url
        $file =  "$home\Videos\$($matches.file)"
        if (Test-Path $file)
        {Write-Host "$file is already there mate"}
        else {
            Write-Host "Downloading $file"
            $wc.DownloadFile($url,$file)
        }
    }
}

This script will check the RSS feed, connect to all of the links in it, find the first .wmv link that it comes across, then copy that file to your videos directory in your documents folder IF it’s not already there.  If you have to stop this script that’s fine, it will figure out which videos have already been downloaded and skip them*.  If you want to try downloading the .MP4 files instead, just swap out .wmv for .mp4 and you should be all set.  A warning about that though; they seem to be a little more than twice the size of the .wmv files

*This script is written for Windows 7 and maybe for Windows Vista.  You’ll have to edit it yourself for Windows XP or just bug Nic Cain to post one :-)
**IMPORTANT:  I think I have fixed the HTML rendering issues for the code block but you may need to download the script here.

PowerShell First Timers!

First off, welcome to the best addiction that you will ever have!  Since so many people are checking out this post and firing up PowerShell for the first time, I’ve gathered together some useful links.  Here’s one from the ScriptingGuys themselves: How Do I Install PowerShell on Windows 7 and Other Questions 

I have a series of posts you may want to look at to help you get up & running as well as a video I did a while back for MSDN’s geekSpeak:
PowerShell Week Post 0 (5/17)
PowerShell Week Post 1 (5/18)
PowerShell Week Post 2 (5/20)
PowerShell Week Post 3 (5/20)

I’ll blog more later this week about how I put together the script but I wanted to get it into people’s hands ASAP.  Special Thanks go out to Jeremiah Peschka ( blog | twitter ) for not laughing too hard at my first ever attempt at a RegEx as well as Nicolas Cain ( blog | twitter ) & Chad Miller (Blog|Twitter) for giving me pointers on how to use my new RegEx hammer inside of PowerShell.

PowerShell Grab Bag Code

Dec 15th, 2010
by Aaron Nelson.
Comments are off for this post

Thanks to everyone who took the time to check out my PowerShell Grab Bag session today for the PowerShell Virtual Chapter of PASS.  Here’s a zip of all the code that I used and the two links I promised to include.

Today’s Code

Quest’s Free PowerShell Commands for Active Directory

Shay Levy’s ( blog | twitter ) WinRar blog post

Happy Scripting!

Presenting for the PowerShell Virtual Chapter of PASS

Dec 15th, 2010
by Aaron Nelson.
1 comment

pass_logo_thumbSorry for the late notice folks but I will be Presenting for the PowerShell Virtual Chapter of PASS today at Noon Eastern.  Today’s session is just going to be a bunch of cool things that I’ve been working on lately but haven’t gotten a chance to present or blog about yet.  I’m still working on one last script but I’ll post the code before the session if I can.  Hope to see you there!

PowerShell Grab Bag

Wednesday December 15, 2010, 12 Noon Eastern Time (GMT -5)
Speaker:  Me
Live Meeting Information Join the meeting

In this short grab bag session we’ll go over a bunch of new scripts that I’ve built over the last few weeks.  Things like:

  • checking free space on mount points,
  • checking SQL agent for failed jobs,
  • polling instances to make sure they’re available,
  • checking the status of log shipping across multiple targets,
  • emailing result sets and
  • scheduling all these with SQL Agent. 
  • Checking remote registries (including why on earth you would ever want to do that?!).
  • Zipping files, even better, we’ll go over how to not zip files that have already been zipped. 
  • The final grab from this session:  for all the Summit attendees out there, I’ll share with you my script to download all the session videos from the 2010 Summit

I’m on the PowerScripting Podcast

Dec 1st, 2010
by Aaron Nelson.
Comments are off for this post

PowerScripting PodcastTwo Weeks ago I got to sit around a table with Jon Walz ( blog | twitter ), Hal Rottenberg ( blog | twitter ), Ed Wilson ( blog | twitter ) and Mark Mitchell ( blog | twitter ) to talk about PowerShell and the 2010 PASS Summit. Surprised it only took 48 minutes! Open-mouthed smile It’s always a lot of fun to get to sit around and talk with those guys at any time but I’m glad it was recorded so that their community can hear how we’re using PowerShell in the SQL community.

If you’ve never been to their site (PowerScripting.net) it is PACKED with PowerShell tips! You would be amazed at the cool tips that you can find from shows they did a year or two ago and their all it the show notes.

I hope you enjoy the show oh and sorry B/I people, I didn’t mean any thing by the parsley thing Winking smile

Listen:

← Earlier Posts Newer Posts →