Painful or Helpful? No SSMS Multiserver Results if One Instance Errors

I've been going back and forth with this question for a few days now.

If one instance in a multiserver query presents an error, no resultsets from any of the other instances are displayed, even if n "row(s) affected)" is displayed in the Messages tab.

This is particularly painful when querying dynamic management objects across server groups, because new DMV's and new DMV columns are being introduced in every version, sometimes with service packs.

• Is this a feature preventing you from using code that won't work against the group as a whole, saving you from accidentally assuming that all instances returned data?
• Or, is this a pain that causes you to need version-specific registered server groups to get at data from some instances?

There is no right answer to this question, only the opportunity to be aware of this behavior.

There is an old MSConnect item from 2007 for a similar issue that was marked as "won't fix" in 2011, so it is not a bug. I am not sure if there is any beneficial intent from MS to prevent you from getting any data when one instance in a group has a syntax error.

Imagine if, within a large list of production SQL Servers, a handful are of a prior SQL version where some utility scripts won't work.

Should I then create another registered server group for instances that can run sys.dm_os_volume_stats, or a group for instances that support the new columns added to sys.dm_exec_query_stats? No correct answer here either, in the absence of an option to allow for partial resultsets to be returned from multiserver queries, you will need to consider what suits your needs best.

Here's the steps to reproduce for a simple scenario:

1. Add a SQL 2008 instance and a SQL 2008 R2 instance to a new Registered Servers group.
2. Create a new multiserver query.
3. Attempt to query a DMF or DMV that was introduced in SQL 2008 R2, such as sys.dm_os_volume_stats. (Sample script below.)
4. The message table includes one error and one rows returned message, like below. No Results tab is returned, so the rows are not visible even though the query worked successfully on the SQL 2008 R2 instance.

sqldemo1\sql2008(domain\user): Msg 208, Level 16, State 1, Line 2Invalid object name 'sys.dm_os_volume_stats'.sqldemo1\sql2008r2(domain\user): (2 row(s) affected)

Sample script:

select distinct
vs.volume_Mount_point,
file_system_type,
   drive_size_GB = convert(decimal(19,2), vs.total_bytes/1024./1024./1024. ) ,
   drive_free_space_GB = convert(decimal(19,2), vs.available_bytes/1024./1024./1024. ),
   drive_percent_free = CONVERT(DECIMAL(9,2), vs.available_bytes * 100.0 / vs.total_bytes)
FROM
   sys.master_files AS f
CROSS APPLY
   sys.dm_os_volume_stats(f.database_id, f.file_id) vs

I have confirmed this behavior in Microsoft SQL Server Management Studio 12.0.2000.8, 11.0.3128.0 and 10.50.4000.0.

SQLSat Birmingham: SQL Server Admin Best Practices with DMVs

Thanks for attending my session today SQL Server Admin Best Practices with DMVs at 8AM!

Here's the link to a .zip file to download the slide deck and all sample files: http://1drv.ms/VH3mda

SQLSat Birmingham: SQL Server Permissions and Security Principals

Thanks for attending my session today, SQL Server Permissions and Security Principals at 9:15AM!

Here's the link to a .zip file to download the slide deck and all sample files: http://1drv.ms/VH3lpx

SQL Saturday Birmingham 2014

I'm looking forward to geauxing with a handful of Sparkhound colleagues and friends to speak at SQL Saturday Birmingham on August 23 at Samford University.

Fun trivia fact: This will be the first time I'll have spoken at a SQL Saturday east of Baton Rouge since SQL Saturday Pensacola stopped happening annually after 2012, but, the second time I'll have spoken this year in the state of Alabama.

I'll be presenting the first two sessions of the DBA track at 8AM and 9:15AM on the topics of SQL Admin Best Practices with DMV's and then SQL Server Permissions and Security Principals.

My colleague Cody Gros will be speaking on PowerShell Fundamentals for SharePoint 2010/2013 at 10:30AM in the PowerShell room, then we'll get together for the Sparkhound lunch session at 11:45AM, also in the PowerShell room. My wife will then be giving two presentations after lunch in the Prof Dev \ Misc Room as well, so I'll be there for the duration!

