Final Recap – 2009 Goals

Well, I set 7 professional goals for 2009 and I did okay in meeting them, but I was not successful in all and, really, that is only because I did not put in the time to get them all done.  So let’s go through them and see how I did:

1. Learn SQL Server 2008, particularly policy-based management, resource governor, and service broker.
   • Well, I did learn some SQL Server 2008.  I run it on my laptop and have spent some time with it.
   • Policy-based Management – I have played with it, read about it, and attended a couple of presentations about it.  I’d call this one attained
   • Resource Governor – I did a little reading on it, but I did not study beyond that.  I have not played with it all.  I’d call this one missed.
   • Service Broker – I really need to learn this one, but I have not.  This a fail.
2. Write 1 article per quarter for SQLServerCentral
   Man, I started out great with this one, but fell off in the second half of the year.  I still have a series on Profiler to complete, but I got stuck on a problem with Replaying a trace so I flamed out.  I had 2 in the first half and none in the second half.  This is a major fail and I have no excuse except lack of motivation.
3. Record and submit 1 video per quarter for JumpstartTV (now SQLShare).
   I did one video in January and one in December (still to be approved at SQLShare).  So again, another fail because of lack of effort and planning.
4. One blog post per week
   I met this one by average as there were weeks I did not have a post, but more weeks when I had multiple posts.  With this post I’ll have 113 posts in 2009 which is a little more than 2 per week.  I would say a large majority were technical/professional posts with a couple personal and some announcement type posts.  This one is ACHIEVED!
5. Become more involved in leadership in my local user group (OPASS).
   • I am officially co-president and have been scheduling speakers and will be starting to work on sponsors in the coming weeks.  Definitely achieved!
     • Speak at 2 community events (user groups, SQLSaturday’s, etc…)
       I spoke at the Pensacola and Orlando SQLSaturday’s and the Tampa and Space Coast User Groups so this is definitely achieved.
     • Attend the PASS Summit
       I made it!  It was FANTASTIC!  I met at least 50 people, learned a ton, and made great contacts.  I can’t wait to go back in 2010.  Don’t forget it is only $995 until January 15th.  Believe me, it is worth it and I don’t plan on missing any in the future.

Overall a pretty good year as I was week in my contributions to SQLServerCentral and SQLShare, but I learned a lot about SQL Server 2008 even though I didn’t meet my specific goals, and I exceeded goals 4-6, and attained goal 7.

Even though I did not meet all my goals 2009 was a great year for my professional development.  I learned a lot, not just technically, but how to be a professional and how to contribute to the community, so I’ll call 2009 a success!

Presenting a Live Meeting vs. a Real Meeting

I recently had the opportunity to present,  Nuggets Found by Mining the Default Trace, for SQLLunch.  This was my first time presenting via Live Meeting (or any on-line meeting functionality for that matter) and it was quite different.  I have presented technical contant at several user groups and SQLSaturday’s, and have spoken at many churches, but all of those times have been in the front of a roomful of people.  Presenting on-line is definitely different than presenting in-person.  Here are my first impressions:

Pacing

When presenting in-person you can tell by the attendees expressions and body language if you are going at the right pace, but on-line you don’t get that immediate feedback.

Personality

I’m not sure that your personality as a presenter comes across on-line as well as in-person.  Again, the lack of feedback from visual cues makes it hard to tell if your style and personality is coming through or if it is appropriate based on the audience.

Interaction

I thrive on interaction with the attendees and there is very limited opportunity for this on-line, at least with my limited experience.  I like to take questions throughout my presentations and tell some jokes and I found this more difficult to do in a Live Meeting

My Conclusion

I think Live Meeting and similar technologies are great even though I prefer to present in-person. I do enjoy being able to hear some great speakers through Live Meeting. 

Where I Think Live Meeting Fits Best

Remote locations to have some top speakers present.  Similar to where I use to live in northern New Hampshire.  Let’s just say that being 2+ hours from the nearest airport and 3+ hours from the nearest big city (Boston or Montreal) isn’t conducive to bringing in speakers from away, but Live Meeting would allow a group to have some top speakers.

For organizations like PASS to give a taste of conferences like the Summit through events like the 24 Hours of Pass.

Going Forward

As Co-President of OPASS, we are going to be having our first meeting with a Live Meeting presention at our January meeting with Andy Leonard (@AndyLeonard) presenting.  My co-President, Andy Warren (@sqlandy), is a big thinker and wants to try to send 2-way video so that the presenter can see the group and, if possible, take questions during the presentation.  We will be doing some testing before to see how and if we can make it work, and then try to make it work well.   

OT: What Christmas Means to Me

Merry Christmas to everyone.  As many of you know, at least I hope you do, I am a born-again Christian and try to live my life according to the Bible, so I’d like to share what Christmas means to me at this time.

There are many scripture passages that are regularly used in Christmas cards, sermons, blogs, and letters, but I’d the one I’ll share is a little different:

   
¹² Therefore, just as through one man sin entered the world, and death through sin, and thus death spread to all men, because all sinned— ¹³ (For until the law sin was in the world, but sin is not imputed when there is no law. ¹⁴ Nevertheless death reigned from Adam to Moses, even over those who had not sinned according to the likeness of the transgression of Adam, who is a type of Him who was to come. ¹⁵ But the free gift is not like the offense. For if by the one man’s offense many died, much more the grace of God and the gift by the grace of the one Man, Jesus Christ, abounded to many. ¹⁶ And the gift is not like that which came through the one who sinned. For the judgment which came from one offense resulted in condemnation, but the free gift which came from many offenses resulted in justification. ¹⁷ For if by the one man’s offense death reigned through the one, much more those who receive abundance of grace and of the gift of righteousness will reign in life through the One, Jesus Christ.)
¹⁸ Therefore, as through one man’s offense judgment came to all men, resulting in condemnation, even so through one Man’s righteous act the free gift came to all men, resulting in justification of life. ¹⁹ For as by one man’s disobedience many were made sinners, so also by one Man’s obedience many will be made righteous.
²⁰ Moreover the law entered that the offense might abound. But where sin abounded, grace abounded much more, ²¹ so that as sin reigned in death, even so grace might reign through righteousness to eternal life through Jesus Christ our Lord. (Romans 5:12-21, New King James Version)

There’s a lot in that passage, but the key idea is that, because Adam sinned ALL people are born sinners (vv. 12-15, 18-19), but because Jesus Christ was fully obedient (vv.18-19), God through His grace offers justification (Ephesians 2:8-9) to us.

The giving and receiving of gifts at Christmas is fun, and, believe it or not, I prefer the giving over receiving, but the birth, life, death, and resurrection of Christ and the free gift offered by God because of that is the greatest gift that can be given.  I’m glad that I accepted and opened that gift and I hope you have as well.

Speaking at SQLSaturday #32 – Tampa

I just found out that I’ve been accepted to speak at SQLSaturday #32 – Tampa on January 23, 2010.  My session will be:

Why Should I Use Stored Procedures?

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.

I hope this will turn out to be a great, open discussion where there are different viewpoints presented and honest evaluation of the best way to access data in SQL Server.

Why Attend?

