The upside

Not so much that we have a better idea what the community wants to see, we were actually pretty on-target, but now we have more confidence in our already well-formed opinions of what they community wants to see.

The downside

It took *a lot* of work to get the internal voting and then the survey pulled together.  This unfortunately led to a much longer than anticipated amount of time to get the full schedule selected and the speakers notified.  Absolutely no one on the team wanted it to take as long as it did.  In retrospect, it would have faster to immediately take the 133 sessions submitted and send all of them out on the survey; that would have reduced some but not all of the delay.   Unfortunately, we just had no way of knowing that at the time and I take full responsibility for not knowing what we didn’t know!  Thankfully, the team was able to overcome the challenges and pull everything together.  The silver lining is that we now have experience in surveying our groups and can do it much more quickly in the future.

Drumroll please…

Alright, enough about surveys and what-not.  Here is what YOU picked:

I look forward to see you at SQL Saturday #220 in Atlanta and can’t wait to hear your feedback on the sessions you attend! 

Survey

Today we have sent out a survey to the folks who have registered for SQL Saturday Atlanta to get their help with selecting a few of the sessions for the event.  Some of the sessions are from speakers whom the team knows we want to speak and we just need help selecting which session of theirs to pick.  We know which session we want to see, but we’re not the only ones who will be there   Because of the strong demand to speak at SQL Saturday Atlanta it is unlikely that any speakers will get two sessions so please make your voice heard!

The other sessions are ones where the team couldn’t come to a consensus on who to select.  We’ve decided to seek your input here as well.

Advanced Sessions

Beyond the survey, I wanted to let folks know that in looking over the abstracts submitted, the team has determined (and voted) that there were not enough Advanced sessions to include on the schedule.  Because of this, we have decided to open the call for speakers again but this time we will only but for “Advanced” sessions only.  Any newly submitted Intermediate or Beginner sessions will not be considered because we’re going to use the feedback from the above mentioned survey to help us finish picking from what has been submitted already.

We’re only holding back 5 session slots for this attempt, a mere 10% of our schedule, so please act quickly.

We value everyone’s time!

Again, we value everyone’s time! Obviously we will give preference to any speakers who already submitted and wants to submit an “Advanced” session for us to consider.  However, if you did not submit already and have an Advanced session you would like to submit, please do!  We will close the call for speakers again as soon as we think we have enough Advanced sessions we can move forward with.  We’re not going to keep it open any longer than we need to.

We’re also not going to hold up notifying the first 45 speakers because of the extended Advanced sessions call, the hold up there has been getting a survey together and out.  Serious lessons learned there!  (but that’s a completely different blog post.)

At the end of the day, we’re just trying to come up with the best content for the people who are missing out on their Saturday to learn more.  I hope this helps explain things and gets people excited about the next SQL Saturday Atlanta!

[Already] Submitted Speakers

You may have received an email today from the SQLSaturday website announcing an extended call for speakers; our goal was to stress the limitations of this call (Advanced topics only), but the system-generated email went out without our intended edits.  We’re VERY sorry for the confusion.

Did I mention that we value everyone’s time?

SQL Saturday is returning to Atlanta on May 18th at Georgia State University – Alpharetta, 3775 Brookside Pkwy, Alpharetta, GA 30022

The day before the event Atlanta MDF is presenting 5 Pre-Cons to choose from. Last year all three Pre-Cons sold out well before the event so get your tickets while you still can!

• Adam Machanic: Surfing the Multicore Wave: Processors, Parallelism, and Performance
• Denny Cherry: SQL Server Security
• Stacia Misner: Languages of BI
• Bill Pearson: Practical Self-Service BI with PowerPivot for Excel
• Eddie Wuerch: The DBA Skills Upgrade Toolkit

Surfing the Multicore Wave: http://surfmulticore.eventbrite.com

Processors, Parallelism, and Performance Today’s server hardware ships with a truly massive amount of CPU power. And while SQL Server is designed to automatically and seamlessly take advantage of available processing resources, there are a number of options, settings, and patterns that can be exploited to optimize parallel processing of your data. This full-day seminar starts with an overview of CPU scheduler internals from the perspective of both Windows and the SQL Server Operating System (SQLOS). Next, you will learn about intra-query parallelism, the method that SQL Server uses to break up large tasks into many smaller pieces, so that each piece can be processed simultaneously. You will come to understand how to read parallel query plans and how to interpret parallel wait statistics, as well as best practices for the various parallelism settings within SQL Server. Finally, you will be introduced to techniques for exploiting parallelism at the query level: patterns that can help the optimizer do a better job of parallelizing your query. After attending this seminar, you will be in full control of your CPUs, able to compel your server to use every clock tick to your end users’ advantage. This session is intended for mid-level to advanced DBAs and DB developers.

_____________________________________________________________

SQL Server Security: http://sqlsecurity.eventbrite.com