I'll see you there! Don't forget to print your SpeedPASS doc!

Here's the links to downloads for my two presentations:

SQLSat Birmingham: SQL Server Permissions and Security Principals

SQLSat Birmingham: SQL Server Admin Best Practices with DMVs

SQLSaturday Baton Rouge Business Intelligence and Recap - 2 of 2

(This is the second part of a two-part post on a SQLSaturday Baton Rouge 2014 recap.)

Out of a total of 614 attendees in house on August 2, 264 responded to our post-event cross-platform flurry of online survey invitations to surveymonkey. After reviewing this valuable feedback, it is definitely something we should have also been doing all along. We got some great feedback and testimonials, like these actual responses:

• It was great networking, learning, fun and there were light-sabers.
• Some very good talks with great content, large community of very smart, talented developers, great networking, and awesome SWAG/goodies
• Great networking opportunity, good way to get to know the community. Some talks were really excellent presentations on state of the art database techniques.
• Lots of companies with great networking opportunities; lots of free training and free stuff. High quality and free is very unique.
• It was a fun way to network and learn about how everyone is using technology that we can bring back to our own organizations. It is a chance to learn something new, and meet new people.
• My first sql saturday and certainly not my last. Had a very nice time.

We also got some great info on stuff that we, our speakers and our sponsors could do better in 2015. You better believe we are listening to those and will follow up on it. I highly recommend to SQLSaturday organizers to followup your event with a survey like this.

As for what sessions people want to see next year? More demand for content at both sides of the spectrum, from expert level to beginner, and lots of requests for new technology topics, summarized by these actual responses:

• I would like to see more "deep dive" presentations. Those submitted were good, but would like to see some that go more in-depth. Some did, but several were very high level. It's good to have variety, but I feel there could have been more "deep dive" sessions.
• More advanced areas in each track 
• more intermediate or advanced BI/BA

• Cover beginning concepts to persons new to SQL
• Basic SQL Server Indexing, Generally more SQL Server basics
• Even the "beginner" level seminars were way above the heads of people like me who are truly beginners and who don't yet have real world experience with SQL. A few sessions truly targeting newcomers to the field would help.

• Anything new and upcoming to keep me up to date with technology.
• SQL 2014 specific stuff
• Machine Learning
• Underwater basketweaving

We asked attendees about their attendance during the day:

I was there all day!	75.10% 196
I missed some of the afternoon sessions.	19.54% 51
I missed some of the morning sessions.	5.36% 14
Total	261

We asked attendees how we could best reach them to tell them about SQLSaturday Baton Rouge 2015:

Answer Choices–	Responses–
Email from SQLSaturday.com (back in March)	34.10% 89
Social Media - (Facebook, G+, LinkedIn, Twitter, FriendFace)	18.77% 49
A colleague at work	11.88% 31
User Group meeting	8.81% 23
Email from the User Group or tech community	8.81% 23
Friend	6.13% 16
Events Calendar from media outlet (newspaper, TV, radio)	4.21% 11
My manager	3.83% 10
Other (please specify)	3.45% 9
Someone I manage	0.00% 0
Total	261

We asked attendees who their favorite booth was (by any measure they liked), and envoc's shag-carpeted, comfy-couch swag pad was the winner:

We asked who was the favorite speaker at the event, and the world-travelling Kevin Boles and his two auditorium-sized SQL Dev sessions carried the day. However, a whopping 37 different speakers received at least two votes their "favorite speaker", so great job to the 13 track, 5 session schedule lineup of speakers this year!

	Percent	Count
Kevin Boles	8.2%	19
Raghu Dodda	5.6%	13
Jennifer McCown	5.6%	13
Brian Rigsby	5.2%	12
Sean McCown	4.7%	11
George Mauer	4.3%	10
Cherie Sheriff	4.3%	10
Carlos Bossy	4.3%	10
Thomas Allsup	3.9%	9
Mike F. Robbins	3.4%	8