If you’ve never been to a SQLSaturday, I highly recommend that you check out the schedule as there are several events upcoming all around the US.  Where else can you find a full day of free training from some of the best minds in the SQL Server community.  There are usually several SQL Server MVP’s and authors presenting in addition to the best local SQL Server minds.  The time spent in conversation with your peers is well-worth a Saturday. 

Remember, YOU are responsible for your professional development.

SQLLunch Resources – Mining Nuggets from the Default Trace

I did my first SQLLunch today and it was fun.  I don’t know how many attended, my last look was 12, so not a huge crowd, but that’s okay.  It is definitely different presenting online versus in-person and there will be a blog post soon about how I found it different and where I struggled, but I digress.  I promised that I put the scripts up on my blog so here they are on SkyDrive since I can’t post them here:

• Slide Deck
• T-SQL + PowerShell
• SSIS

Enjoy!  Oh, and as always, don’t trust any code you download from the internet, even mine always test it then improve it and then share it.

Have Lunch with Me

I know you all are clamoring for the opportunity to have lunch with me so I agreed to do a SQLLunch on Monday, December 21.  Doing it this way will allow more people to enjoy lunch with me.

I’ll be speaking on Nuggets Found by Mining the Default Trace.  Lunch starts at 11:30 am CST and will last for about 30 minutes with some time for questions.

Where’d the Data Go?

The Reported Problem

A few weeks ago I was contacted by a former employer because an SSRS report I had written several years ago was no longer returning the data that was expected.  The report returned Manufacturing Instructions that were attached to an Order or Order Item and was used to ensure that everything was done as requested by the customer.  There are 2 groups on the report:

1. Runs – a Run consists of orders that contain the same product.
2. Orders

The detail section consists of the specific Manufacturing Instructions for each order in the run.  Well, the report was showing the Runs and the Orders but no Instructions.  Obviously this is bad since the whole point of the report is to show the instructions.

Troubleshooting the Problem

I vpn’ed into the network and opened up the report to find the SQL that was generating the report.  I knew I had authored the report, but could not remember what the data source was.  There were 2 possible data sources:  SQL Server or DB2 on the iSeries.  I was hoping it would be SQL Server.  It wasn’t.  I had a query similar to this (underlining added) in the report that was connecting to DB2 using the IBM DA400 OLE DB Driver:

SELECT
    S.run_no,
    O.order_no,
    OI.instruction AS order_instruction,
    S.start_date,
    S.end_date
FROM
    @orders O JOIN 
    @order_instructions OI
        ON O.order_no = OI.order_no JOIN
    @schedule S
        ON O.order_no = S.order_no
WHERE
    (S.start_date >= '12/17/2009' AND
     S.end_date < '12/21/2009') OR
    (S.start_date < '12/21/2009' AND
     S.end_date > '12/14/2009')
UNION ALL
SELECT
    S.run_no,
    O.order_no,
    OII.instruction AS order_item_instruction,
    S.start_date,
    S.end_date
FROM
    @orders O JOIN 
    @order_items OI
        ON O.order_no = OI.order_no JOIN 
    @order_item_instructions OII
        ON OI.order_item_no = OII.order_item_no JOIN
    @schedule S
        ON O.order_no = S.order_no
WHERE
    (S.start_date >= '12/17/2009' AND
     S.end_date < '12/21/2009') OR
    (S.start_date < '12/21/2009' AND
     S.end_date > '12/14/2009')
ORDER BY
    run_no,
    O.order_no 

I ran the query in the dataset designer in Visual Studio 2003 (yup, SSRS 2000) and got data back including instructions.  I couldn’t figure out why I wasn’t seeing instructions on the report, especially since this report had been running without problem.  Then I ran the query in a custom query tool and that’s when I noticed the instruction column was not being returned as instruction but as column 00003!  What! I don’t have that anywhere?  Well, look at the underlined text in query, notice how I aliased the instruction columns with different aliases.  Turns out THIS was the problem.  Apparently when an update (ptf) was applied to the iSeries the behavior of column aliases in a UNION changed.  If you run a query like that in SQL Server the ALIAS used in the FIRST query is returned as the column name.  This was also how it USED to work in DB2 on the iSeries, but apparently that changed and instead of getting EITHER of the ALIASES it returns the column number, in the case you’d get 00003 for the ALIASED column.

The Solution

Well, simple solution, I removed the ALIAS in the second part of the UNION query and, VIOLA!, the report now worked again.

Why did I have the separate ALIASes in the first place?  I think it was because when I was testing the queries for correct data I wanted to know which instructions were at the order level and which were at the order item level and I ran the queries separately.  Then when I was convinced the data was correct I UNION’ed the queries and stuck them in the report without removing the ALIASes. 

So in reality the query was returning the correct data, but the SSRS report was looking for a column named instructions and it didn’t exist, so it didn’t show it!

Next time, I’ll know better!

What is Your Biggest Weakness?

I was tagged by Ken Simmons (@kensimmons) in the blog chain started by David Stein (@made2mentor).  Here’s Ken’s post and the original.  There are several other posts including ones by Brent Ozar (@BrentO) and Thomas LaRock (@SQLRockstar).  So I guess I’ll add mine.

This is a very hard post for me to write for two reasons:

1. Who likes to publicize where they struggle?  I know I prefer to talk about successes and strengths.
2. I tend to be very critical of myself (and others many times), See Joe Healy’s comments on my blog post about areas where SQLSaturday #21 – Orlando could have been better, and others usually have higher regard for my knowledge/abilities than I do, so it’s hard for me to pick one weakness as my biggest.

Maybe procrastination is it, as I know you are all saying, “Get to the point, what’s your biggest weakness?”.  Okay, okay, I’ll pick one.

I’d say my biggest weakness is lack of confidence.  I know I may not come across that way, but while I may know something intellectually I hesitate to implement for fear I may be wrong.  In some ways this is a good thing because it makes me plan, learn, and test more, but it can be a bad thing because it can make me get stuck in one place when I should be moving to another.  The funny part of it is, each time I finally move or when I do the thing that I’m afraid of, none of the things I worried about happened.  For example, whenever I speak, whether on technical topic or in elsewhere (I speak in church regularly), I’m EXTREMELY nervous and think “What if someone in the crowd knows more than me and shows where I’m wrong?” or “What if the projector or demos don’t work?”, but once the talk starts, I’m fine.  I’ve spoken probably hundreds of times, and I still fight the fear.  I do the same thing when I learn something new for SQL Server or when it’s time to apply a Service Pack or upgrade, “What if it doesn’t work?”.  Well, experience tells me it most likely will work, especially if I tested it, and experience also tells me that I’ve recovered from disasters before, so I can probably fix it.  It’s funny though because the only time I’ve had problems is when I didn’t worry at all, what does that mean?

I’m not sure all who have been tagged, so I’m not going to tag anyone else.

Do You Use Projects/Solutions in SSMS?