During this full day precon we will review a variety of ways to secure your SQL Server databases and data from attack. In this session we will review proper network designs, recommended firewall configurations, and physical security options. We will also review our data encryption options, password protection options, using contained databases, and AlwaysOn Availability Groups security. There will also be discussions about additional measures which should be taken when working with Internet facing applications. From there we will move to the most dangerous attack vector: SQL Injection, including all the ways that attackers can use SQL Injection to get into your system and how to protect against it. The security options for database backups is the next topic on the list followed by proper SAN security designs. We will then finish up by reviewing the auditing options which are available and how they can be used to monitor everything else which we’ve just talked about during the course of the day.

_____________________________________________________________

Languages of BI: http://languagesofbi.eventbrite.com

To get the most of the presentation layer tools in the Microsoft BI stack when data sources include cubes, PowerPivot workbooks, and data mining models, you can’t rely solely on your T-SQL skills.To produce high-performing reports and analytical tools, you’ll need to use other query languages like MDX, DAX, or DMX.

In this workshop, you’ll learn which languages each presentation layer tool supports and the basic syntax used by each language. You’ll also learn how take advantage of query builder tools to help you produce queries until you’ve developed the proficiency to create more complex queries on your own.

You’ll also learn how to work with expressions to add calculations to cubes and PowerPivot workbook, and how to use expressions to customize the appearance of reports, scorecards, and dashboards.

_____________________________________________________________

Practical Self-Service BI with PowerPivot for Excel: http://selfservicebi.eventbrite.com

SQL Server MVP and Business Intelligence Architect Bill Pearson leads this full-day, hands-on introduction to using PowerPivot for Excel to deliver self-service business intelligence.  The focus of the course is to help those new to PowerPivot to become familiar with the assembly of data from diverse sources into models that deliver business intelligence upon demand.  Participants will gain exposure to accessing and relating data, and to employing the Data Analysis Expressions (DAX) language, to construct and share PowerPivot applications that support analysis and reporting throughout the enterprise.

_____________________________________________________________

The DBA Skills Upgrade Toolkit: http://dbatoolkit.eventbrite.com

This full-day seminar is for DBAs and developers with DBA responsibilities looking to beef up their skillsets and simplify their lives with additional insight into their SQL Servers.  Eddie Wuerch takes his extensive experience as a speaker, trainer, mentor, and DBA in one of the largest and busiest SQL Server environments in the world and distills it into a collection of topics for DBAs managing databases of all sizes.

The details for this event have come together quickly.  I know it’s hard to buy a ticket to an event when the speakers haven’t been chosen yet but I’m pretty sure this event will sell out before that happens.  I‘ve heard great things about the previous PowerShell Summits and I’m sure this one will be even better since it will be on the Microsoft campus.

Come help support the new PowerShell.org community as they put on their first community-driven conference! 

In addition to speaking Tuesday morning, I will be answering questions at the SQL Server booths (there’s 12 of them):

Monday from 12-2 and again from 5:30 – 7:30
Tuesday from 3 – 5
Wednesday 10:30 – 1
Thursday 10:30 – 1

If you’re at the conference and you have questions about SQL Server (especially if they involve PowerShell) I hope you’ll stop by!

P.S.  The easiest way to find where the SQL Server booths are is to head over to the Porsche the Visual Studio team is giving away.

I point that out because I don’t want anyone to constrain themselves to talking about logging within just SQL Server.  Please bring your ideas for file transfers, report generating, performance gathering, uptime monitoring and the like.  But don’t stop there!  This is an open invite to anyone that does anything in the SQL Server community.

If Karla Landrum ( blog | twitter ) wants to explain to us how on Earth she keeps track of all these SQL Saturdays around the world, that’s logging!  If Tim Radney ( blog | twitter ) wants to tell us how he makes sure he keeps in touch with all of the chapters he’s responsible for as a PASS Regional Mentor, that’s logging!

How you keep track of blog ideas, white papers you read, or however it is you life-hack *your* SQL world: please share it with us!

Rules
•Your post must be published between 00:00 GMT Tuesday June 12th, 2012, and 00:00 GMT Wednesday June 13th, 2012
•Your post must contain the T-SQL Tuesday logo from above and the image should link back to this blog post.
•Trackbacks should work, but if you don’t see one please link to your post in the comments section below so everyone can see your work

Optional, but highly encouraged…
•Include a reference to T-SQL Tuesday in the title of your post
•Tweet about your post using the hash tag #TSQL2sDay
•Consider hosting T-SQL Tuesday yourself. Adam Machanic keeps the list.

P.S.  Since I’m the host, any PowerShell topic on logging is cool too!

I want to thank everyone who joined the webcast and especially all the people that asked the great questions at the end!  I received some really awesome feedback from attendees about the webcast!! 

When I get some time in a few weeks, I will go back and write a blog post showing code to answer the questions from the webcast.

