Last week I spoke to the Atlanta PowerShell User Group about Remoting and Background Jobs.  There are a couple different flavors of Remoting but the one I’m most excited to show people is called Implicit Remoting.  What if you were on a machine that only had SSMS 2005 and you had to run something against a remote server that had SSMS 2008?  Enter Implicit Remoting.

With Implicit Remoting, essentially you temporarily download the cmdlets from the remote machine onto your local machine. When you execute the cmdlet it runs against the remote machine.

Before we start I want to set the scene.  On your local machine you need to have PowerShell 2.0.  The remote machine needs PowerShell 2.0 and SSMS 2008 installed.

Setting up the Remove Server
Log into the remote server via Remote Desktop and create a profile (notepad $pshome\profile.ps1) that contains this:

add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100
 

Next create a new Remoting Configuration to load your SQL Items into your profile:

(* I used the ISE profile)

Register-PSSessionConfiguration -Name SQLSupport `
-StartupScript C:\Users\YourUserNameHere!!!\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1

Setting Up Your Local Machine
Open a Power Shell window that has no profile loaded. Run the following:

$RemoteSession = New-PSSession -ComputerName MyRemoteServer -ConfigurationName SQLSupport

Connect to the remote session you just defined in the variable:

Enter-PSSession $RemoteSession

Run this command to test your setup. You will see the SQL Server cmdlets from the snapin that was loaded with the Remoting Configuration.

get-command invoke-sqlcmd

Exit the session:

Exit-PSSession

Now we want to Import invoke-sqlcmd.  This will allow you to run your scripts against the remote server session that you defined.  This might not seem like much, but if you are working over a VPN from home this could be very useful.  Plus you don’t have to keep switching between your local shell and the remote one (*more on that another day).

Import-PSSession $RemoteSession -CommandName invoke-sqlcmd

get-command invoke-sqlcmd

Putting it to Use
Now that all of this is done what command will you run?  Well anything.  Just because I wanted to see if it could be done I went ahead and ran Back Woodys ‘backup all user databases script’.   Run anything you want, but a good start is something simple like:

invoke-sqlcmd -query "sp_databases" -database master `
-serverinstance localhost | format-table

Happy scripting.

It feels like forever since I spoke at a SQL Saturday!  The South Florida SQL Saturday will be help on July 31st, 2010 at Devry University – South Florida, 2300 SW 145 Ave, Miramar, FL 33027. Event checking will be at 7:30 with the Keynote beginning at 8:00 and sessions beginning at 8:30.    I can’t wait to get down there.

Max Trinidad ( blog | twitter ) has put together an entire day of PowerShell training for this event and I get to be one of the speakers!  I’ll be doing my PowerShell for Data Professionals session.  Which I am very proud to say I will be presenting  a longer and more in depth version of this session at this year’s PASS Summit, so come cathch the preview!  I’ve got a new trick to show the fine folks of South Florida and I can’t wait to get their feedback.

Besides myself the three other PowerShell speakers are going to be: Ronald Dameron ( blog | twitter ), David Corrales from Sapien, and of course Maximo Trinidad.

Here’s what that PowerShell track is going to look like:

Ronald Dameron Why SQL Server DBAs should learn PowerShell

Ronald Dameron Automate ID Administration w/ PowerShell & SQLPSX

Aaron Nelson PowerShell for the Data Professional

David Corrales Sneak Preview: SAPIEN’s Visual PowerShell 2011

Maximo Trinidad Working with SQL Server – SQLPS

Maximo Trinidad Using PowerShell with SQL Server Agent

I saw a tweet on the #SQLHelp hotline last night from Mike Talon ( blog | twitter ) asking “Is there an #Exchange version of #SQLHelp?”

My immediate reaction was: Wow!!  What a compliment to the #SQLPASS community that other communities want to help each other like we do!  [Insert Giant Community Pat-On-The-Back]

I know less than nothing about Exchange.  I’m pretty sure that it runs on Windows and I of course know that you can manage with PowerShell Oh, and my Outlook doesn’t work if it can’t connect to Exchange; and that’s pretty much it.  So in the spirit of #SQLHelp I had to lend what hand I could, and that’s only in naming.  The key is to be short and recognizable.

• #EXCHelp  Can’t do it because people could mistake the hash for Excel.
• #EXCHHelp  Was my suggestion because it’s short and hopefully recognizable to them.
• #EXCHGHelp  Was Mike’s idea, he said that EXCHG was a common way of referencing Exchange. 

I really have no idea what’s going to be a success for them here but I wish them the best of luck in getting the word out on whichever hashtag they settle on.

I am We are now monitoring #PoShHelp on twitter for #PowerShell help

The #SQLHelp hashtag has been an overwhelming success and it is time for PowerShell to get their own help tag. The PowerShell community is incredibly generous and this tag can help those generous and knowledgeable folks impart their wisdom when you are struggling. I think #PoShHelp will be a great success.

How to Ask #PoShHelp Questions

Sign up for a Twitter account.  You don’t have to follow anyone, but if you want to, I’d suggest following Steven Murawski’s PowerShell Twitterers list instead of individual people.  Brent Ozar has a post on how to use Twitter lists, but in a nutshell, they let you keep in touch with a lot of people who focus on a particular topic.  The cool part is that their tweets don’t clutter up your main Twitter page, which is important because there’s several hundred PowerShell folks on Twitter as of this writing.

When you need help, write a tweet and include #PoShHelp in the tweet, like this:

Why not just use the #PowerShell hashtag that everyone already uses?

People use that hashtag for anything PowerShell related.  Blogs, news stories, events, pictures, funny stories, training, even problems: all things that someone wants to let everyone else in the community know about.  The problem is that it can become very chatty and so I can’t have tweetdeck notify me every time that someone says something about PowerShell.  I do have a job ya’ know

What not to use the #PoShHelp hashtag for?

Pretty much the #1 rule is:  Don’t use the #PoShHelp tag for marketing.  The tag is there for people to help other people they may have never met.  Because of this (and just how insanely generous all these PowerShell people are) they might setup the advanced notifications in twitter apps like tweetdeck to notify them every time someone asks for #PoShHelp.  If you’re trying to promote software, events, or your blog (and the blog post has nothing to do with how you helped someone that asked for #PoShHelp) then don’t use #PoShHelp just use #PowerShell.