When SQL Server 2005 was released Microsoft made a major change to the client tools provided with SQL Server, moving from Enterprise Manager for management tasks and Query Analyzer for scripting to the Visual Studio-based SQL Server Management Studio.  I have to admit that when SSMS was released I didn’t like it and continued using Query Analyzer for a while.  I have now adjusted to SSMS and, while I still wish I had a simple query tool, have grown to appreciate what it does.  My two favorite features are the Standard Reports and the ability to add Projects and Solutions like in Visual Studio.
I have to admit that I don’t use the Project and Solutions feature enough in my day to day work, but I do use it when I put together a blog post or presentation for a SQLSaturday or User Group.  By putting all the code for the demos in a Project I don’t have to go searching the file system to find a demo, I don’t have to have the code pre-loaded into SSMS, and I can stay in SSMS throughout the demos.  Here’s my project for my Default Trace presentation:
As you can see it provides a single place for all your scripts.  You can also add the Project/Solution to your source control product of choice (I don’t have one installed on my personal laptop at this time). 
I will be working with this feature more to have projects for my database scripts, maintenance scripts, and my favorite queries in addition to using it for presentations and blogs.
I’d love to hear how you are using projects and solutions in SSMS.

OPASS December Meeting Recap

This was the first meeting where I really lead most of the meeting.  Andy Warren (@sqlandy) did the networking at the beginning and the raffle at the end while I did announcements and set the agenda.  As usual, I forgot to mention something at the beginning, the Florida Microsoft Speaker Idol competition for which we had two takers (Tommy Bolhoffer, probably misspelled, and Robert Hurwitz, again probably misspelled).  Exciting times at OPASS!

We started the meeting with some time to meet and talk to someone you didn’t know or didn’t know well.  I spoke with Ronnie, Lisa, Rob, and several others during this 10-15 minute time.  Then I went over the PASS November-December slide deck and announcements, and Andy, Kendal Van Dyke (@SQLDBA), and I shared some of our takeaways from the PASS Summit to show the value in attending and encourage others to attend.

Our main speaker was Gus Gwynne (GSquared on SQLServerCentral) speaking on “Auditing and Logging”.  He did a very good job.  He shared reasons for auditing (Blamethrower was my favorite, though not his), different types of auditing which include passive (log reading, tracing) and active (triggers, logging within the transaction).  He also shared several examples using triggers and had some good stories to illustrate how logging has helped in his workplace.  One of the interesting stories was when an outsourcing company updated the last name and password for all users in a database, and, because a trigger logged the changes, he was able to quickly (within minutes) undo the update.

We then raffled off a few prizes, a Microsoft Arcmouse, Office 2007 Standard, and Windows 7 Ultimate 64-bit were the big ticket items.  After the raffle we had some good discussion about speaking, self-promotion, and personality.

All in all a good night.  Now I need to find a speaker for January.  If you are interested contact me, @unclebiguns (blog post here for why that’s my handle) on Twitter or via the contact me link on the blog.  We are open to do a Live Meeting or having you come in person.

T-SQL Tuesday #001 – Dates and Times

T-SQL Tuesday was started by Adam Machanic (@AdamMachanic)  on his blog to encourage SQL Bloggers to share their tips and tricks about a specific topic once a month.  A great idea and a great way to get a topic to blog about!
I’ve been working with SQL Server since 1999 and it wasn’t until the last couple of years that I finally learned a better way to retrieve a date range.  I used to do:

SELECT
columns
FROM
TABLE
WHERE
date_column BETWEEN start_date AND end_date;

I’m guessing that there are people reading this that are saying, “I do that all the time and it works fine for me, so what’s the problem?”.  I understand where they are coming from, but I finally really understood that, until the specific DATE data type (date only) in SQL Server 2008, datetime and smalldatetime columns ALWAYS have a time part.  This can and does affect date range queries.  Here’s an example, albeit slightly contrived, but I’ve seen it happen in the real word:

DECLARE @sales TABLE (sale_id INT IDENTITY(1,1) PRIMARY KEY, sale_date DATETIME, sale_amt FLOAT);

WITH cteNums AS 
(
SELECT TOP 50
ROW_NUMBER() OVER (ORDER BY AC.NAME) AS N
FROM
sys.all_columns AS AC 
)
INSERT INTO @sales (
sale_date,
sale_amt
)
SELECT
DATEADD(DAY, -N, GETDATE()) AS sales_date,
N * ABS(CHECKSUM(NEWID()))/10000.00 AS sale_amt 
FROM
cteNums 

/* 
Setup for contrived example.
Make sure the first sale of the month is at midnight 
*/     
UPDATE @sales
SET sale_date = DATEADD(DAY, DATEDIFF(DAY, 0, sale_date), 0)
WHERE
DATEPART(DAY, sale_date) = 1

/*
Task is to get sales for the previous full month
*/
/*
Set up variables for the first and last day of th month
*/
DECLARE @start_date DATETIME,
@end_date DATETIME

/*
Set the variables to the first of last month and the last day of 
last month at the time of writing '2009-11-01' and '2009-11-30' See this blog post by Lynn Pettis for why I am using DATEADD and DATEPART with 0
*/        
SELECT
@start_date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0),
@end_date = DATEADD(DAY, -1, DATEADD(MONTH, 1, @start_date))

SELECT @start_date, @end_date, DATEADD(DAY, 1, @end_date)

/*
My old method
*/
SELECT
COUNT(*) AS sales,
MIN(sale_date) AS first_sale,
MAX(sale_date) AS last_sale,
SUM(sale_amt) AS total_sales 
FROM
@sales
WHERE
sale_date BETWEEN @start_date AND @end_date 

/*
My new method - accurate
*/    
SELECT
COUNT(*) AS sales,
MIN(sale_date) AS first_sale,
MAX(sale_date) AS last_sale,
SUM(sale_amt) AS total_sales 
FROM
@sales
WHERE
sale_date >= @start_date AND 
/*First of the next month */
sale_date < DATEADD(DAY, 1, @end_date)

Now I'm sure someone out there will say, “Hey in your last example you are using the first day of the next month and you could do that with between.” Well yes I could and here is the query:

SELECT
COUNT(*) AS sales,
MIN(sale_date) AS first_sale,
MAX(sale_date) AS last_sale,
SUM(sale_amt) AS total_sales 
FROM
@sales
WHERE
sale_date BETWEEN @start_date AND DATEADD(DAY, 1, @end_date)

But this does not return the correct results either.  Here are the results for each query:

	Count	First Sale	Last Sale	Total
1st Between	29	2009-11-01 00:00:00.000	2009-11-29 11:09:30.107	86362080.49
>= and <	30	2009-11-01 00:00:00.000	2009-11-30 11:09:30.107	86362080.49
2nd Between	31	2009-11-01 00:00:00.000	2009-12-01 00:00:00.000	86362080.49

All that to show that I believe you are better off using >= and < instead of BETWEEN when comparing dates.

Good Programmer = Lazy & Dumb?

Alternative title for programmers using C-derivatives and Java:

Good Programmer == Lazy & Dumb?

I read this blog post, Why Good Programmers Are Lazy and Dumb, and it struck me a being an excellent perspective.  Especially with these 2 quotes:

Lazy, because only lazy programmers will want to write the kind of tools that might replace them in the end.

My goal when working on a project it produce a product that takes me out of the loop on the project.  By that I mean that I want to provide the end users all the tools they need to manage the application once released to them.  No, I don’t want to have to manage in-application security, I want a tool provided to the appropriate person to do that.  I want to be a DBA\Developer, NOT an application administrator.  I want to be able to work on new and exciting projects, not stick with one.