A surprise to us all, I survived the community vote and will be speaking at SQL Saturday #108 in Redmond, WA this weekend.  I’m very excited that A) at least *some* people aren’t tired of hearing me speak [yet] and B) I get to speak on the Microsoft campus!!!!! 

The folks at Idera are sending Wes Brown ( blog | twitter ) and myself out to this event as part of their ACE program.  If you’ve never been to one of Wes’ sessions, you need to go.  I offer a money back guarantee that you will laugh at least once during his session.  The reason I bring all this up is that they are now recruiting a fresh crop of ACEs for 2012.  If you’d be interested in finding out more about the ACE program, drop David Fargo ( twitter | email ) a line by this Friday and tell him that you’d like to hear more. 

SQL Saturday #111 Pre-Conference Sessions

SQL Saturday is coming back to Atlanta on April 14, and once again, we’ve managed to schedule 3 awesome pre-con sessions for the Friday before (April 13). They will cover a broad spectrum of topics: Performance Tuning, SSIS, and SSRS. Registration is limited, so go take a look and see if you or someone from your company can benefit from this great training opportunity.

Also don’t forget that we will have an absolutely amazing lineup of presenters for Saturday! We’ll be announcing the final schedule for SQL Saturday #111 by Monday, February 13th. Registration is open, and we’re already halfway to capacity, so head out to SQLSaturday.com to reserve your seat.

_____________________________________________________________

Andy Leonard and Matt Masson– A Day of SSIS – http://dayofssis.eventbrite.com/

A Day of SSIS was developed by Andy Leonard to train technology professionals in the fine art of using SQL Server Integration Services (SSIS) to build data integration and Extract-Transform-Load (ETL) solutions. The training is focused around lectures and emphasizes a practical approach. The target audience for this training is database professionals, application developers, and business intelligence practitioners interested in acquiring or expanding their existing SSIS skill set. No experience with SQL Server or SQL Server Integration Services is required before attending this training. It is helpful (but not required) that students possess some knowledge of and experience with relational databases. SQL Server knowledge / experience will be more helpful than experience and knowledge with other technologies.

_____________________________________________________________

Adam Machanic – No More Guessing! An Enlightened Approach to Performance Troubleshooting – http://nomoreguessing.eventbrite.com/

Scratching your head, you stare at the screen. Should you rebuild an index? Create a new one? Reboot the server? Why is this query so slow?!? Figuring out performance problems can sometimes feel like fumbling your way through a dark room. Maybe you’ll get lucky and find the right solution―or maybe you’ll stub your toe. Either way, it’s a slow, potentially painful process. Yet finding the root cause of most performance issues is a simple exercise, once you understand where to look and when. In this full day seminar, you will learn a proven methodology that can be used to approach virtually any performance problem. Created and refined by Adam Machanic over several years, this strategy leverages core SQL Server performance tools (including dynamic management views, Extended Events, and WMI counters), applying them to various performance troubleshooting techniques (such as waits and queues analysis, baselining, and real-time activity monitoring). Each of these tools and techniques has a unique role, and you will learn to use them cooperatively to quickly and adaptively find the actual cause of performance issues. All of this will be illustrated through complete demonstrations that will teach you how to drill from high-level problem detection all the way to specific spot in a query plan or deeper―pinpointing the exact problem and helping you to quickly solve it. Attend this seminar to take full control of your databases—and never again stumble blindly through the dimly lit world of performance troubleshooting.

_____________________________________________________________

Jessica Moss – Learn SSRS in a Day – http://ssrsinaday.eventbrite.com/

SQL Server MVP, Jessica M. Moss, presents an exciting, introductory, full day training session on SQL Server Reporting Services 2008 R2. In the three-part class, Jessica will teach you how to build reports from the ground up. In Part 1, learn the basics of report development, including picking a report development tool and creating your first report. Part 2 delves into visualizations, groupings, and drill-down functionality. Finally, Part 3 highlights core administration tasks in Reporting Services. In addition, Jessica will point out industry-wide best practices for report development and show numerous live demos using a variety of data sources.

Friday, April 13, 2012

Georgia State University – Alpharetta
3775 Brookside Pkwy
Alpharetta, GA

Register for pre-conference sessions by March 15 for $99!
($109 after March 15)

For registration to the Saturday Event and more information visit http://www.sqlsaturday.com/111/eventhome.aspx.

I received multiple requests to post the scripts I used before I even had a chance to sit down so here they are.

If you were in the class and would like to use these and don’t even know where to start with PowerShell have a look at my “Resources for my SQL Server PowerShell Extensions webcast” post.

If you ever get a chance to attend one of their classes and present in front of Paul and Kim TAKE IT!   Even if it’s just about your SSMS tips or how to work around a problem you encountered.  The feedback you get is *more* than worth it!

Building a PowerShell function is almost as easy easier than building a stored procedure around a select statement in SQL.  The reason PowerShell is easier than SQL is that when you have a parameter that you are passing in, you can give it a data type, but you don’t have to.