While the PowerShell community isn’t quite as large as the SQL community it’s still pretty darn big and everyone I’ve ever learned PowerShell from is on twitter.

* Please Note:  A portion of this post was completely ripped off and hacked back together from a post Brent Ozar originally did on the #SQLHelp hashtag

Please come join us for our sixth meeting

When: Tuesday July 20, 2010 at 6 pm
Where: 211 Perimeter Center Parkway, Suite 200, Atlanta, GA 30346
How: Please RSVP so we can get a head count, bring your laptop if you can
What:  People from all over Atlanta getting together to talk PowerShell

The July meeting of the Atlanta PowerShell User Group will be all about Jobs and Remoting. The presentation will be presented by Aaron Nelson, aka SQLvariant. <—Oh wait, that’s me!

Some of the items covered include:

• How to run PowerShell scripts without having to wait for them to complete.
• How to run PowerShell scripts on multiple remote systems.
• How to interactively run PowerShell on remote systems.
• much much more.

Please RSVP so we can get a head count. Pizza and drinks are provided by the ALTPUG thanks to UGSS.org Our meeting will be held at the New Horizon’s training center at 211 Perimeter Center Parkway, Suite 200, Atlanta, GA 30346. Click here for directions and a map. We currently do not have access to the site’s computers so bring your laptop if you can.

Yesterday a conversation on twitter about PowerShell learning resources lead me to realize that: The PowerShell tab on my own site could use some updating BUT: It’s grown to the point where it’s probably not all that useful for someone starting out.  To that end here are 7 8 key resources that I would recommend for learning PowerShell:

• Listen to the PowerScripting Podcast
• The Show Notes for the PowerScripting Podcast are freaking treasure trove of info so subscribe to that blog and go exploring what was mentioned in past shows.
• Subscribe to Jonathan Medd’s PowerShell 2.0: One Cmdlet at a Time  blog series.
• Jonathan Medd also co-hosts a podcast on PowerShell. Subscribe to both of these, load them on the mp3 player and listen to them on your way to work. You’ll learn without realizing it.
• Windows PowerShell In Action (Second Edition):  An excellent book that even includes some of the “hows” and “whys” they made the tough decisions in this new language.  
• Get yourself on the PowerShell Tip of the Day email (or RSS Feed)
• Windows PowerShell™ 2.0 Best Practices: This book is really thick but it is written in a way that is very easy to read even for people that are sill learning.  Everyone should feel comfortable picking up this book.
• And of course:  If you run into a problem just tweet the ScriptingGuys.  Also be sure to subscribe to their daily blog (including weekends & holidays) where they lay out how to solve all kinds of questions people ask of them.  And if you problem is longer than a tweet.  Scripter@Microsoft.com

I use quite a few more learning resources but this is where I started and where I would recommend for people to get started.  I hope this helps! 