But there’s a more crucial point why a good programmer must be dumb. That’s because for him to find the best solutions to problems, he must keep a fresh mindset and manage to think out of the box (or rather, know its actual shape).

Sometimes, the worst situation is the one where you are re-writing an existing application.  Why?  Because you, or someone else on the team, knows too much about it and can’t see new ways to do it.  A few years ago I moved to a new position and was put on a project with one of the existing developers who had been in the organization for several years and had written much of the current application.  Let’s just say this was a blessing and a curse.  They knew the processes well and what hadn’t worked well, but this also meant that they weren’t able to see other ways to do things which may be better.  I was the “dumb” programmer in this situation and tended to ask, “Why?” a lot and suggest alternate ways of doing things.

So the questions are:

1. Are you trying to write software that replaces you?  Are you automating the routine tasks of being a DBA so that, with proper documentation, someone can come behind you and not miss a beat?

   I know I have a long way to go in regards to automation and documentation, but I’m working on it.

2. Are you content with, “I’ve always done it that way”, or are you looking for new or better ways to do things?

   I’d like to think that this an area I do well in.  I’m always looking for a better way to do something, which is why I attend user group meetings, read books and blogs, and participate in the online SQL Community.

How about you?

Book Review: Apress Pro SQL Server 2008 Administration

Several months ago I received a copy of the Apress book, Pro SQL Server 2008 Administration (Amazon) by Ken Simmons (@kensimmons), a friend of mine, and Sylvester Carstarphen (this is my disclaimer that I didn’t buy the book).  Well, I finally finished the book and can post my thoughts.

General Impression

This was a good book, but my first thought was that I expected more from a “Pro” level book.  I’m not sure if this is because my expectations were too high, or because I underestimated my own knowledge.  What the book does cover it covers well and the writing is excellent, but I didn’t think that a “Pro” level book would include doing an install or upgrade from the GUI, which this book includes and I thought that there were a lot of pages on what I would consider basic level tasks (security, indexing, backup & restore).  

The Book

I liked how the book starts with a three chapter section, Introducing Microsoft SQL Server 2008, covering new features, pre-installation, and high-availability options. 

Part 2 – Getting Started

Three chapters on Installing, Upgrading, and Post-Installation. The end of chapter 5, Upgrading to Microsoft SQL Server 2008, where the authors covered post-upgrade tasks like changing compatibility levels, updating statistics, page-verify settings, etc.. was excellent as these are often forgotten or overlooked tasks. 

Part 3 – Administering Microsoft SQL Server 2008

The meat of the book with seven chapters on Multi-server Administration, Managing Security, Administering Database Level Objects, Indexing, Backups, Restore Strategies, and Automating Routine Maintenance. 
Chapter 7 which covers Multi-server Administration, including Policy Based Management and Central Management Servers was excellent, although I think more time could have been spent on these features, especially if less time was spent on the GUI install and upgrade. 
Chapters 8-11 did a good job explaining the mechanics and the how-to’s of security, managing objects, indexing, and backups, but I assumed this would have been covered in a beginner or intermediate level book and a pro book would have spent more on the strategies and why’s of these topics. 
Chapter 12 on Restore and Recovery Strategies does spend more time on the reason for the different restore options and did a better job hitting higher-level topics like Page restores, piecemeal restores, and online-restore options which is why this is one of my favorite chapters. 
Chapter 13 – Automating Routine Maintenance, was one I was a bit disappointed in.  It does a good job of covering Database Mail, SQL Server Agent, and Maintenance plans, but I thought that there would be more on writing and using scripts like Ola Hallengren’s Maintenance scripts or Michelle Ufford’s (@sqlfool) index maintenance script to show how the author’s or others automate and plan for maintenance.

Part 4 – Troubleshooting and Tuning

Three chapters on monitoring, auditing, and managing query performance.
Chapter 14 covers monitoring and does a good job explaining the tools available (Perfmon, DMV’s, DBCC, Profiler/Trace), including the counters and events to watch.  I especially liked how the authors showed how to get performance counter information both from PerfMon and the DMV’s.  Baselining is discussed but one thing is missing, and I haven’t found it anywhere yet, is how/where to store your baseline data for comparison later.  I know I could figure something out, but I want that one to be given to me.
Chapter 15 covers auditing.  Actually it covers the new enterprise edition only SQL Server Audit.  It’s a good chapter and covers it well, I guess, but since I don’t run enterprise edition, I didn’t really play with it all.  I still wish the book had covered some other ways like SQL Trace, the Default Trace, and Common Criteria auditing which are still available even at the non-enterprise level.
Chapter 16 was on managing query performance and included hints on using Profiler and PerfMon together, using the standard performance reports in SSMS, DMV’s, query hints, plan guides, and Resource Governor.  All in all a very informative chapter on performance monitoring/tuning for an administration book.  The chapter does mention another Apress book, SQL Server 2008 Query Performance Tuning Distilled (Amazon), as a more detailed resource for this information (I have it and it is, a review will be coming).

Part 5 - Conclusion

Two chapters covering Secrets to Excelling as a Professional DBA and What’s Next which were excellent as they talk about knowing your limitations, communicating, being a leader, and taking charge of your own professional development.  These are all areas where I can see myself needing growth.

My Conclusion

I recommend the book as I think people at all but the extremely low or extremely high levels will get something from it, but people moving from SQL Server 2000 to 2008 and junior DBA’s will get a lot from it.

Maintaining Security and Performance Using Stored Procedures Part II – Signing

Well, it has been a couple of weeks since my last blog post and over a month since Maintaining Security and Performance Using Stored Procedures Part I – Using EXECUTE AS was posted, although I did spend time working on the originally unplanned follow up to that post when I would have been doing this post.  I know you all have been anxiously awaiting this post.

EXECUTE AS vs. Signing

Like EXECUTE AS, the ability to sign a module (stored procedure, DML trigger, function, or assembly) with a certificate was added in SQL Server 2005.  In addition to being able to allow access to objects within the current database context, signing also allows you to access resources in another database or that require server level permissions.  With EXECUTE AS on functions, stored procedures, and DML triggers you are limited by database context, so if you need access to objects in another database you are out of luck, even if the EXECUTE AS user has proper rights in the database.  You can download code that demonstrates this behavior here.

Demonstrations of Signing

Laurentiu Cristofor has an excellent blog post that demonstrates signing a stored procedure to grant server level permissions here, so I am not going to duplicate his work in this post.  I will demonstrate how to sign a procedure for use within a database and when accessing another database.

Using Signing to enable Dynamic SQL within the database

USE AdventureWorks;
GO

/*
Create a restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

/*
Create a Certificate first
*/    
CREATE CERTIFICATE cert_dynamic_sql 
ENCRYPTION BY PASSWORD = 'c3rtificatePa$$w0rd'
WITH subject = 'Dynamic SQL Security'   
GO 

/* 
Create user based on the certificate
*/ 
CREATE USER certificate_user FROM CERTIFICATE cert_dynamic_sql;

/*
Give the certificate_user select on all objects
*/
GRANT SELECT ON SCHEMA::Person TO certificate_user;
GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE PROCEDURE dbo.FindPhoneByName
(
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null
)
AS   
BEGIN
 SET NOCOUNT ON;