For the function below I gave it a default value of the local machine but you can pass in a machine name that you are trying to get to.

Just copy the code below into an ISE window and hit F5.

function Get-MachineInfo($ServerName="localhost")             
{            
get-wmiobject win32_computersystem -ComputerName $ServerName |            
select DNSHostName, Manufacturer, Model, SystemType ,             
        @{Name="TotalPhysicalMemoryInMB";Expression={"{0:n2}" -f($_.TotalPhysicalMemory/1mb)}},             
        NumberOfLogicalProcessors, NumberOfProcessors, CurrentTimeZone, DaylightInEffect            
}# End Get-MachineInfo

After you’ve done that, to call the stored proc function simply type in the name ( Get-MachineInfo ) to the prompt at the bottom and hit enter

It came about because some of the SQL MVPs were trying to figure out the best way to download a VM that had been split up into 36 different equal-sized files.  I’m sure there’s a better way than this but at the same time it took me about 4 minutes to write.  Write is the wrong term, more like copy/paste/change a few things.

Give it a whirl and let me know what you think! 

1. Table Size
1. Store Results
2. Compress Tables
1. Compress Indexes
2. Store Results
3. Process Cube
4. Backup Database –PassThru
5. Download Denali CTP
6. Grab Configuration Info
7. New Get-DisksSpace try to pass from Reg servers w/ Group
8. Email Space Report
9. CSV with Bulk Insert
10. Import CSV with Out-DataTable
11. Allen Kinsel – Logs
12. SQL Saturday – Navigate Event Schedule XML

I showed how you could pull size and free space information from all of your data files, then used the same technique to pull size information from all of your tables across every database on an instance.  Later we created a database and some tables, and then showed how to do some basic login comparisons, all with SQLPSX.

In addition, we stored the results of the frees pace and table size information in a database using the Out-DataTable and Write-DataTable functions.  Finally we wrapped up with a quick demo on how the new SQL Server cmdlets inside of the SQLPS Module in Denali combined with some new functionality in PowerShell v3 CTP1 can really make backing up a database with PowerShell a lot easier.

Here’s what you’ll need to recreate today’s demos;

• Download and install SQLPSX
• The Out-DataTable function from the TechNet script repository
• The Write-DataTable function from the TechNet script repository
• and these scripts
• Oh, and here are the handful of slides that I had

I hope I see you next week at the PASS Summit or next month during another webcast, this time on customizing your own functions to work with the SMO.

In the meantime I want to give a quick mention about SQL Saturday #89 happening here in Atlanta in just 12 days. We’ve hit the maximum number of registrations we feel comfortable handling. If you’re on the wait list, we’ll send out an email later this week to people that were able to successfully register but now can’t make it, asking them to unregister. From the looks of the registration counts this might end up being a little under twice the size of last year’s SQL Saturday.

More blogs coming soon!

I woke up at 5 am this morning to kick off my download.  I’ve finished the install and I’m happy that I can finally report that we have new cmdlets for SQL Server. 

First off, after running Get-Module -ListAvailable I saw:

Manifest   SQLASCMDLETS
Manifest   SQLPS

This means that SQLPS inside of SQL Agent should finally allow you to import a module during your job step. 

There is now a Function called SQLServer that, from what I can tell, is the SQLServer Provider.  When under PS SQLSERVER:\> if you do a dir | Select pschildname you will find the following list:

PSChildName
———–
SQL
SQLPolicy
SQLRegistration
DataCollection
XEvent
Utility
DAC
IntegrationServices
SQLAS

From my notes it looks like XEvent, IntegrationServices, and SQLAS are the directories that have been added here.  Can’t wait to try out the IntegrationServices portion!  [Update]: Apparently I may need a defaul instance install for this to work.

I also noticed that Tab-Completion appears to be fixed!!    Also, SQL Registrations > ‘Database Engine Server Group’ are split up into instances like CMS was.  Not sure what that’s all about just yet.  In fact when I try to use it, it doesn’t seem to work.  Maybe I should read the help files.  But I think they may have just gotten CMS & RS reversed.  CMS isn’t working for me either yet.

When you drill down under XEvents and go to your Denali instaince you will see: Packages, & Sessions.  I guess this means that I finally need to read Jonathat’s blog series on X-Events.

When I imported the SQLPS module I received a warning about unapproved verbs.  Oh well, hopefully they’ll work that out soon.  Next I ran Get-Command -Module “SQLPS” and found the following list of cmdlets.

Analysis Services got some love too in the form of 11 cmdlets!  After inporting the SQLASCMDLETS module I ran this Get-Command -Module “SQLASCMDLETS” and found the following.

More news to follow soon!

PowerShell has taken the Pole for the DBA division at SQLRally!  Come see why the organizers have chosen this session to lead the pack to the Green Flag.

