Why I Love #SQLSaturday

PASS SQLSaturdays are free 1-day training events for SQL Server professionals that focus on local speakers, providing a variety of high-quality technical sessions, and making it all happen through the efforts of volunteers. Whether you're attending a SQLSaturday or thinking about hosting your own, we think you'll find it's a great way to spend a Saturday – or any day. - sqlsaturday.com

Sounds simple, doesn't it?  Here are the top three reasons I *LOVE* SQLSaturdays:

The opportunity to learn - the quality and quantity of free training available at a SQLSaturday never ceases to amaze me.  Most SQLSaturdays have anywhere from three to six tracks, resulting in 15-30 sessions on everything from database administration to business intelligence to professional development and everything in between.  Sessions range from 100-level introductory up through 400-level expert.

 

If you want to pay $100-$150, most SQLSaturdays offer full-day "pre-conference" sessions on the Friday before the event (and sometimes even the Thursday before as well). While it isn't free, there aren't very many places to get a full day of high end training for a hundred bucks!

Another aspect of this is *who* provides the training.  I regularly see sessions from Microsoft Certified Masters (MCM's) and Most Valuable Professionals (MVP's) as well as amazing content from first-timers dipping their toes in the pond.  At a recent SQL Saturday (in Boston) I saw an MCM present a great talk on working with virtualized SQL, one MVP speak on working with your infrastructure team, and another MVP talk about the Query Store, an upcoming feature from the unreleased next version of SQL Server.  Having said that, one of the best SQLSaturday sessions I have ever seen came a couple years ago from a first-time speaker excitedly presenting a new way she had devised to analyze trace data. 

All of these speakers share their expertise without pay or reward (other than usually a nice dinner the night before).

The opportunity to share - another fun part of SQLSaturday for me is being one of those speakers sharing what I know for free.  I have written before about the benefits of blogging and speaking, and they are many.  The biggest benefit to me personally (not counting the joy of helping others) is how creating a presentation helps force me to learn a new topic or a better way to do something I already do.

The presentation I used to give all the time was about doing health checks using Glenn Berry's DMV scripts, Ola Hallengren's maintenance solution, and how to make them work together to check client servers.  The presentation I frequently give now is about Extended Events (XEvents) - I told myself about a year ago I needed to learn more about XEvents and Powershell, and I knew (for me) creating presentations would help.  I submitted an Intro to Extended Events 100-level session to a couple of SQLSaturdays, and when it was chosen I was suddenly very motivated to learn more about the topic to produce the content!

The first presentation - the health check talk - highlights an aspect of how I work with SQL Server, and it is shared by many others.  The are lots of free tools and shared knowledge out there about SQL Server, and you don't need to recreate the wheel nine times out of ten - a little Google-Fu or #sqlhelp will usually give you an answer or at least give you raw material you can mold into an answer.  Just because you are working with someone else's raw material does not mean you can't write or speak about the situation - just make 100% sure you give credit (and notation) whenever it is due.  If you read my blog or see me speak with any regularity you will see that a lot if what I write about is *how* I use someone else's scripts, whether modified or "straight from the box," as opposed to creating completely new scripts, but I also reference those authors' blog posts, forum answers, and Twitter feeds.

You don't have to create a completely new way to do something for it to be useful to share!

The opportunity to network - I list this third, but it can often be the most important. While it can be very useful to interact with the #sqlfamily online, there is no substitute for being able to sit down across the table from an expert on a topic you need help with and getting hundreds of dollars worth of free consulting while forming friendships that continue on after the event ends. There is nothing like the #sqlfamily, and it is fun to watch people from other areas as their eyes open wide. I have seen an Oracle DBA visit a SQLSaturday and watched their jaw hit the floor when they heard a speaker sharing their expert knowledge for free; I have had a manager ask me "How did you get the answer so fast?" and reply "I got on Twitter and asked the person who wrote the feature and he told me"; I have watched people who have never met in person raise thousands of dollars for charitable causes suggested by #sqlfamily members.