Declare @sql_cmd nvarchar(2000),
@select nvarchar(1000),
@where nvarchar(1000),
@parameters nvarchar(1000);

 Set @parameters = N'@FirstName nvarchar(50), @LastName nvarchar(50)';

Set @select = N'Select
Title,
FirstName,
MiddleName,
LastName,
Suffix,
Phone  
From
Person.Contact';

Set @where = N' Where 1=1 '                

If @LastName is not null
Begin
Set @where = @where + N' And LastName Like @LastName + N''%'' ';
End;

If @FirstName is not null     
Begin
Set @where = @where + N' And FirstName Like @FirstName + N''%''';
End;

Set @sql_cmd = @select + @where;

Exec sys.sp_executesql @sql_cmd, @parameters, @LastName = @LastName, @FirstName = @FirstName;

Return;         
END

GO

/*
Give the restricted user exec on the proceduere
*/
GRANT EXEC ON dbo.FindPhoneByName TO restricted_user; 

GO
/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will fail on the dynamic portion
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO

/*
Sign the procedure
*/
ADD SIGNATURE TO [dbo].[FindPhoneByName]
BY CERTIFICATE cert_dynamic_sql
WITH PASSWORD = 'c3rtificatePa$$w0rd';
GO

/*
Change context to the restricted rights user
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
Exec the procedure - will work now
*/
EXEC [dbo].FindPhoneByName;

GO

/* 
Return to sysadmin rights
*/
revert;

GO

Using Signing to Access an Object in Another Database

In this example I’ll use a signed procedure to access a table in the Northwind database (download here) from the AdventureWorks database (I use a 2005 copy with extra data, the unmodified version is available here).  One thing I found in my testing is that you have to use a private key file in this case.  If anyone knows how to do it without the file please let me know.  Here is the code:

USE MASTER;
GO

/*
Create the restricted_login
*/
CREATE LOGIN restricted_login WITH Password = '$tr0ngPassword';

GO

USE Northwind;

GO

/*
Create the Certificate
*/    
CREATE CERTIFICATE cert_access_other_db 
ENCRYPTION BY PASSWORD = 'c3rtPa$$word'
WITH subject = 'Access Other DB'   
GO 

/* 
Backup the certificate being sure to use a Private Key
*/
BACKUP CERTIFICATE cert_access_other_db TO FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk' ,
ENCRYPTION BY PASSWORD = '3ncRyptKeyPa$$word',
DECRYPTION BY PASSWORD = 'c3rtPa$$word');
GO

/*
Create the certificate user in the Northwind and give needed permissions
*/
CREATE USER certificate_user FROM CERTIFICATE cert_access_other_db;

GO

GRANT SELECT ON dbo.Categories TO certificate_user;

GO 

USE AdventureWorks;

GO

/*
Create a restricted rights database user
*/    
CREATE USER restricted_user FROM LOGIN restricted_login;

GO

/*
Create the procedure
*/
CREATE PROCEDURE [dbo].access_other_db
AS
SET NOCOUNT ON

SELECT 
SYSTEM_USER AS USERName, 
*
FROM
[Northwind].dbo.[Categories] AS C;

RETURN;    

GO

/*
Give the restricted_user execute rights on the sp
*/
GRANT EXEC ON dbo.access_other_db TO restricted_user;

GO

/*
Create the certificate in this database from the file
*/
CREATE CERTIFICATE cert_access_other_db FROM FILE = 'C:\Certificates\cert_access_other_db.cer'
WITH PRIVATE KEY (FILE = 'C:\Certificates\cert_access_other_db.pvk',
DECRYPTION BY PASSWORD = '3ncRyptKeyPa$$word', /*The password used to create the private key */
ENCRYPTION BY PASSWORD = 'D3cryptKeyPa$$word');
GO

/* 
Execute as a sysadmin - works - this is my user
*/
EXEC [dbo].[access_other_db];

GO


/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will fail.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Sign the procedure 
*/
ADD SIGNATURE TO dbo.access_other_db
BY CERTIFICATE cert_access_other_db WITH Password = 'D3cryptKeyPa$$word'
GO

/*
Now execute as the restricted user 
*/
EXECUTE AS LOGIN = 'restricted_login';

GO

/*
This will now work.
*/
EXEC [dbo].[access_other_db];

GO

/*
Back to the sysadmin level
*/
Revert;

GO

/*
Be sure to delete the certificate and
private key when done.
*/

Summary

As you can see from this post and the previous post (or two), the SQL Server team has given you some good options when it comes to using Stored Procedures for data access and manipulation while still maintaining security.  You can use EXECUTE AS to allow cross-schema or within database access and you can use module signing to allow access to system objects or cross-database queries without specifically granting users access to the objects.

Resources

After having planned and started the post because I had not found anything outside of Books On Line, I found a few resources that covered the material as well, and I used each to help me write this post once I found them.

• I linked to Laurentiu Cristofor’s post earlier
• Erland Sommarskog has an excellent write-up on Giving Permissions through Stored Procedures which handles this subject very thoroughly as you would expect form Erland.  Erland’s post helped me get past the fact that I needed to use a private key in order to get the cross database piece working as none of my other resources did this.
• Jonathan Kehayias also answered this this forum post with an example.

In reality I could have just posted these links as these other folks covered the subject on signing thoroughly, but I decided that since I did the research and wanted to try the code myself that I’d share my experiences as well, crediting these guys for their work and hopefully sending them some traffic, however limited, from this blog.

Finally all the MY code from this post can be downloaded from here.

PASS Summit 2009 – Wrap-up – What’s the Value?

I’ve already written four posts (1, 2, 3, 4) that discussed what I did at the Summit, including what I learned, now I want to discuss my thoughts on the event.

This being my first PASS Summit, I really wasn’t sure what to expect.  I had spoken with several people who have attended in the past, but I don’t think they could really explain the experience. 

It’s All in the Family

As I mentioned in another post, the Summit has been described as “A family reunion where you like everyone.”  This really sums it up well.  I did not expect the community to be so much like a family.  Regular attendees would run up and hug each other (men would usually shake hands).  The excitement everyone had to see people again was unbelievable!  Then the way everyone was welcomed into the community family was great!.  By the end of the week it felt like I knew more people than I didn’t know. 

I went with a list of about 50 people I wanted to meet, and in every case they were happy to meet me and have a discussion with me.  The only people on my list that I did not meet were either sick (Lynda Rab, hope you feel better) or did not attend, and I met more than enough other people to make up for the ones who weren’t there.  It also didn’t matter if they were the SQL elite, like Paul Randal, or the relatively unknown like me, everyone was excited to meet you and help you if they could.

The Learning Never Stops

Tuesday through Thursday the day started at about 7am and ran until midnight (later for the karaoke folks), and there was always something to learn at any hour.  From the breakfast discussions to sessions to late night talks, I felt like I was bettering myself and hopefully having the same affect on the people I was with.  I felt like I was always with someone smarter than me and that made me feel smarter. 

The sessions helped me realize how much I actually know about SQL Server.  There were definitely things I learned, but much was familiar to me, which I did not expect.

Meeting Other SQL Server Professionals of Like Faith