We asked about our jambalaya lunch (from Pot & Paddle Jambalaya Kitchen):

Answer Choices–	Responses–
Lunch was great!	63.01% 155
Lunch was just OK.	23.17% 57
I chose not to eat the free lunch.	10.98% 27
I missed lunch.	2.85% 7
Total	246

We got lots of good feedback about improving next year and praise for the this year. Too much to summarize here, other than that rest assured - we hear you!

Finally, on the suggestion of my wife with a business degree, we asked the classic "Would you recommend SQL Saturday" question. 

'Nuff said.

SQLSaturday Baton Rouge Business Intelligence and Recap - 1 of 2

(This is the first part of a two-part post on a SQLSaturday Baton Rouge 2014 recap.)

Been quiet on this blog the past few weeks as the crush of one of the world's largest SQLSaturday events arrives.

SQLSaturday #324 Baton Rouge this past Saturday was a big success for us and made me very proud of my alma mater (which made attendance mandatory for the entire Masters of Science in Analytics program), my city (except for its traffic), our 2014 sponsors who filled the atrium with booths, and the crew of the Baton Rouge SQL Saturday planning committee, who worked to clean up two full-size trailer loads of garbage. A big public and personal thanks to all of the volunteers and planning committee members who helped this year!

We had 614 verified attendees in 2014. This is a great accomplishment for Baton Rouge and a 9.4% increase from last year's actual attendance. Here's our photo album of the event.

Here's the final numbers on some of the business intelligence we worked up using registration data from the last five years of Baton Rouge SQL Saturday events, which includes all but our first year of the event in Baton Rouge in 2009.

Note in the first graph below how we registered 274 attendees in the final workweek of the event, that's 31% of our total registrants and 45% of our actual attendance. Look for the plateaus in the lines - those are weekends.

Building on my feelings from last year at this time, I believe that doubling the number of registered attendees one month away from the event is a decent indicator of actual attendance. In this case, roughly 300 registered on July 2 turned into 614 folks through the doors.

Registered volunteers this year gratefully rose to meet the occasion. We had new volunteers, older and young, who we'd never met before, hauling garbage out the back door well after the raffle ended. I couldn't thank enough and still feel indebted grateful from my tired feet for helping move and clean.

Curious when your folks register? Tuesday is the most popular day for folks to register but Monday at 9AM and Friday at 9AM at the hottest hours of the week. This data has been compiled over 5 years of registration data. Note the impressive spike at 11AM on Saturday morning - prime lawn-cutting time turned into productive stall tactics?

Twitter is staggeringly popular among the SQL Saturday speakers - many of which were pulling their hair out as I10E crawled to a halt on Friday afternoon before the much-anticipated banquet at Baton Rouge's best churrascaria. This was the first year that more than a quarter of our attendees registered with their Twitter accounts, and judging by activity on #SQLSatBR it was a popular event.

This isn't a big surprise - Louisianans overwhelmingly make up the attendees of Baton Rouge SQL Saturday...

 ... but this part I am proud of. As we continue to grow the conference to its (theoretical?) limit, the percentage of locals in our natural gas-boom-bound city is rising, this year overtaking the number of folks from outside the city. That's word of mouth marketing for you.

Finally, and this may be related to some of the anonymous feedback we received in our post-event SurveyMonkey, we saw vegetarians make a resurgence in attendance after slipping in each of the past three years. It looked like they were headed for extinction until a big bounceback in 2014. Perhaps this should have prepared us for an anonymous comment excoriating us for the lack of cruelty-free vegetarian lunch alternatives.

Thanks to everyone who was a part of SQL Saturday Baton Rouge! There is a post-mortem meeting this week for the 2014 event and to begin prep for the 2015 event, if you'd like to get involved, please email sqlpassbr at gmail com.

(This is the first part of a two-part post on a SQLSaturday Baton Rouge 2014 recap... continue to part two)