We will be covering new ground, not rehashing last year’s PASS Summit presentation.  I have developed several new examples for leveraging PowerShell in your everyday DBA role since then.  I am promising 6 new scripts for everyone but if I get on a role that number might be closer to 10 when they drop the green flag on this year’s event. 

If you’ve never used PowerShell before don’t worry. We will spend a few minutes on a ‘get up to speed lap’ so everyone is going at the same pace when we step on the gas with all this new material.  All scripts will be made available for you to download during the session and access later on from pit-row.

After my session here’s what I’ll be checking out:

What sessions I'll be checking out during SQLRally

Deploying SQL code with PowerShell can be very easy but why would you want to do that instead of just open a script in SQL Management Studio and run it?  The number 1 reason that I can think of is when you need to deploy to multiple destinations.  Let’s get started with something simple, we’ll deploy a table that I use in the post on collecting database sizes.

Invoke-Sqlcmd -ServerInstance Win7Netbook -Database CentralInfo -InputFile C:\temp\dbo.DatabaseFileSizes.SQL

That’s a pretty simple example and one that doesn’t offer too much any benefit over just opening up SSMS and running the script.   Let’s take it a little further with the loop construct and this time we’ll deploy Adam Machanic’s ( blog | twitter ) sp_WhoIsActive to a bunch of machines

$DeployFile = C:\temp\who_is_active_v11_00.sql
<# Loops through Registered SQL Severs and applys WhoIsActive #>
foreach ($RegisteredSQLs in dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\Development\ | where {$_.Mode -ne "d"} )
{
"Deploying to "+ $RegisteredSQLs.ServerName;
Invoke-Sqlcmd -InputFile $DeployFile -ServerInstance $RegisteredSQLs.ServerName -database master}

So now we can see how we can deploy a SQL Script to multiple machines with just 4 lines of PowerShell.  The script obviously doesn’t have to be deploying code.  It could inserting/updating data or even verifying permissions.

Marco provides an overview of using sysprep for Windows Server 2008R2, and SQL Server 2008R2, which also now supports this feature. This is especially useful when wanting to get up a new (lab or even production environment) quickly. He will be doing this using only Microsoft Hyper-V & Windows PowerShell without any other external tools.

Marco Shaw
Marco ( blog | twitter ) is a consultant with CGI and has over 12 years of IT experience. He has been awarded a Microsoft MVP award for the last 4 years in the Windows PowerShell category.

LiveMeeting: Registration Link
_____________________________________________________________
Designing Cubes for Performance
April 20, 2011, 12 PM EDT (GMT -4)
Stacia Misner

Building a cube is simple. After all, you have a lot of wizards at your disposal to do the development work. But building a cube that delivers data quickly requires you to make additional changes to the database design. In this session, you’ll learn what steps you need to take in the development process to ensure the cube is designed for optimal performance, whether you’re using Analysis Services 2005 or Analysis Services 2008. You learn not only how to properly design dimensions, aggregates, and partitions, but why these design principles improve performance.

Bio:
Stacia Misner is a consultant, educator, mentor, and author specializing in Business Intelligence solutions since 1999. With more than 25 years of experience in IT, she is the author or co-author of ten books about BI. Her most recent books include Business Intelligence in Microsoft SharePoint 2010 and Building Integrated Business Intelligence Solutions with SQL Server 2008 R2 and Office 2010. Stacia provides consulting and custom education services through Data Inspirations, writes about her experiences with BI at blog.datainspirations.com, and tweets as @StaciaMisner.

LiveMeeting: Link
_____________________________________________________________
Database Standards SOP
April 21, 2011, 1 PM EDT (GMT -4)
Thomas LeBlanc

Join Normalization nut Thomas LeBlanc for a review of a standard operating procedure used among DBAs at an employer. See the changes he made after joining the BI group at this employer. The session will go through naming conventions, check list for creating a table, formatting in stored procedures, and more. A brief preview of the SQLRally talk 3rd Normal Form: That’s Crazy Talk!!! Will be given about Lookup tables. This discussion comes from 21+ years of developing databases for application developers. The use of identity columns for primary keys, and the need for a unique constraint on transaction tables that do resort to ID columns will be covered.