Another way to get involved and network is to volunteer at a SQLSaturday. I mentioned the speakers are volunteers, but so ate all of the others involved - coordinators, room monitors, check-in staff, and the rest.

This networking is invaluable to your career in other ways - two of my last three jobs came from a member of the #sqlfamily notifying me directly of an opening or making a key introduction to help me start the process.

SQLSaturdays are awesome!!!

Pulling Security Info

A frequent request I receive is to pull a list of logins/users with certain accesses, role memberships, etc.

I had a query to use xp_logininfo to pull group membership chains - that is, DOMAIN\Andy has access, but not directly - DOMAIN\Andy has access because he is a member of DOMAIN\DBAGroup.  The query is this:

/*
Domain Login Group Security Info
*/ 

DECLARE @name sysname 

CREATE TABLE ##logininfo
(
[account name] sysname,
[type] nvarchar(50),
[privilege] nvarchar(50),
[mapped login name] sysname,
[permission path] sysname
) 

DECLARE namecursor cursor fast_forward
for
select name from master.sys.server_principals
where type='G' and name not like 'NT SERVICE%' 

open  namecursor
fetch next from namecursor into @name 

WHILE @@fetch_status=0
BEGIN
insert into ##logininfo EXEC ('xp_logininfo '''+ @name+''',''members''')
fetch next from namecursor into @name
END 

CLOSE namecursor
DEALLOCATE namecursor 

select @@SERVERNAME as InstanceName, *
from ##logininfo
/*
where [mapped login name] like'%agalb%'
*/ 

DROP TABLE ##logininfo

I needed the other half (or two-thirds) - I needed the ability to pull server and database role memberships.  Rather than script something from scratch I went looking and sure enough found the raw material for what I wanted on SQLServerCentral in the forums in the post "Query to get the lisst of logins having sysadmin and serveradmin."

The query is pulled from Jagan Kondapalli's (@JVKondapalli) reply to the original poster's question.  I modified it in a couple places and am posting my modification here:

IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
    DROP TABLE ##Users
IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##LOGINS' AND type in (N'U'))
    DROP TABLE ##LOGINS