If you visit the New Tribes Mission website, you can see what I believe and that I try to live what I believe.  I’m not pushy about my faith, but I don’t hide it either.  It was great to meet several other people who share my faith.  This was something that made the Summit even more special for me.  In the past I have thought that I was in very small minority, but this event helped me see I’m not alone, but I have friends in the community that I can lean on when it comes to faith.  See Mike Walsh’s and K. Brian Kelley’s blogs as well.

The One Bad Thing about PASS

It ends before you can meet everyone!  I’m already looking forward to next year.  If I can attend I plan on doing things differently.  My changes are:

1. Attend sessions about areas I normally do not work in.  This probably means more BI.
2. Do more to make other attendees feel welcome.  I will seek out people sitting or standing alone and speak with them.  This year was about me meeting the big wigs of SQL Server, SQL Server Twitterati, and SQLServerCentral friends, next year will be about others

I can’t recommend the Summit enough.  It is refreshing (even though you are worn out) and inspiring.

Thanks to all those who made it such a great event, the PASS Board, the PASS HQ Staff, volunteers, speakers, and attendees.

PASS Summit Day 3 – Brain on Overload

After the first 2 official days of the Summit the brain is already on overload and then the second part of the keynote by Dr. Dewitt and 45 minutes of Itzik Ben-Gan’s tips and tricks totally fried my brain.

I arrived and sat with Bob Hovious with the plan to connect to the wireless and publish a couple of blog posts I had written the Wednesday night but couldn’t publish because I wasn’t about to pay for internet in the $200/night hotel room.  Unfortunately I wasn’t able to publish the posts because when I turned on the laptop I got an error, No boot device found.  I was totally ticked off, more because of the time I had put in on the blog posts than because of the hardware issue.  Fortunately, after lunch when I went to the hotel to print my boarding pass and check to see if I was still under warranty, it booted up, and has worked since.  I am planning on getting an image done at the office on Monday.

Keynote

The day started off with recognition of departing board members: Pat Wright (@SQLAsylum), Greg Low, and Kevin Kline (@kekline).  It was a tearful goodbye by PASS president Wayne Snider to Kevin as Kevin has served on the board since PASS’s inception.  Kevin has been a tireless worker for PASS and deserved all the recognition and the standing ovation given to him.

Following the recognition of departing members was a presentation by DELL.  Unfortunately it was not the best presentation of the week, to put it mildly.

Then, we were taught by Dr. DeWitt.  He talked about the changes in CPU, memory, and storage since 1980 and how while there have been improvements in each the real improvement especially relative to speed vs. size has been in CPU so I/O is the real bottleneck.  One way to offset this, especially in data warehouse applications, is through the development of columnar data stores vs. the traditional row-based database.  Columnar data stores narrow the data so that there are fewer I/O operations required.  To be honest much of this was over my head, but definitely worth learning more about.

T-SQL Tips and Tricks – Itzik Ben-Gan

I was only able to attend about half of this session because I had to be at the Chapter Leader lunch, but what I heard while in there was mind-blowing.  I was sitting in the back with Kathi Kellenberger (@auntkathi) and Tom LaRock (@SQLRockstar) and at one point Tom said, “Where does he come up with this?  He’s like a mad scientist”, that may be somewhat paraphrased, but the meaning is there.  The 2 new tips for me in that time were:

• Using (select null) in the Order By for windowing functions as a constant which eliminates a sort.  Select RowNumber() Over(Order By (select null)).
• ON clause ordering eliminating the need for multiple LEFT JOIN’s.
  
  customers C LEFT JOIN (sales S JOIN products P ON S.product_id = P.product_id) ON C.customer_id = S.customer_id

Just those 2 tips will help me write better performing and more readable code.

Lunch with Chapter Leaders

As co-president of OPASS, I was tasked to sit at our table at lunch.  The idea, I think, was to let people who are not attending chapter meetings where the chapters are located and to meet people involved in the chapters.  There are a lot of chapters in Florida, but it didn’t seem like there were a lot of chapter members at the Summit other than the chapter leaders.  I only had Kendal Van Dyke (@SQLDba) from the Orlando area sit with me, while Scott Gleason from Jacksonville also joined us because the Jacksonville chapter hadn’t gotten the right paperwork in. 

This is definitely a good idea as was the MVP Birds of a Feather lunch.

Proactive DBA: Manage SQL Server Better – Ross Laforte

This session had to do with finding all your SQL Server instances using the MAP tool and using PBM to manage your servers.  Another session that had content that was familiar to me.  Lots of good information shared, but not necessarily new to me.

Advanced SQL Server 2008 Extended Events: Performance Profiling and Troubleshooting Techniques – Adam Machanic

This was a FANTASTIC session.  There was a ton of information, great demos, and humor.  Adam is one the best presenters I have seen.  I have not done anything with Extended Events as I am running SQL Server 2005, but this session inspired me to try to learn more.  Extended Events are powerful, especially when you use Causality.  Causality can help you track down what caused the condition that CAUSED the event to happen.  I think Extended Events are designed to eventually replace SQL Trace and certainly give you much more flexibility.  While I’m not Brad McGehee, I do consider myself to be an expert in Profiler, but I’m looking forward to being able to use Extended Events.

Post-Event

Since I’m not currently a Friend of Redgate, I didn’t have a party to go to, but did have a great dinner with Bob Hovious, a friend from SQLServerCentral.  After dinner we went back to the Sheraton Lobby lounge to continue our discussion.  We saw a bunch of people and Robert Cain (@arcanecode) and Paul Waters sat down with us for awhile.  These conversations are the highlight of each day.

Friday – Heading Home

Had breakfast with my roommate, Tim Mitchell (@tim_mitchell) and saw some of the board a the restaurant with Bill Graziano (@billgraziano) stopping at our table for a short chat.  Then it was off to the airport where I met up with Pam Shaw, so for the first hour the Summit continued.  I had good flights all the way to Orlando and, while it is great to be home with family, it’s a downer to be away from the SQL family.  I’m already excited for next year’s Summit.  Fortunately I can make a couple of SQLSaturday’s between now and then to get a small fix of Summit and SQL goodness.

PASS Summit Day 2 – What More Can I Learn

I actually slept in on Wednesday until 6am.  This was a change from being wide awake at 4am.  I made my way over to the convention center and was roped into invited to an informal meeting of bloggers with Andy Warren (@sqlandy) and a member of the PASS marketing team.  It was a discussion of how PASS and SQL Server bloggers can work together to get the message of PASS out to the community.  It was more of a brain storming session than a meeting. 

When the meeting closed I spent some time with Gail Shaw and Bob Hovious before the opening remarks and keynote.  PASS recognized some of the key volunteers, Tim Ford (@sqlagentman), Grant Fritchey (@gfritchey), and a few others.  Then it was time for the PASSion awards.  The international PASSion award went to Charlie Hanania while the U.S. award when to Allen Kinsel (@sqlinsaneo), both of whom did yeoman's work for PASS and certainly earned their awards.  After the awards were announced I took a break and worked on blogging my experiences.

Paul Randal – Logging and Recovery

This was my first session of the day.  Because I read Paul’s blog I did know a lot of the material, but he definitely expanded and explained more.  One of his main points is that the Transaction Log is the most important component of the database as ALL changes must be written to the transaction log BEFORE they can be written to the data files.  Some takeaways were:

