Introduction to SQL Server Statistics
Designing High Performance I/O for SQL Server
Data Mining with PowerPivot 2010
_____________________________________________________________

Introduction to SQL Server Statistics
September 7th 12:00 PM EDT (GMT -4)
Presenter: Andy Warren

This beginner to intermediate level session will introduce you to statistics, including how to create, manage, troubleshoot, and more! Statistics are a key part of getting solid and repeatable performance and is easily just as important as having indexes. Whether you’re a DBA or trying to get by without one, this session will give you the information you need to know.

Andy Warren

Andy Warren 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.

Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=2CHB5C&role=attend&pw=W%5Dw5%22K45B

_____________________________________________________________

Designing High Performance I/O for SQL Server
September 8th 12:00 PM EDT (GMT -4)
Presenter: Jimmy May

This session attempts to describe the “full story” of SQL Server I/O.  We will start at the hardware level working our way up the stack through Windows and into the SQL Server storage engine.  I aim to provide in depth information about all aspects related to the fundamentals of high performance storage design for SQL Server and best practices on this complex subject.

Jimmy May

Jimmy is a Sr. Program Manager for the SQL Server Customer Advisory Team.  He is the manager of the SQL Server Customer Lab where customers from around the world bring the biggest, fastest, & most interesting SQL Server apps for tuning.  He is a founder of both the Indiana Windows User Group (http://www.iwug.net) and Indianapolis Professional Association for SQL Server (http://www.indypass.org).  Jimmy recently moved to Redmond from Indianapolis, Indiana with his lovely bride, Phyllis, and Fannie May the Wonder Dog.  Subscribe to and read his SQL Server performance and personal productivity blog at http://blogs.msdn.com/jimmymay.  Contact him at jimmymay@microsoft.com or aspiringgeek@live.com.  Jimmy is also part of the rich & robust SQL Server community on Twitter:  www.twitter.com/aspiringgeek.

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

Data Mining with PowerPivot 2010
September 8th 12:00 PM EDT (GMT -4)
Presenter: Mark Tabladillo

Excel provides a compelling and ubiquitous interface for Microsoft Data Mining. With new features available through PowerPivot, business users can apply the technology through a well-designed infrastructure of Microsoft technologies. This presentation will welcome any newcomers to data mining, and provide interactive demos which highlight data mining through these technologies.

Mark Tabladillo

Mark provides consulting and training for data mining with Solid Quality Mentors. He has taught statistics at Georgia Tech and for the graduate business school of the University of Phoenix. In addition to his Microsoft certifications (MCAD .NET and MCT), Mark has years of deep experience with the SAS System, and has presented at many local, regional, and national technical conferences.  Mark produces a data mining resource and blog at http://www.marktab.net

Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=2J4ZPD&role=attend&pw=8QN%40qdzgQ

I spoke in Columbus, GA and Edmonton, um…, somewhere in Canada on back to back nights this Tuesday and Wednesday.  (I’m kidding, I know it’s in Alberta, and you guys like the Jets right? )  I used the same slide deck for both sessions; well, for Edmonton I didn’t even use a slide deck, but the scripts I covered were a little different. 

I’ve recently learned that if you only have PowerPoint 2007 you’ll need to download this viewer because I built the slides in PowerPoint 2010.  Here are the scripts for Columbus and Edmonton.

I think both events went really well and got a lot of people saying: ‘Hey, this PowerShell stuff could work in my environment’.  I really hope they catch the fever!  I’d love to speak to each of these groups next year and see what questions they come up with and see how much they’re using it.  I’ve got a more advanced session that I will be debuting a little later this year so hopefully I’ll get to share it with them too.

Huge thanks to Jonathan Boulineau ( twitter ) and Colin Stasiuk ( blog | twitter ) for all they do for their groups all year long.

Wow.  I think Nashville may have just thrown down the gauntlet and tried to steal the title from Atlanta for ‘Best Attendee Shirt’.  It’s pretty sweet and I can’t wait to wear mine.  The speaker shirt was excellent as well; I may wear mine to work next Friday.  Unfortunately I couldn’t stick around because I had a date with history but as promised, here are the scripts and slides I used.

As for the session itself.  I got several compliments and saw quite a few good tweets on twitter but I have to say I could have done better.  *As always* I tried showing off a script that I had never dived into during a SQL event and it ended up generating more questions than I expected.  It was a little out of sequence with my normal routine (hey you have to try new things) and unfortunately I realized afterwards that it should have been the 4th script that I demoed not the second.  BUT.  The questions that were asked were great and it was great feedback as to what people were able to pick up on without ever having seen PowerShell before.

I will reseed this scripts for my presentation tonight at the Columbus, GA chapter.  Who know, maybe if I get it right Louis will let me come back and do the full session after the Summit. 

Writing a better Where Clause
SQL Storage for Performance: Best Practices
_____________________________________________________________

Writing a better Where Clause
August 24th 8:00 AM EDT (GMT -4)
Presenter: Scott Gleason

This session will cover the basics of writing optimized query’s with focus on the ‘where clause’ and ‘having clause’ of a select statement. If you have never attended a query performance session before, you’ll learn a lot!

Scott Gleason
Scott has worked in Jacksonville Florida exclusively as DBA for over nine years. He is currently the Vice President of the Jacksonville SQL Server Users Group (JSSUG) and actively speaks about SQL at SQL Saturdays and Code Camps.

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

SQL Storage for Performance: Best Practices
August 25th 12:00 AM EDT (GMT -4)
Presenter: Amy Styers

SQL Storage Planning for Best Performance What are the different storage optimizations available for SQL server? What are some techniques that can be used to determine performance bottlenecks? Amy will discuss tips and tricks from the field on how to answer these questions for planning and designing your SQL infrastructure from disk up!

Amy Styers
Amy is a Microsoft Infrastructure Architect in the EMC Commercial Solutions Advisory Group where she has been engaged for the last three years discussing with customers their options for highly available, highly scalable, high performing Microsoft applications. She regularly addresses customers with regards to their virtualization initiatives and helps customers make decisions regarding architecting their Microsoft infrastructures both in physical and virtual environments.

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

Did you know Atlanta has a B/I User Group?  This new group is a special extension to Atlanta MDF that focuses on providing monthly BI content and discussions for the hard working people of Atlanta.  The first meeting of the B/I SIG of the Atlanta MDF is tonight, 6:30 pm at Matrix Resources in Perimeter.  (Did I mention that Matrix is a wonderful sponsor of us SQL folks here in Atlanta?)  The topic is going to be something I keep meaning to try out: PowerPivot. Come on ouy and help get this new group off to a great start.

Here are all the vital details for you:

Topic: Self-service B/I with PowerPivot
Date: Monday, August 23, 2010
Speaker: Teo Lachev

Meeting Agenda
6:30 – 6:45 – Networking, Announcements
6:45 – 7:15 – Sponsor Presentation(s)
7:15 – 8:15 – Main Presentation(s)
8:15 – 8:30 – Q&A, Discussion

Meeting Place
Matrix Resources
115 Perimeter Center Place
Suite 250 (South Terraces Building)
Atlanta, GA 30346

Directions and parking information

Wow, having been an attendee at SQL Saturday #21 in October of last year (just 10 months ago) I can’t believe we’re already up to #51.  That means we’re averaging 3 a month and there’s no stopping in site; in fact Next year’s schedule is already starting to fill in.  If you’re wondering where the closest one to you is just have a look at the Map.

I’ll be speaking at SQL Saturday #51 about PowerShell just before Allen White and our sessions cover completely different content so please come to both!  As soon as I’m done with my session I’m headed straight out the door and to the “World’s Fastest Half Mile”.  That’s right, I’m taking a 230 mile detour on my way to the night race.  There is going to be a freaking amazing amount of great speakers at this event and if you live within a 6 hour drive of Music City I Highly recommend you don’t miss one of the best lineups since SQL Saturday#33 in Charlotte. 

Next Tuesday I’ll be speaking at the Columbus, GA PASS Chapter.  This was actually supposed to be the first time for me to speak at a User Group meeting (as opposed to a SQL Saturday) but duty called in Atlanta this week. 

As always, there will be something new that I’ve never showed off before, most likely just a failed script from my Atlanta session but hey, they didn’t get to see it yet.    I’ve also been asked to include some sys-admin type stuff so I will do a walk through of Implicit Remoting and probably some important tips on working with Services.  There will also be an XML demo if there are any developers that want to check it out.

Details:

• The meeting will be held at the Columbus Library
• Date: 8/24/2010
• Time: 6:00 PM – 7:30 PM
• 3000 Macon Rd, Columbus, GA 31906

We had a great crowd of about 60 people last night at the Atlanta MDF which was really great considering it was the first day of school for a lot of the parents in the area.  The crowd had a bunch of great questions and while I couldn’t handle all of them I took several notes and will hopefully blog about the answers as I find them over the next couple of weeks. 

As promised, here are the scripts and slides I used.

Resources:
I mentioned several different resources last night and here are the ones I remember (just comment if you’re looking for one I forgot about):

• I did a week-long blog series on PowerShell with SQL Server for SQL University.
• Here’s the CodePlex project so that you can edit PowerShell in Visual Studio 2010 (You also need to install the free version of PowerGUI for this)
• The cheap software to build desktop gadgets with charts and maps is called Power Gadgets.
• A few weeks back I did a post on the top Learning Resources I use to learn PowerShell.
• and of course I can’t finish without saying:  If you need some help getting started from scratch using PowerShell with SQL Server check out my video on MSDN’s geekSpeak.

I’ll be speaking at the Atlanta MDF Monday night (2010-08-09).  I wanted to talk about internals but they’re making me speak about PowerShell.    I have a couple new demos since my Standing-Room-Only presentation at SQL Saturday #41 – Atlanta and as always I will have one new demo that I’ve never shown before.  If you’re in the area please come on out and see why I love using PowerShell to work with SQL Server so much.

Details:

• Please register so we know how much pizza to order.
• Date: 8/9/2010
• Time: 6:30 PM – 9:00 PM
• Place: Microsoft 1125 Sanctuary Pkwy., Suite 300, Alpharetta, GA
• Speaker: Me.

I had a really great time at SQL Saturday #40 in South Florida. I was afraid the full day PowerShell track would really be short on attendees by the end of the day but it actually went much better than I expected! I will post a round-up here shortly but until then here are my scripts and slides from the weekend.

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.
• 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
• and of course I can’t finish without saying:  If you need some help getting started from scratch using PowerShell with SQL Server check out my video on MSDN’s geekSpeak and blog series from SQL University.

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.