Recently Andy Leonard ( blog | twitter ) tweeted: “I was asked recently about secrets to success. My reply? “Never fear failure.” :{>” I really agree with this in a lot of ways.  Surprised?  Don’t be, there’s a big difference between ‘not fearing failure’ and ‘liking failure’.  I don’t get up in the morning because I like to fail, I just know that it’s going to happen.  A lot.

I approach a new programming feature by reading through it’s capabilities one command at a time.   After I’ve read the definition of a command then I like to play with the examples.  After that I’ve done some of the things it says it can do, then I like to try and make it do things that weren’t actually listed.  I like to test the boundaries of a feature. This is where some “controlled failure” comes in.

Boundary testing is useful for a number of reasons. A best practice for using the feature might be established, but without testing the boundary you won’t understand why it is the best practice. When I’m done testing boundaries for myself I look at what others have done with the feature.  I find out what problems they’ve experienced and what innovations they discovered.  Because I’ve tested the boundaries myself I can better understand examples online and discuss these discoveries with other data nerds.  By doing this I learn from my failures and other peoples failures too.

This appetite for failure spills over into my work–and I’d bet it spills over into your work too.  At the end of the day though no matter how big or awesome a solution we create chances are a ton of failure that went into it.  On a good day I probably fail at trying more things by noon than most people do all day, heck maybe even all week. Embrace failure as part of the learning process.  It makes you a better employee and even a better mentor at work, because let’s face it, if you can fail and then persistently succeed yourself then you’re better positioned to help a struggling coworker find that persistent success too.

TechEd is coming to Atlanta in May 2011!

That’s right in just 10 months over 10,000 IT Professionals from around the world are going to descend on Atlanta for a week.  I was really surprised to hear this since they were not that far away in New Orleans this year but who cares this is awesome news!

Why is Microsoft bringing TechEd to Atlanta?  Well they sure didn’t check with me but my first guess would be that it has something to do with the fact that:

• The Georgia World Congress Center is accessible to 80 percent of the American population in two hours or less (via car, train or the World’s Busiest Airport).
• Or maybe that it’s easier to fly direct to Atlanta than any other place on earth.

Although instead of focusing on being open and accessible to as many people as possible Microsoft could have just been looking out for peoples wallets.  The over 12,000 hotel rooms located in close proximity to the Georgia World Congress Center are probably the cheapest of any of the 10 largest cities in America.  (I think only Dallas can come close.)

So how on earth can over 10,000 IT Pros fit into a single convention center here in Atlanta and it not be cramped?  Pretty easily actually because:

• The Congress Center’s Building B exhibit halls are more than twice as long as Atlanta’s highest skyscraper is high (which happens to be the tallest building in the Western Hemisphere outside of New York and Chicago).  From one end of these halls to the other, the earth curves 3/4 inch.
• As many as 125,000 people have attended a single event at the GWCC.
  • For relevance sake: that’s approx. 50x larger than the PASS Summit
  • Or… Larger than every PASS Summit and every SQL Saturday ever held, COMBINED.
• The lobby of Building C alone is large enough to hold the Titanic.
  • Building C: entrance lobby is over 1,000 feet long, 80 feet wide and over 90 feet high
  • Titanic: 882 feet long, 92 feet 6 inches wide, and 60 feet 6 inches from the waterline to the main deck.
• Heck there’s over 90 acres or 3.9 million square feet throughout the GWCC

The local users groups here in Atlanta are busy working on ideas to capitalize on this great opportunity and you can look forward to me blabbing all about what we’re up to sometime in the near future. 

* Facts obtained from http://www.gwcc.com/about/Fun_Facts.aspx, http://www.atlanta-airport.com/Airport/ATL/ATL_FactSheet.aspx, http://en.wikipedia.org/wiki/Bank_of_America_Plaza_%28Atlanta%29

I just wanted to take a quick moment to let everyone know that yesterday I received the great news that I will be speaking about PowerShell at the 2010 PASS Summit!    Here’s what my session will be:

The Dirty Dozen: PowerShell Scripts for the Busy DBA

You’ve heard it said, “If you have to do it twice, automate it.” That’s great advice and PowerShell provides a simple but extremely powerful way to do it. PowerShell is the future of Windows scripting. Cut the learning curve and get a real handle on this powerful automation tool. This session walks you through a dozen scripts to simplify and easily automate time-consuming and tedious elements of your day to day job. This isn’t stuff you’ll use SOMEDAY, these are scripts you can use when you get home tonight.  Harness the power of PowerShell to easily find Servers short on space. Script out tables and constraints across all of your databases at once. Backup databases and restore them to a different environment. These tricks and many others will allow PowerShell to simplify your job like no other tool.

Speaker:

Aaron Nelson
Senior Database Administrator

I owe a huge thanks to everyone who helped me make this a reality and I’ll write a post thanking many of them when I get some time.

For those of you that need to get your hands on the graphics for the 2010 PASS Summit in Seattle here they are:

Loops, Lookups and Splits: SSIS Beyond the Basics
Managing Teams
Why Should I use Stored Procedures?
_____________________________________________________________

Loops, Lookups and Splits: SSIS Beyond the Basics
July 13th 9:00 PM EDT (GMT -4)
Presenter: Tim Mitchell

In this session, we’ll explore some of the useful tasks and data flow components of SSIS. Join in as we review the File System Task and the SSIS looping containers, as well as data flow components including the Lookup Transformation, the Conditional Split, and the Derived Column. This session will include various demonstrations using SSIS 2008.

Tim Mitchell

Tim Mitchell is a Microsoft SQL Server consultant, developer, and speaker. He has been working with SQL Server for over 7 years, working primarily in business intelligence, ETL/SSIS, and reporting. He has earned numerous certifications and holds a Bachelor’s Degree in Computer Science from Texas A&M at Commerce, and is a Microsoft SQL Server MVP. Tim is a business intelligence consultant for Artis Consulting in the Dallas, Texas area. See his full profile and blog at www.timmitchell.net.

How do I view the presentation?
Attendee URL:  Live Meeting link

_____________________________________________________________

Managing Teams
July 13th 1:00 PM EDT (GMT -4) / 10:00 AM PDT (GMT -7)
Presenter: Andy Leonard

Are you a member of a team of database professionals? Are you part of a developer team? Do you manage or lead a team? This presentation is for you. Andy Leonard shares experiences, war stories, and lessons learned from years of managing and leading teams.

Andy Leonard
Andy is an Architect with Molina Medicaid Solutions, SQL Server database and Integration Services developer, SQL Server MVP, PASS Regional Mentor, and engineer. He is a co-author of Professional SQL Server 2005 Integration Services and SQL Server MVP Deep Dives. Andy blogs for SQLBlog.com. His background includes web application architecture and development, VB, and ASP; SQL Server Integration Services (SSIS); data warehouse development using SQL Server 2000, 2005 and 2008.

Click here to join the Live Meeting.

_____________________________________________________________

Why Should I use Stored Procedures?
July 14th 12:00 PM MDT (GMT -6)
Presenter: Jack Corbett

With the proliferation of ORM Tools like Linq To SQL, NHibernate, there has been a move to no longer use stored procedures for Data Access, I personally disagree. This will be a professional and polite open discussion starting with why I believe stored procedures are the best way to access data in SQL Server. Alternate view points welcome.

Jack Corbett
Professional who has worked with SQL Server for 10 yrs (6.5-2008) and experience in .NET development as well. Author of several articles for SQLServerCentral and video author for SQLShare.com. Speaker at several SQLSaturday’s and Florida User Groups. Co-President of OPASS. Lover of all things SQL except Analysis Services and MDX.

Live Meeting Link: https://www.livemeeting.com/cc/8000181573/join?id=7FKD56&role=attend

_____________________________________________________________

I had a really great time at CodeStock this weekend!  I plan on doing a wrap-up post later this week but I just wanted take a moment and post my slides and scripts that I used during my PowerShell for Data Professionals session.

It seemed like now would be a good time to update the SQL Saturday Map so here it is.  The center-left of the map is starting to fill in nicely.  I hear that California has the most people of any state in the union and yet they only have 1 SQL Saturday…  What’s up with that?? [UPDATE: News of SQL Saturday San Diego is starting to come in!]

If you know of any SQL Saturdays that haven’t made this list please let me know.  Even if they are still in the discussions/planning stage I’d still like to get it on the list.

Green: held a SQL Saturday prior to 2010
Yellow: holding their first SQL Saturday in 2010 or early 2011
Turquoise: I’ve heard rumors over twitter…

Event Date	Event Name	iCal
Jul 31, 2010	SQLSaturday #40 – South Florida 2010
Aug 14, 2010	SQLSaturday #28 – Baton Rouge 2010
Aug 21, 2010	SQLSaturday #51 – Nashville 2010
Sep 18, 2010	SQLSaturday #46 – Raleigh 2010
Sep 18, 2010	SQLSaturday #50 – East Iowa 2010
Sep 18, 2010	SQLSaturday #55 – San Diego 2010
Sep 25, 2010	SQLSaturday #52 – Colorado 2010
Oct 2, 2010	SQLSaturday #53 – Kansas City 2010
Oct 2, 2010	SQLSaturday #48 – Columbia 2010
Oct 16, 2010	SQLSaturday #49 – Orlando 2010
Oct 23, 2010	SQLSaturday #54 – Salt Lake City 2010
Jan 22, 2011	SQLSaturday #45 – Louisville 2011
Feb 5, 2011	SQLSaturday #47 – Phoenix 2011

 
_____________________________________________________________

ETL with PowerShell
June 22nd 9:00 PM EDT (GMT -4)
Presenter: Chad Miller

In this session we will look at performing common data loading tasks with PowerShell. A basic understanding of PowerShell is helpful, but not necessary. Specific topics covered include importing structured files, XML, WMI objects and ADO.NET data sources.

Chad Miller
Chad Miller is a Senior Manager of Database Administration at Raymond James Financial. Chad has worked with Microsoft SQL Server since 1999 and has been automating administration tasks using Windows PowerShell since 2007. In his spare time he is the Project Coordinator/Developer of PowerShell-based Codeplex project SQL Server PowerShell Extensions (SQLPSX). Chad leads the Tampa PowerShell User Group and is also a frequent speaker at SQL Server users groups, SQL Saturdays and Code Camps.

How do I view the presentation?
Attendee URL:  Live Meeting link
_____________________________________________________________

How SQL Server Indexes Work
June 23rd 12:00 PM EDT (GMT -4)
Presenter: Sharon F. Dooley

There are many useful presentations about indexes. Most of these concentrate on tricks and techniques. This presentation will provide the background knowledge of index architectures so that you can understand why techniques work and select the appropriate indexing strategies for your databases. Topics covered include page splits/fragmentation, disk accesses required for different index types, covering indexes and optimization.

Speaker Bio:

Sharon Dooley (MCP, former SQL Server MVP) has worked with SQL Server since its first release. Her major areas of specialization are database design and performance tuning, though she also does a lot of administration. She has a consulting practice and teaches and writes courses for Learning Tree International, where she is SQL Server Curriculum Manager. Ms. Dooley is the author of SQL Server 7.0 Essential Reference (New Riders), the lead author of Professional SQL Server 6.5 Administration (Wrox). Sharon is an active member of PASS (The Professional Organization for SQL Server) and has presented at numerous conferences. She was named a Microsoft MVP in 2001 and was an MVP for SQL Server until 2004. In her spare time, she enjoys being bossed around by her cats.

Attendee URL: https://www.livemeeting.com/cc/8000181573/join?id=7DJZ27&role=attend

_____________________________________________________________

Reporting Services for the DBA
June 23rd 12:00 PM CDT (GMT -5)
Presenter: Ef Romero

Reporting Services for the DBA: Using Reporting Services to monitor and affect changes to SQL Server.

Ef Romero:
Ef Romero is a Senior Consultant at Hitachi Consulting where he specializes in business intelligence development with SQL Server. His work with SQL Server is concentrated in ETL, data warehouse development, database optimization, and reporting. He received his bachelor’s and master’s degrees, both in pure mathematics, from UTEP and has worked as a mathematician and BI developer for the past four years. He is a collaborator on the recently published book SQL Server Reporting Services Recipes: for Designing Expert Reports.

Attendee URL: Join the meeting

Two weeks back I had the opportunity to present a session on PowerShell Windows Admin Commands at the Atlanta TechStravaganza.  I was a little worried about the presentation because I had never presented to a crowd of system administrators before.  Luckily I pulled it off and believe the standing room only crowd learned a lot and discovered ways to make PowerShell relevant to their work.  They did stump me with a question about opening ports but luckily Mark Schill ( blog | twitter ) was able to provide the correct answer for us all. 

Thanks to everyone who took time out of their Friday to come by and see it.  I got a lot of great comments from people in the hallway after my session.  Some of them really liked the different approach I took to presenting it.  As promised here are all of the Sildes and Scritps that I used during the session.

Oh and thanks to all the sponsors!

Please come join us for our fourth meeting

Note: Due to a scheduling conflict with the hosting facility our meeting this week is being held NEXT WEEK, a week later than normal.

When: Tuesday, June 22, 2010 at 6 pm
Where: 211 Perimeter Center Parkway, Suite 200, Atlanta, GA 30346
How: Please RSVP so we can get a head count
What:  People from all over Atlanta getting together to talk PowerShell [Profiles]

Our June meeting will be all about PowerShell Profiles. Mark Schill will be demoing his profile. Some of the key concepts that he will be covering include:

- aliases
- one-liner functions
- other functions
- setting prompt
- env vars (environmental variables)
- "configuring your environment"
- sync/roam across machines
- modular (dot-source or import) vs monolithic (all in one file)

If you have any cool tips/tricks that you use in your profile and would like to share, email them to Mark.Schill@cmschill.net.

For the second half of the meeting you will be working on and discussing your own profiles so make sure to print your profiles and collaborate with your colleagues.

Again, please RSVP so we can get a head count.  We currently do not have access to the site’s computers so bring your laptop if you can.

del.icio.us Tags: PowerShell,Atlanta,User Group

Providers

What are Providers?  Providers in PowerShell are a hierarchical namespace way flattening out an infrastructure to make it accessible and traverse-able just like a directory structure hard drive.  Why do you need them?  Well you quite possibly may not ‘need’ them per-say but they sure do make things easier to work with from time to time.  In case you don’t already know, you can traverse your SQL Server [2008] as if it were just another drive in either SSMS 2008 or in the PowerShell ISE if you add the Provider Snapin.

To make sure that you have this snapin installed on your machine see yesterday’s post but this time we are going to add the ProviderSnapin instead of the CmdletSnapin. 

To do that just run

add-pssnapin SqlServerProviderSnapin100

and poof you can traverse SQL Server.  Let’s take a look at what we might see.  Run

get-psdrive

and you should see a list of drives including SQL Server.  Now if you have more that one instance you will be able to access all of them through this one “SQLServer:\” PSDrive that you now have.  In a lot cases what we find inside of these drives will be similar to what we see in the Object Explorer view in SSMS but almost always have something extra too.  Let’s take a look at that here by changing directories down to our databases

cd SQLSERVER:\sql\WIN7\KILIMANJARO\

Now let’s go ahead and run the good old “Dir” command and we should get back something like this

dir

As I mentioned before, we got back more than we see if we expand our SQL Server node in the Object Explorer window of SSMS, now if we CD to Databases and do another dir we will see not just more than what we see in SSMS, we see what at first glace to a SQL person is just garbage (but it’s not). 

cd Databases
dir

What you’re seeing here is the Methods and Properties of the databases in your instance.  What are Methods and Properties?  Well if you’re like I was 6 months ago you have no idea what Methods and Properties are.  Methods are the Verbs of what you can do to your database (Create, Rename, Drop, Shrink) and Properties and the Adjectives that describe your database (Collation, CreateDate, Compatibility Level, Owner, AutoShrink Enabled).  To get just the list of the names of your databases you’re going to have to run this:

dir | select-object name

Ok so now we can see a list of databases that our Provider made available to us.   So what?  Well I’m going to dive a little deeper tomorrow but for now let’s just go with one final example on scripting out tables (more on that here).  Run this: cd AdventureWorks\Tables Then this:

foreach ($tbl in dir )

{
$k="C:\Temp\" + $($tbl.name) + "_table.SQL"
$tbl.Script() > $k
}

I really hope you have temp directory on your C:\ drive.  If so go have a look what’s there now

So is that it?  Almost.  While SQL Server 2008 only comes with one provider and it’s for the Relational Engine a few people have taken it upon themselves to create their own Provider for things like Analysis Services,  Reporting Services, and event BizTalk up on the codeplex site.  I can say that I have tried the Analysis Services project and it does work. 

Cmdlets, Functions and Snapins

So far we’ve learned how to open up PowerShell and add on to it with some modules; then we learned how to create a variable and populate it with anything from some numbers to an entire table.  Today we’re going to quickly look at another way of expanding PowerShell with something called a snapin so that we can get to some cmdlets.

First, what are cmdlets?  Cmdlets are similar to DOS commands but have some noteworthy differences.  In SQL Server terms you might think of cmdlets as the System Stored Procedures of PowerShell and think of functions as the regular user defined Stored Procedures of PowerShell. 

Cmdlets and functions accept parameters just like stored procedures do.  Just like stored procedures you can even define default values for these parameters in case the user doesn’t have one to pass in.  Unlike stored procedures though there is actually a drive that you can go to and see all of your functions.  Just run this and you’ll see what I mean dir function:

Snapins are akin to finished product code. They were created for PowerShell 1.0 and were succeeded in PowerShell 2.0 by modules– which are more like the manifest list for deploying a bunch of SSIS packages.  If you have SSMS 2008 installed you’ve got yourself two extra snapins already.  And if you have Idera’s SQL Diagnostic Manager installed you may be in for a real surprise when you retrieve your list of snapins.  Run this command to see all of the extra snapins that are available to you:
get-pssnapin –registered 

At the very least you should see this:

If you don’t see those two you’ll need to install SSMS 2008.  If you don’t have a copy of that handy you can get the free version of it here.  The SqlServerCmdletSnapin100 snapin give us the 5 cmdlets that come with SQL Server 2008 and today we’re going to focus on Invoke-SQLcmd which basically just calls out to good old sqlcmd.  To load this just run:

add-pssnapin SqlServerCmdletSnapin100

From there we can do a quick query like this:

invoke-sqlcmd -query "sp_databases" -database master -serverinstance WIN7\Kilimanjaro | format-table

Now the reason that I brought up functions is because they override cmdlets and that’s really important to know.  If you run this command get-command invoke-sqlcmd you will only see one thing returned for invoke-sqlcmd.  However if we create a new function like:

function invoke-sqlcmd { get-date }

You end up overriding the Invoke-SQLcmd with your new function.  Give it a try, run just this invoke-sqlcmd and you should end up seeing something like this:

So how do you know when you’ve done something like that?  Just run get-command invoke-sqlcmd again and this time you will see this:

I realize that we kind of rushed through that info but thankfully there is only one more piece to the basics before we can get on to code that you can use on a daily basis

Getting Started With PowerShell Variables

Yesterday we laid the ground work for PowerShell Week.  Today we will learn about Variables in PowerShell. You will notice that PowerShell variables work very differently from SQL Server variables.

The evolution of variables in SQL Server is pretty straight forward. In SQL Server 2000 you had to DECLARE a variable as a specific type in one statement and then SET the value of the variable in another statement, like this: DECLARE @SQLvariable VARCHAR(12) SET @SQLvariable = ‘OldFeature’. By the time SQL 2008 hit the streets we could DECLARE and SET a variable in one statement like this: DECLARE @SQLvariable VARCHAR(12) = ‘NewFeature’ . PowerShell evolves variables to the next logical step.  You no longer need to declare or type your variable. Instead PowerShell uses the value of the variable to infer it’s type. If a variable does not alreayd exist PowerShell simply creates it for you.  In PowerShell our statement looks like this: $SQLvariable = ‘NewFeature’. (Beginer tip: $ identifies variables in PowerShell just like @ identifies them in SQL Server.)

Since it doesn’t work in SQL let’s try it in PowerShell: $SQLvariable = ‘NewFeature’.  OK so we loaded data into a variable, ‘big deal’ right?  How do we know that we actually loaded it and more importantly what datatype is it?  Well in PowerShell you can simply call the variable to get its value. (Another beginer tip: you don’t need to use a keyword like SELECT for this, just the variable name: $SQLvariable.)  What if you just want to determine the datatype?  All you have to do is tack on the .GetType() Method: $SQLvariable.GetType().

$SQLvariable = 'NewFeature'
$SQLvariable
$SQLvariable.GetType()

When you run this here’s what your result will be:

As I alluded to earlier, another feature of PowerShell is that it remembers your variables for you, even between executions.  Basically as long as you don’t end your session the variable, it’s datatype, and its value will persist.  You will see later than you can also set a variable in one script and consume it in another.

Let’s try this again with a new variable and set it to a different value:

$PoShvariable = 123
$PoShvariable
$PoShvariable.GetType()

What happens if once the datatype is set we try to set it to a different, incompatible datatype?  It’s going to explode right?  Nope, you’ll have to throw something more difficult than that at it.  Give this a try:

$PoShvariable.GetType()
$PoShvariable = 'NewFeature'
$PoShvariable
$PoShvariable.GetType()

Since PowerShell relies on .NET a variable could be any datatype in .NET so if you’re sitting inside the AdventureWorks database you can see for yourself, just run this:

$MyTable = get-item TABLES\Production.TransactionHistory
$MyTable.GetType()            

$MyStoredProc = get-item StoredProcedures\HumanResources.uspUpdateEmployeePersonalInfo
$MyStoredProc.GetType()

Now earlier I mentioned that PowerShell keeps track of and remembers the variables that you previously declared.  So how do you see what variables are already there?  Just run this command and you can see every variable you have running in the session, even the internal ones.

Variable

In the list of items that get returned you’ll see one called Error that holds the last error message that occurred in your session.  This handy feature can help you when you have to debug your own work.

Is that all there is to know about variables in PowerShell?  Not hardly, but I think that’s a good stopping point for this post.  More on variables later.

Welcome to PowerShell Week at SQL University.  For regulars at SQLvariant, SQL University is something Jorge Segarra organized to get industry experts together to create learning courses around SQL Server.  These learning courses are in one week segments that focus on a specific area. 

When I began blogging about PowerShell there were some complications based on your operating system. There were so many people blogging about these issues that I didn’t address it initially.  Since this is an introductory course I decided to go ahead and spin up a brand new Windows 7 VM so that I can walk you through these small but vital details.  If you don’t have Windows 7 this would be a great time to upgrade.  If you can’t upgrade then download PowerShell for you operating system from this link.  Now, onto getting started:

Disclaimer:
PowerShell opens in a protected state as a security measure.  To enjoy the full power of PowerShell (and run scripts you downloaded off of the internet) you need to run without these safeguards. In today’s lesson we are simply launching PowerShell and priming it for the scripts and work we will interact with in the next several lessons.

To get started with PowerShell just click the Start button and type in ISE to locate the Integrated Script Editor in your program listing.  Right click and launch PowerShell in Run as Adminsitrator mode:

Enter Get-ExecutionPolicy as a command. If you haven’t used PowerShell yet you will most likely see that the Execution Policy is set to “Restricted”.  You’ll need to run the command Set-ExecutionPolicy RemoteSigned to be able to run the scripts and follow along with the lesson.  You’ll also get a popup that you’ll have to say yes to. 

Your output should look very non-informative like this:

Now that we have that set let’s go to SQLPSX.CodePlex.com and and download the SQLPSX project.  It’s currently in version 2.2.1 and consists of 9 modules containing 133 advanced functions, only 2 cmdlets and 7 scripts.  It’s for working with ADO.NET, SMO, SQL Agent, RMO, SSIS, SQL script files and using the PowerShell ISE as a SQL query tool:

NOTE:  Update at bottom with workaround:

During the installation we will run it without modifying the profile (just yet).

Once you’ve installed it successfully you should now have a WindowsPowerShell directory under your My Documents if you didn’t have one already (which is really important):

After it’s installed we should be able to open up the ISE again and run this command :

Get-Module -ListAvailable

And finally we’re going to get to the step where we load something up.  Run import-module SQLServer to load up the main SQL Server module.  After you run that command it won’t give you any output telling you that it successfully loaded so you can run this command write-host -foregroundcolor DarkGreen “Modules Loaded: $(get-module)” to have it tell you what Modules it currently has loaded.

Alright that pretty much wraps it up for today.  I know we didn’t do a lot with PowerShell and SQL Server yet but this gets some important ground work out of the way so that hopefully everyone is able to script along for the rest of the week.  (If you’re dying to read ahead have a look at this post: Basic Querying From PowerShell)

If you run into any issues please leave a comment and I’ll be glad to lend any help that I can.  See all you back here tomorrow.

UPDATE:

There seems to be an issue with loading the SQLServer Module as I had described.  I have found this to be a work-around:  Go to \\~\My Documents\WindowsPowerShell\Modules\SQLIse\Modules\ and copy everything except for the SQLISE folder, then go to the \\~\My Documents\WindowsPowerShell\Modules\ folder and paste all of those items.  At this point you should now be able to run the command and get it to load up properly. 

I will post more when I found out other alternatives.

Please come join us for our third meeting

When: Tuesday May 18, 2010 at 6 pm
Where: 211 Perimeter Center Parkway, Suite 200, Atlanta, GA 30346
How: Please RSVP so we can get a head count
What:  People from all over Atlanta getting together to talk PowerShell

What is a script club you ask? You bring an idea for a script, and ask your fellow PowerShell users for help getting the script written. If it’s PowerShell, its covered — just bear in mind that it may be hard to test things like Exchange scripts, Active Directory, or SQL Server management scripts unless you have a nice virtual lab on your laptop. What are the Rules of Script Club?

1. You always talk about script club
2. You always talk about script club
3. If someone asks for help, and you can help, you help
4. Two people help one person at one time
5. One module per person per night
6. All scripts, all PowerShell
7. Scripts will be as short as they can be
8. If this is your first time at Script Club, You have to script!

Please RSVP so we can get a head count. Our meeting is graciously sponsored by PowerShellCommunity.org.

Last week I did a podcast for geekSpeak and a similar session at SQL Saturday #38 in Jacksonville.  I’ve already blogged about most of the scripts in those sessions but here is a download of a bunch of those scripts.  I have changed them a little from the way they were written in my session so that they can run independently without worrying about whether or not you need to load a snapin or module.  If you need it to run the script, it’s in there.  Don’t forget that for all but one of them you will either need to have SSMS 2008 installed or the SQLPSX codeplex project.

If you don’t remember which script it was that you were interested in, here’s the recording of the session that I did for geekSpeak.

Enjoy!

Oh, and don’t forget Buck Woody’s disclaimer about running scripts you find on the internet:

Next week is the 24 Hours of SQL Server 2008 R2 PASS but before we get there the Virtual Chapters of PASS have some free training on tap for us:

_____________________________________________________________

“You Got XML In My Database? What’s Up With That?”
May 11th 12:00 PM EDT (GMT -4)
Add to Calendar
Presenter: Stuart Ainsworth

A brief presentation exploring the marriage of XML and relational databases, including when it works and when it doesn’t. Coverage will include various use case scenarios, and some tips on how to improve performance using design techniques.

Stuart Ainsworth:

Stuart I Ainsworth, MA, Med is a Database Architect working in the realm of Financial Information Security; over the last 15 years, he’s worked as a Research Analyst, a report writer, a DBA, a programmer, and a public speaking professor. He’s one of the chapter leaders for AtlantaMDF, the Atlanta chapter of PASS. A master of air guitar, he has yet to understand the point of Rock Band (“You push buttons? What’s that all about?”).

How do I view the presentation?
Attendee URL:  Live Meeting link
_____________________________________________________________

Indexing Guidelines
May 12, 2010 Noon Mountain Time (-6 GMT)
Presenter: Greg Larsen

This session will discuss indexing guidelines. During this discussion there will be information about how developers and DBA’s should approach the indexing development lifecycle. This lifecycle will help attendees understand that you shouldn’t just develop some indexes while you are developing your applications and then forget about them. In addition some indexing guidelines will be discussed. These guidelines will help you make better choices on which columns to select for your indexes and how those columns should be ordered in multi-column indexes. There will be number of demonstrations to support this discussion. Lastly an indexing toolkit (a set of scripts) that you can use to help manage your indexes will be provided.

Greg Larsen:

Greg Larsen has been working with SQL Server since 1999. He has authored over 125 articles related to SQL Server. He holds a MCITP Database Administrator and Developer certification for SQL Server 2005 and is a SQL Server MVP. Greg also hosts the www.sqlserverexamples.com website which contains a number of T-SQL examples to help you manage your SQL Server environment and application T-SQL code. Greg has a full-time DBA gig, but also provides SQL Server consulting services in his spare time.

Door Prize: There will be a drawing for a $50.00 Amazon Gift Certificate
Registration: You can attend the meeting without registering but if you want to be entered in the drawing, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=jhh2nc28185sd034 no later than 5:00 PM Eastern on May 11th.

Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=CPN75W&role=attend

_____________________________________________________________

12 Key Development Areas
May 13th 1:00 PM EDT (GMT -4)
Presenter: Andy Warren

A good professional development plan has to go far beyond ‘learning more technology’, it has to encompass the wide variety of skills you need to build the next step in your career, and it has to include a variety of learning methods. We’ll discuss how much time and money you should budget when building your plan, and how the distribution of time and money changes as your career evolves. We’ll look at the growth phases you’ll encounter, learning how you learn best, how to keep up with new technology without burning out, and even how things like blogging and Twitter can be an interesting part of your plan.

Andy Warren
Andy Warren (@SQLAndy) is a SQL trainer focusing on basic administration and performance tuning, he runs the free SQLShare.com training site, is currently a SQL Server MVP, blogs daily at SQLAndy.com, started the SQLSaturday franchise,  is co-President of the Orlando SQL Server Users Group, serves as a member of the Board of Directors of PASS, and was a founding partner in SQLServerCentral.com.  In his remaining free time he’s working on a book for first time managers and squeezes in some woodworking for relaxation.

Live Meeting Link: https://www323.livemeeting.com/cc/usergroups/join?id=5233Z9&role=attend

_____________________________________________________________

Extract, Transform, and Load your Data Warehouse
May 14, 2010 12:00 PM EDT (GMT -4) Add to calendar
Presenter: Jessica M. Moss

One data warehouse buzzword that often gets used is “ETL”, also known as extract, transform, and load.  But what does this really mean from a data warehousing perspective and how do we use SQL Server Integration Services to perform these ETL functions?  This session will answer these questions and show solutions to common ETL problems.

Jessica M. Moss

Jessica M. Moss, an architect with Ironworks Consulting and a Microsoft SQL Server MVP, is a well-known practitioner, author, and speaker in Microsoft SQL Server business intelligence.  Jessica has created numerous data warehousing solutions for companies in the retail, internet, health services, finance, and energy industries and authored technical content for multiple magazines, websites, and the book “Microsoft SQL Server 2008 Integration Services Problem-Design-Solution”.  Jessica enjoys working with the central Virginia community and speaks regularly at user groups, code camps, and conferences.  You can read about her work on her blog, http://www.jessicammoss.com.

Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=CPPC3S&role=attend

_____________________________________________________________

Having problems with my blog feed so this is just a temporary post.  I hope to get things fixed this weekend.  Does anyone know how to fix a blog feed that is bigger than 512k?

This code here will loop through a list of servers that you supply it in a text file and then spit out only the Mount Points on those servers that have less than 20% free space remaining on them.

foreach ($svr in get-content "C:\PowerShell\AllServers.txt")             

{             

$svr; Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr `
| where{$_.Name -ne "_Total" -and $_.PercentFreeSpace -lt 20} | select-object Name, PercentFreeSpace | format-list            

}

If you didn’t make it to my webcast on MSDN’s geekSpeak yesterday you can catch me at SQL Saturday #38 in Jacksonville this weekend.  As always I’ll be including a few new scripts since the last time I gave this presentation.  There will be a lot of great speakers down in Jacksonville and the last I heard, there were over 550 people registered.

In addition to my PowerShell for Data Professionals session I will also be doing a presentation on using Hyper-V and SCVMM to manage your Dev and Test environments called Virtualize This! 

Oh, and please remember to bring lots of good questions.

The other day I was answering a question in the forums on PowerGUI.org that I thought would be a quick answer.  The only problem was that I had my T-SQL hammer in my hand and not my PowerShell hammer.  When I read the question I thought of a way to solve the problem using T-SQL and PowerShell; the only problem was I didn’t think outside of this toolset.  PowerShell MVP Shay Levy ( blog | twitter ) chimed in with a much better answer.

Now granted I usually prefer to give answers that your typical data professionals will be comfortable with, ones that pass along a query, but in this case I have to go with the solution that you might not be as comfortable with.  There’s a good reason though:  It’s faster.

here was my answer:

#Only load this part once when you open up your PowerShell Session
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100            

#Create a text file in your c:\temp\ directory called AllServers.txt
#Put each server\instance on a new line.
foreach ($svr in get-content "C:\temp\AllServers.txt")             

{
#sp_databases | format-table
invoke-sqlcmd -query "SELECT @@SERVERNAME AS 'ServerName', @@VERSION AS 'SQL Version'" -database master -serverinstance $svr >> "c:\temp\MySQLServerVersions.txt"
}

and here was Shay’s answer:

$null = [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')            

Get-Content Allservers.txt | Foreach-Object {
   $sql= New-Object Microsoft.SqlServer.Management.Smo.Server $_
   $sql | Select-Object @{n='ServerName';e={$sql.name}},@{n='Version';e={$sql.version}}
}

Without a doubt Shay’s answer is better because it returned the same results in a better format AND ran in a quarter of the time of my answer.  In my environment my solution took a respectable 60 milliseconds to run, Shay’s answer using the SMO took 15 milliseconds.  What’s my point today?  The SMO is worth learning because it can be faster.�
Where can you learn the SMO?  I’m glad you asked   Take a look at this MSDN article by SMO expert Allen White ( blog | twitter )

Hey Folks,

Have you ever been about to take a trip somewhere new and thought ‘hey, I wonder if I’m close enough to a PASS Chapter to catch their meeting’ or for speakers, ‘if I can speak at their meeting’?  Well guess what, I’ve had the same question.  So I went to the PASS website and tried to figure out if Abilene was close to Houston.  Unfortunately there was no easy way for me to figure that out because the chapters were all listed on separate pages and there is no map.

So I did what I’m sure everyone does, I sent an email to Blythe Morrow ( twitter ) saying hey Blythe, “we need a map off all the PASS chapters around the world”.  Blythe said “That’s a great idea Aaron!!  When can you have it done?”

Doh!  I didn’t realize that I was volunteering myself for this but I figure hey this is our PASS so I smiled and said “I don’t know, send me the data and we’ll see”.  I built out a map, and we ran into some issues with chapters not having quite the right address the software could recognize. Fun stuff like that. 

Along the way I realized Rob Farley ( blog | twitter ) not only knows how to do this stuff, but can even do it with Silverlight.  I roped him into my little guerilla-community-service project, but we quickly noticed a problem:  quite a few of the chapters have addresses that are not where the group actually meets but  instead where the chapter leader lives or works.  That being said, we quickly realized that we now need all 211 chapters to give us their meeting addresses, not their mailing address.

Right now all we can show you is a static map of the chapters in the US.  If we can get all of the addresses from all of the chapter leaders verified, we can give you a zoom-able map of all the chapters in the world sometime soon (*that soon part is completely up to the chapter leaders). 

What needs to be done?  We have sent out an email to all chapter leaders asking for at the very least their meeting address and which day of the month the chapter meets.  If they also want to include a twitter handle for the group and/or chapter leaders, a picture of the chapter leader(s), chapter logo, or anything along those lines we can add those as well.  Please send this information as a reply to the email that has been sent out to all of the Chapter Leaders.

[Click on the map for the full size version]

Next week I am giving a PowerShell for Data Professionals session on MSDN’s geekSpeak.  If you haven’t heard of  geekSpeak all you really need to know is they have one primary rule for presenters:Your webcast is limited to only 1 Power Point slide. geekSpeak presentations focus on practical live examples and interactive question and answer sessions that allow you to learn from a presentation in a way that you can immediately start applying to your job.

https://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032449694&EventCategory=4&culture=en-US&CountryCode=US

If you haven’t caught this presentation at a SQL Saturday yet, or if you want to see it again, then this is a chance to watch from your desk and follow along in Powershell for yourself.

The show is at 3pm Eastern/Noon Pacific.  You can see previous geekSpeak shows and subscribe to their feed here.  Please stop by and heckle me check it out.

Hey folks, I did a quick update to the SQL Saturday Map.  I went ahead and color coded the cities that have already done a SQL Saturday in a previous year as Green, cities that are holding their first SQL Saturday this year as Yellow, and cities that are in discussion to hold a SQL Saturday but aren’t official yet as Turquoise.

Has anyone else noticed that despite being home to a SQL conference, Las Vegas hasn’t held one yet?  Or as Brad Shultz pointed out, San Francisco nor Silicon Valley have held one.  I wonder if there’s a Chapter in Key West?  I know there’s one in the Cayman’s; I’d love to speak there.

I also built another map using the same colors to show where you can still catch a SQL Saturday this year.  Did you know that you can learn some PowerShell at almost every one of these events?   I will update this one later and try to include the info off the location page and things like that.

Just a quick post here.

Yesterday there was a discussion around the notion of SQL Saturdays hitting a saturation point.  Statements like that remind me of the guy who was the head of the patent office and said that there was nothing left to patent.  I say that because I think that if we are in fact being successful with these events, as we expand the capabilities of DBAs DBDs and BIDs around the country, we will also expand what they want to learn next.  Not to mention the value proposition that these people bring to the table at their jobs.  Who knows, maybe I’m wrong and we’ll look back at this post 4 years from now and say “wow Aaron, that was a dumb prediction wasn’t it”. 

In any event Andy Warren ( blog | twitter ) thought before we got too far into this discussion we should have a look at a map so that we’re all working from the same frame of reference.  I’m currently working on some mapping stuff that you’ll find out more about later this week so I volunteered to whip up this map.  It’s basic and shows all SQL Saturdays past and currently scheduled on the home page.  If you click on the map it will take you to a larger version of it. 

There looks to be a lot of open space there in the middle, plus I hear they do SQL in Canada (or at least organize events around it).

I think a city the size of Atlanta could easily handle 2 SQL Saturdays per year depending on size; and the greater L.A. area could probably handle 4 per year so long as they move it around.  What do you guys and gals think?