• Each data page has the last LSN (Log Sequence Number) stamped on the page for recovery purposes.
• While there are minimally logged operations there are NO non-logged operations.
• You can enable trace flag 3502 to see checkpoint operations.
• A checkpoint writes ALL dirty pages from memory to the log file, even pages that are part of an uncommitted transaction.
• The log always reserves enough space to rollback open transactions.
• The log cannot be “cleared” (have virtual log files marked as available) while a Full or Differential backup is occurring, even if there is a simultaneous log backup taken.
• Only log backups “clear” the transaction log, Full and Differential do not.

Very interesting material on the details of the transaction log and how it is used in recovery.

Lunch

I enjoyed lunch with some newly made Christian friends again, Mike Walsh, Brain Moran, Peter Schott, and Erik Veerman.  Always good to spend time with men of faith.

Louis Davidson – Database Design

Louis has an interesting presenting style where he uses a lot of humor to make his point.  His main premise is that taking the time to have a well designed database at the start, saves much time later in the process.

Gail Shaw – Insight into Indexes

Gail’s session was about how to find out information about your indexes using the Dynamic Management Views and Functions.  I did know a bit about this, but I did not know about sys.dm_index_operational_stats which can help you see what is happening to indexes (inserts, updates, deletes) and how they are affecting locking, i.e. the number of locks taken on the resource by type (page, row).  Page Latch (waiting for access to page in memory) and Page IO Latch  (waiting for pages to be written to disk or read from disk) wait information is also available in this DMV.  Gail did a great job using a book to demonstrate how indexes work, a very good visual example.

Panel Discussion with the PASS Board of Directors

For the first time the BoD made themselves available for a question and answer session, moderated by former board member Joe Webb.  This was a late addition to the schedule, which contributed to the light turnout, about 15-20 people, not counting the board members.  One of the key points to come out is that there needs to be better communication between the board and other parties (members, chapter leaders, and sponsors/partners).  Chapter leaders would like more support from PASS and PASS would like more information from the chapter leaders. 

Steve Jones (@way0utwest) asked for more transparency and publication of the goals and accomplishments for each board member.  His point is that, to be honest, the community really doesn’t know what they are doing/have done, and they should be publishing their accomplishments.

Another interesting issue, raised by Jessica Moss, was that we, the community, need to know who to contact and how to go about presenting ideas we have for the organization.  Kevin Kline (past president) answered that the board was working on a “process” for this.  Jessica followed up by asking “When will this process be available?” and, unfortunately, there was not a concrete answer given, probably the low point of the session.

Lastly I asked about the recent BoD election controversy and if that caused them to consider changing the election process.  There was clear, “Yes”, and that there may need to be changes made to the by-laws to make the election process better.  I also commented that I’d like to see the BoD aim higher for the number of voters than the 1100 or so they are looking for next year.  Granted that would be double the votes cast this year, but still a small percentage of membership (30000).  I took some grief from Andy Warren as I would not/could not provide a realistic number to shoot for for next year’s election.  I’ll be thinking about this later.

It was, I think, a positive experience for both the BoD and those from the community who attended and I applaud the efforts being made by the BoD to be more transparent and make themselves more easily available to the membership.  As a relative newcomer to PASS, I can’t speak for how available they have been in the past, but I do believe that they are doing the best they can.

I certainly haven’t covered everything that was discussed, but I covered what I remembered.

I also want to thank the board for making themselves available for this session, it was definitely a step in the right direction.  I also want to thank Bill Graziano (@billgraziano) for being willing to hang around a while longer to continue the discussion.

Please take the time to email or talk to the board members and bring them your ideas and goals for PASS.  I believe that they truly want to serve the needs of the community, but without feedback they can’t know if they are focusing on the areas we, as a community, are most concerned about.

In the evening

I went over to the party provided by Microsoft at Gameworks, the biggest arcade/game room, I have ever been in.  There was good food and unlimited game play made available.  I grabbed some food and visited with some of my new friends while there and then went out with old friends before heading in for an early evening (10:00pm).

Trying to save up energy for my last day at the Summit where I will get to “host” the OPASS table at the Chapter Leader lunch.

Let the Learning Begin

Finally time for the sessions to begin after a day and a half in Seattle being overwhelmed by the people that I’ve had the opportunity to meet.

I started the day meeting with Mike Walsh (@mike_walsh) and Joe Webb (@joewebb) in the Sheraton living area (lobby) where we had a short time of prayer to start the day.  We then went on to meet with some other men of like faith to discuss Brian Moran’s idea about reaching others.  It was great to meet Brian and encouraging to know others in the SQL Server community that share my faith.  Then it was on to the keynote.

Keynote

I attended the keynote by Bob Muglia and Ted Kummert from Microsoft. Mostly stuff about the new technologies available in SQL Server 2008 R2, Windows 2008 R2, and Visual Studio 2010/.NET 4.  The coolest thing was the LIVE migration of VM’s using Hyper-V.  Move a running VM from one VM server to another without interrupting the virtual server!  Wow!

Session 1

My first session was titled Data Access Layers:  A road map to smarter, efficient, and effective queries.  It was not exactly what I anticipated as it was a session mainly about using Inline Table Valued functions to replace views and direct table access.  I’m not a big fan of UDF’s because they are often mis-used and have to admit I tuned out a little bit and spent time following Twitter so I’d know where the people I wanted to meet were.

Lunch

Today’s lunch was a Birds of a Feather lunch with MVP’s hosting topic-focused tables.  I went to Paul Randal’s (@PaulRandal) table about Corruption and High Availability.  It was very interesting to hear him tell stories and hear the stories of other folks at the table.  It was also great that he was able to give me reason why a restored database with no activity would have transaction log growth. It was AutoShrink!  AutoShrink is evil in more ways than one.  It was also cool that when I introduced myself, after everyone else had left because I was late to lunch, he recognized my name immediately from the SQLServerCentral forums and Twitter and asked why I hadn’t introduced myself earlier!  I really try not to put people on a pedestal, but he and Kimberly Tripp (his wife) are pretty much universally recognized as SQL Server royalty, so it’s definitely cool to meet them and then be recognized as well!  Both are very nice and desire to help people out.  That’s the great thing about the SQL Server community, it seems that the more well-known you are, the more helpful you are.

After Lunch

Because of my discussion with Paul, Kim, and Gail Shaw I didn’t make the first session after lunch so I spent some time in the vendor exhibit hall looking around and talking with some of the vendors.

Next I went to Andy Leonard’s session, A Tale of Careers and User Groups, where Andy explained how he became involved in user groups and the lessons he has learned.  He gave some good tips about how to get people involved in a user group.  A key way is to recruit  encourage those who come early or stay late to be a part of the user group leadership team.

Finally I checked into Kalen Delaney’s session, Indexing Internals.  It was a good session on the structure of indexes.  The interesting thing was that, having read Kalen’s book and some other resources on indexes, I actually knew the majority of the information.  It was interesting to see what I actually know.

After the session I had a great discussion with Lorie Edwards (@lorieedwards) about what to learn in SQL Server and how the size of your environment definitely affects the features you need.

Dinner

I went out to dinner with Andy Warren, Steve Jones, Pam Shaw, Rob, Tim Mitchell and Kendal Van Dyke.  We didn’t talk much SQL Server, but more general conversation.  Always a good time with this crew.