Thomas LeBlanc
Thomas is a semi-retired Senior DBA turned ETL Lead Architect @ Amedisys in Baton Rouge, LA. Worked in the IT field for 21 years experience w/ COBOL dBase, FoxPro, Visual FoxPro, VB 3 thru 6 and .Net(C#). Designing and developing Normalized & Dimensional database has become his passion. Full-time DBA work started about 9 years ago. he has been blessed with speaking at SQLRally this year. Free time is spent helping other & improving his relationship with family & God.

LiveMeeting: Link

Building Applications That A DBA Will Love
April 12, 2011, 12 PM Eastern
Presenter: Andy Warren
Attendee URL: LiveMeeting

It’s natural for the focus to be on the end user when we build applications, but it’s important and worthwhile to build into our design some things that make it easier for the DBA to provision, tune, and support the product we deliver. So what does a DBA care about? Performance of course, but also security, scalability, space usage, and yes, even documentation. This presentation will cover tips in a number of areas that address the concerns of the DBA, and we’ll talk about why you should care about making the DBA happy too!

Andy Warren

Andy Warren is a SQL Server consultant and trainer who occasionally writes some code too. He’s on the PASS Board of Directors, was a founding partner in SQLServerCentral.com, started the SQLSaturday event format, and is currently a SQL Server MVP. Andy blogs at www.sqlandy.com, and can be reached via Twitter as @sqlandy and on LinkedIn at www.linkedin.com/in/sqlandy.

How do I attend?
Attendee URL: LiveMeeting
_____________________________________________________________

Efficient and effective processing of OLAP CUBES with AMO and XMLA
Fri April 15th 12PM EST (US)
Speaker: Steve Simon

Topic Description:

Within the financial industry, rapid and effective decision making is critical to our client relationships. Analytical tools such as OLAP cubes help make this a reality. In many 24 X 7 enterprises, data capture and cube updates occur on an ongoing basis throughout the business day. We are able to achieve this through the innovative utilization of SQL Server Integration Services (SSIS). This intermediate level hands-on presentation will show the attendee how we have utilized Analysis Management Objects (AMO) commands and SSIS to ‘fire’ our OLAP CUBE updating processes. The presentation will discuss the high level concepts behind Analysis Management Objects (and the XMLA that is generated from the commands) and the scenarios under which we can benefit from their usage. Topics covered will be the updating of dimensions, measures, partitions, the cube and more.

Bio:

Steve Simon is an Assistant Vice President with State Street Corporation in Boston. He has been involved with database design and analysis for over 26 years. Steve has presented papers at eight North American PASS Summits (in Orlando, Seattle WA (4), Denver CO (2) and San Francisco CA), two at PASS Europe 2009 and one at PASS Europe 2010. He has just recently presented his PASS 2008 Summit presentation in Johannesburg South Africa. Steve is actively involved with the SQL Server community within the Boston area and is a regular presenter at the New England Code Camps and SQL Saturday events and participates actively on many of the Microsoft User Forums. He is also the Virtual Chapter leader of the Professional Association for SQL Server (PASS) Oracle – SQL Server Virtual Chapter and is a PASS regional mentor. He will also be presenting a data mining paper at SQL Teach in Montreal between May 31^st , 2011 and June 3^rd 2011.

URL: https://www.livemeeting.com/cc/usergroups/join?id=RPN4J9&role=attend&pw=J%25%7E*7r%229d

Last year I kept hearing about all these people using something called GTD (Getting Things Done).  They didn’t talk about it much.  But occasionally one of them would send out an excited tweet mentioning GTD.  I got tired of hearing all these excited tweets and finally decided to dive in.  I had actually purchased the audiobook of GTD years ago but never got around to listening to it.  I was only a few pages into the book when I had the urge to make a big list of everything I have to finish.  I started out putting everything into a spreadsheet.  Everything from “Script new Mount Point Daily Check Job” to “Remember to pickup dry cleaning” was going into this list.  I would frequently realize that I was behind on a certain task; so I’d stop and take care of that item. 

After 6 hours I was in love with this technique.  After 18 hours I was slightly overwhelmed.  And after  48 hours I was ready to quit.  I had a list of over 80 items, and adding to that list did help me finish any of those items.  In fact the list left me feeling that life was more out of control than before my to-do items were written on paper.  I couldn’t understand how anyone –let alone all these people I had heard of – could get anything done using method.  Like my first attempts with PowerShell, I closed it down and decided it wasn’t very useful.

At the same time I had just started a new job much closer to home.  I had an extra hour available for every workday, but I was accomplishing less than ever.  It felt weird.  Almost everything was in place – so much so that I felt anyone should be able to succeed given these conditions – and yet I wasn’t!  I wasn’t getting to the gym more, I wasn’t spending significantly more time with my daughter and I wasn’t all that much less stressed.

I took a while, but after seeing that list and realizing how much was there I recognized that, short of having two clones, I was never going to be done with my to-do list.  What to do?  Simple.  That list must be shorter. With the writing on the wall I decided to categorize the things on my now out of date GTD list and see what I could scrap.  To say it was a punch in the face would like saying Southerners like Barbeque.  “It ain’t dinner unless a pig had to die.”  Right now my daughter is 13 and still listens to me at least pretends to listen when I speak.  I’m not sure how much longer that will last.  Her school is 2.2 miles away from both our home and my office  (For those playing at home, that is a 12 minute Atlanta commute. ). At the end of the day life is about priorities and I’ve finally figured mine out. So until my daughter stops letting me drop her off at school, play taxi-cab for her and her friends, and generally allows me to be seen with her in public… well, I’m not going to have a lot of free time to do much else

So what does that mean?  Am I going to stop blogging, writing, speaking, everything?  I don’t know, quite possibly.  Maybe for a while, maybe for a good while.  I have two items in my Professional Development category that I’ve committed to get done (and thankfully they both won’t take up very much time at all).   I’m still going to finish those two and they might result in some interesting content.  But for everything else?  Well following the GTD method those are all marked as “Maybe-Some Day”.

– Thanks for the idea Tom

This one would look great in your email signature!

This one would look great in the tight-hand column of your blog

Or this one:

If you’re using the PrimePress theme like me you’ll want to put this under /wp-content/themes/primepress/headers

Paul’s query is pretty simple:

SELECT os.Cores, df.Files
FROM
(SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
(SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;
GO

I simply took that query and wrapped it with almost the same code that I used at SQL University.  The difference allows me to show off two new tricks. 

• First, I separated out the part that grabs the list of servers from your Registered Servers to happen before the foreach loop.  I had received feedback from a reader that A) Having it there made it hard to read on my website, and B) it simplified the logic flow.  It also has the added benefits of performing faster when you’re running against hundreds of instances like me, and making it a little more clear how to swap it out when you want to use something besides Registered Servers to list your instances.  For more information on the different data sources that you can use for reading in a list of servers have a look here at Post 5.
• Second I added this $S++; it is a little piece of code to enumerate the instances for you.  Last time I had written the results to a table and then read them out using DENSE_RANK but since we don’t have multiple results this time [and because I love showing off cool PowerShell code] I used this instead.

$ServerList = dir -recurse SQLSERVER:\SQLRegistration\'Database Engine Server Group'\ | where {$_.Mode -ne "d"}            

foreach ($RegisteredSQLs in $ServerList )
{
$S++;
$dt+=invoke-sqlcmd2 -ServerInstance $RegisteredSQLs.ServerName -database master -Query "
    (SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
    (SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df" -As 'DataTable'
}            

$MultipleResults = $dt | ConvertTo-Html -Property Instance, Cores, Files | Out-String;            

Send-MailMessage -To paul@SQLskills.com `
 -Subject "tempdb Survey Results" `
 –From YourEmailAddress@GoesHere.com `
 -SmtpServer YourSMTPServerGoesHere `
 -Body $MultipleResults -BodyAsHtml

Hopefully this will help you email query results to your heart’s content   Big thanks to TJay Belt ( blog | @TJayBelt ) for pointing out this request on the #SQLHelp hotline to me.  I’ve done this tons of times myself and I LOVE that anyone can walk up with a question and when people don’t have the answer they still do what they can to help them out right away.  I less than three community!!

Please Note:

DO NOT SPAM Paul Randal! You should probably try emailing this to yourself a time or two before you decide to send it to him.

I’m using Chad Miller’s (Blog|Twitter) invoke-sqlcmd2 to output the results as a data table (you’ll need that).

Finally a friendly reminder that unlike SQL Server, PowerShell persists variables so if you run this multiple times you’ll want to clear out the $dt variable like this: $dt=$null.

I have to tell you, this doesn’t get old:

I love that somebody on the other side of the planet is telling someone else that I ‘probably have a PowerShell script to solve their problem’. The funny thing is that a lot of the time I don’t have a script for the problem they’re trying to solve *yet*. I love it when someone gets referred to me and I don’t have a script because it gives me a chance to go explore the language and figure out something I don’t know. PowerShell makes this quest pretty fast.

Ironically Paul White ( blog | twitter ) didn’t know I had a script for that, he was just making a joke. Well, actually, I didn’t have a script for that. I did however know right where to find the person that did; and that is even better than if I had a script of my own. Why is it better that I had to point someone some where else? Could I not have figured this script out own my own? No that’s not it at all.

PowerShell is NOT a hammer it’s an Automation Language. Better yet PowerShell is a Toolset, and it happens to come with some pretty freaking awesome hammers built in. We all understand that SQL Server isn’t just a hammer, that it’s an entire toolset. Inside of that toolset is: T-SQL, SQL Agent, Profiler, SSIS, the SMO, SSRS, SSAS etc… just to name a few.

The cool thing about PowerShell is it’s actually built to work with other hammers. So the reason that you hear me talking about PowerShell all the time is not because I’m replacing my T-SQL Hammer, I’m just automating it! Like this, here’s a quick script to figure out how long your SQL 2008+ instances have been running:

<# Number of Days since the Instance has been restarted #>
$InstanceList = "WIN7NetBook", "WIN7NetBook\R2", "WIN7NetBook\SQLExpress"
foreach($Instance in $InstanceList)
{
Invoke-Sqlcmd -Query "SELECT @@SERVERNAME AS 'ServerName'
, DATEDIFF(D, Sqlserver_start_time, SYSDATETIME()) AS 'NumberOfDays'
  FROM sys.dm_os_sys_info" -ServerInstance $Instance -Database master
}

The automation doesn’t stop with SQL Server. You can find plenty of posts form people in our SQL Community on working with things like Red-Gate’s SQL Compare, Outlook, Active Directory, and Subversion just to name a few. The key here is that all of these people are automating something adjacent to their SQL world and they’re using PowerShell to make it happen.

Heck just last week I wanted to know how many sessions had been submitted to SQL Saturday #67 in Chicago. Do you think I went to the schedule page, copied the list of sessions into Excel and then checked to see what line number the last session was on to get my count?? Heck NO!! Last year I wrote a script for Allen Kinsel ( blog | twitter ) to count how many people at the PASS Summit had registered their twitter handle when the signed up. I took that script, changed the link, changed the term I was looking for and presto! I now know that 105 sessions have been submitted to SQL Saturday #67.

That has nothing to do with SQL Server right?!… Except for that whole bit about it being a SQL Saturday that I was interested in.

<#Start up a web client and download the web page into a text file#>
$url="http://www.sqlsaturday.com/67/schedule.aspx"
$file="c:\temp\DownloadSession$(((Get-Date).ToString("yyyyMMddHHmm"))).txt"
$webclient = New-Object system.net.webclient
$webclient.DownloadFile($url,$file)            

<#Sift through the file for lines that include "viewsession" #>
(Select-String -path $file -Pattern "viewsession").count

OK, so what’s your point?

My point is that I wouldn’t have even had the twitter script that I started with if Allen Kinsel hadn’t taken a chance and sent me an email at noon the Friday before the PASS Summit and asked me if I could use PowerShell to count the number of attendees with twitter handles.

Again, what’s your point?

My point is Use PowerShell to make one tool talk to another.

And…

And tell people faced with an annoying problem that you think I’ve got a script for that because whose knows, maybe I do!

When I do my presentations I have a set of scripts that have the name of the computer and instance I am working with.  I’m not a fan of using localhost or anything like that; I also like to use instance names that let people know what version of SQL that I’m using.  When I switch computers I spend less than a minute changing all of the names.  At the PASS Summit I used a laptop that I had just purchased; here’s the script I used to rename everything.

foreach ($SC in dir "$home\Documents\PoSh\" -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
(Get-Content $SC) | Foreach-Object { $_ -replace 'KILIMANJARO', 'R2' } | Set-Content $SC
}

To make this work for you just change out the highlighted parts above to whatever you need.  The part that says “$home\Documents\PoSh\” will go to the PoSh folder under your “My Documents” directory (If you don’t have one, now’s a good time ).  You can also use a path like C:\SQL\Databases\AdventureQuirks\ here.  The -replace ‘KILIMANJARO’, ‘R2′ portion seems pretty self explanatory; same goes for -recurse.

If you just wanted to search for all the files with a certain table name, column name, stored proc name, etc… and return a report (not to modify) you can use something like this:

#Make sure to navigate to the directory that you want to start looking in:
## 
cd c:\temp            

foreach ($SC in dir -recurse | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}

This piece of code is setup slightly different solely to demonstrate another way you can use this functionality.  Make sure that you navigate to directory that you want to search in first for this script.  When you get the results you may end up seeing the same filename listed more that once because the “pattern” appears in the file multiple times.  To make the results show each filename just once simple add -List.

One final option that I want to call out is -Filter.  If you want to search for only .SQL files in a directory (or .PS1, .txt, whatever) just add this: -Filter *.SQL

So you might end up using something like this:

foreach ($SC in dir C:\SQL\Databases\AdventureQuirks\ -recurse -Filter *.SQL | where{ Test-Path $_.fullname -pathtype leaf} )
{
Select-String -path $SC -Pattern "WIN7NetBook"
}

Alright I better stop here before I start showing off some other features that I just learned.
Hope that helps!

To answer Paul Randal’s question of why I should get to win a week of training from SQLskills this is what I have to say:

Plain and Simple.  I’m a Second Generation DBA with over a decade of experience with SQL Server.  I want some deep level ideas for some new PowerShell scripts to simply tasks for DBAs everywhere.  I’ll also spend countless hours telling all my fellow DBAs here in Atlanta what all I learned until they can take it no more and demand this level of top-notch training for themselves! 

I’ll get started with the PowerShell scripts right now even.  This little script will search twitter for a giver hashtag, in this case: #SQLskills.  It will then save everything it finds off to an XML file and then query it:

$SearchHashtag = "sqlskills"            
            
$wc = new-object net.webclient            
$url = "http://search.twitter.com/search.atom?q=%23$SearchHashtag"            
$OutFile = "c:\temp\Download_$($SearchHashtag)_$([DateTime]::Now.ToString('yyyyMMddHHmmss')).xml"            
$wc.DownloadString($url) | add-content $OutFile;            
            
[xml]$tweetxml = get-content $OutFile            
            
$tweetxml.feed.entry | Format-List            
            
($tweetxml.feed.entry).count