GO
USE tempdb
GO
/*CREATE TABLE ##LOGINS
(
[Login Name] varchar(50),
[Default Database] varchar(60),
[Login Type] varchar(40),
[AD Login Type] varchar(40),
[sysadmin] char(5),
[securityadmin] char(5),
[serveradmin] char(5),
[setupadmin] char(5),
[processadmin] char(5),
[diskadmin] char(5),
[dbcreator] char(5),
[bulkadmin] char(5)
)*/
CREATE TABLE ##Users
(
    [Database] VARCHAR(64),
    [Database User ID] VARCHAR(64),
    [Server Login] VARCHAR(64),
    [Database Role] VARCHAR(64)
)
use master
go
SELECT  sid,
        loginname AS [Login Name],
        dbname AS [Default Database],
        CASE isntname
            WHEN 1 THEN 'AD Login'
            ELSE 'SQL Login'
        END AS [Login Type],
        CASE
            WHEN isntgroup = 1 THEN 'AD Group'
            WHEN isntuser = 1 THEN 'AD User'
            ELSE ''
        END AS [AD Login Type],
        CASE sysadmin
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [sysadmin],
        CASE [securityadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [securityadmin],
        CASE [serveradmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [serveradmin],
        CASE [setupadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [setupadmin],
        CASE [processadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [processadmin],
        CASE [diskadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [diskadmin],
        CASE [dbcreator]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [dbcreator],
        CASE [bulkadmin]
            WHEN 1 THEN 'Yes'
            ELSE 'No'
        END AS [bulkadmin]
INTO ##LOGINS
FROM dbo.syslogins /*IN ORDER TO GET THE ACCESS INFORMATION A LOGIN ADD THE LOGIN NAME TO THE WHERE CLAUSE BELOW*/
--WHERE [LOGINNAME] = 'PUNCH IN THE LOGIN NAME HERE'
SELECT @@SERVERNAME as InstanceName, [Login Name],         [Default Database],  
        [Login Type],         [AD Login Type],         [sysadmin],         [securityadmin],         [serveradmin],         [setupadmin],         [processadmin],         [diskadmin],         [dbcreator],         [bulkadmin]
FROM tempdb..##LOGINS
--where [mapped login name] like'%agalb%'
ORDER BY [Login Type], [AD Login Type], [Login Name]
--
USE master
GO
DECLARE @DBName             VARCHAR(60)
DECLARE @SQLCmd             VARCHAR(1024)
Declare @DBID varchar(3)
set @DBID = (select MAX(database_id) from sys.databases)
--print @DBID
WHILE @DBID != 0
BEGIN
set @DBName = (select DB_NAME (''+@DBID+''))
SELECT @SQLCmd = 'INSERT ##Users ' +
        '  SELECT ''' + @DBName + ''' AS [Database],' +
        '       su.[name] AS [Database User ID], ' +
        '       COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
        '       COALESCE (sug.name, ''Public'') AS [Database Role] ' +
        '    FROM [' + @DBName + '].[dbo].[sysusers] su' +
        '        LEFT OUTER JOIN ##LOGINS u' +
        '            ON su.sid = u.sid' +
        '        LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
        '                             INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug  ' +
        '                                 ON sm.groupuid = sug.uid)' +
        '            ON su.uid = sm.memberuid ' +
        '    WHERE su.hasdbaccess = 1' +
        '      AND su.[name] != ''dbo'' '
     IF DATABASEPROPERTYEX(@DBName, 'Status')='ONLINE'
EXEC (@SQLCmd)
     print @DBID
     set @DBID = @DBID - 1
END
SELECT @@SERVERNAME as InstanceName,*
FROM ##Users
/*IN ORDER TO GET THE ACCESS INFORMATION A USER ADD THE USER TO THE WEHRE CLUASE BELOW*/
--WHERE [Database User ID] = 'PUNCH IN THE USER HERE'
/*IN ORDER TO GET THE ACCESS INFORMATION OF ALL USERS TO A PARTICULAR DATABASE, ADD THE DATABASE NAME TO THE WHERE CLUASE BELOW*/
--WHERE [DATABASE] = 'PUNCH IN YOUR DATABASE NAME HERE'
--where [server login] like '%AGALBRAI%'
ORDER BY [Database], [Database User ID]
   
IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##LOGINS' AND type in (N'U'))
    DROP TABLE ##LOGINS
GO
IF  EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
    DROP TABLE ##Users

Again - this is *not* my underlying query but it is exactly what I needed to pull role memberships.  I usually pull this info and dump it into Excel for client consumption.

I could not find a blog or other presence for Jagan besides his SQLServerCentral profile and the inactive Twitter account mentioned above, but thanks!

--

Hope this helps!

Update Your Scripts!

I had an incident today where a colleague was running a Wait Stats Report and the output looked like this:

As soon as he showed me the report I knew he had an outdated script.

Each new version of SQL Server introduces new wait types, often (but not always) related to new features.  For example, waits related to AlwaysOn (the feature formerly known as HADRON) have a prefix of HADR.

The wait at the top of his list, HADR_FILESTREAM_IOMGR_IOCOMPLETION  means "The FILESTREAM AlwaysOn I/O manager is waiting for I/O completion" - the catch is the AlwaysOn I/O manager is almost always waiting for I/O completion as data flows back and forth - this isn't indicative of any issue, so it can be excluded.

Paul Randal (@PaulRandal/blog) keeps a list of the "excludable" wait types in a blog post he maintains related to wait stats.  The post includes a version of the Wait Stats script from Glenn Berry's (@GlennAlanBerry/blog) DMV scripts, and that Wait Stats script has a large list of exclusions:

WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
        N'CHKPT',                           N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                        N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')

If you refer back to my colleague's original screen shot, you'll see that the top ten items on his list - everything above "OLEDB" - is on the excludable list!

--

The broader lesson here is to make sure you update your script libraries regularly - even if a script still runs and provides output (that is, you think it "works") it doesn't mean you are receiving valid data.

Although this example is about wait stats and wait types, it is applicable to a wide array of configurations and settings.  Changes like this are often version-related, but even within a version it can be decided that a particular wait type/trace flag/sp_configure setting/etc. is no longer important and can be ignored - or even worse, that some item is now important but wasn't included in your original scripts!

A little regular maintenance and ongoing research will help your toolbox stay clean and organized.

http://s421.photobucket.com/user/48548/media/tools/IMG_3765.jpg.html

Hope this helps!

Another Source of Knowledge

It started with a tweet from one the many members of the #sqlfamily who always has something meaningful to say, Jes Borland (blog/@grrl_geek):

The referenced link is http://www.ted.com/talks/celeste_headlee_10_ways_to_have_a_better_conversation.

I followed Jes's advice, paused the other items I was working on, and watched the talk.  It was a nice tight eleven minutes on how to be present in the moment, paying attention to those around you and interacting with them in a more meaningful way.

I was not formerly familiar with the speaker Celeste Headlee (blog/@CelesteHeadlee), a public radio talk host, but after listening to her talk I could definitely listen to her again - she spoke very clearly and made her points without much fluff.

I'm sure some people will tune out right there, thinking they would never agree with a public radio host or her agenda - my take has always been that it is worth giving any speaker at least two chances.  If I have listened to you speak/present/etc. twice (anyone can have a bad day) and both times you have failed to hold my interest (on topics in which I am interested) I will discount then you as a speaker - at least as a speaker I would be interested in listening to.

--

While I enjoyed this particular talk, it reminded me of something I used to really enjoy doing and have all but stopped over the last couple years.

I tripped over TED talks online five or six years ago, back when I still worked in an on-site office.  I don't remember how - probably from Twitter or Facebook - but I discovered this wide array of free recordings that I could listen to on my breaks or when I had a free few minutes.  As a full-time work-from-home these last few years I have not handled my workday in the same way (I eat lunch with my family now for example) and have not watched talks in the same way I used to.

TED is a nonprofit organization that puts on a series of conferences on Technology, Entertainment, and Design.  The talks given at the conferences are by practice 18 minutes or less (although some do run longer), with many talks being around ten minutes long.

The conferences themselves aren't cheap (and as such I have never been in person) - TED2017 in Vancouver next year has a registration cost of US$8500 - but the real treasure trove are the talks themselves, posted online for free on TED's website (and often on YouTube) after a reasonable time has passed.

TED talks are given by an amazing array of speakers from all different backgrounds, from industry leaders like Bill Gates and Richard Branson to intellectuals like Sir Ken Robinson and Dan Gilbert to entertainers like Apollo Robbins and Ze Frank.

You can search the talks by topic or speaker or length, or you can watch one of the curated playlists of talks to see talks related to one another on a given subject.

A sidebar to the TED conferences and talks is TEDx, TEDx is an offshoot of the parent conference where local staff organize an event that is a combination of TED-style talks from local/regional speakers and airings of national TED talks.  The best comparison I can make for SQL Server is to think of TEDx as TED's SQL Saturday - not entirely accurate but close.  TEDx talks are also available for viewing for free at ted.com.

Another benefit of a resource like this is to a speaker (or a potential speaker, which is about everyone).  You can learn a lot about speaking to an audience from simply watching other people present, and these talks are great examples.

--

At the end of the day, you probably won't learn anything directly about SQL Server from seeking out things like TED talks, but you will learn a lot about the world around you, and remember Knowledge is Power!

http://cdn.thedailybeast.com/content/dailybeast/articles/2014/09/06/schoolhouse-rock-a-trojan-horse-of-knowledge-and-power/jcr:content/body/inlineimage.img.800.jpg/47000010.cached.jpg

(Whoa...flashback...)

Hope this helps!