After dinner I went to a party sponsored by SQL Sentry and met a ton of new people.  The guys from SQL Sentry (Greg, Peter Shire), Adam Machanic, Peter Ward, Charlie Hanania, Chuck Heinzelman, and others. 

Finally I went back to the hotel about midnight and crashed.

My First PASS Experiences

Getting to Seattle

I started my travels to the PASS Summit at about 9:30 am (EST) on Sunday and arrived at the Sheraton in Seattle at about 6:30 pm (PST) Sunday.  It was an uneventful trip on Southwest from Orlando to Albuquerque, then on to Seattle.  On the second leg I met up with Tim Mitchell (@tim_mitchell) which made that leg seem much shorter as we had great conversations about PASS, SQL Server, professional development, and family life.  I’m sure we drove the 3rd person in our row crazy as we talked the entire 3 hour flight.  When we arrived at SeaTac we met up with Wendy Pastrick (@wendy_dance) and Damon Ripper (@DamonRipper) and shared a town car to the Sheraton.

Sunday Night

Amazingly enough a lot of attendees made it on Sunday so there were some good networking opportunities.  Tim and I had originally planned to try to get together with Andy Warren (@sqlandy) who was having dinner with “Small Talk” expert, Don Gabor.  Well, since we had met up with Wendy and Damon and then met with a fairly large group of other attendees in the Sheraton lounge we decided to just hang with this crew.  Amazingly, I was able to meet several people on my list during the evening.  Some of the folks I was able to meet were:

• Bill Graziano (@billgraziano) – who organized dinner for about 20 of us.
• Jeremiah Peschka (@peschkaj)
• Grant Fritchey (@gfritchey)
• Wendy Pastrick (@wendy_dance)
• Allen Kinsel (@sqlinsaneo)

Then there all the people who were not on my list including:

• Louis Davidson (@DrSQL)
• Rich Baumet
• Scott Stauffer (@SQLSocialite)
• Todd McDermid (@Todd_McDermid)
• Todd Robinson (@DevSQL)
• Chris Massey (@camassey)
• Andrew Clarke
• Denny Cherry (@mrdenny)

And the people I already knew like Jessica Moss, Andy Leonard (@AndyLeonard), and Pam Shaw.  The only downside was that I stayed up until after midnight, but my body decided that it was still on the East coast and woke up at 4 am and refused to go back to sleep.

Monday

Started off the morning with a nice walk with my roommate Tim Mitchell to Top Pot Donuts to meet with Andy Warren and Don Gabor.  While there Robert Cain (@arcanecode), on my list, and Greg Larsen, popped in to talk with us, well, they know Andy, so they stopped to talk with him, but, of course, it was great opportunity to meet them.

After that I headed over to the convention center to check into the event and on the way I ran into Andy Leonard and Jim (from the Northern Virginia User Group) and stopped to talk for bit.  At registration I also saw Buck Woody (@BuckWoody) again and was able to introduce him to Tim Mitchell.  By then it was time for the Chapter Leader meeting, I wasn’t officially on the list, but as co-President of OPASS with Andy Warren I attended.  It was nice meeting where the chapter leaders got an opportunity to discuss what PASS can/should do to help chapters.  The meeting was led by director Greg Low (another person on my list that I met), he is currently in charge of chapters, assisted by Blythe Morrow (@blythemorrow) who is the community coordinator for PASS.  We discussed:

• how PASS can better integrate chapter meetings and events into the PASS event calendar.
• better list and messaging management for PASS-hosted chapter sites.
• re-working the responsibilities of the regional mentors
• holding chapters accountable for meeting and growing
• PASS providing a speaker bureau
• the use and viability of Live meeting as provided by PASS
• training for chapter leaders, especially on using Live meeting and the DotNetNuke chapter sites hosted by PASS.
• If PASS should consider hosting the Summit in locations other than Seattle.

The best part is that most of the items discussed were items I broached about PASS helping local chapters in my blog post, What Should PASS Strive to Be?.  I think it showed that PASS is trying to become more relevant at the Chapter level. 

I then went out to see who else was around and met Gail Shaw, who had the quote of the day, “It’s like a family reunion where you want to see everyone.”.  That might be a bit of a paraphrase, but the meaning was there.  I also met Bob Hovious, Brent Ozar (@BrentO), and ran into Kevin Kline (@kekline) and Steve Jones (@way0utwest).  I went to lunch with Steve, always a good time.  We ended up at the Fox Sport Grill where I got a very good burger.  It’s always good to sit and talk with Steve.  We spend more time discussing family and life in general than technical stuff, which is a nice change.

Let the Networking Begin

After lunch I attended a networking session for PASS volunteers with Don Gabor.  It was a great session about starting conversations and leaving a good impression.  It was fun with lots of exercise to help us apply the concepts.  The biggest thing I got out of it was how to remember names.  He shared his 5-second plan for remembering names, with, in my opinion, the key point being to concentrate on the other person’s name during the introduction instead of thinking about the next thing you are going to say.  During this session I met many people, whose last names I did not get, but there were several people on my list in the session that I did meet and, because they are on my list, I do remember their full names:

• Michelle Ufford (@sqlfool)
• Tim Ford (@sqlagentman)
• TJay Bely (@tjaybelt)
• Pat Wright (@SqlAsylum)
• Tom LaRock (@SQLRockstar)
• Jason Strate (@StrateSQL)

Following the volunteer networking session there was longer, Networking for Business Contacts session with Don, that covered the content from the first session, but also included more information about body language (Smile, Open arms, Forward lean, Touch, Eye contact, Nod).

Opening Reception and SSC Party

From networking to the opening reception where some prizes were given out including the Log Reader Awards for blogging (I didn’t win any).  Then three 2-person teams competed in the Quiz Bowl put together by Tim Ford and Louis Davidson.  It was done Jeopardy-style with all questions having some kind of relationship to SQL Server.  The three teams were:

• Paul Randal and Kimberly Tripp – who, not surprisingly won.
• Brent Ozar and Kevin Kline – they really knew their pop culture.
• Grant Fritchey and Joe Webb

I tried to practice my newly learned networking skills at the reception, especially in one case where I saw someone sitting alone, so I went over and introduced myself to Phil, but I think I failed as I took a quick break to grab some food and one I came back, Phil was gone!  Well Don, I think I need more practice. I also met Arlene Gray (@whimSQL).

From there is was on to the SQLServerCentral party where I met Kevin Boles and Wes Brown and saw many other old friends.  I played some Texas Hold’em and managed to win more than I lost, but not enough to win a prize.  There was no cash involved as you were given a ticket for chips and then played until either time or your chips ran out.  I was also able to meet Marc Beacom (@marcbeacom) and re-connect with Tony Davis and Brad McGehee (@bradmcgehee)

After the Party

Many people went out to karaoke later in the evening, but I stayed late at the SSC party, so I was left behind, and to be honest, I was ready for a break and an earlier night. 

The amazing thing is that my head is already spinning after meeting so many great people (many of whom I have not named because I only remember first names).  I can’t imagine what it will be like when the sessions start and everyone arrives.

Also looking forward to getting together with people of like-faith on Tuesday.